这是我参与「第五届青训营 」伴学笔记创作活动的第 8 天

三、排序

1. 默认顺序：数据添加的顺序

2. 排序：ORDER BY

• 顺序

  • 升序：ASC (ascend) 默认
  • 降序：DESC (descend)

SELECT * 

FROM employees

ORDER BY salary DESC;

• 可以使用列的别名进行排序

SELECT salary * 12 AS "annual_sal" 

FROM employees

ORDER BY annual_sal DESC;

• 要放在WHERE后面

四、分页

1. 格式：LIMIT 位置偏移量, 条目数

• 好处：减少数据表网络传输量，提升查询效率
• 每页显示pageSize条记录，此时显示第pageNo页

LIMIT (pageNo-1) * pageSize, pageSize;

LIMIT 0, 条目数 等价于 LIMIT 条目数

#每页显示20条记录，此时显示第1页

SELECT *

FROM employees

LIMIT 0,20;



#每页显示20条记录，此时显示第2页

SELECT *

FROM employees

LIMIT 20,20;

2. WHERE...ORDER BY...LIMIT声明顺序

#每页显示20条记录，此时显示第1页

SELECT *

FROM employees

WHERE department=90

ORDER BY salary DESC

LIMIT 0,20;

3. Mysql 8.0新特性LIMIT...OFFSET...

LIMIT pageSize OFFSET 位置偏移量

五、多表查询

1. 查询语法

SELECT t1.column, t2.column

FROM table1 t1, table2 t2

WHERE t1.column1 = t2.column2 #连接条件

• 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
• 建议多表查询时，每个字段前都指明其所在的表

表别名：如果起了，必须得用（WHERE，SELECT）

2. 非等值连接

SELECT e.last_name, e.salary, j.job_title

FROM employees e, jobs j

WHERE e.salary BETWEEN j.min_salary AND j.max_salary;

3. 自连接

#查询员工id，员工姓名及其管理者的id和姓名

SELECT emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name

FROM employees emp, employees mgr

WHERE emp.manager_id = mgr.employee_id;

4. 内连接 vs 外链接

• 内连接（&&）：合并具有同一列的两个以上的表的行，结果集中不包括一个表与另一个表不匹配的行

#查询所有员工last_name, department_name信息

SQL92

SELECT e.last_name,d.department_name

FROM employees e, departments d

WHERE e.`manager_id`=d.`department_id`;



SQL99

SELECT e.last_name,d.department_name,l.city

FROM employees e JOIN departments d

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

JOIN locations l

ON d.`location_id`=l.location_id;

• 外连接（||）：合并具有同一列的两个以上的表的行，结果集中除了包括一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行

  • 分类：左外连接，右外连接，满外连接

左外连接（LEFT JOIN）：两个表在连接过程中处理返回满足连接条件的行以为，还返回左表中不满足条件的行

右外连接（RIGHT JOIN）：两个表在连接过程中处理返回满足连接条件的行以为，还返回右表中不满足条件的行

满外连接（ FULL JOIN ）：mysql不支持

#查询所有员工last_name, department_name信息

SQL92 MySQL不支持SQL92语法中外连接的写法 orcal支持

SELECT e.last_name,d.department_name

FROM employees e, departments d

WHERE e.`manager_id`=d.`department_id`(+);



SQL99中使用JOIN...ON的方式实现多表查询

SELECT e.last_name,d.department_name,l.city

FROM employees e LEFT OUTER JOIN departments d #OUTER可省略

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

JOIN locations l

ON d.`location_id`=l.location_id;

5. UNION vs UNION ALL

UNION：会执行去重操作

UNION ALL：不会执行去重操作

6. 7种JOIN的实现

1. 中图：内连接

SELECT e.last_name,d.department_name

FROM employees e JOIN departments d #OUTER可省略

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

2. 左上图：左外连接

SELECT e.last_name,d.department_name

FROM employees e LEFT JOIN departments d #OUTER可省略

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

3. 右上图：右外连接

SELECT e.last_name,d.department_name

FROM employees e RIGHT JOIN departments d #OUTER可省略

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

4. 左中图：

SELECT e.last_name,d.department_name

FROM employees e JOIN departments d #OUTER可省略

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

WHERE d.`department_id` IS NULL

5. 右中图：

SELECT e.last_name,d.department_name

FROM employees e JOIN departments d #OUTER可省略

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

WHERE e.`manager_id` IS NULL

6. 左下图：满外连接

方式1： 左上 UNION ALL 右中

SELECT e.last_name,d.department_name

FROM employees e LEFT JOIN departments d #OUTER可省略

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

UNION ALL

SELECT e.last_name,d.department_name

FROM employees e JOIN departments d #OUTER可省略

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

WHERE e.`manager_id` IS NULL

7. 右下图：

方式1： 左中 UNION ALL 右中

SELECT e.last_name,d.department_name

FROM employees e JOIN departments d #OUTER可省略

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

WHERE d.`department_id` IS NULL

UNION ALL

SELECT e.last_name,d.department_name

FROM employees e JOIN departments d #OUTER可省略

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

WHERE e.`manager_id` IS NULL

8. SQL99新特性

NATURAL JOIN：自动查询两张连接表中所有相同的字段，然后进行等值连接

SELECT e.last_name,d.department_name

FROM employees e NATURAL JOIN departments d; #OUTER可省略

USING：适用于两个表中同名字段

SELECT e.last_name,d.department_name

FROM employees e JOIN departments d #OUTER可省略

USING (department_id);