SQL 语法基础指南SQL 是用于管理关系型数据库的标准查询语言。本文按照 SQL 标准语法，系统介绍 SQL 相关概念

SQL (Structured Query Language) 是用于管理关系型数据库的标准查询语言。本文按照 SQL 标准语法，系统介绍数据定义(DDL)、数据查询(DQL)、数据操作(DML)、事务控制(TCL)和数据控制(DCL)五大核心语法类别，帮助读者掌握 SQL 的基础语法规则和使用方法。

SQL 标准的演进

SQL 由国际标准化组织 ISO 和美国国家标准协会 ANSI 共同制定和维护。标准持续演进，主要版本包括 SQL-86、SQL-92、SQL:1999、SQL:2003、SQL:2011、SQL:2016 等，每个版本都引入新特性以适应数据库技术的发展。

虽然各数据库厂商（如 PostgreSQL、MySQL、Oracle、SQL Server）在实现上有所差异，但它们都遵循 SQL 标准的核心语法。理解标准语法有助于编写可移植的 SQL 代码。

SQL 标准文档来源：

ISO/IEC 9075 标准：SQL 的官方标准由 ISO/IEC JTC 1/SC 32 维护，可以从 ISO 官网购买正式文档
- ISO 官网：www.iso.org/standard/76…
- 标准编号：ISO/IEC 9075（多个部分）
SQL 标准参考资料：
- Modern SQL 网站：modern-sql.com/ （介绍 SQL 标准演进）
- Wikipedia SQL 标准页面：en.wikipedia.org/wiki/SQL

本文基于 SQL 标准语法编写，示例代码在遵循标准的数据库（如 PostgreSQL）中均可运行。

SQL 中的重点概念

在深入学习 SQL 语法之前，理解一些核心概念有助于更好地掌握 SQL 的使用。本章将 SQL 中的重要概念按类别进行介绍。

数据库对象层次结构

SQL 数据库采用层次化的组织结构，从上到下依次为：

Database（数据库）→ Schema（模式）→ Table/View/Function（表/视图/函数等）→ Column/Row（列/行）

Database（数据库）

数据库是所有数据和对象的最顶层容器。一个数据库服务器可以包含多个数据库。

-- 创建数据库
CREATE DATABASE company;

-- 连接到数据库（语法因数据库而异）
\c company  -- PostgreSQL
USE company;  -- MySQL

-- 删除数据库
DROP DATABASE company;

Schema（模式）

Schema 是数据库中对象的逻辑容器，类似于文件系统中的文件夹。一个数据库可以包含多个 Schema，每个 Schema 可以包含表、视图、函数等对象。

Schema 的作用：

组织数据库对象：将相关对象分组管理
命名空间隔离：不同 Schema 中可以有同名的表
权限控制：可以对整个 Schema 授权

-- 创建 Schema
CREATE SCHEMA sales;
CREATE SCHEMA hr;

-- 在 Schema 中创建表
CREATE TABLE sales.orders (
    id INTEGER PRIMARY KEY,
    total_amount DECIMAL(10, 2)
);

-- 使用 Schema
SELECT * FROM sales.orders;

-- 设置当前 Schema（搜索路径）
SET search_path TO sales, public;
SELECT * FROM orders;  -- 自动查找 sales.orders

-- 删除 Schema
DROP SCHEMA sales CASCADE;  -- CASCADE 删除 Schema 中的所有对象

Table（表）

表是存储数据的基本单位，由行（记录）和列（字段）组成。表是关系型数据库的核心概念。

表的组成：

Column（列）：定义数据的结构，每列有名称和数据类型
Row（行）：表中的一条记录，包含每列的具体值
约束（Constraint）：定义列或表的规则（如主键、外键、唯一性等）

-- 创建表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构（PostgreSQL）
\d users

-- 修改表结构
ALTER TABLE users ADD COLUMN age INTEGER;

-- 删除表
DROP TABLE users;

虚拟对象和辅助对象

View（视图）

视图是基于查询结果的虚拟表。视图不存储实际数据，而是存储查询定义，每次访问视图时动态执行查询。

视图的作用：

简化复杂查询：将复杂的 JOIN 和聚合封装成视图
数据安全：隐藏敏感列，只暴露部分数据
逻辑数据独立性：应用程序依赖视图而非表，表结构变化时只需修改视图定义

-- 创建视图
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE is_active = TRUE;

-- 使用视图（像使用表一样）
SELECT * FROM active_users;

-- 删除视图
DROP VIEW active_users;

Index（索引）

索引是数据库用于加速数据检索的数据结构。索引类似于书的目录，可以快速定位数据位置。

索引类型：

B-Tree 索引：默认索引类型，适用于等值查询和范围查询
Hash 索引：只支持等值查询
GiST 索引：通用搜索树，适用于几何数据
GIN 索引：倒排索引，适用于全文搜索和数组查询

-- 创建普通索引
CREATE INDEX idx_users_email ON users(email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 创建复合索引
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- 创建部分索引（条件索引）
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;

-- 删除索引
DROP INDEX idx_users_email;

-- 之后正常写查询即可，数据库会自动使用索引
SELECT * FROM users WHERE email = 'user@example.com';
-- 不需要写成：SELECT * FROM users USE INDEX (idx_users_email) WHERE ...

索引使用原则：

在经常作为查询条件的列上创建索引（WHERE、JOIN）
索引加速查询但会降低写入性能
避免过度索引，每个索引都会占用存储空间

Sequence（序列）

序列是自动生成唯一数字的数据库对象，常用于生成主键值。

-- 创建序列
CREATE SEQUENCE user_id_seq
START WITH 1
INCREMENT BY 1;

-- 获取下一个值
SELECT nextval('user_id_seq');

-- 获取当前值（不增加）
SELECT currval('user_id_seq');

-- 在表中使用序列
CREATE TABLE users (
    id INTEGER DEFAULT nextval('user_id_seq') PRIMARY KEY,
    username VARCHAR(50)
);

-- 删除序列
DROP SEQUENCE user_id_seq;

可执行对象

Function（函数）

函数是返回单个值的可重用代码块，可以在 SELECT 语句中使用。

-- 创建函数（PostgreSQL）
CREATE FUNCTION calculate_discount(price DECIMAL, discount_rate DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
    RETURN price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT calculate_discount(100, 0.1);  -- 返回 90

-- 在查询中使用函数
SELECT id, name, price, calculate_discount(price, 0.1) AS discounted_price
FROM products;

-- 删除函数
DROP FUNCTION calculate_discount;

Stored Procedure（存储过程）

存储过程可以执行复杂逻辑，可以返回多个结果集或不返回值。

存储过程 vs 函数：

函数：返回单个值，可以在 SELECT 语句中使用
存储过程：可以执行复杂逻辑，可以返回多个结果集或不返回值

-- 创建存储过程（PostgreSQL 11+）
CREATE PROCEDURE transfer_funds(
    from_account INTEGER,
    to_account INTEGER,
    amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
END;
$$;

-- 调用存储过程
CALL transfer_funds(1, 2, 100);

-- 删除存储过程
DROP PROCEDURE transfer_funds;

Trigger（触发器）

触发器是在特定事件（INSERT、UPDATE、DELETE）发生时自动执行的数据库对象。触发器用于维护数据完整性、审计日志、自动更新关联数据等。

触发器类型：

BEFORE 触发器：在事件执行前触发，可以修改即将写入的数据
AFTER 触发器：在事件执行后触发，适合审计日志
INSTEAD OF 触发器：替代原操作，常用于视图

-- 创建触发器函数（PostgreSQL）
CREATE OR REPLACE FUNCTION update_modified_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trigger_update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_timestamp();

-- 删除触发器
DROP TRIGGER trigger_update_timestamp ON users;

触发器使用场景：

自动更新时间戳
记录审计日志
验证数据完整性
级联更新关联数据

注意事项：

触发器会影响性能，谨慎使用
触发器逻辑应简单明确
避免触发器之间的循环调用

数据完整性和约束

Constraint（约束）

约束用于确保数据的完整性和一致性。主要约束类型包括：

PRIMARY KEY（主键约束）：

唯一标识表中的每一行
不允许 NULL 值
一个表只能有一个主键（可以是单列或多列组合）

FOREIGN KEY（外键约束）：

建立表之间的关联关系
外键列的值必须在被引用表的主键或唯一键中存在（或为 NULL）
维护参照完整性

级联操作：

ON DELETE CASCADE：删除父表记录时，自动删除子表关联记录
ON DELETE SET NULL：删除父表记录时，子表外键设为 NULL
ON DELETE RESTRICT：如果存在子表关联记录，禁止删除父表记录
ON UPDATE CASCADE：更新父表主键时，自动更新子表外键

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

UNIQUE（唯一约束）：

确保列中的值唯一
允许 NULL 值（NULL 不等于 NULL）

CHECK（检查约束）：

限制列值必须满足指定条件

NOT NULL（非空约束）：

确保列不能存储 NULL 值

DEFAULT（默认值约束）：

为列指定默认值

事务和并发控制

Transaction（事务）

事务是一组 SQL 语句的逻辑执行单元，要么全部成功，要么全部失败。

ACID 特性：

原子性 (Atomicity)：事务中的所有操作要么全部完成，要么全部不完成
一致性 (Consistency)：事务执行前后，数据库从一个一致性状态转换到另一个一致性状态
隔离性 (Isolation)：多个事务并发执行时，互不干扰
持久性 (Durability)：事务提交后，对数据的修改是永久的

事务控制语句：

BEGIN / START TRANSACTION：开始事务
COMMIT：提交事务
ROLLBACK：回滚事务
SAVEPOINT：设置保存点

Isolation Level（隔离级别）

SQL 标准定义了四种事务隔离级别：

READ UNCOMMITTED：读未提交（可能出现脏读）
READ COMMITTED：读已提交
REPEATABLE READ：可重复读
SERIALIZABLE：串行化

权限和安全

User（用户）

用户是数据库的访问主体，可以登录数据库的实体，具有身份验证信息（如密码）。

Role（角色）

角色是一组权限的集合，不一定能登录，主要用于权限管理。

角色 vs 用户：

用户：可以登录数据库，具有密码
角色：权限集合，可以授予用户或其他角色

Privilege（权限）

权限控制用户对数据库对象的访问和操作，包括：

SELECT、INSERT、UPDATE、DELETE：数据操作权限
CREATE、ALTER、DROP：对象定义权限
EXECUTE：执行函数或存储过程的权限
USAGE：使用 Schema 或序列的权限
CONNECT：连接数据库的权限
TRIGGER：创建触发器的权限

概念总结

分类	概念	说明
层次结构	Database	最顶层容器
	Schema	逻辑命名空间
	Table	存储数据的基本单位
	Column	列/字段
	Row	行/记录
虚拟和辅助	View	虚拟表
	Index	加速检索
	Sequence	自增序列
可执行对象	Function	返回值的函数
	Stored Procedure	存储过程
	Trigger	自动触发的操作
数据完整性	Primary Key	主键约束
	Foreign Key	外键约束
	Unique	唯一约束
	Check	检查约束
	Not Null	非空约束
	Default	默认值约束
事务控制	Transaction	事务
	Isolation Level	隔离级别
	Savepoint	保存点
权限安全	User	用户
	Role	角色
	Privilege	权限

这些概念构成了 SQL 数据库的完整体系。理解它们有助于设计高效、可维护的数据库应用。

SQL 基础语法规则

SQL 语法规则定义了如何编写合法的 SQL 语句。掌握这些基础规则是编写正确、可读的 SQL 代码的前提。

语法约定

大小写：

SQL 关键字（如 SELECT、FROM、WHERE）不区分大小写，但惯例上使用大写以提高可读性
标识符（表名、列名）在标准中不区分大小写，但某些数据库（如 PostgreSQL）在使用双引号时区分大小写

-- 以下三种写法等价
SELECT name FROM users;
select name from users;
Select Name From Users;

语句分隔符：

每条 SQL 语句以分号 ; 结尾
单条语句执行时可以省略分号，但多条语句必须使用分号分隔

-- 单条语句
SELECT * FROM users;

-- 多条语句
SELECT * FROM users;
SELECT * FROM orders;

注释：

单行注释：使用 -- 开头
多行注释：使用 /* */ 包围

-- 这是单行注释
SELECT * FROM users; -- 也可以在语句后添加注释

/*
这是多行注释
可以跨越多行
*/
SELECT * FROM orders;

命名规范

标识符规则：

标识符（表名、列名、索引名等）必须以字母或下划线开头
可以包含字母、数字、下划线
长度限制因数据库而异，标准建议不超过 128 字符
避免使用 SQL 关键字作为标识符

-- 合法的标识符
user_name
User123
_temp_table

-- 不推荐的标识符（关键字）
select
table
order

-- 使用双引号可以使用关键字或特殊字符（不推荐）
"select"
"user-name"

数据类型

SQL 标准定义了以下主要数据类型：

数值类型：

INTEGER / INT：整数
SMALLINT：小整数
BIGINT：大整数
DECIMAL(p, s) / NUMERIC(p, s)：定点数，p 为精度，s 为小数位数
REAL：单精度浮点数
DOUBLE PRECISION：双精度浮点数

字符类型：

CHAR(n) / CHARACTER(n)：定长字符串，不足 n 个字符会用空格填充
VARCHAR(n) / CHARACTER VARYING(n)：变长字符串，最大长度 n
TEXT：可变长文本（非标准，但广泛支持）

日期时间类型：

DATE：日期（年-月-日）
TIME：时间（时:分:秒）
TIMESTAMP：时间戳（日期 + 时间）
INTERVAL：时间间隔

布尔类型：

BOOLEAN：布尔值，取值为 TRUE 或 FALSE

其他类型：

BLOB：二进制大对象
CLOB：字符大对象

字面量表示

字符串字面量：

使用单引号 ' 包围
单引号本身需要转义为两个单引号 ''

SELECT 'Hello World';
SELECT 'It''s a test';  -- 输出: It's a test

数值字面量：

整数直接书写
小数使用点号 . 分隔
科学计数法使用 E 或 e

SELECT 123;
SELECT 123.45;
SELECT 1.23E2;  -- 123

日期时间字面量：

使用标准格式字符串，配合类型转换

SELECT DATE '2025-12-10';
SELECT TIME '14:30:00';
SELECT TIMESTAMP '2025-12-10 14:30:00';

布尔字面量：

SELECT TRUE;
SELECT FALSE;

NULL 值：

NULL 表示缺失或未知的值
NULL 不等于任何值（包括它自己）

SELECT NULL;
SELECT column_name IS NULL;      -- 检查是否为 NULL
SELECT column_name IS NOT NULL;  -- 检查是否不为 NULL

数据定义语言 (DDL)

DDL (Data Definition Language) 用于定义和管理数据库对象的结构，如数据库、表、索引等。DDL 语句会影响数据库的架构，而不直接操作数据本身。

CREATE 语句

CREATE 用于创建数据库对象。

创建数据库：

CREATE DATABASE company;

创建表：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

创建索引：

-- 创建普通索引
CREATE INDEX idx_department ON employees(department);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON employees(email);

ALTER 语句

ALTER 用于修改已存在的数据库对象结构。

添加列：

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

修改列类型：

ALTER TABLE employees
ALTER COLUMN salary TYPE DECIMAL(12, 2);

删除列：

ALTER TABLE employees
DROP COLUMN phone;

添加约束：

ALTER TABLE employees
ADD CONSTRAINT check_salary CHECK (salary > 0);

DROP 语句

DROP 用于删除数据库对象。删除操作不可恢复，需谨慎使用。

删除表：

DROP TABLE employees;

删除数据库：

DROP DATABASE company;

删除索引：

DROP INDEX idx_department;

条件删除（如果存在才删除）：

DROP TABLE IF EXISTS employees;

TRUNCATE 语句

TRUNCATE 用于快速清空表中的所有数据，但保留表结构。

TRUNCATE TABLE employees;

TRUNCATE 与 DELETE 的区别：

TRUNCATE 是 DDL 操作，直接释放表空间，速度快，不记录单行删除日志
DELETE 是 DML 操作，逐行删除数据，速度慢，可以回滚
TRUNCATE 会重置自增计数器，DELETE 不会

约束类型

约束 (Constraint) 用于限制表中数据的规则，确保数据的完整性和一致性。

PRIMARY KEY（主键约束）：

唯一标识表中的每一行
不允许 NULL 值
一个表只能有一个主键（可以是单列或多列组合）

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50)
);

-- 多列组合主键
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

FOREIGN KEY（外键约束）：

建立表之间的关联关系
外键列的值必须在被引用表的主键或唯一键中存在（或为 NULL）
维护参照完整性

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 指定级联操作
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE  -- 删除用户时同时删除其订单
        ON UPDATE CASCADE  -- 更新用户 ID 时同时更新订单中的 user_id
);

UNIQUE（唯一约束）：

确保列中的值唯一
允许 NULL 值（NULL 不等于 NULL）
一个表可以有多个唯一约束

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE
);

CHECK（检查约束）：

限制列值必须满足指定条件
可以基于单列或多列

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    age INTEGER CHECK (age >= 18),
    salary DECIMAL(10, 2) CHECK (salary > 0),
    start_date DATE,
    end_date DATE,
    CHECK (end_date > start_date)  -- 多列约束
);

NOT NULL（非空约束）：

确保列不能存储 NULL 值
必须在插入或更新时提供值

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

DEFAULT（默认值约束）：

为列指定默认值
插入数据时如果未提供该列的值，使用默认值

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

数据查询语言 (DQL)

DQL (Data Query Language) 用于从数据库中检索数据。SELECT 是 DQL 的核心语句，也是 SQL 中使用最频繁的语句。

SELECT 基本语法结构

SELECT 语句的完整语法结构如下：

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...]
[LIMIT count [OFFSET offset]];

简单查询：

-- 查询所有列
SELECT * FROM users;

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

-- 使用别名
SELECT id AS user_id, username AS name FROM users;

-- 去重查询
SELECT DISTINCT department FROM employees;

WHERE 子句

WHERE 子句用于筛选满足条件的记录。

比较运算符：

-- 等于
SELECT * FROM products WHERE price = 99.99;

-- 不等于
SELECT * FROM products WHERE price != 99.99;
SELECT * FROM products WHERE price <> 99.99;  -- 标准写法

-- 大于、小于
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <= 50;

-- BETWEEN
SELECT * FROM products WHERE price BETWEEN 50 AND 100;  -- 包含 50 和 100

-- IN
SELECT * FROM users WHERE status IN ('active', 'pending');

-- LIKE（模糊匹配）
SELECT * FROM users WHERE username LIKE 'admin%';  -- 以 admin 开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- 以 @gmail.com 结尾
SELECT * FROM users WHERE username LIKE '%test%';  -- 包含 test
SELECT * FROM users WHERE username LIKE '_dmin';  -- _ 匹配单个字符

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

逻辑运算符：

-- AND
SELECT * FROM products WHERE price > 50 AND stock_quantity > 0;

-- OR
SELECT * FROM users WHERE status = 'active' OR status = 'pending';

-- NOT
SELECT * FROM users WHERE NOT status = 'banned';

-- 组合使用（使用括号控制优先级）
SELECT * FROM products
WHERE (category = 'electronics' OR category = 'books')
  AND price < 100;

JOIN 连接

JOIN 用于关联多个表的数据。理解不同类型的 JOIN 对于编写复杂查询至关重要。

INNER JOIN（内连接）：

返回两个表中满足连接条件的记录
只返回匹配的行

-- 查询用户及其订单信息
SELECT users.username, orders.id, orders.total_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- 简写（省略 INNER）
SELECT u.username, o.id, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id;

LEFT JOIN（左外连接）：

返回左表的所有记录
如果右表没有匹配的记录，右表列显示为 NULL

-- 查询所有用户及其订单（包括没有订单的用户）
SELECT u.username, o.id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

RIGHT JOIN（右外连接）：

返回右表的所有记录
如果左表没有匹配的记录，左表列显示为 NULL

-- 查询所有订单及其用户（包括用户已删除的订单）
SELECT u.username, o.id, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

FULL OUTER JOIN（全外连接）：

返回两个表的所有记录
没有匹配的记录对应列显示为 NULL

-- 查询所有用户和所有订单（包括没有订单的用户和没有用户的订单）
SELECT u.username, o.id, o.total_amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

CROSS JOIN（交叉连接）：

返回两个表的笛卡尔积
左表每行与右表每行组合

-- 生成所有颜色和尺寸的组合
SELECT colors.name AS color, sizes.name AS size
FROM colors
CROSS JOIN sizes;

多表连接：

-- 查询用户、订单、订单明细
SELECT u.username, o.id AS order_id, p.name AS product_name, oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

GROUP BY 和聚合函数

GROUP BY 用于按一个或多个列对结果进行分组，通常与聚合函数配合使用。

聚合函数：

-- COUNT：计数
SELECT COUNT(*) FROM users;  -- 统计总行数
SELECT COUNT(phone) FROM users;  -- 统计非 NULL 的 phone 数量
SELECT COUNT(DISTINCT department) FROM employees;  -- 统计不重复的部门数

-- SUM：求和
SELECT SUM(total_amount) FROM orders;

-- AVG：平均值
SELECT AVG(salary) FROM employees;

-- MAX / MIN：最大值 / 最小值
SELECT MAX(price) FROM products;
SELECT MIN(hire_date) FROM employees;

GROUP BY 分组：

-- 按部门统计员工数量
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- 按用户统计订单总额
SELECT user_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id;

-- 多列分组
SELECT department, status, COUNT(*) AS count
FROM employees
GROUP BY department, status;

-- 使用聚合函数计算百分比
SELECT
    department,
    COUNT(*) AS count,
    COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees) AS percentage
FROM employees
GROUP BY department;

HAVING 子句

HAVING 用于对分组后的结果进行筛选。它与 WHERE 的区别在于：

WHERE 在分组前筛选行
HAVING 在分组后筛选组

-- 查询订单总额超过 1000 的用户
SELECT user_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total_amount) > 1000;

-- 查询员工数量超过 10 的部门
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

-- WHERE 和 HAVING 组合使用
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > DATE '2020-01-01'  -- 分组前筛选：只统计 2020 年后入职的员工
GROUP BY department
HAVING AVG(salary) > 50000;  -- 分组后筛选：平均工资超过 50000 的部门

ORDER BY 排序

ORDER BY 用于对查询结果排序。

-- 升序排序（默认）
SELECT * FROM products ORDER BY price ASC;
SELECT * FROM products ORDER BY price;  -- ASC 可以省略

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

-- 多列排序
SELECT * FROM employees
ORDER BY department ASC, salary DESC;  -- 先按部门升序，再按工资降序

-- 使用列位置排序（不推荐，可读性差）
SELECT name, price FROM products ORDER BY 2 DESC;  -- 按第 2 列（price）降序

-- NULL 值排序
SELECT * FROM users ORDER BY phone NULLS FIRST;  -- NULL 值排在前面
SELECT * FROM users ORDER BY phone NULLS LAST;   -- NULL 值排在后面

LIMIT 和 OFFSET

LIMIT 用于限制返回的行数，OFFSET 用于跳过指定数量的行，常用于分页。

-- 限制返回行数
SELECT * FROM products LIMIT 10;  -- 返回前 10 条

-- 分页查询
SELECT * FROM products LIMIT 10 OFFSET 0;   -- 第 1 页（第 1-10 条）
SELECT * FROM products LIMIT 10 OFFSET 10;  -- 第 2 页（第 11-20 条）
SELECT * FROM products LIMIT 10 OFFSET 20;  -- 第 3 页（第 21-30 条）

-- 计算 OFFSET
-- 第 n 页（每页 page_size 条）：OFFSET = (n - 1) * page_size

注意： LIMIT 和 OFFSET 不是 SQL 标准的一部分，但被大多数数据库支持。标准的分页方式是使用 FETCH 和 OFFSET：

-- SQL 标准分页语法
SELECT * FROM products
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

子查询

子查询 (Subquery) 是嵌套在另一个查询中的查询。

WHERE 子句中的子查询：

-- 查询价格高于平均价格的商品
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 查询有订单的用户
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- 查询没有订单的用户
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

FROM 子句中的子查询：

-- 查询每个部门的平均工资
SELECT dept_avg.department, dept_avg.avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg
WHERE dept_avg.avg_salary > 60000;

SELECT 子句中的子查询：

-- 显示每个用户及其订单数量
SELECT
    u.username,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

CTE（公共表表达式）

CTE (Common Table Expression) 使用 WITH 关键字定义临时结果集，提高查询的可读性和可维护性。

-- 单个 CTE
WITH high_value_orders AS (
    SELECT user_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY user_id
    HAVING SUM(total_amount) > 1000
)
SELECT u.username, hvo.total_spent
FROM users u
JOIN high_value_orders hvo ON u.id = hvo.user_id;

-- 多个 CTE
WITH
user_order_count AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
),
user_total_spent AS (
    SELECT user_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY user_id
)
SELECT
    u.username,
    COALESCE(uoc.order_count, 0) AS order_count,
    COALESCE(uts.total_spent, 0) AS total_spent
FROM users u
LEFT JOIN user_order_count uoc ON u.id = uoc.user_id
LEFT JOIN user_total_spent uts ON u.id = uts.user_id;

-- 递归 CTE（树形结构遍历）
WITH RECURSIVE category_tree AS (
    -- 基础查询：顶层分类
    SELECT id, name, parent_id, 1 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归查询：子分类
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, id;

数据操作语言 (DML)

DML (Data Manipulation Language) 用于操作表中的数据，包括插入、更新和删除。DML 操作会直接修改数据内容，通常在事务中执行以保证数据一致性。

INSERT 语句

INSERT 用于向表中插入新数据。

插入单行：

-- 指定所有列的值
INSERT INTO users (id, username, email, created_at)
VALUES (1, 'alice', 'alice@example.com', CURRENT_TIMESTAMP);

-- 如果列顺序与表定义一致，可以省略列名
INSERT INTO users
VALUES (2, 'bob', 'bob@example.com', CURRENT_TIMESTAMP);

-- 省略有默认值或允许 NULL 的列
INSERT INTO users (id, username, email)
VALUES (3, 'charlie', 'charlie@example.com');

插入多行：

INSERT INTO products (id, name, price, stock_quantity)
VALUES
    (1, 'Laptop', 999.99, 10),
    (2, 'Mouse', 19.99, 50),
    (3, 'Keyboard', 49.99, 30);

从查询结果插入：

-- 将查询结果插入另一个表
INSERT INTO archived_orders (id, user_id, total_amount, order_date)
SELECT id, user_id, total_amount, order_date
FROM orders
WHERE order_date < DATE '2024-01-01';

-- 创建表并插入数据
CREATE TABLE high_value_customers AS
SELECT user_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total_amount) > 10000;

返回插入的数据：

-- 使用 RETURNING 子句返回插入的行（PostgreSQL 等支持）
INSERT INTO users (username, email)
VALUES ('david', 'david@example.com')
RETURNING id, username, created_at;

UPDATE 语句

UPDATE 用于修改表中已存在的数据。

更新所有行：

-- 给所有商品涨价 10%
UPDATE products
SET price = price * 1.1;

条件更新：

-- 更新指定用户的邮箱
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;

-- 更新多列
UPDATE users
SET email = 'newemail@example.com', updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- 使用表达式更新
UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE id = 10;

基于其他表更新：

-- 根据订单明细更新商品库存
UPDATE products
SET stock_quantity = stock_quantity - (
    SELECT SUM(quantity)
    FROM order_items
    WHERE order_items.product_id = products.id
      AND order_items.order_id = 100
)
WHERE id IN (SELECT product_id FROM order_items WHERE order_id = 100);

-- 使用 FROM 子句（PostgreSQL 等支持）
UPDATE products
SET stock_quantity = stock_quantity - oi.quantity
FROM order_items oi
WHERE products.id = oi.product_id
  AND oi.order_id = 100;

返回更新的数据：

-- 使用 RETURNING 子句返回更新后的行
UPDATE users
SET is_active = FALSE
WHERE last_login < DATE '2024-01-01'
RETURNING id, username, last_login;

DELETE 语句

DELETE 用于删除表中的数据。

删除所有行：

-- 删除表中所有数据（保留表结构）
DELETE FROM temp_table;

条件删除：

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

-- 删除多条记录
DELETE FROM orders WHERE status = 'cancelled';

-- 使用子查询删除
DELETE FROM order_items
WHERE order_id IN (
    SELECT id FROM orders WHERE status = 'cancelled'
);

基于其他表删除：

-- 使用 EXISTS 删除
DELETE FROM users
WHERE NOT EXISTS (
    SELECT 1 FROM orders WHERE orders.user_id = users.id
);

-- 使用 JOIN 删除（部分数据库支持）
DELETE users
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;

返回删除的数据：

-- 使用 RETURNING 子句返回删除的行
DELETE FROM users
WHERE is_active = FALSE
RETURNING id, username, email;

DELETE 与 TRUNCATE 的区别：

特性	DELETE	TRUNCATE
类型	DML	DDL
速度	慢（逐行删除）	快（直接释放空间）
WHERE 子句	支持	不支持
事务回滚	可以回滚	部分数据库可以回滚
触发器	触发 DELETE 触发器	不触发触发器
自增计数器	不重置	重置为初始值
日志记录	记录每行删除	最小日志记录

MERGE 语句

MERGE 语句（也称为 UPSERT）用于根据条件合并插入或更新数据。如果记录存在则更新，不存在则插入。

标准 MERGE 语法：

MERGE INTO products AS target
USING (VALUES
    (1, 'Laptop', 1099.99, 15),
    (2, 'Mouse', 24.99, 60),
    (4, 'Monitor', 299.99, 20)
) AS source (id, name, price, stock_quantity)
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET
        name = source.name,
        price = source.price,
        stock_quantity = source.stock_quantity
WHEN NOT MATCHED THEN
    INSERT (id, name, price, stock_quantity)
    VALUES (source.id, source.name, source.price, source.stock_quantity);

PostgreSQL 的 ON CONFLICT 语法：

-- 插入或更新（根据主键或唯一约束）
INSERT INTO products (id, name, price, stock_quantity)
VALUES (1, 'Laptop', 1099.99, 15)
ON CONFLICT (id)
DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    stock_quantity = EXCLUDED.stock_quantity;

-- 插入或忽略
INSERT INTO products (id, name, price, stock_quantity)
VALUES (1, 'Laptop', 1099.99, 15)
ON CONFLICT (id)
DO NOTHING;

-- 批量 UPSERT
INSERT INTO products (id, name, price, stock_quantity)
VALUES
    (1, 'Laptop', 1099.99, 15),
    (2, 'Mouse', 24.99, 60),
    (4, 'Monitor', 299.99, 20)
ON CONFLICT (id)
DO UPDATE SET
    price = EXCLUDED.price,
    stock_quantity = EXCLUDED.stock_quantity;

MySQL 的 ON DUPLICATE KEY UPDATE 语法：

INSERT INTO products (id, name, price, stock_quantity)
VALUES (1, 'Laptop', 1099.99, 15)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price),
    stock_quantity = VALUES(stock_quantity);

事务控制语言 (TCL)

TCL (Transaction Control Language) 用于管理数据库事务，确保数据的一致性和完整性。事务是一组 SQL 语句的逻辑执行单元，要么全部成功，要么全部失败。

BEGIN/START TRANSACTION 语句

BEGIN 或 START TRANSACTION 用于开始一个新事务。

-- 开始事务（两种写法等价）
BEGIN;
START TRANSACTION;

COMMIT 语句

COMMIT 用于提交事务，使所有更改永久生效。

BEGIN;

-- 执行 SQL 操作
INSERT INTO accounts (id, user_id, balance) VALUES (1, 100, 1000.00);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务（使更改永久生效）
COMMIT;

ROLLBACK 语句

ROLLBACK 用于回滚事务，撤销所有未提交的更改。

-- 回滚事务（撤销所有更改）
BEGIN;

INSERT INTO orders (user_id, total_amount) VALUES (1, 500.00);
-- 发现错误，回滚
ROLLBACK;

SAVEPOINT 语句

SAVEPOINT 用于在事务中设置保存点，允许部分回滚事务。

BEGIN;

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

-- 设置保存点
SAVEPOINT sp1;

INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');

-- 回滚到保存点（撤销 bob 的插入，但保留 alice）
ROLLBACK TO SAVEPOINT sp1;

-- 释放保存点（可选）
RELEASE SAVEPOINT sp1;

-- 提交事务
COMMIT;

事务隔离级别

SQL 标准定义了四种事务隔离级别，用于控制事务之间的可见性和并发行为。

隔离级别对比：

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED	可能	可能	可能	读取未提交的数据（最低级别）
READ COMMITTED	不可能	可能	可能	只读取已提交的数据（默认）
REPEATABLE READ	不可能	不可能	可能	可重复读（MySQL 默认）
SERIALIZABLE	不可能	不可能	不可能	串行化（最高级别）

并发问题说明：

脏读 (Dirty Read)：读取其他事务未提交的数据
不可重复读 (Non-Repeatable Read)：在同一事务中，两次读取同一行数据结果不同
幻读 (Phantom Read)：在同一事务中，两次查询结果的行数不同

设置隔离级别：

-- 设置当前会话的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
-- 执行事务操作
COMMIT;

-- 设置全局隔离级别（需要管理员权限）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

行锁定

在事务中，可以使用 FOR UPDATE 或 FOR SHARE 锁定查询的行，防止其他事务修改。

FOR UPDATE（排他锁）：

BEGIN;

-- 锁定行，其他事务无法修改或锁定这些行
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- 执行更新操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

FOR SHARE（共享锁）：

BEGIN;

-- 锁定行，其他事务可以读取但无法修改
SELECT * FROM products WHERE id = 10 FOR SHARE;

COMMIT;

事务使用示例

银行转账示例：

-- 银行转账：从账户 1 转账 100 元到账户 2
BEGIN;

-- 检查余额是否充足并锁定行
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- 扣款
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 到账
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 记录转账记录
INSERT INTO transactions (from_account, to_account, amount, transaction_date)
VALUES (1, 2, 100, CURRENT_TIMESTAMP);

-- 提交事务
COMMIT;

使用保存点的示例：

BEGIN;

-- 插入订单
INSERT INTO orders (user_id, total_amount) VALUES (1, 1000.00)
RETURNING id;  -- 假设返回订单 ID 为 100

SAVEPOINT order_items;

-- 插入订单明细
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (100, 1, 2, 500.00);

-- 更新库存失败
UPDATE products SET stock_quantity = stock_quantity - 2 WHERE id = 1;
-- 假设库存不足，回滚订单明细的插入
ROLLBACK TO SAVEPOINT order_items;

-- 取消订单
UPDATE orders SET status = 'cancelled' WHERE id = 100;

-- 提交事务
COMMIT;

数据控制语言 (DCL)

DCL (Data Control Language) 用于控制数据库的访问权限。通过 DCL，数据库管理员可以管理用户、角色和权限，确保数据的安全性。

用户管理

用户 (User) 是数据库的访问主体，每个用户都有独立的身份和权限。

创建用户：

-- 创建用户并设置密码
CREATE USER alice WITH PASSWORD 'secure_password';

-- 创建用户时指定更多属性
CREATE USER bob WITH
    PASSWORD 'password123'
    CREATEDB  -- 允许创建数据库
    VALID UNTIL '2025-12-31';  -- 账户有效期

修改用户：

-- 修改用户密码
ALTER USER alice WITH PASSWORD 'new_password';

-- 修改用户属性
ALTER USER bob WITH NOCREATEDB;  -- 撤销创建数据库权限

-- 重命名用户
ALTER USER alice RENAME TO alice_admin;

删除用户：

-- 删除用户
DROP USER bob;

-- 如果存在才删除
DROP USER IF EXISTS bob;

角色管理

角色 (Role) 是一组权限的集合。角色与用户的区别在于：

用户 (User)：可以登录数据库的实体，具有身份验证信息（如密码）
角色 (Role)：权限的集合，不一定能登录，主要用于权限管理

角色的优势：

简化权限管理：将相同权限的用户归为一组
易于维护：修改角色权限会自动影响所有拥有该角色的用户
支持权限继承：角色可以授予其他角色

创建角色：

-- 创建角色
CREATE ROLE readonly;
CREATE ROLE admin;

-- 创建可登录的角色（实际上就是用户）
CREATE ROLE developer WITH LOGIN PASSWORD 'dev123';

授予角色给用户：

-- 将角色授予用户
GRANT readonly TO alice;
GRANT admin TO bob;

-- 将角色授予另一个角色（角色继承）
GRANT readonly TO developer;

撤销角色：

-- 从用户撤销角色
REVOKE readonly FROM alice;

删除角色：

-- 删除角色
DROP ROLE readonly;

-- 如果存在才删除
DROP ROLE IF EXISTS readonly;

GRANT 语句

GRANT 用于授予用户或角色特定的数据库权限。

授予表权限：

-- 授予单个权限
GRANT SELECT ON users TO alice;

-- 授予多个权限
GRANT SELECT, INSERT, UPDATE ON products TO bob;

-- 授予所有权限
GRANT ALL PRIVILEGES ON orders TO admin;

-- 授予特定列的权限
GRANT SELECT (id, username), UPDATE (email) ON users TO alice;

授予数据库权限：

-- 授予连接数据库的权限
GRANT CONNECT ON DATABASE company TO alice;

-- 授予创建 schema 的权限
GRANT CREATE ON DATABASE company TO alice;

授予 schema 权限：

-- 授予 schema 的使用权限
GRANT USAGE ON SCHEMA public TO alice;

-- 授予在 schema 中创建对象的权限
GRANT CREATE ON SCHEMA public TO alice;

授予所有表的权限：

-- 授予 schema 中所有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 授予未来创建的表的权限（PostgreSQL）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

授予执行权限：

-- 授予执行函数或存储过程的权限
GRANT EXECUTE ON FUNCTION calculate_total(INTEGER) TO alice;

WITH GRANT OPTION：

-- 授予权限，并允许被授予者将权限授予其他人
GRANT SELECT ON users TO alice WITH GRANT OPTION;

-- alice 现在可以将 SELECT 权限授予其他用户
GRANT SELECT ON users TO bob;  -- alice 执行

REVOKE 语句

REVOKE 用于撤销用户或角色的权限。

撤销表权限：

-- 撤销单个权限
REVOKE SELECT ON users FROM alice;

-- 撤销多个权限
REVOKE INSERT, UPDATE ON products FROM bob;

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON orders FROM alice;

撤销授予权限的能力：

-- 撤销 GRANT OPTION
REVOKE GRANT OPTION FOR SELECT ON users FROM alice;

级联撤销：

-- 撤销权限，并级联撤销由该用户授予他人的权限
REVOKE SELECT ON users FROM alice CASCADE;

-- 只撤销直接授予的权限，不影响他人
REVOKE SELECT ON users FROM alice RESTRICT;

权限类型

SQL 标准定义了以下主要权限类型：

权限	说明	适用对象
`SELECT`	查询数据	表、视图、列
`INSERT`	插入数据	表、列
`UPDATE`	更新数据	表、列
`DELETE`	删除数据	表
`TRUNCATE`	清空表数据	表
`REFERENCES`	创建外键引用	表、列
`TRIGGER`	创建触发器	表
`CREATE`	创建对象	数据库、Schema
`CONNECT`	连接数据库	数据库
`EXECUTE`	执行函数/存储过程	函数、存储过程
`USAGE`	使用 Schema 或其他对象	Schema、序列
`ALL PRIVILEGES`	所有权限	所有对象