Day 19：MySQL 數據庫入門核心概念與基礎操作完整筆記

一、核心概念釐清

二、登錄與編碼設置

# 登錄（-h 主機 -u 用戶 -p 密碼）
mysql -h127.0.0.1 -uroot -p

# 進入後首條指令：防止中文亂碼
SET NAMES utf8mb4;

最佳實踐：生產環境統一使用 utf8mb4（支援 Emoji），而非舊版 utf8（僅 3 字節）。

三、數據庫級 DDL

-- 創建（指定字符集與排序規則）
CREATE DATABASE shop_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 查看
SHOW DATABASES;

-- 切換
USE shop_db;

-- 刪除（謹慎！）
DROP DATABASE shop_db;

四、常用數據類型速查

五、表級 DDL 與約束

CREATE TABLE user (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  username VARCHAR(32) NOT NULL COMMENT '用戶名',
  email VARCHAR(128) NOT NULL COMMENT '郵箱',
  password_hash CHAR(60) NOT NULL COMMENT 'bcrypt 雜湊',
  status TINYINT NOT NULL DEFAULT 1 COMMENT '1正常 0禁用',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username),
  UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

約束關鍵字：

• PRIMARY KEY：唯一 + 非空 + 聚簇索引。
• UNIQUE：業務唯一性（如用戶名、郵箱）。
• NOT NULL：拒絕 NULL，優化器更易優化。
• DEFAULT：隱式賦值，減少應用層判斷。
• AUTO_INCREMENT：自增主鍵，配合 BIGINT 防溢出。
• COMMENT：文檔化，SHOW CREATE TABLE 可見。

六、表結構查看與修改

-- 查看結構
DESC user;
SHOW CREATE TABLE user;

-- 修改：增刪改列
ALTER TABLE user ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE user MODIFY COLUMN phone VARCHAR(30);  -- 擴長
ALTER TABLE user CHANGE COLUMN phone mobile VARCHAR(20); -- 重命名+改類型
ALTER TABLE user DROP COLUMN mobile;

-- 刪表
DROP TABLE user;

七、DML 增刪改查（CRUD）核心模板

-- 增
INSERT INTO user (username, email, password_hash) VALUES ('alice', 'alice@ex.com', '$2y$10$...');
INSERT INTO user (username, email, password_hash) VALUES 
  ('bob','bob@ex.com','$2y$10$...'),('cindy','cindy@ex.com','$2y$10$...'); -- 批次

-- 刪（帶 WHERE，防誤刪全表）
DELETE FROM user WHERE id = 100;
-- 清空表並重置自增
TRUNCATE TABLE user;

-- 改
UPDATE user SET status = 0 WHERE id = 100;

-- 查
SELECT id, username, email, created_at FROM user WHERE status = 1 ORDER BY created_at DESC LIMIT 20;

八、避坑指南（新手必讀）

1. 密碼明文存儲：嚴禁！必須用 bcrypt/Argon2 雜湊。
2. utf8` vs `utf8mb4`：舊版 utf8` 只支援 BMP 字符，Emoji 會截斷報錯。
3. 隱式類型轉換：WHERE varchar_col = 123` 導致全表掃描，字串加引號。
4. 自增 ID 耗盡：高併發場景用 BIGINT` 或雪花算法。
5. 無索引 UPDATE/DELETE：鎖全表，業務停擺，必須加 WHERE 索引列。