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

MySQL 數據庫與表操作命令大全：新手到精通的完整指南

編輯：MySQL 綜合教學

MySQL 數據庫與表操作命令大全：新手到精通的完整指南

掌握 MySQL 基礎 DDL/DML 操作是資料庫工程師的必修課。本文系統整理數據庫、表的創建、查看、修改、刪除等核心命令，附帶實戰場景與最佳實踐。

一、數據庫級操作

1. 查看數據庫

SHOW DATABASES;
-- 過濾系統庫
SHOW DATABASES LIKE 'biz_%';

2. 創建數據庫

-- 基礎創建
CREATE DATABASE db_test;

-- 指定字符集排序規則（推薦）
CREATE DATABASE db_test 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

3. 使用/切換數據庫

USE db_test;
-- 或連接時指定
mysql -u root -p db_test

4. 刪除數據庫

DROP DATABASE db_test;
-- 安全刪除（不存在不報錯）
DROP DATABASE IF EXISTS db_test;

5. 查看數據庫創建語句

SHOW CREATE DATABASE db_test;

二、表級操作

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),
    INDEX idx_name (firstname, lastname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 條件創建（不存在才創建）

CREATE TABLE IF NOT EXISTS db_test.tb_test (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

3. 復制表結構與數據

-- 完整復制（結構+數據+索引）
CREATE TABLE tb_test2 SELECT * FROM db_test.tb_test;

-- 僅復制結構
CREATE TABLE tb_test3 LIKE tb_test;

-- 部分列復制
CREATE TABLE tb_test4 SELECT id, firstname, email FROM tb_test;

4. 創建臨時表

CREATE TEMPORARY TABLE tmp_users 
SELECT id, name FROM users WHERE created_at > '2024-01-01';
-- 會話結束自動刪除，或手動 DROP

5. 查看表列表

SHOW TABLES;
SHOW TABLES LIKE 'tmp_%';
SHOW FULL TABLES;  -- 顯示表類型（BASE TABLE/VIEW）

6. 查看表結構

-- 簡要結構
DESC tb_test;
-- 或
DESCRIBE tb_test;

-- 詳細創建語句
SHOW CREATE TABLE tb_test\G;

-- 信息模式查詢（可編程）
SELECT COLUMN_NAME, DATA_TYPE, COLUMN_KEY, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='db_test' AND TABLE_NAME='tb_test';

7. 刪除表

DROP TABLE tb_test;
DROP TABLE IF EXISTS tb_test;
DROP TABLE tb1, tb2, tb3;  -- 批量刪除

8. 修改表結構 (ALTER TABLE)

操作	語法示例	說明
重命名表	ALTER TABLE old_name RENAME new_name;	元數據操作，極快
添加列	ALTER TABLE tbl ADD col VARCHAR(50) NOT NULL DEFAULT '' AFTER exist_col;	支持 FIRST/AFTER 定位
刪除列	ALTER TABLE tbl DROP COLUMN col;	數據永久丟失，慎用
修改列類型/屬性	ALTER TABLE tbl MODIFY col VARCHAR(100) NOT NULL;	大表鎖表，建議 pt-osc
重命名列	ALTER TABLE tbl CHANGE old_col new_col VARCHAR(100);	需指定完整類型定義
調整列順序	ALTER TABLE tbl MODIFY col VARCHAR(50) FIRST;	重建表，大表慎用
添加索引	ALTER TABLE tbl ADD INDEX idx_col (col);	或 ADD UNIQUE/PRIMARY KEY
刪除索引	ALTER TABLE tbl DROP INDEX idx_col;
修改引擎	ALTER TABLE tbl ENGINE=InnoDB;	重建表，鎖表操作
修改字符集	ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;	數據轉碼，耗時長
添加外鍵	ALTER TABLE tbl ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES ref_tbl(ref_col);	需滿足前提條件
刪除外鍵	ALTER TABLE tbl DROP FOREIGN KEY fk_name;

三、進階實戰場景

場景 1：大表添加列零停機

-- 使用 pt-online-schema-change
pt-online-schema-change \
  --alter "ADD COLUMN new_col VARCHAR(50) DEFAULT ''" \
  D=db,t=big_table --execute

場景 2：批量修改表前綴

-- 生成重命名語句
SELECT CONCAT('RENAME TABLE `', TABLE_NAME, '` TO `new_', TABLE_NAME, '`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='db' AND TABLE_NAME LIKE 'old_%';

場景 3：獲取表大小統計

SELECT 
    TABLE_NAME,
    ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='db'
ORDER BY DATA_LENGTH DESC;

四、新手常見錯誤與避坑

❌ 未指定字符集 → 默認 latin1，中文亂碼 → ✅ 總是指定 CHARSET=utf8mb4
❌ 無主鍵表 → InnoDB 隱式主鍵，複製/同步問題 → ✅ 所有表必須有顯式主鍵
❌ VARCHAR 無長度 → 語法錯誤 → ✅ VARCHAR(255)
❌ 直接 DROP 大表 → 刪除緩衝池脏頁、磁盤 IO 飆升 → ✅ 先 RENAME 再異步清理
❌ ALTER 表未評估影響 → 長時間鎖表業務中斷 → ✅ 使用 pt-osc 或維護窗口

五、命令速查卡片

# 數據庫操作
CREATE DATABASE db CHARSET utf8mb4;
USE db;
DROP DATABASE db;

# 表操作
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50)) ENGINE=InnoDB;
SHOW TABLES;
DESC t;
SHOW CREATE TABLE t\G;
DROP TABLE t;

# 結構變更
ALTER TABLE t ADD col INT;
ALTER TABLE t DROP COL col;
ALTER TABLE t MODIFY col BIGINT;
ALTER TABLE t CHANGE old new VARCHAR(100);
ALTER TABLE t RENAME new_t;
ALTER TABLE t ENGINE=InnoDB;

熟練掌握這些命令只是起點，真正的專業能力在於：理解每個操作的鎖行為、性能影響、數據安全風險，並能根據業務場景選擇最優執行策略。