【MySQL】排序与多表查询

一、排序与分页

1、排序

• 如果没有排序操作，数据的展示会按照插入的先后顺序来展示
• 语法结构：如果我们要按照某个字段进行排序，那么就要用到ORDER BY关键字了，例如SELECT * FROM t_test WHERE 筛选条件 ORDER BY 排序字段 排序方式;
• 排序方式：升序和降序
  • 升序：ASC(ascend，如果在排序字段后面没有显式声明排序方式，那就是默认按照升序排序)
  • 降序：DESC(descend)
• 我们可以使用列的别名进行排序，例如SELECT 字段1,字段2 as 别名 FROM t_test WHERE 筛选条件 ORDER BY 别名;。需要注意的是，别名只能在ORDER BY中使用，在WHERE条件中使用会报错
  • 原因：以上面的SQL语句为例。SQL语句的执行是先去表中查询符合条件的数据(所有字段)，然后再去筛选出我们需要哪些字段(此时别名才被声明)，然后再按照ORDER BY进行排序
• 关键字顺序：WHERE声明在FROM之后，ORDER BY之前
• 多级排序：假如我们想按照字段A升序展示数据，当字段A的值相同时按照字段B降序展示数据。那么我们要执行的SQL就是SELECT * FROM t_test WHERE 筛选条件 ORDER BY 字段A ASC,字段B DESC;

2、分页

• 使用场景：数据太多想分页展示，或者是只想展示中间某一部分的数据
• 语法格式：SELECT 字段1,字段2 FROM t_test WHERE 筛选条件 LIMIT 偏移量,条目数;
  • 偏移量：表示要从第几条数据开始查，注意第一条数据的偏移量是0
  • 条目数：要往后查多少条数据
  • 如果我们从第1条数据开始查，想要查n条数据，LIMIT 0,n等价于LIMIT n，所以直接写LIMIT n也是可以的
• 假如我们想要每页展示的数据条数是PageSize，想要展示第PageNum页的数据，那么执行的SQL就为SELECT 字段1,字段2 FROM t_test WHERE 筛选条件 LIMIT (PageNum - 1) * PageSize,PageSize;
• 同时使用排序和分页，语法顺序为WHERE 筛选条件 ORDER BY 排序字段 LIMIT 偏移量,条目数;
• MySQL8新特性：分页的语法调整为LIMIT 条目数 OFFSET 偏移量
• LIMIT只在MySQL等部分数据库中生效，SQL Server、DB2和Oracle中不生效

二、多表查询

1、对数据进行分表并使用多表查询的原因

• 减少冗余数据，有些不必要的数据就不用重复展示了，可以通过某一字段(例如外键)进行关联
• 提高IO效率，减少和数据库的交互次数
• 多并发，可以在同一时刻对不同的表进行操作，便于数据的维护

2、笛卡尔积错误

• 现象
  假设将公司的人员信息和部门信息拆成2各表，分别是t_employee和t_dept
  人员和部门通过dept_id进行关联，人员表有m条数据，部门表有n条数据
  当我们查询人员名称和部门名称时
  假设执行的SQL为SELECT employee_name,dept_name FROM t_employee,t_dept;
  那么最后查询出来的数据一共有m*n条，这显然是不对的
  这种现象就称为笛卡尔积错误

• SQL92中笛卡尔积也叫交叉连接，英文名为CROSS JOIN

• 出现笛卡尔积错误的原因：缺少了多表的连接条件

3、多表查询的正确方式：在WHERE后加入连接条件

使用连接条件后，上方的查询SQL就可以改为

SELECT employee_name,dept_name 
FROM t_employee,t_dept
WHERE t_employee.dept_id = t_dept.dept_id;

4、如果查询语句中出现了多张表中都存在的字段，必须指明从哪张表中查询该字段

从SQL优化的角度来说，在执行多表查询时，建议每一个字段都声明从哪张表中查出
例如，我们在这里多查一个部门id

SELECT t_employee.employee_name,t_dept.dept_name,t_dept.dept_id
FROM t_employee,t_dept
WHERE t_employee.dept_id = t_dept.dept_id;

5、表的别名

可以在SELECT和WHERE中使用表的别名
一旦在SELECT或WHERE中使用表的别名，那字段前面就不能再使用原表名，必须都使用别名

SELECT emp.employee_name,dept.dept_name,dept.dept_id
FROM t_employee emp,t_dept dept
WHERE emp.dept_id = dept.dept_id;

6、如果有n个表实现多表查询，则需要至少n-1个连接条件
假设新增一个位置表t_location，它和部门表通过location_id进行关联
那么如果要多查一个部门所在的城市名称，上面的SQL就可以改为

SELECT emp.employee_name,dept.dept_name,dept.dept_id,location.location_id,location.location_name
FROM t_employee emp,t_dept dept,t_location location
WHERE emp.dept_id = dept.dept_id
AND dept.location_id = location.location_id;

7、多表查询的分类

• 角度一：等值连接 VS 非等值连接
  • 等值连接：多表查询连接条件使用等号相连，例如上面的那些SQL
  • 非等值连接：多表查询连接条件不是使用等号相连，例如非等、大于、小于等
• 角度二：自连接 VS 非自连接
  • 非自连接：多表查询连接的不是同一张表，比如我们之前举例的那些SQL
  • 自连接：自己连接自己，指的是在多表查询中连接的是同一张表，也就是自我引用
    例如员工表中含有员工id、员工姓名、直接领导id
    如果要从员工表中查出员工和他直接领导的工号和姓名，就要根据直接领导id去查领导信息
    涉及到自己调用自己，执行的SQL就是

SELECT emp.employee_id,emp.employee_name,mgr.employee_id,mgr.employee_name
FROM t_employee emp,t_employee mgr 
WHERE emp.manager_id = mgr.employee_id;

• 角度三：内连接 VS 外连接
  • 内连接(inner join)：两个表在连接过程中只返回满足连接条件的行
  • 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左(或者右)表中不满足条件的行，这种称之为左(或者右)外连接
    • 左外连接：连接条件中左表称为主表，右表称为从表。查询出来的条数应该与左表中的数据条数相等。右表中那些没有办法和左表数据通过关联条件进行匹配的行，它在结果集中展示的字段都放置NULL
    • 右外连接：与左外连接相似，只不过主表换成了右表
    • 满外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左表和右表中不满足连接条件的行

8、SQL99语法实现内连接与外连接

• SQL99语法实现内连接，使用INNER JOIN...ON

SELECT e.last_name,d.depart_name
FROM t_employee e INNER JOIN t_department d 
ON e.department_id = d.department_id;

这里的INNER可以省略,如果是三张以上的表，那就继续拼接JOIN...ON

SELECT e.last_name,d.depart_name,c.city_name
FROM t_employee e JOIN t_department d 
ON e.department_id = d.department_id
JOIN t_city c
ON d.location_id = c.location_id;

• SQL99语法实现外连接

左外连接实现如下

SELECT e.last_name,d.depart_name
FROM t_employee e LEFT OUTER JOIN t_department d 
ON e.department_id = d.department_id;

左外连接实现如下，左外连接和右外连接中的OUTER可以省略

SELECT e.last_name,d.depart_name
FROM t_employee e RIGHT OUTER JOIN t_department d 
ON e.department_id = d.department_id;

满外连接实现如下，注意MySQL不支持FULL OUTER JOIN

SELECT e.last_name,d.depart_name
FROM t_employee e FULL OUTER JOIN t_department d 
ON e.department_id = d.department_id;

9、UNION的使用
利用UNION关键字，可以将多个SELECT语句的结果集组合成单个结果集
这些SELECT语句所查询的列数和数据类型必须相同，并且相互对应

SELECT column... FROM tableA
UNION [ALL]
SELECT column... FROM tableB;

• UNION：返回两个结果集的并集，去除重复数据
• UNION ALL：与UNION的作用相同，但不去除重复数据。如果明确知道不会出现重复数据或者不需要去重，那么尽量使用UNION ALL，提高效率

10、七种SQL JOIN的实现

之前说的MySQL不支持FULL OUTER JOIN，满外连接就可以通过使用UNION ALL关键字
将左上与中右进行组合得到，或者将右上和中左组合得到

11、自然连接和Using连接

• 自然连接：NATURAL JOIN，它会自动查询两张表中的所有的关联字段并进行等值连接

上面的等值连接SQL

SELECT e.last_name,d.depart_name
FROM t_employee e INNER JOIN t_department d 
ON e.department_id = d.department_id;

可以替换成

SELECT e.last_name,d.depart_name
FROM t_employee e NATURAL JOIN t_department d;

自然连接NATURAL JOIN是一定会自动查询两张表中所有相同的字段，但很多时候我们仅需要一个相同字段作为连接条件即可，使用起来不够灵活

• Using连接
  USING 连接是指定表中同名字段进行等值连接，只能配合 JOIN 一起使用

上面的等值连接SQL

SELECT e.last_name,d.depart_name
FROM t_employee e,t_department d 
WHERE e.department_id = d.department_id;

可以替换成

SELECT e.last_name,d.depart_name
FROM t_employee e JOIN t_department d 
Using (department_id);

如有错误，欢迎指正！！！