您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 常用 SQL 大全：用戶權限、庫表操作、備份還原、複製表、DDL 模板

編輯：MySQL 綜合教學

MySQL 常用 SQL 大全：用戶權限、庫表操作、備份還原、複製表、DDL 模板

整理高頻 MySQL 操作指令，涵蓋帳號管理、庫表 DDL/DML、備份還原、表複製、結構導出，適合貼在工位牆上隨時查閱。

1. 用戶與權限

-- 建立用戶 (8.0+ 語法)
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass123!';
CREATE USER 'ro'@'10.0.0.%' IDENTIFIED BY 'ReadOnlyPass!';

-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'%';
GRANT SELECT ON app_db.* TO 'ro'@'10.0.0.%';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'10.0.0.10' WITH GRANT OPTION;

-- 修改密碼
ALTER USER 'app'@'%' IDENTIFIED BY 'NewStrongPass456!';

-- 撤銷
REVOKE INSERT, UPDATE ON app_db.* FROM 'app'@'%';

-- 刪除用戶
DROP USER 'old_user'@'%';

-- 查看權限
SHOW GRANTS FOR 'app'@'%';

2. 庫與表操作

-- 庫
SHOW DATABASES;
CREATE DATABASE biz_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
DROP DATABASE IF EXISTS old_db;
USE biz_db;

-- 表
SHOW TABLES;
DESC users;  -- 或 SHOW COLUMNS FROM users
SHOW CREATE TABLE users\G

-- 建表模板 (InnoDB、utf8mb4、註解齊全)
CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  user_id BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
  order_no VARCHAR(32) NOT NULL COMMENT '訂單號',
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '金額',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1待付款 2已付款 3已取消',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_status (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='訂單表';

-- 刪表
DROP TABLE IF EXISTS tmp_table;

3. 表複製與資料遷移

-- 僅複製結構 (含索引/約束)
CREATE TABLE new_table LIKE old_table;

-- 複製結構 + 資料
CREATE TABLE new_table AS SELECT * FROM old_table;

-- 複製結構 + 指定資料
CREATE TABLE new_table AS SELECT * FROM old_table WHERE status = 1;

-- 僅複製結構 (無索引/約束)
CREATE TABLE new_table SELECT * FROM old_table WHERE 1=2;

-- 將舊表資料導入新表 (新表需已存在)
INSERT INTO new_table SELECT * FROM old_table;

-- 清空表 (重置自增 ID)
TRUNCATE TABLE log_table;

4. 備份與還原

# 邏輯備份 (mysqldump)
# 單庫
mysqldump -u backup -p --single-transaction --routines --triggers --events biz_db > biz_db_$(date +%F).sql

# 多庫
mysqldump -u backup -p --databases db1 db2 > multi_db.sql

# 全例項 (含系統表)
mysqldump -u backup -p --all-databases --single-transaction --master-data=2 > full_backup.sql

# 只導出結構
mysqldump -u backup -p --no-data biz_db > biz_db_structure.sql

# 只導出資料
mysqldump -u backup -p --no-create-info biz_db > biz_db_data.sql

# 壓縮備份
mysqldump -u backup -p --single-transaction biz_db | gzip > biz_db_$(date +%F).sql.gz

# 還原
mysql -u root -p biz_db < biz_db_2024-06-15.sql
# 或登入後
SOURCE /path/to/biz_db_2024-06-15.sql;

# 物理備份 (xtrabackup) - 需另行安裝
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=backup --password=****
xtrabackup --prepare --target-dir=/backup/full_2024-06-15

5. 常用 DDL 變更 (線上安全)

-- 新增欄位
ALTER TABLE users ADD COLUMN phone VARCHAR(20) COMMENT '手機' AFTER email;

-- 修改欄位類型/長度
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30) COMMENT '手機';

-- 重命名欄位
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30) COMMENT '手機';

-- 刪除欄位
ALTER TABLE users DROP COLUMN mobile;

-- 新增索引
ALTER TABLE users ADD INDEX idx_mobile (mobile);
ALTER TABLE users ADD UNIQUE INDEX uk_mobile (mobile);

-- 刪除索引
ALTER TABLE users DROP INDEX idx_mobile;

-- 修改表註解
ALTER TABLE users COMMENT = '用戶表 v2';

-- 線上安全變更 (大表必須用)
pt-online-schema-change --alter "ADD COLUMN status TINYINT DEFAULT 1" D=biz_db,t=users --execute
gh-ost --alter "ADD COLUMN status TINYINT DEFAULT 1" --database=biz_db --table=users --execute

結語：熟練這些高頻 SQL，能解決 90% 日常運維場景。建議將常用片段存入 IDE Snippets / MySQL Workbench / DataGrip Favorites，實現「指尖上的 DBA」。