第七章 SQL Server数据库应用开发技术

第7章数据查询知识技能目标:1．理解数据查询的意义。2．熟练掌握各种查询技术，包括单表查询、多表查询、汇总查询、分类汇总、汇总计算、子查询等，并能对查询结果排序、合并、保存。第7章数据查询引导案例：按照第2章的设计在计算机上建立起教务管理数据库后，就可以访问其中的数据了，如查看某个班的学生信息，查看某门课程的信息，查看某个学生某门课程的成绩，统计某班某门课程的最好成绩、最差成绩、平均成绩等。如何实现这些操作呢？这就需要使用数据查询技术。本章就来介绍各种数据查询技术。7.1SELECT语句7.2简单查询7.3多表查询7.4汇总查询7.5子查询小结第7章数据查询7.1SELECT语句查询是关系数据库中最基本的数据操作，通过SELECT语句来实现基本语法格式：SELECT[ALL|DISTINCT]字段列表[INTO目标数据表]FROM源数据表[，…n][WHERE条件表达式][GROUPBY分组表达式][HAVING搜索表达式][ORDERBY排序表达式[，…n][ASC][DESC]][COMPUTE行聚合函数名（统计表达式）[，…n][BY分类表达式[，…n]]]返回目录7.2简单查询7.2.1基本查询语法格式：SELECT字段名1[，…字段名n]FROM表名功能：从指定表中查询指定字段的值。1.查询指定字段在很多情况下，我们只对表中的一部分属性列感兴趣，查询时可以指定需要显示的部分列名，而过滤掉表中不需要的列，查询结果中数据的排列顺序与选择列表中所指定的列名排列顺序相同。【例7.1】在教务管理数据库中查询学生的学号、姓名和班级。返回目录7.2简单查询7.2.1基本查询2.查询所有字段要将表中所有属性列都列出来有两种方法：一是在SELECT关键字后面列出所有的列名；二是简单的将字段列表用“*”号表示，查询结果将按用户创建表格时声明列的顺序来显示所有的列名。【例7.2】在教务管理数据库中查询每门课程的全部信息。返回目录7.2简单查询7.2.1基本查询3．设置字段别名在显示查询的结果时，为了阅读方便，用户可以根据实际需要对查询数据的列标题进行修改，或者为没有标题的列加上临时标题。语法格式：表达式[as]别名或字段别名=表达式【例7.3】在教务管理数据库中查询学生姓名、性别和年龄。返回目录7.2简单查询7.2.1基本查询4．查询全部记录要查询全部记录可在SELECT后使用ALL，ALL是默认设置，因此也可以省略。【例7.4】在教务管理数据库中查询所有班级的人数。5．取消重复行若要在查询结果中去掉重复的行，需在SELECT子句中使用关键字DISTINCT。【例7.5】在教务管理数据库中查询被选的课程。返回目录7.2简单查询7.2.1基本查询6．查询前面部分记录语法格式：SELECTTOPn|TOPnPERCENT列名1[，…n]FROM表名其中：TOPn表示返回最前面的n行，n表示返回的行数；TOPnPERCENT表示返回的最前面的n%行。【例7.6】查询学生表中前10条记录。【例7.7】查询学生表中前面10%条记录。返回目录7.2简单查询7.2.2条件查询条件查询是指在数据库中按给定条件进行查询，在SELECT语句中使用WHERE子句可以给定查询条件。语法格式：SELECT字段名1[，…字段名n]FROM表名WHERE条件表达式其中的条件表达式描述了查询条件。构成这个条件表达式的运算符包括：比较运算符：=、、、、!、!、=、=、!=逻辑运算符：NOT、AND、OR、BETWEENAND、IN、ANY、SOME、ALL、LIKE、EXISTS、ISNULL、ISNOTNULL返回目录7.2简单查询7.2.2条件查询1．使用比较运算符【例7.8】在教务管理数据库中查询各门课程的不及格学生的学号、课程号、成绩。2．使用逻辑运算符【例7.9】在教务管理数据库中查询00001号课程的不及格学生的学号、课程号、成绩。返回目录7.2简单查询7.2.2条件查询3．使用BETWEEN关键字语法格式：表达式[NOT]BETWEEN表达式1AND表达式2功能：当表达式的值在表达式1的值与表达式2的值之间时，返回真，否则返回假。【例7.10】在教务管理数据库中查询00001号课程成绩在90分到100分之间的学生。返回目录7.2.2条件查询4．使用IN关键字语法格式：表达式[NOT]IN(表达式1,表达式2[,…表达式n])功能：当表达式的值为IN后面括号中的表达式1，表达式2，…表达式n的值之一时，返回真，否则返回假。【例7.11】在教务管理数据库中查询jy071、jy072、jw071班的学生。返回目录7.2简单查询7.2简单查询7.2.2条件查询5．使用LIKE关键字LIKE关键字用于模糊查询。在实际的应用中，有时不能精确地给出查询条件，只知道一些不确切的查询线索，这时就需要进行模糊查询。语法格式：表达式[NOT]LIKE‘格式串’其中格式串通常与通配符配合使用，给出一个模式。返回目录7.2简单查询7.2.2条件查询5．使用LIKE关键字通配符如下：百分号%：代表任意长度的字符串。下划线_：代表任意单个字符。封闭方括号[]：代表方括号里列出的任意一个字符。[^]：代表任意一个没有在方括号里列出的字符。【例7.12】查询jy071、jy072和jw071班学生的姓名和班级。返回目录7.2简单查询7.2.2条件查询6．使用IS[NOT]NULL关键字在涉及空值的查询中，可以使用ISNULL或者ISNOTNULL来指定这种查询条件。通常情况下，该查询条件要与其他的查询条件共同使用，NULL值表示列的数据值未知或不可用。语法格式：表达式IS[NOT]NULL【例7.13】在选课表中查询没有成绩的记录。返回目录7.2简单查询7.2.3查询结果排序对查询结果进行排序，需使用ORDERBY子句。语法格式：ORDERBY表达式1[ASC|DESC][，…n]功能：查询结果按照ORDERBY后的表达式的值排序，ASC表示升序DESC表示降序，默认为升序。【例7.14】查询000001号课程的学生成绩，结果按分数从高到低的显示，成绩相同的情况按学号顺序排列。返回目录7.2简单查询7.2.4查询结果保存查询的结果可以直接显示，也可以不直接显示，而是保存到一个数据表中，或保存到一个临时表中。保存查询结果，需使用INTO子句。语法格式：INTO目标数据表【例7.15】查询jy071班学生的信息，将结果保存到数据表jy071中如果要将查询结果存放到临时表，则在临时表名前要加“#”号。【例7.16】将所有不及格学生的学号、课程号、成绩存入临时表中返回目录7.3多表查询在上一节的查询举例中，所查询的数据都来自一个表，而在实际应用中，要查询的数据经常来自多个表，这时就需要进行多个表之间的连接查询。连接查询实际上是通过多个表之间的共同列的相关性来查询数据的，是数据库查询最主要的方式。连接查询就是关系运算的连接运算。在SQLServer中连接查询分为内连接查询、外连接查询、交叉连接查询。返回目录7.3多表查询7.3.1内连接查询内连接也叫自然连接，是将两个表中满足连接条件的行组合起来作为查询的结果。语法格式：FROM表1[inner]JION表2ON条件表达式【例7.17】从教学管理数据库中查询学生的学号、姓名和所学课程的课程号、成绩。【例7.18】从教学管理数据库中查询学生的学号、姓名、课程名和成绩。返回目录7.3多表查询7.3.2外连接查询使用外连接，以主表中每行的数据去匹配从表中的数据行：如果符合连接条件则返回到结果集中；如果没有找到匹配行，则主表的行仍然返回到结果集中，相应的从表中的数据行被填上NULL值后也返回到结果集中。根据返回行的主从表形式不同，外连接可以分为左外连接、右外连接和全外连接。左外连接是以连接中的左表为主表，对左表不加限制；右外连接是以连接中的右表为主表，对右表不加限制；全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。返回目录7.3多表查询7.3.2外连接查询1．左外连接左外连接是指返回所有的匹配行，并从关键字JOIN左边的表中返回所有不匹配行。由此可知，即使不匹配，JOIN关键字左边的表中数据也将被保留，所以在左外连接中JOIN关键字左边的表为主表，右边的表为从表。语法格式：FROM表1LEFT[OUTER]JION表2ON条件表达式【例7.19】从教学管理数据库中查询学生的学号、姓名、课程名和成绩（包括没有成绩的新入学同学的学号、姓名）。返回目录7.3.2外连接查询2．右外连接与左外连接相反，右外连接返回所有的匹配行，并从关键字JOIN右边的表中返回所有不匹配的行。因此，在右外连接中JOIN关键字右边的为主表，而关键字左边的为从表，右外连接返回结果与左外连接相同，即不满足匹配的结果集在相应列中填入NULL值。语法格式：FROM表1RIGHT[OUTER]JION表2ON条件表达式【例7.20】从教学管理数据库中查询学生的学号、姓名、课程名和成绩（包括还没有被选过的新课的名称）。返回目录7.3多表查询7.3多表查询7.3.2外连接查询3．全外连接全外连接又称完全外连接，该连接查询方式返回连接表中所有行的数据。与左外连接相同，JOIN关键字左边的为主表，而JOIN关键字右边的从表。根据匹配条件，如果满足匹配条件则返回数据，如果不满足匹配条件时，同样返回数据只不过在相应的列中填入NULL值。在整个全外连接返回结果中，包含了两个完全连接表的所有数据。语法格式：FROM表1FULL[OUTER]JION表2ON条件表达式【例7.21】从学生表和班级表中查询同姓的班主任和学生的姓名。返回目录7.3多表查询7.3.3交叉连接查询交叉连接也叫非限制连接，它是将两个表不加任何约束地组合起来。使用交叉连接，如果不带WHERE子句时，则返回的结果是被连接的两个表的笛卡尔积；如果交叉连接带有WHERE子句时，则返回结果为连接两个表的笛卡尔积减去WHERE子句所限定而省略的行数。语法格式：FROM表1CROSSJION表2或FROM表1，表2WHERE条件表达式【例7.22】查询每个学生的必修课程。返回目录7.3多表查询7.3.4自连接查询连接操作不仅可以在不同的表上进行，也可以在同一张表内进行自身连接，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。【例7.23】从学生表中查询同名学生的姓名和学号。返回目录7.3多表查询7.3.5合并结果集合并结果集不同于连接查询，它是将两个或两个以上的查询结果纵向合并在一起，形成一个结果集。合并结果集通过UNION关键字来实现。语法格式：SELECT语句UNION[ALL]SELECT语句【例7.24】查询jy071班学生和班主任的姓名。返回目录7.3多表查询7.3.5合并结果集注意：使用UNION合并的结果集都必须具有相同的结构，它们的列数必须相同，并且相应的结果集列的数据类型必须兼容。合并形成的结果集的列名与第一个SELECT语句的结果集中的列名相同。默认情况下，UNION将从结果集中删除重复行。如果使用ALL关键字，那么结果中将包含所有行，即不删除重复行。如果使用UNION，那么各个SELECT语句不能包含它们自己的ORDERBY或COMPUTE子句，而只能在最后一个SELECT语句的后面使用一个ORDERBY或COMPUTE子句，该子句适用于最终的合并结果集。返回目录7.4汇总查询汇总查询是用SELECT语句对表中数据进行统计汇总，得到汇总信息。汇总查询需要使用聚合函数，常用的聚合函数有sum()、avg()、max()、min()、count()等。7.4.1简单汇总简单汇总是在SELECT语句的选择列表中只使用聚合函数对表中的指定列进行计算，将所有符合查询条件的数据汇总到一起，生成一个汇总数据记录。【例7.25】在教务管理数据库中查询000001号课程的最高分、最低分、平均分、总分和选修这门课的学生