点击上方头像关注我，每周上午 09:00准时推送，每月不定期赠送技术书籍，小窗口回复“资源”、“测试工具包”领取测试资源。
本文5580字，阅读约需14分钟
Hi，大家好。计算机领域有许多伟大的设计理念和思想，例如：
- 在 Unix 中，一切皆文件。
- 在面向对象的编程语言中，一切皆对象。
- 关系数据库同样也有自己的设计思想：在 SQL 中，一切皆关系。
多表查询经常用到连接，关系型数据库连接查询中有常用到内连接、左连接、右连接、交叉连接几种查询方式，今天就给大家介绍各种连接。
首先准备数据表，如需领取Navicat Premium或本文所需建表语句，可在ITester软件测试小栈后台回复“数据库礼包”。
1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号、学生姓名、出生年月、学生性别。
2.课程表
Course(c_id,c_name,t_id) – –课程编号、 课程名称、 教师编号。
3.教师表
Teacher(t_id,t_name) –教师编号、教师姓名。
4.成绩表
Score(s_id,c_id,s_score) –学生编号、课程编号、分数。
一
连接查询返回的结果分类
内连接（inner join）定义：只返回两张表中所有满足连接条件的行，即使用比较运算符根据每个表中共有的列的值匹配两个表中的行。
应用场景：满足某一条件的A，B内部的数据。
示例：
执行结果：
说明：inner join 是比较运算符，只返回符合条件的行。
此时相当于：select * from Student,Course where Student.s_id=Course.c_id;
外连接（outer join）(1) 左连接 （left join/left outer join ）
定义：左连接返回左表中的所有记录和右表中连接字段相等的记录。(左表就是写在left join关键字左边的表)。
应用场景：A的所有数据，和满足某一条件的B的数据。
示例：
运行结果：
说明：左外连接包含left join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL).注：此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。
(2) 右连接 （right join/right outer join）定义：返回包括右表中的所有记录和左表中连接字段相等的记录。(右表就是写在right join关键字右边的表)。
应用场景：B的所有数据，和满足某一条件的A的数据。
示例：
运行结果：
说明：右外连接包含right join右表所有行，如果左表中某行在右表没有匹配，则结果中对应左表的部分全部为空(NULL)。注：同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。
(3) 全外连接（full join/full outer join）定义：返回左右表中所有的记录和左右表中连接字段相等的记录。
应用场景：满足某一条件的公共记录，和独有的记录。
示例：
说明：完全外连接包含full join左右两表中所有的行，如果右表中某行在左表中没有匹配，则结果中对应行右表的部分全部为空(NULL)，如果左表中某行在右表中没有匹配，则结果中对应行左表的部分全部为空(NULL)。
注：全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上。但是MySQL目前不支持此种方式，如果去掉on条件语句的话，是有查询结果的。
运行结果：
定义：交叉连接/笛卡尔积返回左表中的所有行，左表中的每一行与右表中的所有行组合。
举例：有两个集合A和B，A = {0,1} B = {2,3,4}
集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：
A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；
B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；
以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。
从以上的数据分析我们可以得出以下两点结论：
- 两个集合相乘，不满足交换率，既 A×B ≠ B×A;
- A集合和B集合相乘，包含了集合A中元素和集合B中元素相结合的所有的可能性，即两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数。
交叉连接：
SELECT * from 表1 cross JOIN 表2；
交叉连接实际上是将两个表进行笛卡尔积运算，结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表，称为‘笛卡尔积表’，结果表的行数等于两个表的行数之积。
示例：
运行结果：
说明：没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。如果我们在此时给这条SQL加上WHERE子句的时候比如SQL:select * from Student cross join course where Student.s_id=Course.c_id;此时将返回符合条件的结果集，结果和inner join所示执行结果一样。
自连接在一个连接查询中，涉及到的两个表都是同一个表，这种查询就称为自连接查询。同一张表在FROM字句中多次出现，为了区别该表的每一次出现，需要为表定义一个别名。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。
示例：
运行结果：
二
按连接条件分类
等值连接（join）定义：使用”=”关系将表连接起来的查询，其查询结果中列出被连接表中的所有列，包括其中的重复列。
示例：
运行结果：
不用=连接，运算符包括>、>=、<=、<、!>、!<和<>。一般是通过外键建立索引使用等值连接，如果没有索引则需要遍历整个表，非常耗费时间，建立了索引就可以通过特定的值找到特定的记录。
自然连接（natural join）自然连接运算要求两个表有共同属性（列），自然连接运算的结果表是在参与操作的两个表的共同属性上进行等值连接后，再去除重复的属性后所得的新表。
三
数据库连接查询题
数据表如下：
学生表Student--stu，学生编号s_id，学生姓名s_name，出生日期s_age，性别s_sex；
成绩表Score--sc，学生编号s_id，课程编号c_id，成绩s_score；
课程表Course--co，课程编号c_id，课程名称c_name，教师编号t_id；
教师表Teacher--th，教师编号t_id、教师姓名t_name；
1.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩；
select Student.s_id,s_name,count(c_id) 选课数量,ifnull(sum(s_score),0) 总成绩
from Student left join Score on Student.s_id=Score.s_id
group by Student.s_id;
查询结果：
2.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select Student.s_id,s_name ,Score.s_score
from Student join Score on Student.s_id=Score.s_id
where c_id="01" and s_score>=80;
查询结果：
3.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩；
select Student.s_id,s_name,avg(s_score) 平均成绩
from Student join Score on Student.s_id=Student.s_id
group by Student.s_id
having sum(s_score<60)>1;
查询结果：
4.查询没有参与任意课程的学生信息；
select *
from
Student stu
left join Score sc on stu.s_id = sc.s_id
where
sc.s_id is null;
运行结果：
5.查询Jessie老师的授课信息；
select
*
from
Teacher th
inner join Course co on th.t_id = co.t_id
where
th.t_name = 'Jessie老师';
查询结果：
6.查询Jessie老师每个授课课程的学员人数；
select
co.c_id,
co.c_name,
th.t_name,
count( * )
from
Teacher th
inner join Course co on th.t_id = co.t_id
inner join Score sc on sc.c_id = co.c_id
where
th.t_name = 'Jessie老师'
group by
co.c_id,
co.c_name;
查询结果：
7.没有报名Jessie老师课程的学生信息；
select
*
from
Teacher th
inner join Course co on th.t_id = co.t_id
and th.t_name= 'Jessie老师'
inner join Score sc on co.c_id= sc.c_id
right join Student stu on stu.s_id= sc.s_id
where
sc.s_id is null;
查询结果：
8.查询报名Jessie老师课程的学生信息;
select
*
from
Student stu
inner join Score sc on stu.s_id = sc.s_id
inner join Course co on sc.c_id = co.c_id
inner join Teacher th on co.t_id = th.t_id
where
th.t_name= 'Jessie老师';
查询结果：
9.查询CoCo的数学成绩；
select
*
from
Student stu
inner join Score sc on stu.s_id = sc.s_id
inner join Course co on co.c_id = sc.c_id
where
stu.s_name = 'CoCo'
and co.c_name= '数学';
查询结果：
10.查询出语文最高分；
select
max(sc.s_score),
co.c_name
from
Student stu
inner join Score sc on stu.s_id = sc.s_id
inner join Course co on sc.c_id= co.c_id
where
co.c_name = '语文';
查询结果：
11.查询没有参与语文考试的学生信息；
select
*
from
Course co
inner join Score sc on co.c_id = sc.c_id
and co.c_name = '语文'
right join Student stu on stu.s_id= sc.s_id
where
sc.s_id is null;
查询结果：
12.查询语数外三门成绩的平均分；
select
co.c_id,
co.c_name,
avg(sc.s_score)
from
Student stu
inner join Score sc on stu.s_id = sc.s_id
inner join Course co on sc.c_id = co.c_id
group by
co.c_id,
co.c_name;
查询结果：
四
连接查询相关问题
你在测试工作中，常用到哪些连接查询？1）inner join（内连接）：如果表中有至少一个匹配，则返回行（在语法中可以省略inner关键字）；
2）left join（左连接）：从左表返回所有的行，如果右表中没有匹配，对应的列返回Null；
3）right join（右连接）：从右表返回所有的行 ，如果左表中没有匹配，对应的列返回Null；
4）full join（全连接）：只要其中一个表中存在匹配，则返回行（即结合左连接和右连接的结果）；
等值连接和自然连接的区别？1）等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。
2）等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。
涉及多表连接查询，你的思路是？1）分析查询列来源的表，构成from子句；2）分析表的关联关系，如两个表之间无直接关联关系，而是通过另一个中间表关联，则也要在from子句中补充中间关联表；3）在where子句中补充表之间的关联关系，N个表一般要N-1个关联关系；4）分析是否需要其他限制条件补充到where子句的表关联关系之后，作为限制条件；5）根据要求补充select子句；6）分析是否要求排序，如要且涉及其他表就要再次执行2）、3）、4）步；
更多系列文章
敬请期待
我是CoCo，计算机科学与技术专业，深漂大厂互联网民工（女），坐标深圳。5年工作经验，3年持续输出技术文。ITester软件测试小栈（ID：ITestingA）号主，专注于软件测试技术和宝藏干货分享，每周准时更新原创技术文章，每月不定期赠送技术书籍，愿我们在更高处相逢。喜欢记得星标⭐我，每周及时获得最新推送，第三方转载请注明出处。
想获取更多最新干货内容
快来星标 置顶 关注我
,
