MySQL-实验4-数据查询（二）

目录

第1关 内连接查询

第2关 外连接查询

第3关 复合条件连接查询

第4关 GROUP BY 与 聚合函数

第5关 使用 HAVING 与 ORDER BY

第6关 COUNT( )函数

第7关 SUM( )函数

第8关 AVG( )函数

第9关 MAX( )函数

第10关 MIN( )函数

---

第1关 内连接查询

任务描述

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

相关知识

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

内连接查询

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

• 关键字：[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_student inner join tb_class 
on tb_student.class_id=tb_class.id


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

第2关 外连接查询

任务描述

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

相关知识

为了完成本关任务，你需要掌握： 1.什么是外连接查询； 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

---

开始你的任务吧，祝你成功！

USE School;
 
########## 使用左外连接查询所有学生姓名和对应的班级 ##########
 
#请在此处添加实现代码
########## Begin ##########
 #studentName列在左，className列在右
select tb_student.name as studentName,tb_class.name as className
from tb_class right join tb_student #class表右插入student表，student表在左边，以在左边的为name（emma-mary-allen-kevin-rose-james）为优先顺序先排（先放），
#然后将class表根据id号相等后连接
on tb_class.id=tb_student.class_id;
 
 
########## End ##########
 
########## 使用右外连接查询所有学生姓名和对应的班级 ##########
 
#请在此处添加实现代码
########## Begin ##########
#studentName列在左，className列在右
select tb_student.name as studentName,tb_class.name as className 
from tb_class left join tb_student#class表左插入student表，class表在左边，以在左边表的name（1631-1632-1631-1632）为顺序，根据class与student表的id号相等，连接
on tb_class.id=tb_student.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 s1.name as studentName,score,s2.name as className 
from tb_student as s1,tb_class as s2 
where s1.class_id=s2.id and s1.score>90 
order by score desc ;
########## End ##########
 

第4关 GROUP BY 与 聚合函数

任务描述

本关任务：使用GROUP BY关键字结合聚合函数将数据进行分组。

相关知识

在之前的实训中我们简单的提到过GROUP BY关键字，本实训让我们进一步了解GROUP BY与聚合函数的使用。

为了完成本关任务，你需要掌握： 1.GROUP BY与聚合函数的结合使用； 2.GROUP BY中SELECT指定的字段限制。

GROUP BY与聚合函数的使用

基本格式：

1. select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名]

先提供表Info结构如下：

category	count	digest
a	5	a2002
a	2	a2001
a	11	a2001
b	10	b2003
b	6	b2002
b	3	b2001
c	9	c2005
c	9	c2004
c	8	c2003
c	7	c2002
c	4	c2001

示例：将表中数据分类并汇总

GROUP BY中SELECT指定的字段限制

示例：

1. select category,sum(count),disgest from info group by category;

执行后会提示下错误，如下图。这就是需要注意的一点，在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

编程要求

在右侧编辑器补充代码，对年级Id和性别进行分组，分别统计表中2、3、4年级的男生总人数和女生总人数。

student表数据结构如下：

stuId	gradeId	sex
0201	2	男
0221	2	男
0319	3	女
0508	5	男
0610	6	女
0101	1	男
0224	2	女
0413	4	女

要求输出结果显示如下：

测试说明

平台会对你编写的代码进行测试：

预期输出：

1. gradeId sex count(*)
2. 2 女 1
3. 2 男 2
4. 3 女 1
5. 4 女 1

---

开始你的任务吧，祝你成功！

USE School;

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

#1.查询表中2,3,4年级中分别男女的总人数
select gradeId,sex,count(*) from student 
where gradeid in (2,3,4) group by gradeid,sex;

-- 方法2
select gradeId,sex,count(*) from student 
group by gradeid,sex having gradeid in (2,3,4);
########## End ##########

第5关 使用 HAVING 与 ORDER BY

任务描述

本关任务：按照要求编写sql查询语句。

相关知识

为了完成本关任务，你需要掌握：

1.使用having子句进行分组筛选；

2.Having与Where的区别；

3.Group By 和 Order By。

使用having子句进行分组筛选

简单来说，having子句用来对分组后的数据进行筛选，即having针对查询结果中的列发挥筛选数据作用。因此having通常与Group by连用。

基本格式：

1. select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [having 字段名 筛选条件]

表Info的数据信息仍如下：

category	count	digest
a	5	a2002
a	2	a2001
a	11	a2001
b	20	b2003
b	15	b2002
b	3	b2001
c	9	c2005
c	9	c2004
c	8	c2003
c	7	c2002
c	4	c2001

示例：查询将表中数据分类后数量大于20的类别信息

select语句中，where、group by、having子句和聚合函数的执行次序如下：

1.where子句从数据源中去除不符合条件的数据；

2.然后group by子句搜集数据行到各个组中；

3.接着统计函数为各个组计算统计值；

4.最后having子句去掉不符合其组搜索条件的各组数据行。

Having与Where的区别

where子句都可以用having代替，区别在于where过滤行，having过滤分组；

• where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行；

• having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件过滤出特定的组，也可以使用多个分组标准进行分组。

having结合where示例：

Group By 和 Order By

基本格式

1. select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [order by 字段名 排序方向]

示例：（以降序方式输出数据分类的汇总）

若分组字段和排序字段一样时，可不需要order by关键字，则只需告知排序方向，即可简写成：

编程要求

根据提示，在右侧编辑器补充代码：

1. 我们要评选三好学生，条件是至少有两门课程在90分以上（包括90分）才能有资格，请列出符合的学生的学号（sno）及其90分以上（包括90分）科目总数；

2. 学校评选先进学生，要求平均成绩大于90分（包括90分）的学生都有资格，并且语文课必须在95分以上（包括95分），请列出有资格的学生的学号（sno）及其科目的平均分。

给定数据表tb_grade格式如下：

sno	pno	score
1	语文	95
1	数学	98
1	英语	90
2	语文	89
2	数学	91
2	英语	92
3	语文	85
3	数学	88
3	英语	96
4	语文	95
4	数学	89
4	英语	88

测试说明

平台会对你编写的代码进行测试：

预期输出：

1. sno count(*)
2. 1 3
3. 2 2
4. sno avg(score)
5. 1 94.3333
6. 4 90.6667

---

开始你的任务吧，祝你成功！

USE School;

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

#1.查询表中至少有两门课程在90分以上的学生信息
select sno,count(*) 
from tb_grade 
where score>=90 
group by sno 
having count(*)>=2;

#2.查询表中平均成绩大于90分且语文课在95分以上的学生信息
-- select sno,`avg(score)`
-- from(
--     select sno,pno,score,avg(score) `avg(score)`
--     from tb_grade
--     group by sno) a
-- where score>=95 and `avg(score)`>=90
-- group by sno;

select sno,avg(score) from tb_grade 
where sno in(
    select sno from tb_grade 
    where pno='语文' and score>=95)
group by sno
having avg(score)>90;
########## End ##########

第6关 COUNT( )函数

任务描述

本关任务： 1.使用COUNT()函数查询数据表中总数据量， 2.使用COUNT()函数统计班级总人数。

相关知识

为了完成本关任务，你需要掌握：如何使用COUNT()函数。

COUNT()函数基本使用

COUNT()函数是用来统计记录的总条数。

1. select count(*/字段名) from 数据表;

例子说明： 假设我们现在有一张表tb_dept；内容如下：

id	name	dept_id
1	Nancy	301
2	Tob	303
3	Carly	301

我们需要获取整张表有多少条数据，那么我们就可以使用COUNT(*)来查询了。

那么，现在同样是这张表，我假若想知道301部门一共有少人，我们可以思考该怎么查询？

相信大家都想到了，我们可以使用where条件进行查询：

编程要求

根据提示，在右侧编辑器Begin-End处补充代码： 我们为你提供了一张tb_class表，内容如下：

id	name	classid
1	Emma	366
2	Mary	367
3	Allen	367
4	Kevin	367
5	Rose	366
6	James	367
7	Hale	366
8	David	367

根据此表数据：

1. 查询该表中一共有多少条数据；

2. 查询此表中367 班有多少位学生,以班级id和对应人数格式输出。

测试说明

平台会对你编写的代码进行测试：

预期输出：

1. count(*)
2. 8
3. classid count(*)
4. 367 5

---

开始你的任务吧，祝你成功！

USE School;

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

########## 查询该表中一共有多少条数据 ##########
select count(*) from tb_class;

########## 查询此表中367班有多少位学生 ##########
select classid,count(*) from tb_class where classid=367;

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

第7关 SUM( )函数

任务描述

本关任务： 1.使用SUM()函数查询数据表中学生的总成绩； 2.使用SUM()函数查询语文课程中学生的总成绩。

相关知识

为了完成本关任务，你需要掌握：如何使用SUM()函数。

SUM()函数基本使用

SUM()函数是对数据表的某列进行求和操作。

1. select sum(字段名) from 数据表;

例子说明： 现在有一张tb_Salary表,内容如下：

id	name	Salary	Month
1	Nancy	2300	11
2	Tob	5800	11
3	Carly	3200	11
4	Nancy	2600	12
5	Tob	6300	12
6	Carly	5200	12

假设场景公司老板想知道公司总共给员工开了多少工资，则可以使用SUM()函数来进行统计了。

若想得知Tob同事在11月和12月的薪水一共有多少，加上where条件即可。

编程要求

根据提示，在右侧编辑器Begin-End处补充代码，我们为你提供了一张tb_class表，内容如下：

id	name	course	score
1	Emma	语文	86
2	Mary	语文	79
3	Allen	语文	92
4	Emma	英语	116
5	Mary	英语	95
5	Allen	英语	100

根据此表数据：

1. 查询该表中学生的总成绩；

2. 查询学生语文课程的总分数。

测试说明

平台会对你编写的代码进行测试：

预期输出：

1. sum(score)
2. 568
3. course sum(score)
4. 语文 257

---

开始你的任务吧，祝你成功！

USE School;

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

########## 查询所有学生总分数 ##########
select sum(score) from tb_class;

########## 查询学生语文科目的总分数 ##########
select course,sum(score) from tb_class where course='语文';


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

第8关 AVG( )函数

任务描述

本关任务：使用AVG()函数查询学生各科目的平均分数。

相关知识

为了完成本关任务，你需要掌握：如何使用AVG()函数。

AVG()函数基本使用

AVG()函数是对数据表的某列进行求平均值操作。

1. select avg(字段名) from 数据表;

例子说明： 现在有一张tb_Salary表,内容如下：

id	name	Salary	Month
1	Nancy	2300	11
2	Tob	5800	11
3	Carly	3200	11
4	Nancy	2600	12
5	Tob	6300	12
6	Carly	5200	12

假设场景，老板想知道公司发给员工的薪水的平均值为多少，此时就可使用AVG()函数来计算了。

现在又想知道Nancy在11-12月份的薪水平均有多少，则可进行如下查询：

编程要求

根据提示，在右侧编辑器Begin-End处补充代码，我们为你提供了一张tb_class表，内容如下：

id	name	course	score
1	Emma	语文	86
2	Mary	语文	79
3	Allen	语文	92
4	Emma	英语	116
5	Mary	英语	95
5	Allen	英语	101

根据此表数据，查询表中该班级三位同学语文和英语课程的平均分数以及对应的课程名。

测试说明

平台会对你编写的代码进行测试：

预期输出：

1. course avg(score)
2. 语文 85.66666666666667
3. course avg(score)
4. 英语 104

---

开始你的任务吧，祝你成功！

USE School;

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

########## 查询学生语文科目的平均分数 ##########
select course,avg(score) from tb_class where course='语文';


########## 查询学生英语科目的平均分数 ##########
select course,avg(score) from tb_class where course='英语';


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

第9关 MAX( )函数

任务描述

本关任务：使用MAX()函数查询各科中的最高分数。

相关知识

为了完成本关任务，你需要掌握：如何使用MAX()函数。

MAX()函数基本使用

MAX()函数是求某列的最大数值。

1. select max(字段名) from 数据表;

例子说明： 现在有一张tb_Salary表,内容如下：

id	name	Salary	Month
1	Nancy	2300	11
2	Tob	5800	11
3	Carly	3200	11
4	Nancy	2600	12
5	Tob	6300	12
6	Carly	5200	12

假设场景，老板想知道公司在11-12月份中月薪最高的是多少，此时就可使用MAX()函数来统计了。

这样就可以看到公司中月薪最高是多少了。我们也可查询Carly在11-12月份中月薪最高是多少：

编程要求

根据提示，在右侧编辑器Begin-End处补充代码，我们为你提供了一张tb_class表，内容如下：

id	name	course	score
1	Emma	语文	86
2	Mary	语文	79
3	Allen	语文	92
4	Emma	英语	116
5	Mary	英语	95
6	Allen	英语	100

根据此表数据，分别查询语文和英语课程中的最高分数。

测试说明

平台会对你编写的代码进行测试：

预期输出：

1. course max(score)
2. 语文 92
3. course max(score)
4. 英语 116

---

开始你的任务吧，祝你成功！

USE School;

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

########## 查询语文课程中的最高分数 ##########
select course,max(score) from tb_class where course='语文';


########## 查询英语课程中的最高分数 ##########
select course,max(score) from tb_class where course='英语';
########## End ##########

第10关 MIN( )函数

任务描述

本关任务：使用MIN()函数查询学生在各科中的最低分数。

相关知识

为了完成本关任务，你需要掌握：如何使用MIN()函数。

MIN()函数基本使用

MIN()函数是求某列的最小数值。

1. select min(字段名) from 数据表

例子说明： 现在有一张tb_Salary表,内容如下：

id	name	Salary	Month
1	Nancy	2300	11
2	Tob	5800	11
3	Carly	3200	11
4	Nancy	2600	12
5	Tob	6300	12
6	Carly	5200	12

假设场景，老板想知道公司11-12月份中月薪最低的是多少，此时就可使用MIN()函数来统计了。

我们仍可以快速查出Tob在11-12月份中月薪最低时是多少：

编程要求

根据提示，在右侧编辑器Begin-End处补充代码，我们为你提供了一张tb_class表，内容如下：

id	name	course	score
1	Emma	语文	86
2	Mary	语文	79
3	Allen	语文	92
4	Emma	英语	116
5	Mary	英语	95
5	Allen	英语	100

根据此表数据，分别查询语文和英语课程中的最低分数。

测试说明

平台会对你编写的代码进行测试：

预期输出：

1. course min(score)
2. 语文 79
3. course min(score)
4. 英语 95

---

开始你的任务吧，祝你成功！

USE School;

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

########## 查询语文课程中的最低分数 ##########
select course,min(score) from tb_class where course='语文';


########## 查询英语课程中的最低分数 ##########
select course,min(score) from tb_class where course='英语';


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