首頁  >  文章  >  資料庫  >  mysql 查

mysql 查

黄舟
黄舟原創
2017-01-16 13:14:201137瀏覽

獲取表格有多少行
技巧:

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 * good (4,11); 

取出價格>=100 且

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

取出價格=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

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

in是散點的集合,between and是
3也不是11的欄位


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

算出比市價優惠的數值


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

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


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




疑點注意:where是對真實表中的資料發揮作用,having可以對where結果進行過濾


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

作用相同


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

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


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

like模糊查詢

截取諾基亞後面的內容


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;

查找有諾基亞開頭的更換為htc(沒有更改真實表)

。 htc(更改真實表格內容)

rrreee

以上就是mysql 查的內容,更多相關內容請關注PHP中文網(www.php.cn)!

🎜🎜
陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn