MySQL 零距離實戰筆記：數據表 CRUD、運算符函數、查詢進階與安全加密完全指南
本文整理自《與 MySQL 零距離接觸》視頻課程學習筆記，系統梳理數據庫/表/行/列四個維度的 CRUD 操作、核心運算符與函數庫（字符、數值、比較、時間日期、信息、聚合、加密）、查詢進階語法，為 MySQL 入門開發者提供結構化速查手冊。
一、四大維度 CRUD 操作速查表
1. 表級操作
| 操作 | 語法 |
|---|
| 創建 | CREATE TABLE table_name (...) |
| 刪除 | DROP TABLE table_name |
| 重命名 | ALTER TABLE old_name RENAME TO new_name |
| 查看結構 | SELECT * FROM table_name / DESC table_name / SHOW COLUMNS FROM table_name |
2. 行級操作 (數據操作 DML)
| 操作 | 語法 |
|---|
| 插入 (方式 1) | INSERT INTO table_name (col1, col2) VALUES (val1, val2) |
| 插入 (方式 2) | INSERT INTO table_name SET col1=val1, col2=val2 |
| 刪除 | DELETE FROM table_name WHERE condition |
| 更新 | UPDATE table_name SET col1=val1, col2=val2 WHERE condition |
| 查詢 | SELECT col1, col2 FROM table_name WHERE condition |
3. 列級操作 (結構變更 DDL)
| 操作 | 語法 |
|---|
| 增加列 | ALTER TABLE table_name ADD column_name datatype |
| 刪除列 | ALTER TABLE table_name DROP column_name |
| 修改列 (CHANGE) | ALTER TABLE table_name CHANGE old_name new_name datatype |
| 修改屬性 (MODIFY) | ALTER TABLE table_name MODIFY column_name datatype |
| 查看列 | SHOW COLUMNS FROM table_name |
二、核心運算符與函數庫
1. 字符函數
| 函數 | 說明 | 示例 |
|---|
CONCAT(str1, str2, ...) | 字符串拼接 | SELECT CONCAT('hha','-','hdf') → 'hha-hdf' |
CONCAT_WS(sep, str1, str2, ...) | 帶分隔符拼接 | SELECT CONCAT_WS('=','A','B','C') → 'A=B=C' |
FORMAT(num, decimals) | 格式化數字 (千分位、小數位) | SELECT FORMAT(125060.752, 2) → '125,060.75' |
LOWER(str) / UPPER(str) | 大小寫轉換 | SELECT LOWER('HIIHO') → 'hiihO' |
LEFT(str, len) / RIGHT(str, len) | 左/右截取指定長度 | SELECT LEFT('fsfds', 2) → 'fs' |
LENGTH(str) | 字節長度 | SELECT LENGTH(' fsfds ') |
LTRIM(str) / RTRIM(str) / TRIM(str) | 去除左/右/兩側空格 | SELECT TRIM(' fsfds ') → 'fsfds' |
TRIM([BOTH|LEADING|TRAILING] 'chars' FROM str) | 去除指定字符 | SELECT TRIM(LEADING '?' FROM '?fs?fds?') |
SUBSTRING(str, pos, len) / SUBSTRING(str, pos) | 子串提取 | SELECT SUBSTRING('fsfds', 2, 3) → 'sfd' |
REPLACE(str, from_str, to_str) | 字符串替換 | SELECT REPLACE('fsgasgsfa','s','t') → 'ftgatgtfa' |
LIKE | 模糊匹配 | SELECT 'myufsd' LIKE 'm%' → 1 (真) |
通配符： % 匹配任意長度字符 (含 0) | _ 匹配單個字符
轉義： SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1' (用 '1' 轉義 '%')
2. 數值運算符
DIV：整數除法 (如 10 DIV 3 = 3)- 標準算術：
+, -, *, /, % (取模)
3. 比較運算符與函數
BETWEEN ... AND ... / NOT BETWEEN ... AND ...IN (...) / NOT IN (...)=, <=> (NULL 安全等於), !=, <>, <, <=, >, >=IS NULL / IS NOT NULLGREATEST() / LEAST() (取最大/最小值)
4. 時間日期函數
| 函數 | 說明 | 示例 |
|---|
NOW() | 當前日期時間 | 2024-01-15 10:30:45 |
CURDATE() | 當前日期 | 2024-01-15 |
CURTIME() | 當前時間 | 10:30:45 |
DATE_ADD(date, INTERVAL expr unit) | 日期加減間隔 | DATE_ADD(NOW(), INTERVAL 1 DAY) |
DATEDIFF(date1, date2) | 日期差 (天數) | DATEDIFF('2024-01-15', '2024-01-10') → 5 |
DATE_FORMAT(date, format) | 日期格式化輸出 | DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') |
5. 信息函數
| 函數 | 說明 |
|---|
CONNECTION_ID() | 當前連接 ID |
DATABASE() | 當前默認數據庫 |
LAST_INSERT_ID() | 上一條 INSERT 生成的自增 ID |
USER() | 當前用戶@主機 |
VERSION() | MySQL 版本號 |
6. 聚合函數
COUNT(expr)：非 NULL 個數 / COUNT(*)：行數SUM(expr)、AVG(expr)、MIN(expr)、MAX(expr)GROUP_CONCAT(expr)：分組拼接字符串- 配合
GROUP BY、HAVING 使用
7. 加密函數
| 函數 | 說明 | 特點 |
|---|
MD5(str) | MD5 雜湊 (128 位) | 不可逆、定長 32 十六進制字符、碰撞風險較高 |
PASSWORD(str) | MySQL 密碼加密 (內部用) | 版本相關算法，**不建議應用層使用** |
SHA1(str) / SHA2(str, hash_len) | SHA 雜湊族 | 更安全，推薦 SHA2-256/512 |
AES_ENCRYPT/DECRYPT | 對稱加密 | 可逆，需鍵管理 |
三、查詢進階語法
1. 別名
SELECT id AS userId, username AS uname FROM users;
2. 分組查詢
SELECT sex FROM users GROUP BY sex;
3. 分組過濾 (HAVING)
條件：聚合函數 或 HAVING 後字段出現在 SELECT 列表中
SELECT age, sex FROM users GROUP BY sex HAVING age > 2;
4. 排序
SELECT * FROM users ORDER BY id DESC; -- DESC 降序，默認 ASC 升序
5. 限制結果集
SELECT * FROM users LIMIT 2; -- 前 2 條
SELECT * FROM users LIMIT 2, 2; -- 跳過 2 條，取 2 條 (第 3-4 條)
四、DML 進階與多表查詢
1. 子查詢插入
INSERT INTO test(username) SELECT username FROM users WHERE age > 27;
2. 更新與子查詢
IN / NOT INEXISTS / NOT EXISTS (相關子查詢，通常性能更優)
3. 多表連接類型
| 類型 | 關鍵字 | 說明 |
|---|
| 內連接 | INNER JOIN / JOIN / CROSS JOIN | MySQL 中三者等價，僅返回匹配行 |
| 左外連接 | LEFT [OUTER] JOIN | 左表全量，右表匹配或 NULL |
| 右外連接 | RIGHT [OUTER] JOIN | 右表全量，左表匹配或 NULL |
SELECT u.username, u.age, u.sex
FROM user1 u
INNER JOIN user2 u2 ON u.id = u2.id;
五、安全編碼最佳實踐提醒
- 防 SQL 注入： 應用層**強制使用參數化查詢 (PreparedStatement)**，拼接 SQL 絕對禁止。
- 密碼存儲： **禁用
MD5/PASSWORD()**，採用 bcrypt/scrypt/Argon2 或 PBKDF2 (高計算成本、自帶鹽值)。
- 敏感數據加密： 應用層加密落庫 (AES-GCM 等)，數據庫僅存密文，鍵由 KMS 管理。
- 最小權限： 業務賬號僅授予必要增刪改查權限，嚴禁
DROP/ALTER/GRANT。
- 審計日志： 開啟 Binlog (ROW 格式) + 審計插件 (MariaDB Audit Plugin / MySQL Enterprise Audit) 記錄所有 DML/DDL。
六、總結
MySQL 基礎操作看似簡單，但細節決定質量：CHAR vs VARCHAR 選型、DATETIME vs TIMESTAMP 時區語義、LEFT JOIN 驅動表選擇、LIMIT 分頁深度陷阱、聚合函數 NULL 語義、加密函數安全等級。建立結構化知識體系，配合 EXPLAIN 驗證執行計劃，養成參數化查詢肌肉記憶，是從「會用」到「用好」的必經之路。
