MySQL數(shù)據(jù)庫基礎(chǔ)（三）：多表查詢，子查詢，開窗函數(shù)
十一、多表查詢（重點(diǎn)、難點(diǎn)）
表與表之間的關(guān)系
在SQL語句中，數(shù)據(jù)表與數(shù)據(jù)表之間，如果存在關(guān)系，一般一共有3種情況：
① 一對(duì)一關(guān)系（高級(jí)）
比如有A、B兩張表，A表中的每一條數(shù)據(jù)，在B表中有一條唯一的數(shù)據(jù)與之對(duì)應(yīng)。
用戶表user
用戶詳情表user_items
我們把用戶表與用戶詳情表之間的關(guān)系就稱之為一對(duì)一關(guān)系。
② 一對(duì)多關(guān)系（重點(diǎn)）
比如有A、B兩張表，A表中的每一條數(shù)據(jù)，在B表中都有多條數(shù)據(jù)與之對(duì)應(yīng)，我們把這種關(guān)系就稱之為一對(duì)多關(guān)系?
產(chǎn)品分類表
產(chǎn)品信息表
我們把產(chǎn)品分類表與產(chǎn)品表之間的關(guān)系就稱之為一對(duì)多關(guān)系。
③ 多對(duì)多關(guān)系（高級(jí)）
用戶表
權(quán)限表
雖然從以上圖解來看，兩者之間好像沒有任何聯(lián)系，但是兩者之間其實(shí)是有關(guān)系的，這種關(guān)系需要通過一張臨時(shí)表進(jìn)行呈現(xiàn)。
每個(gè)用戶，應(yīng)該有對(duì)應(yīng)的權(quán)限，admin賬號(hào)可以做增刪改查，itheima賬號(hào)可以做查詢
反過來
每個(gè)權(quán)限都應(yīng)該對(duì)應(yīng)多個(gè)用戶，查詢權(quán)限 => admin/itheima
中間表 ：用戶_權(quán)限表
交叉連接(了解)
沒有意義，但是它是所有連接的基礎(chǔ)。其功能就是將表1和表2中的每一條數(shù)據(jù)進(jìn)行連接。
結(jié)果：
字段數(shù) = 表1字段 + 表2的字段
記錄數(shù) = 表1中的總數(shù)量 * 表2中的總數(shù)量（笛卡爾積）
1、內(nèi)連接
☆ 連接查詢的介紹
連接查詢可以實(shí)現(xiàn)多個(gè)表的查詢，當(dāng)查詢的字段數(shù)據(jù)來自不同的表就可以使用連接查詢來完成。 連接查詢可以分為:
內(nèi)連接查詢
左外連接查詢
右外連接查詢
自連接查詢（自己查詢自己）
☆ 內(nèi)連接查詢
查詢兩個(gè)表中符合條件的共有記錄
內(nèi)連接查詢語法格式:
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
說明:
inner join 就是內(nèi)連接查詢關(guān)鍵字
on 就是連接查詢條件
例1：使用內(nèi)連接查詢學(xué)生表與班級(jí)表:
☆ 小結(jié)
內(nèi)連接使用inner join .. on .., on 表示兩個(gè)表的連接查詢條件
內(nèi)連接根據(jù)連接查詢條件取出兩個(gè)表的 “交集”
2、左外連接
☆ 左連接查詢
以左表為主根據(jù)條件查詢右表數(shù)據(jù)，如果根據(jù)條件查詢右表數(shù)據(jù)不存在使用null值填充
左連接查詢語法格式:
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
說明:
left join 就是左連接查詢關(guān)鍵字
on 就是連接查詢條件
表1 是左表
表2 是右表
例1：使用左連接查詢學(xué)生表與班級(jí)表:
☆ 小結(jié)
左連接使用left join .. on .., on 表示兩個(gè)表的連接查詢條件
左連接以左表為主根據(jù)條件查詢右表數(shù)據(jù)，右表數(shù)據(jù)不存在使用null值填充。
3、右外連接
☆ 右連接查詢
以右表為主根據(jù)條件查詢左表數(shù)據(jù)，如果根據(jù)條件查詢左表數(shù)據(jù)不存在使用null值填充
右連接查詢語法格式:
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2
說明:
right join 就是右連接查詢關(guān)鍵字
on 就是連接查詢條件
表1 是左表
表2 是右表
例1：使用右連接查詢學(xué)生表與班級(jí)表:
☆ 小結(jié)
右連接使用right join .. on .., on 表示兩個(gè)表的連接查詢條件
右連接以右表為主根據(jù)條件查詢左表數(shù)據(jù)，左表數(shù)據(jù)不存在使用null值填充。
4、自連接查詢(擴(kuò)展)
自連接查詢：數(shù)據(jù)表自己連接自己，前提：連接操作時(shí)必須為數(shù)據(jù)表定義別名！
左表和右表是同一個(gè)表，根據(jù)連接查詢條件查詢兩個(gè)表中的數(shù)據(jù)。
兩個(gè)實(shí)際的工作場(chǎng)景，求省市區(qū)信息，求分類導(dǎo)航信息
地域：area
pid 全稱 parent id（父級(jí)ID編號(hào)），如果pid值為null代表本身就是父級(jí)，如果pid是一個(gè)具體的數(shù)值，則代表其屬于子級(jí)
例1：查詢省的名稱為“廣東省”的所有城市
創(chuàng)建areas表:
執(zhí)行sql文件給areas表導(dǎo)入數(shù)據(jù):
自連接查詢的用法:
說明:
自連接查詢必須對(duì)表起別名
☆ 小結(jié)
自連接查詢就是把一張表模擬成左右兩張表，然后進(jìn)行連表查詢。
自連接就是一種特殊的連接方式，連接的表還是本身這張表
十二、子查詢(三步走)
1、子查詢（嵌套查詢）的介紹
在一個(gè) select 語句中,嵌入了另外一個(gè) select 語句, 那么被嵌入的 select 語句稱之為子查詢語句，外部那個(gè)select語句則稱為主查詢.
主查詢和子查詢的關(guān)系:
子查詢是嵌入到主查詢中
子查詢是輔助主查詢的,要么充當(dāng)條件,要么充當(dāng)數(shù)據(jù)源(數(shù)據(jù)表)
子查詢是可以獨(dú)立存在的語句,是一條完整的 select 語句
2、子查詢的使用
例1. 查詢學(xué)生表中大于平均年齡的所有學(xué)生:
需求：查詢年齡 > 平均年齡的所有學(xué)生
前提：
① 獲取班級(jí)的平均年齡值
② 查詢表中的所有記錄，判斷哪個(gè)同學(xué) > 平均年齡值 第一步：寫子查詢
第二步：寫主查詢
第三步：第一步和第二步進(jìn)行合并
例2. 查詢tb_goods產(chǎn)品表中具有分類信息的產(chǎn)品
需求：查詢產(chǎn)品表中具有分類信息的產(chǎn)品（沒有與之對(duì)應(yīng)分類信息的產(chǎn)品不顯示）
前提：① 查詢分類表中，到底有哪些分類（獲取cid編號(hào)）
② 到產(chǎn)品表中進(jìn)行判斷，判斷這個(gè)商品的cid編號(hào)與①中的是否相等 第一步：編寫子查詢
第二步：編寫主查詢
第三步：把主查詢和子查詢合并
例3. 查找年齡最小且成績最低的學(xué)生:
第一步：獲取年齡最小值和成績最小值
第二步：查詢所有學(xué)員信息（主查詢）
第三步：把第一步和第二步合并
注：數(shù)據(jù)表中必須有這樣一條記錄，否則可能查詢不到結(jié)果，重點(diǎn)練習(xí)子查詢返回多個(gè)結(jié)果情況。
3、小結(jié)
子查詢是一個(gè)完整的SQL語句，子查詢被嵌入到一對(duì)小括號(hào)里面 掌握子查詢編寫三步走
十三、外鍵約束（擴(kuò)展）
主鍵：primary key
外鍵：foreign key（應(yīng)用場(chǎng)景：在兩表或多表關(guān)聯(lián)的時(shí)候設(shè)置的，用于標(biāo)志兩個(gè)表之間的關(guān)聯(lián)關(guān)系）
① 主鍵約束primary key
② 默認(rèn)值約束default
③ 非空約束not null
④ 唯一約束unique key
⑤ 外鍵約束foreign key
原則：在一張表中，其是主鍵。但是在另外一張表中，其是從鍵（非主鍵），但是這個(gè)字段是兩張表的關(guān)聯(lián)字段。
1、外鍵約束作用
外鍵約束:對(duì)外鍵字段的值進(jìn)行更新和插入時(shí)會(huì)和引用表中字段的數(shù)據(jù)進(jìn)行驗(yàn)證，數(shù)據(jù)如果不合法則更新和插入會(huì)失敗，保證數(shù)據(jù)的有效性。
dage表：
xiaodi表：
外鍵設(shè)計(jì)原則：保證兩張表的關(guān)聯(lián)關(guān)系，保證數(shù)據(jù)的一致性。在選擇時(shí)，一般在一個(gè)表中時(shí)關(guān)聯(lián)字段，在另外一個(gè)表中是主鍵，則這個(gè)字段建議設(shè)置為外鍵。
2、對(duì)于已經(jīng)存在的字段添加外鍵約束
3、在創(chuàng)建數(shù)據(jù)表時(shí)設(shè)置外鍵約束
4、刪除外鍵約束
十四、索引[了解]
① 編寫SQL ② SQL優(yōu)化（查詢數(shù)據(jù)把查詢時(shí)間縮短）
TB級(jí)別，10s => 0.01s
1、索引概述
索引作用: 快速檢索數(shù)據(jù)(提高查詢效率)，InnoDB引擎其底層主要是使用B+ Tree結(jié)構(gòu)
2、普通索引使用
主鍵就是一個(gè)索引，比如百萬條數(shù)據(jù)，沒有主鍵索引，查詢可能需要3-5s，如果我們添加了主鍵索引且剛好，要查詢的字段就是主鍵，則可以縮短到零點(diǎn)零幾秒。
備注：主鍵、外鍵、唯一鍵其實(shí)也是索引
創(chuàng)建索引: create index index_cname on category(cname); create index index_cname on category(cname(20));
修改表添加索引: alter table category add index index_cname(cname(20));
查詢索引: show index from category;
刪除索引: drop index index_cname on category;
查看所有庫或者表的索引:
3、唯一索引使用
創(chuàng)建索引
刪除索引
擴(kuò)展：性能監(jiān)測(cè)
4、索引使用注意
索引不是越多越好. 索引使用應(yīng)該注意以下問題:
磁盤空間消耗
創(chuàng)建索引和維護(hù)索引的時(shí)間消耗
經(jīng)常增刪改數(shù)據(jù)，索引需要?jiǎng)討B(tài)維護(hù)，效率低下。
不經(jīng)常查詢的字段不需要?jiǎng)?chuàng)建索引
大部分值相同的字段不需要?jiǎng)?chuàng)建索引
擴(kuò)展:
開啟mysql時(shí)間檢測(cè): set profiling=1;
查看sql語句執(zhí)行時(shí)間: show profiles;
十五、開窗函數(shù)(mysql 8.0后新的)
1、數(shù)據(jù)準(zhǔn)備
2、開窗函數(shù)使用
格式:
partition by ：相當(dāng)于分組group by
order by ：相當(dāng)于前面的order by
使用:
