MySQL数据库系列第三次指导

最新推荐文章于 2023-05-07 11:32:03 发布

7.KM

最新推荐文章于 2023-05-07 11:32:03 发布

阅读量154

点赞数

分类专栏：数据库 MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_65773135/article/details/125578361

版权

数据库同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

MySQL

3 篇文章 0 订阅

订阅专栏

😊先回顾上期文章要点内容：

任务1：回顾查询的语法

select [distinct] *|字段1，字段2……字段n 5

from 表名 1

where 查询条件 2

group by 分组字段 3

having 过滤条件 4

order by 字段 [asc|desc]; 6

任务2：复杂查询

🐇分组查询
🐱子查询
🐅关联查询

2.1 分组查询

group by having

【示例1】查询平均成绩高于80分的科目

1 先查询各个科目的平均成绩

select kch,round(avg(cj))

from cjb

group by kch;

2 然后过滤出平均成绩高于80分的

select kch,round(avg(cj))

from cjb

group by kch

having avg(cj)>80;

【示例2】查询不止1门课程不及格的学员学号

1 首先过滤出不及格的学员学号：

select xh from cjb where cj<60;

2 然后按学号分组，过滤出分组数目大于1的分组：

select xh

from cjb

where cj<60

group by xh

having count(*)>1;

使用group by子句的注意事项:

1)一旦按照某个字段分组，那么select后只出现分组的字段和聚合函数；

2）如果有where条件，那么一定在group by的前面，它会在分组之前先过滤不满足条件的数据，然后在剩下的满足条件的数据中，进行分组；

3）如果有order by排序，那么一定在group by的后面，是对分组后的结果集中的数据的排序；

4）如果分组后，想要筛选符合条件的组，那么需要在group by后使用having，而不是where;

2.2 子查询

子查询又称为查询嵌套，指的是查询语句里又嵌套了若干个小的查询。

【示例3】查询选修了java语言的全部学员的平均成绩

思路：先通过java语言这个课程名在kcb里找到它所对应的课程号，

然后再在cjb里根据课程号查询它所对应的成绩，然后进行统计求平均值

select kch from kcb where kcm='java语言'; -- 02

select round(avg(cj)) from cjb where kch='02';

将上面两步进行合并：

select round(avg(cj)) from cjb

where kch=(

select kch from kcb

where kcm='java语言'

);

【示例4】查询2班叫李四的学员的java语言的成绩

2班叫李四的学员的学号

select xh from xsb where bj='2班' and xm='李四'; -- 002

java语言对应的课程号

select kch from kcb where kcm='java语言'; -- 02

将上面两步进行合并：

select cj from cjb

where xh=(select xh from xsb where bj='2班' and xm='李四')

and kch=(select kch from kcb where kcm='java语言');

【示例5】列出选修了java语言课程的所有学员的姓名和班级

思路：

1 在kcb里查询java语言对应的课程号；

select kch from kcb where kcm='java语言'; -- 02

2 在cjb里查询 1 中求出来的课程号所对应的学号

select xh from cjb where kch=(1);

3 在xsb里查询 2 中求出来的学号所对应的姓名和班级

select xm,bj from xsb where xh in(2);

合并以上三个步骤：

select xm,bj from xsb

where xh in(

select xh from cjb

where kch=(

select kch from kcb

where kcm='java语言'));

【示例6】查询java语言课程的分数高于这门课平均分的学员信息

1 求出java语言对应的课程号

select kch from kcb where kcm='java语言';

2 求java语言的平均分

select avg(cj) from cjb where kch=(select kch from kcb where kcm='java语言');

3 求分数高于2里的求的平均分的学号

select xh from cjb

where kch=(

select kch from kcb

where kcm='java语言'

)

and cj>(

select avg(cj) from cjb

where kch=(

select kch from kcb

where kcm='java语言'

)

);

4 在学生表里查到学员详细信息：

select * from xsb

where xh in

(select xh from cjb

where kch=(

select kch from kcb

where kcm='java语言'

)

and cj>(

select avg(cj) from cjb

where kch=(

select kch from kcb

where kcm='java语言'

)

)

);

使用子查询要注意的事项:

1)子查询要用小括号括起来；

2）子查询经常用在where后做条件的一部分；

3）如果子查询返回多行，要用in进行连接，而不是=

2.3 关联查询

引入：查询李四同学的各科成绩，要求同时显示姓名、课程号、成绩

需要将原本不在同一张表的字段一起显示在同一个查询结果里，通过关联查询（表连接）来实现。

如何连接表？通过笛卡尔积运算（将第一张表里的每一行跟第二张表里的每一行一一组合，从而生成一张大的表的过程）连接。

在SQL里，from xsb,cjb 做了笛卡尔积运算，生成了一张大的表：

select * from xsb,cjb;

这张大的表里存在许多没有意义的垃圾数据（例如003号学员的信息连接了002号学员的成绩），如何过滤这些垃圾数据呢？可以通过添加关联条件的方式进行垃圾数据的过滤。我们希望003号学员信息只关联003号学员的成绩，那么可以通过让xsb的xh跟cjb的xh进行等值连接来消除垃圾数据：

内连接：

select *

from xsb,cjb

where xsb.xh=cjb.xh;

select *

from xsb inner join cjb

on xsb.xh=cjb.xh;

【示例7】查询李四同学的各科成绩，要求同时显示姓名、课程号、成绩

select xsb.xm,cjb.kch,cjb.cj

from xsb,cjb

where xsb.xh=cjb.xh

and xsb.xm='李四';

也可以写成：

select xsb.xm,cjb.kch,cjb.cj

from xsb inner join cjb

on xsb.xh=cjb.xh

where xsb.xm='李四';

注意：

1）对于同名字段，我们需要在字段名前面加上表名做前缀以示区分；

对于非同名的字段，可以不加表名做前缀，但是，建议初学者加上以避免错误。

2）只要看到了from后面跟两张及其以上的表，就要反应出这是在做笛卡尔积运算，笛卡尔积运算会产生垃圾数据，需要添加关联条件来消除垃圾数据；

3）如果题目中的查询需求除了表的关联以外还有其他的限定条件，我们可以通过and连接其他的条件（表的连接简写方式里），对于inner join的这种标准写法，

我们把条件放在where子句里。

4）除了可以给字段取别名以外，还可以给表取别名

select x.xm,c.kch,c.cj

from xsb x,cjb c

where x.xh=c.xh

and x.xm='李四';

5）表的连接方式：

内连接：多表连接的一种，它只返回满足关联条件的结果集；

外连接：

左外连接：left join 指的是除了返回满足关联条件的结果集以外，还会把left join左边的那张表完整的展示出来，右边的那张表里不满足关联条件的字段位置补空值（null）

右外连接：right join 指的是除了返回满足关联条件的结果集以外，还会把right join右边的那张表完整的展示出来，左边的那张表里不满足关联条件的字段位置补空值（null）

全外连接 full join 指的是除了返回满足关联条件的结果集以外，还会把full join两边的表完整的展示出来，两边不满足关联条件的字段位置补空值（null）

注意：MySQL不支持full join，可以通过union集合操作来实现全外连接

【示例8】查询所有学员的学号、姓名、课程号和成绩（要求没有选课的学员信息也要展示出来）

以下这种写法不能将没有选课的学员信息展示出来（例如008/009等学员）

select x.xh,x.xm,c.kch,c.cj

from xsb x,cjb c

where x.xh=c.xh;

select * from xsb;

可以通过左外连接或者右外连接来实现这个需求（将所有学员信息展示，不管有没有选课）

select x.xh,x.xm,c.kch,c.cj

from xsb x left join cjb c

on x.xh=c.xh;

select x.xh,x.xm,c.kch,c.cj

from cjb c right join xsb x

on x.xh=c.xh;

【示例9】全外连接

select x.xh,x.xm,c.kch,c.cj

from xsb x left join cjb c

on x.xh=c.xh

union

select x.xh,x.xm,c.kch,c.cj

from cjb c left join xsb x

on x.xh=c.xh;

【示例10】三表关联：查询学员的姓名、选修的课程名和成绩

select x.xm,k.kcm,c.cj

from xsb x,kcb k,cjb c

where x.xh=c.xh

and k.kch=c.kch;

select x.xm,k.kcm,c.cj

from xsb x inner join cjb c

on x.xh=c.xh

inner join kcb k

on k.kch=c.kch;

【示例11】多表和子查询结合的示例：查询选修了java语言课程课程的学员的名字和成绩

select kch from kcb where kcm ='java语言';

select x.xm,c.cj

from xsb x,cjb c

where x.xh=c.xh

and c.kch in(

select kch from kcb

where kcm ='java语言'

);

或：

select x.xm,c.cj

from xsb x,cjb c,kcb k

where x.xh=c.xh

and c.kch=k.kch

and k.kcm='java语言';