MySQL 基礎入門第 13 天：SQL 標準、DDL/DML/DQL 實戰與完整性約束

MySQL 學習第 13 天筆記，系統梳理 SQL 標準與方言、SQL 語言四大分類 (DDL/DML/DQL/TCL/DCL)、資料庫/表結構操作、資料增刪改查、完整性約束 (域/實體/参照)、字符集設定、自增主鍵、外鍵關聯等核心知識，附完整命令列實操記錄。

一、SQL 基礎認知

• SQL： Structured Query Language，ANSI/ISO 標準，標準 SQL 可跨資料庫運行
• 方言： 特定資料庫專有語法 (如 MySQL AUTO_INCREMENT、LIMIT、REPLACE INTO)
• 類比： 標準語 = 普通話，方言 = 地方話

二、SQL 語言分類

三、對應關係

• Java Class ↔ 表結構
• Java Object ↔ 表記錄 (行)
• MySQL Linux 環境嚴格區分大小寫 (庫/表/別名/變量)，Windows 不區分
• 約定：關鍵字小寫、多詞下劃線分隔

四、DDL 實操全記錄

4.1 資料庫操作

CREATE DATABASE MYDB1;
SHOW DATABASES;
SHOW CREATE DATABASE MYDB1;
CREATE DATABASE MYDB2 CHARACTER SET gbk;
CREATE DATABASE MYDB3 CHARACTER SET gbk COLLATE gbk_chinese_ci;
DROP DATABASE MYDB3;
ALTER DATABASE MYDB2 CHARACTER SET utf8;

4.2 表結構操作

USE MYDB1;
CREATE TABLE EMPLOYEE(
  ID INT,
  NAME VARCHAR(200),
  GENDER VARCHAR(200),
  BIRTHDAY DATE,
  ENTRY_DATE DATE,
  JOB VARCHAR(200),
  SALARY FLOAT(8,2),
  RESUME TEXT
);
SHOW TABLES;
DESC EMPLOYEE;
SHOW CREATE TABLE EMPLOYEE;

ALTER TABLE EMPLOYEE ADD IMAGE BLOB;
ALTER TABLE EMPLOYEE MODIFY JOB VARCHAR(60);
ALTER TABLE EMPLOYEE DROP IMAGE;
RENAME TABLE EMPLOYEE TO USER;
ALTER TABLE USER CHARACTER SET utf8;
ALTER TABLE USER CHANGE NAME USERNAME VARCHAR(100);

五、DML 實操

-- 插入 (字符串/日期單引號)
INSERT INTO USER(ID,USERNAME,GENDER,BIRTHDAY,ENTRY_DATE,JOB,SALARY,RESUME) 
VALUES(1,'wangdongxue','female','1991-09-08','2014-02-17','CEO',100000,'beautygirl');
INSERT INTO USER VALUES(2,'wangdong','male','1990-09-08','2014-02-17','CTO',100000,'strong man');
INSERT INTO USER VALUES(3,'查顯成','男性','1988-09-08','2014-02-17','UFO',100000,'帥鍋');

SELECT * FROM USER;

-- 字符集切換解決中文亂碼
SHOW VARIABLES LIKE 'character_set%';
SET CHARACTER_SET_CLIENT = gbk;
SET CHARACTER_SET_RESULTS = gbk;

-- 更新
UPDATE USER SET SALARY = 5000;
UPDATE USER SET SALARY = 3000 WHERE USERNAME = '查顯成';
UPDATE USER SET SALARY = 4000, JOB = 'CEO' WHERE USERNAME = 'wangdong';
UPDATE USER SET SALARY = SALARY + 1000 WHERE USERNAME = 'wangdongxue';

-- 刪除
DELETE FROM USER WHERE USERNAME = '查顯成';
DELETE FROM USER;        -- DML 逐行刪除
TRUNCATE TABLE USER;     -- DDL 截斷表，重建結構，極快

六、DQL 查詢進階

注意： ORDER BY 後可用非 SELECT 列，關鍵字作識別符需反引號 \`order\`。

七、資料完整性三大支柱

7.1 域完整性 (欄位級)

• NOT NULL：非空，空字串 '' 允許，NULL 不允許
• UNIQUE：唯一，允許多 NULL，有值則唯一
• CHECK：條件約束 (MySQL 8.0.16+ 強制)

7.2 實體完整性 (行級)

• PRIMARY KEY：主鍵，非空+唯一，一表一個
• AUTO_INCREMENT：自增 (MySQL 方言)，Oracle 用 Sequence
• 範例：ID INT PRIMARY KEY AUTO_INCREMENT

7.3 参照完整性 (表間)

• FOREIGN KEY：外鍵，維護主從表引用關係
• 一對多、多對多、一對一

八、實操輸出片段 (含中文編碼問題)

mysql> INSERT INTO USER VALUES (3,'查顯成','男性','1988-09-08','2014-02-17','UFO',100000,'帥鍋');
ERROR 1366 (HY000): Incorrect string value: '\xB2\xE9\xCF\xD4\xB3\xC9' for column 'username' at row 1

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_client     | utf8  |
| character_set_connection | utf8  |
| character_set_database   | utf8  |
| character_set_results    | utf8  |
| character_set_server     | utf8  |
| character_set_system     | utf8  |
+--------------------------+-------+

mysql> SET CHARACTER_SET_CLIENT = gbk;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO USER VALUES (3,'湯柳清','男性','1988-09-08','2014-02-17','UFO',100000,'帥鍋');
Query OK, 1 row affected (0.02 sec)

教訓： 客戶端編碼 (character_set_client) 必須與輸入資料編碼一致，否則中文寫入亂碼。生產環境統一配置 utf8mb4 端到端。

九、關鍵術語對照

這份筆記涵蓋 MySQL 入門必會的 90% 核心操作，建議在本地環境逐條敲擊執行，建立肌肉記憶，為進階優化與架構設計打牢基礎。