DML：增删改查

3.1 INSERT — 插入数据

单行插入

SQL-- 标准写法：指定列名（推荐，防止列顺序变化导致出错）
INSERT INTO users (username, email, role)
VALUES ('alice', 'alice@example.com', 'user');

-- 简写：省略列名，值必须与表定义顺序完全一致（不推荐）
INSERT INTO users VALUES (NULL, 'bob', 'bob@example.com', '$2b$...', NULL, NULL, 'user', NOW(), NOW());

多行批量插入（推荐，性能远优于多次单行插入）

SQLINSERT INTO users (username, email)
VALUES
  ('carol', 'carol@example.com'),
  ('dave',  'dave@example.com'),
  ('eve',   'eve@example.com');
-- 一条 SQL 插入 3 行，比 3 条 INSERT 快约 3-5 倍

INSERT ... SELECT — 从查询结果插入

SQL-- 将 old_users 表中 2024 年前注册的用户复制到 archived_users
INSERT INTO archived_users (username, email, created_at)
SELECT username, email, created_at
FROM users
WHERE created_at < '2024-01-01';

INSERT OR IGNORE / ON DUPLICATE KEY UPDATE

SQL-- 如果主键/唯一键冲突，忽略本次插入（MySQL）
INSERT IGNORE INTO users (username, email)
VALUES ('alice', 'alice@example.com');

-- 如果冲突则更新指定字段（UPSERT）
INSERT INTO users (username, email, role)
VALUES ('alice', 'alice@example.com', 'admin')
ON DUPLICATE KEY UPDATE
  role = 'admin';        -- 更新 role 字段

-- PostgreSQL 的 UPSERT 语法
INSERT INTO users (username, email)
VALUES ('alice', 'new@example.com')
ON CONFLICT (username) DO UPDATE SET
  email = EXCLUDED.email;  -- EXCLUDED 代表尝试插入的那行数据

3.2 SELECT — 查询数据

基本 SELECT 语法

SQL-- 查询所有列（生产环境避免 SELECT *）
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

-- 列别名（AS 可省略，但建议保留）
SELECT
  id,
  username AS name,           -- 显示列名为 name
  email    AS contact_email,
  created_at AS '注册时间'      -- 中文别名需加引号
FROM users;

-- DISTINCT 去重（去掉重复的行）
SELECT DISTINCT role FROM users;  -- 返回不重复的角色列表

-- 表达式和计算
SELECT
  username,
  UPPER(email)                         AS email_upper,
  DATEDIFF(NOW(), created_at)           AS days_since_register,
  price * 1.1                          AS price_with_tax
FROM users;

3.3 WHERE — 条件过滤

SQL-- 比较运算符：=  !=(<>)  >  <  >=  <=
SELECT * FROM users WHERE role = 'admin';
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price != 0;

-- 逻辑运算符：AND  OR  NOT
SELECT * FROM products
WHERE price > 50 AND price < 200
  AND NOT status = 'archived';

-- BETWEEN...AND（包含两端边界）
SELECT * FROM products WHERE price BETWEEN 50 AND 200;

-- IN / NOT IN（枚举匹配，比多个 OR 更清晰）
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- LIKE 模糊匹配（% 匹配任意多字符，_ 匹配一个字符）
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- 以 @gmail.com 结尾
SELECT * FROM users WHERE username LIKE 'a%';         -- 以 a 开头
SELECT * FROM posts  WHERE title LIKE '__数据库';     -- 两个任意字符+数据库

-- IS NULL / IS NOT NULL（空值判断，不能用 = NULL！）
SELECT * FROM posts WHERE published_at IS NULL;     -- 查未发布的文章
SELECT * FROM users WHERE bio IS NOT NULL;          -- 填了个人简介的用户

3.4 ORDER BY — 排序

SQL-- 升序（ASC 是默认，可省略）
SELECT * FROM products ORDER BY price ASC;

-- 降序
SELECT * FROM products ORDER BY price DESC;

-- 多列排序：先按 category 升序，同类别内按 price 降序
SELECT * FROM products
ORDER BY category ASC, price DESC;

-- NULL 的排序位置（标准 SQL：NULL 最大）
-- MySQL：NULL 排在最前（升序时）
-- PostgreSQL：NULL 排在最后（升序时），可用 NULLS FIRST/LAST 控制
SELECT * FROM users ORDER BY bio ASC NULLS LAST;  -- PostgreSQL

-- ORDER BY 列序号（不推荐，可读性差）
SELECT username, email, created_at FROM users ORDER BY 3 DESC;
-- 等同于 ORDER BY created_at DESC

3.5 LIMIT 与 OFFSET — 分页

SQL-- 取前 10 条
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

-- 分页：第 1 页（偏移 0，取 10 条）
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 0;

-- 第 2 页（偏移 10，取 10 条）
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 10;

-- 第 3 页（偏移 20）
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 20;

-- 公式：OFFSET = (页码 - 1) × 每页条数
-- 第 N 页：LIMIT page_size OFFSET (N - 1) * page_size

-- MySQL 的另一种语法（不推荐，容易混淆顺序）
SELECT * FROM posts LIMIT 20, 10;  -- LIMIT offset, count（先偏移再取）

3.6 UPDATE — 更新数据

SQL-- 单列更新
UPDATE users
SET role = 'admin'
WHERE id = 1;

-- 多列更新（用逗号分隔）
UPDATE users
SET
  email = 'newemail@example.com',
  bio   = 'Updated bio text'
WHERE id = 1;

-- 基于当前值计算更新
UPDATE products
SET stock = stock - 1
WHERE id = 42 AND stock > 0;  -- 库存减 1，防止减为负数

-- 更新全表某列（注意！影响所有行）
UPDATE posts
SET view_count = 0;  -- 没有 WHERE，重置所有文章浏览量

3.7 DELETE — 删除数据

SQL-- 删除单行
DELETE FROM users WHERE id = 42;

-- 删除满足条件的多行
DELETE FROM posts
WHERE status = 'archived'
  AND created_at < '2023-01-01';

-- 删除全表（记录每行删除日志，可回滚）
DELETE FROM posts;

-- 清空全表更高效的方式（不可回滚，重置自增）
TRUNCATE TABLE posts;

DELETE vs TRUNCATE vs DROP 对比

操作	删除范围	WHERE	速度	可回滚	触发器
DELETE	数据（可条件）	支持	慢（逐行）	是	触发
TRUNCATE	全部数据	不支持	快	否（MySQL）	不触发
DROP TABLE	数据+表结构	不支持	快	否	不触发

3.8 实战：电商 orders 表的完整 CRUD

准备测试数据

SQL-- 创建测试用的 orders 表
CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  quantity    INT NOT NULL DEFAULT 1,
  unit_price  DECIMAL(10,2) NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  status      ENUM('pending','paid','shipped','completed','cancelled')
              NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 批量插入测试数据
INSERT INTO orders (user_id, product_name, quantity, unit_price, total_price, status)
VALUES
  (1, '机械键盘',    1, 599.00, 599.00,  'completed'),
  (1, '鼠标',        2, 129.00, 258.00,  'shipped'),
  (2, '显示器',      1, 1999.00,1999.00, 'paid'),
  (3, '耳机',        1, 299.00, 299.00,  'pending'),
  (2, '键盘膜',      3, 29.00,  87.00,   'cancelled');

示例数据

id	user_id	product_name	quantity	unit_price	total_price	status
1	1	机械键盘	1	599.00	599.00	completed
2	1	鼠标	2	129.00	258.00	shipped
3	2	显示器	1	1999.00	1999.00	paid
4	3	耳机	1	299.00	299.00	pending
5	2	键盘膜	3	29.00	87.00	cancelled

常用查询场景

SQL-- 查询用户 1 的所有订单（按时间降序）
SELECT id, product_name, total_price, status, created_at
FROM orders
WHERE user_id = 1
ORDER BY created_at DESC;

-- 查询所有未完成的订单（金额大于 200 元）
SELECT * FROM orders
WHERE status NOT IN ('completed', 'cancelled')
  AND total_price > 200
ORDER BY total_price DESC;

-- 更新订单状态：支付成功 → 已发货
UPDATE orders
SET status = 'shipped'
WHERE id = 3 AND status = 'paid';  -- 多一个条件防止重复操作

-- 分页：第 1 页，每页 2 条，按 id 升序
SELECT * FROM orders
ORDER BY id
LIMIT 2 OFFSET 0;

-- 删除已取消的订单
DELETE FROM orders
WHERE status = 'cancelled';