江的福与MySQL（连接查询92&99）

“今日暖阳，甚是灿烂。”

连接查询

含义： 又称多表查询，当查询的字段来自于多个表中，就会用到多表查询。
错误演示：

这是和我们实际不符的，因为我们没有加任何连接条件。

SELECT `name`,  boyName
FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id

sql92

等值连接

【案例1】查询员工名和对应的部门名

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

【案例2】查询员工名，工种号，工种名（取别名）

SELECT last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id

一般在连接查询中，尽量对表起别名。
但是注意，如果为表起了别名，我们查询的字段就不能用原来的表名做限定。

【案例3】 查询有奖金的员工名，部门名（筛选）

SELECT e.last_name,e.department_id
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.commission_pct is NOT NULL

【案例4】查询城市名中第二个字符为o的部门名和城市名(筛选)

SELECT department_name,city
FROM departments d,locations l
WHERE d.department_id=l.location_id
AND city LIKE '_o%'

【案例5】查询每个城市的部门个数（分组）

SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.department_id =l.location_id
GROUP BY city

【案例6】 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资（分组）

讲真，我觉得这个例子不好

SELECT department_name, d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.department_id =e.department_id
AND commission_pct is not NULL
GROUP BY department_name,d.manager_id;

【案例7】查询每个工种的工种名和员工的个数，并且按员工的个数降序

SELECT j.job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY j.job_id
ORDER BY COUNT(*) DESC

【案例8】查询员工名，部门名和所在城市（三表连接）

SELECT last_name, department_name,city
FROM employees,departments,locations
WHERE employees.department_id =departments.department_id
AND departments.location_id =locations.location_id
ORDER BY city

小总结

1、多表等值连接的结果为多表的交集部分
2、n表连接，至少需要n-1个连接条件
3、多表的顺序没有要求
4、一般为表起别名
5、可以搭配子句使用，排序，分组，等

非等值连接

【案例1】 查询员工的工资和工资级别（工资范围）

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND  g.highest_sal

自连接

【案例1】员工名和上级的名字

首先在employees表中找到员工对应的领导编号，再用编号找名字

SELECT e.employee_id,e.last_name,m.employee_id ,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id

sql 99

语法：

		select 查询列表
		from 表1 别名【连接类型】
		join   表2 别名  
		on 连接条件
		【where  筛选条件】
		【group by 分组】
		【having 筛选条件】

内连接 ：inner

1、等值连接

【案例1】查询员工名，部门名

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

【案例2】查询名字中包含e的员工名和工种名（添加筛选）

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id	=j.job_id
WHERE e.last_name LIKE '%e%'

【案例3】查询部门个数>3的城市名和部门个数（添加分组+筛选）

SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT(*)>3

【案例4】查询那个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

SELECT d.department_name,COUNT(*) 个数
FROM departments d
INNER JOIN  employees e
on  d.department_id =e.department_id
GROUP BY department_name
HAVING 个数>3
ORDER BY 个数  DESC

【案例5】 查询员工名、部门名、工种名，并按部门排序（多表连接）

SELECT last_name,department_name,job_title
FROM employees e 
INNER JOIN departments  d 
ON e.department_id =d.department_id
INNER JOIN jobs j
ON e.job_id =j.job_id
ORDER BY d.department_name DESC

非等值连接

【案例】 查询员工的工资级别

SELECT last_name,department_name,job_title
FROM employees e 
INNER JOIN departments  d 
ON e.department_id =d.department_id
INNER JOIN jobs j
ON e.job_id =j.job_id
ORDER BY d.department_name DESC

自连接

【案例】查询员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN  employees m
ON e.manager_id =m.employee_id
WHERE e.last_name LIKE '%k%';

外连接

应用场景：用于查询一个表中有，另一个表中没有的记录
引入： 【案例】查询男朋友不在男神表的女神名
特点：
1、外连接的查询结果为主表中的所有记录。如果从表中又和它匹配的，显示值，若无，显示null
外连接查询结果=内连接结果+主表有从表无
2、左外连接:left join左边是主表
右外连接：right join 右边是主表

3、左外和右外交换两个表的顺序，可以实现相同的效果
4、全外连接 = 内连接的结果+表一中有但表二中无+表二中有但表一中无

左外：left 【outer】

SELECT b.`name`,boys.*
FROM beauty b
LEFT OUTER JOIN boys 
ON b.boyfriend_id=boys.id 
WHERE boys.id is NULL

右外：right 【outer】

SELECT d.department_name,employee_id
FROM employees e
LEFT OUTER JOIN departments d
ON d.department_id =e.department_id
WHERE e.employee_id =NULL

全外：full 【outer】

交叉连接 ：cross

这个在这里意义不大

SELECT b.*,boys.*
FROM beauty b
CROSS JOIN boys