MySQL 常用 SQL 語句大全：建表、查詢、備份、索引與優化技巧

MySQL 常用 SQL 語句大全

本文整理了 MySQL 日常開發與運維中最高頻的 SQL 語句，涵蓋數據庫/表操作、數據增刪改查、備份還原、索引管理、分頁查詢、效能分析與優化建議，適合作為速查手冊隨時查閱。

一、數據庫與表的基礎操作

數據庫管理

-- 登入/切換/查看
USE database_name;
SHOW DATABASES;
SELECT DATABASE();           -- 當前數據庫
SELECT VERSION();            -- 版本號
SELECT USER();               -- 當前用戶
SELECT NOW();                -- 當前時間

-- 建庫/刪庫
CREATE DATABASE db_name [CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci];
DROP DATABASE db_name;

表結構操作

-- 建表/複製表/判斷存在
CREATE TABLE tbl (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE tbl2 AS SELECT * FROM tbl;                    -- 複製結構+數據
CREATE TABLE tbl2 AS SELECT * FROM tbl WHERE 1<>1;         -- 僅複製結構
CREATE TABLE IF NOT EXISTS tbl (...);                      -- 安全建表

-- 重命名/刪表/清空
ALTER TABLE old_name RENAME TO new_name;
DROP TABLE tbl;
TRUNCATE TABLE tbl;                                        -- 清空並重置自增ID

-- 欄位增刪改
ALTER TABLE tbl ADD COLUMN col_name DATA_TYPE;
ALTER TABLE tbl ADD COLUMN col1 TYPE1, ADD COLUMN col2 TYPE2;
ALTER TABLE tbl MODIFY col_name NEW_TYPE;                  -- 僅改類型
ALTER TABLE tbl CHANGE old_col new_col NEW_TYPE;           -- 改名+改類型

二、索引管理

-- 建立索引
ALTER TABLE tbl ADD INDEX idx_name (col_name);
CREATE INDEX idx_name ON tbl (col_name);
CREATE UNIQUE INDEX uk_name ON tbl (col_name);             -- 唯一索引

-- 刪除索引
DROP INDEX idx_name ON tbl;
ALTER TABLE tbl DROP INDEX idx_name;

MySQL 使用索引的條件

• 操作符：=, >, >=, <, <=, BETWEEN, IN, LIKE 'prefix%' (不以萬用字元開頭)
• 不使用索引：LIKE '%keyword%', 函數運算列 YEAR(date_col), 隱式類型轉換

索引優缺點權衡

三、查詢與分頁

-- 基本查詢
SELECT col1, col2 FROM tbl WHERE condition ORDER BY col1 LIMIT offset, count;
-- LIMIT 9,10 表示跳過前 9 筆，取 10 筆 (第一筆 offset=0)

-- 字串串接
SELECT CONCAT(id, ':', name, '=') FROM students;

-- 多表關聯查詢
SELECT s.name, e.date, sc.score, e.type
FROM score sc
JOIN event e ON sc.event_id = e.event_id
WHERE e.date = '1999-09-23';

四、EXPLAIN 執行計畫分析

EXPLAIN SELECT * FROM tbl WHERE col = 'value';

五、數據備份與還原

# 完整庫備份
mysqldump -u root -p db_name > backup.sql

# 指定表備份
mysqldump -u root -p db_name tbl1 tbl2 > backup.sql

# 所有庫備份
mysqldump -u root -p --all-databases > all.sql

# 遠程備份
mysqldump -h host -u user -p db_name > backup.sql

# 還原
mysql -u root -p db_name < backup.sql
# 或在 mysql 命令列
SOURCE backup.sql;

六、表優化與維護

-- 碎片整理 (MyISAM/BDB)
OPTIMIZE TABLE tbl_name;

-- 禁用/啟用索引 (大量導入時加速)
ALTER TABLE tbl DISABLE KEYS;
-- 導入數據...
ALTER TABLE tbl ENABLE KEYS;

-- 分析表統計資訊
ANALYZE TABLE tbl_name;

-- 檢查/修復表
CHECK TABLE tbl_name;
REPAIR TABLE tbl_name;

七、欄位設計最佳實踐

1. 優先使用定長類型：CHAR 優於 VARCHAR (頻繁更新表易產生碎片)，固定長度利於崩潰恢復與快速定位。
2. 盡量用 NOT NULL：減少儲存空間、加快查詢、避免 NULL 特殊判斷。
3. 枚舉值用 ENUM：性別、狀態等有限值集合，內部以數值儲存，處理更快。
4. PROCEDURE ANALYSE() 建議：SELECT * FROM tbl PROCEDURE ANALYSE(); 可獲取欄位類型優化建議。

八、查詢快取配置

[mysqld]
query_cache_type = 1          -- 1=開啟(預設), 2=按需(SQL_CACHE), 0=關閉
query_cache_size = 64M        -- 快取總大小
query_cache_limit = 2M        -- 單查詢結果超過此值不快取

監控指標：Qcache_hits / (Qcache_hits + Com_select) 為命中率；Qcache_lowmem_prunes 大表示記憶體不足；Qcache_free_blocks 大表示碎片多，需 FLUSH QUERY CACHE。

九、硬體層面優化建議

1. 增加記憶體：減少磁碟 I/O，擴大各類緩衝區。
2. 使用更快磁碟/SSD/RAID：降低尋道時間與 I/O 等待。
3. 分離繁忙數據庫到不同物理磁碟：避免磁頭爭用，分區無效，必須是不同物理設備。

十、MySQL 不支援的功能 (舊版本)

事務、視圖、外鍵、參考完整性、預存程序、觸發器 —— 注意：MySQL 5.0+ 已全部支援，請以實際版本為準。