MySQL 基礎操作速查手冊：庫表管理、數據增刪改查核心命令

前言

MySQL 日常運維與開發中，高頻使用的核心命令集相對固定。本文整理從連接、庫表管理到數據 CRUD 的標準化操作指南，適合快速查閱與新手入門。

一、連接與基礎庫操作

# 連接（生產環境建議配置 ~/.my.cnf 免密）
mysql -u root -p

# 庫管理
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
SHOW DATABASES;
USE db_name;
DROP DATABASE db_name;

二、表結構管理（DDL）

-- 建表範例
CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用戶名',
  email VARCHAR(128) NOT NULL DEFAULT '' COMMENT '郵箱',
  gender CHAR(1) NOT NULL DEFAULT 'U' COMMENT 'M/F/U',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看結構
DESC users;
SHOW CREATE TABLE users\G

-- 修改表
ALTER TABLE users RENAME TO app_users;           -- 改名
ALTER TABLE users MODIFY email VARCHAR(255);     -- 改類型
ALTER TABLE users CHANGE username user_name VARCHAR(64); -- 改列名+類型
ALTER TABLE users ADD phone VARCHAR(20) AFTER email;   -- 指定位置加列
ALTER TABLE users DROP COLUMN phone;             -- 刪列
ALTER TABLE users ENGINE=InnoDB;                 -- 換引擎
ALTER TABLE users AUTO_INCREMENT=10000;          -- 改自增起值

三、數據操作（DML）

-- 插入
INSERT INTO users (username, email) VALUES ('zhangsan', 'zhang@example.com');
INSERT INTO users (username, email) VALUES ('lisi', 'li@example.com'), ('wangwu', 'wang@example.com');

-- 查詢
SELECT * FROM users;
SELECT id, username FROM users LIMIT 10;          -- 分頁
SELECT * FROM users WHERE id > 100 ORDER BY id DESC;

-- 更新
UPDATE users SET email='new@example.com' WHERE id=1;

-- 刪除（慎用）
DELETE FROM users WHERE id=1;          -- 可回滾，觸發器生效
TRUNCATE TABLE users;                  -- DDL，不可回滾，重置自增，速度極快

-- 重命名表
ALTER TABLE old_name RENAME TO new_name;

四、進階查詢技巧

• 分頁優化：深分頁避免 LIMIT 1000000, 10，改用 WHERE id > last_id LIMIT 10（游標分頁）
• 條件推導：WHERE col IS NULL → 設置默認值查 col=0；!=/<> → 改用 NOT IN 或範圍查詢
• 覆蓋索引：SELECT id, name FROM t WHERE status=1 命中 idx_status_name(id, name, status) 避免回表

五、權限與安全基線

-- 建用戶
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass#2024';
-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'%';
FLUSH PRIVILEGES;
-- 修改密碼
ALTER USER 'app'@'%' IDENTIFIED BY 'NewPass#2024';
-- 刪除用戶
DROP USER 'app'@'%';

六、常用維護命令

SHOW PROCESSLIST;              -- 當前連線
KILL 12345;                    -- 斬斷長事務
SHOW ENGINE INNODB STATUS\G    -- 引擎狀態
ANALYZE TABLE users;           -- 更新統計信息
OPTIMIZE TABLE users;          -- 整理碎片（鎖表，慎用）