MySQL数据库——连接查询

第1关 内连接查询

一、本关任务：使用内连接查询数据表中学生姓名和对应的班级。

内连接查询

• 仅将两个表中满足连接条件的行组合起来作为结果集，称为内连接；

• 关键字：[inner] join ... on。

语法：

1. 表1 [inner] join 表2 on 表1.字段=表2.字段

语法解释：

1. 从表1中取出每一条记录，去表2中与所有的记录进行匹配，匹配必须是某个条件在表1中与表2中相同，最终才会保留结果，否则不保留。inner 关键字可省略不写；on 表示连接条件：条件字段就是代表相同的业务含义（如下面两张表中的 employee.dept_id 和 department.id），大多数情况下为两张表中的主外键关系。

内连接查询的使用

现在我们有两张表，数据如下：
employee表数据：

id	name	dept_id
1	Nancy	4
2	Tod	2
3	Carly	1
4	Allen	2
5	Mary	(null)

department表数据：

id	name
1	开发部
2	测试部
3	运维部
4	销售部

现在想要查询出员工姓名以及其对应的部门名称，我们就使用内连接来进行查询。

• 我们可以将关联查询思路分为三步：

1.确定所连接的表，
2.确定所要查询的字段，
3.确定连接条件与连接方式。

其中，没有部门的员工和部门没有员工的部门都没有被查出来，这就是内连接的特点，只查询在连接表中有对应的记录，其中dept.id=emp.dept_id是连接条件。

二、编程要求

补充代码，查询数据表中学生姓名以及对应的班级名称，将其对应的列名分别另命名为studentName和className。

两张表，内容如下：

tb_student表数据：

id	name	class_id
1	Emma	2
2	Mary	4
3	Allen	(null)
4	Kevin	1
5	Rose	2
6	James	1

tb_class表数据：

id	name
1	软件1631
2	软件1632
3	测试1631
4	测试1632

三、预期输出：

1. studentName className
2. Kevin 软件1631
3. James 软件1631
4. Emma 软件1632
5. Rose 软件1632
6. Mary 测试1632

 四、代码

USE School;

########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
SELECT tb_student.name as studentName,tb_class.name as className from tb_class join tb_student on tb_student.class_id=tb_class.id;



########## End ##########

第2关 外连接查询

一、本关任务：使用外连接查询数据表中所有班级和对应班级里学生的姓名。

外连接查询

• 以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留。能匹配，正确保留；不能匹配，其它表的字段都置空（null），称为外连接。

• 外连接查询分为左外连接查询和右外连接查询；

• 关键字：left/right [outer] join ... on。

语法：

1. 表1 left/right [outer] join 表2 on 表1.字段=表2.字段

语法解释：

1. 左外连接：在内连接的基础上，还包含表1中所有不符合条件的数据行，并在其中的表2列填写 NULL；
2. 右外连接：在内连接的基础上，还包含表2中所有不符合条件的数据行，并在其中的表1列填写 NULL。

外连接查询的使用

我们仍使用如下两张数据表：

employee表数据：

id	name	dept_id
1	Nancy	4
2	Tod	2
3	Carly	1
4	Allen	2
5	Mary	(null)

department表数据：

id	name
1	开发部
2	测试部
3	运维部
4	销售部

例如查询所有员工姓名以及他所在部门，在内连接中Mary没有被查出，因为他没有对应的部门，现在想把Mary也查出来，就要使用左外连接。

此查询语句以employee为主表查询，因此最终记录至少不少于主表已有的记录数。

• 右外连接是同理的，只是基准表的位置变化了而已。

我们在这里只是将left修改成了right,但是基准表变化了，是以department表的数据去匹配employee表，所以左外连接能做到的查询，右外连接也能做到，仅展现的效果有所不同。

虽然左外连接和右外连接有主表差异，但左外连接和右外连接可以互转，如上面的右外连接也等效于如下查询语句，只需将基准表换一下：

二、编程要求

补充代码，分别使用左外连接和右外连接查询数据表中所有学生姓名和对应的班级名称，查询结果列分别另命名为studentName和className。

我们为你提供了两张表，内容如下：

tb_student表数据：

id	name	class_id
1	Emma	2
2	Mary	4
3	Allen	(null)
4	Kevin	1
5	Rose	2
6	James	1

tb_class表数据：

id	name
1	软件1631
2	软件1632
3	测试1631
4	测试1632

注意：请使用 tb_student 作为左表，tb_class 作为右表。

三、预期输出：

1. studentName className
2. Emma 软件1632
3. Mary 测试1632
4. Allen NULL
5. Kevin 软件1631
6. Rose 软件1632
7. James 软件1631
8. studentName className
9. Kevin 软件1631
10. James 软件1631
11. Emma 软件1632
12. Rose 软件1632
13. NULL 测试1631
14. Mary 测试1632

四、代码 

########## Begin ##########
SELECT tb_student.name as studentName,tb_class.name as className from tb_student left join tb_class on tb_student.class_id = tb_class.id;

SELECT tb_student.name as studentName,tb_class.name as className from tb_student right join tb_class on tb_student.class_id = tb_class.id;



########## End ##########

第3关 符合条件连接查询

一、本关任务：使用连接查询，查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级。

复合条件连接查询

• 复合条件连接查询，就是在连接查询的过程中，通过添加过滤条件来限制查询结果，使查询结果更加精确。

如下两张数据表：

employee表数据：

id	name	dept_id	age
1	Nancy	3	18
2	Tod	2	22
3	Carly	1	19
4	Allen	2	24

department表数据：

id	name
1	开发部
2	测试部
3	运维部

要求查询员工姓名和所在部门名称，使用内连接查询，将查询结果按照年龄升序排序：

二、编程要求

查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级，其中学生的姓名和学生所在班级分别另命名为studentName和className。

两张表，内容如下：

tb_student表数据：

id	name	class_id	score
1	Emma	2	89
2	Mary	4	92
4	Kevin	1	76
5	Rose	3	68
6	James	1	99

tb_class表数据：

id	name
1	软件1631
2	软件1632
3	测试1631
4	测试1632

三、预期输出：

1. studentName score className
2. James 99 软件1631
3. Mary 92 测试1632

 四、代码

USE School;

########## 查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级 ##########
#请在此处添加实现代码
########## Begin ##########

SELECT tb_student.name as studentName,tb_student.score,tb_class.name as className from tb_class join tb_student on tb_student.class_id=tb_class.id where score>90;


########## End ##########