前言

本文系統整理 MySQL 核心 DDL、DML、DQL、DCL 語法與實戰範例，涵蓋建庫建表、數據操作、條件查詢、聚合分析、多表連接、聯合查詢、事務控制等高頻操作，適合初學者速查與開發人員備忘。

一、連接與庫表管理

1. 連接數據庫

mysql -u username -p -h host -P port

2. 庫操作

CREATE DATABASE ceshi CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
DROP DATABASE ceshi;
SHOW DATABASES;
USE ceshi;

3. 表操作

-- 建表
CREATE TABLE students (
  sid INT(10) AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  course VARCHAR(255),
  score INT(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 修改表編碼
ALTER TABLE students DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 刪表
DROP TABLE students;

二、數據操作（DML）

1. 插入數據

-- 單條
INSERT INTO students (name, course, score) VALUES ('張三', '數學', 95);

-- 多條（VALUES 列表）
INSERT INTO students (name, course, score) VALUES
('李四', '英語', 88),
('王五', '物理', 92);

-- 從其他表複製（指定列）
INSERT INTO students (name, course, score)
SELECT name, course, score FROM old_students WHERE score > 90;

-- 多表聯合複製（UNION ALL 保留重複，UNION 去重）
INSERT INTO target (a,b,c)
SELECT a,b,c FROM src1 WHERE cond1
UNION ALL
SELECT a,b,c FROM src2 WHERE cond2;

2. 查詢與排序

-- 全量查詢
SELECT * FROM students;

-- 條件 + 排序
SELECT * FROM students WHERE score > 90 ORDER BY score DESC;  -- 降序
SELECT * FROM students ORDER BY score ASC;  -- 升序（默認）

3. 分組聚合

-- 按課程分組，查詢每門課最高分
SELECT course, MAX(score) AS max_score FROM students GROUP BY course;

-- HAVING 過濾分組結果
SELECT course, AVG(score) AS avg_score
FROM students
GROUP BY course
HAVING AVG(score) > 80;

-- 多字段分組
SELECT course, name, COUNT(*) FROM students GROUP BY course, name;

4. 更新與刪除

-- 更新（必帶 WHERE）
UPDATE students SET score = 100 WHERE sid = 1;

-- 刪除（必帶 WHERE）
DELETE FROM students WHERE sid = 1;

-- 別名與字符串拼接
SELECT CONCAT('姓名：', name) AS info FROM students;

三、進階查詢

1. 條件運算符

WHERE score BETWEEN 80 AND 100
WHERE course IN ('數學', '物理')
WHERE name LIKE '張%'
WHERE remark IS NOT NULL
WHERE id <> 5

2. 聚合函數

COUNT(*)      -- 行數
SUM(score)    -- 總分
AVG(score)    -- 平均分
MAX(score)    -- 最高分
MIN(score)    -- 最低分

3. 多表連接

假設三表：yg(員工)、gs(工作)、dz(單位地址)

-- 內連接（僅返回匹配行）
SELECT yg.name, gs.title, dz.address
FROM yg
JOIN gs ON yg.name = gs.name
JOIN dz ON gs.unit = dz.unit;

-- 左外連接（保留左表所有行）
SELECT yg.name, gs.title
FROM yg
LEFT JOIN gs ON yg.name = gs.name;

-- 右外連接
SELECT gs.title, yg.name
FROM yg
RIGHT JOIN gs ON yg.name = gs.name;

-- MySQL 不支持 FULL OUTER JOIN，可用 UNION 模擬
SELECT * FROM yg LEFT JOIN gs ON yg.name = gs.name
UNION
SELECT * FROM yg RIGHT JOIN gs ON yg.name = gs.name;

4. 聯合查詢 UNION

-- 合併男/女學生表，去重
SELECT * FROM nan UNION SELECT * FROM nv;

-- 保留重複行
SELECT * FROM nan UNION ALL SELECT * FROM nv;

-- 將結果建表
CREATE TABLE all_students SELECT * FROM nan UNION SELECT * FROM nv;

四、事務控制

SET autocommit = 0;
START TRANSACTION;
INSERT INTO t1 VALUES (1, 'a');
UPDATE t2 SET val = val - 100 WHERE id = 1;
COMMIT;  -- 或 ROLLBACK;

• InnoDB 支持事務，MyISAM 不支持。
• SELECT ... FOR UPDATE 加排他鎖（需在事務中）。

五、視圖、存儲過程、觸發器速覽

-- 視圖
CREATE VIEW v_high_score AS SELECT * FROM students WHERE score > 90;

-- 存儲過程
DELIMITER //
CREATE PROCEDURE add_student(IN p_name VARCHAR(50), IN p_score INT)
BEGIN
  INSERT INTO students (name, score) VALUES (p_name, p_score);
END //
DELIMITER ;

-- 觸發器
CREATE TRIGGER trg_before_insert BEFORE INSERT ON students
FOR EACH ROW SET NEW.created_at = NOW();

六、常用維護命令

SHOW TABLES;
SHOW CREATE TABLE students\G
DESC students;
SHOW INDEX FROM students;
ANALYZE TABLE students;
OPTIMIZE TABLE students;
CHECK TABLE students;
REPAIR TABLE students;  -- 僅 MyISAM

總結

掌握上述核心語法，覆蓋 90% 日常開發運維場景。進階優化請關注：EXPLAIN 執行計劃分析、索引設計原則、事務隔離級別、鎖機制、主從同步、分區表、CTE/窗口函數（8.0+）。