国产农村妇女毛片精品久久,久久久久久极精品久久久,久久99精品久久久久久9蜜桃

mysql子查詢語句是指在另一個(gè)查詢語句中的SELECT子句，例句如“SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);”。

mysql子查詢語句是什么

本文操作環(huán)境：Windows7系統(tǒng)、mysql5.7版、Dell G3電腦。

mysql子查詢語句是什么？

mysql 子查詢語句：

1.子查詢是指在另一個(gè)查詢語句中的SELECT子句。

? 例句：

??SELECT?*?FROM?t1?WHERE?column1?=?(SELECT?column1?FROM?t2);

? 其中，SELECT * FROM t1 …稱為Outer Query[外查詢](或者Outer Statement),

? SELECT column1 FROM t2 稱為Sub Query[子查詢]。

? 所以，我們說子查詢是嵌套在外查詢內(nèi)部。而事實(shí)上它有可能在子查詢內(nèi)部再嵌套子查詢。

? 子查詢必須出現(xiàn)在圓括號(hào)之間。

? 行級(jí)子查詢

??SELECT?*?FROM?t1?WHERE?(col1,col2)?=?(SELECT?col3,?col4?FROM?t2?WHERE?id?=?10); ??SELECT?*?FROM?t1?WHERE?ROW(col1,col2)?=?(SELECT?col3,?col4?FROM?t2?WHERE?id?=?10);

? 行級(jí)子查詢的返回結(jié)果最多為一行。

? 優(yōu)化子查詢

? — 創(chuàng)建數(shù)據(jù)表

CREATE?TABLE?IF?NOT?EXISTS?tdb_goods( ????goods_id????SMALLINT?UNSIGNED?PRIMARY?KEY?AUTO_INCREMENT, ????goods_name??VARCHAR(150)?NOT?NULL, ????goods_cate??VARCHAR(40)??NOT?NULL, ????brand_name??VARCHAR(40)??NOT?NULL, ????goods_price?DECIMAL(15,3)?UNSIGNED?NOT?NULL?DEFAULT?0, ????is_show?????BOOLEAN?NOT?NULL?DEFAULT?1, ????is_saleoff??BOOLEAN?NOT?NULL?DEFAULT?0 ??);

?— 寫入記錄

INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('R510VC?15.6英寸筆記本','筆記本','華碩','3399',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('Y400N?14.0英寸筆記本電腦','筆記本','聯(lián)想','4899',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('G150TH?15.6英寸游戲本','游戲本','雷神','8499',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('X550CC?15.6英寸筆記本','筆記本','華碩','2799',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('X240(20ALA0EYCD)?12.5英寸超極本','超級(jí)本','聯(lián)想','4999',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('U330P?13.3英寸超極本','超級(jí)本','聯(lián)想','4299',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('SVP13226SCB?13.3英寸觸控超極本','超級(jí)本','索尼','7999',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('iPad?mini?MD531CH/A?7.9英寸平板電腦','平板電腦','蘋果','1998',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('iPad?Air?MD788CH/A?9.7英寸平板電腦?（16G?WiFi版）','平板電腦','蘋果','3388',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('?iPad?mini?ME279CH/A?配備?Retina?顯示屏?7.9英寸平板電腦?（16G?WiFi版）','平板電腦','蘋果','2788',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('IdeaCentre?C340?20英寸一體電腦?','臺(tái)式機(jī)','聯(lián)想','3499',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('Vostro?3800-R1206?臺(tái)式電腦','臺(tái)式機(jī)','戴爾','2899',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('iMac?ME086CH/A?21.5英寸一體電腦','臺(tái)式機(jī)','蘋果','9188',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('AT7-7414LP?臺(tái)式電腦?（i5-3450四核?4G?500G?2G獨(dú)顯?DVD?鍵鼠?Linux?）','臺(tái)式機(jī)','宏碁','3699',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('Z220SFF?F4F06PA工作站','服務(wù)器/工作站','惠普','4288',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('PowerEdge?T110?II服務(wù)器','服務(wù)器/工作站','戴爾','5388',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('Mac?Pro?MD878CH/A?專業(yè)級(jí)臺(tái)式電腦','服務(wù)器/工作站','蘋果','28888',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('?HMZ-T3W?頭戴顯示設(shè)備','筆記本配件','索尼','6999',DEFAULT,DEFAULT); ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('商務(wù)雙肩背包','筆記本配件','索尼','99',DEFAULT,DEFAULT); ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('X3250?M4機(jī)架式服務(wù)器?2583i14','服務(wù)器/工作站','IBM','6888',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('玄龍精英版?筆記本散熱器','筆記本配件','九州風(fēng)神','',DEFAULT,DEFAULT); ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('?HMZ-T3W?頭戴顯示設(shè)備','筆記本配件','索尼','6999',DEFAULT,DEFAULT); ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('商務(wù)雙肩背包','筆記本配件','索尼','99',DEFAULT,DEFAULT);

— 求所有電腦產(chǎn)品的平均價(jià)格,并且保留兩位小數(shù)，AVG,MAX,MIN、COUNT、SUM為聚合函數(shù)

SELECT?ROUND(AVG(goods_price),2)?AS?avg_price?FROM?tdb_goods;

— 查詢所有價(jià)格大于平均價(jià)格的商品，并且按價(jià)格降序排序

SELECT?goods_id,goods_name,goods_price?FROM?tdb_goods?WHERE?goods_price?&gt;?5845.10?ORDER?BY?goods_price?DESC;

— 使用子查詢來實(shí)現(xiàn)

SELECT?goods_id,goods_name,goods_price?FROM?tdb_goods? ??WHERE?goods_price?&gt;?(SELECT?ROUND(AVG(goods_price),2)?AS?avg_price?FROM?tdb_goods)? ??ORDER?BY?goods_price?DESC;

— 查詢類型為“超記本”的商品價(jià)格

SELECT?goods_price?FROM?tdb_goods?WHERE?goods_cate?=?'超級(jí)本';

— 查詢價(jià)格大于或等于”超級(jí)本”價(jià)格的商品，并且按價(jià)格降序排列

SELECT?goods_id,goods_name,goods_price?FROM?tdb_goods? ???WHERE?goods_price?=?ANY(SELECT?goods_price?FROM?tdb_goods?WHERE?goods_cate?=?'超級(jí)本') ???ORDER?BY?goods_price?DESC;

? ?

— = ANY 或 = SOME 等價(jià)于 IN

SELECT?goods_id,goods_name,goods_price?FROM?tdb_goods? ???WHERE?goods_price?IN?(SELECT?goods_price?FROM?tdb_goods?WHERE?goods_cate?=?'超級(jí)本') ???ORDER?BY?goods_price?DESC;

— 創(chuàng)建“商品分類”表

CREATE?TABLE?IF?NOT?EXISTS?tdb_goods_cates( ????cate_id?SMALLINT?UNSIGNED?PRIMARY?KEY?AUTO_INCREMENT, ???? ????cate_name?VARCHAR(40) ??);

— 查詢tdb_goods表的所有記錄，并且按”類別”分組

??SELECT?goods_cate?FROM?tdb_goods?GROUP?BY?goods_cate;

— 將分組結(jié)果寫入到tdb_goods_cates數(shù)據(jù)表

?INSERT?tdb_goods_cates?(cate_name)?SELECT?goods_cate?FROM?tdb_goods?GROUP?BY?goods_cate;

— 通過tdb_goods_cates數(shù)據(jù)表來更新tdb_goods表

UPDATE?tdb_goods?INNER?JOIN?tdb_goods_cates?ON?goods_cate?=?cate_name? ??SET?goods_cate?=?cate_id?;

— 通過CREATE…SELECT來創(chuàng)建數(shù)據(jù)表并且同時(shí)寫入記錄

--?SELECT?brand_name?FROM?tdb_goods?GROUP?BY?brand_name; ??CREATE?TABLE?tdb_goods_brands?( ????brand_id?SMALLINT?UNSIGNED?PRIMARY?KEY?AUTO_INCREMENT, ????brand_name?VARCHAR(40)?NOT?NULL ??)?SELECT?brand_name?FROM?tdb_goods?GROUP?BY?brand_name;

— 通過tdb_goods_brands數(shù)據(jù)表來更新tdb_goods數(shù)據(jù)表(錯(cuò)誤)

UPDATE?tdb_goods??INNER?JOIN?tdb_goods_brands?ON?brand_name?=?brand_name ??SET?brand_name?=?brand_id; ??--?Column?'brand_name'?in?field?list?is?ambigous

? — 正確

??UPDATE?tdb_goods?AS??g??INNER?JOIN?tdb_goods_brands?AS?b?ON?g.brand_name?=?b.brand_name ??SET?g.brand_name?=?b.brand_id;

— 查看tdb_goods的數(shù)據(jù)表結(jié)構(gòu)

DESC?tdb_goods;

— 通過ALTER TABLE語句修改數(shù)據(jù)表結(jié)構(gòu)

ALTER?TABLE?tdb_goods?? ??CHANGE?goods_cate?cate_id?SMALLINT?UNSIGNED?NOT?NULL, ??CHANGE?brand_name?brand_id?SMALLINT?UNSIGNED?NOT?NULL; ?? --?分別在tdb_goods_cates和tdb_goods_brands表插入記錄 ???INSERT?tdb_goods_cates(cate_name)?VALUES('路由器'),('交換機(jī)'),('網(wǎng)卡'); ???INSERT?tdb_goods_brands(brand_name)?VALUES('海爾'),('清華同方'),('神舟');

— 在tdb_goods數(shù)據(jù)表寫入任意記錄

INSERT?tdb_goods(goods_name,cate_id,brand_id,goods_price)?VALUES('?LaserJet?Pro?P1606dn?黑白激光打印機(jī)','12','4','1849');

— 查詢所有商品的詳細(xì)信息(通過內(nèi)連接實(shí)現(xiàn))

?SELECT?goods_id,goods_name,cate_name,brand_name,goods_price?FROM?tdb_goods?AS?g ???INNER?JOIN?tdb_goods_cates?AS?c?ON?g.cate_id?=?c.cate_id ???INNER?JOIN?tdb_goods_brands?AS?b?ON?g.brand_id?=?b.brand_idG;

— 查詢所有商品的詳細(xì)信息(通過左外連接實(shí)現(xiàn))

???SELECT?goods_id,goods_name,cate_name,brand_name,goods_price?FROM?tdb_goods?AS?g ???LEFT?JOIN?tdb_goods_cates?AS?c?ON?g.cate_id?=?c.cate_id ???LEFT?JOIN?tdb_goods_brands?AS?b?ON?g.brand_id?=?b.brand_idG;

— 查詢所有商品的詳細(xì)信息(通過右外連接實(shí)現(xiàn))

SELECT?goods_id,goods_name,cate_name,brand_name,goods_price?FROM?tdb_goods?AS?g ???RIGHT?JOIN?tdb_goods_cates?AS?c?ON?g.cate_id?=?c.cate_id ???RIGHT?JOIN?tdb_goods_brands?AS?b?ON?g.brand_id?=?b.brand_idG;

— 無限分類的數(shù)據(jù)表設(shè)計(jì)

CREATE?TABLE?tdb_goods_types( ?????type_id???SMALLINT?UNSIGNED?PRIMARY?KEY?AUTO_INCREMENT, ?????type_name?VARCHAR(20)?NOT?NULL, ?????parent_id?SMALLINT?UNSIGNED?NOT?NULL?DEFAULT?0 ??);? ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('家用電器',DEFAULT); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('電腦、辦公',DEFAULT); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('大家電',1); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('生活電器',1); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('平板電視',3); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('空調(diào)',3); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('電風(fēng)扇',4); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('飲水機(jī)',4); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('電腦整機(jī)',2); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('電腦配件',2); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('筆記本',9); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('超級(jí)本',9); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('游戲本',9); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('CPU',10); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('主機(jī)',10);

— 查找所有分類及其父類

??SELECT?s.type_id,s.type_name,p.type_name?FROM?tdb_goods_types?AS?s?LEFT?JOIN?tdb_goods_types?AS??p?ON?s.parent_id?=?p.type_id;

? ?

— 查找所有分類及其子類

??SELECT?p.type_id,p.type_name,s.type_name?FROM?tdb_goods_types?AS?p?LEFT?JOIN?tdb_goods_types?AS??s?ON?s.parent_id?=?p.type_id;

— 查找所有分類及其子類的數(shù)目

SELECT?p.type_id,p.type_name,count(s.type_name)?AS?children_count?FROM?tdb_goods_types?AS?p?LEFT?JOIN?tdb_goods_types?AS?s?ON?s.parent_id?=?p.type_id?GROUP?BY?p.type_name?ORDER?BY?p.type_id;

— 為tdb_goods_types添加child_count字段

ALTER?TABLE?tdb_goods_types?ADD?child_count?MEDIUMINT?UNSIGNED?NOT?NULL?DEFAULT?0;

— 將剛才查詢到的子類數(shù)量更新到tdb_goods_types數(shù)據(jù)表

UPDATE?tdb_goods_types?AS?t1?INNER?JOIN?(?SELECT?p.type_id,p.type_name,count(s.type_name)?AS?children_count?FROM?tdb_goods_types?AS?p? ????????????????????????????????????????????LEFT?JOIN?tdb_goods_types?AS?s?ON?s.parent_id?=?p.type_id? ????????????????????????????? ????????????????????????????????????????????GROUP?BY?p.type_name? ????????????????????????????????????????????ORDER?BY?p.type_id?)?AS?t2? ??ON??t1.type_id?=?t2.type_id? ??SET?t1.child_count?=?t2.children_count;

— 復(fù)制編號(hào)為12,20的兩條記錄

SELECT?*?FROM?tdb_goods?WHERE?goods_id?IN?(19,20);

— INSERT … SELECT實(shí)現(xiàn)復(fù)制

INSERT?tdb_goods(goods_name,cate_id,brand_id)?SELECT?goods_name,cate_id,brand_id?FROM?tdb_goods?WHERE?goods_id?IN?(19,20);

— 查找重復(fù)記錄

SELECT?goods_id,goods_name?FROM?tdb_goods?GROUP?BY?goods_name?HAVING?count(goods_name)?&gt;=?2;

— 刪除重復(fù)記錄

??DELETE?t1?FROM?tdb_goods?AS?t1?LEFT?JOIN?(SELECT?goods_id,goods_name?FROM?tdb_goods?GROUP?BY?goods_name?HAVING?count(goods_name)?&gt;=?2?)?AS?t2??ON?t1.goods_name?=?t2.goods_name??WHERE?t1.goods_id?&gt;?t2.goods_id;

【相關(guān)推薦：mysql視頻教程】

以上就是

? 版權(quán)聲明

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載。

THE END