mysql中的通用語法及分類

MySQL 是一種廣泛使用的關系型數據庫管理系統（RDBMS），其語法設計遵循 SQL 標準，但也有一些特有的擴展。以下從??通用語法規范??和??SQL 語句分類??兩個維度系統梳理 MySQL 的核心語法體系。

一、MySQL 通用語法規范

通用語法是編寫 SQL 語句時需遵守的基礎規則，確保 MySQL 能正確解析和執行代碼。

1. 注釋（Comments）

用于解釋代碼邏輯，不會被 MySQL 執行。支持三種注釋方式：

??單行注釋??：以 -- 或 # 開頭（-- 后需至少一個空格）。

SELECT * FROM users; -- 查詢所有用戶（-- 后有空格）
SELECT * FROM users; # 另一種單行注釋方式

??多行注釋??：以 /* 開頭、*/ 結尾，可跨越多行。

/*功能：創建用戶表作者：張三時間：2023-10-01
*/
CREATE TABLE users (...);

2. 分隔符（Delimiter）

默認語句以分號 ; 結尾，但在定義存儲過程、觸發器等復合語句時，需臨時修改分隔符以避免沖突。

DELIMITER $$  -- 將分隔符臨時改為 $$
CREATE PROCEDURE GetUser()
BEGINSELECT * FROM users;
END$$
DELIMITER ;  -- 恢復默認分隔符為 ;

3. 標識符（Identifier）

用于命名數據庫對象（如數據庫、表、列、索引等），需遵守以下規則：

??合法字符??：字母（a-z, A-Z）、數字（0-9）、下劃線（_）、美元符號（$），或 Unicode 字符（如中文）。
??長度限制??：默認最大長度為 64 字符（不同存儲引擎可能略有差異，如 InnoDB 支持 64 字節，MyISAM 支持 64 字符）。
??區分大小寫??：取決于操作系統（Linux 下默認區分表名大小寫，Windows 不區分；列名始終不區分）。
??保留字處理??：若使用 MySQL 保留字（如 order、user）作為標識符，需用反引號 ` 包裹。
```
CREATE TABLE `order` (id INT);  -- 正確（使用反引號轉義保留字）
```

4. 變量（Variables）

MySQL 支持多種類型的變量，用于存儲臨時數據或配置參數：

??用戶變量??：以 @ 開頭，會話級作用域（僅當前連接有效）。

SET @username = 'admin';  -- 賦值
SELECT @username;         -- 查詢（輸出 'admin'）

??系統變量??：控制 MySQL 運行行為，分為全局（@@global.var_name）和會話（@@session.var_name/@@var_name）級。

SHOW VARIABLES LIKE 'max_connections';  -- 查看全局最大連接數
SET GLOBAL max_connections = 200;       -- 修改全局變量（需管理員權限）

??局部變量??：在存儲過程、函數中使用，以 DECLARE 聲明，僅在塊內有效。
```
DELIMITER 
```

CREATE PROCEDURE CalcSum(IN a INT, IN b INT, OUT sum INT)BEGINDECLARE temp INT;  -- 局部變量SET temp = a + b;SET sum = temp;END$$DELIMITER ;```#### 5. 流程控制語句
主要用于存儲過程、函數、觸發器等復合邏輯中，類似編程語言的控制結構：
- **條件判斷**：`IF...ELSEIF...ELSE`、`CASE...WHEN...ELSE...END CASE`。```sql-- IF 示例IF score > 90 THENSET grade = 'A';ELSEIF score > 80 THENSET grade = 'B';ELSESET grade = 'C';END IF;-- CASE 示例CASE WHEN status = 1 THEN '啟用'WHEN status = 0 THEN '禁用'ELSE '未知'END CASE;```
- **循環**：`LOOP`、`WHILE...DO`、`REPEAT...UNTIL...END REPEAT`。```sql-- WHILE 循環（累加 1 到 10）SET i = 1;WHILE i <= 10 DOSET sum = sum + i;SET i = i + 1;END WHILE;-- REPEAT 循環（直到條件滿足）REPEATSET count = count + 1;UNTIL count >= 5 END REPEAT;```### 二、SQL 語句分類
SQL（結構化查詢語言）按功能可分為五大類，MySQL 完全支持并擴展了部分語法。#### 1. DDL（數據定義語言，Data Definition Language）
用于定義或修改數據庫對象（如數據庫、表、索引、視圖等）的結構。| 語句          | 功能描述                                                                 | 示例                                                                 |
|---------------|--------------------------------------------------------------------------|----------------------------------------------------------------------|
| `CREATE`      | 創建數據庫對象（數據庫、表、索引、視圖等）                               | `CREATE DATABASE db1;`<br>`CREATE TABLE users (id INT PRIMARY KEY);` |
| `ALTER`       | 修改已有數據庫對象的結構                                                 | `ALTER TABLE users ADD COLUMN age INT;`<br>`ALTER TABLE users DROP COLUMN age;` |
| `DROP`        | 刪除數據庫對象                                                           | `DROP DATABASE db1;`<br>`DROP TABLE users;`                          |
| `TRUNCATE`    | 清空表數據（比 `DELETE` 更快，且無法回滾）                               | `TRUNCATE TABLE users;`                                              |
| `COMMENT`     | 為對象添加注釋                                                           | `COMMENT ON TABLE users IS '用戶信息表';`（MySQL 不直接支持此語法，需通過元數據表設置） |
| `RENAME`      | 重命名對象                                                               | `RENAME TABLE old_table TO new_table;`                               |**注意**：`TRUNCATE` 與 `DELETE` 的區別：  
- `TRUNCATE` 是 DDL，直接釋放表空間，不記錄逐行日志（僅記錄頁刪除），無法通過事務回滾恢復；  
- `DELETE` 是 DML，逐行刪除并記錄日志，可通過 `ROLLBACK` 回滾（若在事務中）。#### 2. DML（數據操作語言，Data Manipulation Language）
用于對表中的數據進行增、刪、改操作（不包括查詢）。| 語句          | 功能描述                                                                 | 示例                                                                 |
|---------------|--------------------------------------------------------------------------|----------------------------------------------------------------------|
| `INSERT`      | 向表中插入新數據                                                         | `INSERT INTO users (name, age) VALUES ('張三', 25);`<br>`INSERT INTO users VALUES (1, '李四', 30);`（全列插入） |
| `UPDATE`      | 修改表中已有數據                                                         | `UPDATE users SET age = 26 WHERE name = '張三';`                     |
| `DELETE`      | 刪除表中符合條件的數據                                                   | `DELETE FROM users WHERE age < 18;`                                  |#### 3. DQL（數據查詢語言，Data Query Language）
MySQL 中特指 `SELECT` 語句，用于從表中查詢數據，是最復雜也最常用的語法。**核心語法結構**：
```sql
SELECT [DISTINCT] 列名/表達式 [AS 別名]
FROM 表名 [別名]
[WHERE 條件]
[GROUP BY 分組列 HAVING 分組過濾條件]
[ORDER BY 排序列 [ASC/DESC]]
[LIMIT 限制行數];
```**關鍵子句說明**：
- **`WHERE`**：過濾行（支持比較運算符、邏輯運算符、范圍查詢、子查詢等）。  示例：`SELECT * FROM users WHERE age BETWEEN 18 AND 30 AND gender = '女';`
- **`GROUP BY`**：按列分組統計（常配合聚合函數 `COUNT`、`SUM`、`AVG` 等）。  示例：`SELECT gender, COUNT(*) AS user_count FROM users GROUP BY gender;`
- **`HAVING`**：對分組后的結果過濾（與 `WHERE` 類似，但只能用于 `GROUP BY` 后）。  示例：`SELECT gender, AVG(age) AS avg_age FROM users GROUP BY gender HAVING avg_age > 25;`
- **`ORDER BY`**：按列排序（升序 `ASC` 默認，降序 `DESC`）。  示例：`SELECT * FROM users ORDER BY age DESC, name ASC;`
- **`LIMIT`**：限制返回行數（支持 `LIMIT n` 或 `LIMIT offset, n`）。  示例：`SELECT * FROM users LIMIT 10;`（前10行）<br>`SELECT * FROM users LIMIT 0, 10;`（同上）<br>`SELECT * FROM users LIMIT 10, 20;`（第11-30行）**高級查詢**：
- **連接查詢**：關聯多表數據（`INNER JOIN`、`LEFT JOIN`、`RIGHT JOIN`、`FULL JOIN`（MySQL 不直接支持，需用 `UNION` 模擬））。  示例：`SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id;`
- **子查詢**：嵌套 `SELECT` 語句（支持標量子查詢、行子查詢、列子查詢、表子查詢）。  示例：`SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);`
- **聯合查詢**：合并多個 `SELECT` 結果（`UNION` 去重，`UNION ALL` 不去重）。  示例：`(SELECT name FROM users) UNION (SELECT product_name FROM products);`#### 4. DCL（數據控制語言，Data Control Language）
用于管理數據庫用戶的訪問權限和角色。| 語句          | 功能描述                                                                 | 示例                                                                 |
|---------------|--------------------------------------------------------------------------|----------------------------------------------------------------------|
| `GRANT`       | 授予用戶或角色權限                                                       | `GRANT SELECT, INSERT ON db1.users TO 'user1'@'localhost';`          |
| `REVOKE`      | 收回已授予的權限                                                         | `REVOKE DELETE ON db1.users FROM 'user1'@'localhost';`               |
| `SET PASSWORD`| 修改用戶密碼                                                             | `SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('new_pass');`（MySQL 5.7+ 已棄用 `PASSWORD()`，直接用 `SET PASSWORD = 'new_pass';`） |#### 5. TCL（事務控制語言，Transaction Control Language）
用于管理數據庫事務（保證數據的一致性和完整性）。| 語句                | 功能描述                                                                 | 示例                                                                 |
|---------------------|--------------------------------------------------------------------------|----------------------------------------------------------------------|
| `START TRANSACTION` | 開始一個事務（等價于 `BEGIN`）                                           | `START TRANSACTION;`                                                 |
| `COMMIT`            | 提交事務（所有操作永久生效）                                             | `COMMIT;`                                                            |
| `ROLLBACK`          | 回滾事務（撤銷所有未提交的操作）                                         | `ROLLBACK;`                                                          |
| `SAVEPOINT`         | 設置事務保存點（可部分回滾）                                             | `SAVEPOINT sp1;`<br>`ROLLBACK TO SAVEPOINT sp1;`（回滾到 `sp1`）     |**事務特性（ACID）**：  
- **原子性（Atomicity）**：事務中的操作要么全成功，要么全失敗。  
- **一致性（Consistency）**：事務前后數據狀態合法（如轉賬后總金額不變）。  
- **隔離性（Isolation）**：事務間互不干擾（通過隔離級別控制，如 `READ UNCOMMITTED`、`READ COMMITTED`、`REPEATABLE READ`（MySQL 默認）、`SERIALIZABLE`）。  
- **持久性（Durability）**：提交的事務數據永久保存（通過日志 `redo log` 保證）。### 三、其他高級語法
除上述基礎語法外，MySQL 還支持以下高級功能：#### 1. 存儲過程（Stored Procedure）
封裝多條 SQL 語句，可重復調用并支持邏輯控制。
```sql
DELIMITER

CREATE PROCEDURE GetUserCount(OUT total INT)
BEGIN
SELECT COUNT(*) INTO total FROM users;
END$$
DELIMITER ;

CALL GetUserCount(@count); -- 調用存儲過程
SELECT @count; -- 輸出結果


#### 2. 觸發器（Trigger）
在特定事件（`INSERT`、`UPDATE`、`DELETE`）發生時自動執行的代碼塊。
```sql
-- 創建觸發器（插入用戶后記錄日志）
DELIMITER 
$$
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGININSERT INTO user_log (action, user_id, create_time)VALUES ('INSERT', NEW.id, NOW());
END$$
DELIMITER ;

3. 事件（Event）

定時執行的任務（需開啟事件調度器 event_scheduler）。

-- 開啟事件調度器（臨時生效）
SET GLOBAL event_scheduler = ON;-- 創建每日凌晨清理過期日志的事件
CREATE EVENT clean_expired_logs
ON SCHEDULE EVERY 1 DAY STARTS '2023-10-01 03:00:00'
ON COMPLETION PRESERVE
DODELETE FROM logs WHERE expire_time < NOW();

4. 窗口函數（Window Functions，MySQL 8.0+ 支持）

用于復雜數據分析（如排名、累計求和等），不改變結果集行數。

-- 計算每個部門的工資排名
SELECT name, department, salary,RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

總結

MySQL 的語法體系以 SQL 標準為基礎，結合了豐富的擴展功能。掌握通用語法（注釋、分隔符、標識符等）是編寫正確 SQL 的前提，而熟練使用 DDL/DML/DQL/DCL/TCL 及高級對象（存儲過程、觸發器等）則是實現復雜業務邏輯的關鍵。實際開發中需根據場景選擇合適的語法和優化策略（如索引優化、批量操作等），以提升數據庫性能。