SQL语句排序、分页、多表查询

一、排序

1.1 排序规则

使用 ORDER BY 子句排序

ASC（ascend）: 升序

DESC（descend）:降序

ORDER BY 子句在SELECT语句的结尾。

1.2 单列排序

SELECT last_name, job_id, department_id, hire_date

FROM employees

ORDER BY hire_date ;

若没有指定排序方式，则默认按照升序排序

1.3 多列排序

SELECT last_name, department_id, salary

FROM employees

ORDER BY department_id, salary DESC;

二、分页

MySQL中使用 LIMIT 实现分页

格式：

LIMIT [位置偏移量,] 行数第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移

量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是

1，以此类推）；第二个参数“行数”指示返回的记录条数。

公式：每页显示size条记录，此时显示第page页

LIMIT（page - 1）*size，size

2. MySQL8.0新特性：LIMIT 条目数 OFFSET 位置偏移量

3. 作用：减少传输量，提升查询效率

三、多表查询

代码实现

#中图：内连接 A∩B

SELECT employee_id,last_name,department_name

FROM employees e JOIN departments d

ON e.`department_id` = d.`department_id`;

#左上图：左外连接

SELECT employee_id,last_name,department_name

FROM employees e LEFT JOIN departments d

ON e.`department_id` = d.`department_id`;

#右上图：右外连接

SELECT employee_id,last_name,department_name

FROM employees e RIGHT JOIN departments d

ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B

SELECT employee_id,last_name,department_name

FROM employees e LEFT JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE d.`department_id` IS NULL

#右中图：B-A∩B

SELECT employee_id,last_name,department_name

FROM employees e RIGHT JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE e.`department_id` IS NULL#左下图：满外连接

# 左中图 + 右上图 A∪B

SELECT employee_id,last_name,department_name

FROM employees e LEFT JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE d.`department_id` IS NULL

UNION ALL #没有去重操作，效率高

SELECT employee_id,last_name,department_name

FROM employees e RIGHT JOIN departments d

ON e.`department_id` = d.`department_id`;

#右下图

#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）

SELECT employee_id,last_name,department_name

FROM employees e LEFT JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE d.`department_id` IS NULL

UNION ALL

SELECT employee_id,last_name,department_name

FROM employees e RIGHT JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE e.`department_id` IS NULL