第 03 章：SQL 基础

zjc 于 2026-01-03 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 SQL 是和 MySQL 对话的语言。写 SQL 容易，写清楚、稳定、可维护的 SQL 不容易。本章从数据库、表、列、行的基本概念开始，覆盖查询、过滤、排序、分页、事务和常见错误。

3.1 数据库、表、行、列

MySQL 的逻辑层级如下：

Server
  +-- Database / Schema
       +-- Table
            +-- Column：字段，定义数据类型和约束
            +-- Row：记录，代表一个具体实体或关系

在 MySQL 中，DATABASE 和 SCHEMA 基本同义。日常操作通常说“库”和“表”。

创建数据库：

CREATE DATABASE IF NOT EXISTS shop
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

删除数据库：

DROP DATABASE IF EXISTS shop;

DROP DATABASE 会删除库下所有对象，只应在明确允许丢数据的学习或重建场景使用。

3.2 SQL 分类

分类	全称	常见语句
DDL	Data Definition Language	`CREATE`、`ALTER`、`DROP`、`TRUNCATE`
DML	Data Manipulation Language	`SELECT`、`INSERT`、`UPDATE`、`DELETE`
DCL	Data Control Language	`GRANT`、`REVOKE`、`CREATE USER`
TCL	Transaction Control Language	`BEGIN`、`COMMIT`、`ROLLBACK`、`SAVEPOINT`

不同资料对 SELECT 和 TRUNCATE 的归类略有差异，理解行为比记分类更重要。

3.3 建表与修改表

创建一张简单表：

CREATE TABLE tags (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tag_name VARCHAR(32) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_tag_name (tag_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

增加字段：

ALTER TABLE tags
  ADD COLUMN status TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER tag_name;

修改字段：

ALTER TABLE tags
  MODIFY COLUMN tag_name VARCHAR(64) NOT NULL COMMENT '标签名';

增加索引：

ALTER TABLE tags
  ADD INDEX idx_status_created (status, created_at);

删除字段：

ALTER TABLE tags DROP COLUMN status;

生产修改表结构前必须确认：

表数据量和当前读写峰值；
MySQL 版本与 DDL 算法；
是否有锁表风险；
是否可回滚；
是否已备份。

3.4 查询语句结构

一条 SELECT 的逻辑书写顺序如下：

SELECT product_name, category, price
FROM products
WHERE status = 1
  AND price >= 50
GROUP BY category
HAVING COUNT(*) >= 1
ORDER BY price DESC
LIMIT 10;

常见子句：

子句	作用
`SELECT`	选择输出列
`FROM`	指定数据来源
`JOIN`	关联其他表
`WHERE`	行级过滤
`GROUP BY`	分组
`HAVING`	分组后过滤
`ORDER BY`	排序
`LIMIT`	限制返回行数

注意：逻辑书写顺序不等于执行顺序。优化器会根据统计信息和代价选择实际执行方式。

3.5 查询列与表达式

查询所有列：

SELECT * FROM products;

生产中应避免在程序里无条件 SELECT *，原因：

传输不必要的列；
破坏覆盖索引机会；
新增大字段后应用可能误加载；
代码对结果字段的依赖不清晰；
网络和 ORM 映射成本更高。

明确列名：

SELECT id, product_name, category, price
FROM products;

使用别名和表达式：

SELECT
  id AS product_id,
  product_name AS name,
  price * 0.9 AS discount_price
FROM products;

条件表达式：

SELECT
  product_name,
  price,
  CASE
    WHEN price < 50 THEN 'LOW'
    WHEN price < 200 THEN 'MIDDLE'
    ELSE 'HIGH'
  END AS price_level
FROM products;

常用函数：

函数	示例	说明
`IFNULL`	`IFNULL(paid_at, '未支付')`	空值兜底
`CONCAT`	`CONCAT(order_no, '-', status)`	字符串拼接
`UPPER`	`UPPER(status)`	转大写
`DATE_FORMAT`	`DATE_FORMAT(paid_at, '%Y-%m-%d')`	格式化日期
`ROUND`	`ROUND(price, 2)`	四舍五入
`ABS`	`ABS(-1)`	绝对值

3.6 WHERE 条件

查询上架且有库存的商品：

SELECT id, product_name, price
FROM products
WHERE status = 1
  AND stock > 0;

查询两个类目：

SELECT id, product_name, category
FROM products
WHERE category IN ('FOOD', 'BOOK');

范围查询：

SELECT id, order_no, total_amount, created_at
FROM orders
WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at < '2026-09-01 00:00:00';

模糊查询：

SELECT id, product_name
FROM products
WHERE product_name LIKE 'My%';

LIKE 'My%' 可以利用索引前缀；LIKE '%Book%' 通常无法使用普通 B+Tree 索引。全文搜索应考虑 FULLTEXT 或 Elasticsearch。

NULL 判断

NULL 表示未知或缺失，不等于空字符串或 0。

SELECT order_no, paid_at
FROM orders
WHERE paid_at IS NULL;

SELECT order_no, paid_at
FROM orders
WHERE paid_at IS NOT NULL;

以下写法不会返回 paid_at 为 NULL 的行：

SELECT order_no
FROM orders
WHERE paid_at != NULL;

空值比较会得到 UNKNOWN，这是初学者最常见的错误之一。

3.7 排序与分页

排序：

SELECT id, product_name, price
FROM products
WHERE category = 'DIGITAL'
ORDER BY price DESC, id DESC;

分页：

SELECT id, product_name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 0;

第 2 页：

SELECT id, product_name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 10;

也可以写成：

SELECT id, product_name, price
FROM products
ORDER BY id
LIMIT 10, 10;

深分页示例：

SELECT id, product_name
FROM products
ORDER BY id
LIMIT 10 OFFSET 1000000;

MySQL 仍要扫描并丢弃前 100 万行。更高效的方式是游标分页：

SELECT id, product_name
FROM products
WHERE id > 1000000
ORDER BY id
LIMIT 10;

3.8 INSERT

插入单行：

INSERT INTO products
  (merchant_id, product_name, category, price, stock)
VALUES
  (1, 'Banana', 'FRUIT', 5.50, 120);

批量插入：

INSERT INTO products
  (merchant_id, product_name, category, price, stock)
VALUES
  (1, 'Orange', 'FRUIT', 6.80, 90),
  (1, 'Bread', 'FOOD', 9.90, 40),
  (2, 'Redis Book', 'BOOK', 89.00, 30);

指定列插入比省略列名更安全，避免表结构变化后语义漂移。

插入时处理唯一键冲突：

INSERT INTO products
  (merchant_id, product_name, category, price, stock)
VALUES
  (1, 'Banana', 'FRUIT', 5.20, 150)
ON DUPLICATE KEY UPDATE
  price = VALUES(price),
  stock = VALUES(stock);

MySQL 8.0.20 开始推荐使用别名语法：

INSERT INTO products
  (merchant_id, product_name, category, price, stock)
VALUES
  (1, 'Banana', 'FRUIT', 5.20, 150) AS new_row
ON DUPLICATE KEY UPDATE
  price = new_row.price,
  stock = new_row.stock;

3.9 UPDATE 与 DELETE

更新：

UPDATE products
SET stock = stock - 1,
    status = 1
WHERE id = 1
  AND stock > 0;

删除：

DELETE FROM tags
WHERE id = 10;

生产红线：

更新和删除必须带 WHERE；
先 SELECT 或 COUNT 确认影响范围；
大量修改分批执行；
开启事务并检查后再提交；
线上手工操作要有双人复核。

安全执行流程：

START TRANSACTION;

SELECT id, stock
FROM products
WHERE id = 1
FOR UPDATE;

UPDATE products
SET stock = stock - 1
WHERE id = 1
  AND stock > 0;

-- 确认影响行数正确
COMMIT;

3.10 事务初体验

事务把一组 SQL 变成一个原子执行单元：

START TRANSACTION;

INSERT INTO orders
  (order_no, user_id, merchant_id, status, total_amount)
VALUES
  ('20260825000001', 1, 1, 'CREATED', 25.50);

INSERT INTO order_items
  (order_id, product_id, product_name, unit_price, quantity, subtotal)
VALUES
  (LAST_INSERT_ID(), 1, 'Apple', 8.50, 3, 25.50);

COMMIT;

回滚：

START TRANSACTION;

UPDATE products
SET stock = stock - 100
WHERE id = 1;

ROLLBACK;

事务不是越大越安全。长事务会持有锁和 Undo 版本，影响并发，也会拖慢主从复制。业务边界应尽量短，耗时操作不应包在数据库事务中。

3.11 SQL 编写规范

推荐规则：

关键字统一大小写；
明确字段列表；
复杂查询缩进分层；
表使用别名，别名有业务含义；
每条 DML 都能说清影响范围；
查询条件避免隐式类型转换；
禁止拼 SQL，使用参数绑定；
对慢查询加注释说明用途；
生产 SQL 必须有索引评估；
报表 SQL 与交易 SQL 分离。

示例：

SELECT
  o.order_no,
  o.status,
  o.total_amount,
  u.username
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE o.user_id = 1
  AND o.created_at >= '2026-08-01'
ORDER BY o.created_at DESC
LIMIT 20;

3.12 常见错误

错误	原因	处理
`Unknown database`	库不存在或连接错实例	检查库名和环境
`Unknown column`	字段名或表别名错误	检查 SELECT 和别名
`Access denied`	权限不足	检查用户、密码、来源主机
`Duplicate entry`	唯一键冲突	改用幂等插入或处理冲突
`Data too long`	字段长度不足	扩字段或修正输入
`Truncated incorrect DOUBLE`	字符串和数字隐式转换	修正类型
`Lock wait timeout`	锁等待超时	分析锁冲突和事务边界

本章小结

SQL 的核心是描述“要什么数据”，而不是“怎么取数据”。掌握 SELECT、WHERE、ORDER BY、LIMIT、INSERT、UPDATE、DELETE 和事务后，就能完成日常开发。生产 SQL 要明确列名、控制影响范围、避免深分页和隐式类型转换，并用事务保护必须原子成功的业务动作。

思考题

WHERE 和 HAVING 的区别是什么？
为什么程序中不建议使用 SELECT *？
NULL = NULL、NULL IS NULL、IFNULL(NULL, 0) 分别返回什么？
深分页为什么慢？如何用游标分页改写？
编写一条安全更新 SQL 时，你会做哪些检查？