软件测试必备之数据库知识（下）

02

软件测试必备之数据库知识（下）

7. update：修改表数据

做测试数据的时候，如果想在已有的数据的基础上去做一些修改，该怎么通过SQL命令去完成呢？现在学生表里面的数据如下：

如果我们要修改学号为1501001这个学生的住址改为“北京海淀区”改怎么做，如果我们要将所要学生的住址改为“北京海淀区”又该怎么做。

接下来我们即将学习的update语句就是用来更新表数据的。根据实际情况更新数据又分为更新一个表的所有数据，以及更新一个表的部分数据。

7.1 无条件更新

无条件更新表的数据指的是更改表里的某些字段的值，而且没有指定的任何条件来限制要更改那几条数据，所有最终被更新的数据会是表里面的所有数据。

语法

Update 表名 set 字段 A=值A，字段B=值B，……字段N=值N；

注意事项：

1）set后面声明的是需要更新的字段，以及每个字段将要被更新成的值，需要更新几个就写几个，字段间以英文的逗号来分隔。

2）给值得时候要注意值得类型跟表字段类型一致。

示例：

更新学生表里所有学生的住址为“北京海淀区”：

Update student set address=“北京海淀区”；

7.2 条件修改

修改表里满足条件的那一部分数据指的是，在更新某些字段的值得时候，影响到的是一部分数据，而不是所有，这样的数据满足某一个条件或者多个条件，故称为“条件更新”

语法

Update 表名 set 字段A=值A，字段B=值B，……字段N=值N where 条件A and（or）条件B and（or）条件C and（or）……and（or）条件N；

注意事项：

1.set后面是需要更新的字段，需要更新几个就写几个。

2.where是一个关键字是用来做过滤的，条件更新的时候，条件表达式就写在where关键字后。

3.多个条件表达式之间使用的是“and”或者“or”来连接，and连接的条件表示需要同时成立，or连接的条件表示只须满足其中一个或者多个。

4.作条件更新的时候，会根据条件过滤出来的数据去做更新，当条件描述为“且”，“并且”，“而且”，“和”时，往往就说明多个条件需要用and来连接，如果条件描述为“或”，“或者”等情况的时候就说明对个条件需要使用or来连接。

5.需要更新的多个字段之间使用的英文输入法下的逗号来分隔的，而多个条件表达式之间使用and或者or来连接的，并不是逗号。

示例：

将学生表里班级编号为1501并且名字叫小杨的同学的住址更新为“上海市浦东新区”：

Update student set address=“上海市浦东新区” where class_id=1501 and name=“小青”；

8. delete（drop）：删除表数据（删除库，表）

现实工作中我们可能也会碰到需要删除一些无效的测试数据，这种情况下我们可以通过delete命令来完成，下面我们会详细的学习删除SQL语法。至于删除数据库和删除表这几种情况在工作中出现的比较少，所以我会简单的带过一下语法。

有如下表数据，需要删除表的一部分数据，比如删掉班级编号为1501班的所有学生信息，该怎么写sql去完成，需要删除表里所有学生数据，又该如何完成。

其实刚提到的两种场景分别是：

1）删除表的所有数据

2）根据条件删除表里的一部分数据

这两种情况分别对应的语法是怎样的呢？看下面

8.1 条件删除

语法

delete from 表名 where 条件 A and(or) 条件B .......... and （or）条件N；

注意事项

1）多个条件之间是用and 还是or 来连接取于条件必须成立还是不需要同时成立，同时成立则用and来连接多个条件，不需要同时成立，则多个

条件之间使用or来连接

示例

删除学生表里班级编号为1502，并且姓名为梨子的学生数据

delete from student where class——id=1502 and name=’梨子’；

执行完后，查看数据，我们会发现，班级编号为1502并且姓名为梨子的同学被删除掉了

8.2 无条件删除

语法

delete from 表名

示例

删除学生表里的所有数据

delete from student;

8.3 删除数据库/表

在工作中删除库删除表是非常危险的动作，尤其是生产环境，也叫正式环境或者线上环境，如果数据库或者表被删除后就会很危险，不仅数据丢失了，而且项目也会因此而阻塞。当然也有人说这样的数据做下备份不就行了吗，但是真是环境上的数据每天都在更新的，所以频繁的做备份才能保证数据是最新的，所以大家在工作中基本上是使用不到这几个命令的。

删除数据库语法

drop database XX;

删除表语法

drop table YY;

总结：使用上面的语法 drop database xx 就可以删除XX数据库，当然这种情况表和数据也都不存在了使用 drop table YY就可以把YY表删除掉，同时这个表里的数据也会一并被删除，但是这种只删某个表的情况不会影响到其表和数据库。

9. select：查询表数据

在做项目的时候，我们出来页面上的功能测试有时候还需要去数据库复查数据的准确性，这也是我们在测试的工作中做的最多的数据库操作了，从表里面拿出我们想要的数据可能是从一个表取，也可能从多个表取，但是不管是从一个表还是多个表都需要用到select 查询命令去取，这里我们将针对单表数据查询和多表查询分别做语法和例子讲解。

9.1 单表查询

单表查询简单来讲就是从一个表里查询到你想要的数据，但其实从一个表里查数据也会涉及到多种情况，比如查询表里的某些特定信息（特定字段），查询表的所有信息（所有字段）

9.1.1 无条件查询

所谓无条件查询其实指的是在做查询的时候不做任何条件过滤，无条件查询又根据查询的内容来细分为两种情况。第一种是查询特定字段，第二种是查询所有字段。

9.1.1.1 查询特定字段

语法

select 字段 A ，字段B，....，字段N from 表名

注意事项

1)需要查询到的字段都必须声在select 关键字后

2）多个字段之间是使用的英文输入法下的逗号分开的，最后一个字段不需要逗号。

示例

有如下学生表：

如现在要查出来1502班的所有学生的学号和姓名，该怎么做呢？

selece id,nanefrom student where class_id=1502;

执行结果：

9.1.1.2 查询所有字段

语法

select * from 表名；

注意事项

1）查询表的所有字段信息的时候，可以把所有字段都写在select关键字后面，但是MySQL提供了另外一种更简单的方法，即就是用符号‘*’表示所有的字段。

示例

要查询出如下学生表中的所有学生信息该怎么做呢？

i. select id,name,sex,class_id,address,createtime,updatedate from student;

ii.select*from student;

两种写法都是可以的，但是我们发现第二张语法会相对简单许多。

9.1.2 条件查询

条件查询顾名思义就是在做查询的时候会根据给定的条件查询出满足条件的数据，比如，要在如下学生表中查询出1502班的学生信息。

对于上面的要求，我们可以分析得出条件是班级为1502.我们并不需要查询非1502班的学生信息。

语法

select ...from 表名 where 条件 A and （or）条件B and （or）...and(or) 条件N；

注意

1）select 后面怎么写，这里我就不再赘述，如果需求是要查特定字段，就把字段写在select 关键字后，如果是查询所有字段信息就用*代替。

2）条件表达式之间到底是用and还是or来连接，要根据具体情况来定，如果条件是需要同时成立则用and来连接，如果不需要同时成立则用or来连接。

9.2 多表查询

当你想要查询的数据数据是分布在了多个表，那该怎么查出来这些数据呢？这个其实就是咱们接下来要讲的多表查询。多表查询是整个数据库知识学习中是最难也是最重要的部分了，所以这里我们也会做重点讲解。但是，讲多表查询之前，我们首先得必须理解一个概念：笛卡尔积，因为我们做多表查询其实就是做的表数据的笛卡尔积。

9.2.1 笛卡尔乘积

所谓笛卡尔乘积就是将 表A 和 表B 的所有数据进行任意组合得到的数据集，假设A表3条记录，B表 2条记录，则 select * from A, B 就是 做了A 表和B 表数据的一个笛卡尔积，得到的结果就是六条数据。

示例

有如下年级表 （grade）和班级表（class）。

年级表：

班级表：

执行select * from grade ,class; 就会做一个笛卡尔乘积得到如下八条数据：

这8条记录是年级表的数据与班级表的数据一一组合得到的。这个就是笛卡尔积的原理。

9.2.2 关联字段

简单点来讲，关联字段有这么两个特点：

1.它是一个字段，存在于表中。

2.需要用到此字段才能将该表与其他表中的数据联系起来。比如有以下类型的数据：（高二年级，1501班，学1503001，小敏，女，语文80分，数学85分，英语94分，深圳市南山区家和苑小区），大家思考一下，这种类型的数据是直接放到一个表里保存就可以了还是需要设计多个表来维护更合适。按照咱们前面提到的一个基本原则，数据要有好的维护性，咱们应该就可以得出这样一个结论:上面的数据放在一个表维护不合适。因为这条数据包含了多部分信息,一部分是学生的信息,一部分是选课的信息,一部分是成绩的信息,而这些部分其实都是可以拆出来分别单独维护在学生表,课程表,成绩表的。那么,现在问题又来了,数据被拆分了,分数表里保存分数,课程表里保存课程,我如何知道小敏同学的课程和分数是什么。这个时候关联字段就派上用场了,可以在分数表维护一个字段 student id来保存学生的学号id,维护一个 course id来保存课程的编号id,这样就知道了每一个分数分别是哪一门课程,哪位学生的了。这里student id和 course id就是关联字段,而且关联字段一般是保存的其他表的字段的主键值,在有些情况下这些字段会被定义为外键,但更多的都只是做一个关联声明写在了数据库设计文档里。当然,肯定又有人想问了,我一个做测试的需要参与到表的设计工作中吗?可以肯定的告诉大家,是不需要的,设计是开发人员做的事情,测试人员不需要参与,那测试人员怎么知道表之间的关联字段呢?解决办法就是问开发要数据库设计文档,如果没有就可以直接问开发人员你要查数据的表之间的关系。

9.2.3.多表查询语法

语法

select 字段信息 from 表AB…, N where关联条件and过滤条件;

注意事项

1)这里查询字段可以是AB.等多个表的字段

2)查询涉及到的表都要写在from后面

3)关联条件即多个表通过关联字段关联起来的条件表达式

4)过滤条件是指在笛卡尔积的基础上进行进一步过滤的条件

示例

有如下年级表和班级表,请查出高一年级底下的所有班级信息年级表:

班级表:

分析过程:

1)查询涉及到的表: grade,cass

2)查询字段信息:没有指定特定的字段就直接用*来表示,显示两个表的所有字段

3)关联条件: grade. id= class grade id

4)过滤条件: grade. name=高一年级

所以最终得到的sq如下:

SELECT

FROM

grade

class

WHERE

grade. id class grade id

AND grade. NAME=’高一年级’;

大家注意上面的sq,字段前面都有指定表名,目的是为了区分字段来自于哪个表,因为你要查询的字段可能存在于多个表,如果不加表名指定是具体哪个表的字段就会有问题。比如,查询高一年级编号和高一年级下所有班级的编号,你可能会这么写:

SELECT

id

FROM

WHERE

grade. id= class grade id

AND grade.NAME=‘高一年级

你想用前面的d表示年级编号,后面id表示班级编号,看上去没啥问题,每个表都有一个id字段,但是真正运行的时候会报错----"模棱两可的列"：

5)有时候因为表名比较长,而字段前都加上表名说明就会导致最后sq会很长,很繁琐,有一个很好的办法就是给表指定一个别名,做法是在表的后面指定一个简短的名字,比如上面的sq我们就可以简化为:set, from gradet, class t2 where t1.id=t2. grade id and t1.name=‘高一年级’；

9.2.4.结果集排序

有时候我们会希望对查询到的数据做一个排序,比如查询学生表的数据时我们希望最终结果是按照学号从小到大来排列,或者从大到小来排列,就可以用到我们mysql中的排序“ order by”关键字来进行排序。

语法

select…from…. where… order by字段 A asc(desc)…,字段 N asc(dec)；

注意事项

1)排序的时候需要指定排序字段的排序规则,asc:升序,desc:降序,若没有指定则默认为升序。

2)这里做排序是对查询出来的数据结果进行排序

3)多个排序字段的用法是,先按第一个排序字段排序,如果出现字段的数据相同的情况就按第二个排序字段来排序,以此类推。

9.3.高级查询用法

学习了前面一章的单表查询和多表查询,基本上是可以满足工作的一个基本需要了,但是可能现实工作中还会碰到一些其他复杂的情形,下面我们来学习一下查询语句的其他高级用法。

9.4.查询语句结构

以下给大家总结了查询语句的结构,出现了一些结构导致的语法错误时可以来比对进行排查:

select

...

from

...

[where]

...

[group by]

...

[having]

...

[order by]

...

[limit]

...

9.4.1 掌握in的用法

使用场景:做条件查询的时候,条件字段的取值有多个的情况。

语法

select ... from ... where 条件字段 in (值A，值B ，... ,值N)；

注意事项

1)查询语法前面这一部分没什么变化,主要是看条件字段,条件字段与值之间是使用的in而不是“=”。

2)条件字段的多个值都写在一个小括号内,值与值之间是使用的逗号来分隔。

示例

查询学号为1501001或1501002或1502001的学生信息。

select.from student where id=1501001 or id=1501002 or id=1502001

对于上面这种id 取值多个的情况，我们就可以使用in 的语法来完成：

select * from student where id in(1501001,1501002,1502001);

我们可以看到两条sql 的效果是一样的，查询的数据完全相同。

拓展：

not in 的用法：条件字段的值不在某个数据集内的情况。

select * from student where id != 1501001 and id != 1501002 and id != 1502001;

对于上面这种id 取值多个的情况，我们就可以使用in的语法来完成：

我们可以看到两条sql的效果是一样的，查询数据完全相同。

拓展：

not in 的用法：条件字段的值不在某个数据集内的情况。

例如：查询学生表学号不为 1501001,1501002,1502001的学生信息

select * from where id != 1501001 and id !=1501002 and id !=1502001;

我们可以看到两条sql 的效果是一样的，查询的数据完全相同。

9.4.2 掌握模糊查询 like 的用法

使用场景：做条件查询的时候，条件字段的值不完整的情况。这里我们会学习两种模糊查询的语法，一种是使用“ %” 来做模糊匹配，一种是使用“_”来做模糊匹配。

9.4.2.1 使用“%”来模糊匹配

现在我们来考虑一下这些情况，假如我们只知道条件字段值的一部分为“XX” ，而这个XX可能出现在真实值的前半部分，后半部分，或者出现在中间位置。而mysql 提供了对应的匹配语法，%来匹配任意长度的字符串，并且出现的位置不同， 代表的意思不同。

语法

用%来匹配条件字段值的前半部分：select ... from ...where 条件字段 like '%XX';

用%来匹配条件字段值的后半部分：select ... from ...where 条件字段 like 'XX%';

用%来匹配条件字段值的两端部分：select ... from ...where 条件字段 like '%XX%';

注意事项

1）%可以匹配任意长度的字符串

2）条件字段与值是用like 来连接而非 "="

3）要根据实际情况来判断模糊匹配符的位置

示例

1）查询姓“小”的学生

select * from student where name like '小%';

2）查询名字以“青”字结尾的学生信息

select * from student where name like '%青';

3）查询名字里面有个“敏”字的学生信息

select * from student where name like '%敏%';

9.4.2.2 使用“_” 来模糊匹配

使用“_” 来进行模糊匹配跟“%”有一点不一样，因为“_”起到的是一个占位符的作用，一个“_”只能匹配一个任意的字符，而不是任意长度的字符串。语法跟“%”类似。

示例

找出学生表student 里名字为两个字，最后一个字为“青”的学生信息。

select * from student where name like '_青';

9.4.3 掌握统计 count() 的用法

使用场景：获取一个表里的所有数据的条数或者满足某些条件的数据的条数。

语法

获取表中的所有记录数：select count(*) from ...;

获取表中满足某些条件的记录数： select count(*) from ... where ... ;

注意事项

1）count()是一个mysql 函数，可以对具体的某个字段来进行统计：count(字段)，也可以直接用count(*)来统计，根据字段去统计的时候不会把此字段的值为空的记录统计进去。

2）如果是没有条件，那直接就是统计查询结果的记录条数。

3）如果有条件，那直接就是统计根据条件查询下的记录条数。

示例

有如下年级和班级表：

年级表：grade

班级表：class

请统计grade_id = 1的班级个数。

select count(*) from class where grade_id = 1;

9.4.4 掌握去重 distinct 的用法

使用场景：去除 查询结果中的重复数据

语法

select distinct 字段A ,...,字段N from ...;

注意事项

去重的时候支持多字段，如果只有一个字段，那就根据此字段去重，重复的数据都只会显示一条，最终拿到所有的不同数据。如果是多个去重字段，就会以多个字段作为组合去判断记录是否重复，重复记录只抽取一条，最终拿到所有不同的数据组合。

如果有多个去重字段，则字段之间使用逗号“，”来分隔

示例

如下是学生的成绩表，如何查出有成绩的学生学号，重复的学号只显示一条？

分析：

根据题意要获取成绩表里有成绩的学生学号信息，可以直接使用：select student_id from score; 然后我们就拿到了所有有成绩的学生的学号：

但是我们分析结果其实就会发现，有很多重复的学号，因为成绩表里有学生是选修了多门课程的。那我们该如何来过滤重复数据呢？这个时候distinct 就派上用场 了：

select distinct student_id from score;

得到结果如下：

总结：对于多字段去重的时候原理也是一样的，只不过在判断重复数据时是依照这多字段组合去判断的。

9.4.5 掌握between 的用法

使用场景：条件查询时，条件字段的值处于某两个值之间的情况。例如：查询成绩表中分数在85 到90分数的记录。根据题意其实就是获取大于等于85，小于等于90分的记录。我们首先想到的是通过这样一条sql来完成查询：select * from score where score>=85 and score

语法

select ... from ... where 字段A between 值A and 值B;

条件字段的值可以是数值，字符串也可以是日期时间

示例

查询成绩表中分数在85 到90 分数的记录

select * from score where score between 85 and 90;

9.4.6 掌握mysql 分页

使用场景：查询结果集记录太多需要分页来展示。大家应该见过分页，我们打开淘宝，输入“牛仔裤”就会搜索到成千上万的相关商品信息，这么多的商品信息在一个页面展示给买家肯定是不行的，页面会被拉的很长不说，用户体验也不好，一般网站的处理方式是将数据分页处理，显示第一页的数据，其他页的数据以页码的形式来提供查询入口，如下图：

如果每一页是显示10条数据，那么想查看第二页10条数据就直接点击页码数“2”，这个其实就是通过数据库的分页拿到的对应页数下的数据。这里我们假设一下淘宝使用的是mysql 的数据库，我们来看一下 mysql 数据库是怎么实现分页的。

语法

select ... from...limit m ,n;

注意事项

m：索引从m开始，第一条数据的索引为0 ，第二条为1，... ，第K条为 k-1

n：每一页取的记录条数，比如每页取十条，那n就是10

示例

每页取十条，第一页，二页，三页的分页表达式该怎么写呢？

分析：m代表的所在页数的第一条数据的索引，第一页的第一条数据索引为0，往后索引依次递增1，所以第一页的索引开始值m应该为0，n因为代表的是每页显示的数据条数，所以是10。这样我们就得到了第一页的分页表达式：

第一页：limit 0，10

第二页分析过程也是一样的，n的值还是一样，每页固定显示10条，那么n还是10，m值的分析可能会难一点，第二页第一条数据的索引值是什么取决于前面m的索引值已经到了几，前面第一页的索引值是从0开始到9（十条数据），所以第二页数据的索引值应该是从10开始取，取十条，索引取值分别为10~19，我们只用取开始的值10，这样我们又得到了第二页的分页表达式：

第二页：limit 10,10

以此类推：

第三页：limit 20,10

思考：

如果每页展示y条，那么第x页的分页语句是什么？

limit (x-1)*y,y

学以致用

查询学生表里年纪最大的三个学生信息

9.4.7 掌握mysql 常用函数

mysql提供了一系列丰富的函数，调用这些现成的函数来解决工作中的一些数据查询需求往往能收到事半功倍的效果，所以作为测试人员非常有必要了解一些常见的mysql函数。

9.4.7.1 数值类型函数

数值类型的函数主要用来操作数值，这里会学习几个比较常用的聚合函数，在后面学习分组的时候，需要用到聚合函数。

有以下学生表：

1）求一列的最小值：min()

这个函数主要是用来求某个字段所有数据里的最小值。比如，要求学生表里最小的年龄：

select min(age) from student;

2）求一列的最大值：max()

此函数的作用刚好跟min()函数相反，主要是用来求一列数据里的最大值。比如，要求学生表里最大的年龄：

select max(age) from student;

3）求一列的平均值：avg()

此函数的作用主要是用来求一列的平均值，比如要求学生表里学生的平均年龄：

select avg(age) from student;

4)求一列的和：sum()

此函数的作用主要是用来求一列的数据的和，比如要查询出来学生的年龄总和：

select sum(age) from student;

9.4.7.2 日期类型函数

日期数据处理起来比较麻烦，这时函数的优势就体现出来了，你不用去关注这些系统函数的底层实现是怎么样的，只用知道它的用法，会用它来实现你的工作需求就行了。

1）sysdate()

系统日期时间函数。可以通过这个函数快递获取到数据库服务器的当前日期时间（年 月 日 时 分 秒）。select sysdate();

2)curdate()函数

系统日期函数，可以通过此函数迅速获取到数据库服务器的当前日期（年 月 日）。

select curdate();

3)curtime()函数

系统时间函数，可以通过此函数迅速获取到数据库服务器的当前时间（时 分 秒）

select curtime();

4）year()

根据指定日期获取日期的年份值。

例如：请获取当前系统日期时间的年份值。

select year(curdate());

5)month()函数

根据指定日期获取日期的月份值

例如：请获取当前系统时间的月份值

select month(curdate());

6)day()

根据指定日期获取日期里的天数是当月的第几天

例如：请获取当前系统时间的天数是当月的第几天

select day(curdate());

7)date_add()

使用此函数可以在某个日期的基础上增加一个时间间隔

用法：date_add(date,internal,expression,unit)

例如：在当前系统日期时间的基础上加一天

select date_add(curdate(),interval 1 day);

8)date_sub()函数

使用此函数可以在某个日期的基础上减去一个时间间隔

用法：date_sub(date,internal,expression,unit)

例如：在当前系统日期时间的基础上减去一天

select date_sub(curdate(),interval 1 day);

9.4.7.3 字符串函数

工作情况中，对于字符串的操作也比较多，下面将重点介绍几个常用的操作字符串的函数

concat()函数

使用此函数可以将多个常量值拼接起来，也可以将多个字段的数据拼接起来。

1）用concat()函数将多个常量值拼接起来

例如：select concat('a','b','c',1,2,3);

执行结果：

2）将表多个字段的数据拼接起来

例如：有如下学生表

要将学号和姓名拼接起来可以这么做：

select concat(id,name) from student;

substr() 函数

substr()是一个截取数据的函数，有多种用法，这里重点介绍两种常用的用法。

select substr(str,pos);

select substr(str,pos,len);

str代表的是要操作的字符串，pos 代表的是开始截取的位置，len代表的要截取的长度，如果没有指定len,就默认从 pos 的位置截取到最后一位。

例如：

查询学生学号的前四位。

select substr(id,1,4) from student;

length()函数

获取一个值的长度，一般应用在字符串类型字段上的情况较多，这里我们也分两种情况来说明一下这个函数的用法。

1）应该在字符串常量上

select length('helloworld');

2)应用在字段上

例如我们现在想获取学生表里每个学生学号的长度：

select length(id) from student;

9.4.8 掌握分组 group by 的用法

使用场景：group by 顾名思义就是根据什么来分组的意思，一般是一个，或者多个字段来做分组，它必须有“聚合函数”来配合才能使用，使用时至少需要一个分组字段。什么是分组，其实概念也很好理解，比如上体育玩游戏老师要求大家分组，男生站左手边，女生站右手边。那这里是根据什么来分组的呢？没错，性别。如果再让你统计男女分别是多少人，就需要用上统计的聚合函数 count。

语法

select 字段A，字段B，...，字段N 聚合函数 from ...group by 字段A，字段B，...字段N;

注意事项

1）查询信息里必须要有聚合函数：对一组执行计算并返回单一的值的函数。常见的聚合函数有：sum()、count()、avg()、min()、max() 等。

2）group by后面的分组字段要跟select 后面字段保持一致。

3）如果要在分组的基础上做进一步的过滤，就要使用having + 过滤条件来完成。

示例

1）统计学生表里男女生分别是多少人。

分析：题目里面并没有出现分组这样的字眼，但是题意是要统计人数，所以会用到count()函数，看到聚合函数就想到可能是一个分组，再者，我们看到是分别对男女生统计人数，就能够坚定下来这个就是一个分组，根据性别来分组，分别统计男女生的人数。

答案：

select

count(*)

gender

from

student

group by

gender;

2) 统计每个年级的学生人数（显示年级名称，年级人数）

年级表：

班级表：

学生表：

分析：要统计每个年级的学生人数，我们只需要确认学生表里每个学生的班级编号哪些是高一年级的班级，哪些是高二年级的班级，哪些是高三年级的班级。按照这个思路，我们通过关联班级表就知道了每个学生是属于哪个班级，关联字段（student.class_id = class.id）,但是并不知道这个班级是属于哪个年级，所以进一步知道此班级是属于哪个年级就需要继续关联年级表，关联字段（class.grade_id = grade.id）。这样将表数据关联起来后最后再根据年级编号和年级名称来分组统计学生人数就达到了我们的目的。

答案：

select

t3.id,

t3.name,

count(*)

from

student t1,

class t2,

grade t3

where

t1.class_id = t2.id

and

t2.grade_id = t3.id

group by

t3.id;

10. 内连接

作为多表之间的一种关联方式，内连接的特点是只返回满足关联条件，两表都存在的数据。内连接有多种方式，前面我们在学习多表关联查询的时候，就是一种内连接的写法。例如有以下部门表department,员工表employee。

department表：（dno:部门编号，dname:部门名称）

employee 表：（eno:员工编号，dno:员工所在部门编号，ename:员工姓名,egender:员工性别）

现在，有个需求，请用sql 查询出每个部门编号，名称，以及对应部门下员工的编号，员工姓名，性别信息，并按部门编号升序，员工编号升序排列。利用咱们前面学习过的关联查询语法，写出这条sql并不难：

select

t1.dno,

t1.dname,

t2.eno,

t2.ename,

t2.egender

from

department t1,

employee t2

where

t1.dno = t2.dno

order by

t1.dno,

t2.eno;

我们来分析一下这个结果，这几个员工对应的部门编号既存在于部门表又存在于员工表，刚好满足内连接的特性，所以咱们前面学习的关联查询的语法是一种内连接的语法。下面我们就来学习一下另外一种内连接的语法。

语法

select ... from 表A inner join 表B on 关联条件 1 inner join 表 C on 关联条件2 ... inner join 表 n on 关联条件n；

注意事项

1）表A inner join B 也是做的笛卡尔积

2）关联条件为两个表的关联字段，关联条件的作用是在笛卡尔乘积的基础上做进一步过滤

3）inner join只能返回两个表都存在的数据

示例

使用 inner join 的语法实现上面的需求

select

t1.dno,

t1.dname,

t2.eno,

t2.ename,

t2.egender

from

department t1

inner join employee t2 on t1.dno=t2.dno;

11. 外连接

外连接作为不同于内连接的关联方式，主要区别在于根据关联字段做查询的时候，对于不满足条件的数据也可以查询出来。而外连接又细分了左外连接和右外连接，下面我们来详细的来看一下这两种外连接的区别是什么。

11.1 左外连接

左外连接不同于内连接，即使不满足关联条件的数据也能查询出来，这种连接方式可能刚好满足咱们工作中的某种需求。

语法

select ... from 表A left join 表B on 关联条件 1 left join 表C on 关联条件 2 ... left join 表N on 关联条件n；

注意事项

1）关联条件为两表之间的关联字段

2）做外连接左边的表为主表，右边的表为从表

3）不管条件满足不满足，主表的数据都会全部返回，从表不满足的数据会以null显示

示例

请使用左外连接实现上面的请求：

select

t1.dno,

t1.dname,

t2.eno,

t2.ename,

t2.egender

from

department t1

left join employee t2 on t1.dno = t2.dno;

因为部门表是主表，所以部门表里的数据都会查询出来。我们发现部门表1003 这个部门并不满足关联条件，但是查询结果照样显示了，它的员工信息以null值显示。

11.2 右外连接

右外连接跟左外连接差别不大，右外连接以右表为主表，左表为从表，右表的所有数据都显示。

语法

select...from 表A right join 表B on 关联条件 1 right join 表C on 关联条件2 ... right join 表n on 关联条件n;

注意事项

1）右连接主表为右表

2）不管关联条件满不满足，主表的数据都会全部返回，从表不满足的数据会以null显示

示例

select

t1.dno,

t1.dname,

t2.eno,

t2.ename,

t2.egender

from

department t1

right join employee t2 on t1.dno = t2.dno;

我们可以看到所有右表 employee的所有数据都查出来了，即使16004这个员工的部门编号并不满足关联条件，它的部门信息以null显示。