数据库 - DQL - 连接查询

最新推荐文章于 2021-12-06 15:10:19 发布

今天你学习了么

最新推荐文章于 2021-12-06 15:10:19 发布

阅读量305

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/weixin_43907800/article/details/104954192

版权

Mysql 专栏收录该内容

26 篇文章 6 订阅

订阅专栏

连接查询

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

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

按功能分类：
	|--内连接：从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留
		|--等值连接
		|--非等值连接
		|--自连接
	|--外连接：
		|--左外连接
		|--右外连接
		|--全外连接
	|--交叉连接：笛卡尔积

1. 内连接

从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留
语法1

select 查询列表
from 表1 别名,表2 别名
where 连接条件
and 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表

语法2：SQL99，使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性！！！

SELECT 查询列表
FROM 表名1 别名
【INNER】 JOIN  表名2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表;

在这里插入图片描述

1.1 等值连接

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

①简单连接
案例：查询员工名和部门名

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

②添加筛选条件
案例1：查询部门编号>100的部门名和所在的城市名

SELECT department_name,city
FROM departments d
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id`>100;

③添加分组+筛选
案例1：查询每个城市的部门个数

SELECT COUNT(*) 部门个数,l.`city`
FROM departments d
JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`;

④添加分组+筛选+排序
案例1：查询部门中员工个数>10的部门名，并按员工个数降序

SELECT COUNT(*) 员工个数,d.department_name
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>10
ORDER BY 员工个数 DESC;

案例1：查询女神名和对应的男神名

SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

案例2：查询员工名和对应的部门名

SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

为表起别名

①提高语句的简洁度
②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

查询员工名、工种号、工种名

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

可以加筛选
案例：查询有奖金的员工名、部门名

SELECT last_name,department_name,commission_pct

FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

案例2：查询城市名中第二个字符为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`;

可以加排序

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

SELECT job_title,COUNT(*)
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%'

ORDER BY department_name DESC;

1.2 非等值连接

案例1：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组

SELECT * FROM sal_grade;


SELECT COUNT(*) 个数,grade
FROM employees e
JOIN sal_grade g
ON e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.grade;

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

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

1.3 自连接

案例：查询员工名和对应的领导名

SELECT e.`last_name`,m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

案例：查询员工名和上级的名称

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`;

2. 外连接

说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则从表那一步显示null，一般用于查询主表中有但从表没有的记录

特点：

外连接分主从表，两表的顺序不能任意调换
左连接的话，left join左边为主表
右连接的话，right join右边为主表

在这里插入图片描述

语法：

select 查询列表
from 表1 别名
left|right|full 【outer】 join 表2 别名
on 连接条件
where 筛选条件;

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

左连接

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

右连接

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

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

#左连接
SELECT b.`name`
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id`  IS NULL;

#右连接
SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN  beauty b ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id`  IS NULL;

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

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

3. 七种join理论

如何理解sql的七种join
在这里插入图片描述
建表
员工表：emp_tbl:

create table emp_tbl
(
	emp_id int auto_increment,
	emp_name varchar(40) null,
	dep_id int not null,
	constraint emp_tbl_pk
		primary key (emp_id)
);

在这里插入图片描述
其中，5号员工peter对应的部门id是6，在部门表中是没有对应的部门的

部门表：dep_tbl:

create table dep_id
(
	dep_id int auto_increment,
	dep_name varchar(40) null,
	dep_addr varchar(40) null,
	constraint dep_id_pk
		primary key (dep_id)
);

在这里插入图片描述

① Left Join

在这里插入图片描述
左边的集合全都有，而右边的集合只有一部分与A重合的存在，即为：左表的全部都在，而且通过dep_id把右表存在的对应内容全部接上了，如果没有的话就是null

SELECT * FROM emp_tbl left join dep_tbl dt on emp_tbl.dep_id = dt.dep_id;

在这里插入图片描述

② Right Join

在这里插入图片描述

SELECT * FROM emp_tbl right join dep_tbl dt on emp_tbl.dep_id = dt.dep_id;

在这里插入图片描述

③ Inner Join

在这里插入图片描述
如图：意思就是取交集，就是要两边都有的东西，所以也就是不能有null出现

SELECT * FROM emp_tbl inner join dep_tbl dt on emp_tbl.dep_id = dt.dep_id;

在这里插入图片描述

④ Left Join且不含B的内容

在这里插入图片描述
也就是说，我们获得的结果就是A中与B没有交集的部分，所以就是，join B表会得到null的内容

SELECT * FROM emp_tbl left join dep_tbl dt on emp_tbl.dep_id = dt.dep_id where dt.dep_id is null;

在这里插入图片描述

⑤ Right Join且不含A的内容

在这里插入图片描述

SELECT * FROM emp_tbl ep right join dep_tbl on ep.dep_id = dep_tbl.dep_id where ep.dep_id is null;

在这里插入图片描述

⑥ Full Join

两边的所有内容都要有，连得起的就连，连不起的就补充null
在这里插入图片描述
mysql语法不支持full outer join，所以我们用union来实现，sql语句如下：

SELECT * FROM emp_tbl et left join dep_tbl dt on et.dep_id = dt.dep_id
union
SELECT * FROM emp_tbl et right join dep_tbl dt on et.dep_id = dt.dep_id where et.dep_id is null;

其实这整个红色部分就等于：A和B的左连接加上 A和B的右连接去公共部分
在这里插入图片描述

⑦ Full Join且不含交集

在这里插入图片描述

SELECT * FROM emp_tbl et left join dep_tbl dt on et.dep_id = dt.dep_id where dt.dep_id is null
union
SELECT * FROM emp_tbl et right join dep_tbl dt on et.dep_id = dt.dep_id where et.dep_id is null;

在这里插入图片描述

4. 一个练习

查找不在表里的数据
下面是学生的名单，表名为“学生表”；近视学生的名单，表名为“近视学生表”。请问不是近视眼的学生都有谁？
在这里插入图片描述
使用分析思路里的sql语句联结两表

select a.姓名 as 不近视的学生名单
from 学生表 as a
left join 近视学生表 as b
on a.学号=b.学生学号
where b.序号 is null;

我们来理解下这个sql的运行过程，方便你更深入的理解。

1）在不加where字句的情况下，两表联结得到下图的表

在这里插入图片描述

2）假设where字句（where b.序号 is null;）就会把b.序号这一列里为空值（NULL）的行选出来，就是题目要求的不近视的学生。（下图绿色框里的行）

在这里插入图片描述

今天你学习了么

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
数据库 - DQL - 连接查询

连接查询又称多表查询，当查询语句涉及到的字段来自于多个表时，就会用到连接查询笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行发生原因：没有有效的连接条件如何避免：添加有效的连接条件按功能分类： |--内连接： |--等值连接 |--非等值连接 |--自连接 |--外连接： |--左外连接 |--右外连接 |--全外连接 |--交叉连接1. 内...
复制链接

扫一扫