3、排序（order by）与分页（limit）多表查询 -mysql
3、排序与分页&多表查询 -mysql
- 排序与分页
- 一、排序 Order By
- 二、分页 Limit
- 多表查询
- 一、一个案例引发的多表连接
- 2、笛卡尔积（或交叉连接）的理解
- 二、多表查询分类讲解
- 1、等值连接 vs 非等值连接
- 2、自连接 vs 非自连接
- 3、内连接 vs 外连接
- 三、SQL99语法实现多表查询
- 2、内连接(INNER JOIN)的实现
- 3、外连接(OUTER JOIN)的实现
排序与分页
——————————————————————————————————————————————————————
一、排序 Order By
1、排序数据
排序规则
- 使用
ORDER BY子句排序ASC（ascend）: 升序DESC（descend）:降序
ORDER BY子句在SELECT语句的结尾。
2、单列排序
-
如果不指明排序规则，则默认为
ASC升序SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ; #默认为 asc 升序SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC ;
3、多列排序
-
若有多个排序规则，则从左到右依次书写
-
从左到右，优先级别依次降低
SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC; # 上面这条sql的意思实际上是：先按照department_id升序排序，如果department_id相等，再按照salary降序
4、自定义排序（使用"FIELD()"函数）
参考地址：https://blog.csdn.net/u011447905/article/details/80013987
二、分页 Limit
1、背景
- 返回的记录太多了
- 减少网络传输压力
2、实现规则
-
格式
LIMIT [位置偏移量,] 行数 -
举例
#前10条记录： SELECT * FROM 表名 LIMIT 0,10; #或者 SELECT * FROM 表名 LIMIT 10; #第11至20条记录： SELECT * FROM 表名 LIMIT 10,10; #第21至30条记录： SELECT * FROM 表名 LIMIT 20,10;MySQL 8.0 中可以使用“
LIMIT 3 OFFSET 4”
意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。 -
分页显式公式
（当前页数-1）* 每页条数，每页条数
SELECT * FROM table LIMIT (PageNo - 1) * PageSize,PageSize; -
LIMIT 子句必须放在整个SELECT语句的最后！
-
好处
约束返回结果的数量可以 减少数据表的网络传输量 ，也可以 提升查询效率 。
如果我们知道返回结果只有1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。
这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
3、拓展
在不同的 DBMS 中使用的关键字可能不同。
在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字，而且需要放到 SELECT 语句的最后面。
-
SQL Server和Access，需要使用TOP关键字SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC -
DB2，使用FETCH FIRST 5 ROWS ONLY关键字SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY -
Oracle，基于ROWNUM来统计行数SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC; #先过滤rownum < 5，排序这条语句是先取出来前 5 条数据行，然后再按照 hp_max 从高到低的顺序进行排序。但这样产生的结果和上述方法的并不一样。
我会在后面讲到子查询，你可以使用
SELECT rownum, last_name,salary FROM (SELECT last_name,salaryFROM employeesORDER BY salary DESC) WHERE rownum < 10; #根据子查询查询排序好的结果中，再过滤rownum < 10
得到与上述方法一致的结果。
4、课后练习
# 1、查询员工的的姓名和部门号和年薪，按年薪降序，按姓名升序显示
select last_name,department_id,salary * 12 annual_salary from employees order by annual_salary desc,last_name asc;# 2、选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据
select last_name,salary from employees where salary not between 8000 and 17000 order by salary desc limit 20,20;# 3、查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
select employee_id,last_name,email,department_id from employees
# where email like '%e%'
where email regexp '[e]'
order by length(email) desc,department_id asc;
多表查询
——————————————————————————————————————————————————
多表查询，也称为关联查询，指 两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。
比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
一、一个案例引发的多表连接
1、案例说明
#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;
上述多表查询中出现的问题称为：笛卡尔积的错误。
2、笛卡尔积（或交叉连接）的理解
笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能
组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。
组合的个数即为两个集合中元素个数的乘积数。
SQL92中，笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。
在 SQL99 中也是使用 CROSS JOIN表示交叉连接。
它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：
#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;3、案例分析与问题解决
-
笛卡尔积的错误会在下面条件下产生：
- 省略多个表的连接条件（或关联条件）
- 连接条件（或关联条件）无效
- 所有表中的所有行互相连接
-
为了避免笛卡尔积， 可以在
WHERE 加入有效的连接条件。 -
加入连接条件后，查询语法：
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; #连接条件- 在
WHERE子句中写入连接条件。
- 在
-
正确写法：
#案例：查询员工的姓名及其部门名称 SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id; -
在表中有相同列时，在列名之前加上表名前缀。
二、多表查询分类讲解
1、等值连接 vs 非等值连接
-
等值连接
表与表之间有字段相关联
连接 n个表,至少需要n-1个连接条件
-
非等值连接
表与表之间没有明确的关联条件，是根据一个表中的字段的，在另一个表中一个范围的情况
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
#e.salary字段在j.lowest_sal中是一个范围情况2、自连接 vs 非自连接
-
自连接
同一张表，自己连接自己
当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。
然后两个表再进行内连接，外连接等查询。
#查询employees表，返回“Xxx works for Xxx”
SELECT CONCAT(worker.last_name ,' works for ', manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
3、内连接 vs 外连接
- 内连接: 合并具有同一列的两个以上的表的行,
结果集中不包含一个表与另一个表不匹配的行 - 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右）
外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。 - 如果是左外连接，则连接条件中左边的表也称为
主表，右边的表称为从表。 - 如果是右外连接，则连接条件中右边的表也称为
主表，左边的表称为从表。
SQL92：使用(+)创建连接
- 在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
- Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;# (+)在谁那里就是相反的连接，代表谁补null
# 如果在左边就是右外连接
# 如果在右边就是左外连接- 而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。
三、SQL99语法实现多表查询
1、基本语法
-
使用JOIN…ON子句创建连接的语法结构：
SELECT table1.column, table2.column,table3.column FROM table1 JOIN table2 ON table1 和 table2 的连接条件 JOIN table3 ON table2 和 table3 的连接条件 -
SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。
-
语法说明：
- 可以使用 ON 子句指定额外的连接条件。
- 这个连接条件是与其它条件分开的。
- ON 子句使语句具有更高的易读性。
- 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
2、内连接(INNER JOIN)的实现
-
语法
SELECT 字段列表 FROM A表 INNER JOIN B表 ON 关联条件 WHERE 等其他子句; -
题目1
SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id FROM employees e JOIN departments d ON e.department_id = d.department_id;
-
题目2
SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id;
3、外连接(OUTER JOIN)的实现
以一张表为标准，其他表做辅助，如果没有则，辅助表用null填充
-
左外连接(LEFT OUTER JOIN)
- 语法
#实现查询结果是A SELECT 字段列表 FROM A表 LEFT JOIN B表 ON 关联条件 WHERE 等其他子句;- 举例
SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id;
-
右外连接(RIGHT OUTER JOIN)
-
语法
#实现查询结果是B SELECT 字段列表 FROM A表 RIGHT JOIN B表 ON 关联条件 WHERE 等其他子句; -
举例
SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ;
-
需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，
只能用 (+) 表示。
- 满外连接(FULL OUTER JOIN)
- 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
- SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
- 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNIONRIGHT join代替。
上一篇：如何划分诗句节奏？