子查詢是指出現在其他SQL語句內的SELECT子句. 例如: SELECT * FROM t1 WHERE column1 = (SELECT column2 FROM t2) 其中 SELECT * FRIN t1 稱為outerQuery SELECT column2 FROM t2 稱為subQuery 注意:子查詢指嵌套在查詢內部,且必須始終出現在圓括號內. 子查詢可以包含多個關鍵字或者條件,如 DISTINCT , GROUP BY, ORDER BY,函數等. 子查詢的外層查詢可以是SELECT , INSERT, UPDATE,SET 或DO. 子查詢可以返回標量,一行,一列或子查詢.
###下面是我創建的goods表結構以及添加的一些數據
###子查詢 ######查詢商品價格的平均值
SELECT AVG(goods_price) FROM goods
復制代碼######查詢商品價格的平均值并對值進行四舍五入取兩位小數 SELECT ROUND(AVG(goods_price),2) FROM goods
######子查詢 查詢出價格大于平均值得商品 SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > ANY (SELECT goods_price FROM goods WHERE goods_cate = '超級本') ORDER BY goods_price DESC SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price NOT IN (SELECT goods_price FROM goods WHERE goods_cate = '超級本') ORDER BY goods_price DESC
- AVG,MAX,MIN、COUNT、SUM為聚合函數(平均值,最大值,最小值,行數,求和)
- ROUND 是四舍五入 2代表保留兩位小數
- 用ANY,SOME或ALL修飾比較運算符 (ANY,SOME 代表的滿足任意一個就可以,ALL代表必須全部滿足)
- [NOT] IN ,[NOT] EXISTS
多表插入
INSERT [INTO] table_name[(column_name,....)] SELECT ...
再創建一個新的表
CREATE TABLE IF NOT EXISTS goods_cates(cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, cate_name TEXT NOT NULL)
復制代碼######查詢商品的分類 SELECT goods_cate FROM goods GROUP BY goods_cate ######插入所有商品的分類到goods_cate分類的表中 INSERT INTO goods_cates(cate_name) SELECT goods_cate FROM goods GROUP BY goods_cate ###多表更新 ######UPDATE table_references SET column_name1 = {expr1 | DEFAULT} [, column_name2 = {expr2 | DEFAULT}] ...... [WHERE where_condition]
for example:
UPDATE goods INNER JOIN goods_cates ON goods_cate = cate_name SET goods_cate = cate_id
復制代碼CREATE ...SELECT
######創建數據表的同時將查詢結果寫入到數據表 CREATE TABLE [IF NOT EXISTS] table_name [(create_definition,....)] select_statement for example:
CREATE TABLE goods_brands (brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, brand_name TEXT NOT NULL) SELECT brand_name FROM goods GROUP BY brand_name
復制代碼######再進行剛剛的多表更新 UPDATE goods INNER JOIN goods_brands ON goods.brand_name = goods_brands.brand_name SET goods.brand_name = goods_brands.brand_id ###連接 ######MySQL在SELECT 語句,多表更新,多表刪除語句中支持JOIN 操作 #####語法結構 table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON conditional_expr
A表 + 連接類型{INNER JOIN | LEFT JOIN | RIGHT JOIN} + B表 ON + 連接的條件
#####數據表參照 table_reference
table_name [[AS] alias] | table_subquery [AS] alias
復制代碼- 數據表可以使用table_name AS alias_name 或 table_name alias_name 賦予別名
- table_subquery可以作為子查詢使用在FROM子句中,這樣的子查詢必須為其賦予別名 #####連接類型
- INNER JOIN (內連接) 在MySQL中,JOIN , CROSS JOIN 和 INNER JOIN 是等價的
- LEFT [OUTER] JOIN (左外連接)
- RIGHT [OUTER] JOIN (右外連接) ######注意:使用ON關鍵字來設定連接條件,也可以使用WHERE來代替通常使用ON關鍵字來設定連接條件,使用WHERE關鍵字進行結果集記錄的過濾 #####內連接
for example:
SELECT goods_id, goods_name, cate_name FROM goods INNER JOIN goods_cates ON goods_cate = cate_id
復制代碼###外鏈接
- 如果使用內連接查找的記錄在連接數據表中不存在,并且在WHERE子句中嘗試一下操作: column_name IS NULL 時,如果column_name被定義為NOT NULL, MySQL將在找到符合連接條件的記錄后停止搜索更多的行
- A LEFT JOIN B join_condition
- 數據表B的結果集依賴數據表A
- 數據表A的結果集根據左連接條件依賴所有數據表(B表除外)
- 左外連接條件決定如何縮減數據表B(在沒有指定WHERE條件的情況下)
- 如果數據表A的魔調記錄符合WHERE條件,但是在數據表B不存在連接條件的記錄,將會生成一個所有列為空的額外的B行 #####左外連接
for example:
SELECT goods_id, goods_name, cate_name FROM goods LEFT JOIN goods_cates ON goods_cate = cate_id
復制代碼#####右外連接
for example:
SELECT goods_id, goods_name, cate_name FROM goods RIGHT
復制代碼######多表連接 ######我們先把goods表的兩個字段名稱以及類型修改一下 ALTER TABLE goods CHANGE brand_name brand_id SMALLINT NOT NULL UNSIGNED ALTER TABLE goods CHANGE goods_cate cate_id SMALLINT NOT NULL UNSIGNED
for example :
SELECT goods_id,goods_name, goods_price, cate_name ,brand_name FROM goods AS good
INNER JOIN goods_cates AS cate ON good.cate_id = cate.cate_id
INNER JOIN goods_brands AS brand ON good.brand_id = brand.brand_id
復制代碼###無限分類的數據表設計 CREATE TABLE goods_types ( type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMEN type_name TEXT NOT NULL, parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 )
-
INSERT goods_types(type_name,parent_id) VALUES('家用電器',DEFAULT);
-
INSERT goods_types(type_name,parent_id) VALUES('電腦、辦公',DEFAULT);
-
INSERT goods_types(type_name,parent_id) VALUES('大家電',1);
-
INSERT goods_types(type_name,parent_id) VALUES('生活電器',1);
-
INSERT goods_types(type_name,parent_id) VALUES('平板電視',3);
-
INSERT goods_types(type_name,parent_id) VALUES('空調',3);
-
INSERT goods_types(type_name,parent_id) VALUES('電風扇',4);
-
INSERT goods_types(type_name,parent_id) VALUES('飲水機',4);
-
INSERT goods_types(type_name,parent_id) VALUES('電腦整機',2);
-
INSERT goods_types(type_name,parent_id) VALUES('電腦配件',2);
-
INSERT goods_types(type_name,parent_id) VALUES('筆記本',9);
-
INSERT goods_types(type_name,parent_id) VALUES('超級本',9);
-
INSERT goods_types(type_name,parent_id) VALUES('游戲本',9);
-
INSERT goods_types(type_name,parent_id) VALUES('CPU',10);
-
INSERT goods_types(type_name,parent_id) VALUES('主機',10);
######查詢父類下邊的子類 SELECT s.type_id,s.type_name,p.type_name FROM goods_types AS s LEFT JOIN goods_types AS p ON s.parent_id = p.type_id ######查父類下的父類的子類 SELECT p.type_id,p.type_name,s.type_name FROM goods_types AS p LEFT JOIN goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id
SELECT p.type_id,p.type_name,s.type_name child_count FROM goods_types AS p LEFT JOIN
goods_types AS s ON s.parent_id = p.type_id
復制代碼###多表刪除 DELETE table_name [.] [table_name[.]]..... FROM table_references [WHERE where_condition]
for example:
SELECT goods_id,goods_name FROM goods GROUP BY goods_name HAVING count(goods_name) >= 2;DELETE t1 FROM goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM 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;
復制代碼)
)
