MySQL 數據庫與表操作命令精華：從創建到結構變更的完整速查表

一、數據庫級操作

1. 查看、創建、切換、刪除

SHOW DATABASES;                    -- 列出所有庫
CREATE DATABASE db_test;           -- 創建庫
USE db_test;                       -- 切換當前庫
DROP DATABASE db_test;             -- 刪除庫

2. 實用技巧：條件創建與跨庫建表

-- 僅當不存在時創建，避免報錯
CREATE DATABASE IF NOT EXISTS db_test;

-- 未切換庫時指定庫前綴建表
CREATE TABLE db_test.tb_test (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    email VARCHAR(45) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    PRIMARY KEY (id)
);

二、表操作核心命令

1. 創建表的多種模式

-- 標準建表
CREATE TABLE tb_test (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    email VARCHAR(45) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    PRIMARY KEY (id)
);

-- 條件創建（避免報錯）
CREATE TABLE IF NOT EXISTS tb_test (...);

-- 復制表結構與數據
CREATE TABLE tb_test2 SELECT * FROM db_test.tb_test;

-- 部分欄位復制
CREATE TABLE tb_test2 SELECT id, firstname, lastname, email FROM tb_test;

-- 創建臨時表（會話結束自動銷毀）
CREATE TEMPORARY TABLE emp_temp SELECT firstname, lastname FROM tb_test;

2. 查看表結構

SHOW TABLES;                    -- 當前庫所有表
DESCRIBE tb_test;               -- 結構詳情
SHOW COLUMNS IN tb_test;        -- 等同 DESCRIBE

輸出關鍵欄位含義：

• Field：欄位名
• Type：數據類型
• Null：是否允許 NULL
• Key：索引類型（PRI/UNI/MUL）
• Default：預設值
• Extra：附加屬性（如 auto_increment）

3. 刪除表

DROP TABLE [IF EXISTS] tb_test [, tb_test2 ...];
DROP TEMPORARY TABLE IF EXISTS emp_temp;

4. 結構變更 (ALTER TABLE)

-- 增加欄位（指定位置）
ALTER TABLE tb_demo ADD COLUMN email VARCHAR(45);
ALTER TABLE tb_demo ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE tb_demo ADD COLUMN id INT FIRST;

-- 修改欄位屬性/重命名
ALTER TABLE tb_demo CHANGE email email VARCHAR(45) NOT NULL;
ALTER TABLE tb_demo MODIFY email VARCHAR(60);  -- 僅改類型

-- 刪除欄位
ALTER TABLE tb_demo DROP COLUMN email;

-- 重命名表
ALTER TABLE tb_demo RENAME TO tb_new;

三、索引管理實戰

-- 普通索引
ALTER TABLE employee ADD INDEX emp_name (name);

-- 主鍵索引
ALTER TABLE employee ADD PRIMARY KEY (id);

-- 唯一索引
ALTER TABLE employee ADD UNIQUE emp_name2 (cardnumber);

-- 刪除索引
ALTER TABLE employee DROP INDEX emp_name;

四、進階應用場景

1. 大表查詢優化：臨時表分流

當需對千萬級大表執行多次複雜查詢時：

-- 1. 提取高頻查詢子集到臨時表
CREATE TEMPORARY TABLE tmp_active_users
SELECT id, name, email FROM users WHERE status=1 AND last_login > '2023-01-01';

-- 2. 對臨時表建索引加速後續 JOIN
ALTER TABLE tmp_active_users ADD INDEX idx_email (email);

-- 3. 多次複雜查詢復用臨時表
SELECT ... FROM tmp_active_users JOIN orders ...;
SELECT ... FROM tmp_active_users JOIN logs ...;

2. 結構變更最佳實踐

1. 線上加欄位：優先用 ADD COLUMN ... AFTER ... 指定位置，避免鎖表久
2. 大表改類型：採用 pt-online-schema-change 或 gh-ost 工具無鎖變更
3. 刪除大表：先 RENAME TABLE big_table TO big_table_bak，再異步 DROP TABLE big_table_bak

五、命令速查卡片

熟練掌握這些命令組合，能覆蓋 95% 的日常庫表維護場景。建議收藏備查，實戰中根據業務特點選擇最優語法。