.jpg)
獲取表有多少行
技巧：
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);?
取出價(jià)格>=100 且
select?*?from?goods?where?shop_price?>=?100?and?shop_price?<p>取出價(jià)格=500<br><br></p><pre class="brush:sql;toolbar:false">select?*?from?goods?where?shop_price?=?500; select?*?from?goods?where?shop_price?not?between?100?and?500;
in是散點(diǎn)的集合，between and是區(qū)間?
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);
算出比市場價(jià)優(yōu)惠的數(shù)值
select?goods_id,(market_price-shop_price)?as?chajia?,goods_name?from?goods?;
查找本地價(jià)格比市場價(jià)便宜200以上的
select?goods_id,(market_price-shop_price)?as?chajia?,goods_name?from?goods?where?(market_price?-?shop_price)?>?200;
（chajia列是where作用過之后的產(chǎn)生的）
疑點(diǎn)注意：where是對真實(shí)表中的數(shù)據(jù)發(fā)揮作用，having可以對where結(jié)果進(jìn)行過濾
select?goods_id,(market_price-shop_price)?as?chajia?,goods_name?from?goods?where?chajia?>?200;(錯(cuò)誤的)
作用相同
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模糊查詢
截取諾基亞后面的內(nèi)容
select?goods_id?,goods_name,substring(goods_name,4)?from?goods?where?goods_name?like?'諾基亞%';
查找有諾基亞開頭的更換為htc（沒有更改真實(shí)表內(nèi)容）
select?goods_id?,goods_name,concat('htc',substring(goods_name,4))?from?goods?where?goods_name?like?'諾基亞%';
把諾基亞更換為htc（更改真實(shí)表內(nèi)容）
update?goods? set?goods_name?=?concat('htc',substring(goods_name,4)) where?goods_name?like?'諾基亞%'?and?cat_id=4;
以上就是mysql 查的內(nèi)容，更多相關(guān)內(nèi)容請關(guān)注PHP中文網(wǎng)（www.php.cn）！
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
.png)
