六、多表查詢

1、多表關系

①、一對多（多對一）

• 舉例：一個部門對多個員工

• 實現：多的那邊建立外鍵，指向一的那邊的主鍵

②、多對多

• 舉例：一個學生可選多門課，一門課可被多個學生選

• 實現：建立中間表，包含兩個外鍵，指向兩邊的主鍵

③、一對一

• 舉例：一張表是用戶姓名和id，另一張表是用戶密碼和信息

• 實現：任意一方加入外鍵，指向另一方的主鍵

2、多表查詢分類

①、連接查詢

Ⅰ、內連接

  # 隱式內連接SELECT 字段列表 FROM 表1,表2 WHERE 條件 ...;# 顯示內連接SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 連接條件 ...;?# 內連接查詢的是兩張表交集的部分

Ⅱ、外連接

  # 左外連接SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 條件 ...;# 相當于查詢表1(左表)的所有數據，包含表1和表2交集部分的數據?# 右外連接SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 條件 ...;# 相當于查詢表2(右表)的所有數據，包含表1和表2交集部分的數據

Ⅲ、自連接

  # 自連接查詢，可以是內連接查詢，也可以是外連接查詢。# 自連接其實就是將一張表取兩個名字，看成兩張表來使用SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ...;SELECT 字段列表 FROM 表A 別名A LEFT [OUTER] JOIN 表A 別名B ON 條件 ...;SELECT 字段列表 FROM 表A 別名A RIGHT [OUTER] JOIN 表A 別名B ON 條件 ...;?# 補充：聯合查詢，把多次查詢的結果合并起來，形成一個新的查詢結果集# 對于聯合查詢的多張表的列數必須保持一致，字段類型也需要保持一致。# union all 會將全部的數據直接合并在一起，union 會對合并之后的數據去重。SELECT 字段列表 FROM 表A ...UNION[ALL]SELECT 字段列表 FROM 表B ...;

②、子查詢

SQL語句中嵌套SELECT語句，稱為嵌套查詢，又稱子查詢。

做法都是將前一個表的查詢語句插入到后一個表的限制條件內，只不過是前一個表的查詢內容不同，有的是一個值（標量子查詢）、一列（列子查詢）、一行（行子查詢）、一個表（表子查詢）

Ⅰ、標量子查詢

子查詢返回的結果是單個值（數字、字符串、日期等），最簡單的形式，這種子查詢成為標量子查詢。常用的操作符：= <> > >= < <=

Ⅱ、列子查詢

子查詢返回的結果是一列（可以是多行），這種子查詢稱為列子查詢。常用的操作符：IN 、NOT IN 、ANY 、SOME 、ALL

Ⅲ、行子查詢

子查詢返回的結果是一行（可以是多列），這種子查詢稱為行子查詢。常用的操作符：= 、<>、IN 、NOT IN

Ⅳ、表子查詢

子查詢返回的結果是多行多列，這種子查詢稱為表子查詢。常用的操作符：IN

3、實操

注意不是所有都演示，只演示部分

  create table student(id varchar(20) primary key not null unique comment '學號',name varchar(20) not null comment '姓名',age smallint not null comment '年齡',gender varchar(1) not null comment '性別',major_class varchar(20) not null comment '專業')comment '學生表';create table course(course_id varchar(5) not null unique comment '課程編號',course_name varchar(20) not null comment '課程名稱')comment '課程表';create table sc(id varchar(20) primary key not null unique comment '學號',course_id varchar(5) not null unique comment '課程編號',grade float not null comment '成績',# 創建外鍵將本表 sc 的 id 和 student 表的 id 綁定constraint fk_s_sc foreign key (id) references student(id),# 創建外鍵將本表 sc 的 course_id 和 course 表的 course_id 綁定constraint fk_c_sc foreign key (course_id) references course(course_id))comment '學生課程表';create table staff(class int not null comment '所處層級',name varchar(20) not null comment '姓名',age smallint comment '年齡',gender varchar(1) comment '性別',job varchar(20) not null comment '工作',manage int comment '管理層級')comment '教職工表';INSERT INTO student (id, name, age, gender, major_class) VALUES(1, '張三', 12, '男', '計算機科學'),(2, '李四', 22, '女', '電子工程'),(3, '王五', 15, '男', '機械工程'),(4, '趙六', 18, '女', '化學工程'),(5, '孫七', 25, '男', '土木工程');INSERT INTO course (course_id, course_name) VALUES('C001', '高等數學'),('C002', '線性代數'),('C003', '大學物理'),('C004', '數據結構'),('C005', '操作系統');INSERT INTO sc (id, course_id, grade) VALUES(1, 'C001', 45.5),(2, 'C002', 77.0),(3, 'C003', 84.5),(4, 'C004', 98.0),(5, 'C005', 67.5);INSERT INTO staff VALUES(1, '王校長', 50, '男', '校長', 3),(2, '李副校長', 45, '女', '副校長', 3),(3, '張教導主任', 40, '男', '教導主任', 4),(4, '趙年級主任', 35, '女', '年級主任', 5),(5, '錢老師', 30, '女', '教師', 6);INSERT INTO staff (class, name, age, gender, job) VALUES(6, '孟同學', 14, '女', '學生');?# 隱式內連接（查詢每個同學所選的課程名稱）SELECT name,course_name FROM student,sc,courseWHERE student.id = sc.id AND course.course_id = sc.course_id;# 顯示內連接（查詢 id 為 1 的同學其所選課程即成績）SELECT name,course_name,grade FROM student,sc,courseWHERE student.id = sc.id AND course.course_id = sc.course_id AND student.id = 1;# 自連接（查詢教職工名稱及其直屬管理人員）SELECT a.name '上級',b.name '下級' FROM staff a JOIN staff b on a.manage = b.class;# 子查詢（無非就是一個套一個進行查詢）# 查詢張三和李四其所選課程即成績SELECT id FROM student WHERE name = '張三' OR name = '李四';# 得id=1、2（一列數據），向下整合即可，其他子查詢類似，只不過得到的變為一個標量、一行、一表SELECT name,course_name,grade FROM student,sc,courseWHERE student.id = sc.id AND course.course_id = sc.course_idAND student.id in (SELECT id FROM student WHERE name = '張三' OR name = '李四');

七、事務

1、事務簡介

事務是一組操作的集合，這組操作要么全部成功，要么全部失敗

2、事務操作

系統默認是自動提交，即每當我們執行了DML語句，MySQL會隱式提交事務。

但是如果是多個事務自動提交，那么當出現事務的報錯后，可能上一個事務會執行而下一個事務不會執行，為避免一部分語句成功一部分失敗的情況發生，所以需要手動提交事務。

  # 1、通過控制事務的提交方式來操作事務# 查看/設置事務提交方式SELECT @@autocommit;SET @@autocommit = 0; ? ? ?# 如果是1，則是自動提交，如果是0，則是手動提交# 提交事務COMMIT;# 回滾事務ROLLBACK;# 當我們將事務的提交方式設置為手動提交，我們每次執行完語句，其內部數據不會改變，只有當執行commit; 語句后才會改變# 當出現錯誤時，我們可以使用rollback；語句回滾事務來保證數據的完整性和正確性?# 2、通過控制事務的開啟來操作事務# 開啟事務START TRANSACTION 或 BEGIN;# 提交事務COMMIT;# 回滾事務ROLLBACK;# 開啟事務到事務的提交（commit）或回滾（rollback）是一個完整的事務單元，即開啟后一旦提交或回滾后就結束，然后需要再次開啟# 當出現語句錯誤，由于我們開啟事務，這時候原來的數據就不會改變，而是先報錯提醒我們將會出現一部分成功一部分失敗的情形而導致原來的數據發生改變

3、事務四大特性（ACID）

• 原子性（Atomicity）：事務是不可分割的最小操作單元，要么全部成功，要么全部失敗。

• 一致性（Consistency）：事務完成時，必須使所有的數據都保持一致狀態。

• 隔離性（Isolation）：數據庫系統提供的隔離機制，保證事務在不受外部并發操作影響的獨立環境下運行。

• 持久性（Durability）：事務一旦提交或回滾，它對數據庫中的數據的改變就是永久的。

4、并發事務問題

并發事務問題即兩個事務同時對同一數據的操作時會出現的問題

5、事務隔離級別

  # 查看事務隔離級別SELECT @@TRANSACTION_ISOLATION;# 設置事務隔離級別SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

性能排序（從高到低）：

• Read uncommitted

• Read committed

• Repeatable Read(默認)

• Serializable

隔離級別排序（從高到低）：

• Serializable

• Repeatable Read(默認)

• Read committed

• Read uncommitted

這里就不演示每個隔離級別了，如有興趣可自行雙開終端試一試。

八、存儲引擎

1、MySQL體系結構

2、存儲引擎

①、簡介

存儲引擎是數據庫管理系統中用于存儲、管理和檢索數據的軟件組件。它決定了數據的存儲方式、索引方式以及如何在數據庫中存取數據。

  # 在創建表時，指定存儲引擎CREATE TABLE 表名(字段1 字段1類型 [COMMENT 字段1注釋 ],......字段n 字段n類型 [COMMENT 字段n注釋 ])ENGINE = INNODB [ COMMENT 表注釋 ];?# 查看當前數據庫支持的存儲引擎SHOW ENGINES;

②、特點

Ⅰ、InnoDB（默認）

• DML操作遵循ACID模型，支持事務；

• 行級鎖，提高并發訪問性能；

• 支持外鍵FOREIGN KEY約束，保證數據的完整性和正確性；

Ⅱ、MyISAM

• 不支持事務，不支持外鍵

• 支持表鎖，不支持行鎖

• 訪問速度快

Ⅲ、Memory

• 內存存放

• hash索引（默認）

③、選擇

在選擇存儲引擎時，應該根據應用系統的特點選擇合適的存儲引擎。對于復雜的應用系統，還可以根據實際情況選擇多種存儲引擎進行組合。

• InnoDB：是Mysql的默認存儲引擎，支持事務、外鍵。如果應用對事務的完整性有比較高的要求，在并發條件下要求數據的一致性，數據操作除了插入和查詢之外，還包含很多的更新、刪除操作，那么InnoDB存儲引擎是比較合適的選擇。

• MyISAM：如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務的完整性、并發性要求不是很高，那么選擇這個存儲引擎是非常合適的。

• MEMORY：將所有數據保存在內存中，訪問速度快，通常用于臨時表及緩存。MEMORY的缺陷就是對表的大小有限制，太大的表無法緩存在內存中，而且無法保障數據的安全性。

九、索引

MySQL 中，索引是一種用于優化查詢效率的數據結構。索引可以加快數據檢索速度，提高數據庫性能。

1、索引結構

MySQL的索引是在存儲引擎層實現的，不同的存儲引擎有不同的結構，主要包含以下幾種：

我們主要研究 InnoDB 的 B+Tree 索引

①、預備知識

Ⅰ、B樹（B-Tree）

定義：

• B樹是一種平衡多路查找樹（多叉樹），可以有多于兩個兒子，通常用于數據庫和文件系統中。

特點：

• 平衡：所有葉子節點都位于同一層。

• 多路：每個節點可以有多個子節點（通常為2到最大度數）。

• 路徑從根節點到葉子節點長度相同。

• 支持范圍查詢，可以找到給定范圍內的所有數據。

• 任一元素左子樹小于它本身，右子樹大于它本身

n路B樹即n階B樹，每個節點最多存儲（n-1）個值，n個指針，當每個節點插入值后多于（n-1）個值即會分裂。

Ⅱ、B+樹（B+Tree）

定義：

• B+樹是B樹的變種，具有所有葉子節點都鏈接在一起，并且葉子節點增加一個指向下一個葉子節點的指針。即葉子節點會形成單向列表。

特點：

• 保持B樹的平衡性，但增加了順序訪問的能力。

• 葉子節點包含數據，非葉子節點不包含數據，只包含鍵和指針。

• 支持高效的范圍查詢，因為葉子節點按順序鏈接。

• 支持高效的順序訪問（如掃描）。

B+樹的數據只存在于列表，故每個節點可存儲更多地址

Ⅲ、哈希表

哈希索引（Hash Index）是一種數據庫索引技術，它使用哈希表（Hash Table）來實現索引。哈希表是一種數據結構，它通過哈希函數將數據值映射到一個固定大小的數組索引表中，從而實現快速的數據查找。

• 快速查找：哈希索引非常適合于等值查詢操作，因為它可以直接通過哈希函數計算出數據應該存儲的位置，從而實現快速查找。

• 不支持范圍查詢：由于哈希索引的特性，它不支持范圍查詢（如 BETWEEN、LIKE 等）。如果需要進行范圍查詢，可能需要使用其他類型的索引，如B+樹索引。

• 哈沖突解決：當兩個不同的數據值經過哈希函數計算后得到相同的索引位置時，稱為哈希沖突（Hash Collision）。解決哈希沖突的方法通常是鏈地址法（Chaining），即在哈希表的每個槽位上維護一個鏈表，用于存儲所有映射到該槽位的數據。

• 適用場景：哈希索引適用于那些需要快速查找且數據分布均勻的場景。如果數據分布不均勻，可能會導致哈希沖突增多，從而影響性能。

②、補充知識

Ⅰ、B樹和B+樹區別

Ⅱ、MySQL選擇B+樹的原因

1. 更高的查詢效率：

   • B+樹的非葉子節點不存儲數據，因此可以存儲更多的鍵值，使樹的高度更低

   • 通常3-4層的B+樹就能存儲千萬級的數據，減少磁盤I/O次數

2. 更穩定的查詢性能：

   • 所有查詢都必須到達葉子節點，查詢路徑長度相同

   • 避免了B樹中可能在非葉子節點找到數據導致的不穩定情況

3. 優秀的范圍查詢能力：

   • 葉子節點形成的鏈表使范圍查詢非常高效

   • 只需找到起始節點，然后沿著鏈表遍歷即可

4. 更高的磁盤I/O效率：

   • B+樹的內部節點只存儲鍵值，不存儲數據，可以一次讀入更多的鍵值

   • 更適合以頁為單位(如4KB)的磁盤讀取

5. 更適合數據庫場景：

   • 數據庫經常需要全表掃描，B+樹只需遍歷葉子節點鏈表

   • 而B樹需要遍歷整棵樹，效率低很多

6. 更好的緩存利用率：

   • 非葉子節點可以常駐內存，只對葉子節點進行磁盤I/O

   • 因為非葉子節點不包含數據，占用空間小，緩存效率高

MySQL的InnoDB存儲引擎使用B+樹作為索引結構，這種設計在數據庫的讀多寫少、大量范圍查詢的場景下表現尤為出色。

2、索引分類

①、主鍵索引（PRIMARY KEY）：

• 每個表只能有一個主鍵索引。

• 主鍵索引列不能包含 NULL 值。

• 通常用于唯一標識表中的每一行。

②、唯一索引（UNIQUE）：

• 保證列中的所有值都是唯一的。

• 可以包含 NULL 值。

③、常規普通索引（INDEX）：

• 最常用的索引類型，沒有唯一性或主鍵的限制。

④、全文索引（FULLTEXT）：

• 用于全文搜索，支持對較大的文本字段進行搜索。

⑤、空間索引（SPATIAL）：

• 用于地理數據類型，如 GEOMETRY。

按存儲分類可將索引分為兩類：聚集索引和二級索引

聚集索引：

• 聚集索引通常用于主鍵（PRIMARY KEY）。

• 由于數據行的物理順序與索引順序一致，因此聚集索引可以非常高效地支持范圍查詢。

• 聚集索引可以包含其他列，但只有第一列是聚集的。

• 若有主鍵則主鍵索引就是聚集索引，沒有主鍵有唯一索引則唯一索引就是聚集索引，否則生成一個隱藏聚集索引

二級索引：

• 二級索引不保證數據行的物理順序與索引順序一致。

• 可以有選擇表中的任何列作為索引列，不僅限于主鍵。

• 適用于非聚集索引列，可以提高查詢性能，尤其是在非范圍查詢中。

聚集索引的葉子節點下掛的是主鍵對應的行數據，二級索引的葉子節點下掛的是該字段值對應的主鍵值。

回表查詢即先通過二級索引查找相應主鍵值，再通過主鍵值的聚集索引進行查找

3、索引語法

4、性能分析

5、索引使用

6、索引設計原則

明天繼續補充完成