排序 ORDER BY

排序语句，将要显示的数据按照自己要求的规则进行排序。

将 SELECT 出来的数据，按照指定的列值进行排序，ASC 为从小到大，DESC 为从大到小，如果不写排序方式，则默认为 ASC，从小到大。

ASC全称ascend ，为升序的意思，即从小到大。

DESC全程descend，为降序的意思，即从大到小。

1
2
select * from "table"
order by column1 ASC/DESC, column2 ASC/DESC;

如果 ORDER BY 后面有多个列，则满足 column1 的排序规则后，对于 column1 相等的列则进行 column2 的排序。

排序依据

对于 ORDER BY 后面跟着 FROM 表里面的 column 的，就可以直接找到对应的数据，作为排序依据。

如果利用关系子查询返回的内容作为排序依据，那么则会执行子查询，返回结果作为本行的排序依据。

分页 LIMIT

用来只显示一段指定的查询出来的结果，只能放在语句末尾。

基本用法

LIMIT [位置偏移量,] 行数

  • 第一个 “位置偏移量” 参数指示 MySQL 从哪一行开始显示，是一个可选参数，如果不指定 “位置偏移量” ，将会从表中的第一条记录开始（第一条记录的位置偏移量是 0，第二条记录的位置偏移量是 1，以此类推）；第二个参数 “行数” 指示返回的记录条数。
1
2
3
4
5
--前10条记录：
SELECT * FROM 表名 LIMIT 10;

--第21至30条记录： 
SELECT * FROM 表名 LIMIT 20,10;

优势

约束返回结果的数量可以 减少数据表的网络传输量，也可以 提升查询效率 。如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

多表查询概念

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段。

通过多表查询可以从多个表得到对应的完整数据，比如：

1
SELECT table1.a table2.b FROM table1, table2。

举个栗子：

我们有员工表和部门表，我们想要知道所有员工的部门信息（员工对部门为 多对一 关系），就需要多表查询。

1
2
3
SELECT employees.last_name, departments.department_name -- 要查询的信息为两个表的列
FROM employees, departments
WHERE employees.department_id = departments.department_id; -- 两个表的连接条件

多表查询-表名前缀

因为是多表查询，所以可能会涉及到两个表有字段名字相同，所以可以在表中有相同列时，在列名之前加上表名前缀来区分。

1
SELECT table1.a table2.b FROM table1, table2。

多表查询-分类

等值连接 和 非等值连接

是看两个表的连接条件是否为 相等。

  • 等值连接：连接条件为字段的值相等。

  • 非等值连接：连接条件为非相等符号，一般来说是一个区间范围。

比如，工资等级表 和 员工工资 进行连接，那就是 员工工资 满足 工资等级表 的两个值之间的范围就可以连接起来：

1
2
3
4
SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_salary AND j.highest_salary; 
-- 连接条件为一个区间，而不是等号。

自连接 和 非自连接

  • 非自连接：就是这里的多表查询，利用了表之间的 N 对 N 关系（除了自我引用以外的所有关系）。

  • 自连接：单表查询，但是连接的对象是一张表里面的两个字段。利用了这张表的自我引用的关系。

比如，一张表里面有所有的人信息，我们想要通过每个人的 manager_id 和 其他人的 employee_id 进行连接，从而获取每个人的上司的名字，整个过程是一个表的自连接：

1
2
3
4
SELECT concat(worker.last_name ,' works for ' 
       , manager.last_name)
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;

内连接 和 外连接

  • 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行，也就是不满足 WHERE 条件的其他所有行数据，都不会出现在结果中。

  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

  • 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。

  • 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

内连接可用 WHERE 直接连接，或者 Inner Join ON 连接。

外连接用 Left/Right Join On 进行。

多表查询-笛卡尔积错误

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

如果在多表查询中，出现了部分行或者全部行的数据进行了笛卡尔积组合，那么就说明这个多表查询出现了连接错误。可能原因：

  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接

比如：

1
2
SELECT employees.last_name, departments.department_name
FROM employees, departments

查询结果为两个表所有行数据组合，数量为两个表的行数乘积。

如果加上连接条件，结果就是 多对一 中多的那个行数了：

1
2
3
SELECT employees.last_name, departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

连接条件是用来消除不符合要求的笛卡尔积组合的，如果连接条件没有设好，那么就会出现不符合要求的笛卡尔积组合。

多表查询实现（99 标准）

1
2
3
4
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

多个表之间的连接查询，只需要 table 1 join table2 之后，再将结果 join table3 就实现了三个表的连接：

1
2
3
4
SELECT table1.column, table2.column,table3.column
FROM table1
    JOIN table2 ON table1 和 table2 的连接条件
        JOIN table3 ON table2 和 table3 的连接条件

内查询

1
2
3
SELECT employees.last_name, departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

或者：

1
2
3
SELECT employees.last_name, departments.department_name
FROM employees INNER JOIN  departments 
ON employees.department_id = departments.department_id;

外查询

假设我们要查询 employees A departments B 两个表的 employee_id last_name department_name 数据。

下面是对上图的一个实现：

  1. 中图：内连接 A∩B
1
2
3
SELECT A.employee_id, A.last_name, B.department_name 
FROM employees A INNER JOIN departments B
ON A.department_id = B.department_id;
  1. 左上图：左外连接
1
2
3
SELECT A.employee_id, A.last_name, B.department_name
FROM employees A LEFT JOIN departments B 
ON A.department_id = B.department_id;
  1. 右上图：右外连接
1
2
3
SELECT A.employee_id, A.last_name, B.department_name
FROM employees A RIGHT JOIN departments B 
ON A.department_id = B.department_id;
  1. 左中图：A - A∩B
1
2
3
4
5
SELECT A.employee_id, A.last_name, B.department_name
FROM employees A LEFT JOIN departments B 
ON A.department_id = B.department_id
WHERE B.department_name IS NULL; 
-- A 中没有匹配到 B 中的那一部分，department_name 自然为 NULL
  1. 右中图：B-A∩B
1
2
3
4
5
SELECT A.employee_id, A.last_name, B.department_name
FROM employees A RIGHT JOIN departments B 
ON A.department_id = B.department_id
WHERE A.last_name IS NULL; 
-- B 中没有匹配到 A 中的那一部分，last_name 自然为 NULL
  1. 左下图：满外连接
1
2
3
4
5
6
7
8
9
-- 左上+右中
SELECT A.employee_id, A.last_name, B.department_name
FROM employees A LEFT JOIN departments B 
ON A.department_id = B.department_id
UNION ALL -- 不需要去重
SELECT A.employee_id, A.last_name, B.department_name
FROM employees A RIGHT JOIN departments B 
ON A.department_id = B.department_id
WHERE A.last_name IS NULL;
  1. 右下图
1
2
3
4
5
6
7
8
9
10
-- 左中+右中
SELECT A.employee_id, A.last_name, B.department_name
FROM employees A LEFT JOIN departments B 
ON A.department_id = B.department_id
WHERE B.department_name IS NULL
UNION ALL -- 不需要去重
SELECT A.employee_id, A.last_name, B.department_name
FROM employees A RIGHT JOIN departments B 
ON A.department_id = B.department_id
WHERE A.last_name IS NULL;

多表查询练习

要首先判断目标为某个表 or 两个表的全体成员还是 两个表的有链接成员。（及判断是否需要一部分为 NULL 的数据），从而确定是 OUTER JOIN 还是 INNER JOIN。

员工表：employees；
部门表：departments；
位置表：locations；

  • 显示 所有 员工的姓名，部门号和部门名称。
1
2
3
4
5
-- 因为是 所有 员工，所以要覆盖员工所有数据，即使没有连接在部门的那部分。
SELECT emp.last_name, emp.department_id, dep.department_name
FROM employees AS emp 
LEFT JOIN departments AS dep
ON emp.department_id = dep.department_id;
  • 选择所有有奖金的员工的 last_name , department_name , location_id , city 
1
2
3
4
5
6
7
8
-- 因为还是 所有 员工，所以两次 join 都要保留 emp 表 数据。
SELECT emp.last_name, dep.department_name, dep.location_id, loc.city
FROM employees AS emp
LEFT JOIN departments AS dep
ON emp.department_id = dep.department_id
LEFT JOIN locations AS loc
ON dep.location_id = loc.location_id
WHERE emp.commission_pct IS NOT NULL;
  • 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号（都在 employees 里），结果类似于下面的格式
1
2
employees   Emp   manager   Mgr 
 kochhar    101    king     100

多表查询中的自连接查询，也就是建立连接的是一张表内的两个不同但有关系的字段（员工 id 和它的上司 id）。

1
2
3
4
SELECT emp.last_name AS `employees`, emp.employee_id AS Emp, man.last_name AS `manager`, man.employee_id AS Mgr
FROM employees AS emp -- 一张表叫两个名字，方便区分。
LEFT JOIN employees AS man -- 因为可能有员工没有上司，所以要 LEFT JOIN
ON emp.manager_id = man.employee_id;
  • 查询哪个城市没有部门

因为是看那个城市没有部门，所以关注的是 城市 中，无法和部门建立连接的数据，locations 放在 left join 的左边，显示所有城市数据（尤其是连接后 部门相关信息为 null 的）。

1
2
3
4
5
SELECT loc.city
FROM locations AS loc
LEFT JOIN departments AS dep
ON loc.location_id = dep.location_id
WHERE dep.department_id IS NULL;