02多表查询- 连接查询（二）

第1关：内连接查询

任务描述

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

相关知识

为了完成本关任务，你需要掌握：
1.什么是内连接查询；
2.如何使用内连接查询。

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

关键字：[inner] join … on。

语法：

表1 [inner] join 表2 on 表1.字段=表2.字段
语法解释：

从表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。

答案

USE School;

########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
-- 等值连接
-- select * from 表1，表2 where 表1.字段 = 表2.字段
select s.name studentName, c.name className
from tb_student s, tb_class c
where s.class_id = c.id

/*select s.name studentName, c.name className 
from tb_student s join tb_class c on s.class_id = c.id;*/

-- 内连接的查询语句
-- select * from 表1 join 表2 on 表1.字段 = 表2.字段

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

第2关：外连接查询

任务描述

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

相关知识

为了完成本关任务，你需要掌握：
1.什么是外连接查询；
2.如何使用外连接查询。

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

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

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

语法：

表1 left/right [outer] join 表2 on 表1.字段=表2.字段
语法解释：

左外连接：在内连接的基础上，还包含表1中所有不符合条件的数据行，并在其中的表2列填写 NULL；
右外连接：在内连接的基础上，还包含表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 作为右表。

答案

########## Begin ##########
-- 右外连接可以变成左外连接
    -- select s.name studentName, c.name className
    -- from tb_class c left join tb_student s on s.class_id = c.id;

    select s.name studentName, c.name className
    from tb_student s right join tb_class c on s.class_id = c.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

答案

USE School;

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

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


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