MySQL 增刪改查 (CRUD) 基礎語法完整速查表

MySQL 增刪改查 (CRUD) 基礎語法完整速查表

本文系統整理 MySQL 最核心的 DML（數據操作語言）語法，包含建庫建表、結構查看、四大基本操作的多種寫法與實戰示例，適合初學者快速入門與老手查漏補缺。

一、環境準備：建庫建表

-- 1. 創建數據庫
CREATE DATABASE students;

-- 2. 進入數據庫
USE students;

-- 3. 創建表 class1
CREATE TABLE class1 (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL UNIQUE,
    school VARCHAR(30) DEFAULT 'chengdu_collage'
);

-- 4. 查看表結構
DESC class1;
-- 或 SHOW CREATE TABLE class1\G

輸出示例：

+--------+----------------------+------+-----+-----------------+----------------+
| Field  | Type                 | Null | Key | Default         | Extra          |
+--------+----------------------+------+-----+-----------------+----------------+
| id     | smallint(5) unsigned | NO   | PRI | NULL            | auto_increment |
| name   | varchar(30)          | NO   | UNI | NULL            |                |
| school | varchar(30)          | YES  |     | chengdu_collage |                |
+--------+----------------------+------+-----+-----------------+----------------+

二、INSERT 插入數據

1. VALUES 完整/部分列插入

-- 指定列（推薦，順序無關、擴展性強）
INSERT INTO class1 (name) VALUES ('john');

-- 全列插入（需按定義順序，DEFAULT 佔位自增列）
INSERT INTO class1 VALUES (DEFAULT, 'jobs', 'chengdu_agricultural_college');

-- 批量插入（單條 SQL，性能最優）
INSERT INTO class1 (name, school) VALUES 
('alice', 'shanghai_univ'),
('bob', DEFAULT),
('charlie', 'beijing_tech');

2. SET 語法（列名=值，可讀性強）

INSERT INTO class1 SET name='tom';
INSERT INTO class1 SET name='lues', school='chengdu_agricultural_college';

3. INSERT ... SELECT（從其他表複製）

INSERT INTO class1 (name, school) 
SELECT username, 'migrated' FROM old_students WHERE grade > 80;

三、UPDATE 修改數據

-- 基礎語法
UPDATE [LOW_PRIORITY] [IGNORE] table_name 
SET col1=val1, col2=val2, ... 
[WHERE condition] [ORDER BY ...] [LIMIT n];

-- 實例
UPDATE class1 SET name='lues2' WHERE name='lues';
-- 輸出: Rows matched: 1  Changed: 1  Warnings: 0

-- 多表更新（JOIN 語法）
UPDATE class1 c JOIN scores s ON c.name=s.student 
SET c.school='honor_class' WHERE s.total > 270;

關鍵提示：永遠帶 WHERE 條件，否則全表更新；生產環境先 SELECT 驗證影響行數。

四、DELETE 刪除數據

-- 基礎語法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name 
[WHERE condition] [ORDER BY ...] [LIMIT n];

-- 實例
DELETE FROM class1 WHERE name='lues2';

-- 多表刪除
DELETE c FROM class1 c JOIN graduates g ON c.name=g.student;

對比 TRUNCATE：TRUNCATE TABLE class1 為 DDL，不可回滾、不觸發觸發器、重置自增 ID、速度極快；DELETE 為 DML，可回滾、觸發器生效、不重置自增 ID。

五、SELECT 查詢數據

1. 基礎查詢

SELECT * FROM class1;
SELECT id, name FROM class1;

2. 條件過濾

SELECT * FROM class1 WHERE id > 1;
SELECT * FROM class1 WHERE name LIKE 'j%' AND school != 'chengdu_collage';

3. 分組聚合

SELECT school, COUNT(*) AS cnt FROM class1 GROUP BY school;
SELECT school, COUNT(*) FROM class1 GROUP BY school HAVING COUNT(*) > 1;

4. 排序分頁

SELECT * FROM class1 ORDER BY id DESC LIMIT 2 OFFSET 1;  -- 第 2-3 行
SELECT * FROM class1 ORDER BY id DESC LIMIT 1, 2;        -- 同義寫法

5. 連接查詢（擴展）

-- 內連接
SELECT c.name, s.score FROM class1 c INNER JOIN scores s ON c.id=s.student_id;
-- 左連接（保留左表所有行）
SELECT c.name, s.score FROM class1 c LEFT JOIN scores s ON c.id=s.student_id;

六、常用函數與操作符速查

七、最佳實踐口訣

1. 建表必備：主鍵、非空、默認值、註釋、引擎 InnoDB、字符集 utf8mb4。
2. 插入優先：批量 VALUES、指定列名、避免 SELECT *。
3. 更新刪除：先 SELECT 驗證、帶 WHERE、小批次提交、留意影響行數。
4. 查詢原則：只取所需列、善用覆蓋索引、EXPLAIN 分析、避免隱式轉換。
5. 事務邊界：業務邏輯原子操作包裹在 START TRANSACTION ... COMMIT/ROLLBACK 中。