软件测试Mysql数据库学习（三）完结

最新推荐文章于 2024-03-17 16:22:23 发布

大圣测试

最新推荐文章于 2024-03-17 16:22:23 发布

阅读量431

点赞数

分类专栏：测试必备技能文章标签：数据库 mysql database 测试工程师软件测试

本文链接：https://blog.csdn.net/qq_57145741/article/details/121737853

版权

测试必备技能专栏收录该内容

5 篇文章 4 订阅

订阅专栏

本文详细介绍了MySQL数据库的复杂查询，包括分组查询、子查询和关联查询（表连接）。重点讲解了分组查询的注意事项，子查询的使用场景和关联查询的多种连接方式，并给出了多个示例来帮助理解。此外，还提到了SQL查询语句的执行顺序和如何限制结果输出。

摘要由CSDN通过智能技术生成

回顾：查询的语法

select 5

from 1

where 2

group by 3

having 4

order by 6

复杂查

分组查询
子查询
关联查询（表连接）

（1）分组查询

group by

having

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

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

2）如果有where条件，那么一定在group by前面，它会过滤掉不满足条件的数据，

然后再在剩下的满足条件的数据中进行分组；

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

数据进行排序；

4）如果分组后，想要筛选符合特定条件的组而不是所有组，那么需要在group by

后使用having，而不是where.

示例：

统计各个班级的人数

select * from xsb;

select bj,count(xh)

from xsb

group by bj;

统计班级人数多于2个人的班级及其数据

select bj,count(xh)

from xsb

group by bj

having count(xh)>2;

查询不止一门课程不及格的学员学号

select xh

from cjb

where cj<60

group by xh

having count(xh)>1;

（2）子查询

查询嵌套：查询语句里又嵌套了若干个小的查询

示例1：

查询选修了java语言的全部学员的平均成绩

思路：先通过“jva语言”这个课程名在kcb里找到它对应的kch，

然后再在cjb里根据kch查询到它所对应的cj，然后统计求平均值

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语言'

);

示例2：

查询1班叫张三的学员的java语言的成绩

思路：

求出1班叫张三的学员的学号

select xh from xsb where bj='1班' and xm='张三'; -- 001

求出java语言对应的课程号

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

再在cjb里求出对应的成绩

select cj from cjb where xh='001' and kch='02';

将以上三步合并:

select cj from cjb

where xh=(

select xh from xsb

where bj='1班' and xm='张三'

)

and kch=(

select kch from kcb

where kcm='java语言'

);

示例3：

查询选修了java语言课程的所有学员的姓名和班级

思路：java语言——》在kcb里找到对应的kch——》cjb里找到对应的xh

——》在xsb里找到对应的xm和bj

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

select xh from cjb where kch='02'; -- 002 001

select xm,bj from xsb where xh in('001','002');

合并以上3步：

select xm,bj from xsb

where xh in(

select xh from cjb

where kch=(

select kch from kcb

where kcm='java语言'

)

);

使用子查询的注意事项:

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

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

3）如果子查询返回多行，要用in连接，而不是=，不然会报错；

补充示例4：（不做要求）

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

思路：

求java语言的平均分，

然后查询java语言课程成绩项里高于java平均分的记录从而得到学号

最后再在xsb里找到对应的学员信息

select avg(cj) from cjb

where kch=(

select kch from kcb

where kcm='java语言'

);

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语言'

)

);

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语言'

)

);

（3）关联查询（表连接）

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

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

表怎么连接？通过笛卡儿积运算连接（将第一张表的每一行跟第二张表里的每一行一一组合，

从而生成一张大的表的过程）——》在SQL里，from后面跟了多张表的时候就是在做笛卡儿积运算，生成一张大的表：select * from xsb,cjb;——》这张大的表里存在许多没有意义的垃圾数据（例如002号学员连接了001号学员的成绩）——》如何过滤这些垃圾数据？——》通过添加关联条件的方式进行垃圾数据的消除——》我们希望002号学员连接002号学员的各科成绩，那么可以通过让xsb的xh跟cjb的xh相等进行垃圾数据的消除。

select * from xsb,cjb; -- 132行（11*12）11列（8+3）

select * from xsb; -- 11行8列

select * from cjb; -- 12行3列

有两种添加关联条件的写法

简写方式：

select * from xsb,cjb

where xsb.xh=cjb.xh;

标准写法：

select * from xsb inner join cjb

on xsb.xh=cjb.xh;

回到一开始的查询需求：

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

简写方式：

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='李四';

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

from xsb x inner join cjb c

on x.xh=c.xh

where x.xm='李四';

表的连接方式

内连接：inner join，只返回满足关联条件的结果集

外连接:

左外连接：left join/left outer join,指的是除了返回满足关联条件的结果集以外，还会把

left join左边的那张表完整的展示出来，右边的那张表里不满足关联条件的字段位置补空值（null）

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

full join两边的表完整的展示出来，两边不满足关联条件的字段位置补空值（null）

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

外连接示例：

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

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

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

from xsb x,cjb c

where x.xh=c.xh;

select * from xsb;

2、可以通过左外连接或者右外连接来实现这个需求（需要将xsb信息完整展示）