【Mysql進階技巧(1)】MySQL的多表關聯與自連接 · Mysql的進階與高可用架構

[toc] ## 自連接 ### 測試數據準備 ```sql CREATE TABLE `t2` ( `id` int(11) NOT NULL, `gid` char(1) DEFAULT NULL, `col1` int(11) DEFAULT NULL, `col2` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; insert into t2 values (1,'A',31,6), (2,'B',25,83), (3,'C',76,21), (4,'D',63,56), (5,'E',3,17), (6,'A',29,97), (7,'B',88,63), (8,'C',16,22), (9,'D',25,43), (10,'E',45,28), (11,'A',2,78), (12,'B',30,79), (13,'C',96,73), (14,'D',37,40), (15,'E',14,86), (16,'A',32,67), (17,'B',84,38), (18,'C',27,9), (19,'D',31,21), (20,'E',80,63), (21,'A',89,9), (22,'B',15,22), (23,'C',46,84), (24,'D',54,79), (25,'E',85,64), (26,'A',87,13), (27,'B',40,45), (28,'C',34,90), (29,'D',63,8), (30,'E',66,40), (31,'A',83,49), (32,'B',4,90), (33,'C',81,7), (34,'D',11,12), (35,'E',85,10), (36,'A',39,75), (37,'B',22,39), (38,'C',76,67), (39,'D',20,11), (40,'E',81,36); ``` ### 通過自連接查詢每組col2最大的值; ```sql -- 方法1: select * from t2 as a where not exists (select 1 from t2 where gid=a.gid and col2>a.col2); -- 1. select 1 from t2 where gid=a.gid and col2>a.col2 : select就進入了隱式迭代,同組中比當前col2大的就輸出1; -- 2. 然后not exists來判斷是否存在比當前col2大的,如果不存在就返回true;返回true就輸出當前col2這一列; -- 3. 這里的exists與not exists是判斷語句,返回的是true or false; -- 方法2: select * from (select * from t2 order by gid,col2 desc) as t group by gid; -- t2按照gid和col2來降序排列,然后group分組,分組就取的是frist row,而frist row就是最大的值; -- 乍看之下貌似不用自連接也可以搞定,但是group by分組是不能放在order by之后的,否則就會報錯; ``` ### 通過自連接查詢每組col2最大的三個值; ```sql select * from t2 as a where 3 > (select count(*) from t2 where gid=a.gid and col2>a.col2) order by a.gid,a.col2 desc; -- 比當前col2大的值如果小于三條就輸出(注意必須是小于三條,如果等于三條就代表已經有了三條),然后輸出后排序; ``` > 上面兩條自連接sql都比較難理解,但只要換個角度,其實理解起來也很容易,首先在mysql中要把select翻譯為輸出,并且要滿足where以后才輸出;輸出以后再分組,分組以后才輪到排序,排序之后才輪到取幾個 ## JOIN ### [inner] join,left join,right join; > 通過join,mysql可以做到集合中的求交集,并集,差集等需求,但比起類似redis等集合來說,效率差了不止一個級別了; **數據準備** ```sql -- 創建數據表 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 )ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COMMENT='測試商品表'; -- 寫入記錄 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); ``` ### 語法 ```sql table1 {[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER]JOIN} table2 ON conditional_expr ... {[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER]JOIN} tablen ON conditional_expr ``` JOIN 按照功能大致分為如下三類： + INNER JOIN（內連接,或等值連接,）：取得兩個表中存在連接匹配關系的記錄。 + LEFT JOIN（左連接）：取得左表（table1）完全記錄，然后再去匹配(table2),如果匹配不到以NULL的形式返回table2的字段值。 + RIGHT JOIN（右連接）：與 LEFT JOIN 相反。 ### 關聯更新 ```sql -- ↓↓把單表更新的table換成了關聯在一起的table UPDATE tdb_goods AS g JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name JOIN tdb_goods_cates AS c ON g.goods_cate = c.cate_name SET g.brand_name = b.brand_id, g.goods_cate = c.cate_id; -- ↓↓由于把原來的品牌名和分類名更換為了id,所以相應的字段名稱和類型要有所改變 ALTER TABLE tdb_goods CHANGE brand_name brand_id TINYINT NOT NULL DEFAULT 0, CHANGE goods_cate cate_id TINYINT NOT NULL DEFAULT 0; DESC tdb_goods; ``` ### 關聯查詢 ```sql -- 內連接 SELECT * FROM tdb_goods AS g JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id; -- 左連接 SELECT * 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_id; ``` ### 關聯刪除 ```sql -- 查找出重復記錄; SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2; -- 進行刪除 DELETE t1 FROM tdb_goods AS t1 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; -- 注意,單表的刪除在delete后面不用加上表名,但多表一定要加,否則會報語法錯誤; -- 如何理解這條sql語句? 首先把t1和t2關聯一起;然后把t1全刪除;也就是說把符合另外一張表關聯條件的本表給刪掉; ``` ### Cross join [笛卡爾] 交叉連接，得到的結果是兩個表的乘積 ```sql select * from tdb_goods,tdb_goods_brands; -- tdb_goods表23條數據,tdb_goods_brands有10條數據,笛卡爾以后就出現23*10條數據; select * from tdb_goods as g join tdb_goods_brands as b; -- join在沒有on條件的時候也是笛卡爾乘積; ``` ### Full join [union] - [ UNION和UNION ALL之間的區別](https://blog.csdn.net/allway2/article/details/109435605) mysql里面沒有Full join,只有union;如果要使用union的話,被union的表的結構要一樣才能并在一起; ```sql -- 先查出1101的評論,再查出1101所關注的人的評論; SELECT * FROM `comment` WHERE user_id = 1101 UNION SELECT * FROM `comment` WHERE user_id IN (select follow_user_id from follow where user_id = 1101); ``` MySQL中的Union運算符允許我們將來自多個SELECT查詢的兩個或多個結果組合到單個結果集中。**它具有默認功能，可從表中刪除重復的行**。此運算符語法始終使用第一個SELECT語句中的列名作為輸出的列名。 `UNION ALL`運算符組合來自多個SELECT查詢的兩個或多個結果，并將所有記錄返回到單個結果集中。**它不會從SELECT語句的輸出中刪除重復的行。** `UNION ALL`的性能比`UNION`要高，因為`UNION`還要查找和刪除重復的記錄。 ### 關于join的外鍵約束以往做PHP+MYSQL的web應用時,從未用過外鍵,但即使如此,外鍵還是很有必要的,它能是多表之間的關聯更嚴格,能夠達到一致性的需求; 如果不用外鍵約束的話,在多表關聯的應用場景中,我們插入一條數據,該條數據只要滿足語法規范既可插入,但如果使用了外鍵,該語句還要同時滿足當前外鍵在關聯的表中是否存在; 使用外鍵要滿足以下條件: 1. 兩張表必須都是InnoDB表，并且它們沒有臨時表。 1. 外鍵列和參照列必須具有相似的數據類型.**其中數字的長度及是否有符號位必須相同;而字符的長度則可以不同** 1. 外鍵列和參照列必須創建索引.如果外鍵列不存在索引的話,mysql將自動創建索引 **語法** ```sql [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION ``` 如果子表試圖創建一個在父表中不存在的外鍵值，InnoDB會拒絕任何INSERT或UPDATE操作。如果父表試圖UPDATE或者DELETE任何子表中存在或匹配的外鍵值，最終動作取決于外鍵約束定義中的ON UPDATE和ON DELETE選項。InnoDB支持5種不同的動作，如果沒有指定ON DELETE或者ON UPDATE，默認的動作為RESTRICT: 1. CASCADE: 從父表中刪除或更新對應的行，同時自動的刪除或更新子表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。 2. SET NULL: 從父表中刪除或更新對應的行，同時將子表中的外鍵列設為空。注意，這些在外鍵列沒有被設為NOT NULL時才有效。ON DELETE SET NULL和ON UPDATE SET NULL都被InnoDB所支持。 3. NO ACTION: InnoDB拒絕刪除或者更新父表。 4. RESTRICT: 拒絕刪除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE選項的效果是一樣的。 5. SET DEFAULT: InnoDB目前不支持。 > 上文中的父表是指被參照的表; 外鍵約束使用最多的情況無外乎： 1. 父表更新時子表也更新，父表刪除時如果子表有匹配的項，刪除失敗；(在外鍵定義中，我們使用ON UPDATE CASCADE ON DELETE RESTRICT) 1. 父表更新時子表也更新，父表刪除時子表匹配的項也刪除。(使用ON UPDATE CASCADE ON DELETE CASCADE) InnoDB允許你使用ALTER TABLE在一個已經存在的表上增加一個新的外鍵: ```sql ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] ``` 也支持 ```sql ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; ``` **Example** ```sql CREATE TABLE `test1` ( `goods_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `goods_name` varchar(150) NOT NULL, `brand_id` smallint(5) unsigned NOT NULL DEFAULT '0', `goods_price` decimal(15,3) unsigned NOT NULL DEFAULT '0.000', PRIMARY KEY (`goods_id`), KEY `brand_id` (`brand_id`), FOREIGN KEY (`brand_id`) REFERENCES `test2` (`brand_id`) -- test1的brand_id必須和test2一樣,包括類型,長度,是否有符號,才能創建外鍵; -- 創建完之后FOREIGN KEY...這sql就會自動變為:CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `test2` (`brand_id`) -- 如果不加任何ON DELETE reference_option語句的話,默認就是NO ACTION,也就是說,父表test2更新和刪除,只準增加; -- 如果加上ON DELETE CANSCADE和ON UPDATE CANSCADE的話,test2刪除和更新都會影響到test1; ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='索引測試test1'; CREATE TABLE `test2` ( `brand_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `brand_name` varchar(40) NOT NULL, PRIMARY KEY (`brand_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='索引測試test2'; ``` ```sql mysql> insert into test1(goods_name,brand_id) values('i phone 1',6); 1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`test1`, CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `test2` (`brand_id`)) -- 沒有參照不能插入 mysql> delete from test2 where brand_id=1; 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`test1`, CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `test2` (`brand_id`)) -- NO ACTION,凡是test1已經關聯上的test2的row都不能被刪除;保持數據的一致性;沒關聯上可以被刪除,雖然是no action; ``` ## 自連接+join 實現一級分類 > 表的設計是無限極的設計方式,但mysql搞不了遞歸,只能實現一級; **數據準備** ```sql 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 )ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='無限極分類表';; 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); ``` **實現分類** ```sql SELECT t1.type_id, t1.type_name AS parent_name, t2.type_name, t2.parent_id FROM `tdb_goods_types` AS t1 JOIN tdb_goods_types AS t2 ON t1.type_id = t2.parent_id; ```