MySQL4（多表查询）

排序与分页

如果没有使用排序操作，默认情况下查询返回得数据是按照添加数据的顺序显示的。

单列排序

举例：按照salary从高到低排序（没有指明默认升序）

ASC（ascend）: 升序
DESC（descend）:降序

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;

！！！WHERE和ORDER BY 同时出现，先用WHERE

二级排序

练习：显示员工信息，按照id降序排序，salary升序。

SELECT employee_id ,salary,department_id
FROM employees
ORDER BY department_id DESC ,salary ASC;

分页

语法格式：

LIMIT [位置偏移量,] 行数#偏移量为所在行数减一

#需求，每一页显示20条记录
SELECT employee_id ,last_name
FROM employees
LIMIT 0,20;#从第一行开始显示20行

！！！注意：LIMIT 子句必须放在整个SELECT语句的最后！

排序分页练习

1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示

SELECT last_name,department_id,salary*12 "year salary"
FROM employees
ORDER BY salary DESC ,last_name ASC

2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据

SELECT last_name,salary
FROM employees
WHERE salary <8000 OR salary >17000
ORDER BY salary DESC 
LIMIT 20,20;

3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT last_name ,email,department_id
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC ,department_id ASC;

多表查询

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

#查询Abel在哪个城市工作？
SELECT *
FROM employees
WHERE last_name ='Abel';

SELECT *
FROM departments
WHERE department_id =80;

SELECT *
FROM locations
WHERE location_id =2500;

实现多表查询

案例：查询员工的姓名及其部门名称

SELECT employee_id,department_name 
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`

多表查询需要加入链接条件。
案例：查询员工的employee_id,last_name,department_name,city

SELECT e.employee_id,last_name,department_name,city
FROM employees e,departments  d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`

多表查询分类

等值连接和非等值连接

SELECT last_name ,salary,grade_level
FROM employees e,job_grades j
#where e.`salary` between j.`lowest_sal` and j.`highest_sal`;
WHERE e.`salary`>=j.`lowest_sal` AND e.`salary`<=j.`highest_sal`;

自连接和非自连接

题目：查询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 ;

内连接和外连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

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

SQL99语法实现多表查询
使用JOIN…ON子句创建连接的语法结构：

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

内连接(INNER JOIN)的实现

SELECT last_name,department_name,city
FROM employees e JOIN departments d
ON e.`department_id`= d.`department_id`
JOIN locations l
ON d.`location_id`=l.`location_id`;

左、右外连接，简单理解哪边多往哪边链接

#实现查询结果是A 
SELECT 字段列表 
FROM A表 LEFT JOIN B表 
ON 关联条件 
WHERE 等其他子句

SELECT last_name,department_name 
FROM employees e LEFT JOIN departments d
ON e.`department_id` =d.`department_id`

SELECT last_name,department_name 
FROM departments d RIGHT JOIN   employees e 
ON e.`department_id` =d.`department_id`

UNION

#内连接 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