MySQL数据库 - 连接查询和子查询

最新推荐文章于 2024-06-03 18:35:01 发布

星与星熙.

最新推荐文章于 2024-06-03 18:35:01 发布

阅读量1.2k

点赞数 32

分类专栏：数据库原理与应用实训文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qwertf123/article/details/138192343

版权

数据库原理与应用实训专栏收录该内容

10 篇文章 0 订阅

订阅专栏

目录

第1关：内连接查询

任务描述

相关知识

内连接查询

内连接查询的使用

编程要求

答案：

第2关：外连接查询

任务描述

相关知识

外连接查询

外连接查询的使用

编程要求

答案：

第3关：关键字子查询

任务描述

相关知识

ALL关键字

ANY和SOME关键字

IN关键字

编程要求

答案：

第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是连接条件。

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

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

编程要求

在右侧编辑器补充代码，查询数据表中学生姓名以及对应的班级名称，将其对应的列名分别另命名为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

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

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

答案：

USE School;

########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########

SELECT s.name AS studentName, c.name AS className
FROM tb_student AS s
JOIN tb_class AS c ON s.class_id = c.id;

########## 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表，所以左外连接能做到的查询，右外连接也能做到，仅展现的效果有所不同。

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

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

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

编程要求

在右侧编辑器补充代码，分别使用左外连接和右外连接查询数据表中所有学生姓名和对应的班级名称，查询结果列分别另命名为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

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

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

答案：

USE School;

########## 使用左外连接查询所有学生姓名和对应的班级 ##########

#请在此处添加实现代码
########## Begin ##########

SELECT s.name AS studentName, c.name AS className
FROM tb_student AS s
LEFT JOIN tb_class AS c ON s.class_id = c.id;

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

########## 使用右外连接查询所有学生姓名和对应的班级 ##########

#请在此处添加实现代码
########## Begin ##########

SELECT s.name AS studentName, c.name AS className
FROM tb_student AS s
RIGHT JOIN tb_class AS c ON s.class_id = c.id;

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

第3关：关键字子查询

任务描述

本关任务：根据要求使用关键字进行查询。

相关知识

由于列子查询返回的结果集是 N 行一列，因此不能直接使用 = 、>、<、>=、<=、<>这些比较标量结果的操作符。在列子查询中可以使用 ALL、ANY、SOME 和 IN关键字操作符。

为了完成本关任务，你需要掌握：如何在子查询中使用关键字进行查询。

ALL关键字

ALL必须接在一个比较运算符的后面，表示与子查询返回的所有值比较都为 TRUE则返回TRUE。

table1表数据：

col1
2
10

table2表数据：

col2
5
12
20

举个例子：
SELECT col1 FROM table1 WHERE col1 > ALL (SELECT col2 FROM table2)
该查询语句不会返回任何结果，因为col1中没有比col2所有值都大的值。

ANY和SOME关键字

ANY与比较操作符联合使用，表示与子查询返回的任何值比较为TRUE ，则返回TRUE。 SOME是ANY的别名，一般用的比较少。

仍使用上面二张表的数据：

在子查询中，返回的是table2的所有col2列的结果（5,12,20），然后将table1中col1的值与之进行比较，只要大于col2的任何值则为TRUE，因此查询结果为10。

IN关键字

IN的意思就是指定的一个值是否在这个集合中，如果在就返回TRUE；否则就返回FALSE了，同IN(项1，项2，...)；

IN 是 = ANY 的别名，二者相同，但 NOT IN 的别名却不是 <> ANY 而是 <> SOME。

col1
2
10

col2
5
12
20

编程要求

我们为你提供了如下数据表： tb_salary表数据：

id position salary
1 Java 8000
2 Java 8400
3 Java 9000
4 Python 6500
5 Python 10000

根据提供的数据，在右侧编辑器中补充代码：

查询薪资表中比Java最高工资高的所有员工职位名称和薪资；

查询薪资表中比Java最低工资高的所有员工职位名称和薪资；

查询薪资表中职位为Java的所有员工职位名称和薪资。

id	position	salary
1	Java	8000
2	Java	8400
3	Java	9000
4	Python	6500
5	Python	10000

答案：

USE Company;
#请在此处添加实现代码
########## Begin ##########
########## 查询薪资表中比Java最高工资高的所有员工职位名称和薪资 ##########
#1.使用 ALL 关键字进行查询

SELECT position, salary
FROM tb_salary
WHERE salary > (SELECT MAX(salary) FROM tb_salary WHERE position = 'Java')
ORDER BY position;

#2.使用 ANY 关键字进行查询

SELECT position, salary
FROM tb_salary
WHERE salary > (SELECT MIN(salary) FROM tb_salary WHERE position = 'Java')
ORDER BY position;

#3.使用 IN 关键字进行查询

SELECT position, salary
FROM tb_salary
WHERE position = 'Java';

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