SQL语句

🧠 SQL 语句复习大全（详细版）

版本：完整版（含基础、进阶、最佳实践与示例）
说明：本文件覆盖常见关系型数据库（MySQL、PostgreSQL、SQLite、SQL Server、Oracle）中的 SQL 基础与进阶用法，并标注了不同数据库间常见差异。

SQL 简介与分类
数据定义语言（DDL）
数据操纵语言（DML）
查询基础与 WHERE 子句详解
连接（JOIN）详解与示例
聚合、GROUP BY 与 HAVING
子查询（Subquery）与公用表表达式（CTE）
集合运算（UNION/INTERSECT/EXCEPT）
窗口函数（Window Functions）
视图（VIEW）与物化视图（Materialized View）
约束（Constraints）详解
事务（Transactions）与隔离级别
索引（Indexes）详解与优化策略
性能优化与查询计划（EXPLAIN / ANALYZE）
分区（Partitioning）与分表策略
触发器（Triggers）与存储过程（Stored Procedures）
备份与导入导出（Export / Import）
安全与权限管理（Users / Grants）
常见函数（字符串、日期、数学、聚合）
正则、全文检索与模糊匹配
设计范式、反范式与建模建议
常见陷阱与调试技巧
示例：综合查询与实战题
附：不同数据库的差异速查表

1. SQL 简介与分类

SQL（Structured Query Language） 用于操作关系型数据库。主要分类：

DDL（Data Definition Language）：创建/修改结构（CREATE, ALTER, DROP）
DML（Data Manipulation Language）：数据操作（SELECT, INSERT, UPDATE, DELETE）
DCL（Data Control Language）：权限控制（GRANT, REVOKE）
TCL（Transaction Control Language）：事务控制（BEGIN/START TRANSACTION, COMMIT, ROLLBACK）

常见数据库：MySQL、PostgreSQL、SQLite、SQL Server、Oracle。

2. 数据定义语言（DDL）

创建数据库

CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- MySQL
CREATE DATABASE db_name; -- PostgreSQL/SQLite (注意权限与编码设置方式不同)

删除数据库

DROP DATABASE db_name;

使用数据库（切换）

USE db_name; -- MySQL, SQL Server
\c db_name; -- PostgreSQL (psql)

创建表（含常见字段类型）

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT, -- MySQL
  -- PostgreSQL 使用: SERIAL / BIGSERIAL 或 IDENTITY
  username VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  age INT,
  bio TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER 表常见操作

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT; -- PostgreSQL
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(150); -- MySQL
ALTER TABLE users RENAME COLUMN username TO user_name; -- PostgreSQL
ALTER TABLE users DROP COLUMN phone;

总结表格

功能	关键字	示例
添加字段	`ADD COLUMN`	`ALTER TABLE t ADD COLUMN c INT;`
修改字段类型	`ALTER COLUMN ... TYPE`（PostgreSQL） `MODIFY COLUMN`（MySQL）	`ALTER TABLE t ALTER COLUMN c TYPE SMALLINT;`
重命名字段	`RENAME COLUMN`（PostgreSQL） `CHANGE COLUMN`（MySQL）	`ALTER TABLE t RENAME COLUMN a TO b;`
删除字段	`DROP COLUMN`	`ALTER TABLE t DROP COLUMN c;`
修改表名	`RENAME TO`	`ALTER TABLE t RENAME TO t_new;`

除了 ALTER TABLE，SQL 还支持以下命令 👇

命令	用途
`ALTER DATABASE`	修改数据库属性（如字符集、排序规则等）
`ALTER USER`	修改用户属性、密码或权限
`ALTER VIEW`	修改视图定义（相当于重新创建视图）
`ALTER INDEX`	重建或重命名索引，提高性能或调整结构

删除表

DROP TABLE IF EXISTS users;
TRUNCATE TABLE users; -- 清空表（注意事务行为在不同数据库中不同）

3. 数据操纵语言（DML）

插入数据

INSERT INTO users (username, email, age) VALUES ('alice', 'a@ex.com', 25);

-- 多行插入
INSERT INTO users (username, email, age)
VALUES ('bob', 'b@ex.com', 30), ('cathy', 'c@ex.com', 22);

查询数据（基础）

SELECT id, username, email FROM users;
SELECT * FROM users WHERE is_active = TRUE;

更新数据

UPDATE users SET age = age + 1 WHERE id = 10;

注意：没有 WHERE 将更新全表。

删除数据

DELETE FROM users WHERE id = 10;
-- 或使用 TRUNCATE 快速清空（不可回滚在某些引擎/配置下）

4. 查询基础与 WHERE 子句详解

比较运算符与逻辑运算

=, <>, !=, >, <, >=, <=, IS NULL, IS NOT NULL, BETWEEN, IN, LIKE, ILIKE (Postgres)

SELECT * FROM orders
WHERE amount BETWEEN 100 AND 500
  AND status IN ('paid', 'shipped')
  AND created_at >= '2024-01-01';

LIKE 与通配符

%：零个或多个任意字符
_：单个任意字符

SELECT * FROM users WHERE username LIKE 'a%'; -- 以 a 开头
SELECT * FROM users WHERE username LIKE '_an%'; -- 第二个字母为 a，第三个字母为 n

Postgres 支持 ILIKE 做不区分大小写的匹配：

SELECT * FROM users WHERE username ILIKE '%Alice%';

NULL 的注意事项

NULL 表示未知，NULL = NULL 是 NULL，需使用 IS NULL / IS NOT NULL。

SELECT * FROM table WHERE col IS NULL;

优化 WHERE：避免函数在列上（影响索引）

差的写法： WHERE DATE(created_at) = '2025-01-01' 会导致索引失效。推荐：WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02'。

5. 连接（JOIN）详解与示例

JOIN 类型

INNER JOIN：只返回匹配的行
LEFT JOIN / LEFT OUTER JOIN：返回左表所有行 + 右表匹配
RIGHT JOIN：返回右表所有行 + 左表匹配（MySQL/Postgres 支持）
FULL JOIN / FULL OUTER JOIN：返回两表所有行（Postgres 支持，MySQL 需用 UNION 模拟）
CROSS JOIN：笛卡尔积
自连接：将表与自身连接

示例：内连接

SELECT u.id, u.username, o.id AS order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

示例：左连接（常用于查找无关联记录）

SELECT u.id, u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL; -- 查找没有订单的用户

多表连接顺序与别名

使用表别名简洁且提高可读性：users u JOIN orders o JOIN products p。注意写清楚每个 JOIN 的 ON 条件以避免产生笛卡尔积。

6. 聚合、GROUP BY 与 HAVING

常见聚合函数

COUNT(), SUM(), AVG(), MIN(), MAX(), GROUP_CONCAT (MySQL), STRING_AGG (Postgres)

SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

GROUP BY 的规则

在标准 SQL 中，非聚合列必须出现在 GROUP BY 中，或者在支持的数据库中确定性地使用函数/表达式。
MySQL 有 ONLY_FULL_GROUP_BY 模式影响行为，建议开启并遵循标准 SQL 写法。

7. 子查询（Subquery）与公用表表达式（CTE）

标准子查询

标量子查询（返回单个值）
行子查询（返回单列多行，可与 IN/EXISTS 联用）
表子查询（在 FROM 中当作临时表）

-- 在 WHERE 中使用子查询
SELECT username FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 标量子查询
SELECT username, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count FROM users;

EXISTS vs IN

EXISTS 在存在性判断时通常比 IN 更高效（尤其是子查询返回大量值时）。

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);

公用表表达式（CTE）

WITH recent_orders AS (
  SELECT user_id, SUM(amount) AS total FROM orders WHERE created_at >= '2025-01-01' GROUP BY user_id
)
SELECT u.username, r.total FROM users u JOIN recent_orders r ON u.id = r.user_id;

PostgreSQL 和 SQL Server 支持递归 CTE（WITH RECURSIVE），MySQL 从 8.0 开始支持 CTE。

8. 集合运算（UNION/INTERSECT/EXCEPT）

-- 合并两次查询结果并去重（UNION ALL 不去重以提升性能）
SELECT id, username FROM users_active
UNION
SELECT id, username FROM users_inactive;

-- PostgreSQL 支持 INTERSECT 和 EXCEPT（MySQL 8.0+ 不支持 INTERSECT/EXCEPT）
SELECT id FROM table1 INTERSECT SELECT id FROM table2;
SELECT id FROM table1 EXCEPT SELECT id FROM table2; -- 相当于 table1 - table2

9. 窗口函数（Window Functions）

窗口函数用于在查询结果行的“窗口”上进行计算，不会折叠行。常见函数有：ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER (...)

SELECT id, user_id, amount,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
       SUM(amount) OVER (PARTITION BY user_id) AS user_total
FROM orders;

窗口函数在分析与排序分组场景非常有用。

10. 视图（VIEW）与物化视图（Materialized View）

CREATE VIEW active_users AS SELECT id, username FROM users WHERE is_active = TRUE;

-- PostgreSQL 支持物化视图
CREATE MATERIALIZED VIEW mv_top_users AS
SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id;
REFRESH MATERIALIZED VIEW mv_top_users;

视图是查询的别名；物化视图会存储结果以提升查询性能，但需要刷新以保持同步。

11. 约束（Constraints）详解

PRIMARY KEY：主键，唯一且不为空
UNIQUE：唯一约束，可为单列或多列组合
FOREIGN KEY：外键，参考其他表，支持 ON DELETE CASCADE 等
NOT NULL：非空
CHECK：条件约束（MySQL 从 8.0.16 开始更好支持）
DEFAULT：默认值

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL CHECK (amount >= 0),
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

12. 事务（Transactions）与隔离级别

基本事务语句

BEGIN; -- 或 START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- ROLLBACK; -- 回滚

隔离级别（四种标准）

READ UNCOMMITTED（脏读）
READ COMMITTED（不可重复读可能存在）
REPEATABLE READ（MySQL InnoDB 默认）
SERIALIZABLE（最高隔离，性能影响大）

不同数据库对隔离级别的实现略有差异：例如 MySQL 的 REPEATABLE READ 使用多版本并发控制（MVCC）防止幻读。

13. 索引（Indexes）详解与优化策略

常见索引类型

B-Tree（默认，多数场景）
Hash（用于等值查询，Postgres 支持）
GiST / GIN（全文搜索、数组、JSONB，Postgres）
倒排索引（全文检索）

创建索引

CREATE INDEX idx_users_username ON users(username);
-- 组合索引（注意列顺序）
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
-- 覆盖索引（covering index）: 查询只访问索引即可满足 SELECT 列

索引使用建议

在频繁做 WHERE / JOIN / ORDER BY / GROUP BY 的列上建立索引。
组合索引列顺序应基于最常用的查询过滤顺序。
避免在高基数低选择性的列（如性别）上建立单独索引。
小表不需要过多索引，索引会影响写入性能（INSERT/UPDATE/DELETE）。
使用 EXPLAIN 查看查询是否使用索引。

删除索引

DROP INDEX idx_users_username; -- MySQL
DROP INDEX idx_users_username ON users; -- MySQL 有时需要指定表
DROP INDEX idx_users_username; -- PostgreSQL (在索引名上操作)

14. 性能优化与查询计划（EXPLAIN / ANALYZE）

使用 EXPLAIN 查看执行计划，了解是否走索引、全表扫描、行估算等。
EXPLAIN ANALYZE（Postgres）会实际执行并返回真实耗时。
常见优化策略：加索引、重写子查询为 JOIN 或 CTE、避免 SELECT *、分页使用索引（不要 OFFSET 很大）、限制返回列与行、拆分复杂查询为中间表或物化视图。

示例（Postgres）：

EXPLAIN ANALYZE SELECT u.username, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.username;

15. 分区（Partitioning）与分表策略

分区按范围（RANGE）、按列表（LIST）、按哈希（HASH）等划分，能提升大表查询与维护性能。
水平分表将数据按某个维度拆分到多个表，适用于超大规模数据写入与查询。
注意分区键的选择，避免跨分区查询频繁。

MySQL 示例（RANGE 分区）：

CREATE TABLE logs (
  id BIGINT,
  created_at DATE
) PARTITION BY RANGE ( YEAR(created_at) ) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025)
);

16. 触发器（Triggers）与存储过程（Stored Procedures）

触发器示例（MySQL）

CREATE TRIGGER orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
SET NEW.created_at = IFNULL(NEW.created_at, NOW());

存储过程（示例）

-- MySQL
DELIMITER $$
CREATE PROCEDURE transfer_funds(IN from_id INT, IN to_id INT, IN amt DECIMAL(10,2))
BEGIN
  START TRANSACTION;
    UPDATE accounts SET balance = balance - amt WHERE id = from_id;
    UPDATE accounts SET balance = balance + amt WHERE id = to_id;
  COMMIT;
END$$
DELIMITER ;

注意：存储过程在不同数据库中语法差异较大（Postgres 使用 PL/pgSQL）。

17. 备份与导入导出（Export / Import）

MySQL：mysqldump 导出，mysql 恢复。

mysqldump -u root -p dbname > dump.sql
mysql -u root -p dbname < dump.sql

PostgreSQL：pg_dump / pg_restore。

pg_dump -U user -Fc dbname > dump.dump
pg_restore -U user -d dbname dump.dump

SQLite：直接拷贝 .db 文件或使用 .dump。

考虑点：在线备份、逻辑备份与物理备份、增量备份、恢复演练。

18. 安全与权限管理（Users / Grants）

CREATE USER 'appuser'@'%' IDENTIFIED BY 'secret'; -- MySQL
GRANT SELECT, INSERT, UPDATE ON dbname.* TO 'appuser'@'%';
REVOKE DELETE ON dbname.* FROM 'appuser'@'%';
FLUSH PRIVILEGES;

Postgres：CREATE ROLE appuser WITH LOGIN PASSWORD 'secret'; GRANT ...

注意：最小权限原则、避免使用 root/superuser 执行应用程序连接。

19. 常见函数（字符串、日期、数学、聚合）

字符串

CONCAT, ||(Postgres), SUBSTRING, LEFT, RIGHT, TRIM, REPLACE, LOWER, UPPER

日期时间

NOW(), CURRENT_TIMESTAMP, DATE_TRUNC(Postgres), DATE_FORMAT(MySQL), EXTRACT(YEAR FROM ts)

数学

ROUND, CEIL, FLOOR, ABS

聚合（窗口/普通）

COUNT, SUM, AVG, MIN, MAX, STRING_AGG, GROUP_CONCAT

20. 正则、全文检索与模糊匹配

PostgreSQL 提供强大的全文检索（to_tsvector, to_tsquery, @@）以及正则表达式匹配（~, ~*）。
MySQL 提供 FULLTEXT 索引用于全文搜索（对 MyISAM / InnoDB 支持差异）。
模糊匹配：使用 LIKE, ILIKE, 或借助 trigram 扩展（Postgres 的 pg_trgm）提升模糊搜索性能。

示例（Postgres 全文）：

SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('database & index');

21. 设计范式、反范式与建模建议

规范化（Normalization）

1NF, 2NF, 3NF, BCNF 等用于消除冗余、保持数据一致性。
规范化会导致更多表与 JOIN，影响 OLAP/OLTP 性能权衡。

反范式（Denormalization）

为提升查询性能或简化查询，可适当冗余（例如预计算汇总、缓存字段）。
结合物化视图、预聚合表或缓存层（Redis）使用。

建模建议

明确访问模式（读多写少或写多读少），选择适配的设计。
选择合适的主键（单列自增 vs UUID vs 组合键）。
为时间序列数据考虑分区或专用时间序列数据库。

22. 常见陷阱与调试技巧

忘记 WHERE 导致全表更新/删除。
在大字段上做 LIKE '%...%' 导致全表扫描。
隐式类型转换导致索引失效（例如把数字列与字符比较）。
未合理使用分页（OFFSET 大时性能差），使用基于索引的分页（WHERE id > last_id LIMIT n）。
大事务造成锁等待与长时间占用资源，避免在事务中做大量计算或交互式操作。

调试技巧：查看慢查询日志、使用 EXPLAIN、在开发环境复制查询、限制数据量排查问题。

23. 示例：综合查询与实战题

示例 1：每个用户最近 3 条订单（使用窗口函数）

SELECT id, user_id, amount, created_at FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t WHERE rn <= 3;

示例 2：按月活跃用户与月付费总额

SELECT DATE_TRUNC('month', o.created_at) AS month,
       COUNT(DISTINCT o.user_id) AS active_users,
       SUM(o.amount) AS total_revenue
FROM orders o
GROUP BY month
ORDER BY month;

示例 3：查找 A 表中不在 B 表的记录（反连接）

SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

24. 附：不同数据库的差异速查表

功能	MySQL	PostgreSQL	SQLite	SQL Server	Oracle
支持 CTE	8.0+	支持	支持	支持	支持
窗口函数	8.0+	支持	支持	支持	支持
FULL OUTER JOIN	支持（MySQL 支持）	支持	支持	支持	支持
INTERSECT/EXCEPT	MySQL 8.0+ 支持部分	支持	支持	支持	支持
JSON 支持	JSON / JSONB（MySQL 有 JSON）	强（JSONB）	支持（扩展）	有	有
事务隔离默认	REPEATABLE READ (InnoDB)	READ COMMITTED	SERIALIZABLE (file-based caveats)	READ COMMITTED	READ COMMITTED

常用速查小节

常用 DDL

CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50));
ALTER TABLE t ADD COLUMN created_at TIMESTAMP;
DROP TABLE t;

常用 DML

SELECT * FROM t WHERE name LIKE 'A%';
INSERT INTO t (id,name) VALUES (1,'A');
UPDATE t SET name = 'B' WHERE id = 1;
DELETE FROM t WHERE id = 1;

posted @ 2025-10-16 16:10 灰灰奋斗录阅读(15) 评论(0) 收藏举报

刷新页面返回顶部

bch24