SQL, Structured Query Language（结构化查询语言）

SQL 是用于管理和操作关系型数据库的标准语言。它让您能够存储、查询、更新和删除数据。

类别	全称	用途	常用命令
DDL	数据定义语言	定义数据库结构	`CREATE`, `ALTER`, `DROP`
DML	数据操作语言	操作数据	`SELECT`, `INSERT`, `UPDATE`, `DELETE`
DCL	数据控制语言	权限管理	`GRANT`, `REVOKE`
TCL	事务控制语言	事务管理	`COMMIT`, `ROLLBACK`

DDL, Data Definition Language（数据定义语言）

sql

-- database
> CREATE DATABASE <db>;

-- table
> CREATE TABLE <tb> (
>  FIELD TYPE,
> [FIELD TYPE,...]
> );

> ALTER TABLE <tb> RENAME <ntb>;

-- view （虚拟表，不存储数据）
> CREATE VIEW <v_name> 
>     AS 
>     SELECT * 
>         FROM <tb> 
>         WHERE <condition>
> ;

-- PROCEDURE（预编译 SQL 语句集合。复用和封装复杂逻辑，可传递参数）
-- IN 入参
-- OUT 出参
-- INOUT 出入参
> CREATE PROCEDURE PROC_FUNC(IN param INT)
> BEGIN
> ...
> END;

INDEX（索引）

sql

> CREATE INDEX <idx_name> ON <tb>(field_name);
-- 组合索引
> CREATE INDEX <idx_name> ON <tb>([field_name...]);
-- 全文索引：适合处理大段文字的查询
> CREATE FULLTEXT INDEX <idx_name> ON <tb>(field_name);
-- 删除索引
> DROP INDEX <idx_name> ON <tb>;

CONSTRAINT（约束）

sql

-- CONSTRAINT - PRIMARY KEY
> ALTER TABLE <tb> ADD CONSTRAINT PRIMARY KEY(field,[field...]);
> ALTER TABLE <tb> DROP CONSTRAINT PRIMARY KEY;

-- CONSTRAINT - UNIQUE
> ALTER TABLE <tb> ADD CONSTRAINT <uq_field> UNIQUE(<field>);
> ALTER TABLE <tb> DROP CONSTRAINT <uq_field>;

-- CONSTRAINT - FOREIGN KEY
> ALTER TABLE <tb> ADD CONSTRAINT <fk_field> FOREIGN KEY(<field>) REFERENCES <tb>.(<field>);
> ALTER TABLE <tb> DROP CONSTRAINT <fk_field>;

-- CONSTRAINT - CHECK
> ALTER TABLE <tb> ADD CONSTRAINT <chk_field> CHECK(field > 0 AND field <= 1000);
> ALTER TABLE <tb> DROP CONSTRAINT <chk_field>;

-- 清空数据
> TRUNCATE TABLE <tb>;

三范式

范式	名称	要求	解决的问题	说明
1NF	原子性	列不可再分	重复组、多值属性	每列都是不可再分的最小数据单元，每行每列只有一个值。
2NF	完全依赖	完全依赖主键	部分依赖（复合主键时）	在满足 1NF 基础上，非主键列必须完全依赖于整个主键（针对复合主键）。
3NF	直接依赖	消除传递依赖	传递依赖、冗余存储	在满足 2NF 基础上，非主键列必须直接依赖于主键，不能通过其他非主键列传递依赖。

反规范化

三范式并非绝对！实际项目中常有意反规范化以提升性能。

场景	做法	收益	代价
高频查询的关联表	冗余存储常用字段	减少 JOIN，加速查询	增加存储，需维护一致性
报表/数据仓库	宽表设计，大量冗余	分析查询极快	数据更新复杂
缓存场景	存储计算好的聚合值	实时读取	需定时更新或触发更新

例子：电商

sql

-- 创建数据库
> CREATE DATABASE ecommerce CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
> USE ecommerce;

-- 用户表
> CREATE TABLE users (
>    user_id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>    username    VARCHAR(50) NOT NULL UNIQUE,
>    email       VARCHAR(100) NOT NULL UNIQUE,
>    password    VARCHAR(255) NOT NULL,
>    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
>    status      TINYINT DEFAULT 1 CHECK (status IN (0,1))
> ) ENGINE=InnoDB;

-- 商品分类表
> CREATE TABLE categories (
>    cat_id      INT AUTO_INCREMENT PRIMARY KEY,
>    name        VARCHAR(100) NOT NULL,
>    parent_id   INT NULL,
>    FOREIGN KEY (parent_id) REFERENCES categories(cat_id) ON DELETE SET NULL
> ) ENGINE=InnoDB;

-- 商品表
> CREATE TABLE products (
>    product_id  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>    cat_id      INT NOT NULL,
>    name        VARCHAR(255) NOT NULL,
>    price       DECIMAL(10,2) NOT NULL CHECK (price >= 0),
>    stock       INT UNSIGNED DEFAULT 0,
>    FOREIGN KEY (cat_id) REFERENCES categories(cat_id) ON DELETE RESTRICT
> ) ENGINE=InnoDB;

-- 订单表
> CREATE TABLE orders (
>    order_id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>    user_id     BIGINT UNSIGNED NOT NULL,
>    total       DECIMAL(12,2) NOT NULL,
>    status      ENUM('pending','paid','shipped','completed','cancelled') DEFAULT 'pending',
>    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
>    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
> ) ENGINE=InnoDB;

-- 订单详情表
> CREATE TABLE order_items (
>    item_id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>    order_id    BIGINT UNSIGNED NOT NULL,
>    product_id  BIGINT UNSIGNED NOT NULL,
>    quantity    INT UNSIGNED NOT NULL,
>    price       DECIMAL(10,2) NOT NULL,  -- 下单时的价格
>    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
>    FOREIGN KEY (product_id) REFERENCES products(product_id)
> ) ENGINE=InnoDB;

-- 创建索引优化查询
> CREATE INDEX idx_orders_user ON orders(user_id, created_at);
> CREATE INDEX idx_products_cat ON products(cat_id, price);

DML, Data Manipulation Language（数据操作语言）

sql

-- select
> SELECT <field>[,field...], AVG
>     FROM <tb> 
>     WHERE <condition> 
>     GROUP BY <field>[,field...] 
>     HAVING <condition> -- 聚合过滤或引用 SELECT 字段作为条件
>     ORDER BY <field>[, field...]
>     LIMIT <n>,<m>
> ;

-- INSERT
> INSERT INTO <tb> (<field>[,field...])
>     VALUES
>         (...)
>         [,(...)]
> ;

-- UPDATE
> UPDATE <tb>
>     SET field=value[,field=value...]
>     WHERE <condition>
> ;
-- 联表更新
> UPDATE employees e
>     SET salary = salary * 1.1
>     WHERE EXISTS (
>     SELECT 1 FROM departments d 
>     WHERE d.id = e.dept_id AND d.name = '销售部'
> );

-- DELETE
> DELETE 
>     FROM <tb>
>     WHERE <condition>
> ;
-- 链表删除
> DELETE FROM orders 
> WHERE customer_id IN (
>     SELECT id FROM customers WHERE status = 'inactive'
> );

SELECT 子句执行顺序

执行步骤	子句	核心作用
1	`FROM`	确定数据来源表
2	`JOIN`	关联其他表，生成笛卡尔积后过滤
3	`WHERE`	过滤行（不能使用聚合函数）
4	`GROUP BY`	按列分组，将多行合并为组
5	Aggregation Operation	计算 `COUNT/SUM/AVG/MAX/MIN`
6	`HAVING`	过滤组（可以使用聚合函数）
7	`SELECT`	选择最终输出的列
8	`DISTINCT`	去重
9	`ORDER BY`	排序（可以使用 SELECT 别名）
10	`LIMIT/OFFSET`	限制返回行数
	窗口函数	`ROW_NUMBER()` 等在 `SELECT` 阶段执行，但逻辑上在 `ORDER BY` 之前
	UNION	先执行左右查询，再去重合并（`UNION ALL` 不去重更快）
	子查询	相关子查询（慢）、无关子查询（快）

sql

-- 无关子查询
-- 查询工资高于公司平均工资的员工
> SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);  -- 子查询只执行1次

-- 相关子查询
-- 查询每个部门工资高于该部门平均工资的员工
> SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary) 
    FROM employees e2 
    WHERE e2.department_id = e1.department_id  -- 外层查询一次内层也需要进行一次
);

窗口函数

窗口函数也称为，分析函数，"用 SQL + 窗口函数做 OLAP 分析" 成为轻量级场景的标准做法。另外，窗口函数无法直接实现：多维度切片钻取，需要配合 GROUP BY 或专门的多维引擎。例如： "查看 2023年 → 华东区 → 电子产品 → 各月销售额" 并在这些维度间任意滚动。

函数类别	函数	说明	OLAP 用途
聚合窗口	`SUM()`, `AVG()`		移动平均、累计求和
排名窗口	`ROW_NUMBER()`	每行唯一编号，即使值相同也会递增。	Top-N 分析、排行榜
排名窗口	`RANK()`	相同值排名相同，但会跳号。	递增排序
排名窗口	`DENSE_RANK()`	相同值排名相同，下一个名次不跳号。	自然排序
取值窗口	`LAG()`, `LEAD()`, `FIRST_VALUE()`		同比环比、趋势分析
统计窗口	`NTILE()`, `PERCENT_RANK()`		分位数分析、ABC分类

sql

-- 排名与分页
> SELECT 
>     name,
>     salary,
>     -- 递增排序
>     RANK() OVER (ORDER BY salary DESC) as rank,
>     -- 自然排序（同值重复序号）
>     DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank,
>     ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
> FROM employees;

-- 累计计算
> SELECT 
>     month,
>     sales,
>     SUM(sales) OVER (ORDER BY month) as running_total,
>     AVG(sales) OVER (ORDER BY month ROWS 2 PRECEDING) as moving_avg
> FROM monthly_sales;

CTE, Common Table Expressions（公用表表达式）

替代子查询

sql

> WITH cte_name AS (
>     -- CTE 查询定义
>     SELECT ...
> )
> SELECT * FROM cte_name;

递归

sql

WITH RECURSIVE org_hierarchy AS (
>     -- 锚点成员：从 CEO 开始（无上级）
>     SELECT id, name, manager_id, 0 as level, 
>            CAST(name AS VARCHAR(100)) as path
>     FROM employees 
>     WHERE manager_id IS NULL
>     
>     UNION ALL
>     
>     -- 递归成员：找到下属
>     SELECT e.id, e.name, e.manager_id, oh.level + 1,
>            CONCAT(oh.path, ' > ', e.name)
>     FROM employees e
>     INNER JOIN org_hierarchy oh ON e.manager_id = oh.id
> )
> SELECT 
>     REPEAT('  ', level) || name as org_chart,
>     level,
>     path
> FROM org_hierarchy
> ORDER BY path;

生成序列

sql

> WITH RECURSIVE numbers AS (
>     SELECT 1 as n
>     UNION ALL
>     SELECT n + 1 FROM numbers WHERE n < 100
> )
> SELECT * FROM numbers;

分页优化

sql

> WITH paginated_data AS (
>     SELECT 
>         id, name, created_at,
>         ROW_NUMBER() OVER (ORDER BY created_at DESC) as row_num
>     FROM large_table
>     WHERE status = 'active'
> )
> SELECT * FROM paginated_data
> WHERE row_num BETWEEN 1001 AND 1100;  -- 第11页，每页100条

组合-销售分析报表

sql

> WITH 
-- 月度销售汇总
> monthly_sales AS (
>     SELECT 
>         DATE_TRUNC('month', order_date) as month,
>         region,
>         SUM(amount) as total_sales,
>         COUNT(*) as order_count
>     FROM orders
>     WHERE order_date >= '2024-01-01'
>     GROUP BY 1, 2
> ),
> 
-- 计算同比增长
> yoy_growth AS (
>     SELECT 
>         month,
>         region,
>         total_sales,
>         LAG(total_sales) OVER (PARTITION BY region ORDER BY month) as prev_year_sales,
>         ROUND(
>             (total_sales - LAG(total_sales) OVER (PARTITION BY region ORDER BY month)) 
>             / NULLIF(LAG(total_sales) OVER (PARTITION BY region ORDER BY month), 0) * 100,
>             2
>         ) as growth_rate
>     FROM monthly_sales
> ),
> 
-- 区域排名
> regional_rank AS (
>     SELECT 
>         month,
>         region,
>         total_sales,
>         RANK() OVER (PARTITION BY month ORDER BY total_sales DESC) as rank
>     FROM monthly_sales
> )

> SELECT 
>     y.month,
>     y.region,
>     y.total_sales,
>     y.growth_rate || '%' as yoy_growth,
>     r.rank as regional_rank
> FROM yoy_growth y
> JOIN regional_rank r 
>     ON y.month = r.month AND y.region = r.region
> WHERE y.month = '2024-03-01'
> ORDER BY y.total_sales DESC;

DCL, Data Control Language（数据控制语言）

控制数据库的访问权限和安全性，包括用户权限的授予和撤销。

sql

-- 授于对象维度：
-- *.*                 -- 全局权限（所有数据库所有表）
-- database.*          -- 数据库级别权限
-- database.table      -- 表级别权限
-- database.table(col) -- 列级别权限

-- 授予用户查询权限
> GRANT SELECT ON employees TO 'zhangsan'@'localhost';

-- 授予多个权限
> GRANT SELECT, INSERT, UPDATE ON sales.* TO 'manager'@'%';

-- 授予所有权限，并允许该用户再授权给他人
> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 撤销用户的更新权限
> REVOKE UPDATE ON employees FROM 'zhangsan'@'localhost';

-- 撤销所有权限
> REVOKE ALL PRIVILEGES ON sales.* FROM 'manager'@'%';

-- 创建用户
> CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 修改密码
> ALTER USER 'username'@'host' IDENTIFIED BY 'newpassword';

-- 删除用户
> DROP USER 'username'@'host';

TCL, Transaction Control Language（事务控制语言）

用于管理事务，确保数据库操作的完整性和一致性。

sql

-- 或 START TRANSACTION
> BEGIN TRANSACTION;  
> UPDATE accounts SET balance = balance - 100 WHERE id = 1;
> UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 提交，更改永久生效
> COMMIT; 

> BEGIN TRANSACTION;
> DELETE FROM orders WHERE order_date < '2023-01-01';
-- 撤销删除操作，数据恢复原状
> ROLLBACK;

> BEGIN TRANSACTION;
> UPDATE inventory SET stock = stock - 10 WHERE product_id = 101;
-- 设置保存点
> SAVEPOINT sp1;
> UPDATE inventory SET stock = stock - 5 WHERE product_id = 102;
-- 只回滚到保存点，保留第一个 UPDATE
> ROLLBACK TO SAVEPOINT sp1;
-- 只提交 product_id=101 的更新
> COMMIT; 

-- 设置事务隔离级别为 SERIALIZABLE
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 或设置只读事务
> SET TRANSACTION READ ONLY;

悲观锁 (Pessimistic Locking)

"先取锁，后访问" — 假设冲突一定会发生，所以在操作数据之前先加锁，确保独占访问。

适用场景：

写操作频繁（高冲突场景）
强一致性要求（金融转账、库存扣减）
临界区执行时间较长

类型	说明	数据库示例
共享锁 (S锁/读锁)	允许多个事务同时读取	`SELECT ... LOCK IN SHARE MODE`
排他锁 (X锁/写锁)	独占访问，阻塞其他读写	`SELECT ... FOR UPDATE`

sql

-- 开启事务
> BEGIN;
-- 查询并加排他锁
> SELECT * FROM account WHERE id = 1 FOR UPDATE;
-- 执行业务逻辑...
> UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 提交事务，释放锁
COMMIT;

乐观锁 (Optimistic Locking)

"先执行，后校验" — 假设冲突很少发生，不加锁直接操作，提交时检查数据是否被修改。

适用场景：

读多写少（低冲突场景）
需要高吞吐量
可以接受失败重试

sql

-- 版本号机制 (Version)
> CREATE TABLE product (
>     id INT PRIMARY KEY,
>     name VARCHAR(50),
>     stock INT,
>     version INT DEFAULT 0  -- 版本号
> ); 

-- 更新逻辑（原子性操作）
> UPDATE product 
> SET stock = stock - 1, version = version + 1 
> WHERE id = 1 AND version = 5;  -- 假设当前版本是5

SQL, Structured Query Language（结构化查询语言） ​

DDL, Data Definition Language（数据定义语言） ​

三范式 ​

DML, Data Manipulation Language（数据操作语言） ​

SELECT 子句执行顺序 ​

窗口函数 ​

CTE, Common Table Expressions（公用表表达式） ​

DCL, Data Control Language（数据控制语言） ​

TCL, Transaction Control Language（事务控制语言） ​

悲观锁 (Pessimistic Locking) ​

乐观锁 (Optimistic Locking) ​