首页  >  文章  >  数据库  >  mysql 查

mysql 查

黄舟
黄舟原创
2017-01-16 13:14:201164浏览

获取表有多少行
技巧:

select count(*) from table_name;


取出cat_id=4和cat_id=11的列
使用or select * from goods where cat_id=4 or cat_id=11;
不使用or select * from goods where cat_id in(4,11); 

取出价格>=100 且ce3a6f74fe39e80693ae6d556615beb2=500

select * from goods where shop_price <=100 and shop_price >= 500;
select * from goods where shop_price not between 100 and 500;

in是散点的集合,between and是区间 

cat_id不是3也不是11的列

select * from goods where cat_id!=3 and cat_id!=11;
select * from goods where cat_id not in(3,11);

算出比市场价优惠的数值

select goods_id,(market_price-shop_price) as chajia ,goods_name from goods ;

查找本地价格比市场价便宜200以上的

select goods_id,(market_price-shop_price) as chajia ,goods_name from goods where (market_price - shop_price) > 200;



(chajia列是where作用过之后的产生的)

疑点注意:where是对真实表中的数据发挥作用,having可以对where结果进行过滤

select goods_id,(market_price-shop_price) as chajia ,goods_name from goods where chajia > 200;(错误的)

作用相同

select goods_id,(market_price-shop_price) as chajia ,goods_name from goods having chajia>200;

把mian表中的num列中 [20,29]改为20 [30,39]改为30

update mian set num = floor(num/10)*10 where num between 20 and 39;

like模糊查询

截取诺基亚后面的内容

select goods_id ,goods_name,substring(goods_name,4) from goods where goods_name like &#39;诺基亚%&#39;;

查找有诺基亚开头的更换为htc(没有更改真实表内容)

select goods_id ,goods_name,concat(&#39;htc&#39;,substring(goods_name,4)) from goods where goods_name like &#39;诺基亚%&#39;;

把诺基亚更换为htc(更改真实表内容)

update goods 
set goods_name = concat(&#39;htc&#39;,substring(goods_name,4))
where goods_name like &#39;诺基亚%&#39; and cat_id=4;

以上就是mysql 查的内容,更多相关内容请关注PHP中文网(www.php.cn)!


声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn