进阶07 --连接查询

1.含义：

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

2.笛卡尔乘积现象：原因以及解决

表1 有m行，表2有n行，结果：mXn行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

3.分类：

按年代分类：

sq192标准：

​ 仅仅支持内连接

sq199标准【推荐】:

​ 支持内连接+外链接【左外和右外】+交叉连接

按功能分类

内连接：

​ 等值连接
​ 非等值连接
​ 自连接

外链接：

​ 左外连接
​ 右外链接
​ 全外连接

交叉连接（了解）

------sql 92语法(仅支持内连接)-----

内连接

1.等值连接

1.1、语法：

表名一般是需要起一个别名，对个表之间是需要使用 , 逗号隔开

注意：一旦起了别名就需要我们使用别名限定查询，不可以再使用表名限定了

from 表名1 别名，表名2 别名2…

​ select 查询列表
​ from 表名1 别名1，表名2 别名2…
​ where 等值连接的连接条件 and 筛选条件

1.2、特点：

​ 1、为了解决多表中的字段名重名问题，往往为表起别名，提高语义性
​ 2.表的顺序是可以调换的，与from 后的表名顺序没有关系，无要求
​ 3.n表连接需要n-1也连接条件
​ 4.一般需要给表起别名
​ 5.可以搭配前面介绍过的所有子句连接使用，比如排序，分组，筛选
​ 6.多表等值连接的结果为多表的交集部分

1.3、执行顺序：

​ 1.from
​ 2.where
​ 3.and
​ 4.group by
​ 5.having
​ 6.select
​ 7.order by

1.4、案例：

①简单的两表连接

USER myemployees

#案例：查询员工名和部门名
#注意：一旦起了别名就需要我们使用别名限定查询，不可以再使用表名限定了
SELECT e.last_name,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;

②添加筛选条件

#案例1：查询部门编号>100的部门名和所在的城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id;

#案例2：查询有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;

#案例3：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
AND city LIKE ‘_o%’;

③添加分组+筛选

#案例1：查询每个城市的部门个数
SELECT COUNT(*) 部门数,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY city;

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
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;

#案例3：查询部门中员工个数>10的部门名
SELECT department_name,COUNT()
FROM departments d,employees e
WHERE d.department_id=e.department_id
GROUP BY d.department_name
HAVING COUNT()>10;

④添加分组+筛选+排序

#案例1：查询部门中员工个数>10的部门名，并按部门名降序
SELECT department_name,COUNT()
FROM departments d,employees e
WHERE d.department_id=e.department_id
GROUP BY d.department_name
HAVING COUNT()>10
ORDER BY d.department_name DESC;
#案例2：查询每个工种的员工个数和工种名，并按个数降序
SELECT COUNT(),job_title
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT() DESC;

⑤三表连接

#案例：查询员工名、部门名、城市名
SELECT 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
AND city LIKE ‘s%’;

2.非等值连接

语法

语法结构和等值连接一样，改变的就是连接条件的不在是等值判断，而是一个范围，等等。。。。

案例1：查询员工的工资和工资级别

SELECT * FROM job_grades;
SELECT salary,employee_id FROM employees;

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

3.自连接（相当于等值连接，自己连接自己）

解释：

同一张表找几遍
#特殊的字段才可以
#查找员工的领导的姓名
#比如我们员工表里面，我们的员工领导编号还是在员工表里面，我们第一步是是去找员工表的领导的id
#按照领导的id，再去员工表里id相同的员工，
#表起别名易于分辨

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

----sql 99语法(不支持交叉连接)----

一、内连接

1.语法：只是在sql 92的基础上优化了一下

join关键字代替了之前的逗号来连接表名（）inner可以写，也可以不写，连接条件也是使用on 关键词来连接

和where过滤条件分开

select 查询列表
from 表名1，别名
【inner】join 表名2 别名
on 连接条件（92语法一样）
where 筛选条件
group by
having
order by

2.sql92和sql99的区别

sql99，使用join关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性！！！！join关键字代替了之前的逗号来连接表名（）inner可以写，也可以不写，连接条件也是使用on 关键词来连接和where过滤条件分来

3.等值连接

#案例：查询员工名和部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;

#②添加筛选条件
#案例1：查询部门编号>100的部门名和所在的城市名
SELECT department_name,city
FROM departments d
INNER JOIN locations l
ON d.location_id=l.location_id
WHERE d.department_id>100;

#③添加分组+筛选

#案例1：查询每个城市的部门个数
SELECT COUNT(*) ,city
FROM locations l
INNER JOIN departments d
ON l.location_id=d.location_id
GROUP BY city;

#④添加分组+筛选+排序
#案例1：查询部门中员工个数>10的部门名，并按部门名降序
SELECT d.department_name,COUNT()
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY d.department_name
HAVING COUNT()>10;

4.非等值连接

#案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
SELECT COUNT(*),g.grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_salAND g.highest_sal
GROUP BY g.grade_level;

5.自连接

查找员工的领导的姓名

SELECT e.last_name 员工,e.manager_id,m.last_name 领导,m.employee_id
FROM employees e
JOIN employees m
ON e.manager_id=m.employee_id;

二、外链接：

1.解释：啥是外链接

两张表，分为主表和从表，每次查询，主表中的每一条数据都会和从表的每一条数数据进行匹配，要是从表被匹配了，就会到结果集中，一直到匹配结束，当我们匹配结束后，主表中和从表匹配上的显示从表信息，没有的显示null，就是谁是主表，谁是一定显示

说明：查询结果为主表中所有的记录，如果从表中有配置项，则显示匹配项，如果从表没有匹配项，则显示null

2.应用场景：一般用于查询主表中有但从表没有的记录

3.特点：分主表，从表

​ 1.外链接分主从表，两表顺序不能随意调换
​ 2.左连接的话，left join左边为主表
​ 右连接的话，right join右边为主表
​ 3.全连接数据都出来，没有数据的都用null来填充

4.语法：

​ select 查询列表
​ from 表1 别名
​ left|right outer join 表2 别名
​ on 连接条件
​ where 筛选条件；

左连接左边的表位主表

右连接右边的边为主表

#案例一：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示null

左连接

SELECT b.*,bo.boyName
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id=bo.id;

右连接

SELECT b.*,bo.boyName
FROM boys bo
RIGHT JOIN beauty b
ON b.boyfriend_id=bo.id;

案例二：查询哪个女神没有男朋友

#一般判断主键为是否为null

左连接

SELECT b.name
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;

右连接

SELECT b.name
FROM boys bo
RIGHT JOIN beauty b
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;

案例三：查询哪个部门没有员工，并显示部门名和部门编号

SELECT COUNT(*)
FROM departments d
LEFT JOIN employees e
ON e.department_id=d.department_id
WHERE e.employee_id IS NULL;

三、全连接

1.解释

就是左右连个表都显示在结果集里面，输出的结果集没有就显示null

外链接练习题：

#一、查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充

SELECT b.name,bo.boyName
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE b.id>3;

#二、查询哪个城市没有部门
SELECT l.,d.
FROM locations l
LEFT JOIN departments d
ON l.location_id=d.location_id
WHERE d.location_id IS NULL;

#三、查询部门名为 SAL 或 IT 的员工信息

SELECT e.*
FROM departments d
LEFT JOIN employees e
ON d.department_id=e.department_id
WHERE d.department_name IN(‘SAL’,‘IT’);