MySQL之實(shí)用篇
MySQL之牛刀小試
子查詢是指出現(xiàn)在其他SQL語(yǔ)句內(nèi)的SELECT子句.
例如:
SELECT * FROM t1 WHERE column1 = (SELECT column2 FROM t2)
其中 SELECT * FRIN t1 稱為outerQuery
SELECT column2 FROM t2 稱為subQuery
注意:子查詢指嵌套在查詢內(nèi)部,且必須始終出現(xiàn)在圓括號(hào)內(nèi).
子查詢可以包含多個(gè)關(guān)鍵字或者條件,如 DISTINCT , GROUP BY, ORDER BY,函數(shù)等.
子查詢的外層查詢可以是SELECT , INSERT, UPDATE,SET 或DO.
子查詢可以返回標(biāo)量,一行,一列或子查詢.
下面是我創(chuàng)建的goods表結(jié)構(gòu)以及添加的一些數(shù)據(jù)
子查詢
查詢商品價(jià)格的平均值
SELECT AVG(goods_price) FROM goods
查詢商品價(jià)格的平均值并對(duì)值進(jìn)行四舍五入取兩位小數(shù)
SELECT ROUND(AVG(goods_price),2) FROM goods
子查詢 查詢出價(jià)格大于平均值得商品
SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > ANY (SELECT goods_price FROM goods WHERE goods_cate = '超級(jí)本') 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 = '超級(jí)本') ORDER BY goods_price DESC
- AVG,MAX,MIN、COUNT、SUM為聚合函數(shù)(平均值,最大值,最小值,行數(shù),求和)
- ROUND 是四舍五入 2代表保留兩位小數(shù)
- 用ANY,SOME或ALL修飾比較運(yùn)算符 (ANY,SOME 代表的滿足任意一個(gè)就可以,ALL代表必須全部滿足)
- [NOT] IN ,[NOT] EXISTS
多表插入
INSERT [INTO] table_name[(column_name,....)] SELECT ...
再創(chuàng)建一個(gè)新的表
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
創(chuàng)建數(shù)據(jù)表的同時(shí)將查詢結(jié)果寫入到數(shù)據(jù)表
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
再進(jìn)行剛剛的多表更新
UPDATE goods INNER JOIN goods_brands ON goods.brand_name = goods_brands.brand_name SET goods.brand_name = goods_brands.brand_id
連接
MySQL在SELECT 語(yǔ)句,多表更新,多表刪除語(yǔ)句中支持JOIN 操作
語(yǔ)法結(jié)構(gòu)
table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON conditional_expr
A表 + 連接類型{INNER JOIN | LEFT JOIN | RIGHT JOIN} + B表 ON + 連接的條件
數(shù)據(jù)表參照
table_reference
table_name [[AS] alias] | table_subquery [AS] alias
- 數(shù)據(jù)表可以使用table_name AS alias_name 或 table_name alias_name 賦予別名
- table_subquery可以作為子查詢使用在FROM子句中,這樣的子查詢必須為其賦予別名
連接類型
- INNER JOIN (內(nèi)連接) 在MySQL中,JOIN , CROSS JOIN 和 INNER JOIN 是等價(jià)的
- LEFT [OUTER] JOIN (左外連接)
- RIGHT [OUTER] JOIN (右外連接)
注意:使用ON關(guān)鍵字來(lái)設(shè)定連接條件,也可以使用WHERE來(lái)代替通常使用ON關(guān)鍵字來(lái)設(shè)定連接條件,使用WHERE關(guān)鍵字進(jìn)行結(jié)果集記錄的過(guò)濾
內(nèi)連接
for example:
SELECT goods_id, goods_name, cate_name FROM goods INNER JOIN goods_cates ON goods_cate = cate_id
外鏈接
- 如果使用內(nèi)連接查找的記錄在連接數(shù)據(jù)表中不存在,并且在WHERE子句中嘗試一下操作: column_name IS NULL 時(shí),如果column_name被定義為NOT NULL, MySQL將在找到符合連接條件的記錄后停止搜索更多的行
- A LEFT JOIN B join_condition
- 數(shù)據(jù)表B的結(jié)果集依賴數(shù)據(jù)表A
- 數(shù)據(jù)表A的結(jié)果集根據(jù)左連接條件依賴所有數(shù)據(jù)表(B表除外)
- 左外連接條件決定如何縮減數(shù)據(jù)表B(在沒(méi)有指定WHERE條件的情況下)
- 如果數(shù)據(jù)表A的魔調(diào)記錄符合WHERE條件,但是在數(shù)據(jù)表B不存在連接條件的記錄,將會(huì)生成一個(gè)所有列為空的額外的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表的兩個(gè)字段名稱以及類型修改一下
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
無(wú)限分類的數(shù)據(jù)表設(shè)計(jì)
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('空調(diào)',3);
INSERT goods_types(type_name,parent_id) VALUES('電風(fēng)扇',4);
INSERT goods_types(type_name,parent_id) VALUES('飲水機(jī)',4);
INSERT goods_types(type_name,parent_id) VALUES('電腦整機(jī)',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('超級(jí)本',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('主機(jī)',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;