一. 函數

在 MySQL 中，函數是用于對數據進行特定處理或計算的工具，根據作用范圍和返回結果的不同，主要分為單行函數和聚合函數（又稱分組函數）。以下是詳細介紹：

1.單行函數

單行函數對每一行數據單獨處理，每行返回一個結果，常見類型包括字符串函數、數值函數、日期函數、流程控制函數等。

1.） 字符串函數

用于處理字符串類型數據，常見函數如下：

2.） 數值函數

用于數值計算，常見函數如下：

3. ）日期函數

用于處理日期和時間類型數據，常見函數如下：

4. ）流程控制函數

用于實現條件判斷邏輯，類似編程語言中的if-else：

2.聚合函數（分組函數）

聚合函數用于對一組數據進行匯總計算，返回單一結果，通常與GROUP BY配合使用（若不分組，則默認對全表數據聚合）。常見聚合函數如下：

注意事項：

1. 聚合函數會自動忽略NULL值（COUNT(*)除外）。

2. 若使用聚合函數時未加GROUP BY，則整個表視為一組。

3. SELECT子句中若有聚合函數，其他非聚合字段需出現在GROUP BY中（避免歧義）。

3.函數使用示例

1. 字符串拼接與日期格式化：

   SELECT CONCAT('姓名：', name) AS 姓名,DATE_FORMAT(birthday, '%Y年%m月%d日') AS 生日
   FROM students;

2. 聚合函數統計：

   SELECT COUNT(*) AS 總人數,AVG(score) AS 平均分,MAX(score) AS 最高分
   FROM students;

3. 條件判斷：

   SELECT name AS 姓名,score AS 分數,CASE WHEN score >= 90 THEN '優秀'WHEN score >= 80 THEN '良好'ELSE '及格'END AS 等級
   FROM students;

二. 約束

在 MySQL 中，約束（Constraints）是用于限制表中數據的規則，確保數據的完整性、一致性和準確性。常見的 MySQL 約束包括以下幾種：

主鍵約束（PRIMARY KEY）

• 唯一標識表中的每條記錄

• 不能有重復值，且不能為 NULL

• 一個表只能有一個主鍵

CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(50)
);

外鍵約束（FOREIGN KEY）

• 用于關聯兩個表，確保參照完整性

• 外鍵列的值必須匹配另一個表的主鍵列的值或為 NULL

CREATE TABLE orders (order_id INT PRIMARY KEY,student_id INT,FOREIGN KEY (student_id) REFERENCES students(id)
);

唯一約束（UNIQUE）

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

• 允許 NULL 值，但 NULL 只能出現一次

CREATE TABLE users (id INT PRIMARY KEY,email VARCHAR(100) UNIQUE
);

非空約束（NOT NULL）

• 確保列不能包含 NULL 值

CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,age INT
);

檢查約束（CHECK）

• 確保列中的值滿足指定的條件

• MySQL 8.0.16 及以上版本支持

CREATE TABLE products (id INT PRIMARY KEY,price DECIMAL(10,2) CHECK (price > 0)
);

默認約束（DEFAULT）

• 為列指定默認值，如果插入數據時未指定該列的值，則使用默認值

CREATE TABLE customers (id INT PRIMARY KEY,country VARCHAR(50) DEFAULT 'China'
);

三. 多表查詢

多表查詢是數據庫操作中常用的技術，用于從兩個或多個相關聯的表中獲取數據。在 MySQL 中，實現多表查詢主要有以下幾種方式：

多表關系

1. 一對一關系（One-to-One）

• 特點：兩個表中的記錄一一對應，一個表的一條記錄只能與另一個表的一條記錄關聯。

• 適用場景：常用于拆分表結構（如將用戶基本信息和詳細信息分開存儲），或關聯具有強歸屬關系的數據。

• 實現方式：在任意一個表中添加外鍵，關聯另一個表的主鍵，并設置外鍵為唯一（UNIQUE）。

-- 示例：用戶表（基本信息）
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) UNIQUE NOT NULL,email VARCHAR(100) UNIQUE NOT NULL
);
?
-- 用戶詳情表（一對一關聯 users 表）
CREATE TABLE user_profiles (id INT PRIMARY KEY AUTO_INCREMENT,user_id INT UNIQUE NOT NULL, -- 唯一外鍵，確保一對一real_name VARCHAR(50),phone VARCHAR(20),FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

2. 一對多關系（One-to-Many）

• 特點：一個表中的一條記錄可以對應另一個表中的多條記錄，但反向只能對應一條。

• 適用場景：最常見的關系（如 “部門 - 員工”“訂單 - 訂單項”）。

• 實現方式：在 “多” 的一方添加外鍵，關聯 “一” 的一方的主鍵。

-- 示例：部門表（一的一方）
CREATE TABLE departments (id INT PRIMARY KEY AUTO_INCREMENT,dept_name VARCHAR(50) NOT NULL
);
?
-- 員工表（多的一方，關聯部門表）
CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,dept_id INT, -- 外鍵關聯部門表FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE SET NULL
);

3. 多對多關系（Many-to-Many）

• 特點：兩個表中的記錄可以互相對應多條記錄（如 “學生 - 課程”：一個學生可選多門課，一門課可被多個學生選）。

• 適用場景：需要雙向一對多的業務關系。

• 實現方式：通過中間表關聯兩個表，中間表至少包含兩個外鍵，分別指向兩個表的主鍵。

-- 示例：學生表
CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL
);
?
-- 課程表
CREATE TABLE courses (id INT PRIMARY KEY AUTO_INCREMENT,course_name VARCHAR(100) NOT NULL
);
?
-- 中間表（關聯學生和課程，實現多對多）
CREATE TABLE student_courses (id INT PRIMARY KEY AUTO_INCREMENT,student_id INT NOT NULL,course_id INT NOT NULL,-- 聯合唯一約束，避免重復關聯UNIQUE KEY (student_id, course_id),FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

關系設計的注意事項

1. 外鍵約束

   強制關聯關系，可選參數：

   • ON DELETE CASCADE：主表記錄刪除時，從表關聯記錄也刪除

   • ON DELETE SET NULL：主表記錄刪除時，從表外鍵設為 NULL（需外鍵允許 NULL）

2. 避免循環依賴：多表關聯時避免形成閉環（如 A 依賴 B，B 依賴 C，C 依賴 A），可能導致數據操作異常。

3. 中間表設計：多對多的中間表可額外添加字段（如 “學生選課表” 可加 score 字段記錄成績）。

1. 交叉連接（CROSS JOIN）

返回兩個表的笛卡爾積（所有可能的組合），通常需要配合 WHERE 條件過濾無意義的記錄。

-- 語法
SELECT 列名 FROM 表1 CROSS JOIN 表2 [WHERE 條件];
?
-- 示例：查詢學生和他們的課程（未過濾前是所有學生與所有課程的組合）
SELECT students.name, courses.course_name
FROM students CROSS JOIN courses
WHERE students.id = courses.student_id; -- 過濾出有效的關聯記錄

2. 內連接（INNER JOIN）

只返回兩個表中滿足連接條件的記錄（即匹配的記錄），是最常用的連接方式。

-- 語法
SELECT 列名 FROM 表1 INNER JOIN 表2 ON 連接條件;-- 示例：查詢學生及其選修的課程（只顯示有選課記錄的學生）
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

3. 外連接（OUTER JOIN）

返回一個表的所有記錄，以及另一個表中滿足條件的匹配記錄。外連接分為：

• 左外連接（LEFT JOIN / LEFT OUTER JOIN）：返回左表所有記錄 + 右表匹配記錄

• 右外連接（RIGHT JOIN / RIGHT OUTER JOIN）：返回右表所有記錄 + 左表匹配記錄

• 全外連接（FULL JOIN）：返回兩個表的所有記錄（MySQL 不直接支持，需用 UNION 模擬）

-- 左外連接示例：查詢所有學生及其選修的課程（包括未選課的學生）
SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id;
?
-- 右外連接示例：查詢所有課程及選修該課程的學生（包括無學生選修的課程）
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.id = courses.student_id;

4. 自連接（SELF JOIN）

將表與自身進行連接，通常用于查詢表中具有層級或關聯關系的數據（如員工與上司）。

-- 示例：查詢員工及其直屬上司的姓名（假設 employees 表中有 manager_id 關聯上司ID）
SELECT e.name AS 員工, m.name AS 上司
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

5. 聯合查詢（UNION / UNION ALL）

用于合并多個 SELECT 語句的結果集，要求列數和數據類型一致。

• UNION：去除重復記錄

• UNION ALL：保留所有記錄（包括重復）

-- 示例：合并兩個查詢結果（查詢10歲以下和20歲以上的學生）
SELECT name, age FROM students WHERE age < 10
UNION
SELECT name, age FROM students WHERE age > 20;

子查詢（Subquery）是嵌套在另一個 SQL 語句中的查詢，也稱為內部查詢，而包含子查詢的語句稱為外部查詢。子查詢通常用于為主查詢提供數據或條件，可使復雜查詢邏輯更清晰。

子查詢的基本特點

• 子查詢必須用括號 () 包裹

• 子查詢通常放在 WHERE、FROM 或 SELECT 子句中

• 子查詢的結果可以是單個值、一行、一列或多行

常見子查詢類型及示例

1. 返回單個值的子查詢（標量子查詢）

常用于 WHERE 子句中，配合比較運算符（=, >, <, >=, <= 等）使用。

-- 示例：查詢與"張三"同齡的學生
SELECT name, age 
FROM students 
WHERE age = (SELECT age FROM students WHERE name = '張三');

2. 返回一列值的子查詢（列子查詢）

通常與 IN、NOT IN、ANY、ALL 等關鍵字配合使用

-- 示例1：查詢選修了"數學"課程的學生（使用IN）
SELECT name 
FROM students 
WHERE id IN (SELECT student_id FROM courses WHERE course_name = '數學'
);
?
-- 示例2：查詢年齡大于所有女生的男生（使用ALL）
SELECT name, age 
FROM students 
WHERE gender = '男' AND age > ALL (SELECT age FROM students WHERE gender = '女');

3. 返回多行多列的子查詢（表子查詢）

可作為一個臨時表，用于 FROM 子句中（通常需要起別名）。

-- 示例：查詢每個班級的平均年齡及班級信息
SELECT classes.class_name, avg_ages.avg_age
FROM classes
JOIN (SELECT class_id, AVG(age) AS avg_age FROM students GROUP BY class_id
) AS avg_ages ON classes.id = avg_ages.class_id;

4. EXISTS 子查詢

用于判斷子查詢是否返回結果，返回 TRUE 或 FALSE，不關心具體數據。

-- 示例：查詢有選修課程的學生（存在選課記錄）
SELECT name 
FROM students s 
WHERE EXISTS (SELECT 1 FROM courses c WHERE c.student_id = s.id
);

5. 嵌套子查詢

子查詢中可以再包含子查詢，形成多層嵌套（但建議層數不宜過多，以免影響性能）。

-- 示例：查詢選修了"張三"所選全部課程的學生
SELECT DISTINCT s.name
FROM students s
JOIN courses c ON s.id = c.student_id
WHERE c.course_name IN (SELECT course_name FROM courses WHERE student_id = (SELECT id FROM students WHERE name = '張三')
)
GROUP BY s.name
HAVING COUNT(c.course_name) = (SELECT COUNT(*) FROM courses WHERE student_id = (SELECT id FROM students WHERE name = '張三')
);

子查詢的注意事項

1. 性能問題：復雜子查詢可能影響效率，可嘗試用 JOIN 改寫（部分場景下 JOIN 性能更優）。

2. 關聯子查詢與非關聯子查詢

   ：

   • 非關聯子查詢：獨立于外部查詢，只執行一次

   • 關聯子查詢：依賴外部查詢的字段，可能執行多次（每行一次）

3. SELECT 子句中的子查詢：只能返回單個值，用于為結果集增加計算列。

多表查詢的注意事項

1. 表別名：使用 AS 為表起別名（可省略 AS），簡化 SQL 語句。

2. 列名沖突：當多表中有同名列時，需用 表名.列名 或 別名.列名 區分（如 students.id）。

3. 性能優化：對連接條件中的列建立索引，避免不必要的 SELECT *，減少數據掃描量。

四. 事務

在 MySQL 中，事務（Transaction）是一組數據庫操作的集合，這些操作要么全部成功執行，要么全部失敗回滾，以此保證數據的一致性和完整性。事務是數據庫管理系統（DBMS）處理并發操作和故障恢復的核心機制。

MySQL 事務的 ACID 特性

事務必須滿足以下四個基本特性，即 ACID 特性：

1. 原子性（Atomicity） 事務中的所有操作要么全部執行成功，要么全部失敗回滾（Rollback），不會出現部分執行的情況。 例如：轉賬操作中，“A 賬戶扣款” 和 “B 賬戶到賬” 必須同時成功或同時失敗。

2. 一致性（Consistency） 事務執行前后，數據庫從一個一致的狀態轉變為另一個一致的狀態，數據規則（如約束、邏輯關系）不會被破壞。 例如：轉賬前后，A 和 B 的賬戶總金額保持不變。

3. 隔離性（Isolation） 多個事務并發執行時，一個事務的操作不會被其他事務干擾，各事務之間相互隔離。 MySQL 通過隔離級別控制并發事務的交互程度（見下文）。

4. 持久性（Durability） 事務一旦提交（Commit），其對數據的修改會永久保存到數據庫中，即使系統崩潰也不會丟失。

MySQL 事務的操作語法

MySQL 中，事務的基本操作通過以下 SQL 命令實現：

1. 開啟事務 關閉自動提交模式，后續操作將納入事務管理：

   START TRANSACTION; ?-- 或 BEGIN;

2. 執行操作 執行一系列 SQL 語句（如 INSERT、UPDATE、DELETE 等）：

   UPDATE accounts SET balance = balance - 100 WHERE id = 1; ?-- A賬戶扣款
   UPDATE accounts SET balance = balance + 100 WHERE id = 2; ?-- B賬戶到賬

3. 提交事務 確認所有操作成功，將修改永久保存到數據庫：

   COMMIT;

4. 回滾事務 若操作失敗，撤銷所有已執行的修改，恢復到事務開始前的狀態：

   ROLLBACK;

5. 設置保存點（可選） 在事務中設置中間點，可回滾到指定保存點（而非整個事務）：

   SAVEPOINT sp1;  -- 創建保存點 sp1
   ROLLBACK TO sp1;  -- 回滾到 sp1

MySQL 事務的隔離級別

多個事務并發執行時，可能出現 臟讀、不可重復讀、幻讀 等問題。MySQL 定義了四種隔離級別（從低到高），用于平衡隔離性和并發性能：

1. 讀未提交（READ UNCOMMITTED） 最低隔離級別，一個事務可讀取另一個未提交的修改。可能導致 臟讀（讀取到未提交的無效數據）。

2. 讀已提交（READ COMMITTED） 一個事務只能讀取另一個已提交的修改，避免 臟讀，但可能出現 不可重復讀（同一事務中多次讀取同一數據，結果不一致）。 這是大多數數據庫的默認級別（如 Oracle）。

3. 可重復讀（REPEATABLE READ） MySQL 的默認隔離級別。保證同一事務中多次讀取同一數據的結果一致，避免 臟讀 和 不可重復讀，但可能出現 幻讀（事務執行中，其他事務新增的數據被讀取到）。

4. 串行化（SERIALIZABLE） 最高隔離級別，強制事務串行執行（類似單線程），避免所有并發問題，但性能最差。

查看和設置隔離級別

• 查看當前隔離級別：

  -- 查看全局隔離級別
  SELECT @@GLOBAL.transaction_isolation;
  -- 查看當前會話隔離級別
  SELECT @@SESSION.transaction_isolation;

• 設置隔離級別（需有足夠權限）：

  -- 設置全局隔離級別（對新會話生效）
  SET GLOBAL transaction_isolation = 'READ COMMITTED';
  -- 設置當前會話隔離級別
  SET SESSION transaction_isolation = 'REPEATABLE READ';

注意事項

1. 存儲引擎支持：MySQL 中，只有 InnoDB 存儲引擎支持事務，MyISAM 等引擎不支持。

2. 自動提交：MySQL 默認開啟 AUTOCOMMIT=1（每條 SQL 自動作為一個事務提交），開啟事務后需手動提交或回滾。

3. 鎖機制：事務的隔離性通過 InnoDB 的鎖機制（如行鎖、表鎖）和 MVCC（多版本并發控制）實現。