项目5学生成绩管理数据库表数据查询

1、项目项目5 学生成绩管理数据库表数据学生成绩管理数据库表数据的查询的查询1. 能利用查询语句进行简单查询能利用查询语句进行简单查询 ；2. 能利用查询语句进行能利用查询语句进行多表查询、嵌套查询多表查询、嵌套查询 ；3. 能利用能利用聚合函数进行简单汇总；聚合函数进行简单汇总；4. 能结合查询进行数据记录的添加、修改、删除等操作。能结合查询进行数据记录的添加、修改、删除等操作。能力目标能力目标项项目目描描述述数据库的最大功能之一是查询，在学生成绩管理系统中用到的最多也是查询。例如，要查询姓名为“张大山”的信息；或者要查询姓名为“张大山”所修课程及对应成绩的信息等等。所以本项目首先通过学生表、成

2、绩表、课程表等记录的查询，学会数据表记录的基本查询方法；其次通过学生表、成绩表等几个表的连接查询、子查询等学会高级查询的方法；然后再通过对学生表、成绩表等表的查询结果更新相应的表数据，学会利用查询结果更新表数据的方法；最后通过课堂实训、课外实训来加强对数据库表数据的灵活查询的能力。学学习习任任务务任务5.1：学生成绩管理数据库单表查询任务5.2：学生成绩管理数据库多表查询任务5.3：用学生成绩管理数据库数据查询结果更新表数据项目描述项目描述及及学习任务学习任务5.1 5.1 学生成绩管理数据库单表查询学生成绩管理数据库单表查询数据查询是对数据库中的数据按指定内容和顺序进行检索输出。它可以对数据

3、数据查询是对数据库中的数据按指定内容和顺序进行检索输出。它可以对数据源进行各种组合，有效地筛选记录、管理数据，并对结果进行排序；它可以让源进行各种组合，有效地筛选记录、管理数据，并对结果进行排序；它可以让用户以需要的方式查询数据表中的数据，可以控制查询数据表中的字段、记录用户以需要的方式查询数据表中的数据，可以控制查询数据表中的字段、记录以及显示记录的顺序等。数据查询是数据库的核心操作。本学习任务主要介绍以及显示记录的顺序等。数据查询是数据库的核心操作。本学习任务主要介绍数据库单表查询语句。数据库单表查询语句。任务描述任务描述 5.1.1 简单查询简单查询数据库查询数据库查询是数据库的核心操作

4、。数据查询是用来描述怎样从数据库中获取所是数据库的核心操作。数据查询是用来描述怎样从数据库中获取所需要的数据。查询会产生一个虚拟表，即看到的是表形式显示的结果，但结果需要的数据。查询会产生一个虚拟表，即看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是从数据表中提取数据，并按照表的形式显示并不真正存储，每次执行查询只是从数据表中提取数据，并按照表的形式显示出来。如图出来。如图5-15-1所示。所示。Application Logic客户程序查询请求查询结果集SQL SERVERA B C D E F G 图图5-1 查询过程查询过程简单查询指的是在一个数据表中查询所需的数据。简单查

5、询指的是在一个数据表中查询所需的数据。1 1格式格式：SELECTSELECT All | Distinct Top n Percent All | Distinct Top n Percent FROMFROM WHERE WHERE ORDER BY ORDER BY ASCASC或或DESCDESC带有方括号的子句是可以选择的。带有方括号的子句是可以选择的。 指所查询列，它可以由一组列名列表、星号、表达式等构成。指所查询列，它可以由一组列名列表、星号、表达式等构成。All | Distinct All All | Distinct All 表示所有行，表示所有行，Distinct Dis

6、tinct 表示过滤重复行，默认为所有表示过滤重复行，默认为所有行。行。 Top n Percent Top n Percent 表示返回的行数，表示返回的行数，Top n Top n 表示显示前表示显示前n n行，行，Top n Top n PercentPercent表示显示前百分比行。表示显示前百分比行。From From ：对单表查询，只需给出一个表名。：对单表查询，只需给出一个表名。Where Where 是筛选条件是筛选条件ORDER BY ORDER BY 是对指定的列进行排序，是对指定的列进行排序，ASCASC表示升序，表示升序，DESCDESC表示降序。表示降序。5.1.1

7、简单查询简单查询5.1.1 5.1.1 简单查询简单查询若想查询若想查询“学生表学生表”中专业为应用电子的前中专业为应用电子的前5 5条信息（按性别排序），将它按格条信息（按性别排序），将它按格式中分类，则如表式中分类，则如表5-15-1所示。所示。选择列限制固定行数表名过滤条件排序条件Select学号,姓名Top 5From学生表Where专业=应用电子Order by性别表表5-1“学生表学生表”中专业为应用电子的前中专业为应用电子的前5条信息查询格式分类表条信息查询格式分类表即：即：Select Top 5 学号学号,姓名姓名 From 学生表学生表 Where 专业专业=应用电应用电子

8、子 order by 性别性别2. 2. 应用应用（1）查询选择部分列，并指定它们的显示次序）查询选择部分列，并指定它们的显示次序【例例5.1】查询查询“学生表学生表”中所有学生学号中所有学生学号,姓名姓名,性别性别,专业的信息。专业的信息。在查询编辑器中输入：在查询编辑器中输入：select 学号学号, 姓名姓名, 性别性别, 专业专业 from 学生表学生表或者或者: select all 学号学号, 姓名姓名, 性别性别, 专业专业 from 学生表学生表点击点击“执行执行”，则会出现如图所示结果。，则会出现如图所示结果。注意：列名之间是用逗号隔开的。注意：列名之间是用逗号隔开的。2.

9、2. 应用应用任务描述任务描述 （2）查询所有列）查询所有列用用“ * ”表示所有列或将表中的所有列一一列出。表示所有列或将表中的所有列一一列出。【例例5.2】查询查询“学生表学生表”所有学生的详细信息。所有学生的详细信息。select * from 学生表学生表或或 select 学号学号, 姓名姓名, 性别性别, 专业专业, 出生年月出生年月, 家庭地址家庭地址, 联系电话联系电话, 总学分总学分 from 学生表学生表执行，则出现如图的结果。执行，则出现如图的结果。2. 2. 应用应用任务描述任务描述 （3 3）查询经过计算的值）查询经过计算的值【例例5.3】查询查询“学生表学生表”所有

10、学生的学号，姓名，性别，专业，年龄，家所有学生的学号，姓名，性别，专业，年龄，家庭地址，联系电话。庭地址，联系电话。select 学号学号, 姓名姓名, 性别性别, year(getdate()-year(出生年月出生年月), 家庭地址家庭地址, 联系联系电话电话 from 学生表学生表执行结果如图所示。执行结果如图所示。注意：由于注意：由于“学生表学生表”中只有中只有“出出生年月生年月”的字段，而无的字段，而无“年龄年龄”字字段，但是通过计算可以得到年龄。段，但是通过计算可以得到年龄。由于是计算得到的列，在原先的表由于是计算得到的列，在原先的表中无此列，所以显示中无此列，所以显示“无列名无列

11、名”。2. 2. 应用应用SQL ServerSQL Server中的函数分：字符串函数、日期函数、数学函数、系统函数。表中的函数分：字符串函数、日期函数、数学函数、系统函数。表5-25-2至表至表5-55-5是部分常用的是部分常用的SQL ServerSQL Server函数。函数。表5-2 日期函数函数名函数名描述描述举例举例getdate取得当前的系统日期Select getdate( )返回：今天的日期dateadd在日期中添加或减去指定的时间间隔Select dateadd(mm, 4, 03/01/1999)返回：以当前的日期格式返回07/01/1999year返回日期表达式中的年

12、份Select year(01/10/2014)返回：2014month返回日期表达式中的月份Select month(2014-11-13)返回：11datename日期中指定日期部分的字符串形式Select datename(dw, 12/03/2013)返回：星期二2. 2. 应用应用任务描述任务描述 表表5-3 字符串函数字符串函数函数名函数名描述描述举例举例LEN返回传递给它的字符串长度SELECT LEN(SQL Server课程)返回：12LOWER把传递给它的字符串转换为小写SELECT LOWER(SQL Server课程)返回： SQL Server课程UPPER把传递给它

13、的字符串转换为大写SELECT UPPER(sql server课程)返回：SQL SERVER课程LTRIM清除字符左边的空格SELECT LTRIM ( 周智宇 )返回：周智宇 （后面的空格保留）RTRIM清除字符右边的空格SELECT RTRIM ( 周智宇 )返回： 周智宇（前面的空格保留）RIGHT从字符串右边返回指定数目的字符SELECT RIGHT(买卖提.吐尔松,3)返回：吐尔松REPLACE替换一个字符串中的字符SELECT REPLACE(莫乐可切.杨可,可,兰)返回：莫乐兰切.杨兰STUFF在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串SELECT ST

14、UFF(ABCDEFG, 2, 3, 我的音乐我的世界)返回：A我的音乐我的世界EFG2. 2. 应用应用任务描述任务描述 表表5-4 数学函数数学函数函数名函数名描述描述举例举例ABS取数值表达式的绝对值SELECT ABS(-43)返回：43CEILING返回大于或等于所给数字表达式的最小整数SELECT CEILING(43.3)返回：44FLOOR取小于或等于指定表达式的最大整数SELECT FLOOR(43.5)返回：43POWER取数值表达式的幂值SELECT POWER(5,2)返回：25ROUND将数值表达式四舍五入为指定精度SELECT ROUND(43.543,1)返回：4

15、3.5SIGN对于正数返回+1，对于负数返回-1，对于0 则返回0SELECT SIGN(-43)返回：-1SQRT取浮点表达式的平方根SELECT SQRT(9)返回：32. 2. 应用应用任务描述任务描述 表表5-5 系统函数系统函数函数名函数名描述描述举例举例CONVERT用来转变数据类型SELECT CONVERT (VARCHAR (5)，12345)返回：字符型12345CURRENT_USER返回当前用户的名字SELECT CURRENT_USER返回：登录的用户名DATALENGTH返回用于指定表达式的字节数SELECT DATALENGTH (中国A盟)返回：7HOST_NA

16、ME返回当前用户所登录的计算机名字SELECT HOST_NAME()返回：所登录的计算机的名字SYSTEM_USER 返回当前所登录的用户名称SELECT SYSTEM_USER返回：当前所登录的用户名USER_NAME从给定的用户I D返回用户名SELECT USER_NAME(1)返回：从任意数据库中返回“dbo”2. 2. 应用应用任务描述任务描述 （4 4）更改列标题）更改列标题【例例5.45.4】将将【例例5.35.3】中的年龄要求显示列标题。中的年龄要求显示列标题。select 学号学号, 姓名姓名, 性别性别, 专业专业, 年龄年龄=year(getdate()-year(出生

17、年月出生年月), 家庭家庭地址地址, 联系电话联系电话 from 学生表学生表执行结果如图所示。执行结果如图所示。2. 2. 应用应用任务描述任务描述 （4 4）更改列标题）更改列标题【例例5.45.4】将将【例例5.35.3】中的年龄要求显示列标题。中的年龄要求显示列标题。select 学号学号, 姓名姓名, 性别性别, 专业专业, 年龄年龄=year(getdate()-year(出生年月出生年月), 家庭家庭地址地址, 联系电话联系电话 from 学生表学生表执行结果如图所示。执行结果如图所示。也可以写成：也可以写成：select 学号学号, 姓名姓名, 性别性别,专业专业, year(

18、getdate()-year(出生年月出生年月) as 年龄年龄, 家庭家庭地址地址, 联系电话联系电话 from 学生表学生表或或select 学号学号, 姓名姓名, 性别性别,专业专业, year(getdate()-year(出生年月出生年月) 年龄年龄, 家庭地家庭地址址, 联系电话联系电话 from 学生表学生表即可以写成：即可以写成：“列别名列别名=表达式表达式”，或，或“表达式表达式 as 列别名列别名”，或，或 “表达式表达式 列别名列别名”。2. 2. 应用应用任务描述任务描述 （5 5）过滤重复行）过滤重复行Select 语句中使语句中使用用all或或distinct选项来

19、显示表中符合条件的所有行或过滤其选项来显示表中符合条件的所有行或过滤其中重复的数据行，默认为中重复的数据行，默认为all 。使用。使用distinct选项时，对于所有重复的数据选项时，对于所有重复的数据行在行在select返回的结果集合中只保留一行。返回的结果集合中只保留一行。【例例5.55.5】显示显示“学生表学生表”中所有的专业（不要出现重复专业）。中所有的专业（不要出现重复专业）。select distinct 专业专业 from 学生学生表表执行结果如图所示。执行结果如图所示。说明：说明：distinct是过滤掉重复的列。即是过滤掉重复的列。即若执行：若执行：select 专业专业 f

20、rom 学生表，学生表，则显示的结果中多次重复出现相同的专则显示的结果中多次重复出现相同的专业。业。2. 2. 应用应用任务描述任务描述 （6 6）限制返回的行数）限制返回的行数使使用用top n percent选项限制返回的数据行数，选项限制返回的数据行数， top n 说明返回说明返回n行，而行，而top n percent 时，时，n表示一百分数，指定返回的行数等于总行数的百分之几。表示一百分数，指定返回的行数等于总行数的百分之几。【例例5.6】显示显示“学生表学生表”中前三位的专业（不要出现重复专业）。中前三位的专业（不要出现重复专业）。select distinct top 3 专业

21、专业 from 学生表学生表执行结果如图所示。执行结果如图所示。说明：说明：Distinct是过滤掉重复是过滤掉重复的列，的列，top 3是前三行。是前三行。2. 2. 应用应用任务描述任务描述 【例例5.75.7】显示显示“学生表学生表”中前中前60%60%的专业（不要重复出现专业）。的专业（不要重复出现专业）。select distinct top 60 percent 专业专业 from 学生表学生表执行结果如图所示。执行结果如图所示。说明：说明：Distinct是过滤掉重复是过滤掉重复的列，的列，top 60 Percent是前是前60%行。行。2. 2. 应用应用任务描述任务描述 （

22、7 7）查询满足条件的记录）查询满足条件的记录查询满足条件的记录用查询满足条件的记录用where 子句，子句， where 子句常用查询条件中的运算符子句常用查询条件中的运算符见表见表5-6表表5-6 常用的查询运算符常用的查询运算符运算符运算符含义含义运算符运算符含义含义运算符运算符含义含义大于大于 不大于不大于!模糊查询模糊查询LikeLikeNot LikeNot Like大于等于大于等于=不小于不小于!空值空值Is NullIs Null等于等于= =在某一范围在某一范围Between andBetween and非空值非空值IS Not NullIS Not Null小于小于 不在某

23、一范围不在某一范围Not Between andNot Between and非非Not Not 小于等于小于等于=指定集合指定集合InIn并且并且AndAnd不等于不等于!=!=；不属于指定集合不属于指定集合Not InNot In或或OrOr2. 2. 应用应用任务描述任务描述 比较运算符比较运算符【例【例5.85.8】显示】显示“学生表学生表”中计算机系学生的中计算机系学生的信息。信息。select * from 学生表学生表 where 专业专业 = 计算机应用计算机应用执行结果如图所示。执行结果如图所示。2. 2. 应用应用任务描述任务描述 【例例5.95.9】查询查询“选课表选课表

24、”中考试成绩小于中考试成绩小于7070分的选课信息。分的选课信息。select * from 选课表选课表 where 成绩成绩 = 70说明：显示结果中将成绩说明：显示结果中将成绩小于等于小于等于70分的记录全都分的记录全都显示，可能会出现一个同显示，可能会出现一个同学有几门课成绩都在学有几门课成绩都在70分分以下，像图中的第以下，像图中的第4行、行、第第5行。行。有时，即使满足条件的记有时，即使满足条件的记录有多条，也只要显示一录有多条，也只要显示一条就行，这样就要用到条就行，这样就要用到Distinct。2. 2. 应用应用任务描述任务描述 【例【例5.105.10】查询】查询“选课表选

25、课表”中考试成绩小于中考试成绩小于7070分的同学。分的同学。select distinct 学号学号 from 选课表选课表 where 成绩成绩 =80 and 成绩成绩=90说明：二者之间可以用说明：二者之间可以用betweenand，也可以用，也可以用and2. 2. 应用应用任务描述任务描述 Not Between Not Between and and 运算符（二者之外）运算符（二者之外）【例【例5.155.15】查询】查询“选课表选课表”中课号为中课号为10011001，学习成绩不在，学习成绩不在8080到到9090分之间的同学信息。分之间的同学信息。select * from

26、选课表选课表 where 课程号课程号=1001 and 成绩成绩 not between 80 and 90或或select * from 选课表选课表 where 课程号课程号=1001 and (成绩成绩 90)说明：不在二者之间，可以用说明：不在二者之间，可以用not between and 也可以用也可以用or 2. 2. 应用应用任务描述任务描述 is null is null，is not nullis not null运算符运算符 - -空或非空查询空或非空查询【例【例5.165.16】查询】查询“课程表课程表”中备注为中备注为nullnull的课程信息。的课程信息。selec

27、t * from 课程表课程表 where 备注备注 is null执行命令，则结果如图所示。执行命令，则结果如图所示。2. 2. 应用应用任务描述任务描述 【例【例5.175.17】查询】查询“课程表课程表”中备注为非中备注为非nullnull的课程信息。的课程信息。select * from 课程表课程表 where 备注备注 is not null执行命令，则结果如图所示。执行命令，则结果如图所示。注意：观察注意：观察is null及及is not null的用的用法，这里法，这里is运算符运算符不能用不能用“=”来代替，来代替，is not也不能用也不能用“”或或“!=”来代来代替。替

28、。2. 2. 应用应用任务描述任务描述 Li Lik ke,Not Likee,Not Like运算符运算符有时查询数据时，不知道查询的范围，只知道查询的模式。这个时候，经常有时查询数据时，不知道查询的范围，只知道查询的模式。这个时候，经常用到用到LikeLike，Not LikeNot Like，其格式为：，其格式为：列名列名 Not Like Not Like 表表5-7 Like 匹配通配符及说明匹配通配符及说明通配符通配符说说 明明_表示任意单个字符。例如表示任意单个字符。例如a_b表示以表示以a开头，以开头，以b结尾的结尾的长度为长度为3的任意字符串。的任意字符串。例如：例如：adb

29、，afb等。等。%表示任意长度的字符（长度可以是表示任意长度的字符（长度可以是0的字符串）。例如的字符串）。例如a%b表表示示以以a开头，以开头，以b结属的任意长度的字符串结属的任意长度的字符串。例如：。例如：ab，acb，asdfb等。等。 表示方括号中列出的任意一个字符。例如：表示方括号中列出的任意一个字符。例如：asdfg，表示，表示a，s，d，f，g中的任意一个中的任意一个。也可以是字符范围，例如。也可以是字符范围，例如abcdef同同a-f的含义一样。的含义一样。表示不在方括号中列出的任意一个字符。例如：表示不在方括号中列出的任意一个字符。例如：asdfg之外之外的任意字符。的任意字

30、符。2. 2. 应用应用任务描述任务描述 【例【例5.185.18】查询】查询“学生表学生表”中姓中姓“李李”的同学的信息。的同学的信息。select * from 学生表学生表 where 姓名姓名 like 李李%执行命令，则结果如图所示。执行命令，则结果如图所示。说明：通配符说明：通配符字符串字符串李李%表示是第一个表示是第一个汉字是汉字是“李李”的字符串。的字符串。2. 2. 应用应用任务描述任务描述 【例【例5.195.19】查询】查询“学生表学生表”中姓名中间一个是中姓名中间一个是“明明”的同学的信息。的同学的信息。select * from 学生表学生表 where 姓名姓名 l

31、ike _明明_执行命令，则结果如图执行命令，则结果如图5-205-20所示。所示。说明：通配符字说明：通配符字符串符串 _明明_表示表示是中间汉字是是中间汉字是“明明”的字符串。的字符串。2. 2. 应用应用任务描述任务描述 【例【例5.205.20】查询】查询“学生表学生表”中姓名中间一个是中姓名中间一个是“海海”或或“建建”的同学的信息。的同学的信息。select * from 学生表学生表 where 姓名姓名 like _海建海建_执行命令，则结果如图所示。执行命令，则结果如图所示。说明：通配符字说明：通配符字符串符串_海建海建_表表示中间汉字是示中间汉字是“海海”或或“建建”的字符

32、串。的字符串。2. 2. 应用应用任务描述任务描述 【例【例5.215.21】查询】查询“学生表学生表”中不姓中不姓“李李”、“张张”、“王王”、“陈陈”的同学的信息。的同学的信息。select * from 学生表学生表 where 姓名姓名 like 李张王陈李张王陈%或：或：select * from 学生表学生表 where 姓名姓名 not like 李张王陈李张王陈%执行命令，则结果如图所示。执行命令，则结果如图所示。说明：通配符字符串说明：通配符字符串李张王陈李张王陈%表示第一个汉字不是表示第一个汉字不是“李李”或或“张张”或或“王王”或或“陈陈”的字符串。的字符串。2. 2.

33、应用应用（8 8）对查询的结果进行排序）对查询的结果进行排序可以使用可以使用Order byOrder by子句对查询结果按照一个或多个属性列的升序子句对查询结果按照一个或多个属性列的升序(ASC)(ASC)或降序或降序(DESC)(DESC)排序，默认为升序。如果不使用排序，默认为升序。如果不使用order byorder by子句，则结果集按照记录在表中子句，则结果集按照记录在表中顺序排序。顺序排序。Order byOrder by子句的语法格式如下：子句的语法格式如下：Order by Order by 列名列名 ASC|DESC,nASC|DESC,n【例例5.22】查询查询“学生表学

34、生表”中应用中应用电子专业同学的信息，按出生年月电子专业同学的信息，按出生年月降序排序。降序排序。select * from 学生表学生表 where 专业专业=应用电子应用电子 order by 出生年月出生年月 desc执行命令，则结果如图所示。执行命令，则结果如图所示。说明：说明：order by 出生年月出生年月 desc 是按是按出生年月的降序排序。出生年月的降序排序。2. 2. 应用应用任务描述任务描述 【例【例5.235.23】查询】查询“学生表学生表”应用电子专业和应用英语专业同学的信息，要求先应用电子专业和应用英语专业同学的信息，要求先按性别排序（升序），再按出生年月降序排序

35、。按性别排序（升序），再按出生年月降序排序。select * from 学生表学生表 where 专业专业 in(应用电子应用电子,应用英语应用英语) order by 性别性别 asc,出生年月出生年月 desc其中：其中：order by 性别性别 asc,出生年月出生年月 desc表示表示是先按性别升序，再按出是先按性别升序，再按出生年月降序排序生年月降序排序5.1.2 5.1.2 对数据进行统计对数据进行统计任务描述任务描述 【例【例5.245.24】计算】计算“选课表选课表”中课中课程号为程号为10011001课程的最低分、最高分同学的信息。课程的最低分、最高分同学的信息。selec

36、t min(select min(成绩成绩),max(),max(成绩成绩) from ) from 选课表选课表 where where 课程号课程号=1001=1001执行命令，则结果如图所示。执行命令，则结果如图所示。1使用聚合函数进行查询使用聚合函数进行查询用户经常需要对结果集进行统计，例如求和、平均、最大值、最小值、个数等，这用户经常需要对结果集进行统计，例如求和、平均、最大值、最小值、个数等，这些统计可能通过聚合函数来实现。些统计可能通过聚合函数来实现。这里这里min()、max()就是聚合函数，常用的聚合函就是聚合函数，常用的聚合函数如表数如表5-8所示。因为所示。因为min(成

37、绩成绩)、max(成绩成绩)是是计算得到的列，所以显示是计算得到的列，所以显示是“无列名无列名”可以给定一个别名，如：可以给定一个别名，如：select min(成绩成绩)as 最低分最低分,max(成绩成绩)as 最高分最高分 from 选选课表课表 where 课程号课程号=10012. 2. 应用应用任务描述任务描述 【例【例5.255.25】查询】查询“选课表选课表”中课程号为中课程号为10011001课程的平均成绩。课程的平均成绩。select AVG(成绩成绩) as 平均成绩平均成绩 from 选课表选课表 where 课程号课程号=1001执行命令，则结果如图执行命令，则结果如

38、图所示。所示。2. 2. 应用应用任务描述任务描述 【例例5.265.26】查询查询“教师表教师表”中教师总人数及有联系电话的教师人数。中教师总人数及有联系电话的教师人数。select count(*) as 总人数总人数,count(联系电话联系电话) as 有联系电话人数有联系电话人数 from 教师表教师表执行命令，则结果如图所示。执行命令，则结果如图所示。说明：说明：count(*) as 总人数：表示总记总人数：表示总记录数；录数；count(联系电话联系电话) as 有联系电有联系电话人数：表示联系电话不为话人数：表示联系电话不为null的总的总记录数。记录数。5.1.2 5.1.

39、2 对数据进行统计对数据进行统计任务描述任务描述 （1 1）格式：）格式：SELECT SELECT FROM FROM WHERE WHERE Group By Group By 列名列名 Having Having 2对结果进行分组对结果进行分组有时，要统计不同类别的数据，例如统计选课表中每门课程的最低分，最高分，平有时，要统计不同类别的数据，例如统计选课表中每门课程的最低分，最高分，平均分，或每个同学的平均分、总分等等，这时就要用到均分，或每个同学的平均分、总分等等，这时就要用到Group By子句。子句。Group By子句是将查询结果集按某一列或多列值分组，并对每一组进行统计。子句是

40、将查询结果集按某一列或多列值分组，并对每一组进行统计。其中：其中：Group By 列名：是按列名指定的字段进行分组，将该字段值相同的记录组成一列名：是按列名指定的字段进行分组，将该字段值相同的记录组成一组，对每一组记录进行汇总统计并生成一列记录。组，对每一组记录进行汇总统计并生成一列记录。注意：注意：SELECT 的列名必须是的列名必须是Group By 列名已有的列名或计算列。列名已有的列名或计算列。（2 2） 应用应用任务描述任务描述 【例例5.275.27】查询查询“选课表选课表”中每个同学的平均分。中每个同学的平均分。select 学号学号,AVG(成绩成绩) as 平均分平均分 f

41、rom 选课表选课表 group by 学号学号执行命令，则结果如图所示。执行命令，则结果如图所示。说明：因为是求每个同学的平均分，说明：因为是求每个同学的平均分，所以是按学号分组的，即所以是按学号分组的，即Group by 学号，因而学号，因而Select 子句中的出子句中的出现的列名必须是现的列名必须是Group By中出现中出现的学号及平均分。的学号及平均分。（2 2） 应用应用任务描述任务描述 【例【例5.285.28】查询】查询“选课表选课表”中每个同学的最高分、最低分、平均分。中每个同学的最高分、最低分、平均分。select 学号学号,MAX(成绩成绩) as 最高分最高分,MIN

42、(成绩成绩) as 最低分最低分,AVG(成绩成绩) as 平均平均分分 from 选课表选课表 group by 学号学号执行命令，则结果如图所示。执行命令，则结果如图所示。（2 2） 应用应用任务描述任务描述 【例【例5.305.30】查询】查询“选课表选课表”中平均分高于中平均分高于7575分的每分的每门课程门课程的最高分、最低分、总的最高分、最低分、总分及平均分，并按平均分的高低进行排序分及平均分，并按平均分的高低进行排序 ( (升序升序) )。select 课程号课程号, MAX(成绩成绩) as 最高分最高分, MIN(成绩成绩) as 最低分最低分, 总分总分=sum(成绩成绩)

43、, AVG(成绩成绩) as 平均分平均分 from 选课表选课表 group by 课程号课程号 having AVG(成绩成绩)=75 order by AVG(成绩成绩)说明：因为是对计算列的结果进行说明：因为是对计算列的结果进行筛选，所以要用到筛选，所以要用到Having AVG(成成绩绩)=75，不能用，不能用where AVG(成成绩绩)=75。同时要对平均分进行排。同时要对平均分进行排序，所以用到序，所以用到Order by avg(成绩成绩)。（2 2） 应用应用任务描述任务描述 【例例5.31】查询查询“选课表选课表”中每门课选课的人数及每门课已有成绩的人数。中每门课选课的人

44、数及每门课已有成绩的人数。select 课程号课程号, COUNT(成绩成绩) 有分数的人数有分数的人数, COUNT(*) 选课的人数选课的人数 from 选课表选课表 group by 课程号课程号说明：因为是统计每门课选课的人说明：因为是统计每门课选课的人数及每门课已有成绩的人数，所以数及每门课已有成绩的人数，所以是按课程号分组的，即是按课程号分组的，即Group by 课程号，选课的人数中可能有成绩，课程号，选课的人数中可能有成绩，也有可能是无成绩，所以要用也有可能是无成绩，所以要用Count(*)，而选课中已有成绩的人，而选课中已有成绩的人数，则要用到数，则要用到Count(成绩成绩

45、)。（2 2） 应用应用任务描述任务描述 【例【例5.32】查询】查询“选课表选课表”中每个学生选课门数及已有成绩课程门数。中每个学生选课门数及已有成绩课程门数。select 学号学号, COUNT(*) 选课门数选课门数, COUNT(成绩成绩) 已有成绩选课门数已有成绩选课门数 from 选课表选课表 group by 学号学号说明：因为是统计选课表中每个学说明：因为是统计选课表中每个学生选课门数及已有成绩课程门数，生选课门数及已有成绩课程门数，所以是按学号分组的，即所以是按学号分组的，即Group by 学号。选课门数仅指学生所选的课，学号。选课门数仅指学生所选的课，可能有成绩，可能无成

46、绩，所以要可能有成绩，可能无成绩，所以要用用Count(*)，而已有成绩的选课门，而已有成绩的选课门数，则要用到数，则要用到Count(成绩成绩)。（2 2） 应用应用任务描述任务描述 【例例5.33】查询查询“选课表选课表”中每个学生选课门数已超过中每个学生选课门数已超过4门（含门（含4门）的学生门）的学生的学号与课程数。的学号与课程数。select 学号学号,COUNT(*) 选课门数选课门数 from 选课表选课表 group by 学号学号 having COUNT(*)=4说明：选课门数是统计值，所以已说明：选课门数是统计值，所以已超过超过4门的条件该用：门的条件该用：having

47、COUNT(*)=45.1.2 5.1.2 对数据进行统计对数据进行统计任务描述任务描述 （1 1）格式）格式COMPUTECOMPUTE聚集函数聚集函数 By By 列名列名 3显示详细清单的查询（显示详细清单的查询（COMPUTE 子句）子句）COMPUTE子句对查询结果的所有记录进行汇总统计，并显示所有参加汇总记录的子句对查询结果的所有记录进行汇总统计，并显示所有参加汇总记录的详细信息。其语法格式为：详细信息。其语法格式为：其中：其中：聚集函数指的是聚集函数指的是sum()、avg()、Max()、Min()、Count()等等By 列名按指定列名按指定“列名列名”字段进行分组计算，并显

48、示被统计记录的详细信息。字段进行分组计算，并显示被统计记录的详细信息。By选项必须与选项必须与order By子句一起使用。子句一起使用。（2 2） 应用应用任务描述任务描述 【例【例5.345.34】查询】查询“选课表选课表”中所有成绩的最高分、最低分、平均分。并显示详中所有成绩的最高分、最低分、平均分。并显示详细清单。细清单。select select * * from from 选课表选课表 compute MAX( compute MAX(成绩成绩),min(),min(成绩成绩),avg(),avg(成绩成绩) )说明：因为是所有记录中的最说明：因为是所有记录中的最高分、最低分、平均

49、分，所以高分、最低分、平均分，所以不用进行排序，故不用加不用进行排序，故不用加Order by子句，后面的子句，后面的By选项自然也选项自然也没有。没有。（2 2） 应用应用任务描述任务描述 【例【例5.355.35】查询】查询“选课表选课表”中每门课程的最高分、最低分、平均分。并显示中每门课程的最高分、最低分、平均分。并显示详细清单。详细清单。select select * * from from 选课表选课表 order by order by 课程号课程号 compute MAX( compute MAX(成绩成绩),min(),min(成成绩绩),avg(),avg(成绩成绩) by

50、) by 课程号课程号说明：说明： compute by与与group by的区别的区别在于前者既显示统计记录又显示详在于前者既显示统计记录又显示详细记录，后者仅显示分组统计的汇细记录，后者仅显示分组统计的汇总记录。总记录。 compute by之前要使用之前要使用order by子句，原因是必须先按分类字段子句，原因是必须先按分类字段排序之后才能使用排序之后才能使用compute by子句子句进行分类汇总。本题是按课程号排进行分类汇总。本题是按课程号排序。序。 compute by与与group by使用的使用的语法格式不一样。语法格式不一样。（2 2） 应用应用任务描述任务描述 【例【例5

51、.365.36】查询】查询“选课表选课表”中每个同学所参加的课程成绩中的最高分、最低中每个同学所参加的课程成绩中的最高分、最低分、平均分。并显示详细清单。分、平均分。并显示详细清单。select select * * from from 选课表选课表 order by order by 学号学号 compute MAX( compute MAX(成绩成绩),min(),min(成成绩绩),avg(),avg(成绩成绩) by ) by 学号学号因为是要查询每个同学几门课因为是要查询每个同学几门课中的统计值，所以要按学号进中的统计值，所以要按学号进行排序。行排序。5.2 5.2 学生成绩管理数据

52、库多表查询学生成绩管理数据库多表查询在实际的应用系统中，多数情况下用户需要查询的信息来自多表。例如查询姓在实际的应用系统中，多数情况下用户需要查询的信息来自多表。例如查询姓名为名为“李小明李小明”同学的成绩单。因为选课表中只有同学的成绩单。因为选课表中只有“学号学号”、“课程号课程号”及及“成绩成绩”这三个字段，而无这三个字段，而无“姓名姓名”这个字段，因而，查询的步骤是先从这个字段，因而，查询的步骤是先从“学学生表生表”中查到姓名为中查到姓名为“李小明李小明”同学的学号，然后根据所查到的学号，从同学的学号，然后根据所查到的学号，从“选选课表课表”中再查相应的数据，这样就涉及两个表的查询。下面

53、，我们来介绍两个中再查相应的数据，这样就涉及两个表的查询。下面，我们来介绍两个或两个以上的表的查询，即高级查询。或两个以上的表的查询，即高级查询。任务描述任务描述 5.2.1 连接查询连接查询涉及二个或二个以上表的查询称作多表连接查询，简称涉及二个或二个以上表的查询称作多表连接查询，简称连接查询连接查询。连接查询有：。连接查询有：谓词连接、内连接、外连接等等。谓词连接、内连接、外连接等等。1谓词连接谓词连接在在select语句的语句的where子句中使用比较运算符给出连接条件，作为多表连接的表示子句中使用比较运算符给出连接条件，作为多表连接的表示形式，称作谓词连接。形式，称作谓词连接。（1）格

54、式）格式Select from 表表1，表，表2 ，表表n where 子句子句（2 2） 应用应用任务描述任务描述 【例【例5.375.37】查询每个同学的选课情况。】查询每个同学的选课情况。分析：因为没有指定显示哪些列，所以就认为显示所有列，即用分析：因为没有指定显示哪些列，所以就认为显示所有列，即用“* *”表示；表示；又因为有学生的信息，所以要用到又因为有学生的信息，所以要用到“学生表学生表”，同时还需要显示选课的情，同时还需要显示选课的情况，所以要用到况，所以要用到“选课表选课表”，这二个表之间可以用，这二个表之间可以用“学号学号”进行联系。在进行联系。在查询编辑器中输入：查询编辑器

55、中输入：select * from 学生表学生表,选课表选课表 where 学生表学生表.学号学号=选课表选课表.学号学号说明：结果表包含说明：结果表包含“学生表学生表”和和“选课选课表表”中的所有列。这里的连接谓词就是中的所有列。这里的连接谓词就是where子句中的字段，即子句中的字段，即“学生表学生表”中中的学号和的学号和“选课表选课表”中的学号。中的学号。（2 2） 应用应用任务描述任务描述 【例例5.38】查询每个同学的成绩情况。结果表中显示：学号、姓名、课程号、查询每个同学的成绩情况。结果表中显示：学号、姓名、课程号、成绩。成绩。分析：要显示分析：要显示“学号学号”、“姓名姓名”字段

56、，要用到字段，要用到“学生表学生表”，而要显示，而要显示“课程课程号号”、“成绩成绩”，则要用到，则要用到“选课表选课表”，又因为结果表中显示指定字段，所以，又因为结果表中显示指定字段，所以要规定表列。要规定表列。在查询编辑器中输入：在查询编辑器中输入：select 学生表学生表.学号学号,学生表学生表.姓名姓名,选课表选课表.课程号课程号,选课表选课表.成绩成绩from 学生表学生表,选课选课表表 where 学生表学生表.学号学号=选课表选课表.学号学号注意：在注意：在select语句中将所要语句中将所要显示的字段前注明了表名，例：显示的字段前注明了表名，例：学生表学生表.学号等，但对二个

57、表中学号等，但对二个表中唯一出现的字段，则可以省略唯一出现的字段，则可以省略前缀表名，例如：前缀表名，例如：“学生表学生表”和和“选课表选课表”二个表中均有二个表中均有“学号学号”，所以需要加前缀表，所以需要加前缀表名，而姓名、课程号、成绩是名，而姓名、课程号、成绩是各表中唯一的，所以可以不加各表中唯一的，所以可以不加前缀，当然，加上前缀也行。前缀，当然，加上前缀也行。 （2 2） 应用应用任务描述任务描述 【例例5.39】查询每个同学的成绩情况。结果表中显示：学号、姓名、课名、成绩。查询每个同学的成绩情况。结果表中显示：学号、姓名、课名、成绩。分析：因为要显示分析：因为要显示“姓名姓名”，则

58、要用到，则要用到“学生表学生表”；要显示；要显示“课名课名”，则需要用到，则需要用到“课程表课程表”；而要显示；而要显示“成绩成绩”，则要用到，则要用到“选课表选课表”。“学生表学生表”和和“选课表选课表”通过通过“学号学号”联系，联系，“选课表选课表”与与“课程表课程表”通过通过“课程号课程号”联系。联系。所以在查询编辑器中输入代码：所以在查询编辑器中输入代码：select a.学号学号,姓名姓名,课名课名,成绩成绩 from 学生表学生表 a,选课表选课表 b,课程表课程表 cwhere a.学号学号=b.学号学号 and b.课程号课程号=c.课程号课程号注意：注意：“学生表学生表”和和

59、“选选课表课表”中根据中根据“学号学号”这这个谓词连接，而个谓词连接，而“选课表选课表”和和“课程表课程表”则是根据则是根据“课程号课程号”这个谓词连接。这个谓词连接。要显示的列来自三个表，要显示的列来自三个表，所以就要三个表两两连接。所以就要三个表两两连接。（2 2） 应用应用任务描述任务描述 【例例5.40】查询选修查询选修“大学英语大学英语”、“计算机基础计算机基础”课的同学的成绩，要课的同学的成绩，要求显示：学号、姓名、课程名、成绩。求显示：学号、姓名、课程名、成绩。select a.学号学号,姓名姓名,课名课名,成绩成绩 from 学学生表生表 a,选课表选课表 b,课程表课程表 c

60、 where a.学号学号=b.学号学号 and b.课程号课程号=c.课程号课程号 and (课名课名=大学英语大学英语 or 课名课名=计算机基础计算机基础)【例例5.41】查询老师的课表信息，要查询老师的课表信息，要求显示：教师号、教师姓名、课名、求显示：教师号、教师姓名、课名、学时、学分。学时、学分。select a.教师号教师号,姓名姓名,课名课名,学时学时, 学分学分 from 教师表教师表 a,课程表课程表 b,授课表授课表 cwhere a.教师号教师号=c.教师号教师号 and b.课程课程号号 =c.课程号课程号（2 2） 应用应用任务描述任务描述 【例【例5.42】查询讲