SQL 教程

SQL 教程

SQL（Structured Query Language，结构化查询语言）是操作关系型数据库的标准语言。无论 MySQL、PostgreSQL、SQL Server 还是 Oracle，都使用 SQL 来查询和操作数据，是数据库工程师和数据从业者的必备技能。

SQL 能做什么

• 查询数据：从表中获取需要的信息
• 操作数据：插入、更新、删除记录
• 定义数据：创建/修改表、数据库结构
• 控制权限：管理用户访问权限

SQL 的核心分类

分类	全称	用途	示例
DDL	数据定义语言	定义结构	CREATE / ALTER / DROP
DML	数据操作语言	操作数据	SELECT / INSERT / UPDATE / DELETE
DCL	数据控制语言	权限管理	GRANT / REVOKE

一个完整的 SQL 示例

-- 查询已发布的教程，按更新时间倒序
SELECT id, title, updated
FROM tutorials
WHERE is_published = 1
ORDER BY updated DESC
LIMIT 10;

关系型数据库的概念

• 表（Table）：数据的二维结构，行=记录，列=字段
• 主键（Primary Key）：唯一标识一行
• 外键（Foreign Key）：关联其他表
• 索引（Index）：加速查询

主流数据库

数据库	特点
MySQL	开源、最流行、Web 首选
PostgreSQL	功能强大、标准兼容好
SQL Server	微软生态、企业级
SQLite	轻量嵌入式、零配置

SQL 简介

SQL 简介：SQL 的历史、标准与基本使用方式。

SQL 的诞生

• 1974 年由 IBM 的 Donald Chamberlin 提出
• 1979 年 Oracle 发布首个商用 SQL 数据库
• 1986 年成为 ANSI 标准，1987 年成为 ISO 标准

SQL 与 NoSQL

对比项	SQL（关系型）	NoSQL（非关系型）
数据模型	表/行/列	文档/键值/图
结构	固定 schema	灵活 schema
事务	ACID 强一致	最终一致（多）
扩展	垂直为主	水平扩展好
代表	MySQL/PostgreSQL	MongoDB/Redis

如何执行 SQL

• 命令行：mysql、psql 客户端
• 图形工具：Navicat、DBeaver、phpMyAdmin
• 编程语言：Python（pymysql）、Java（JDBC）、Node（mysql2）

# 连接 MySQL
mysql -u root -p

# 执行查询
mysql> SELECT VERSION();

SQL 语句的书写习惯

• 关键字大写（SELECT、FROM）—— 便于阅读，非强制
• 每条语句以分号 ; 结尾
• -- 表示单行注释
• 多行书写便于复杂查询可读

SQL 语法

掌握 SQL 基础语法：语句结构、关键字、常用约定。

SQL 语句结构

SQL 语句由关键字、表名/列名、条件表达式组成：

SELECT 列名 FROM 表名 WHERE 条件 ORDER BY 排序;

基础示例

-- 最简单的查询
SELECT * FROM users;

-- 指定列
SELECT name, email FROM users;

-- 带条件
SELECT * FROM users WHERE age > 18;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 限制条数（MySQL）
SELECT * FROM users LIMIT 10;

注释

-- 单行注释（两个减号 + 空格）

# 单行注释（MySQL 支持 #）

/* 多行注释
   可以写多行 */

大小写敏感

SQL 关键字不区分大小写，但表名/列名在部分数据库（如 MySQL 的 Linux 环境）区分大小写。建议：关键字大写、标识符小写。

语句分类速查

类型	关键字
查询	SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY
操作	INSERT、UPDATE、DELETE
定义	CREATE、ALTER、DROP
其他	JOIN、UNION、LIMIT、DISTINCT

SQL SELECT

SELECT 是 SQL 最常用的语句，用于从数据库中查询数据。

基本语法

SELECT 列1, 列2, ... FROM 表名;

查询所有列

-- * 表示所有列
SELECT * FROM tutorials;

查询指定列

SELECT title, desc FROM tutorials;

列别名

-- AS 起别名
SELECT title AS 标题, read_time AS 阅读时长 FROM tutorials;

-- 省略 AS（MySQL 支持）
SELECT title 标题 FROM tutorials;

常量列与计算

-- 常量
SELECT 'CodeNest' AS site, title FROM tutorials;

-- 计算
SELECT price, price * 0.9 AS discount_price FROM products;

-- 拼接（MySQL）
SELECT CONCAT(title, ' - ', read_time) AS info FROM tutorials;

去重

-- DISTINCT 去重
SELECT DISTINCT category_id FROM tutorials;

条件查询

SELECT title FROM tutorials WHERE category_id = 1;

SELECT 执行顺序

逻辑执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。理解顺序有助于写出高效查询。

SQL SELECT DISTINCT

SELECT DISTINCT：查询不重复的唯一值。

基本用法

-- 查询所有不重复的分类 ID
SELECT DISTINCT category_id FROM tutorials;

与普通 SELECT 对比

-- 含重复
SELECT category_id FROM tutorials;   -- 结果可能有很多重复

-- 去重
SELECT DISTINCT category_id FROM tutorials;  -- 每个分类只出现一次

多列去重

-- 多列组合去重（组合唯一）
SELECT DISTINCT category_id, is_published FROM tutorials;

统计唯一值数量

-- COUNT(DISTINCT 列)：统计不重复数量
SELECT COUNT(DISTINCT category_id) AS categories FROM tutorials;

DISTINCT 与 NULL

DISTINCT 会把 NULL 视为一个值（多个 NULL 只返回一个）。

替代方案：GROUP BY

-- GROUP BY 也能去重，还支持聚合
SELECT category_id FROM tutorials GROUP BY category_id;

SQL WHERE

WHERE 子句用于按条件过滤记录，只返回满足条件的行。

基本语法

SELECT 列 FROM 表 WHERE 条件;

常用比较运算符

运算符	含义	示例
=	等于	WHERE age = 25
<> / !=	不等于	WHERE status != 'done'
> / >=	大于/大于等于	WHERE price > 100
< / <=	小于/小于等于	WHERE age <= 18

数值与字符串条件

-- 数值
SELECT * FROM products WHERE price > 50;

-- 字符串（用单引号）
SELECT * FROM users WHERE name = '张三';

-- 日期
SELECT * FROM orders WHERE created_at > '2026-01-01';

逻辑运算符

-- AND：都满足
SELECT * FROM products WHERE price > 50 AND stock > 0;

-- OR：任一满足
SELECT * FROM users WHERE city = '北京' OR city = '上海';

-- NOT：取反
SELECT * FROM products WHERE NOT price > 100;

其他常用条件

-- 区间
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

-- 集合
SELECT * FROM users WHERE city IN ('北京', '上海', '广州');

-- 模糊匹配
SELECT * FROM tutorials WHERE title LIKE '%Java%';

-- 空值判断（不能用 = NULL）
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

SQL AND & OR

AND 与 OR：组合多个查询条件的逻辑运算符。

AND（且）

-- 同时满足所有条件
SELECT * FROM products
WHERE price > 50 AND stock > 0 AND status = 'active';

OR（或）

-- 满足任一条件即可
SELECT * FROM users
WHERE city = '北京' OR city = '上海';

括号控制优先级

AND 优先级高于 OR，复杂条件用括号明确逻辑：

-- 无括号：(price > 50 AND stock > 0) OR status = 'vip'
SELECT * FROM products
WHERE price > 50 AND stock > 0 OR status = 'vip';

-- 加括号：改变含义
SELECT * FROM products
WHERE price > 50 AND (stock > 0 OR status = 'vip');

NOT 组合

-- NOT (A OR B) = NOT A AND NOT B
SELECT * FROM users
WHERE NOT (city = '北京' OR city = '上海');

-- 等价写法
SELECT * FROM users
WHERE city != '北京' AND city != '上海';

与 IN/BETWEEN 结合

SELECT * FROM orders
WHERE status = 'paid'
  AND (amount > 1000 OR priority = 'high');

SQL ORDER BY

ORDER BY：对查询结果排序。

基本用法

-- 升序（默认）
SELECT * FROM tutorials ORDER BY sort_order;

-- 降序
SELECT * FROM tutorials ORDER BY sort_order DESC;

-- 显式升序
SELECT * FROM tutorials ORDER BY sort_order ASC;

多列排序

-- 先按分类，再按更新时间倒序
SELECT * FROM tutorials
ORDER BY category_id ASC, updated DESC;

按表达式排序

-- 按计算值排序
SELECT name, price * quantity AS total
FROM orders
ORDER BY total DESC;

-- 按别名排序
SELECT title, LENGTH(title) AS len FROM tutorials
ORDER BY len DESC;

NULL 的排序

-- MySQL：NULL 在升序时排最前
SELECT * FROM users ORDER BY updated_at ASC;
-- 想让它排最后：
SELECT * FROM users ORDER BY updated_at IS NULL, updated_at ASC;

排序 + 分页

-- 最新 10 条
SELECT * FROM tutorials
ORDER BY updated DESC
LIMIT 10;

-- 第 2 页（每页 10 条）
SELECT * FROM tutorials
ORDER BY updated DESC
LIMIT 10 OFFSET 10;

SQL INSERT INTO

INSERT INTO：向表中插入新记录。

基本语法

-- 指定列插入（推荐）
INSERT INTO tutorials (title, cat_id, desc)
VALUES ('SQL 教程', 3, '数据库基础');

-- 全部列插入（需按列顺序）
INSERT INTO tutorials VALUES (100, 3, 'SQL 教程', '', '');

插入多条记录

INSERT INTO users (name, email) VALUES
('张三', 'zhang@example.com'),
('李四', 'li@example.com'),
('王五', 'wang@example.com');

默认值与 NULL

-- 省略有默认值的列
INSERT INTO users (name) VALUES ('赵六');
-- created_at 等列使用默认值

-- 显式插入 NULL
INSERT INTO users (name, email) VALUES ('孙七', NULL);

INSERT 与 SELECT 结合

-- 从其他表复制数据
INSERT INTO tutorials_backup (title, cat_id, desc)
SELECT title, cat_id, desc FROM tutorials
WHERE cat_id = 1;

注意事项

• 必填列（NOT NULL 无默认值）必须提供值
• 主键不能重复
• 外键必须存在于关联表
• 字符串和日期用单引号

SQL UPDATE

UPDATE：修改表中已有的记录。

基本语法

UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;

更新单条

UPDATE users SET email = 'new@example.com' WHERE id = 1;

更新多条

-- 所有已发布教程的阅读时长设为 10 分钟
UPDATE tutorials SET read_time = '10 分钟' WHERE is_published = 1;

更新多列

UPDATE users
SET name = '李四', email = 'li@example.com', updated_at = NOW()
WHERE id = 2;

基于原值更新

-- 浏览量 +1
UPDATE articles SET views = views + 1 WHERE id = 10;

-- 库存减 1
UPDATE products SET stock = stock - 1 WHERE id = 5;

⚠️ 安全警告

-- 不带 WHERE 会更新所有行！
UPDATE users SET status = 'inactive';   -- 危险！

-- 务必先 SELECT 验证条件
SELECT * FROM users WHERE id = 1;      -- 确认
UPDATE users SET status = 'inactive' WHERE id = 1;

SQL DELETE

DELETE：删除表中的记录。

基本语法

DELETE FROM 表名 WHERE 条件;

删除指定记录

-- 删除 id=1 的用户
DELETE FROM users WHERE id = 1;

-- 删除符合条件的多条
DELETE FROM logs WHERE created_at < '2025-01-01';

清空表（保留结构）

-- 删除所有记录（可回滚）
DELETE FROM logs;

-- 清空并重置自增（更快，不可回滚）
TRUNCATE TABLE logs;

⚠️ 安全警告

-- 不带 WHERE 删除所有！
DELETE FROM users;   -- 危险！

-- 删除前先确认
SELECT COUNT(*) FROM users WHERE id = 1;
DELETE FROM users WHERE id = 1;

DELETE 与 TRUNCATE 对比

对比项	DELETE	TRUNCATE
可加 WHERE	✅	❌
可回滚	✅（事务内）	❌（DDL）
重置自增	❌	✅
触发触发器	✅	❌
速度	慢（逐行）	快

SQL SELECT TOP

SELECT TOP：限制返回的记录数量（不同数据库语法不同）。

各数据库语法

数据库	语法
SQL Server	SELECT TOP 10 * FROM users
MySQL	SELECT * FROM users LIMIT 10
PostgreSQL	SELECT * FROM users LIMIT 10
Oracle	SELECT * FROM users FETCH FIRST 10 ROWS ONLY

MySQL LIMIT 用法

-- 前 10 条
SELECT * FROM users LIMIT 10;

-- 跳过 10 条取 10 条（第 2 页）
SELECT * FROM users LIMIT 10 OFFSET 10;

-- 简写：LIMIT 偏移量, 条数
SELECT * FROM users LIMIT 10, 10;

配合 ORDER BY 取 Top N

-- 浏览量最高的 5 篇文章
SELECT * FROM articles
ORDER BY views DESC
LIMIT 5;

-- 最新 3 篇
SELECT * FROM tutorials
ORDER BY updated DESC
LIMIT 3;

分页的通用写法

-- 第 N 页，每页 size 条：OFFSET = (N-1) * size
SELECT * FROM articles
ORDER BY id
LIMIT size OFFSET (N-1) * size;

-- 示例：第 3 页，每页 20 条
SELECT * FROM articles
ORDER BY id
LIMIT 20 OFFSET 40;

SQL LIKE

LIKE：模糊匹配查询，用于查找包含指定模式的文本。

基本语法

SELECT 列 FROM 表 WHERE 列 LIKE '模式';

两个通配符

通配符	含义
%	匹配任意多个字符（含 0 个）
_	匹配单个字符

常见模式

-- 以 "Java" 开头
SELECT * FROM tutorials WHERE title LIKE 'Java%';

-- 以 "教程" 结尾
SELECT * FROM tutorials WHERE title LIKE '%教程';

-- 包含 "Python"
SELECT * FROM tutorials WHERE title LIKE '%Python%';

-- 第二个字符是 "a"
SELECT * FROM users WHERE name LIKE '_a%';

-- 含下划线的（转义）
SELECT * FROM files WHERE name LIKE '%\_%' ESCAPE '\';

LIKE 与大小写

-- MySQL 默认不区分大小写（取决于排序规则）
SELECT * FROM tutorials WHERE title LIKE '%java%';
-- 会匹配 "Java" 和 "java"

-- PostgreSQL 区分，可用 ILIKE（PostgreSQL）
SELECT * FROM tutorials WHERE title ILIKE '%java%';

性能注意

• 前缀匹配（'Java%'）能利用索引
• 包含匹配（'%Java%'）无法用索引，全表扫描
• 大量模糊查询考虑全文索引或搜索引擎

SQL 通配符

SQL 通配符：LIKE 配合使用的模式匹配符号。

通配符总览

通配符	描述	示例
%	任意多个字符	'%at%' 匹配 cat/hat/at
_	单个字符	'_at' 匹配 cat/hat
[charlist]	字符集合（SQL Server）	'[ch]at' 匹配 cat/hat
[!charlist]	不在集合中（SQL Server）	'[!ch]at' 匹配 bat

MySQL 的替代：正则

-- MySQL 用 REGEXP 实现集合匹配
SELECT * FROM users WHERE name REGEXP '^[ab]';

-- 或 IN 配合
SELECT * FROM users WHERE name IN ('cat', 'hat');

通配符组合

-- 至少 3 个字符，以 s 结尾
SELECT * FROM words WHERE word LIKE '__s%';

-- 以 a 或 b 开头（MySQL 用正则）
SELECT * FROM words WHERE word REGEXP '^[ab]';

匹配特殊字符

-- 匹配含 % 的文本（MySQL）
SELECT * FROM codes WHERE code LIKE '%\%%';

-- 使用 ESCAPE 自定义转义
SELECT * FROM codes WHERE code LIKE '%!%%' ESCAPE '!';

SQL IN

IN：判断值是否在指定集合中。

基本语法

SELECT 列 FROM 表 WHERE 列 IN (值1, 值2, ...);

示例

-- 指定城市
SELECT * FROM users WHERE city IN ('北京', '上海', '广州');

-- 指定状态
SELECT * FROM orders WHERE status IN ('pending', 'paid');

-- 数字集合
SELECT * FROM products WHERE id IN (1, 5, 9, 20);

IN vs OR

-- IN 写法（清晰）
SELECT * FROM users WHERE city IN ('北京', '上海');

-- OR 写法（啰嗦）
SELECT * FROM users WHERE city = '北京' OR city = '上海';

IN 与子查询

-- 找出有订单的用户
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- 找出没有订单的用户
SELECT * FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

NOT IN

-- 排除某些值
SELECT * FROM products WHERE category NOT IN ('配件', '耗材');

IN 与 NULL 陷阱

-- 子查询含 NULL 时 NOT IN 结果为空！
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
-- 如果 orders.user_id 有 NULL，上面查不到任何结果

-- 稳妥做法：排除 NULL
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

SQL BETWEEN

BETWEEN：范围查询（含边界值）。

基本语法

SELECT 列 FROM 表 WHERE 列 BETWEEN 值1 AND 值2;

数值范围

-- 价格 50-100（含 50 和 100）
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

-- 等价写法
SELECT * FROM products WHERE price >= 50 AND price <= 100;

日期范围

-- 2026 年 1 月的订单
SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31 23:59:59';

-- 注意：只用日期时 BETWEEN 是含边界
SELECT * FROM orders
WHERE DATE(created_at) BETWEEN '2026-01-01' AND '2026-01-31';

文本范围

-- 按字母顺序（A-M 开头的名字）
SELECT * FROM users WHERE name BETWEEN 'A' AND 'N';

NOT BETWEEN

-- 范围之外
SELECT * FROM products WHERE price NOT BETWEEN 50 AND 100;

与日期函数结合

-- 近 7 天
SELECT * FROM orders
WHERE created_at BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW();

-- 本月
SELECT * FROM orders
WHERE created_at BETWEEN
  DATE_FORMAT(NOW(), '%Y-%m-01') AND NOW();

SQL 别名

SQL 别名：给表或列起临时名字，让查询更清晰。

列别名

-- 基本用法
SELECT title AS 标题, read_time AS 时长 FROM tutorials;

-- 计算列加别名
SELECT price * quantity AS total FROM order_items;

-- 函数结果加别名
SELECT COUNT(*) AS total_users FROM users;

表别名

-- 缩短表名（JOIN 常用）
SELECT t.title, c.name
FROM tutorials AS t
JOIN categories AS c ON t.cat_id = c.id;

-- 省略 AS
SELECT t.title FROM tutorials t;

自连接（表别名必需）

-- 员工表查上级（同一张表连接两次）
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

别名注意事项

• 别名只在当前查询有效
• 别名含空格/关键字时加引号：AS "total count"
• ORDER BY/GROUP BY 可用别名
• WHERE 中不能用别名（执行顺序原因）

-- ❌ WHERE 中不能用别名
SELECT price * quantity AS total FROM items WHERE total > 100;

-- ✅ 用 HAVING 或重复表达式
SELECT price * quantity AS total FROM items HAVING total > 100;
SELECT price * quantity AS total FROM items WHERE price * quantity > 100;

SQL 连接(JOIN)

SQL JOIN：联结多张表查询，是关系型数据库最强大的特性之一。

为什么需要 JOIN

关系型数据库把数据分散在多张表（规范化），通过外键关联。JOIN 把多张表的数据按关联条件合并查询。

表结构示例

categories（分类表）     tutorials（教程表）
id  name               id  title        cat_id
1   前端开发            1   HTML 教程    1
2   后端开发            2   FastAPI     2
3   数据库              3   MySQL       3

JOIN 的类型

类型	说明
INNER JOIN	两表都匹配的行
LEFT JOIN	左表全部 + 右表匹配
RIGHT JOIN	右表全部 + 左表匹配
FULL JOIN	两表全部（MySQL 不支持，用 UNION）
CROSS JOIN	笛卡尔积（少用）

基本 JOIN 示例

-- 查询教程及所属分类名
SELECT t.title, c.name AS category
FROM tutorials t
INNER JOIN categories c ON t.cat_id = c.id;

JOIN 的执行逻辑

1. 取左表每行
2. 按 ON 条件找右表匹配行
3. 匹配成功则合并输出，不匹配按 JOIN 类型处理

SQL INNER JOIN

INNER JOIN：只返回两表中都匹配的行，是最常用的 JOIN。

基本语法

SELECT 列 FROM 表A
INNER JOIN 表B ON 关联条件;

示例

-- 教程 + 分类（只有有分类的教程才返回）
SELECT t.title, c.name AS category
FROM tutorials t
INNER JOIN categories c ON t.cat_id = c.id;

-- 用户 + 订单
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

三表连接

SELECT t.title, c.name AS category, s.title AS section
FROM tutorials t
INNER JOIN categories c ON t.cat_id = c.id
INNER JOIN sections s ON s.tutorial_id = t.id;

加条件的 INNER JOIN

-- 只查前端分类的教程
SELECT t.title, c.name
FROM tutorials t
INNER JOIN categories c ON t.cat_id = c.id
WHERE c.name = '前端开发'
ORDER BY t.sort_order;

JOIN vs 子查询

-- 用 JOIN（推荐，性能好）
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 等价子查询
SELECT name, (SELECT amount FROM orders WHERE user_id = u.id) AS amount
FROM users u;

INNER JOIN 的省略写法

-- 逗号 + WHERE（旧式，等价 INNER JOIN）
SELECT t.title, c.name
FROM tutorials t, categories c
WHERE t.cat_id = c.id;

SQL LEFT JOIN

LEFT JOIN：返回左表全部行，右表无匹配时为 NULL。

基本语法

SELECT 列 FROM 表A
LEFT JOIN 表B ON 关联条件;

核心区别

-- 左表所有教程都返回，无分类的显示 NULL
SELECT t.title, c.name AS category
FROM tutorials t
LEFT JOIN categories c ON t.cat_id = c.id;

-- 结果：即使某些教程 cat_id 不存在，也会返回，category 为 NULL

LEFT JOIN vs INNER JOIN

对比项	INNER JOIN	LEFT JOIN
左表行	只保留匹配的	全部保留
右表无匹配	整行不出现	字段为 NULL
典型场景	必须有关系的数据	主表全量 + 附加信息

常见应用：查"没有"的数据

-- 找出没有订单的用户（右表为 NULL 即无匹配）
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 找出没有教程的分类
SELECT c.name
FROM categories c
LEFT JOIN tutorials t ON t.cat_id = c.id
WHERE t.id IS NULL;

带条件的 LEFT JOIN

-- 注意：条件放 WHERE 会过滤掉 NULL 行（等效 INNER）
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid';
-- 放 ON 里：保留所有用户，未付订单显示 NULL

SQL RIGHT JOIN

RIGHT JOIN：返回右表全部行，左表无匹配时为 NULL（LEFT JOIN 的镜像）。

基本语法

SELECT 列 FROM 表A
RIGHT JOIN 表B ON 关联条件;

示例

-- 所有分类都返回，无教程的显示 NULL
SELECT t.title, c.name
FROM tutorials t
RIGHT JOIN categories c ON t.cat_id = c.id;

LEFT vs RIGHT

-- 两者等价（交换表位置）
SELECT * FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

SELECT * FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;

使用建议

业界习惯统一用 LEFT JOIN（把"主表"放左边），RIGHT JOIN 较少用——保持代码风格一致，可读性更好。

查"右表无匹配"

-- 没有教程的分类（RIGHT JOIN 写法）
SELECT c.name
FROM tutorials t
RIGHT JOIN categories c ON t.cat_id = c.id
WHERE t.id IS NULL;

-- 等价 LEFT JOIN 写法（推荐）
SELECT c.name
FROM categories c
LEFT JOIN tutorials t ON t.cat_id = c.id
WHERE t.id IS NULL;

SQL FULL JOIN

FULL JOIN（全外连接）：返回两表全部行，任一侧无匹配时补 NULL。

支持情况

数据库	FULL JOIN 支持
PostgreSQL	✅ 原生支持
SQL Server	✅ 原生支持
Oracle	✅ 原生支持
MySQL	❌ 不支持（用 UNION 模拟）

MySQL 模拟 FULL JOIN

-- LEFT JOIN + RIGHT JOIN 用 UNION 合并
SELECT t.title, c.name
FROM tutorials t
LEFT JOIN categories c ON t.cat_id = c.id
UNION
SELECT t.title, c.name
FROM tutorials t
RIGHT JOIN categories c ON t.cat_id = c.id;

FULL JOIN 的语义

行的情况	结果
两表都匹配	合并显示
只在左表	右表字段为 NULL
只在右表	左表字段为 NULL

查"任一侧无匹配"

-- PostgreSQL：既没分类的教程 + 没教程的分类
SELECT t.title, c.name
FROM tutorials t
FULL JOIN categories c ON t.cat_id = c.id
WHERE t.id IS NULL OR c.id IS NULL;

SQL UNION

UNION：合并多个 SELECT 的结果集。

基本语法

SELECT 列 FROM 表A
UNION
SELECT 列 FROM 表B;

UNION vs UNION ALL

-- UNION：去重
SELECT city FROM users_a
UNION
SELECT city FROM users_b;

-- UNION ALL：不去重（更快）
SELECT city FROM users_a
UNION ALL
SELECT city FROM users_b;

对比项	UNION	UNION ALL
去重	✅	❌
性能	慢（需去重）	快
适用	合并后要唯一	合并后保留全部

使用规则

• 各 SELECT 的列数必须相同
• 对应列类型需兼容
• ORDER BY 放最后
• 列名取第一个 SELECT

示例

-- 合并两个表的数据
SELECT name, email FROM vip_users
UNION ALL
SELECT name, email FROM normal_users;

-- 带排序
SELECT title FROM tutorials WHERE cat_id = 1
UNION
SELECT title FROM tutorials WHERE cat_id = 2
ORDER BY title;

UNION 模拟 FULL JOIN（MySQL）

SELECT t.title, c.name FROM tutorials t
LEFT JOIN categories c ON t.cat_id = c.id
UNION
SELECT t.title, c.name FROM tutorials t
RIGHT JOIN categories c ON t.cat_id = c.id;

SQL SELECT INTO

SELECT INTO：把查询结果复制到新表。

基本语法

-- 复制表结构 + 数据到新表
SELECT * INTO new_table FROM old_table;

-- 只复制部分列
SELECT id, name INTO users_copy FROM users;

-- 带条件复制
SELECT * INTO active_users FROM users WHERE status = 'active';

MySQL 的替代方式

MySQL 不支持 SELECT INTO（用作变量赋值），改用 CREATE TABLE ... AS SELECT（CTAS）：

-- 创建表并复制数据（MySQL/PostgreSQL）
CREATE TABLE users_copy AS SELECT * FROM users;

-- 只复制结构（不复制数据）
CREATE TABLE users_copy LIKE users;

-- 带条件
CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

应用场景

• 备份数据（导出快照）
• 创建临时分析表
• 数据清洗结果落库
• 测试环境造数据

SQL INSERT INTO SELECT

INSERT INTO SELECT：从一张表查询并插入到另一张表。

基本语法

INSERT INTO 目标表 (列1, 列2)
SELECT 列1, 列2 FROM 来源表 WHERE 条件;

示例

-- 全量复制（列一致）
INSERT INTO users_backup (id, name, email)
SELECT id, name, email FROM users;

-- 带条件
INSERT INTO vip_orders (user_id, amount)
SELECT user_id, amount FROM orders
WHERE amount > 1000;

-- 合并多个来源
INSERT INTO all_cities (city)
SELECT city FROM users_a
UNION
SELECT city FROM users_b;

INSERT INTO SELECT vs SELECT INTO

对比项	INSERT INTO SELECT	SELECT INTO
目标表	必须已存在	自动创建
MySQL 支持	✅	❌（用 CTAS）
追加数据	✅ 可追加	创建新表

注意事项

• 目标表必须存在且列数/类型匹配
• 主键冲突会报错
• 大批量插入前先评估数据量

SQL CREATE DATABASE

CREATE DATABASE：创建数据库。

基本语法

CREATE DATABASE 数据库名;

MySQL 创建数据库

-- 创建并指定字符集（推荐 UTF-8）
CREATE DATABASE mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- 不存在才创建
CREATE DATABASE IF NOT EXISTS mydb;

-- 删除数据库（危险！）
DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;

查看与切换

-- 查看所有数据库
SHOW DATABASES;

-- 切换当前数据库
USE mydb;

-- 查看当前数据库
SELECT DATABASE();

数据库命名规范

• 小写字母、数字、下划线
• 语义化：knowledge_site、blog_db
• 避免保留字（order、user 等）

本站数据库

-- CodeNest 使用的数据库
CREATE DATABASE knowledge_site
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

SQL CREATE TABLE

CREATE TABLE：创建数据库表。

基本语法

CREATE TABLE 表名 (
  列名 数据类型 [约束],
  ...
);

创建用户表

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  age INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

常用数据类型

类型	用途
INT	整数
BIGINT	大整数
VARCHAR(n)	变长字符串（n 最大长度）
TEXT	长文本
DECIMAL(p,s)	精确小数（金额）
DATE / DATETIME / TIMESTAMP	日期时间
BOOLEAN	布尔（MySQL 映射 TINYINT(1)）

查看表结构

DESC users;          -- 查看结构
SHOW CREATE TABLE users;  -- 查看建表语句
SHOW TABLES;         -- 列出所有表

带外键的表

CREATE TABLE tutorials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cat_id INT NOT NULL,
  title VARCHAR(100) NOT NULL,
  FOREIGN KEY (cat_id) REFERENCES categories(id) ON DELETE CASCADE
);

SQL 约束

SQL 约束（Constraints）：定义表中数据的规则，保证数据完整性。

约束类型

约束	作用
NOT NULL	列不能为空
UNIQUE	列值唯一
PRIMARY KEY	主键（唯一 + 非空）
FOREIGN KEY	外键（引用其他表）
CHECK	条件检查
DEFAULT	默认值

列级约束示例

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT CHECK (age >= 0 AND age <= 150),
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

表级约束（多列）

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  enrolled_at DATE,
  PRIMARY KEY (student_id, course_id),        -- 联合主键
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

给约束命名

CREATE TABLE products (
  id INT,
  price DECIMAL(10,2),
  CONSTRAINT pk_products PRIMARY KEY (id),
  CONSTRAINT chk_price CHECK (price >= 0)
);

约束的作用

• 防止无效数据入库（完整性）
• 保证引用关系正确（外键）
• 错误尽早暴露（数据库层兜底）

SQL NOT NULL

NOT NULL 约束：确保列的值不能为空。

基本用法

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,   -- 必填
  email VARCHAR(100),              -- 可空
  nickname VARCHAR(50)             -- 可空
);

插入测试

-- ✅ 正常插入
INSERT INTO users (id, username) VALUES (1, 'zhangsan');

-- ❌ 报错：username 不能为 NULL
INSERT INTO users (id) VALUES (2);

-- ❌ 报错：username 不能省略（无默认值时）
INSERT INTO users (id, email) VALUES (3, 'a@b.com');

修改列约束

-- 添加 NOT NULL（需先确保现有数据不为 NULL）
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL;

-- 移除 NOT NULL
ALTER TABLE users MODIFY email VARCHAR(100) NULL;

何时用 NOT NULL

• 业务上必有的字段：用户名、手机号、标题
• 主键（隐含 NOT NULL）
• 默认值列（如 created_at）

SQL UNIQUE

UNIQUE 约束：保证列的值唯一，防止重复数据。

基本用法

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,      -- 用户名唯一
  email VARCHAR(100) UNIQUE         -- 邮箱唯一
);

UNIQUE vs PRIMARY KEY

对比项	UNIQUE	PRIMARY KEY
唯一	✅	✅
非空	可空（多个 NULL 允许）	必须非空
每表数量	可多个	只能一个

多列唯一

CREATE TABLE favorites (
  user_id INT,
  article_id INT,
  UNIQUE (user_id, article_id)   -- 组合唯一：同一用户不能重复收藏同一文章
);

添加/删除 UNIQUE

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);

-- 删除唯一约束
ALTER TABLE users DROP INDEX email;

注意 NULL

UNIQUE 列可以有多行 NULL（MySQL 中 NULL 不参与唯一比较）。想强制唯一且非空，配合 NOT NULL。

SQL PRIMARY KEY

PRIMARY KEY（主键）：唯一标识表中每一行。

基本用法

-- 列级定义
CREATE TABLE users (
  id INT PRIMARY KEY,
  ...
);

-- 表级定义（可命名）
CREATE TABLE users (
  id INT,
  ...,
  CONSTRAINT pk_users PRIMARY KEY (id)
);

主键的特性

• 唯一：不能重复
• 非空：不能为 NULL
• 每表一个
• 自动创建索引（查询快）

自增主键（最常用）

-- MySQL
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50)
);

-- PostgreSQL
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50)
);

联合主键

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)   -- 组合唯一标识
);

添加/删除主键

-- 添加
ALTER TABLE users ADD PRIMARY KEY (id);

-- 删除
ALTER TABLE users DROP PRIMARY KEY;

主键的选择建议

• 优先自增整数或 UUID（业务无关）
• 避免用业务字段（手机号、身份证）作主键
• 尽量单列主键，简单高效

SQL FOREIGN KEY

FOREIGN KEY（外键）：建立表之间的关联，保证引用完整性。

基本用法

CREATE TABLE tutorials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cat_id INT NOT NULL,
  title VARCHAR(100),
  FOREIGN KEY (cat_id) REFERENCES categories(id)
);

外键的作用

• 保证引用的记录存在（不能插入不存在的分类）
• 约束删除/更新行为（on delete 规则）
• 明确表间关系（ER 图自动识别）

删除/更新行为

CREATE TABLE tutorials (
  id INT PRIMARY KEY,
  cat_id INT,
  FOREIGN KEY (cat_id) REFERENCES categories(id)
    ON DELETE CASCADE      -- 分类删除时，级联删除教程
    ON UPDATE CASCADE      -- 分类 id 更新时，同步更新
);

-- 其他选项：
-- ON DELETE RESTRICT  有引用时禁止删除（默认）
-- ON DELETE SET NULL  删除时置空（cat_id 需可空）
-- ON DELETE NO ACTION 禁止操作

添加外键

ALTER TABLE tutorials
ADD CONSTRAINT fk_tutorials_cat
FOREIGN KEY (cat_id) REFERENCES categories(id);

删除外键

ALTER TABLE tutorials DROP FOREIGN KEY fk_tutorials_cat;

外键 vs 无外键

对比项	有外键	无外键（应用层管理）
数据完整性	数据库保证	应用保证
性能	写入略慢	更快
灵活性	受约束	自由
适用	强关联业务	高并发/分库分表

SQL CHECK

CHECK 约束：限定列的值满足指定条件。

基本用法

CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2) CHECK (price >= 0),        -- 价格非负
  quantity INT CHECK (quantity >= 0),            -- 数量非负
  status VARCHAR(20) CHECK (status IN ('active', 'inactive'))
);

多条件 CHECK

CREATE TABLE employees (
  id INT PRIMARY KEY,
  age INT,
  salary DECIMAL(10,2),
  CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65),
  CONSTRAINT chk_salary CHECK (salary > 0 AND salary < 1000000)
);

MySQL 版本注意

-- MySQL 8.0.16+ 才真正执行 CHECK
-- 旧版本 CHECK 会被忽略（仅语法兼容）！
-- 业务校验还是要靠应用层或触发器

添加/删除 CHECK

-- 添加（MySQL 8.0.16+）
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price >= 0);

-- 删除
ALTER TABLE products DROP CHECK chk_price;

CHECK 的用途

• 数值范围（年龄、价格、数量）
• 枚举取值（状态、类型）
• 跨列校验（结束时间 > 开始时间）

CREATE TABLE events (
  id INT PRIMARY KEY,
  start_at DATETIME,
  end_at DATETIME,
  CONSTRAINT chk_time CHECK (end_at > start_at)
);

SQL DEFAULT

DEFAULT 约束：为列提供默认值。

基本用法

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  status VARCHAR(20) DEFAULT 'active',        -- 默认值
  score INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 默认当前时间
  is_admin BOOLEAN DEFAULT FALSE
);

插入时省略默认列

INSERT INTO users (name) VALUES ('张三');
-- status='active'、score=0、created_at=当前时间 自动填充

-- 显式使用默认值
INSERT INTO users (name, status) VALUES ('李四', DEFAULT);

修改默认值

-- 添加/修改默认值
ALTER TABLE users ALTER status SET DEFAULT 'pending';

-- 移除默认值
ALTER TABLE users ALTER status DROP DEFAULT;

DEFAULT vs NULL

对比项	DEFAULT 值	NULL
插入时省略	填入默认值	填入 NULL
语义	有明确含义	未知/未填
配合 NOT NULL	✅ 常用	❌ 冲突

常见默认值

-- 时间戳
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- 数字
views INT DEFAULT 0
sort_order INT DEFAULT 0

-- 文本
status VARCHAR(20) DEFAULT 'active'

SQL CREATE INDEX

CREATE INDEX：创建索引加速查询。

什么是索引

索引类似书的目录：让数据库不必全表扫描，直接定位数据。代价是占用存储、写入略慢。

创建索引

-- 单列索引
CREATE INDEX idx_users_email ON users(email);

-- 唯一索引（同时保证唯一）
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 多列索引（组合索引）
CREATE INDEX idx_tutorials_cat_sort ON tutorials(cat_id, sort_order);

-- 表定义时创建
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100),
  INDEX idx_email (email)
);

查看与删除索引

-- 查看表的索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_users_email ON users;

-- 删除主键索引
ALTER TABLE users DROP PRIMARY KEY;

何时建索引

场景	建议
WHERE 频繁过滤的列	✅ 建索引
JOIN 的关联列	✅ 建索引
ORDER BY 的列	✅ 建索引
区分度低的列（性别）	❌ 意义不大
频繁更新的小表	❌ 索引开销大

索引失效场景

-- 函数包裹列 → 索引失效
SELECT * FROM users WHERE DATE(created_at) = '2026-01-01';

-- 前缀模糊 → 索引失效
SELECT * FROM users WHERE email LIKE '%@example.com';

-- 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone 是 VARCHAR

SQL DROP

DROP：删除数据库对象（表、数据库、索引等）。

DROP 的用途

语句	作用
DROP TABLE 表名	删除表（含数据）
DROP DATABASE 库名	删除数据库
DROP INDEX 索引名 ON 表名	删除索引
DROP VIEW 视图名	删除视图

示例

-- 删除表
DROP TABLE users;
DROP TABLE IF EXISTS users;   -- 不存在不报错

-- 删除数据库
DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;

-- 删除索引（MySQL）
DROP INDEX idx_email ON users;

-- 删除视图
DROP VIEW active_users;

DROP vs DELETE vs TRUNCATE

对比项	DROP	TRUNCATE	DELETE
删除对象	整个表（结构+数据）	全部数据（保留结构）	数据（可加 WHERE）
可回滚	❌	❌（多数）	✅（事务内）
可加 WHERE	❌	❌	✅
速度	快	快	慢

⚠️ 安全提示

-- 危险操作！执行前务必确认：
-- 1. 已备份（mysqldump）
-- 2. 确认表名正确
-- 3. 生产环境谨慎操作

-- 备份示例
mysqldump -u root -p mydb users > users_backup.sql;

SQL ALTER

ALTER：修改表结构（增删列、改类型、加约束）。

ALTER 的常见操作

操作	语法
添加列	ALTER TABLE t ADD COLUMN 列名 类型
删除列	ALTER TABLE t DROP COLUMN 列名
修改列	ALTER TABLE t MODIFY 列名 新类型
重命名列	ALTER TABLE t RENAME COLUMN 旧名 TO 新名
重命名表	RENAME TABLE 旧名 TO 新名

示例

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN age INT DEFAULT 0 AFTER email;

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 修改列类型
ALTER TABLE users MODIFY email VARCHAR(200) NOT NULL;

-- 修改默认值
ALTER TABLE users ALTER status SET DEFAULT 'pending';

-- 添加索引
ALTER TABLE users ADD INDEX idx_email (email);

-- 添加外键
ALTER TABLE tutorials
ADD CONSTRAINT fk_cat FOREIGN KEY (cat_id) REFERENCES categories(id);

重命名表（MySQL）

RENAME TABLE old_users TO new_users;

-- 或
ALTER TABLE old_users RENAME TO new_users;

⚠️ 注意事项

• 大表 ALTER 会锁表，选低峰期执行
• 改列类型可能导致数据丢失（类型不兼容）
• 删除列前确认没有依赖（索引、视图、代码）

SQL Auto Increment

Auto Increment（自增）：自动生成唯一递增的主键值。

MySQL 自增

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

-- 插入时省略 id，自动生成 1,2,3...
INSERT INTO users (name) VALUES ('张三');
INSERT INTO users (name) VALUES ('李四');
-- id 自动为 1, 2

设置起始值与步长

-- 从 1000 开始
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY
) AUTO_INCREMENT = 1000;

-- 修改当前自增值
ALTER TABLE users AUTO_INCREMENT = 5000;

获取刚插入的 ID

-- 在同一个连接中
SELECT LAST_INSERT_ID();   -- 返回最后插入的自增 id

-- Python pymysql 示例
cursor.lastrowid   # 直接拿 id

PostgreSQL 自增（SERIAL）

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50)
);

自增的注意事项

• 删除最大 id 后，自增不会回退（MySQL）
• 事务回滚后自增值仍会消耗
• 自增只能用于整数类型
• 分布式场景用 UUID/雪花ID 替代

SQL 视图

SQL 视图（View）：虚拟表，把复杂查询封装成可复用的"表"。

什么是视图

视图是存储的查询：它不存数据，查询时动态执行底层 SELECT。可以把复杂 JOIN/过滤封装起来，当表一样使用。

创建视图

CREATE VIEW active_tutorials AS
SELECT t.id, t.title, c.name AS category
FROM tutorials t
JOIN categories c ON t.cat_id = c.id
WHERE t.is_published = 1;

使用视图

-- 像表一样查询
SELECT * FROM active_tutorials;
SELECT * FROM active_tutorials WHERE category = '前端开发';

-- 查看视图定义
SHOW CREATE VIEW active_tutorials;

修改与删除视图

-- 修改视图
CREATE OR REPLACE VIEW active_tutorials AS
SELECT ...;

-- 删除视图
DROP VIEW active_tutorials;

视图的优点

优点	说明
简化查询	复杂 JOIN 封装后简单调用
安全	只暴露需要的列，隐藏敏感字段
一致性	底层表变化，视图定义不变
逻辑封装	业务口径统一（如"有效订单"）

可更新视图

-- 简单视图可增删改（映射到基表）
INSERT INTO active_tutorials (title, category) VALUES (...);

-- 含 GROUP BY/JOIN 的视图通常不可更新

SQL 日期

SQL 日期：日期时间的存储、比较与常用函数。

日期类型

类型	格式	说明
DATE	2026-08-11	仅日期
TIME	14:30:00	仅时间
DATETIME	2026-08-11 14:30:00	日期+时间
TIMESTAMP	2026-08-11 14:30:00	带时区，范围小

日期函数（MySQL）

-- 当前时间
SELECT NOW();            -- 2026-08-11 14:30:00
SELECT CURDATE();        -- 2026-08-11
SELECT CURTIME();        -- 14:30:00

-- 提取部分
SELECT YEAR(NOW());      -- 2026
SELECT MONTH(NOW());     -- 8
SELECT DAY(NOW());       -- 11
SELECT HOUR(NOW());      -- 14

-- 格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');       -- 2026-08-11
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');     -- 2026年08月11日

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);      -- 7天后
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);    -- 1个月前
SELECT DATEDIFF('2026-08-11', '2026-08-01'); -- 10（天数差）

日期比较

-- 今天创建的订单
SELECT * FROM orders WHERE DATE(created_at) = CURDATE();

-- 近 30 天
SELECT * FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 本月
SELECT * FROM orders WHERE YEAR(created_at) = YEAR(NOW())
  AND MONTH(created_at) = MONTH(NOW());

⚠️ 日期陷阱

• DATE(列) 会导致索引失效（全表扫描）
• 范围查询用 列 >= 起始 AND 列 < 结束+1天
• 存日期优先 DATETIME/TIMESTAMP，不要用字符串

SQL NULL 值

SQL NULL 值：处理缺失数据的特殊值。

什么是 NULL

NULL 表示"未知/无值"，不是 0、空字符串或 false。它是 SQL 中特殊的存在。

NULL 的判断

-- 用 IS NULL / IS NOT NULL（不能用 =）
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- ❌ 错误写法（永远查不到）
SELECT * FROM users WHERE email = NULL;

NULL 的运算陷阱

-- NULL 参与运算结果为 NULL
SELECT NULL + 1;        -- NULL
SELECT NULL = NULL;     -- NULL（不是 true！）
SELECT NULL > 5;        -- NULL

-- 聚合函数忽略 NULL
SELECT AVG(score) FROM students;   -- 忽略 NULL 的分数
SELECT COUNT(score) FROM students; -- 只计非 NULL

-- 但 COUNT(*) 计所有行
SELECT COUNT(*) FROM students;     -- 所有行数

处理 NULL 的函数

-- MySQL：IFNULL / COALESCE
SELECT name, IFNULL(email, '未填写') FROM users;
SELECT name, COALESCE(email, phone, '无联系方式') FROM users;

-- PostgreSQL：COALESCE / NULLIF
SELECT COALESCE(email, '未填写') FROM users;

NULL 与 UNIQUE

UNIQUE 列允许多个 NULL（NULL 不参与唯一比较）。

NULL 与排序

-- MySQL 升序时 NULL 在最前
SELECT * FROM users ORDER BY email ASC;

-- 让 NULL 排最后
SELECT * FROM users ORDER BY email IS NULL, email ASC;

SQL NULL 函数

SQL NULL 函数：处理 NULL 值的专用函数。

IFNULL（MySQL）

-- IFNULL(expr1, expr2)：expr1 为 NULL 时返回 expr2
SELECT name, IFNULL(email, '未填写') AS email
FROM users;

-- 嵌套
SELECT IFNULL(IFNULL(phone, email), '无联系方式') FROM users;

COALESCE（标准 SQL）

-- 返回第一个非 NULL 的值
SELECT COALESCE(email, phone, '无联系方式') FROM users;

-- 常用于默认值
SELECT COALESCE(score, 0) FROM students;

NULLIF（标准 SQL）

-- NULLIF(a, b)：a = b 时返回 NULL，否则返回 a
SELECT NULLIF(score, 0) FROM students;   -- 0 变为 NULL

-- 防止除零
SELECT price / NULLIF(quantity, 0) FROM orders;

ISNULL（SQL Server）

-- SQL Server 用 ISNULL
SELECT ISNULL(email, '未填写') FROM users;

NVL（Oracle）

-- Oracle 用 NVL
SELECT NVL(email, '未填写') FROM users;

函数对比表

函数	数据库	行为
IFNULL(a,b)	MySQL	a 为 NULL 返回 b
COALESCE(a,b,c)	标准	返回第一个非 NULL
NULLIF(a,b)	标准	a=b 返回 NULL
ISNULL(a,b)	SQL Server	同 IFNULL
NVL(a,b)	Oracle	同 IFNULL

SQL 通用数据类型

SQL 通用数据类型：跨数据库通用的数据类型概念。

六大类数据类型

类别	说明	示例
字符串	文本数据	CHAR、VARCHAR、TEXT
数值	整数和小数	INT、BIGINT、DECIMAL、FLOAT
日期时间	时间数据	DATE、DATETIME、TIMESTAMP
布尔	真/假	BOOLEAN
二进制	图片、文件	BLOB、BINARY
其他	枚举、JSON 等	ENUM、JSON

字符串类型选择

类型	特点	适用
CHAR(n)	定长，快	固定长度（手机号、国家码）
VARCHAR(n)	变长，省空间	姓名、标题（常用）
TEXT	长文本	正文、描述

数值类型选择

类型	范围/精度	适用
TINYINT	-128~127	状态、年龄
INT	±21亿	ID、计数（常用）
BIGINT	极大	大表主键、时间戳
DECIMAL(p,s)	精确小数	金额（重要！）
FLOAT/DOUBLE	近似	科学计算（勿用于金额）

类型选择原则

• 够用就好：能 TINYINT 不用 INT
• 金额用 DECIMAL，不用 FLOAT（精度问题）
• 文本用 VARCHAR(n) 限制长度
• 日期用 DATE/DATETIME，别存字符串

SQL DB 数据类型

SQL DB 数据类型：MySQL 常用数据类型的细节。

整数类型

类型	字节	范围（有符号）
TINYINT	1	-128 ~ 127
SMALLINT	2	-32768 ~ 32767
MEDIUMINT	3	±838万
INT	4	±21亿
BIGINT	8	极大

小数类型

-- DECIMAL(p,s)：p 总位数，s 小数位
price DECIMAL(10,2)   -- 最大 99999999.99

-- FLOAT/DOUBLE：近似值（勿用于金额）
score FLOAT
distance DOUBLE

字符串类型

类型	最大长度	说明
CHAR(n)	255	定长
VARCHAR(n)	65535 字节	变长
TEXT	64KB	长文本
MEDIUMTEXT	16MB	中长文本
LONGTEXT	4GB	超长文本

日期时间类型

类型	范围	用途
DATE	1000-01-01 ~ 9999-12-31	仅日期
DATETIME	同上 + 时间	日期时间（推荐）
TIMESTAMP	1970 ~ 2038	带时区（有范围限制）
YEAR	1901-2155	年份

其他类型

BOOLEAN        -- 等价 TINYINT(1)
ENUM('a','b')  -- 枚举
JSON           -- JSON 文档（MySQL 5.7+）
BLOB           -- 二进制大对象（图片/文件）

SQL 函数

SQL 函数：内置函数总览——聚合、字符串、日期、数学等。

函数分类

类别	代表函数
聚合	COUNT、SUM、AVG、MAX、MIN
字符串	CONCAT、LENGTH、UPPER、LOWER、SUBSTRING
日期	NOW、DATE_FORMAT、DATE_ADD、DATEDIFF
数学	ROUND、CEIL、FLOOR、ABS、MOD
条件	IF、CASE WHEN、IFNULL、COALESCE

聚合函数

SELECT
  COUNT(*) AS total,
  COUNT(DISTINCT cat_id) AS cats,
  SUM(views) AS total_views,
  AVG(views) AS avg_views,
  MAX(views) AS max_views,
  MIN(views) AS min_views
FROM tutorials;

字符串函数

SELECT
  CONCAT(title, ' - ', read_time) AS info,
  UPPER(title) AS upper_title,
  LENGTH(title) AS len,
  SUBSTRING(title, 1, 5) AS first5,
  REPLACE(title, '教程', 'Tutorial') AS replaced
FROM tutorials
LIMIT 3;

条件函数

-- IF
SELECT title, IF(views > 100, '热门', '普通') AS popularity FROM tutorials;

-- CASE WHEN（多分支）
SELECT title,
  CASE
    WHEN views > 1000 THEN '爆款'
    WHEN views > 100 THEN '热门'
    ELSE '普通'
  END AS level
FROM tutorials;

函数使用注意

• 聚合函数常配 GROUP BY
• 对列用函数会阻止索引（如 DATE(col)）
• 不同数据库函数名有差异（LENGTH vs LEN）

SQL AVG()

AVG()：计算平均值。

基本用法

-- 所有教程的平均浏览量
SELECT AVG(views) AS avg_views FROM tutorials;

-- 带条件
SELECT AVG(views) FROM tutorials WHERE cat_id = 1;

与 GROUP BY 配合

-- 每个分类的平均浏览量
SELECT cat_id, AVG(views) AS avg_views
FROM tutorials
GROUP BY cat_id;

AVG 与 NULL

-- AVG 忽略 NULL（只对非 NULL 求平均）
SELECT AVG(score) FROM students;

-- 如果全为 NULL，返回 NULL
SELECT AVG(score) FROM students WHERE id = -1;   -- NULL

-- 处理：COALESCE 给默认值
SELECT COALESCE(AVG(score), 0) FROM students;

AVG 与 DISTINCT

-- 去重后平均
SELECT AVG(DISTINCT score) FROM students;

保留两位小数

SELECT ROUND(AVG(views), 2) AS avg_views FROM tutorials;

别名使用

SELECT ROUND(AVG(views), 1) AS "平均浏览量" FROM tutorials;

SQL COUNT()

COUNT()：统计行数。

COUNT 的三种用法

-- COUNT(*)：统计所有行（含 NULL）
SELECT COUNT(*) FROM users;

-- COUNT(列)：统计非 NULL 值数量
SELECT COUNT(email) FROM users;   -- 有邮箱的用户数

-- COUNT(DISTINCT 列)：统计不重复值数量
SELECT COUNT(DISTINCT city) FROM users;

常用场景

-- 总数
SELECT COUNT(*) AS total_users FROM users;

-- 条件计数
SELECT COUNT(*) FROM orders WHERE status = 'paid';

-- 多条件
SELECT
  COUNT(*) AS total,
  COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid,
  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending
FROM orders;

-- 简化（MySQL）
SELECT
  COUNT(*) AS total,
  SUM(status = 'paid') AS paid,
  SUM(status = 'pending') AS pending
FROM orders;

COUNT 与 GROUP BY

-- 每个分类的教程数
SELECT cat_id, COUNT(*) AS cnt
FROM tutorials
GROUP BY cat_id
ORDER BY cnt DESC;

COUNT(*) vs COUNT(1)

两者性能几乎无差别（MySQL 都优化为行数统计）。习惯用 COUNT(*) 即可。

判断记录是否存在

-- 高效方式：LIMIT 1
SELECT EXISTS(SELECT 1 FROM users WHERE id = 100);
-- 返回 1/0，比 COUNT(*) 快

SQL FIRST()

FIRST()：获取第一条记录（各数据库实现不同）。

标准 SQL 无 FIRST()

FIRST() 不是标准 SQL 函数，各数据库用不同方式实现"取第一条"。

MySQL：LIMIT 1

-- 第一条（按插入顺序/主键）
SELECT * FROM users ORDER BY id ASC LIMIT 1;

-- 最新一条
SELECT * FROM tutorials ORDER BY updated DESC LIMIT 1;

-- 第一条的某列
SELECT title FROM tutorials ORDER BY id ASC LIMIT 1;

SQL Server：TOP / FIRST

SELECT TOP 1 * FROM users ORDER BY id;
-- 或 FETCH FIRST
SELECT * FROM users ORDER BY id OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

PostgreSQL

SELECT * FROM users ORDER BY id LIMIT 1;

用子查询实现

-- 最老用户的注册时间
SELECT MIN(created_at) FROM users;

-- 按字母序第一个名字
SELECT MIN(name) FROM users;

注意：FIRST() 的误区

• "第一条"必须配合 ORDER BY 才有意义
• 不排序时返回的行不确定（数据库自由选择）
• MIN/MAX 能表达的（字符串、日期）优先用聚合函数

SQL LAST()

LAST()：获取最后一条记录。

MySQL：LIMIT 1 + 倒序

-- 最后一条（按主键）
SELECT * FROM users ORDER BY id DESC LIMIT 1;

-- 最新创建
SELECT * FROM tutorials ORDER BY created_at DESC LIMIT 1;

SQL Server：LAST 相关

SELECT TOP 1 * FROM users ORDER BY id DESC;

PostgreSQL

SELECT * FROM users ORDER BY id DESC LIMIT 1;

用 MAX 实现

-- 最新时间
SELECT MAX(created_at) FROM users;

-- 获取对应整行（子查询）
SELECT * FROM users
WHERE created_at = (SELECT MAX(created_at) FROM users);

实战：取每组最新记录

-- 每个分类最新一篇教程（窗口函数）
SELECT t.* FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY cat_id ORDER BY updated DESC) AS rn
  FROM tutorials
) t WHERE t.rn = 1;

SQL MAX()

MAX()：求最大值。

基本用法

-- 最高浏览量
SELECT MAX(views) FROM tutorials;

-- 最大价格
SELECT MAX(price) FROM products;

-- 最新日期
SELECT MAX(created_at) FROM users;

-- 字母序最大（字符串）
SELECT MAX(name) FROM users;

带条件

-- 前端分类的最高浏览量
SELECT MAX(views) FROM tutorials WHERE cat_id = 1;

与 GROUP BY

-- 每个分类的最大浏览量
SELECT cat_id, MAX(views) AS max_views
FROM tutorials
GROUP BY cat_id;

获取最大值对应的整行

-- 方式一：子查询
SELECT * FROM tutorials
WHERE views = (SELECT MAX(views) FROM tutorials);

-- 方式二：ORDER BY + LIMIT（更高效）
SELECT * FROM tutorials ORDER BY views DESC LIMIT 1;

-- 方式三：窗口函数（可并列）
SELECT * FROM (
  SELECT *, RANK() OVER (ORDER BY views DESC) AS rk
  FROM tutorials
) t WHERE t.rk = 1;

MAX 与 NULL

-- 全 NULL 时返回 NULL
SELECT MAX(score) FROM students WHERE id = -1;   -- NULL

SQL MIN()

MIN()：求最小值。

基本用法

-- 最低浏览量
SELECT MIN(views) FROM tutorials;

-- 最低价格
SELECT MIN(price) FROM products;

-- 最早日期
SELECT MIN(created_at) FROM users;

-- 字母序最小
SELECT MIN(name) FROM users;

带条件与 GROUP BY

-- 前端分类最低价
SELECT MIN(price) FROM products WHERE cat_id = 1;

-- 每个分类最低价
SELECT cat_id, MIN(price) AS min_price
FROM products
GROUP BY cat_id;

MIN/MAX 与索引

-- 有索引时 MIN/MAX 走索引（极快）
-- 例：id 是主键
SELECT MIN(id), MAX(id) FROM users;   -- O(1) 级别

-- 验证
EXPLAIN SELECT MIN(id) FROM users;

MIN/MAX 组合

SELECT
  MIN(price) AS min_price,
  MAX(price) AS max_price,
  MAX(price) - MIN(price) AS range_price
FROM products;

MIN 与 NULL

-- 忽略 NULL
SELECT MIN(score) FROM students;   -- 非 NULL 中的最小

-- 全 NULL 返回 NULL
SELECT COALESCE(MIN(score), 0) FROM students;

SQL SUM()

SUM()：求和。

基本用法

-- 总浏览量
SELECT SUM(views) FROM tutorials;

-- 订单总金额
SELECT SUM(amount) FROM orders;

-- 带条件
SELECT SUM(amount) FROM orders WHERE status = 'paid';

与 GROUP BY

-- 每个用户的订单总额
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
ORDER BY total DESC;

-- 每个分类的浏览量合计
SELECT cat_id, SUM(views) AS total_views
FROM tutorials
GROUP BY cat_id;

SUM 与 NULL

-- 忽略 NULL
SELECT SUM(score) FROM students;

-- 全 NULL 返回 NULL（不是 0）
SELECT COALESCE(SUM(score), 0) FROM students;

条件求和

-- 已支付金额 vs 未支付金额
SELECT
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_total,
  SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_total
FROM orders;

-- MySQL 简写
SELECT
  SUM(status = 'paid' AND amount) AS paid_total
FROM orders;

金额精度

-- 金额求和用 DECIMAL 类型避免精度问题
SELECT SUM(amount) FROM orders;   -- DECIMAL 列，结果精确

-- 保留两位
SELECT ROUND(SUM(amount), 2) FROM orders;

SQL GROUP BY

GROUP BY：按列分组，配合聚合函数统计。

基本语法

SELECT 列, 聚合函数 FROM 表 GROUP BY 列;

按分类统计

-- 每个分类的教程数
SELECT cat_id, COUNT(*) AS cnt
FROM tutorials
GROUP BY cat_id;

-- 每个分类的总浏览量、平均浏览量
SELECT cat_id,
  COUNT(*) AS cnt,
  SUM(views) AS total_views,
  AVG(views) AS avg_views
FROM tutorials
GROUP BY cat_id;

多列分组

-- 按分类 + 状态统计
SELECT cat_id, status, COUNT(*) AS cnt
FROM tutorials
GROUP BY cat_id, status;

GROUP BY + JOIN

-- 分类名 + 教程数
SELECT c.name, COUNT(t.id) AS cnt
FROM categories c
LEFT JOIN tutorials t ON t.cat_id = c.id
GROUP BY c.id, c.name
ORDER BY cnt DESC;

GROUP BY 的规则

• SELECT 中的列必须出现在 GROUP BY 中或用于聚合函数
• MySQL 5.7+ 默认开启 ONLY_FULL_GROUP_BY（严格模式）
• GROUP BY 后可跟 HAVING 过滤分组

-- ❌ 错误：title 不在 GROUP BY 中
SELECT cat_id, title, COUNT(*) FROM tutorials GROUP BY cat_id;

-- ✅ 正确
SELECT cat_id, COUNT(*) FROM tutorials GROUP BY cat_id;

SQL HAVING

HAVING：对分组后的结果进行过滤（与 WHERE 区分）。

HAVING vs WHERE

对比项	WHERE	HAVING
过滤时机	分组前（行级）	分组后（组级）
可用聚合	❌ 不可用	✅ 可用
执行顺序	FROM → WHERE → GROUP BY	GROUP BY → HAVING → SELECT

基本用法

-- 教程数大于 5 的分类
SELECT cat_id, COUNT(*) AS cnt
FROM tutorials
GROUP BY cat_id
HAVING COUNT(*) > 5;

-- 平均浏览量大于 100 的分类
SELECT cat_id, AVG(views) AS avg_views
FROM tutorials
GROUP BY cat_id
HAVING AVG(views) > 100;

WHERE + GROUP BY + HAVING 组合

-- 先 WHERE 过滤行，再分组，再 HAVING 过滤组
SELECT cat_id, COUNT(*) AS cnt
FROM tutorials
WHERE is_published = 1          -- 行级过滤
GROUP BY cat_id
HAVING COUNT(*) >= 3;           -- 组级过滤

HAVING 使用别名

-- MySQL 支持 HAVING 用别名
SELECT cat_id, COUNT(*) AS cnt
FROM tutorials
GROUP BY cat_id
HAVING cnt > 5;   -- 别名（MySQL 支持）

-- 标准写法（兼容性好）
HAVING COUNT(*) > 5;

HAVING 实战

-- 消费超 1000 的用户
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

-- 收藏数超过 10 的文章
SELECT article_id, COUNT(*) AS favs
FROM favorites
GROUP BY article_id
HAVING COUNT(*) > 10;

SQL EXISTS

EXISTS：判断子查询是否有结果（存在性检查）。

基本语法

SELECT 列 FROM 表A
WHERE EXISTS (SELECT 1 FROM 表B WHERE 关联条件);

示例

-- 有订单的用户
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 没有订单的用户
SELECT name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

EXISTS vs IN

-- IN 写法
SELECT name FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- EXISTS 写法（通常更快，尤其大表）
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 关键区别：
-- IN 先执行子查询生成列表
-- EXISTS 逐行检查（可提前终止）
-- 子查询含 NULL 时：EXISTS 更安全，NOT IN 会出错

EXISTS 实战

-- 找出从未收藏过任何教程的用户
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM favorites f WHERE f.user_id = u.id);

-- 找出有高额订单的用户
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id AND o.amount > 1000);

EXISTS 优化

-- 子查询 SELECT 1 即可（不需要具体列）
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 关联列建索引大幅提速
CREATE INDEX idx_orders_user ON orders(user_id);

SQL UCASE()

UCASE()：把字符串转为大写（等价 UPPER）。

基本用法

-- 转大写
SELECT UCASE('hello world');   -- 'HELLO WORLD'

-- 列应用
SELECT UCASE(name) FROM users;

-- 与普通查询对比
SELECT name, UCASE(name) AS upper_name FROM users;

UPPER vs UCASE

两者等价（MySQL 中 UCASE() 是 UPPER() 的别名）。SQL Server 用 UPPER。

常见应用

-- 统一比较（不区分大小写）
SELECT * FROM users WHERE UCASE(email) = UCASE('User@Example.com');

-- 生成大写编码
SELECT UPPER(LEFT(code, 3)) FROM products;

-- 数据展示规范化
SELECT CONCAT(UPPER(LEFT(city, 1)), LOWER(SUBSTRING(city, 2))) FROM users;

注意索引

-- UCASE(列) 会阻止索引使用
SELECT * FROM users WHERE UCASE(email) = 'A@B.COM';
-- 优化：存储时统一大小写，直接比较
SELECT * FROM users WHERE email = 'a@b.com';   -- 用索引

SQL LCASE()

LCASE()：把字符串转为小写（等价 LOWER）。

基本用法

-- 转小写
SELECT LCASE('HELLO WORLD');   -- 'hello world'

-- 列应用
SELECT LCASE(email) FROM users;

常见应用

-- 统一小写查询
SELECT * FROM users WHERE LCASE(username) = 'admin';

-- 生成小写标签
SELECT LCASE(tag) FROM article_tags;

-- 组合：首字母大写
SELECT CONCAT(UCASE(LEFT(name, 1)), LCASE(SUBSTRING(name, 2))) FROM users;

UPPER/LOWER 的标准名

数据库	大写	小写
MySQL	UPPER / UCASE	LOWER / LCASE
PostgreSQL	UPPER	LOWER
SQL Server	UPPER	LOWER
SQLite	UPPER	LOWER

与排序规则

-- MySQL 默认排序规则（utf8mb4_general_ci 等）不区分大小写
-- 所以 LCASE/UCASE 常用在：
-- 1. 显示规范化
-- 2. 区分大小写的排序规则下做比较
-- 3. 生成代码/键值

SQL MID()

MID()：截取字符串片段（MySQL 方言，等价 SUBSTRING）。

基本语法

-- MID(字符串, 起始位置, 长度)
-- 注意：MySQL 位置从 1 开始
SELECT MID('Hello World', 1, 5);    -- 'Hello'
SELECT MID('Hello World', 7, 5);    -- 'World'

省略长度

-- 从位置 7 到结尾
SELECT MID('Hello World', 7);       -- 'World'

负位置（从尾部数）

-- 从倒数第 5 个字符开始
SELECT MID('Hello World', -5);      -- 'World'
SELECT MID('Hello World', -5, 2);   -- 'Wo'

列应用

-- 取手机号后 4 位
SELECT MID(phone, -4) AS tail FROM users;

-- 取日期字符串的月份部分
SELECT MID(created_at, 6, 2) AS month FROM orders;

-- 截取摘要
SELECT MID(content, 1, 50) AS excerpt FROM articles;

SUBSTRING 等价写法

-- SUBSTRING 是标准写法
SELECT SUBSTRING('Hello World', 1, 5);   -- 'Hello'
SELECT SUBSTR('Hello World', 7);         -- 'World'

-- 三个函数等价（MySQL）：MID / SUBSTRING / SUBSTR

SQL LEN()

LEN()：返回字符串长度。

基本用法

-- SQL Server：LEN（不含尾随空格）
SELECT LEN('Hello');        -- 5

-- MySQL：用 LENGTH / CHAR_LENGTH
SELECT LENGTH('Hello');     -- 5（字节数）
SELECT CHAR_LENGTH('你好'); -- 2（字符数）
SELECT LENGTH('你好');      -- 6（UTF-8 字节数！）

⚠️ 中文长度的坑

-- MySQL LENGTH 返回字节数（中文 3 字节）
SELECT LENGTH('中文');      -- 6
SELECT CHAR_LENGTH('中文'); -- 2（推荐用这个）

-- PostgreSQL
SELECT LENGTH('中文');      -- 2（直接字符数）
SELECT OCTET_LENGTH('中文'); -- 6（字节数）

常见应用

-- 标题太长的记录
SELECT * FROM tutorials WHERE CHAR_LENGTH(title) > 30;

-- 按名字长度排序
SELECT name, CHAR_LENGTH(name) AS len
FROM users
ORDER BY len DESC;

-- 检查必填（非空非空白）
SELECT * FROM users WHERE LENGTH(TRIM(nickname)) = 0;

各数据库函数对照

数据库	字符数	字节数
MySQL	CHAR_LENGTH	LENGTH
PostgreSQL	LENGTH	OCTET_LENGTH
SQL Server	LEN	DATALENGTH

SQL ROUND()

ROUND()：四舍五入。

基本用法

-- 四舍五入到整数
SELECT ROUND(3.14159);      -- 3
SELECT ROUND(3.56789);      -- 4

-- 保留指定位数
SELECT ROUND(3.14159, 2);   -- 3.14
SELECT ROUND(3.14159, 3);   -- 3.142

-- 负数位数（十位/百位）
SELECT ROUND(1234, -1);     -- 1230
SELECT ROUND(1234, -2);     -- 1200

列应用

-- 平均分保留两位
SELECT ROUND(AVG(score), 2) AS avg_score FROM students;

-- 金额显示
SELECT ROUND(price * 0.9, 2) AS discount FROM products;

ROUND vs 其他舍入

函数	行为	示例(3.7)
ROUND()	四舍五入	4
CEIL()	向上取整	4
FLOOR()	向下取整	3
TRUNCATE()	截断（不四舍五入）	3

SELECT
  ROUND(3.7) AS r,       -- 4
  CEIL(3.2) AS c,        -- 4
  FLOOR(3.8) AS f,       -- 3
  TRUNCATE(3.78, 1) AS t; -- 3.7

⚠️ 浮点精度注意

-- 金额不要用 FLOAT 存储（精度丢失）
-- 用 DECIMAL(10,2)，ROUND 才可靠
price DECIMAL(10,2) NOT NULL

SQL NOW()

NOW()：返回当前日期时间。

基本用法

-- 当前日期时间
SELECT NOW();      -- 2026-08-11 14:30:00

-- 只日期
SELECT CURDATE();  -- 2026-08-11

-- 只时间
SELECT CURTIME();  -- 14:30:00

插入时使用

-- 显式插入当前时间
INSERT INTO orders (created_at) VALUES (NOW());

-- 更常用：列默认值
CREATE TABLE orders (
  id INT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

NOW() vs SYSDATE()

-- MySQL：NOW() 是语句开始时间（稳定）
-- SYSDATE() 是函数执行时间（可能不同）

SELECT NOW(), SYSDATE() FROM t WHERE ...;  -- 大查询时可能不同

-- 推荐用 NOW()（一致性更好）

日期计算

-- 时间差
SELECT TIMESTAMPDIFF(DAY, '2026-08-01', NOW());   -- 天数
SELECT TIMESTAMPDIFF(HOUR, '2026-08-01 00:00:00', NOW());  -- 小时

-- 加减
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE);

-- 格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

比较日期

-- 今天创建的订单
SELECT * FROM orders WHERE DATE(created_at) = CURDATE();

-- 未过期（优惠券）
SELECT * FROM coupons WHERE expire_at > NOW();

SQL FORMAT()

FORMAT()：格式化数字（千分位）或日期。

数字千分位（MySQL）

-- FORMAT(数字, 小数位)
SELECT FORMAT(1234567.891, 2);   -- '1,234,567.89'

-- 整数千分位
SELECT FORMAT(1000000, 0);       -- '1,000,000'

日期格式化

-- MySQL 用 DATE_FORMAT
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');           -- 2026-08-11
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i');  -- 2026年08月11日 14:30

-- PostgreSQL 用 TO_CHAR
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');

-- SQL Server 用 FORMAT（也有数字格式）
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');

常用日期格式符（MySQL）

符号	含义	示例
%Y	四位年份	2026
%m	月份（补零）	08
%d	日（补零）	11
%H	24 小时	14
%i	分钟	30
%s	秒	00
%W	星期名	Tuesday

数字格式应用

-- 报表展示
SELECT CONCAT('¥', FORMAT(SUM(amount), 2)) AS total
FROM orders;

-- 百分比
SELECT CONCAT(FORMAT(rate * 100, 1), '%') FROM stats;

SQL 快速参考

SQL 快速参考：常用语句与函数速查表。

查询语句

-- 基础查询
SELECT col1, col2 FROM table WHERE cond ORDER BY col LIMIT 10;

-- 去重
SELECT DISTINCT col FROM table;

-- 聚合
SELECT cat, COUNT(*), SUM(x), AVG(x) FROM table GROUP BY cat HAVING COUNT(*) > 1;

-- 连接
SELECT a.*, b.name FROM a INNER JOIN b ON a.id = b.a_id;
SELECT a.* FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.id IS NULL;

操作语句

INSERT INTO t (c1, c2) VALUES (v1, v2);
INSERT INTO t (c1, c2) SELECT c1, c2 FROM t2;
UPDATE t SET c1 = v1 WHERE cond;
DELETE FROM t WHERE cond;
TRUNCATE TABLE t;   -- 清空（不删结构）

表结构

CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
ALTER TABLE t ADD COLUMN age INT DEFAULT 0;
ALTER TABLE t DROP COLUMN age;
DROP TABLE t;
CREATE INDEX idx ON t(col);
CREATE VIEW v AS SELECT ...;

常用条件

WHERE col = v OR col IN (1,2) OR col BETWEEN 1 AND 10
WHERE col LIKE '%xx%' OR col IS NULL OR col IS NOT NULL
WHERE col >= v AND col < v2

常用函数

COUNT / SUM / AVG / MAX / MIN
UPPER / LOWER / CONCAT / SUBSTRING / LENGTH
NOW / CURDATE / DATE_FORMAT / DATE_ADD / DATEDIFF
ROUND / CEIL / FLOOR / ABS / MOD
IF / CASE WHEN / IFNULL / COALESCE

事务控制

START TRANSACTION;   -- 或 BEGIN
... 操作 ...
COMMIT;              -- 提交
ROLLBACK;            -- 回滚

SQL 注入

SQL 注入：Web 安全头号风险之一，以及如何防御。

什么是 SQL 注入

攻击者把恶意 SQL 代码拼进用户输入，改变原 SQL 语义，从而窃取数据、篡改甚至删除数据。

注入示例

-- 危险的拼接写法
-- 用户输入：' OR '1'='1
SELECT * FROM users
WHERE username = '' OR '1'='1' AND password = '';

-- 结果：条件恒真，绕过登录！
-- 用户输入：'; DROP TABLE users; --
-- 可能删库（取决于驱动是否支持多语句）

注入的危害

• 绕过认证（登录/支付）
• 拖库（泄露用户数据、密码）
• 篡改/删除数据
• 执行系统命令（配合存储过程/文件函数）

防御：参数化查询（首选）

# ❌ 危险：字符串拼接
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

# ✅ 安全：参数化（占位符）
cursor.execute("SELECT * FROM users WHERE name = %s", (name,))

# SQLAlchemy 也自动参数化
session.query(User).filter(User.name == name).all()

防御：ORM 与白名单

# ORM 天然参数化（Django/SQLAlchemy）
User.objects.filter(username=username)

# 排序字段白名单（防 ORDER BY 注入）
allowed = {'name': 'name', 'id': 'id'}
order = allowed.get(user_input, 'id')

其他防御措施

措施	说明
最小权限	应用账号不授予 DROP/GRANT
输入校验	格式校验（邮箱、数字）
禁止多语句	驱动关闭 multi-statements
WAF/防火墙	拦截常见注入特征

SQL 测验

本章测验检验你对 SQL 的掌握。先独立作答，再看解析。

选择题

1. 查询"价格大于 100 的商品"的正确 WHERE 是？

• A. WHERE price > 100 ✅
• B. WHERE price >= 100
• C. WHERE price > '100'
• D. WHERE price >= '100'

2. 判断某列是否为 NULL 用？

• A. = NULL
• B. == NULL
• C. IS NULL ✅
• D. NULL()

3. 返回去重结果的查询是？

• A. SELECT DISTINCT city FROM users ✅
• B. SELECT city FROM users
• C. SELECT UNIQUE city FROM users
• D. SELECT TOP city FROM users

4. 聚合函数的过滤条件放在？

• A. WHERE
• B. HAVING ✅
• C. ORDER BY
• D. GROUP BY 内

5. 返回所有用户（含无订单的）用？

• A. INNER JOIN
• B. LEFT JOIN ✅
• C. CROSS JOIN
• D. 不需要 JOIN

6. 模糊匹配"以 J 开头"用？

• A. LIKE 'J%' ✅
• B. LIKE '%J'
• C. LIKE '_J'
• D. LIKE 'J_'

判断题

7. DELETE 不带 WHERE 会删除表中所有数据。

✅ 正确。不带条件会删除所有行（可回滚与 TRUNCATE 不同）。

8. GROUP BY 后面的 HAVING 可以用 WHERE 替代。

❌ 错误。WHERE 不能使用聚合函数（如 COUNT(*) > 5 只能放 HAVING）。

简答题

9. 写出统计每个分类教程数的 SQL。

SELECT cat_id, COUNT(*) AS cnt
FROM tutorials
GROUP BY cat_id;

10. 如何防止 SQL 注入？

# 参数化查询
cursor.execute("SELECT * FROM users WHERE name = %s", (name,))

计分

答对	水平
9-10	优秀，可处理日常 SQL 需求
6-8	良好，加强薄弱点
<6	建议重学基础章节

SQL 主机

SQL 主机（Hosting）：把数据库部署到服务器/云平台。

数据库部署方式

方式	适用	特点
云数据库（RDS）	生产环境	高可用、自动备份、托管
云服务器自装	中小项目	灵活、可控
面板一键安装	宝塔等	图形化管理、快
本地/内网	开发测试	零成本

宝塔面板安装 MySQL

1. 宝塔面板 → 软件商店 → 数据库
2. 安装 MySQL（选版本 5.7 或 8.0）
3. 数据库 → 添加数据库（设置账号密码）
4. 获取连接信息（host/port/user/password）

远程连接配置

-- 允许远程访问（需授权）
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

-- 安全建议：限定 IP
GRANT ALL ON mydb.* TO 'user'@'59.110.%' IDENTIFIED BY 'password';

防火墙与安全组

• 云服务器安全组放行 3306 端口（仅限可信 IP）
• 或通过 SSH 隧道访问（更安全）
• 生产不建议数据库端口暴露公网

备份与恢复

# 备份
mysqldump -u root -p mydb > mydb_backup.sql

# 恢复
mysql -u root -p mydb < mydb_backup.sql

# 宝塔面板：数据库 → 备份（图形化）

本站部署参考

CodeNest 的 MySQL（knowledge_site）就部署在同一台宝塔服务器上，应用通过 127.0.0.1:3306 连接，未暴露公网端口，安全且快。

SQL 总结

SQL 总结：回顾本教程核心知识点，规划进阶路线。

核心知识回顾

模块	要点
查询	SELECT / WHERE / ORDER BY / DISTINCT / LIMIT
过滤	LIKE / IN / BETWEEN / IS NULL / AND / OR
聚合	COUNT / SUM / AVG / MAX / MIN / GROUP BY / HAVING
连接	INNER / LEFT / RIGHT / FULL JOIN / UNION
操作	INSERT / UPDATE / DELETE / TRUNCATE
结构	CREATE / ALTER / DROP / 索引 / 视图 / 约束
安全	SQL 注入防御（参数化）

进阶学习路线

1. 事务与隔离级别：ACID、MVCC、锁
2. 查询优化：EXPLAIN、索引优化、慢查询
3. 存储引擎：InnoDB vs MyISAM
4. 窗口函数：ROW_NUMBER、RANK、LAG
5. NoSQL：MongoDB、Redis 对比
6. 数据库设计：范式、ER 图、分库分表

练习建议

• 用 MySQL 官方的 employees 示例库练习
• 把本站 knowledge_site 当练习场（categories/tutorials/sections）
• 写报表需求，练习 GROUP BY + JOIN + 窗口函数
• 用 EXPLAIN 分析慢查询，实践索引优化

一句话总结

SQL 的核心 = SELECT + WHERE + JOIN + GROUP BY，其余都是围绕这四个能力的扩展。掌握它们，任何数据库都触类旁通。