mysql子查詢語句是指在另一個查詢語句中的SELECT子句，例句如“SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);”。
本文操作環境：Windows7系統、mysql5.7版、Dell G3電腦。
mysql子查詢語句是什么？
mysql 子查詢語句：
1.子查詢是指在另一個查詢語句中的SELECT子句。
? 例句：
??SELECT?*?FROM?t1?WHERE?column1?=?(SELECT?column1?FROM?t2);
? 其中，SELECT * FROM t1 …稱為Outer Query[外查詢](或者Outer Statement),
? SELECT column1 FROM t2 稱為Sub Query[子查詢]。
? 所以，我們說子查詢是嵌套在外查詢內部。而事實上它有可能在子查詢內部再嵌套子查詢。
? 子查詢必須出現在圓括號之間。
??
? 行級子查詢
??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);
??
? 行級子查詢的返回結果最多為一行。
? 優化子查詢
? — 創建數據表
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英寸筆記本電腦','筆記本','聯想','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英寸超極本','超級本','聯想','4999',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('U330P?13.3英寸超極本','超級本','聯想','4299',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('SVP13226SCB?13.3英寸觸控超極本','超級本','索尼','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英寸一體電腦?','臺式機','聯想','3499',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('Vostro?3800-R1206?臺式電腦','臺式機','戴爾','2899',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('iMac?ME086CH/A?21.5英寸一體電腦','臺式機','蘋果','9188',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('AT7-7414LP?臺式電腦?（i5-3450四核?4G?500G?2G獨顯?DVD?鍵鼠?Linux?）','臺式機','宏碁','3699',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('Z220SFF?F4F06PA工作站','服務器/工作站','惠普','4288',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('PowerEdge?T110?II服務器','服務器/工作站','戴爾','5388',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('Mac?Pro?MD878CH/A?專業級臺式電腦','服務器/工作站','蘋果','28888',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('?HMZ-T3W?頭戴顯示設備','筆記本配件','索尼','6999',DEFAULT,DEFAULT); ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('商務雙肩背包','筆記本配件','索尼','99',DEFAULT,DEFAULT); ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('X3250?M4機架式服務器?2583i14','服務器/工作站','IBM','6888',DEFAULT,DEFAULT); ? ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('玄龍精英版?筆記本散熱器','筆記本配件','九州風神','',DEFAULT,DEFAULT); ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('?HMZ-T3W?頭戴顯示設備','筆記本配件','索尼','6999',DEFAULT,DEFAULT); ?INSERT?tdb_goods?(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)?VALUES('商務雙肩背包','筆記本配件','索尼','99',DEFAULT,DEFAULT);
— 求所有電腦產品的平均價格,并且保留兩位小數，AVG,MAX,MIN、COUNT、SUM為聚合函數
SELECT?ROUND(AVG(goods_price),2)?AS?avg_price?FROM?tdb_goods;
— 查詢所有價格大于平均價格的商品，并且按價格降序排序
SELECT?goods_id,goods_name,goods_price?FROM?tdb_goods?WHERE?goods_price?>?5845.10?ORDER?BY?goods_price?DESC;
??
— 使用子查詢來實現
SELECT?goods_id,goods_name,goods_price?FROM?tdb_goods? ??WHERE?goods_price?>?(SELECT?ROUND(AVG(goods_price),2)?AS?avg_price?FROM?tdb_goods)? ??ORDER?BY?goods_price?DESC;
— 查詢類型為“超記本”的商品價格
?
SELECT?goods_price?FROM?tdb_goods?WHERE?goods_cate?=?'超級本';
— 查詢價格大于或等于”超級本”價格的商品，并且按價格降序排列
?
SELECT?goods_id,goods_name,goods_price?FROM?tdb_goods? ???WHERE?goods_price?=?ANY(SELECT?goods_price?FROM?tdb_goods?WHERE?goods_cate?=?'超級本') ???ORDER?BY?goods_price?DESC;
? ?
— = ANY 或 = SOME 等價于 IN
SELECT?goods_id,goods_name,goods_price?FROM?tdb_goods? ???WHERE?goods_price?IN?(SELECT?goods_price?FROM?tdb_goods?WHERE?goods_cate?=?'超級本') ???ORDER?BY?goods_price?DESC;
— 創建“商品分類”表
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;
— 將分組結果寫入到tdb_goods_cates數據表
?INSERT?tdb_goods_cates?(cate_name)?SELECT?goods_cate?FROM?tdb_goods?GROUP?BY?goods_cate;
— 通過tdb_goods_cates數據表來更新tdb_goods表
UPDATE?tdb_goods?INNER?JOIN?tdb_goods_cates?ON?goods_cate?=?cate_name? ??SET?goods_cate?=?cate_id?;
— 通過CREATE…SELECT來創建數據表并且同時寫入記錄
?
--?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數據表來更新tdb_goods數據表(錯誤)
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的數據表結構
DESC?tdb_goods;
— 通過ALTER TABLE語句修改數據表結構
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('路由器'),('交換機'),('網卡'); ???INSERT?tdb_goods_brands(brand_name)?VALUES('海爾'),('清華同方'),('神舟');
— 在tdb_goods數據表寫入任意記錄
INSERT?tdb_goods(goods_name,cate_id,brand_id,goods_price)?VALUES('?LaserJet?Pro?P1606dn?黑白激光打印機','12','4','1849');
— 查詢所有商品的詳細信息(通過內連接實現)
?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;
— 查詢所有商品的詳細信息(通過左外連接實現)
???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;
— 查詢所有商品的詳細信息(通過右外連接實現)
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;
— 無限分類的數據表設計
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('空調',3); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('電風扇',4); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('飲水機',4); ??INSERT?tdb_goods_types(type_name,parent_id)?VALUES('電腦整機',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('超級本',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('主機',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;
— 查找所有分類及其子類的數目
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;
— 將剛才查詢到的子類數量更新到tdb_goods_types數據表
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;
— 復制編號為12,20的兩條記錄
SELECT?*?FROM?tdb_goods?WHERE?goods_id?IN?(19,20);
— INSERT … SELECT實現復制
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);
— 查找重復記錄
SELECT?goods_id,goods_name?FROM?tdb_goods?GROUP?BY?goods_name?HAVING?count(goods_name)?>=?2;
— 刪除重復記錄
??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)?>=?2?)?AS?t2??ON?t1.goods_name?=?t2.goods_name??WHERE?t1.goods_id?>?t2.goods_id;
【相關推薦：mysql視頻教程】
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
喜歡就支持一下吧
相關推薦
