MySQL 高级(进阶) SQL 语句

MySQL 是一种功能强大的关系型数据库管理系统。为了有效地利用其高级功能，需要掌握一些进阶的 SQL 语句和技巧。本文将介绍几种常用的高级 SQL 语句，包括窗口函数、子查询、联合查询、复杂的连接操作以及事务处理等。

1. 窗口函数

窗口函数是一种高级的 SQL 功能，用于在查询结果集中计算某些聚合值，同时保留详细数据行。常用的窗口函数包括 ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG() 等。

示例

SELECT
    employee_id,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM
    employees;
​

 

 

此查询为每个部门的员工按薪资排名。

2. 子查询

子查询是嵌套在其他 SQL 语句中的查询，用于解决复杂的查询需求。子查询可以分为标量子查询、多行子查询、多列子查询和相关子查询等类型。

示例

标量子查询

SELECT
    employee_id,
    salary
FROM
    employees
WHERE
    salary > (SELECT AVG(salary) FROM employees);
​

 

 

此查询返回薪资高于平均薪资的员工。

相关子查询

SELECT
    e1.employee_id,
    e1.salary
FROM
    employees e1
WHERE
    e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department_id = e2.department_id);
​

 

 

此查询返回薪资高于其部门平均薪资的员工。

3. 联合查询

联合查询使用 UNION, UNION ALL, INTERSECT 和 EXCEPT 等操作符来合并多个查询的结果集。

示例

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    department_id = 10
UNION ALL
SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    department_id = 20;
​

 

 

此查询返回部门 ID 为 10 和 20 的所有员工。

4. 复杂连接操作

连接操作是 SQL 查询的基础，但高级的连接操作能够处理更复杂的业务需求。常见的连接类型包括内连接、左连接、右连接、全连接和自连接。

示例

内连接

SELECT
    e.employee_id,
    e.first_name,
    d.department_name
FROM
    employees e
INNER JOIN
    departments d ON e.department_id = d.department_id;
​

 

 

此查询返回所有员工及其所属部门的名称。

自连接

SELECT
    e1.employee_id AS employee,
    e2.employee_id AS manager
FROM
    employees e1
LEFT JOIN
    employees e2 ON e1.manager_id = e2.employee_id;
​

 

 

此查询返回员工及其经理的对应关系。

5. 事务处理

事务处理是保证数据库操作的原子性、一致性、隔离性和持久性（ACID 属性）的关键。MySQL 支持使用 START TRANSACTION, COMMIT 和 ROLLBACK 来控制事务。

示例

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

-- 检查条件，决定提交还是回滚
IF (condition) THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;
​

 

 

此事务示例实现了从一个账户转账到另一个账户的操作。

6. 分区表

分区表是将大表的数据分成更小的、易于管理的部分。分区可以提高查询性能和管理效率。

示例

创建分区表：

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022)
);
​

 

 

此示例根据销售日期年份对表进行分区。

分析说明表

功能	示例	说明
窗口函数	RANK() OVER (PARTITION BY ...)	按部门对员工薪资进行排名
子查询	SELECT ... FROM (SELECT ...)	查询薪资高于平均薪资的员工
联合查询	UNION ALL	合并多个部门员工的查询结果
复杂连接操作	INNER JOIN, LEFT JOIN	获取员工及其部门信息，返回员工与经理关系
事务处理	START TRANSACTION, COMMIT	实现账户之间的转账操作
分区表	PARTITION BY RANGE	根据年份对销售记录进行分区