第5章 数据查询与操作

1、第第5章章 数据查询与操作数据查询与操作基本表的常用维护操作基本表的常用维护操作1、表重命名表重命名 exec sp_rename Student, stu2、列重命名列重命名 exec sp_rename Student.Sname, name, column 3、添加新列添加新列 alter table Student add Sentrance datetime 注意注意：新增列的数据为空。：新增列的数据为空。4、更改列数据类型更改列数据类型 alter table Student alter column Sage int 注意注意：若表中已有数据，数据类型不相容时不能进行列数据类型的

2、更改。：若表中已有数据，数据类型不相容时不能进行列数据类型的更改。5、删除列删除列 alter table Student drop column Sentrance6、删除表、删除表 drop table Student 注意注意：若该表与其它表关联（如外键的参照等），则删除失败。：若该表与其它表关联（如外键的参照等），则删除失败。第第5章章 数据查询与操作数据查询与操作5.35.3数据汇总数据汇总5.45.4基于多表的连接查询基于多表的连接查询5.55.5子查询子查询5.25.2选择查询选择查询第第5章章 数据查询与操作数据查询与操作预备：SQL概述vSQL（Structured Quer

3、y Language） 结构化查询语言，是关系数据库的标准语言结构化查询语言，是关系数据库的标准语言vSQL是一个通用的、功能极强的关系数据库是一个通用的、功能极强的关系数据库语言语言第第5章章 数据查询与操作数据查询与操作SQL标准的进展过程 标准标准 大致页数大致页数 发布日期发布日期n SQL/86 1986.10n SQL/89(FIPS 127-1) 120页页 1989年年n SQL/92 622页页 1992年年n SQL99 1700页页 1999年年n SQL2003 2003年年SQL只要提出只要提出“做什么做什么”，无须了解存取路径。无须了解存取路径。 存取路径的选择以及

4、存取路径的选择以及SQL的的操作过程由系统自动完成。操作过程由系统自动完成。第第5章章 数据查询与操作数据查询与操作vSQL是独立的语言是独立的语言 能够独立地用于联机交互的使用方式能够独立地用于联机交互的使用方式vSQL又是嵌入式语言又是嵌入式语言 SQL能够嵌入到高级语言（例如能够嵌入到高级语言（例如C，C+，Java）程序中，供程序员设计程序时使用）程序中，供程序员设计程序时使用第第5章章 数据查询与操作数据查询与操作v SQL功能极强，完成核心功能只用了9个动词。第第5章章 数据查询与操作数据查询与操作5.1.1 编写编写SQL 语句语句5.1.2 测试测试SQL 语句语句5.1.3

5、执行执行SQL 语句语句第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.1 简单的简单的SELECT查询查询 SELECT语句是从数据库中检索数据并查询，将查语句是从数据库中检索数据并查询，将查询结果以询结果以表格表格的形式返回。的形式返回。SELECT select_list /*列名列名*/ INTO new_table /*查询结果存储到新表的名字查询结果存储到新表的名字*/FROM table_list /*表名表名*/ WHERE search_condition /* 条件条件*/ GROUP BY group_by_expression /* 对检索到的数

6、据分组对检索到的数据分组*/ HAVING search_condition /* 用于组的条件用于组的条件*/ ORDER BY order_expression ASC| DESC /* 结果排序结果排序*/第第5章章 数据查询与操作数据查询与操作教材所用表的表结构如下：教材所用表的表结构如下：第第5章章 数据查询与操作数据查询与操作5.2选择查询选择查询5.2.1 简单的简单的SELECT查询查询1、选择所有字段、选择所有字段 如果要显示数据表中所有列的值时，如果要显示数据表中所有列的值时，Select子句后用子句后用星号（星号（*）表示。表示。【例例5-1】在在Student数据库中，

7、查询数据库中，查询grade表的所有记录。表的所有记录。2、选择部分字段、选择部分字段 在在Select子句后分别子句后分别列出各个字段名称列出各个字段名称即可。即可。【例例5-2】查询查询grade表中，学号、课程成绩、学期字段的信表中，学号、课程成绩、学期字段的信息。息。use studentselect * from gradeuse studentselect 学号学号,课程成绩课程成绩,学期学期 from grade第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.2 重新对列排序重新对列排序 ORDER BY order_by_expression ASC |

8、 DESC 1、单级排序、单级排序【例例5-3】在在grade表中，在例表中，在例5-2的基础上，按照的基础上，按照“课程成课程成绩绩”升序排序。升序排序。在在grade表中，查询所有学生信息，按照学生的表中，查询所有学生信息，按照学生的“学期学期”降降序排列。序排列。 升序时，升序时，“ASC”可以省略；降序时，可以省略；降序时，“DESC”不可以省略。不可以省略。use student select * from grade order by 课程成绩课程成绩use student select * from grade order by 学期学期 desc第第5章章 数据查询与操作数据查

9、询与操作5.2 选择查询选择查询5.2.2 重新对列排序重新对列排序 2、多级排序、多级排序 ORDER BY order_by_expression ASC | DESC ,.n 【例例5-4】在在grade表中，先按照学生的表中，先按照学生的“学期学期”降序排列，降序排列，然后再按照然后再按照“课程成绩课程成绩”升序排序。升序排序。use studentselect * from grade order by 学期学期 desc, 课程成绩课程成绩第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.3 使用运算符或函数进行列计算使用运算符或函数进行列计算 SELECT

10、表达式表达式1,表达式表达式2,字段字段1,字段字段2,.from 数据表名数据表名【例例5-5】在在tb_stu表中，查询所有学生的学号、姓名与年表中，查询所有学生的学号、姓名与年龄，并将查询结果中的年龄字段值加龄，并将查询结果中的年龄字段值加1。select 学号学号,姓名姓名,年龄年龄=年龄年龄+1 from tb_stu第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.4 使用使用WHERE参数过滤数据参数过滤数据 SELECT FROM WHERE （1）比较运算符（）比较运算符（=、!=、=、=、!）。）。 （2）范围说明（）范围说明（BETWEEN和和NOT

11、 BETWEEN）。）。 （3）可选值列表（）可选值列表（IN和和NOT IN）。）。 （4）模式匹配（）模式匹配（LIKE和和NOT LIKE）。）。 （5）上述条件的逻辑组合（）上述条件的逻辑组合（AND、OR、NOT）。）。第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.4 使用使用WHERE参数过滤数据参数过滤数据 SELECT FROM WHERE 1、比较运算符（如、比较运算符（如=、）【例例5-6】查询查询grade表中课程成绩大于表中课程成绩大于90分的信息。分的信息。【补充补充】多个条件多个条件Select * from student where 姓

12、名姓名=刘大伟 and 性别性别=男Select * from student where 姓名姓名= 李羽凡 or 年龄年龄!25Select * from student where not 年龄年龄25use studentselect * from grade where 课程成绩课程成绩90第第5章章 数据查询与操作数据查询与操作5.2选择查询选择查询5.2.4 使用使用WHERE参数过滤数据参数过滤数据2、范围查询条件、范围查询条件 通常使用通常使用BETWEENAND和和 NOT BETWEENAND来指定范围条件来指定范围条件.【例例5-7】查询查询student表中年龄在表中

13、年龄在2021之间的所有信息。之间的所有信息。【例例5-8】查询查询student表中年龄不在表中年龄不在2021之间的所有信息。之间的所有信息。use student select * from student where 年龄年龄 between 20 and 21 use studentselect * from student where 年龄年龄 not between 20 and 21 第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.4 使用使用WHERE参数过滤数据参数过滤数据3、列表查询条件、列表查询条件 IN（目标值（目标值1，目标值，目标值2，目标

14、值，目标值3，）【例例5-9】查询查询course表中表中 “课程代号课程代号”是是k01，k03，k04的所有信息。的所有信息。【例例5-10】查询查询 course表中表中 “课程代号课程代号”不是不是k01，k03，k04的所有信息。的所有信息。 use studentselect * from course where 课程代号课程代号 in (k01,k03, k04)use studentselect * from course where 课程代号课程代号 not in (k01,k03, k04)第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.4 使用使

15、用WHERE参数过滤数据参数过滤数据4、模糊、模糊LIKE查询查询 LIKE关键字需要使用关键字需要使用通配符通配符在字符串内查找指定的模式。在字符串内查找指定的模式。（1）“%”通配符通配符 % 通配符能匹配通配符能匹配0个个或或更多个更多个字符的字符的任意长度任意长度的字符串。的字符串。【例例5-11】 在在student表中查询姓表中查询姓“李李”的学生信息的学生信息use studentselect * from student where 姓名姓名 like 李李% 第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.4 使用使用WHERE参数过滤数据参数过滤数据

16、4、模糊、模糊LIKE查询查询（2）“_”通配符通配符 _ 通配符通配符只能只能匹配匹配一个一个字符。字符。【例例5-12】 在在student表中查询姓表中查询姓“刘刘”并且名字只有两个字并且名字只有两个字的学生信息。的学生信息。 use studentselect * from student where 姓名姓名 like 刘刘_ 第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.4 使用使用WHERE参数过滤数据参数过滤数据4、模糊、模糊LIKE查询查询（3）“ ”通配符通配符 通配符用于表示一定范围内的任意单个字符，它包通配符用于表示一定范围内的任意单个字符，它

17、包括两端数据。括两端数据。【例例5-13】在在student表中查询联系方式以表中查询联系方式以“3451”结尾，结尾，并且开头数字位于并且开头数字位于1-5之间的学生信息之间的学生信息use studentselect * from student where 联系方式联系方式 like 1-53451第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.4 使用使用WHERE参数过滤数据参数过滤数据4、模糊、模糊LIKE查询查询（4）“ ”通配符通配符 通配符用于查询不在指定范围内的数据。通配符用于查询不在指定范围内的数据。【例例5-14】在在student表中查询联系方

18、式以表中查询联系方式以“3451”结尾，结尾，但不以但不以2开头的学生信息开头的学生信息use studentselect * from studentwhere 联系方式联系方式 like 23451第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.4 使用使用WHERE参数过滤数据参数过滤数据4、模糊、模糊LIKE查询查询 NOT LIKE查询与查询与LIKE查询正好相反，查询结果将查询正好相反，查询结果将返回不符合匹配模式查询。返回不符合匹配模式查询。【例例5-14】在在student表中查询除了联系方式以表中查询除了联系方式以“3451”结结尾并且开头数字位于尾并

19、且开头数字位于1-5的其他学生信息的其他学生信息use studentselect * from student where 联系方式联系方式 not like 1-53451第第5章章 数据查询与操作数据查询与操作5.2选择查询选择查询5.2.4 使用使用WHERE参数过滤数据参数过滤数据5、复合查询条件、复合查询条件 逻辑运算符逻辑运算符AND、OR和和NOT 使用逻辑运算符时，遵循的指导原则如下：使用逻辑运算符时，遵循的指导原则如下： （1）使用）使用AND返回满足所有条件的行；返回满足所有条件的行； （2）使用）使用OR返回满足任一条件的行；返回满足任一条件的行； （3）使用）使用NO

20、T返回不满足表达式的行。返回不满足表达式的行。【例例5-15】在在student表中，要查询年龄大于等于表中，要查询年龄大于等于19岁的男岁的男生或者年龄大于生或者年龄大于21岁的女生信息。岁的女生信息。select * from student where 年龄年龄=19 and 性别性别=男男 or 年龄年龄 21 and 性别性别=女女 第第5章章 数据查询与操作数据查询与操作5.2 选择查询选择查询5.2.5 消除重复记录消除重复记录 DISTINCT关键字主要用来从关键字主要用来从SELECT语句的结果语句的结果集中去掉重复的记录。集中去掉重复的记录。【例例5-16】在在course

21、表中，显示不同的表中，显示不同的“课程类别课程类别”。use studentselect distinct 课程类别课程类别 from course在在grade表中，显示表中，显示“学号学号”和和“课程代号课程代号”的不同值。的不同值。use studentselect distinct 学号学号,课程代号课程代号 from grade第第5章章 数据查询与操作数据查询与操作5.5 选择查询选择查询 【练习练习1】【1】查询出生年月小于查询出生年月小于“1982-1-1”的学生名单。的学生名单。【2】查询查询student表中的所有数据。表中的所有数据。【3】查询查询student表中班级为

22、表中班级为“信息信息081”的学生基本情况。的学生基本情况。【4】查询查询student表中出生日期在表中出生日期在“1981-01-01”至至“1982-05-01”之之间的学生基本情况，并以出生年月降序排列。间的学生基本情况，并以出生年月降序排列。【5】查询姓名中含有查询姓名中含有“红红”字的学生情况。字的学生情况。系别系别 班级班级 专业专业 学号学号 姓名姓名 性别性别 出生年月出生年月总学分总学分 备注备注课程号课程号课程名课程名 开课学期开课学期 学时学时 学分学分学号学号 课程号课程号 成绩成绩 学分学分 student coursegradeselect * from stud

23、ent where 出生年月出生年月1982-1-1select * from studentselect * from student where 班级班级=信息信息081select * from student where 出生年月出生年月 between 1981-01-01 and 1982-05-01 order by 出生年月出生年月 descselect * from student where 姓名姓名 like %红红%第第5章章 数据查询与操作数据查询与操作选择查询选择查询1 简单的简单的SELECT查询查询2 重新对列排序重新对列排序 ORDER BY order_by

24、_expression ASC | DESC SELECT 列名列名,列明列明 FROM 表名表名 WHERE 条件表达式条件表达式 GROUP BY 分组表达式分组表达式 HAVING 分组条件表达式分组条件表达式 ORDER BY 列名列名 ASC| DESC 第第5章章 数据查询与操作数据查询与操作选择查询选择查询3 使用运算符或函数进行列计算使用运算符或函数进行列计算 SELECT 表达式表达式1,表达式表达式2,字段字段1,字段字段2,.from 数据表数据表4 消除重复记录消除重复记录 (DISTINCT关键字关键字)5 使用使用WHERE参数过滤数据参数过滤数据 （1）比较运算符

25、（如）比较运算符（如=、）。）。 （2）范围说明（）范围说明（BETWEEN和和NOT BETWEEN）。）。 （3）可选值列表（）可选值列表（IN和和NOT IN）。）。 （4）模式匹配（）模式匹配（LIKE和和NOT LIKE）。）。 （5）上述条件的逻辑组合（）上述条件的逻辑组合（AND、OR、NOT）。）。第第5章章 数据查询与操作数据查询与操作5.35.3数据汇总数据汇总5.45.4基于多表的连接查询基于多表的连接查询5.55.5子查询子查询5.25.2选择查询选择查询第第5章章 数据查询与操作数据查询与操作5.3 数据汇总数据汇总5.3.1 使用聚合函数使用聚合函数 聚合函数聚合函

26、数 支持的数据类型支持的数据类型功能描述功能描述sum()数字数字对指定列中的所有对指定列中的所有非空值非空值求和求和avg()数字数字对指定列中的所有对指定列中的所有非空值非空值求平求平均值均值min()数字、数字、字符、日字符、日期期返回指定列中的最小数字、最返回指定列中的最小数字、最小的字符串和最早的日期时间小的字符串和最早的日期时间max()数字、数字、字符、日字符、日期期返回指定列中的最大数字、最返回指定列中的最大数字、最大的字符串和最晚的日期时间大的字符串和最晚的日期时间count(*)任意基于行的数任意基于行的数据类型据类型统计结果集中统计结果集中全部记录行全部记录行的数的数量。

27、最多可达量。最多可达2147483647行。行。第第5章章 数据查询与操作数据查询与操作5.3 数据汇总数据汇总5.3.1 使用聚合函数使用聚合函数 在在grade表中，求所有的课程成绩的总和。表中，求所有的课程成绩的总和。 select sum(课程成绩课程成绩) from grade 在在student表中，求所有学生的平均年龄。表中，求所有学生的平均年龄。 select avg(年龄年龄) from student在在student表中，查询最早出生的学生。表中，查询最早出生的学生。 select min(出生日期出生日期) from student在在grade表中，查询课程成绩最高的

28、学生信息。表中，查询课程成绩最高的学生信息。 select max(课程成绩课程成绩) from grade在在student表中，求所有女生的人数。表中，求所有女生的人数。 select count(年龄年龄) from student where 性别性别=女求求student表中记录的个数。表中记录的个数。 select count(*) from student第第5章章 数据查询与操作数据查询与操作5.3 数据汇总数据汇总5.3.2 使用使用GROUP BY子句子句 1、按单列分组、按单列分组 GROUP BY子句可以基于指定某一列的值将数据集子句可以基于指定某一列的值将数据集合划分

29、为合划分为多个多个分组，同一组内所有记录在分组属性上具有分组，同一组内所有记录在分组属性上具有相同相同值。值。 【例例5-17】把把student表按表按“性别性别”这个单列进行分组。这个单列进行分组。1张三张三19男男学号学号姓名姓名年龄年龄性别性别2李四李四20男男3王五王五21女女4陈六陈六19男男学号学号姓名姓名年龄年龄性别性别1张三张三19男男2李四李四20男男4陈六陈六19男男3王五王五21女女studentselect 性别性别 from student group by 性别性别 注意：在注意：在select子句的字段列表中，除了聚合函数外，其他所子句的字段列表中，除了聚合函数

30、外，其他所出现的字段一定要在出现的字段一定要在group by 子句中有定义才行。子句中有定义才行。第第5章章 数据查询与操作数据查询与操作 5.3.2 5.3.2使用使用GROUP BYGROUP BY子句子句 1）在）在SELECT子句的字段列表中，除了聚集函数外，其他子句的字段列表中，除了聚集函数外，其他所出现的字段一定要在所出现的字段一定要在GROUP BY子句中有定义才行。子句中有定义才行。如如group by A,B,那么那么select sum(A),C就有问题，因为就有问题，因为C不在不在group by中。中。 2）SELECT子句的字段列表中不一定要有聚集函数，但至子句的字

31、段列表中不一定要有聚集函数，但至少要用到少要用到GROUP BY子句列表中的一个项目。子句列表中的一个项目。如如 group by A,B,C，则，则select A是可以的。是可以的。3）在）在SQL Server中中text、ntext和和image数据类型的字段不数据类型的字段不能作为能作为GROUP BY子句的分组依据。子句的分组依据。 4）GROUP BY子句不能使用字段别名。子句不能使用字段别名。第第5章章 数据查询与操作数据查询与操作5.3 数据汇总数据汇总5.3.2 使用使用GROUP BY子句子句 1、按单列分组、按单列分组 GROUP BY子句可以基于指定某一列的值将数据集

32、子句可以基于指定某一列的值将数据集合划分为合划分为多个多个分组，同一组内所有记录在分组属性上具有分组，同一组内所有记录在分组属性上具有相同相同值。值。 1张三张三19男男学号学号姓名姓名年龄年龄性别性别2李四李四20男男3王五王五21女女4陈六陈六19男男学号学号姓名姓名年龄年龄性别性别1张三张三19男男2李四李四20男男4陈六陈六19男男3王五王五21女女5.3.2 使用使用GROUP BY子句子句 2、按多列分组、按多列分组 GROUP BY子句可以基于指定多列的值将数据集合划分子句可以基于指定多列的值将数据集合划分为多个分组。为多个分组。2李四李四20男男4陈六陈六19男男【例例5-18

33、】在在student表中，按表中，按“性别性别”和和“年龄年龄”列进行分组。列进行分组。studentselect 性别性别,年龄年龄 from student group by 性别性别,年龄年龄第第5章章 数据查询与操作数据查询与操作5.3 数据汇总数据汇总1张三张三19男男学号学号姓名姓名年龄年龄性别性别2李四李四20男男3王五王五21女女4陈六陈六19男男【例例5-19】在在student表中，先按表中，先按“性别性别”分组求出平均年龄，然分组求出平均年龄，然后筛选出平均年龄大于后筛选出平均年龄大于22岁的分组。岁的分组。5.3.3 使用使用HAVING子句子句 分组之前的条件要用分组

34、之前的条件要用where关键字，而分组之后的条关键字，而分组之后的条件要使用关键字件要使用关键字having子句子句student学号学号姓名姓名年龄年龄性别性别1张三张三19男男男男男男3王五王五21女女2李四李四20男男4陈六陈六19男男select avg(年龄年龄), 性别性别 from student group by 性别性别 having avg(年龄年龄)22第第5章章 数据查询与操作数据查询与操作5.4基于多表的连接查询基于多表的连接查询 5.4.1 5.4.1连接谓词连接谓词 JOIN是一种将两个表连接在一起的连接谓是一种将两个表连接在一起的连接谓词。词。 连接条件可在连接

35、条件可在FROM或或WHERE子句中指子句中指定，建议在定，建议在FROM子句中指定连接条件。子句中指定连接条件。第第5章章 数据查询与操作数据查询与操作 5.4.2 5.4.2以以JOINJOIN关键字指定的连接关键字指定的连接 使用使用JOIN关键字可以进行交叉连接、内关键字可以进行交叉连接、内连接和外连接。连接和外连接。 1 1交叉连接交叉连接 交叉连接是两个表的笛卡儿积的另一个交叉连接是两个表的笛卡儿积的另一个名称。名称。 笛卡儿积就是两个表的交叉乘积，即两笛卡儿积就是两个表的交叉乘积，即两个表的记录进行交叉组合，如图个表的记录进行交叉组合，如图5.21所示。所示。第第5章章 数据查询

36、与操作数据查询与操作图图5.21两个表的笛卡儿积示意图两个表的笛卡儿积示意图第第5章章 数据查询与操作数据查询与操作 交叉连接的语法如下：交叉连接的语法如下： SELECT fieldlist FROM table1 cross JOIN table2use EDUCSELECT * FROM student cross JOIN course第第5章章 数据查询与操作数据查询与操作2 2内连接内连接 内连接也叫连接，是最早的一种连接，内连接也叫连接，是最早的一种连接，还被称为普通连接或自然连接。还被称为普通连接或自然连接。 内连接是从结果中删除其他被连接表中内连接是从结果中删除其他被连接表中

37、没有匹配行的所有行，所以内连接可能会丢没有匹配行的所有行，所以内连接可能会丢失信息。失信息。第第5章章 数据查询与操作数据查询与操作 内连接的语法如下：内连接的语法如下： SELECT fieldlist FROM table1 INNER JOIN table2 ON table1.column=table2.column第第5章章 数据查询与操作数据查询与操作 3 3外连接外连接 外连接则扩充了内连接的功能，会把内外连接则扩充了内连接的功能，会把内连接中删除原表中的一些保留下来，由于保连接中删除原表中的一些保留下来，由于保留下来的行不同，把外连接分为左外连接、留下来的行不同，把外连接分为左

38、外连接、右外连接和全外连接右外连接和全外连接3种连接。种连接。第第5章章 数据查询与操作数据查询与操作 （1）左外连接）左外连接左外连接的语法如下：左外连接的语法如下： SELECT fieldlist FROM table1 left JOIN table2 ON table1.column= table2.column第第5章章 数据查询与操作数据查询与操作【例5-20】 把student表和表和grade表左外连接。表左外连接。 SQL语句如下： use student select * from student left join grade on student.学号=grade.学

39、号第第5章章 数据查询与操作数据查询与操作图图5.22student表和表和grade表左外连接表左外连接第第5章章 数据查询与操作数据查询与操作 （2）右外连接）右外连接右外连接的语法如下：右外连接的语法如下：SELECT fieldlist FROM table1 right JOIN table2 ON table1.column=table2.column 第第5章章 数据查询与操作数据查询与操作【例5-21】 把grade表和表和course表右外连接表右外连接。SQL语句如下： use student select * from grade right join course on

40、 course.课程代号=grade.课程代号 第第5章章 数据查询与操作数据查询与操作图图5.23grade表和表和course表右外连接表右外连接第第5章章 数据查询与操作数据查询与操作 （3）全外连接）全外连接全外连接的语法如下：全外连接的语法如下： SELECT fieldlist FROM table1 full JOIN table2 ON table1.column=table2.column第第5章章 数据查询与操作数据查询与操作 【例5-22】 把course表和表和grade表全外连接。表全外连接。SQL语句如下： use student select * from gra

41、de full join course on course.课程代号=grade.课程代号 第第5章章 数据查询与操作数据查询与操作图图5.24course表和表和grade表全外连接表全外连接第第5章章 数据查询与操作数据查询与操作5.5子子 查查 询询 5.5.1 5.5.1使用使用ININ或或NOT INNOT IN的子查询的子查询（1）使用）使用IN的子查询的子查询 带带in的嵌套查询语法格式如下：的嵌套查询语法格式如下： WHERE 查询表达式查询表达式 IN（子查询）（子查询）第第5章章 数据查询与操作数据查询与操作 use student select * from studen

42、t where 学号学号 in (select 学号学号 from grade )例例5-23在在student和和grade表中，查询参加考试的同学信息。表中，查询参加考试的同学信息。第第5章章 数据查询与操作数据查询与操作 【例例5-23】 使用使用IN关键字进行子查询。关键字进行子查询。图图5.25显示参加考试的同学信息显示参加考试的同学信息图图5.26子查询的结果子查询的结果 第第5章章 数据查询与操作数据查询与操作（2）使用）使用NOT IN的子查询的子查询 not in 和和 in查询过程类似。查询过程类似。not in的嵌的嵌套查询语法格式如下：套查询语法格式如下： WHERE

43、查询表达式查询表达式 NOT IN（子查询）（子查询）注意：注意：子查询存在子查询存在NULL值时，避免使用值时，避免使用not in。因为。因为当子查询的结果包括了当子查询的结果包括了NULL值的列表时，把值的列表时，把NULL值当值当成一个未知数据，不会存在查询值不在列表中的记录。成一个未知数据，不会存在查询值不在列表中的记录。第第5章章 数据查询与操作数据查询与操作 【例例5-24】 使用使用NOT IN进行子查询。进行子查询。例例5-24在在course和和grade表中，查询没有学生参加考表中，查询没有学生参加考试的课程信息。试的课程信息。 SQL语句如下：语句如下： use stu

44、dent select * from course where 课程代号课程代号 not in (select 课程代号课程代号 from grade )错误的！错误的！第第5章章 数据查询与操作数据查询与操作图图5.27查询学生参加考试的课程信息查询学生参加考试的课程信息第第5章章 数据查询与操作数据查询与操作 【例例5-24】 使用使用NOT IN进行子查询。进行子查询。例例5-24在在course和和grade表中，查询没有学生参加考表中，查询没有学生参加考试的课程信息。试的课程信息。use student select * from course where 课程代号课程代号 not

45、in (select 课程代号课程代号 from grade where 课程代号课程代号 is not NULL )第第5章章 数据查询与操作数据查询与操作图图5.28查询没有学生参加考试的课程信息查询没有学生参加考试的课程信息第第5章章 数据查询与操作数据查询与操作 5.5.2 5.5.2使用比较运算符的子查询使用比较运算符的子查询 嵌套内层子查询通常作为搜索条件的一嵌套内层子查询通常作为搜索条件的一部分呈现在部分呈现在WHERE或或HAVING子句中。子句中。 例如，把一个表达式的值和由子查询生例如，把一个表达式的值和由子查询生成的值相比较。成的值相比较。 这个测试类似于简单比较测试。这

46、个测试类似于简单比较测试。第第5章章 数据查询与操作数据查询与操作 子查询比较测试用到的运算符是：子查询比较测试用到的运算符是：=、=。 子查询比较测试把一个表达式的值和由子查询比较测试把一个表达式的值和由子查询产生值进行比较，这时子查询只能返子查询产生值进行比较，这时子查询只能返回回一个值一个值，否则错误。，否则错误。 最后返回比较结果为最后返回比较结果为TRUE的记录。的记录。第第5章章 数据查询与操作数据查询与操作use studentselect * from student where 学号学号 = ( select 学号学号 from grade where 课程成绩课程成绩98

47、) 【例例5-25】 查询查询“课程成绩课程成绩”大于大于98分的学生信息。分的学生信息。图图5.29显示成绩大于显示成绩大于98分的学生信息分的学生信息第第5章章 数据查询与操作数据查询与操作 5.5.3 5.5.3使用使用EXISTSEXISTS的子查询的子查询 exists谓词只注重子查询是否返回行。谓词只注重子查询是否返回行。 如果子查询返回一个或多个行，谓词评如果子查询返回一个或多个行，谓词评价为真，否则为假。价为真，否则为假。 exists搜索条件并不真正地使用子查询搜索条件并不真正地使用子查询的结果，它仅仅测试子查询是否产生任何结的结果，它仅仅测试子查询是否产生任何结果。果。第第

48、5章章 数据查询与操作数据查询与操作【例例5-26】 使用使用EXISTS进行子查询，在进行子查询，在student和和grade表中，查询参加考试的学生信表中，查询参加考试的学生信息。息。 use student select * from student where exists (select 学号学号 from grade where student.学号学号=grade.学号学号 )图图5.30查询参加考试的学生信息查询参加考试的学生信息第第5章章 数据查询与操作数据查询与操作用用in子句完成：子句完成：use student select * from student where

49、学号学号 in (select 学号学号 from grade )第第5章章 数据查询与操作数据查询与操作【例例5-27】 使用使用NOT EXISTS进行子查询。查进行子查询。查询没参加考试的学生信息询没参加考试的学生信息 。use student select * from student where not exists (select * from grade where student.学号学号=grade.学号学号 ) 图图5.31查询没参加考试的学生信息查询没参加考试的学生信息 第第5章章 数据查询与操作数据查询与操作用用NOT IN 子句完成：子句完成：use student

50、select * from student where 学号学号 not in (select 学号学号 from grade where 学号学号 is not NULL ) 第第5章章 数据查询与操作数据查询与操作5.5.45.5.4使用使用UNIONUNION运算符组合多个结果运算符组合多个结果 表的合并操作是指将两个表的行合并到了单个表中，且不表的合并操作是指将两个表的行合并到了单个表中，且不需要对这些行作任何更改。需要对这些行作任何更改。 在构造合并查询时必须遵循以下几条规则。在构造合并查询时必须遵循以下几条规则。（1）所有查询中的列数和列的顺序必须相同。）所有查询中的列数和列的顺序

51、必须相同。（2）数据类型必须兼容。）数据类型必须兼容。（3）作为对所有）作为对所有select语句的合并操作结果进行排序的语句的合并操作结果进行排序的order by子句，必须放到最后一个子句，必须放到最后一个select后面，但它所使用的排序列后面，但它所使用的排序列名必须是第名必须是第1个个select选择列表中的列名。选择列表中的列名。第第5章章 数据查询与操作数据查询与操作 【例5-28】 使用使用UNION运算符合并查询结果。运算符合并查询结果。use studentselect 学号学号,姓名姓名 ,性别性别 from student where 年龄年龄 (select 年龄年龄

52、 from student where 姓名姓名=刘月刘月)第第5章章 数据查询与操作数据查询与操作（2）多表嵌套）多表嵌套例例2：查询课程成绩大于：查询课程成绩大于90分的学生信息。分的学生信息。use studentSelect * from student Where 学号学号 in (select 学号学号 from grade where 课程成绩课程成绩90)或者用多表连接完成：或者用多表连接完成：use studentSelect student.* from student ,grade Where student.学号学号=grade.学号学号 and grade.课程成绩课

53、程成绩90第第5章章 数据查询与操作数据查询与操作2、带有统计函数的嵌套查询、带有统计函数的嵌套查询例例3：查询课程成绩大于平均成绩的所有学生：查询课程成绩大于平均成绩的所有学生信息。信息。use studentSelect * from gradeWhere 课程成绩课程成绩 (select avg(课程成绩课程成绩) from grade)第第5章章 数据查询与操作数据查询与操作例例4：给出所有学生考试中与平均成绩的差值：给出所有学生考试中与平均成绩的差值信息。信息。use studentSelect student.* ,grade.课程成绩课程成绩-(select avg(课程成绩课程

54、成绩) from grade) AS 与平均成绩的差与平均成绩的差 from student, grade where student.学号学号=grade.学号学号第第5章章 数据查询与操作数据查询与操作3、Compute 或或Compute by查询查询（1）没有）没有BY时，查询结果将包含两个结果集。第一个结果时，查询结果将包含两个结果集。第一个结果集将是包含选择列表中所有字段的详细记录。第二个结果集将是包含选择列表中所有字段的详细记录。第二个结果集只有一条记录，这条记录只包含集只有一条记录，这条记录只包含COMPUTE子句中所指子句中所指定的汇总函数的合计。定的汇总函数的合计。例例5：

55、在：在student表中，求表中，求“年龄年龄”字段的平均值、最大值、字段的平均值、最大值、最小值和总和。最小值和总和。use studentSelect * from studentCompute avg(年龄年龄),max(年龄年龄),min(年龄年龄),sum(年龄年龄)第第5章章 数据查询与操作数据查询与操作3、Compute 或或Compute by 查询查询（2）有）有BY时，查询结果将根据时，查询结果将根据BY后的字段名称进行分组后的字段名称进行分组，并且为每个符合，并且为每个符合SELECT语句查询条件的组返回两个结果语句查询条件的组返回两个结果集。第一个结果集是详细记录集，包

56、含结果集中将包含选择集。第一个结果集是详细记录集，包含结果集中将包含选择列表中所有的字段信息。第二个结果集是只包含一条记录，列表中所有的字段信息。第二个结果集是只包含一条记录，这条记录的内容只有该组的这条记录的内容只有该组的COMPUTE子句中所指定的汇总子句中所指定的汇总函数的小计。函数的小计。 例例6：在：在student表中，分别求男生和女生的平均年龄。表中，分别求男生和女生的平均年龄。use studentSelect avg(年龄年龄) as 年龄年龄 from student group by 性别性别第第5章章 数据查询与操作数据查询与操作3、Compute 或或Compute

57、by 查询查询（2）有）有BY时，查询结果将根据时，查询结果将根据BY后的字段名称进行分组后的字段名称进行分组，并且为每个符合，并且为每个符合SELECT语句查询条件的组返回两个结果语句查询条件的组返回两个结果集。第一个结果集是详细记录集，包含结果集中将包含选择集。第一个结果集是详细记录集，包含结果集中将包含选择列表中所有的字段信息。第二个结果集是只包含一条记录，列表中所有的字段信息。第二个结果集是只包含一条记录，这条记录的内容只有该组的这条记录的内容只有该组的COMPUTE子句中所指定的汇总子句中所指定的汇总函数的小计。函数的小计。 例例6：在：在student表中，分别求男生和女生的平均年

58、龄。表中，分别求男生和女生的平均年龄。use studentSelect * from studentorder by 性别性别 compute avg(年龄年龄) by 性别性别注意：注意：如果使用如果使用COMPUTE BY，则必须也使用，则必须也使用ORDER BY子句。表达式必须与在子句。表达式必须与在 ORDER BY 后列出的子句相同或是后列出的子句相同或是其子集，并且必须按相同的序列。其子集，并且必须按相同的序列。第第5章章 数据查询与操作数据查询与操作4、量词查询、量词查询(any表示任意一个，表示任意一个， all表示所有的表示所有的)例例7：求大于最小女生年龄的所有学生信息

59、。：求大于最小女生年龄的所有学生信息。use studentSelect * from student where 年龄年龄 (select min(年龄年龄) from student where 性别性别=女女)或或use studentSelect * from student where 年龄年龄 any (select 年龄年龄 from student where 性别性别=女女)第第5章章 数据查询与操作数据查询与操作4、量词查询、量词查询(any表示任意一个，表示任意一个， all表示所有的表示所有的)例例8：求大于最大男生年龄的所有学生信息。：求大于最大男生年龄的所有学生信息

60、。use studentSelect * from student where 年龄年龄 (select max(年龄年龄) from student where 性别性别=男男)或或use studentSelect * from student where 年龄年龄 all (select 年龄年龄 from student where 性别性别=男男)第第5章章 数据查询与操作数据查询与操作5、Top n 查询前查询前n条记录条记录例例9：求：求student表中年龄最小的表中年龄最小的2名学生信息。名学生信息。use studentSelect top 2 * from student