MySQL 常用 SQL 語句速查手冊：DDL、DML、DQL 核心語法

MySQL 常用 SQL 語句速查手冊：DDL、DML、DQL 核心語法

整理開發高頻使用的 MySQL 語句，涵蓋庫表管理、數據增刪改查、連接查詢、約束操作、常用函數等，採用大寫關鍵字提升可讀性，適合作為桌面參考卡片。

一、數據庫與表結構管理 (DDL)

數據庫操作

CREATE DATABASE mysql_db;
DROP DATABASE mysql_db;
SHOW DATABASES;
USE mysql_db;

表結構操作

-- 創建表
CREATE TABLE user (
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(20) UNIQUE NOT NULL,
  sex ENUM('1','2','3') NOT NULL DEFAULT '3'
);

-- 查看結構
DESC user;
SHOW CREATE TABLE user;

-- 修改表結構
ALTER TABLE user RENAME TO users;          -- 修改表名
ALTER TABLE user ADD age SMALLINT UNSIGNED DEFAULT 18 AFTER sex; -- 增加列
ALTER TABLE user DROP COLUMN sex;          -- 刪除列
ALTER TABLE user CHANGE age a_ge TINYINT UNSIGNED AFTER id;      -- 修改列名/類型/位置

約束管理

-- 主鍵
ALTER TABLE t ADD PRIMARY KEY (id);
ALTER TABLE t DROP PRIMARY KEY;

-- 唯一索引
ALTER TABLE t ADD UNIQUE (email);
ALTER TABLE t DROP INDEX email;

-- 外鍵（父表 user，子表 privinces）
CREATE TABLE privinces (
  pid SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  uid SMALLINT,
  FOREIGN KEY (uid) REFERENCES user(id) ON DELETE CASCADE
);
-- 刪除外鍵：先 SHOW CREATE TABLE 獲取約束名，再 ALTER TABLE t DROP FOREIGN KEY fk_name;

二、數據操作 (DML)

-- 插入
INSERT INTO user(username) VALUES ('John');
INSERT INTO user(id, username, sex) VALUES (1, 'Tom', 1);

-- 更新
UPDATE user SET age = age + 5, sex = 1 WHERE id = 2;

-- 刪除
DELETE FROM user WHERE id = 3;
TRUNCATE TABLE user;  -- 清空表保留結構

-- 多表更新
UPDATE user u INNER JOIN privinces p ON u.province = p.pname
SET u.province = p.pid;

三、數據查詢 (DQL)

基礎查詢模板

SELECT [DISTINCT] col1, col2
FROM table
[WHERE condition]
[GROUP BY col [HAVING condition]]
[ORDER BY col [ASC|DESC]]
[LIMIT offset, row_count];

常用查詢模式

• 分頁：SELECT * FROM user LIMIT 10, 20（第 11-30 行）
• 去重：SELECT DISTINCT dept FROM employee
• 模糊匹配：SELECT * FROM user WHERE name LIKE 'Zhang%'
• 空值判斷：WHERE col IS NULL / IS NOT NULL
• 范圍查詢：WHERE id BETWEEN 1 AND 100 / WHERE id IN (1,2,3)

連接查詢

-- 內連接
SELECT u.*, p.pname FROM user u INNER JOIN privinces p ON u.pid = p.pid;

-- 左外連接（保留左表所有行）
SELECT u.*, p.pname FROM user u LEFT JOIN privinces p ON u.pid = p.pid;

-- 右外連接
SELECT u.*, p.pname FROM user u RIGHT JOIN privinces p ON u.pid = p.pid;

子查詢

SELECT * FROM user 
WHERE userid = ANY (SELECT uid FROM privinces WHERE pname = '河南');
SELECT * FROM user 
WHERE userid IN (SELECT uid FROM privinces WHERE pname = '河南');

四、常用函數速查

五、進階技巧

• 創建表並導入數據：CREATE TABLE new_tbl SELECT * FROM old_tbl WHERE status=1;
• 複製表結構：CREATE TABLE new_tbl LIKE old_tbl;
• 查看索引：SHOW INDEX FROM tbl\G
• 字符集修改：ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;