MySQL 常用 DDL/DML 語句速查手冊：建表、增刪改查、索引、約束全覆蓋

核心術語對照

一、表結構操作 (DDL)

1. 創建表

CREATE TABLE student (
  stu_id     INT PRIMARY KEY AUTO_INCREMENT COMMENT '學號',
  name       VARCHAR(20) NOT NULL COMMENT '姓名',
  age        TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年齡',
  gender     ENUM('男','女') NOT NULL DEFAULT '男' COMMENT '性別',
  address    VARCHAR(100) COMMENT '家庭住址',
  phone      VARCHAR(11) COMMENT '聯系電話',
  education  VARCHAR(10) COMMENT '學歷',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='學生表';

2. 修改表結構

-- 添加列
ALTER TABLE student ADD COLUMN email VARCHAR(50) COMMENT '郵箱';

-- 修改列：類型、默認值、註釋、位置
ALTER TABLE student MODIFY COLUMN education VARCHAR(20) COMMENT '學歷(擴展)';
ALTER TABLE student CHANGE COLUMN address home_address VARCHAR(150) COMMENT '家庭住址(重命名)';

-- 刪除列
ALTER TABLE student DROP COLUMN home_address;

-- 添加索引
ALTER TABLE student ADD INDEX idx_name (name);
ALTER TABLE student ADD UNIQUE INDEX uk_phone (phone);

-- 添加外鍵（需引用表存在、引用列為主鍵/唯一索引、類型一致）
ALTER TABLE scores 
  ADD CONSTRAINT fk_scores_student 
  FOREIGN KEY (stu_id) REFERENCES student(stu_id) 
  ON DELETE CASCADE ON UPDATE CASCADE;

-- 刪除索引/外鍵
ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE scores DROP FOREIGN KEY fk_scores_student;

3. 刪除表

DROP TABLE [IF EXISTS] student;

二、數據操作 (DML)

1. 插入數據

-- 單行完整插入
INSERT INTO student (name, age, gender, phone, education) 
VALUES ('張三', 20, '男', '13800138000', '大學');

-- 多行批量插入（高效）
INSERT INTO student (name, age, gender, phone, education) VALUES
('李四', 21, '男', '13800138001', '碩士'),
('王五', 19, '女', '13800138002', '本科'),
('趙六', 22, '男', '13800138003', '博士');

-- 指定列插入（其他列用默認值/NULL）
INSERT INTO student (name, age) VALUES ('孫七', 20);

2. 更新數據

-- 條件更新
UPDATE student 
SET education = '大專' 
WHERE phone LIKE '11%';

-- 多表更新（MySQL 特有語法）
UPDATE student s
JOIN scores sc ON s.stu_id = sc.stu_id
SET s.education = '碩士'
WHERE sc.total_score > 270;

3. 刪除數據

-- 條件刪除
DELETE FROM student WHERE name LIKE '張%' AND gender = '男';

-- 清空表（重置自增 ID，速度極快，不可回滾）
TRUNCATE TABLE student;

4. 查詢數據

-- 基礎查詢
SELECT name, stu_id FROM student WHERE age < 22 AND education = '大專';

-- 分頁查詢
SELECT * FROM student ORDER BY age DESC LIMIT 10 OFFSET 20;  -- 第 3 頁，每頁 10 條

-- 聚合查詢
SELECT gender, AVG(age) AS avg_age, COUNT(*) AS cnt 
FROM student 
GROUP BY gender;

-- 多表聯接
SELECT s.name, sc.course, sc.score
FROM student s
INNER JOIN scores sc ON s.stu_id = sc.stu_id
WHERE s.gender = '女';

三、聚合函數速查

四、約束定義最佳實踐

CREATE TABLE orders (
  order_id     BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id      INT NOT NULL,
  amount       DECIMAL(10,2) NOT NULL CHECK (amount > 0),
  status       TINYINT NOT NULL DEFAULT 0 COMMENT '0待付款 1已付款 2已發貨 3已完成 4已取消',
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  INDEX idx_user_id (user_id),
  INDEX idx_status_created (status, created_at),
  
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

五、事務控制

START TRANSACTION;
  INSERT INTO orders (user_id, amount) VALUES (100, 99.90);
  UPDATE accounts SET balance = balance - 99.90 WHERE user_id = 100;
  -- 若任一失敗
  ROLLBACK;
  -- 全部成功
COMMIT;

-- 保存點
SAVEPOINT sp1;
  DELETE FROM order_items WHERE order_id = 123;
ROLLBACK TO SAVEPOINT sp1;  -- 僅回滾到保存點

六、權限管理

-- 創建用戶
CREATE USER 'app_user'@'10.0.%.%' IDENTIFIED BY 'StrongP@ssw0rd!';

-- 授權（最小權限）
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'10.0.%.%';
GRANT EXECUTE ON PROCEDURE mydb.proc_name TO 'app_user'@'10.0.%.%';

-- 撤銷
REVOKE DELETE ON mydb.* FROM 'app_user'@'10.0.%.%';

-- 刷新權限
FLUSH PRIVILEGES;

七、常用管理命令

總結

掌握上述核心 DDL/DML 語句，覆蓋 95% 日常開發運維場景。關鍵原則：**顯式列名、批量操作、約束前置、索引跟上、權限最小、事務包裹**。避免 SELECT *、隱式類型轉換、無 WHERE 更新刪除、大事務長鎖定等反模式。