目錄

1. CRUD操作概述

2. Create操作詳解

2.1 表的創建

2.2 單行數據插入

2.3 多行數據插入

2.4 插入沖突處理

3. Retrieve操作詳解

3.1 基礎查詢

全列查詢（慎用）

指定列查詢

表達式查詢

結果去重

3.2 條件查詢（WHERE子句）

比較運算符

邏輯運算符

NULL處理

3.3 結果排序（ORDER BY）

3.4 分頁查詢（LIMIT與OFFSET）

4. Update操作詳解

4.1 單字段更新

4.2 多字段更新

4.3 基于表達式更新

5. Delete操作詳解

5.1 刪除指定數據

5.2 清空表數據

6. 高級查詢技巧

6.1 聚合函數

6.2 GROUP BY與HAVING

7. 實戰案例解析

案例1：去重插入

案例2：分頁查詢

8. 總結與最佳實踐

---

1. CRUD操作概述

CRUD是數據庫操作的四大核心功能：

• Create（創建）：插入數據

• Retrieve（讀取）：查詢數據

• Update（更新）：修改數據

• Delete（刪除）：刪除數據

本文將通過豐富的代碼示例，詳細解析MySQL中CRUD的實現方式及注意事項。

---

2. Create操作詳解

2.1 表的創建

通過CREATE TABLE語句定義表結構。

CREATE TABLE students (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,sn INT NOT NULL UNIQUE COMMENT '學號',name VARCHAR(20) NOT NULL,qq VARCHAR(20)
);

• 字段說明：

  • id：主鍵，自增，無符號整數。

  • sn：唯一學號，不可為空。

  • name：姓名，長度限制為20字符。

  • qq：QQ號，可為空。

2.2 單行數據插入

全列插入時，需按表定義的列順序提供所有值：

INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);

• 注意事項：

  • 若省略自增主鍵（如id），MySQL會自動填充。

  • 必須保證值的順序與表結構完全一致。

2.3 多行數據插入

指定列插入，適用于部分字段賦值：

INSERT INTO students (id, sn, name) VALUES(102, 20001, '曹孟德'),(103, 20002, '孫仲謀');

• 優勢：批量插入提升效率，減少數據庫連接開銷。

2.4 插入沖突處理

當主鍵或唯一鍵沖突時，使用ON DUPLICATE KEY UPDATE進行更新：

INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大師')ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大師';

• 返回值說明：

  • 0 rows affected：沖突數據與更新值相同。

  • 1 row affected：插入新數據。

  • 2 rows affected：更新沖突數據。

或使用REPLACE直接替換沖突記錄：

REPLACE INTO students (sn, name) VALUES (20001, '曹阿晴');

• 注意：REPLACE會刪除原記錄后插入新數據，可能導致自增ID不連續。

---

3. Retrieve操作詳解

3.1 基礎查詢

全列查詢（慎用）

SELECT * FROM exam_result;

• 缺點：傳輸數據量大，可能影響索引性能。

指定列查詢

SELECT id, name, english FROM exam_result;

表達式查詢

計算總分并指定別名：

SELECT name, chinese + math + english AS 總分 FROM exam_result;

結果去重

SELECT DISTINCT math FROM exam_result;

3.2 條件查詢（WHERE子句）

比較運算符

-- 英語不及格的同學
SELECT name, english FROM exam_result WHERE english < 60;-- 語文成績在80到90分之間
SELECT name, chinese FROM exam_result 
WHERE chinese BETWEEN 80 AND 90;

邏輯運算符

-- 姓孫且不叫孫某的同學
SELECT name FROM exam_result 
WHERE name LIKE '孫%' AND name NOT LIKE '孫_';

NULL處理

-- 查詢QQ號已知的同學
SELECT name, qq FROM students WHERE qq IS NOT NULL;

3.3 結果排序（ORDER BY）

-- 按數學成績降序，英語成績升序
SELECT name, math, english FROM exam_result 
ORDER BY math DESC, english ASC;

3.4 分頁查詢（LIMIT與OFFSET）

-- 每頁3條數據，查詢第2頁
SELECT * FROM exam_result 
ORDER BY id LIMIT 3 OFFSET 3;

• 注意：OFFSET起始位置從0開始。

---

4. Update操作詳解

4.1 單字段更新

UPDATE exam_result SET math = 80 WHERE name = '孫悟空';

4.2 多字段更新

UPDATE exam_result SET math = 60, chinese = 70 
WHERE name = '曹孟德';

4.3 基于表達式更新

-- 總成績倒數前三的數學成績加30分
UPDATE exam_result SET math = math + 30 
ORDER BY chinese + math + english LIMIT 3;

---

5. Delete操作詳解

5.1 刪除指定數據

DELETE FROM exam_result WHERE name = '孫悟空';

5.2 清空表數據

• DELETE：逐行刪除，可回滾，保留自增計數。

  DELETE FROM for_delete;

• TRUNCATE：直接清空表，不可回滾，重置自增ID。

  TRUNCATE TABLE for_truncate;

---

6. 高級查詢技巧

6.1 聚合函數

-- 統計數學成績總分
SELECT SUM(math) FROM exam_result;-- 計算平均總分
SELECT AVG(chinese + math + english) AS 平均總分 FROM exam_result;

6.2 GROUP BY與HAVING

-- 按部門統計平均工資
SELECT deptno, AVG(sal) FROM EMP GROUP BY deptno;-- 篩選平均工資低于2000的部門
SELECT deptno, AVG(sal) AS avg_sal FROM EMP 
GROUP BY deptno HAVING avg_sal < 2000;

---

7. 實戰案例解析

案例1：去重插入

-- 創建臨時表并插入去重數據
CREATE TABLE no_duplicate_table LIKE duplicate_table;
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
RENAME TABLE duplicate_table TO old_duplicate_table, no_duplicate_table TO duplicate_table;

案例2：分頁查詢

-- 按ID分頁，每頁3條
SELECT * FROM exam_result ORDER BY id LIMIT 3 OFFSET 6;

---

8. 總結與最佳實踐

1. 避免全列查詢：減少數據傳輸量，提升性能。

2. 謹慎使用UPDATE/DELETE：操作前備份數據，或添加LIMIT限制。

3. 合理使用索引：WHERE和ORDER BY字段建立索引可加速查詢。

4. 事務管理：批量操作時開啟事務，確保數據一致性。

通過本文的系統學習，讀者可掌握MySQL核心操作，應對日常開發中的各類數據管理需求。