MySQL 基礎操作完整指南：從 E-R 建模到 SQL 核心語法

MySQL 基礎操作完整指南：從 E-R 建模到 SQL 核心語法

本文系統梳理關係型數據庫核心概念、E-R 建模方法論、約束體系、常用 SQL 語法與字符集配置，適合入門學習與面試複習。

一、關係型數據庫核心概念

• 表：由列與行組成，列定義屬性，行存儲記錄
• 庫：多張表的邏輯容器
• E-R 模型三要素：實體、屬性、關係

二、E-R 圖構建七步法

1. 識別實體 → 每個實體建一張表
2. 選擇主鍵 → 唯一標識，非空
3. 添加外鍵 → 表達 1:N 關係
4. 建立關聯表 → 解決 M:N 關係
5. 定義約束 → 完整性規則
6. 評價關係質量 → 範式分析（1NF/2NF/3NF/BCNF）
7. 選擇數據類型 → 匹配業務取值範圍

E-R 圖標準符號：

• 實體：矩形框
• 屬性：矩形內部橢圓
• 關係：菱形框，標註 1:1 / 1:N / M:N

三、約束體系詳解

四、常用管理命令

-- 數據庫操作
SHOW DATABASES;
CREATE DATABASE `db_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `db_name`;
DROP DATABASE `db_name`;

-- 表操作
SHOW TABLES;
DESC `tbl_name`;              -- 結構描述
SHOW CREATE TABLE `tbl_name`; -- 完整建表語句
DROP TABLE `tbl_name`;

-- 存儲引擎
SHOW ENGINES;
SET DEFAULT_STORAGE_ENGINE = InnoDB;

-- 字符集查看
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

五、字符集配置最佳實踐

腳本文件方式（init.sql）：

SET NAMES utf8mb4;  -- 等同於以下三條
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

SET character_set_database = utf8mb4;
SET character_set_server = utf8mb4;
SET collation_database = utf8mb4_unicode_ci;
SET collation_connection = utf8mb4_unicode_ci;
SET collation_server = utf8mb4_unicode_ci;

命令行執行：mysql -u root -p < init.sql

六、數據類型選擇指南

七、核心 DML 語法

插入

INSERT INTO `tbl` (`col1`, `col2`) VALUES ('val1', 'val2');
INSERT INTO `tbl` (`col1`, `col2`) VALUES ('a',1), ('b',2), ('c',3);  -- 批量
INSERT INTO `tbl` SELECT ... FROM `other_tbl`;  -- 查詢結果插入

更新

UPDATE `tbl` SET `col1` = 'new_val' WHERE `id` = 1;
UPDATE `tbl` SET `score` = `score` + 10 WHERE `class_id` = 2;

刪除

DELETE FROM `tbl` WHERE `id` = 1;
TRUNCATE TABLE `tbl`;  -- 快速清空，不可回滾，重置自增 ID

查詢

SELECT `col1`, `col2` FROM `tbl`
WHERE `status` = 1 AND `created_at` > '2023-01-01'
GROUP BY `category`
HAVING COUNT(*) > 5
ORDER BY `score` DESC, `id` ASC
LIMIT 10 OFFSET 20;  -- 分頁

聚合函數

COUNT(*) / COUNT(DISTINCT col)  -- 行數 / 去重計數
SUM(col) / AVG(col)             -- 求和 / 平均
MAX(col) / MIN(col)             -- 最大 / 最小
GROUP_CONCAT(col SEPARATOR ',') -- 分組拼接

八、特殊字符轉義

單引號：\'、雙引號：\"、反斜杠：\\、百分號：\%、下劃線：\_（LIKE 模糊查詢中）

九、常見面試題

1. 如何創建索引？CREATE INDEX idx_name ON tbl(col); 或建表時 KEY idx_name (col)
2. 如何修改列類型？ALTER TABLE tbl MODIFY col VARCHAR(200);
3. 什麼是視圖？虛擬表，基於 SELECT 查詢定義，CREATE VIEW v AS SELECT ...
4. 視圖優缺點？簡化複雜查詢、安全隔離、但可能影響性能、不可索引（物化視圖除外）

掌握基礎操作是進階優化與架構設計的前提，建議動手構建完整練習項目鞏固知識體系。