Mysql語句分類
- DDL: 數據定義語言，用來定義數據庫對象（數據庫、表、字段）
- DML: 數據操作語言，用來對數據庫表中的數據進行增刪改
- DQL: 數據查詢語言，用來查詢數據庫中表的記錄
- DCL: 數據控制語言，用來創建數據庫用戶、控制數據庫的控制權限
Mysql中的常用數據類型
數值類型
| 數據類型 | 說明與用途 | 占用空間 | 范圍（有符號） |
|---|
TINYINT | 極小的整數，常用于布爾值或狀態標志（0/1） | 1 字節 | -128 到 127 |
SMALLINT | 小整數 | 2 字節 | -32,768 到 32,767 |
MEDIUMINT | 中整數 | 3 字節 | -8,388,608 到 8,388,607 |
INT / INTEGER | 常用整數型，最常見 | 4 字節 | -2,147,483,648 到 2,147,483,647 |
BIGINT | 大整數，適合用于用戶 ID、資金數據等 | 8 字節 | ±9 * 101? 級別 |
FLOAT(M,D) | 單精度浮點數 | 4 字節 | 小數位數有限，適用于非精確計算（如圖形） |
DOUBLE(M,D) | 雙精度浮點數 | 8 字節 | 精度更高 |
DECIMAL(M,D) | 精確小數，常用于財務數據，M 總長度，D 小數位 | 根據精度決定 | 精確存儲十進制數，不存在浮點誤差 |
字符串類型
| 數據類型 | 說明與用途 | 特點 |
|---|---|---|
CHAR(n) | 固定長度字符串，適合長度固定的數據，如身份證號 | 占用固定空間，存儲和查詢速度快 |
VARCHAR(n) | 可變長度字符串，適合大多數文本字段 | 節省空間，推薦使用 |
TEXT | 長文本，最大 65,535 字節 | 不能設置默認值，適合長評論、文章等 |
TINYTEXT | 最多 255 字節 | 適合很短的文本 |
MEDIUMTEXT | 最多 16,777,215 字節 | 適合大段文章、日志 |
LONGTEXT | 最多 4GB（4,294,967,295 字節） | 超大文本，如小說、日志文件等 |
ENUM(...) | 枚舉類型，只允許特定值之一，如 ENUM('男','女') | 存儲為整數索引，節省空間，提高一致性 |
SET(...) | 集合類型，允許多個選項的組合 | 通常用于表示多選項，如興趣、標簽等 |
日期與時間類型
| 數據類型 | 說明與用途 | 格式 |
|---|
DATE | 僅日期 | 'YYYY-MM-DD' |
TIME | 僅時間 | 'HH:MM:SS' |
DATETIME | 日期和時間，推薦使用（不受時區影響） | 'YYYY-MM-DD HH:MM:SS' |
TIMESTAMP | 日期和時間，隨時區自動調整，常用于記錄創建/修改時間 | 同上，自動更新時間戳 |
YEAR | 僅年份（1901–2155） | 'YYYY' |
DDL（數據定義語言）
數據庫操作
查詢所有數據庫：
SHOW DATABASES;查詢當前數據庫：
SELECT DATABASE();USE testdb;
SELECT DATABASE();
結果：
testdb如果沒有使用use選中任何數據庫，執行該語句返回的是NULL創建數據庫：
CREATE DATABASE [IF NOT EXISTS] 數據庫名
[DEFAULT CHARACTER SET 字符集]
[COLLATE 排序規則];?常見字符集
| 字符集名 | 描述與用途 | 支持字符范圍 | 是否支持 Emoji |
|---|---|---|---|
utf8mb4 | 推薦使用的多字節字符集，完全支持 Unicode，包括 Emoji 和擴展字符。 | 全 Unicode（最多 4 字節） | ? 支持 |
utf8 | 早期的 UTF-8 兼容字符集，但不完全，最多只支持 3 字節，無法包含部分 Emoji。 | 基本多語言字符（最多 3 字節） | ? 不支持 |
latin1 | 西歐語言用單字節字符集，占用少，性能高，但不支持多語言和 Emoji。 | 只支持 ISO 8859-1 字符 | ? 不支持 |
gbk / gb2312 | 簡體中文編碼（GBK 擴展 GB2312），用于中國老系統，但不推薦用于新項目。 | 支持漢字及部分符號 | ? 不支持 |
ascii | 僅支持 0–127 的 ASCII 碼字符，體積最小，適用于英文或標識符字段。 | 僅英文字母、數字和常用標點 | ? 不支持 |
常見排序規則?
注意：排序規則是字符集相關的，命名格式通常為 字符集_比較規則，如：utf8mb4_general_ci。
| 排序規則名 | 所屬字符集 | 描述 | 是否區分大小寫 | 排序特性 |
|---|---|---|---|---|
utf8mb4_general_ci | utf8mb4 | 通用型排序規則，不區分大小寫，性能較高。 | ? 不區分 | 快速但不完全 Unicode 正確 |
utf8mb4_unicode_ci | utf8mb4 | 更嚴格的 Unicode 排序規則，處理國際字符更準確，略慢。 | ? 不區分 | 遵循 Unicode 排序規則 |
utf8mb4_bin | utf8mb4 | 使用二進制編碼進行比較，區分大小寫，適合精確比較。 | ? 區分 | 精確但不符合自然語言排序 |
latin1_swedish_ci | latin1 | latin1 默認排序規則，原設計面向瑞典語（但已廣泛用于默認場景）。 | ? 不區分 | 一般用作 latin1 默認設置 |
utf8mb4_0900_ai_ci | utf8mb4 | MySQL 8.0 引入的新 Unicode 9.0 排序，ai 表示 accent-insensitive。 | ? 不區分大小寫/重音 | 更精細的 Unicode 支持 |
utf8mb4_0900_as_cs | utf8mb4 | 區分大小寫、區分重音的新排序規則，適用于嚴格比較。 | ? 區分 | 完全精確比較 |
? 示例
CREATE DATABASE IF NOT EXISTS mydb
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;如果 mydb 數據庫還不存在，則創建它；
使用 utf8mb4 字符集（支持 Emoji 和更多語言）；
使用 utf8mb4_general_ci 作為排序規則（不區分大小寫的通用排序方式）。刪除數據庫：
DROP DATABASE [ IF EXISTS ] 數據庫名;使用數據庫：
USE 數據庫名;表操作
查詢當前數據庫所有表：
SHOW TABLES;?創建表：
CREATE TABLE 表名(字段1 字段1類型 [COMMENT 字段1注釋],字段2 字段2類型 [COMMENT 字段2注釋],字段3 字段3類型 [COMMENT 字段3注釋],...字段n 字段n類型 [COMMENT 字段n注釋]
)[ COMMENT 表注釋 ];最后一個字段后面沒有逗號查詢表結構：
DESC 表名;它會列出表中**每一列（字段）的：
-
字段名
-
類型（數據類型）
-
是否允許
NULL -
鍵（如是否是主鍵、外鍵、索引）
-
默認值
-
其他額外信息（如
auto_increment）
假設有這樣一張表 users：
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL,email VARCHAR(100),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
查詢結果會是這樣的：
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | NULL | ||
| varchar(100) | YES | NULL | |||
| created_at | timestamp | YES | CURRENT_TIMESTAMP |
查詢指定表的建表語句：
SHOW CREATE TABLE 表名;添加字段：
ALTER TABLE 表名 ADD 字段名 類型(長度) [COMMENT 注釋] [約束];ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵稱';修改數據類型：
ALTER TABLE 表名 MODIFY 字段名 新數據類型(長度);修改字段名和字段類型：
ALTER TABLE 表名 CHANGE 舊字段名 新字段名 類型(長度) [COMMENT 注釋] [約束];將emp表的nickname字段修改為username，類型為varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵稱';刪除字段：
ALTER TABLE 表名 DROP 字段名;修改表名：
ALTER TABLE 表名 RENAME TO 新表名;刪除表：
DROP TABLE [IF EXISTS] 表名;刪除表，并重新創建該表：
TRUNCATE TABLE 表名;DML（數據操作語言）
添加數據
指定字段：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);全部字段：
INSERT INTO 表名 VALUES (值1, 值2, ...);批量添加數據：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);注意事項
- 字符串和日期類型數據應該包含在引號中
- 插入的數據大小應該在字段的規定范圍內
更新和刪除數據
修改數據：
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 條件 ];UPDATE emp SET name = 'Jack' WHERE id = 1;刪除數據：
DELETE FROM 表名 [ WHERE 條件 ];DQL（數據查詢語言）
基礎查詢
查詢多個字段：
#查詢指定字段
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
#查詢所有字段
SELECT * FROM 表名;設置別名：
SELECT 字段1 [ AS 別名1 ], 字段2 [ AS 別名2 ], 字段3 [ AS 別名3 ], ... FROM 表名;
SELECT 字段1 [ 別名1 ], 字段2 [ 別名2 ], 字段3 [ 別名3 ], ... FROM 表名;去除指定字段的重復記錄：
SELECT DISTINCT 字段1, 字段2, ... FROM 表名;條件查詢
語法：
SELECT 字段列表 FROM 表名 WHERE 條件列表;條件：
| 比較運算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| BETWEEN … AND … | 在某個范圍內（含最小、最大值） |
| IN(…) | 在in之后的列表中的值，多選一 |
| LIKE 占位符 | 模糊匹配（_匹配單個字符，%匹配任意個字符） |
| IS NULL | 是NULL |
| 邏輯運算符 | 功能 |
|---|---|
| AND 或 && | 并且（多個條件同時成立） |
| OR 或 || | 或者（多個條件任意一個成立） |
| NOT 或 ! | 非，不是 |
示例：
-- 年齡等于30
select * from employee where age = 30;
-- 年齡小于30
select * from employee where age < 30;
-- 小于等于
select * from employee where age <= 30;
-- 沒有身份證
select * from employee where idcard is null or idcard = '';
-- 有身份證
select * from employee where idcard;
select * from employee where idcard is not null;
-- 不等于
select * from employee where age != 30;
-- 年齡在20到30之間
select * from employee where age between 20 and 30;
select * from employee where age >= 20 and age <= 30;
-- 下面語句不報錯，但查不到任何信息
select * from employee where age between 30 and 20;
-- 性別為女且年齡小于30
select * from employee where age < 30 and gender = '女';
-- 年齡等于25或30或35
select * from employee where age = 25 or age = 30 or age = 35;
select * from employee where age in (25, 30, 35);
-- 姓名為兩個字
select * from employee where name like '__';
-- 身份證最后為X
select * from employee where idcard like '%X';聚合查詢（聚合函數）
常見聚合函數：
| 函數 | 功能 |
|---|---|
| count | 統計數量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
?語法：
SELECT count(id) from employee where workaddress = "廣東省";分組查詢
SELECT 字段列表 FROM 表名 [ WHERE 條件 ] GROUP BY 分組字段名 [ HAVING 分組后的過濾條件 ];where 和 having 的區別：
- 執行時機不同：where是分組之前進行過濾，不滿足where條件不參與分組；having是分組后對結果進行過濾。
- 判斷條件不同：where不能對聚合函數進行判斷，而having可以。
示例：
-- 根據性別分組，統計男性和女性數量（只顯示分組數量，不顯示哪個是男哪個是女）
select count(*) from employee group by gender;
-- 根據性別分組，統計男性和女性數量
select gender, count(*) from employee group by gender;
-- 根據性別分組，統計男性和女性的平均年齡
select gender, avg(age) from employee group by gender;
-- 年齡小于45，并根據工作地址分組
select workaddress, count(*) from employee where age < 45 group by workaddress;
-- 年齡小于45，并根據工作地址分組，獲取員工數量大于等于3的工作地址
select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3;
注意事項
- 執行順序：where > 聚合函數 > having
- 分組之后，查詢的字段一般為聚合函數和分組字段，查詢其他字段無任何意義
排序查詢
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;排序方式：
- ASC: 升序（默認）
- DESC: 降序
示例：
-- 根據年齡升序排序
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age;
-- 兩字段排序，根據年齡升序排序，入職時間降序排序
SELECT * FROM employee ORDER BY age ASC, entrydate DESC;注意
如果是多字段排序，當第一個字段值相同時，才會根據第二個字段進行排序
分頁查詢
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查詢記錄數;示例：
-- 查詢第一頁數據，展示10條
SELECT * FROM employee LIMIT 0, 10;
-- 查詢第二頁
SELECT * FROM employee LIMIT 10, 10;注意
- 起始索引從0開始，起始索引 = （查詢頁碼 - 1） * 每頁顯示記錄數
- 分頁查詢是數據庫的方言，不同數據庫有不同實現，MySQL是LIMIT
- 如果查詢的是第一頁數據，起始索引可以省略，直接簡寫 LIMIT 10
DQL執行順序
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
DCL（數據控制語言）
管理用戶
查詢用戶：
mysql 是 MySQL 系統自帶的數據庫，不是我們平時建的用戶數據庫。
它內部保存著各種權限、用戶、系統配置表，比如 user, db, tables_priv 等。
USE mysql;
查詢 mysql 數據庫中的 user 表，查看所有用戶的完整信息。
SELECT * FROM user;
創建用戶:
CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';修改用戶密碼：
ALTER USER '用戶名'@'主機名' IDENTIFIED WITH mysql_native_password BY '新密碼';?刪除用戶：
DROP USER '用戶名'@'主機名';示例：
-- 創建用戶test，只能在當前主機localhost訪問
create user 'test'@'localhost' identified by '123456';
-- 創建用戶test，能在任意主機訪問
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密碼
alter user 'test'@'localhost' identified with mysql_native_password by '1234';
-- 刪除用戶
drop user 'test'@'localhost';權限控制
常用權限：
| 權限 | 說明 |
|---|---|
| ALL, ALL PRIVILEGES | 所有權限 |
| SELECT | 查詢數據 |
| INSERT | 插入數據 |
| UPDATE | 修改數據 |
| DELETE | 刪除數據 |
| ALTER | 修改表 |
| DROP | 刪除數據庫/表/視圖 |
| CREATE | 創建數據庫/表 |
查詢權限：
SHOW GRANTS FOR '用戶名'@'主機名';授予權限：
GRANT 權限列表 ON 數據庫名.表名 TO '用戶名'@'主機名';撤銷權限：
REVOKE 權限列表 ON 數據庫名.表名 FROM '用戶名'@'主機名';注意
- 多個權限用逗號分隔
- 授權時，數據庫名和表名可以用 * 進行通配，代表所有
示例：
?
-- 創建數據庫、創建用戶、授權、查看權限、撤銷權限-- 1. 創建數據庫（如果不存在）
CREATE DATABASE IF NOT EXISTS mydb
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;-- 2. 創建用戶 alice@localhost，密碼為 'password123'
CREATE USER IF NOT EXISTS 'alice'@'localhost'
IDENTIFIED BY 'password123';-- 3. 授權 alice 對 mydb 中所有表擁有 SELECT 和 INSERT 權限
GRANT SELECT, INSERT
ON mydb.*
TO 'alice'@'localhost';-- 4. 查看 alice 擁有的權限
SHOW GRANTS FOR 'alice'@'localhost';-- 5. 撤銷 alice 的 INSERT 權限
REVOKE INSERT
ON mydb.*
FROM 'alice'@'localhost';-- 6. 再次查看 alice 的權限（驗證撤權結果）
SHOW GRANTS FOR 'alice'@'localhost';-- 7. （可選）刪除 alice 用戶
-- DROP USER 'alice'@'localhost';
函數
- 字符串函數
- 數值函數
- 日期函數
- 流程函數
字符串函數
常用函數：
| 函數 | 功能 |
|---|---|
| CONCAT(s1, s2, …, sn) | 字符串拼接，將s1, s2, …, sn拼接成一個字符串 |
| LOWER(str) | 將字符串全部轉為小寫 |
| UPPER(str) | 將字符串全部轉為大寫 |
| LPAD(str, n, pad) | 左填充，用字符串pad對str的左邊進行填充，達到n個字符串長度 |
| RPAD(str, n, pad) | 右填充，用字符串pad對str的右邊進行填充，達到n個字符串長度 |
| TRIM(str) | 去掉字符串頭部和尾部的空格 |
| SUBSTRING(str, start, len) | 返回從字符串str從start位置起的len個長度的字符串 |
| REPLACE(column, source, replace) | 替換字符串 |
使用示例：
-- 拼接
SELECT CONCAT('Hello', 'World');
-- 小寫
SELECT LOWER('Hello');
-- 大寫
SELECT UPPER('Hello');
-- 左填充
SELECT LPAD('01', 5, '-');
-- 右填充
SELECT RPAD('01', 5, '-');
-- 去除空格
SELECT TRIM(' Hello World ');
-- 切片（起始索引為1）
SELECT SUBSTRING('Hello World', 1, 5);
數值函數
常見函數：
| 函數 | 功能 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x, y) | 返回x/y的模 |
| RAND() | 返回0~1內的隨機數 |
| ROUND(x, y) | 求參數x的四舍五入值，保留y位小數 |
?示例：
-- 示例：MySQL 數學函數示例-- 1. 向上取整：CEIL(x)
SELECT CEIL(4.2) AS ceil_result; -- 返回 5-- 2. 向下取整：FLOOR(x)
SELECT FLOOR(4.7) AS floor_result; -- 返回 4-- 3. 取模：MOD(x, y)
SELECT MOD(10, 3) AS mod_result; -- 返回 1-- 4. 生成隨機數：RAND()
SELECT RAND() AS random_number; -- 返回一個 0 到 1 之間的隨機數-- 5. 四舍五入：ROUND(x, y)
SELECT ROUND(4.567, 2) AS round_result; -- 返回 4.57
日期函數
常用函數：
| 函數 | 功能 |
|---|---|
| CURDATE() | 返回當前日期 |
| CURTIME() | 返回當前時間 |
| NOW() | 返回當前日期和時間 |
| YEAR(date) | 獲取指定date的年份 |
| MONTH(date) | 獲取指定date的月份 |
| DAY(date) | 獲取指定date的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一個日期/時間值加上一個時間間隔expr后的時間值 |
| DATEDIFF(date1, date2) | 返回起始時間date1和結束時間date2之間的天數 |
示例：
-- 示例：MySQL 日期時間函數示例-- 1. 返回當前日期：CURDATE()
SELECT CURDATE() AS current_date;-- 2. 返回當前時間：CURTIME()
SELECT CURTIME() AS current_time;-- 3. 返回當前日期和時間：NOW()
SELECT NOW() AS current_datetime;-- 4. 獲取指定日期的年份：YEAR(date)
SELECT YEAR('2025-05-02') AS year_of_date; -- 返回 2025-- 5. 獲取指定日期的月份：MONTH(date)
SELECT MONTH('2025-05-02') AS month_of_date; -- 返回 5-- 6. 獲取指定日期的日期部分：DAY(date)
SELECT DAY('2025-05-02') AS day_of_date; -- 返回 2-- 7. 返回一個日期加上時間間隔后的新日期：DATE_ADD(date, INTERVAL expr type)
SELECT DATE_ADD('2025-05-02', INTERVAL 10 DAY) AS new_date; -- 返回 2025-05-12-- 8. 返回兩個日期之間的天數：DATEDIFF(date1, date2)
SELECT DATEDIFF('2025-05-02', '2025-04-22') AS date_diff; -- 返回 10
流程函數
常用函數：
| 函數 | 功能 |
|---|---|
| IF(value, t, f) | 如果value為true，則返回t，否則返回f |
| IFNULL(value1, value2) | 如果value1不為空，返回value1，否則返回value2 |
| CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END | 如果val1為true，返回res1，… 否則返回default默認值 |
| CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END | 如果expr的值等于val1，返回res1，… 否則返回default默認值 |
?示例：
-- 示例：MySQL 條件判斷函數示例-- 1. IF(value, t, f)：如果 value 為 true，則返回 t，否則返回 f
SELECT IF(5 > 3, 'True', 'False') AS if_example; -- 返回 'True'-- 2. IFNULL(value1, value2)：如果 value1 不為空，返回 value1，否則返回 value2
SELECT IFNULL(NULL, 'Default Value') AS ifnull_example; -- 返回 'Default Value'-- 3. CASE WHEN [val1] THEN [res1] … ELSE [default] END：如果 val1 為 true，返回 res1，… 否則返回 default 默認值
SELECT CASE WHEN 5 > 3 THEN 'Greater' WHEN 5 < 3 THEN 'Smaller' ELSE 'Equal' END AS case_when_example; -- 返回 'Greater'-- 4. CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END：如果 expr 的值等于 val1，返回 res1，… 否則返回 default 默認值
SELECT CASE 5 WHEN 3 THEN 'Three' WHEN 5 THEN 'Five' ELSE 'Other' END AS case_example; -- 返回 'Five'
約束
分類：
| 約束 | 描述 | 關鍵字 |
|---|---|---|
| 非空約束 | 限制該字段的數據不能為null | NOT NULL |
| 唯一約束 | 保證該字段的所有數據都是唯一、不重復的 | UNIQUE |
| 主鍵約束 | 主鍵是一行數據的唯一標識，要求非空且唯一 | PRIMARY KEY |
| 默認約束 | 保存數據時，如果未指定該字段的值，則采用默認值 | DEFAULT |
| 檢查約束（8.0.1版本后） | 保證字段值滿足某一個條件 | CHECK |
| 外鍵約束 | 用來讓兩張圖的數據之間建立連接，保證數據的一致性和完整性 | FOREIGN KEY |
約束是作用于表中字段上的，可以再創建表/修改表的時候添加約束。
常用約束
| 約束條件 | 關鍵字 |
|---|---|
| 主鍵 | PRIMARY KEY |
| 自動增長 | AUTO_INCREMENT |
| 不為空 | NOT NULL |
| 唯一 | UNIQUE |
| 邏輯條件 | CHECK |
| 默認值 | DEFAULT |
示例：
create table user(id int primary key auto_increment,name varchar(10) not null unique,age int check(age > 0 and age < 120),status char(1) default '1',gender char(1)
);外鍵約束
外鍵（Foreign Key）是數據庫中用來確保數據一致性和完整性的一種約束。外鍵用于在兩個表之間建立關聯關系，使得一個表的某一列（或一組列）指向另一個表的主鍵（Primary Key）或唯一鍵（Unique Key）。外鍵的作用是確保表之間的數據關系有效，同時避免因數據的錯誤操作導致不一致的情況。
添加外鍵：
CREATE TABLE 表名(字段名 字段類型,...[CONSTRAINT] [外鍵名稱] FOREIGN KEY(外鍵字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名) REFERENCES 主表(主表列名);-- 例子
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);刪除外鍵：
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名;刪除/更新行為：
| 行為 | 說明 |
|---|---|
| NO ACTION | 當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則不允許刪除/更新（與RESTRICT一致） |
| RESTRICT | 當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則不允許刪除/更新（與NO ACTION一致） |
| CASCADE | 當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則也刪除/更新外鍵在子表中的記錄 |
| SET NULL | 當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則設置子表中該外鍵值為null（要求該外鍵允許為null） |
| SET DEFAULT | 父表有變更時，子表將外鍵設為一個默認值（Innodb不支持） |
更改刪除/更新行為：
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段) REFERENCES 主表名(主表字段名) ON UPDATE 行為 ON DELETE 行為;示例：
創建 students 表
CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, -- 學生ID，自增長主鍵name VARCHAR(100) NOT NULL, -- 學生姓名age INT -- 學生年齡
);students 表存儲每個學生的基本信息，并且包含一個主鍵 id，用于唯一標識每個學生。創建 enrollments 表
CREATE TABLE enrollments (enrollment_id INT AUTO_INCREMENT PRIMARY KEY, -- 選課記錄IDstudent_id INT, -- 學生ID（外鍵）course_name VARCHAR(100), -- 課程名稱enrollment_date DATE, -- 選課日期FOREIGN KEY (student_id) REFERENCES students(id) -- 外鍵約束
);enrollments 表存儲學生的選課信息，其中包含一個外鍵 student_id，它引用 students 表的 id 字段。這意味著每一條選課記錄都必須對應一個存在的學生。在這個示例中：
-
students表中的id是主鍵，確保每個學生都是唯一的。 -
enrollments表中的student_id是外鍵，引用students表中的id。這表示enrollments表中的每一條記錄都應該關聯一個有效的學生。
我們向這兩個表中插入一些數據：
向 students 表插入數據
INSERT INTO students (name, age) VALUES
('Alice', 20),
('Bob', 22),
('Charlie', 21);向 enrollments 表插入數據
INSERT INTO enrollments (student_id, course_name, enrollment_date) VALUES
(1, 'Mathematics', '2025-05-01'), -- Alice 選修了數學
(2, 'Computer Science', '2025-05-01'), -- Bob 選修了計算機科學
(1, 'Physics', '2025-05-02'); -- Alice 選修了物理外鍵約束的作用
如果你嘗試在 enrollments 表中插入一個沒有對應 students 表中 id 的 student_id，例如 student_id = 4，數據庫會拋出錯誤，因為外鍵約束確保了 enrollments 表中的每個學生必須在 students 表中存在。
-- 這會報錯，因為 student_id 4 不存在
INSERT INTO enrollments (student_id, course_name, enrollment_date) VALUES (4, 'Biology', '2025-05-03');如果你刪除了 students 表中的某個學生，例如刪除 Alice（id = 1），那么如果沒有適當的外鍵約束（如 ON DELETE CASCADE），刪除操作會失敗，因為外鍵約束要求必須先刪除子表中的關聯數據。
-- 刪除 Alice（id = 1）將失敗，除非外鍵添加了級聯刪除
DELETE FROM students WHERE id = 1;多表查詢
多表關系
- 一對多（多對一）
- 多對多
- 一對一
一對一
案例：用戶與用戶詳情
關系：一對一關系，多用于單表拆分，將一張表的基礎字段放在一張表中，其他詳情字段放在另一張表中，以提升操作效率
實現：在任意一方加入外鍵，關聯另外一方的主鍵，并且設置外鍵為唯一的（UNIQUE）
-- 用戶主表（基礎信息表）
CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用戶ID',username VARCHAR(50) NOT NULL UNIQUE COMMENT '用戶名',email VARCHAR(100) NOT NULL UNIQUE COMMENT '電子郵箱',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶基礎表';-- 用戶詳情表（擴展字段表）
CREATE TABLE user_detail (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵ID',user_id INT NOT NULL UNIQUE COMMENT '關聯的用戶ID（外鍵唯一，確保一對一）',real_name VARCHAR(100) DEFAULT NULL COMMENT '真實姓名',address TEXT COMMENT '地址',phone VARCHAR(20) COMMENT '手機號',birthday DATE COMMENT '生日',FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶詳情表';
一對多
案例：部門與員工
關系：一個部門對應多個員工，一個員工對應一個部門
實現：在多的一方建立外鍵，指向一的一方的主鍵
-- 部門表（Department）
CREATE TABLE department (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部門ID',name VARCHAR(100) NOT NULL UNIQUE COMMENT '部門名稱',location VARCHAR(100) DEFAULT NULL COMMENT '部門所在地'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部門表';-- 員工表（Employee）
CREATE TABLE employee (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '員工ID',name VARCHAR(100) NOT NULL COMMENT '員工姓名',email VARCHAR(100) UNIQUE COMMENT '員工郵箱',hire_date DATE COMMENT '入職日期',department_id INT NOT NULL COMMENT '所屬部門ID',FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='員工表';
多對多
案例：學生與課程
關系：一個學生可以選多門課程，一門課程也可以供多個學生選修
實現：建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵
-- 學生表（Student）
CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '學生ID',name VARCHAR(100) NOT NULL COMMENT '學生姓名',enrollment_year INT COMMENT '入學年份'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='學生表';-- 課程表（Course）
CREATE TABLE course (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '課程ID',name VARCHAR(100) NOT NULL COMMENT '課程名稱',credit INT DEFAULT 1 COMMENT '學分'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='課程表';-- 中間表：學生-課程關系表（Student_Course）
CREATE TABLE student_course (student_id INT NOT NULL COMMENT '學生ID',course_id INT NOT NULL COMMENT '課程ID',selected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '選課時間',PRIMARY KEY (student_id, course_id),FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='學生與課程關聯表';
多表查詢
合并查詢（笛卡爾積，會展示所有組合結果）：
select * from employee, dept;笛卡爾積：兩個集合A集合和B集合的所有組合情況（在多表查詢時，需要消除無效的笛卡爾積）
消除無效笛卡爾積：
select * from employee, dept where employee.dept = dept.id;內連接查詢 INNER JOIN
內連接查詢的是兩張表交集的部分
隱式內連接：
SELECT 字段列表 FROM 表1, 表2 WHERE 條件 ...;顯式內連接：
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ...;顯式性能比隱式高
-- 查詢員工姓名，及關聯的部門的名稱
-- 隱式
select e.name, d.name from employee as e, dept as d where e.dept = d.id;
-- 顯式
select e.name, d.name from employee as e inner join dept as d on e.dept = d.id;dept示例數據：
id name
1 技術部
2 人事部
3 市場部
employee示例數據：
id name dept
1 張三 1
2 李四 2
3 王五 1
查詢結果為：
e.name d.name
張三 技術部
李四 人事部
王五 技術部外連接查詢
左外連接：LEFT JOIN
查詢左表所有數據，以及兩張表交集部分數據
會返回左表(表1)中的所有記錄，如果右表(表2)中有匹配的，就一起返回；沒有匹配的，右表的字段為 NULL。
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ...;右外連接：RIGHT JOIN
查詢右表所有數據，以及兩張表交集部分數據
會返回右表(表2)中的所有記錄，如果左表(表1)中有匹配的，就一起返回；沒有匹配的，左表的字段為 NULL。
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ...;-- 左
select e.*, d.name from employee as e left outer join dept as d on e.dept = d.id;
select d.name, e.* from dept d left outer join emp e on e.dept = d.id; -- 這條語句與下面的語句效果一樣
-- 右
select d.name, e.* from employee as e right outer join dept as d on e.dept = d.id;員工表 employee：
id name dept
1 張三 1
2 李四 2
3 王五 3
部門表 dept：
id name
1 技術部
2 人事部
4 財務部LEFT JOIN，從員工出發，查詢結果
id name dept dept_name
1 張三 1 技術部
2 李四 2 人事部
3 王五 3 NULL
LEFT JOIN，從部門出發，查詢結果
dept_name id name dept
技術部 1 張三 1
人事部 2 李四 2
財務部 NULL NULL NULL
左連接可以查詢到沒有dept的employee，右連接可以查詢到沒有employee的dept自連接查詢
當前表與自身的連接查詢，自連接必須使用表別名
SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ...;自連接查詢，可以是內連接查詢，也可以是外連接查詢
員工表 employee：
id name manager
1 張三 NULL
2 李四 1
3 王五 2
4 趙六 NULL
張三和趙六沒有領導（manager 字段為 NULL）
李四的領導是張三（manager 字段是 1，即張三的 id）
王五的領導是李四（manager 字段是 2，即李四的 id）使用 INNER JOIN（默認的隱式連接）：
SELECT a.name AS 員工, b.name AS 領導
FROM employee a, employee b
WHERE a.manager = b.id;
結果：
員工 領導
李四 張三
王五 李四使用 LEFT JOIN（顯式連接）
SELECT a.name AS 員工, b.name AS 領導
FROM employee a
LEFT JOIN employee b ON a.manager = b.id;
結果：
員工 領導
張三 NULL
李四 張三
王五 李四
趙六 NULL聯合查詢 union, union all
把多次查詢的結果合并，形成一個新的查詢集
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...注意
- UNION ALL 會有重復結果，UNION 不會
- 聯合查詢比使用or效率高，不會使索引失效
子查詢
SQL語句中嵌套SELECT語句，稱謂嵌套查詢，又稱子查詢。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);子查詢外部的語句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一個
根據子查詢結果可以分為：
- 標量子查詢（子查詢結果為單個值）
- 列子查詢（子查詢結果為一列）
- 行子查詢（子查詢結果為一行）
- 表子查詢（子查詢結果為多行多列）
根據子查詢位置可分為：
- WHERE 之后
- FROM 之后
- SELECT 之后
標量子查詢
子查詢返回的結果是單個值（數字、字符串、日期等）。
常用操作符：- < > > >= < <=
部門表 dept：
id name
1 技術部
2 人事部
3 財務部
4 銷售部
員工表 employee：
id name dept entrydate
1 張三 4 2021-01-01
2 李四 4 2022-03-15
3 王五 2 2020-06-10
4 趙六 1 2023-02-01
5 錢七 4 2024-07-01-- 查詢銷售部所有員工
SELECT * FROM employee
WHERE dept = (SELECT id FROM dept WHERE name = '銷售部'
);
結果：
id name dept entrydate
1 張三 4 2021-01-01
2 李四 4 2022-03-15
5 錢七 4 2024-07-01
-- 查詢在“李四”入職之后的員工信息
SELECT * FROM employee
WHERE entrydate > (SELECT entrydate FROM employee WHERE name = '李四'
);
結果：
id name dept entrydate
4 趙六 1 2023-02-01
5 錢七 4 2024-07-01列子查詢
返回的結果是一列（可以是多行）。
常用操作符：
| 操作符 | 描述 |
|---|---|
| IN | 在指定的集合范圍內，多選一 |
| NOT IN | 不在指定的集合范圍內 |
| ANY | 子查詢返回列表中，有任意一個滿足即可 |
| SOME | 與ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查詢返回列表的所有值都必須滿足 |
部門表 dept：
id name
1 銷售部
2 市場部
3 財務部
4 研發部
員工表 employee：
id name dept salary entrydate
1 張三 1 6000 2021-01-01
2 李四 2 6500 2021-03-10
3 王五 3 7000 2022-06-01
4 趙六 3 7200 2023-02-20
5 錢七 4 8000 2022-09-10
6 孫八 4 8500 2023-10-01
7 周九 1 5000 2021-12-12-- 查詢銷售部和市場部的所有員工信息
SELECT * FROM employee
WHERE dept IN (SELECT id FROM dept WHERE name = '銷售部' OR name = '市場部'
);
解析：
子查詢得到：dept.id in (1, 2)
所以等價于 WHERE dept IN (1, 2)
結果：
id name dept salary entrydate
1 張三 1 6000 2021-01-01
2 李四 2 6500 2021-03-10
7 周九 1 5000 2021-12-12-- 查詢比財務部所有人工資都高的員工信息
SELECT * FROM employee
WHERE salary > ALL (SELECT salary FROM employee WHERE dept = (SELECT id FROM dept WHERE name = '財務部')
);
解析：
財務部員工工資：7000（王五）、7200（趙六）
所以條件是 salary > 7200
結果：
id name dept salary entrydate
5 錢七 4 8000 2022-09-10
6 孫八 4 8500 2023-10-01行子查詢
返回的結果是一行（可以是多列）。
常用操作符：=, <, >, IN, NOT IN
-- 查詢與xxx的薪資及直屬領導相同的員工信息
select * from employee where (salary, manager) = (12500, 1);
select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');員工表 employee：
id name salary manager dept
1 張三 10000 NULL 1
2 李四 12000 1 2
3 王五 12500 1 2
4 趙六 12500 1 3
5 錢七 12500 2 3
6 xxx 12500 1 4
7 孫八 13000 1 4-- 指定薪資和直屬領導的組合
SELECT * FROM employee
WHERE (salary, manager) = (12500, 1);
說明：
直接指定條件 (salary = 12500 AND manager = 1)
查詢結果：
id name salary manager dept
3 王五 12500 1 2
4 趙六 12500 1 3
6 xxx 12500 1 4-- 查找與 xxx 的薪資和直屬領導相同的員工信息
SELECT * FROM employee
WHERE (salary, manager) = (SELECT salary, manager FROM employee WHERE name = 'xxx'
);
說明：
子查詢得到 (12500, 1)
實際效果等同于前面一條語句
查詢結果：
id name salary manager dept
3 王五 12500 1 2
4 趙六 12500 1 3
6 xxx 12500 1 4表子查詢
返回的結果是多行多列
常用操作符：IN
員工表 employee：
id name job salary entrydate dept
1 張三 程序員 10000 2005-06-01 1
2 李四 程序員 12000 2006-02-01 2
3 xxx1 設計師 12500 2007-03-15 2
4 xxx2 程序員 10000 2008-04-20 3
5 趙六 程序員 10000 2009-05-10 1
6 錢七 設計師 12500 2010-06-01 2
7 孫八 產品經理 14000 2011-07-01 3
部門表 dept：
id name
1 開發部
2 設計部
3 產品部-- 查詢與 xxx1、xxx2 的職位和薪資相同的員工
SELECT * FROM employee
WHERE (job, salary) IN (SELECT job, salary FROM employee WHERE name = 'xxx1' OR name = 'xxx2'
);
說明：
xxx1 的 (job, salary) 是 (設計師, 12500)
xxx2 的 (job, salary) 是 (程序員, 10000)
所以匹配兩個組合的員工
id name job salary entrydate dept
1 張三 程序員 10000 2005-06-01 1
4 xxx2 程序員 10000 2008-04-20 3
5 趙六 程序員 10000 2009-05-10 1
3 xxx1 設計師 12500 2007-03-15 2
6 錢七 設計師 12500 2010-06-01 2事務
事務是一組操作的集合，事務會把所有操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要么同時成功，要么同時失敗。
一句話：事務就是一組必須“要么全做完，要么一個也不做”的數據庫操作。
比如：銀行轉賬操作 —— 張三轉賬給李四 1000 元，不能出現張三的錢扣了但李四沒收到的情況。
示例：
-- 1. 查詢張三賬戶余額
select * from account where name = '張三';
-- 2. 將張三賬戶余額-1000
update account set money = money - 1000 where name = '張三';
-- 此語句出錯后張三錢減少但是李四錢沒有增加
模擬sql語句錯誤
-- 3. 將李四賬戶余額+1000
update account set money = money + 1000 where name = '李四';?這時如果沒有事務：
-
張三的錢就被扣了?
-
李四的錢卻沒有增加?
-
錢“憑空消失”，數據錯誤了！
所以必須讓這步操作打包成一組事務，要么一起成功、要么一起失敗回滾。
如何使用事務？
事務基本操作
?開啟事務：
START TRANSACTION 或 BEGIN TRANSACTION;提交事務：
COMMIT;回滾事務：
ROLLBACK;方式一：關閉自動提交（手動控制提交）
數據庫默認執行完一條語句就自動提交（也就是每條語句都是一個小事務）。要實現多個語句作為一組事務，需要手動控制。
-- 1. 查看當前是否是自動提交模式（1 表示開啟自動提交）
SELECT @@AUTOCOMMIT;-- 2. 關閉自動提交（設置為 0）
SET @@AUTOCOMMIT = 0;-- 3. 執行一組事務操作
select * from account where name = '張三';
update account set money = money - 1000 where name = '張三';
update account set money = money + 1000 where name = '李四';-- 4. 提交事務（保存操作）
COMMIT;-- 如果發現中間出錯了，可以撤回
ROLLBACK;方式二：用 START TRANSACTION 開啟事務
這種方式更清晰、推薦用于正式開發。
START TRANSACTION; -- 或者用 BEGIN;-- 一組操作（作為一個事務）
SELECT * FROM account WHERE name = '張三';
UPDATE account SET money = money - 1000 WHERE name = '張三';
UPDATE account SET money = money + 1000 WHERE name = '李四';COMMIT; -- 提交成功，保存所有操作
-- ROLLBACK; -- 如果失敗，用這個撤銷事務的四大特性：
- 原子性(Atomicity)：事務是不可分割的最小操作但愿，要么全部成功，要么全部失敗
- 一致性(Consistency)：事務完成時，必須使所有數據都保持一致狀態
- 隔離性(Isolation)：數據庫系統提供的隔離機制，保證事務在不受外部并發操作影響的獨立環境下運行
- 持久性(Durability)：事務一旦提交或回滾，它對數據庫中的數據的改變就是永久的
并發事務：
| 題 | 描述 |
|---|---|
| 臟讀 | 一個事務讀到另一個事務還沒提交的數據 |
| 不可重復讀 | 一個事務先后讀取同一條記錄，但兩次讀取的數據不同 |
| 幻讀 | 一個事務按照條件查詢數據時，沒有對應的數據行，但是再插入數據時，又發現這行數據已經存在 |
這三個問題的詳細演示：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=55cd
并發事務隔離級別：
| 離級別 | 臟讀 | 不可重復讀 | 幻讀 |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable Read(默認) | × | × | √ |
| Serializable | × | × | × |
- √表示在當前隔離級別下該問題會出現
- Serializable 性能最低；Read uncommitted 性能最高，數據安全性最差
查看事務隔離級別：
SELECT @@TRANSACTION_ISOLATION;?設置事務隔離級別：
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };SESSION 是會話級別，表示只針對當前會話有效，GLOBAL 表示對所有會話有效
-
SESSION：只影響當前會話（當前連接）的設置，別人不受影響。 -
GLOBAL：影響整個數據庫系統的設置，所有連接都會受到影響。
什么是“會話”？
你每次用 MySQL 客戶端、Navicat、Java 程序、Python 腳本 連接數據庫，都會建立一個 “會話（SESSION）”，也叫一個“連接”。
每個連接之間是 相互獨立的，就像每個人登錄網站時都是自己的一套賬號環境。
示例：AUTOCOMMIT 設置
SELECT @@AUTOCOMMIT; -- 查看當前會話的自動提交設置
SELECT @@GLOBAL.AUTOCOMMIT; -- 查看全局的自動提交設置
SET SESSION AUTOCOMMIT = 0;
你自己這一次連接變成了“手動提交事務”，但別人連進來仍然是“自動提交”。SET GLOBAL AUTOCOMMIT = 0;
所有新連接進來的用戶，默認都是“手動提交事務”狀態了。注意：GLOBAL 設置完后只影響新連接的會話，老連接不會變。
