子查詢

嵌套在 SQL 語句中的查詢語句稱為子查詢，可以嵌套多層。子查詢是 MySQL 中比較常用的查詢方法捂寿，通過子查詢可以實(shí)現(xiàn)多表查詢牡拇。子查詢可以在 SELECT魁瞪、UPDATE 和 DELETE 語句中使用。子查詢通常出現(xiàn)的位置是在 WHERE 或 HAVING 之后作為查詢條件的值惠呼。也可以寫在 from 后面當(dāng)作一張表导俘。

語句格式：

SELECT 查詢字段 FROM 表名,(子查詢) 子查詢別名 WHERE 條件字段 = (子查詢);

使用 MySQLWorkbench 執(zhí)行實(shí)例：

SELECT number,name,r.role_name FROM testrole,(SELECT * FROM role WHERE role_number = 101) r WHERE number = (SELECT role_number FROM role WHERE role_name = "A");

截屏2023-05-22 18.16.44.png

關(guān)聯(lián)查詢

同時(shí)查詢多張表的數(shù)據(jù)稱為關(guān)聯(lián)查詢，關(guān)聯(lián)查詢包括: 等值鏈接剔蹋、內(nèi)連接和外連接旅薄。

內(nèi)連接

內(nèi)連接只返回在連接過程中有連接匹配的記錄。根據(jù)連接條件泣崩，兩個(gè)表中都有對(duì)應(yīng)的數(shù)據(jù)存在的記錄少梁，才會(huì)返回。由于mysql默認(rèn)是內(nèi)連接矫付，所以 join 等同于 inner join凯沪。

語句格式：

SELECT 表別名.查詢字段,表別名.查詢字段 FROM 表名 AS 表別名 JOIN 表名 AS 表別名 ON 表別名.關(guān)聯(lián)字段 = 表別名.關(guān)聯(lián)字段;

使用 MySQLWorkbench 執(zhí)行實(shí)例：

SELECT c.category_name,g.goods_name FROM category AS c JOIN goods AS g ON c.category_id = g.category_id;

截屏2023-05-24 18.01.30.png

等值連接

內(nèi)連接與等值連接效果是相同的，執(zhí)行效率也相同买优，只是書寫方式不一樣著洼，內(nèi)連接是由SQL 1999規(guī)則定的書寫方式。

語句格式：

SELECT 表別名.查詢字段名,表別名.查詢字段名 FROM 表名 AS 表別名,表名 AS 表別名 WHERE 表別名.關(guān)聯(lián)字段 = 表別名.關(guān)聯(lián)字段;

使用 MySQLWorkbench 執(zhí)行實(shí)例：

SELECT c.category_name,g.goods_name FROM category AS c,goods AS g WHERE c.category_id = g.category_id;

截屏2023-05-24 18.01.30.png

外連接

外連接分為左而叼、右外連接以在 JOIN 關(guān)鍵字前添加 LEFT身笤、RIGHT 區(qū)分，左外連接時(shí)葵陵，JOIN 關(guān)鍵字左側(cè)的表顯示所有數(shù)據(jù)液荸，右側(cè)的表顯示交集數(shù)據(jù)；右外連接時(shí)脱篙，JOIN 關(guān)鍵字右側(cè)的表顯示所有數(shù)據(jù)娇钱，左側(cè)的表顯示交集數(shù)據(jù)伤柄。

左外連接語句格式：

SELECT 表別名.查詢字段名,表別名.查詢字段名 FROM 表名 AS 表別名 LEFT JOIN 表名 AS 表別名 ON 表別名.關(guān)聯(lián)字段 = 表別名.關(guān)聯(lián)字段;

使用 MySQLWorkbench 執(zhí)行實(shí)例：

SELECT c.category_name,g.goods_name FROM category AS c LEFT JOIN goods AS g ON c.category_id = g.category_id;

截屏2023-05-25 09.43.16.png

右外連接語句格式：

SELECT 表別名.查詢字段名,表別名.查詢字段名 FROM 表名 AS 表別名 RIGHT JOIN 表名 AS 表別名 ON 表別名.關(guān)聯(lián)字段 = 表別名.關(guān)聯(lián)字段;

使用 MySQLWorkbench 執(zhí)行實(shí)例：

SELECT c.category_name,g.goods_name FROM category AS c RIGHT JOIN goods AS g ON c.category_id = g.category_id;

截屏2023-05-25 09.53.02.png

笛卡爾積

關(guān)聯(lián)查詢時(shí)，如果不寫關(guān)聯(lián)關(guān)系文搂，則查詢結(jié)果為兩張表的乘積适刀，這個(gè)乘積稱為笛卡爾積（注：笛卡爾積本身沒有錯(cuò)誤，笛卡爾積是一種數(shù)學(xué)運(yùn)算）煤蹭。但如果把這種查詢結(jié)果當(dāng)作最終結(jié)果則是錯(cuò)誤的笔喉，因?yàn)楫a(chǎn)生了重復(fù)且錯(cuò)誤的數(shù)據(jù)。

表設(shè)計(jì)的關(guān)聯(lián)關(guān)系

表設(shè)計(jì)的關(guān)聯(lián)關(guān)系分為：一對(duì)一硝皂、一對(duì)多常挚、多對(duì)多、自關(guān)聯(lián)稽物。

一對(duì)一關(guān)系

若有 A 表 和 B 表兩張表奄毡，A 表中一條數(shù)據(jù)對(duì)應(yīng) B 表中的一條數(shù)據(jù)，這種關(guān)系稱為一對(duì)一關(guān)系贝或。

建立一對(duì)一關(guān)系：在從（主從關(guān)系）表中添加一個(gè)外鍵字段值指向主表的主鍵字段值吼过。

一對(duì)多關(guān)系

若有 A 表 和 B 表兩張表，A 表中一條數(shù)據(jù)對(duì)應(yīng) B 表中的多條數(shù)據(jù)咪奖，而 B 表中的一條數(shù)據(jù)只對(duì)應(yīng) A 表中的一條數(shù)據(jù)那先，這種關(guān)系稱為一對(duì)多關(guān)系。

建立一對(duì)多關(guān)系：一對(duì)多的兩張表赡艰，在多的表里添加外鍵記錄另一個(gè)表主鍵值售淡。

多對(duì)多關(guān)系

若有 A 表 和 B 表兩張表，A 表中一條數(shù)據(jù)對(duì)應(yīng) B 表中的多條數(shù)據(jù)慷垮，同時(shí) B 表中的一條數(shù)據(jù)對(duì)應(yīng) A 表中的多條數(shù)據(jù)揖闸，這種關(guān)系稱為多對(duì)多關(guān)系。例如權(quán)限管理表設(shè)計(jì)的經(jīng)典五張表：用戶表料身、角色表汤纸、權(quán)限表、用戶與角色關(guān)系表芹血、角色與權(quán)限關(guān)系表贮泞。

建立對(duì)對(duì)多關(guān)系：需要?jiǎng)?chuàng)建單獨(dú)的關(guān)系表，關(guān)系表中兩個(gè)外鍵字段保存兩表的主鍵值幔烛。

權(quán)限管理表設(shè)計(jì)的經(jīng)典五張表實(shí)例：

截屏2023-05-28 09.14.50.png

SQL 如下：

/*user_role指定外鍵*/
ALTER TABLE user_role add constraint user_foreign_key_name FOREIGN KEY(user_foreign_key) REFERENCES user(user_id);
ALTER TABLE user_role add constraint role_foreign_key_name_one FOREIGN KEY(role_foreign_key) REFERENCES role(role_id);
/*role_power指定外鍵*/
ALTER TABLE role_power add constraint role_foreign_key_name_two FOREIGN KEY(role_foreign_key) REFERENCES role(role_id);
ALTER TABLE role_power add constraint power_foreign_key_name FOREIGN KEY(power_foreign_key) REFERENCES power(power_id);
/*子查詢SQL*/
SELECT rp.user_name,rp.rolecol_name,pr.power_name FROM 
(SELECT re.user_name,re.rolecol_name,rp.power_foreign_key FROM 
(SELECT ur1.user_name,re.rolecol_name,re.role_id FROM 
(SELECT ur.role_foreign_key,us.user_name FROM user AS us JOIN user_role AS ur ON us.user_id = ur.user_foreign_key WHERE us.user_name = '小王') ur1 
JOIN role AS re ON ur1.role_foreign_key = re.role_id) AS re 
JOIN role_power AS rp ON re.role_id = rp.role_foreign_key) AS rp JOIN power AS pr ON rp.power_foreign_key = pr.power_id;
/*關(guān)聯(lián)查詢SQL*/
SELECT user.user_name,role.rolecol_name,power.power_name FROM user 
                   JOIN user_role ON user.user_id = user_role.user_foreign_key 
                   JOIN role ON user_role.role_foreign_key = role.role_id
                   JOIN role_power ON role.role_id = role_power.role_foreign_key
                   JOIN power ON role_power.power_foreign_key = power.power_id
                   WHERE user.user_name = '小王';

當(dāng)查詢用戶名為小王時(shí)啃擦，小王同時(shí)扮演了兩種角色，當(dāng)我們?yōu)橛脩粼黾咏巧珪r(shí)饿悬，只需要在 用戶-角色關(guān)系表中加入數(shù)據(jù)令蛉。 SQL 執(zhí)行結(jié)果：

截屏2023-05-28 10.56.30.png

當(dāng)查詢用戶名為小張時(shí)，小張只扮演了一種角色，但這個(gè)角色同時(shí)被分配了兩種權(quán)限珠叔，當(dāng)我們?yōu)榻巧峙錂?quán)限時(shí)蝎宇，只需要在 角色-權(quán)限關(guān)系表中加入數(shù)據(jù)。 SQL 執(zhí)行結(jié)果：

截屏2023-05-28 11.02.32.png

當(dāng)查詢用戶名為小高時(shí)祷安，小高只扮演了一種角色姥芥，這個(gè)角色也只有一種權(quán)限。SQL 執(zhí)行結(jié)果：

截屏2023-05-28 11.04.40.png

自關(guān)聯(lián)關(guān)系

在表中有一個(gè)外鍵字段指向自己表的主鍵值汇鞭，這種關(guān)系稱為自關(guān)聯(lián)凉唐。自關(guān)聯(lián)多用于需要保存層級(jí)關(guān)系，但不確定有多少層時(shí)使用虱咧。例如在地區(qū)表中使用：

截屏2023-05-27 11.21.29.png