数据库与程序设计

目 录 第 1 章 数据库技术基础 第 2 章 SQL Server基础 第 3 章 数据库管理 第 4 章 查询和视图 目 录 第 1 章 数据库技术基础 第 2 章 SQL Server基础 第 3 章 数据库管理 第 4 章 查询和视图 第 5 章 存储过程和触发器 第 6 章 基于Web的数据库开发技术 第 7 章 数据库应用开发案例 第 8 章 数据库安全性与维护 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

第4章 查询与视图 所谓查询，即按照指定条件在数据表中查找所需的数据内容。 视图是为了用户查询方便或根据数据安全的需要而建立的虚表。 2017/3/5 2017/3/5 所谓查询，即按照指定条件在数据表中查找所需的数据内容。 查询功能是SQL语言的核心功能，是数据库中使用最多的操作。 视图是为了用户查询方便或根据数据安全的需要而建立的虚表。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 2

第4章 查询与视图 本章所有的例题涉及的数据库是在第三章中创建的“student”数据库，所涉及的数据见表4-1、4-2、4-3。 sno 2017/3/5 2017/3/5 本章所有的例题涉及的数据库是在第三章中创建的“student”数据库，所涉及的数据见表4-1、4-2、4-3。 sno sname ssex sdept sifdy sresume sbirthday 2008001 张兰 女 信管系 true null 1990-10-10 2008002 王小惠 工商系 false 1991-3-12 2008003 李大力 男 1989-8-18 2008004 胡晨 会计系 1990-1-1 2008005 张小均 1993-7-6 2008006 钱昆 营销系 2008007 郑一明 1991-11-23 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 3

第4章 查询与视图 sno cno Grade 2008001 c01 87 c02 85 2008002 c03 90 c04 89 2017/3/5 2017/3/5 sno cno Grade 2008001 c01 87 c02 85 2008002 c03 90 c04 89 2008003 78 60 c05 74 2008004 32 2008005 95 cno cname csemester ccredit pre_cno c01 大学计算机基础 1 3 c02 C语言 2 4 c03 数据结构 c04 数据库原理与应用 c05 软件工程 5 c06 计算机网络 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 4

4.1 数据查询 第4章 查询与视图 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》 山东大学出版社 2017/3/5 2017/3/5 第4章 查询与视图 4.1 数据查询 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 5

4.1 数据查询 4.1.1 数据查询概述 SELECT语句比较复杂，其语句格式中除了一些基本参数外，还有大量的选项(子句)。 查询语句用来对已经存在于数据库中的数据按照特定的组合、条件表达式或者一定次序进行检索。 SQL Server提供了数据查询语句SELECT较完整的语句形式。 SELECT语句比较复杂，其语句格式中除了一些基本参数外，还有大量的选项(子句)。 该语句具有灵活的使用方式和丰富的功能 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.1 数据查询概述 SELECT查询语句基本格式如下，： SELECT < select_list > FROM < table_source > [WHERE < search_condition >] SELECT基本查询语句是由SELECT子句、FROM子句和WHERE子句组成。 SELECT子句用于指定将要查询的列名称< select_list >; FROM子句指定了查询数据来自于哪些表或视图< table_source >; WHERE子句用于指定查询应该满足的条件< search_condition >，缺省则为无条件，即全部数据记录参与查询。 在一般的检索中，SELECT子句和FROM子句是必不可少的，只有当SELECT子句中仅包含常量、变量或算数表达式，没有列名时，FROM子句才可以省略(无源输出，见第2章)。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.1 数据查询概述 SELECT语句的完整语法较复杂，其主要子句可归纳如下： SELECT < select_list > [ INTO < new_table > ] FROM < table_source > [ WHERE < search_condition > ] [ GROUP BY < group_by_expression > ] [ HAVING < search_condition > ] [ ORDER BY < orderby_expression > [ ASC | DESC ]][,…n] [ COMPUTE { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ BY expression ] ] SELECT 语句中的子句顺序非常重要。可以省略可选子句，但这些子句在使用时必须按适当的顺序出现。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 简单查询一般是指只涉及一个表的查询。利用SELECT和FROM子句可以实现最简单的查询，其中SELECT子句的功能是指定要查询的结果列。其语法格式如下： SELECT [ ALL | DISTINCT ] [ TOP <n > [ PERCENT ] ] < select_list > FROM < table_source > 其中select_list定义为： < select_list > ::= { * | { table_name | view_name | table_alias }.* | { column_name | expression } [ [ AS ] column_alias ][,…n] | column_alias = expression[,…n] } 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 各参数说明如下： ALL | DISTINCT ： DISTINCT指明结果集中如果有值相同的行，则只显示其中的一行；对于 DISTINCT 关键字来说，Null 值是相等的。 TOP <n> 指明仅返回结果集中的前n行，而TOP <n> PERCENT 则返回结果集中的百分之n行记录。 < select_list >：要查询的结果列，结果列是以逗号分隔的一系列表达式，可指定的表达式的最大数目是4096。 FROM子句指定了这些查询来自于哪些表或视图。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 各参数说明如下： < select_list >可定义以为： *：返回在FROM子句中包括的所有对象的全部列 这些列按 FROM 子句中指定的表或视图顺序返回，并对应于它们在表或视图中的顺序。 { table_name | view_name | table_alias }.*：指明返回指定表或视图或别名表的全部列。 column_name：指明返回的列名，一般需限定 column_name 以避免引用不明确。 expression：是一个由列名、常量、函数通过操作符连接起来的数据表达式，作为返回结果集中的一列。 column_alias：指明用以代替出现在结果集中的列别名或表达式的别名。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 1．查询指定的数据列 查询指定的数据列是指可以在SELECT子句中指定将要检索的列名称。 选择指定的列名称时，列名称应该与表中定义的列名称一致，否则就可能出错或者得到意想不到的结果； 列名称之间的顺序既可与表中定义的列顺序相同，也可不相同。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 1．查询指定的数据列(投影运算) （1）查询全部列 [例4-1]：查询所有学生的详细信息。 在SQL编辑器中输入以下语句： SELECT sno,sname,ssex,sdept,sifdy,sresume,sbirthday FROM student 或 SELECT * FROM student 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 执行结果如图4-1所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 1．查询指定的数据列（投影运算） [例4-2]：查询所有学生的学号与姓名。 （2）查询指定的列 [例4-2]：查询所有学生的学号与姓名。 SELECT sno,sname FROM student 执行结果如图4-2所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 1．查询指定的数据列 （3）消除取值重复的行 [例4-3]：查询有过选课记录的学生的学号。 SELECT DISTINCT sno FROM sc 执行结果如图4-3所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 2．使用文字串 通常情况下，使用单引号将文字串引起来（即字符常量）。 可以通过在SELECT关键字后面增加文字串的方式，来提高SELECT语句检索结果的可读性 通常情况下，使用单引号将文字串引起来（即字符常量）。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 2．使用文字串 [例4-4] 查询所有学生的出生日期。 SELECT sname, 'birthday is',sbirthday FROM student 注：文字串中两个紧连的单引号表示一个单引号。 执行结果如图4-4所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 3．改变列标题 在默认情况下，查询结果中所显示出来的列标题就是在定义表时使用的列名称。 在查询结果中可以根据用户的需要改变显示的列标题。 改变列标题也就是为指定的列定义一个别名。 改变列标题有两种方法： 一种方法是使用等号（=）； 另一种方法是使用AS关键字。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 3．改变列标题 [例4-5] 查询所有学生的编号，姓名和所在系。 Use student Go SELECT '编号'=sno, '姓名'=sname,sdept as 所在系 FROM student 或 SELECT 编号=sno, 姓名=sname,sdept as 所在系 FROM student 执行结果如图4-5所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 4．数据运算 数据运算是指对查询的数据进行各种运算。也就是说，可以在SELECT关键字后面列出的列项中使用各种运算符和函数。 这些运算符和函数包括： 算术运算符、字符串运算符等 数学函数、 字符串函数、 日期和时间函数、 系统函数等等。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 4．数据运算 [例4-6]：查询所有课程的名称、学分以及学分增加20% SELECT cname,ccredit,ccredit*1.2 AS '学分增加20%' FROM course 执行结果如图4-6所示 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.2 基本查询 4．数据运算 查询Student表中每个同学的学号、姓名、年龄。 SELECT sno as 学号,sname as 姓名,datediff(sbirthday,getdate()) as 年龄 from student 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询（选择运算） WHERE子句中常用的查询条件如表4-4所示： 查询条件 谓词 比较(见第2章) =，>，<，>=，<=，!=，<>，!>，!< 确定范围 BETWEEN AND，NOT BETWEEN AND 确定集合 IN，NOT IN 字符匹配 LIKE，NOT LIKE 空值 IS NULL，IS NOT NULL 多重条件（逻辑运算，见第2章） AND，OR，NOT 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 1．比较大小 [例4-7]：查询性别为“女”的学生的学号、姓名和系别。 SELECT sno,sname,sdept FROM student WHERE ssex=N'女' 执行结果如图4-7所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 1．比较大小 [例4-8]：查询学分大于2的所有课程的名称及所在学期 SELECT cname,csemester FROM course WHERE ccredit>2 执行结果如图4-8所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 2．确定范围 [例4-9]：查询成绩在80到100之间的学生及课程信息。 SELECT * FROM sc WHERE grade BETWEEN 80 AND 100 或 SELECT * FROM sc WHERE grade>=80 AND grade<=100 执行结果如图4-9所示。 说明：BETWEEN……AND……的范围是闭区间，包括其上限值和下限值。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 2．确定范围 [[例4-10]：查询出生日期不在1990年到1992年之间的学生的姓名、性别和所属系。 SELECT sname,ssex,sdept FROM student WHERE sbirthday NOT BETWEEN '1990-01-01' AND '1992-01-01' 执行结果如图4-10所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 3．确定集合 [例4-11]：查询信管系、会计系、营销系学生的姓名和性别。 SELECT Sname,Ssex FROM Student WHERE Sdept IN ( N'信管系',N'会计系',N'营销系'); 或 SELECT Sname,Ssex FROM Student WHERE Sdept=N'信管系‘ or Sdept=N'会计系’ or Sdept=N'营销系'); 执行结果如图4-11所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 3．确定集合 [例4-12]：查询不是信管系、会计系、营销系的学生的姓名和性别。 SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN ( N'信管系',N'会计系',N'营销系'); 执行结果如图4-12所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 4．字符匹配 在WHERE子句中，使用谓词LIKE进行字符串的匹配，其一般的语法格式如下： [ NOT ] LIKE '<匹配串>' 其含义是查找指定的属性列值与<匹配串>相匹配的记录。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 4．字符匹配 <匹配串>可以是一个完整的字符串，也可以含有通配符。在SQL Server中，有如表4-5所示的四种通配符： 通配符 功能 实例 % 代表零或多个字符为任意的 “sn%”，表示“sn”后可接任意字符串 _（下划线） 代表某一个位置上的字符为任意的 “s_n”，表示第一字符为s，第二个字符任意，第三个字符为n [ ] 表示在某一范围内的字符 [1-9]，表示1~9之间的字符 [^] 表示不在某一范围内的字符 [^1-9]，表示不在1~9之间的字符 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 4．字符匹配 [例4-13]：查询姓“张”的学生姓名。 SELECT sname FROM student WHERE sname LIKE N'张%' 执行结果如图4-13所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 4．字符匹配 [例4-14]：查询姓“张”且全名是两个汉字的学生姓名。 SELECT sname FROM student WHERE sname LIKE N'张_' 执行结果如图4-14所示。 说明：在汉化的SQL Server中，“_”可以代表一个汉字或英文ASCII字符，由系统自动判断。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 5．空值 [例4-15]：查询没有先行课的课程的名称。 SELECT cname FROM course WHERE pre_cno IS NULL 执行结果如图4-15所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 6．多重条件 [例4-16]：查询信管系的女生的学号、姓名。 SELECT sno,sname FROM student WHERE sdept=N'信管系' AND ssex=N'女' 执行结果如图4-16所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.3 条件查询 6．多重条件 [例4-17]：查询信管系的女生和会计系的学生的姓名、系别和性别。 SELECT sname,sdept,ssex FROM student WHERE sdept=N'信管系' AND ssex=N'女' OR sdept=N'会计系' 执行结果如图4-17所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.4 排序 用ORDER BY子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，空值被视为最低的可能值。 ORDER BY子句的语法格式如下： ORDER BY { orderby_expression [ ASC | DESC ][,…n]} 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.4 排序 各参数说明如下： orderby_expression：指定要排序的列或表达式。 可以将排序列指定为一个名称或列别名 也可指定一个表示该名称或别名在选择列表中所处位置的非负整数。 列名和别名可由表名或视图名加以限定（多表操作时）。 可指定多个排序列，ORDER BY子句中的排序列序列定义了排序结果集的结构。 ORDER BY子句可包括未显示在选择列表中的项。但是，如果未X指定 SELECT DISTINCT，或者如果 SELECT 语句包含 UNION 运算符，则排序列必须显示在选择列表中。 ASC | DESC ： ASC指明查询结果按升序排列，为系统默认值。DESC指明查询结果按降序排列。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.4 排序 1．查询结果排序 [例4-18]：查询所有男生的学号、姓名和出生日期，并按照出生日期降序排列；若出生日期相同，则按学号升序排列。 Use student Go SELECT sno,sname,sbirthday FROM student WHERE ssex=N'男' ORDER BY sbirthday DESC,sno 执行结果如图4-18所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.4 排序 1．查询结果排序 use student go SELECT distinct sno,cno FROM sc ORDER BY sno 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.4 排序 2．选取查询结果的前几行数据 用TOP <n>或TOP <n> PERCENT来选取查询结果的前几行或前百分之几行的数据，此语句经常和ORDER BY子句一起使用。 [例4-19]：查询选修c01号课程的学生学号和成绩，查询结果按成绩升序排列；若成绩相同，则按学号降序排列，取查询结果的前两行记录。 Use student Go SELECT TOP 2 sno,grade FROM sc WHERE cno='c01' ORDER BY grade,sno DESC 执行结果如图4-19所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.4 排序 2．选取查询结果的前几行数据 Use student Go SELECT TOP 10 percent sno,grade FROM sc ORDER BY grade,sno DESC 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.5 查询输出 查询的信息有时往往需要保存下来，在使用SELECT语句查询数据时，可以使用INTO子句将查询后的数据存储到一个新的表中。 新表中的列按选择列表指定的顺序创建，新表中的每列与选择列表中的相应表达式具有相同的名称、数据类型和值。 INTO子句不能与COMPUTE子句同时使用。 INTO子句的语法格式如下： INTO new_table 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.5 查询输出 1．存储到基本表中 [例4-20]：查询工商系所有学生的信息，并存储到名为“gs_student”的基本表中。 SELECT * INTO gs_student FROM student WHERE sdept=N'工商系'; SELECT * FROM gs_student 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.5 查询输出 2．存储到临时表中 如果查询结果存储到临时表中，则需要在表名前加“#”字符。 临时表不存储在当前数据库内，而是存储在系统数据库tempdb内。 如果当用户断开数据库连接时没有除去临时表，SQL Server将自动除去临时表。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.5 查询输出 2．存储到临时表中 [例4-21]：查询学分为3的所有课程信息，并存储到名为“course_credit3”的临时表中。 SELECT * INTO #course_credit3 FROM course WHERE ccredit=3 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.6聚合查询 聚合查询是指通过查询对一组数据(一列中)进行聚合运算得到聚合值的过程。 在聚合运算中主要是使用聚合函数。 在Microsoft SQL Server 2005系统中，一般情况下，可以在Select的三个子句中使用聚合函数，即： SELECT子句、 COMPUTE子句、 HAVING子句。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.6聚合查询 1．SELECT子句中的聚合 在聚合运算的表达式中可以包括列名、常量以及由算术运算符连接起来的函数。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.6聚合查询 SQL Server中提供了许多聚合函数，常用的聚合函数如表4-6所示。 函数 功能 COUNT([DISTINCT | ALL]*) 统计记录个数 COUNT([DISTINCT | ALL]<列名>) 统计一列中值的个数 SUM([DISTINCT | ALL]<列名>) 计算一列值的总和(此列必须是数值型) AVG([DISTINCT | ALL]<列名>) 计算一列值的平均值(此列必须是数值型) MAX([DISTINCT | ALL]<列名>) 求一列值中的最大值 MIN([DISTINCT | ALL]<列名>) 求一列值中的最小值 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.6聚合查询 1．SELECT子句中的聚合 [例4-22]：统计所有的学生人数。 SELECT COUNT(*) AS 学生人数 FROM student 执行结果如图4-22所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.6聚合查询 1．SELECT子句中的聚合 [例4-23]：统计有选课记录的学生人数。 SELECT COUNT(DISTINCT sno) AS 有选课记录的学生人数 FROM sc 执行结果如图4-23所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.6聚合查询 1．SELECT子句中的聚合 [例4-24]：统计选修“c01”课程的平均成绩、最高成绩和最低成绩。 SELECT AVG(grade) AS c01课程的平均成绩, MAX(grade) AS c01课程的最高成绩, MIN(grade) AS c01课程的最低成绩 FROM sc WHERE cno='c01' 执行结果如图4-24所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.6聚合查询 2．COMPUTE子句中的聚合 在SELECT子句中出现聚合函数时，结果集中的数据全是聚合值，没有明细值。 COMPUTE子句不仅可以使用聚合函数计算聚合值，而且可以依然保持原有的明细值，新的聚合值作为附加的汇总列出现在结果集的最后。 需要注意的是，如果是用 COMPUTE 子句指定的行聚合函数，则不允许使用 DISTINCT 关键字。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.6聚合查询 2．COMPUTE子句中的聚合 COMPUTE子句的语法格式如下： 各参数说明如下： COMPUTE { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ BY expression ] 各参数说明如下： AVG | COUNT | MAX | MIN | SUM：这些参数与对应的函数有相同的含义。 expression：指定需要统计的列名，此列必须包含在SELECT列表中，不能用别名。 BY expression：在结果集中生成控制中断和小计。 expression 是关联 ORDER BY 子句中所指定的排序列的相同副本，通常是列名或列别名，可以指定多个。 在 BY 之后列出多个表达式将把组划分为子组，并在每个组级别应用聚合函数。 注意：如果使用 COMPUTE子句的 BY expression ，则还必须使用ORDER BY子句。表达式必须与在ORDER BY 后列出的子句相同或是其子集，并且顺序必须相同。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 2．COMPUTE子句中的聚合 4.1.6聚合查询 [例4-25]：查询信管系学生的学号、姓名和年龄，最后汇总出该系学生的总人数和平均年龄。 SELECT sno,sname,year(getdate( ))-year(sbirthday) as 年龄 FROM student WHERE sdept=N'信管系' COMPUTE COUNT(sno),AVG(year(getdate( ))-year(sbirthday)) 执行结果如图4-25所示。 说明：可以看出，COMPUTE子句产生额外的汇总行，其列标题是系统自定的。COUNT函数的结果是cnt列，AVG函数的结果是avg列。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 2．COMPUTE子句中的聚合 4.1.6聚合查询 [例4-26]：查询“c01”、“c02”课程的选课成绩，并汇总出每门课程的平均成绩，最后汇总出成绩记录的数目和所有课程的平均成绩 SELECT sno,cno,grade FROM sc where cno='c01' or cno='c02' ORDER BY cno COMPUTE AVG(grade) BY cno COMPUTE COUNT(cno),AVG(grade) 执行结果如图4-26所示。 说明：该题使用了两个COMPUTE子句。前面的COMPUTE BY子句表明按课程号统计平均成绩，后面的COMPUTE子句则统计两门课程总的平均成绩。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.6聚合查询 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.7分组查询 GROUP BY子句对查询结果按照一定条件进行分组。 分组子句通常与SQL Server提供的聚合函数一起使用。 对查询结果分组的目的是为了细化聚合函数的作用对象： 如果未对查询结果分组，则聚合函数将作用于整个查询结果； 分组后聚合函数将作用于每一个组，每一个组都有一个函数值。 SELECT语句后的输出列只能是聚合函数和分组列。 如果GROUP子句带有HAVING子句，则只有满足指定条件的组才能输出。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.7分组查询 GROUP BY子句和HAVING子句的语法格式如下： GROUP BY [ ALL ] < groupby_expression > [ WITH { CUBE | ROLLUP } ] [HAVING < search_condition >] 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.7分组查询 各参数说明如下： ALL：返回所有可能的查询结果组合，即使此组合中没有任何满足WHERE子句条件的分组统计列。 Groupby_expression：指明分组条件，通常是一个或多个列名，但不能是别名。 在SELECT子句的字段列表中，除了聚集函数外，其他所出现的字段一定要在Group By子句中有定义才行。 SELECT子句的字段列表中不一定要有聚集函数，但至少要用到Group By子句列表中的一个项目。 在SQL Server中text、ntext和image数据类型的字段不能作为Group By子句的分组依据。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.7分组查询 WITH CUBE：除了返回由GROUP BY子句指定的行外，还返回按组统计的行排序返回的结果。 WITH ROLLUP：只返回第一个分组条件指定的列的统计行，改变列的顺序会使返回的结果的行数发生变化。CUBE和ROLLUP的具体用法见后面实例。 HAVING子句指定分组选择条件，与GROUP BY子句一起使用。 HAVING子句与WHERE子句相似，不同在于其作用对象，WHERE子句作用于行，HAVING子句作用于组，从中选择满足条件的组。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 1．使用GROUP BY子句 4.1.7分组查询 GROUP BY子句将查询结果按某一列或多列值分组，值相等的为一组。 [例4-27]：统计所有被选过的课程的课程号及被选过的次数。 SELECT cno,count(*) AS 被选次数 FROM sc GROUP BY cno 执行结果如图4-27所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.7分组查询 2．使用WITH{CUBE | ROLLUP}选项 使用这两个选项可以返回按组统计的数据行。 CUBE操作符对GROUP BY子句中的各列的所有可能组合均产生汇总行，而ROLLUP操作符只对GROUP BY子句中的第一个分组列产生汇总行。 CUBE 指定在结果集内不仅包含由 GROUP BY 提供的行，还包含汇总行。 GROUP BY 汇总行针对每个可能的组和子组组合在结果集内返回。 GROUP BY 汇总行在结果中显示为 NULL，但用来表示所有值。 结果集内的汇总行数取决于 GROUP BY 子句内包含的列数。 GROUP BY 子句中的每个操作数（列）绑定在分组 NULL 下，并且分组适用于所有其他操作数（列）。 由于 CUBE 返回每个可能的组和子组组合，因此不论在列分组时指定使用什么顺序，行数都相同。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.7分组查询 2．使用WITH{CUBE | ROLLUP}选项 [例4-28]：汇总各专业的男、女学生的人数。 SELECT sdept,ssex,COUNT(*) AS 人数 FROM student GROUP BY sdept,ssex WITH CUBE 执行结果如图4-28（a）所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.7分组查询 2．使用WITH{CUBE | ROLLUP}选项 ROLLUP 指定在结果集内不仅包含由 GROUP BY 提供的行，还包含汇总行。 按层次结构顺序，从组内的最低级别到最高级别汇总组。（即排在靠近Group By的分组字段的级别高，然后是依次递减。） 组的层次结构取决于列分组时指定使用的顺序。 更改列分组的顺序会影响在结果集内生成的行数。 重要提示：使用 CUBE 或 ROLLUP 时，不支持区分聚合，如 不能使用AVG(DISTINCT column_name)、COUNT(DISTINCT column_name) 和 SUM(DISTINCT column_name)。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.7分组查询 2．使用WITH{CUBE | ROLLUP}选项 （2）使用ROLLUP操作符 SELECT sdept,ssex,COUNT(*) AS 人数 FROM student GROUP BY sdept,ssex WITH ROLLUP 执行结果如图4-28（b）所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.7分组查询 3．使用HAVING子句 如果分组后还需要按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING子句指定组的筛选条件。 [例4-29]：统计被选过2次和2次以上的课程的课程号及被选过的次数。 SELECT cno,count(*) AS 被选次数 FROM sc GROUP BY cno HAVING COUNT(*)>=2 执行结果如图4-29所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 若一个查询同时涉及两个或两个以上的表，并且每一个表中的数据往往作为一个单独的列出现在结果集中，则称之为连接查询。 在设计表时，经常把相关数据分散到不同的表中，但是，在使用数据时，需要把这些数据集中在一起，连接查询可以满足这种客观需求。 连接操作给用户带来很大的灵活性: 通过连接可以为不同实体创建新的数据表； 还可以使用一个表中的数据来查询其他表的数据。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 在Microsoft SQL Server 2005系统中，这种连接操作又可以细分为交叉连接、内连接、外连接等。 连接查询的表示形式有： 符合SQL标准连接谓词的表示形式，即＝、>、<、>=、<=、!=以及BETWEEN…AND等用于连接条件，出现在WHERE子句中； 使用关键字JOIN的表示形式：在FROM子句中使用关键字JOIN的表示形式连接数据表，其连接条件出现在ON关键字后。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 FROM子句指定需要进行数据查询的表，其语法格式如下： FROM < table_source > table_source：指明SELECT语句所使用的表、视图等数据源。 其中table_source定义为： < table_source > ::= {table_name [[AS] table_alias] | view_name [[AS] view_alias] | derived_table [[AS] table_alias] | <joined_table>} [,…n] 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 各参数说明如下： table_name [ [ AS ] table_alias ]：指明表名和表的别名。 view_name [ [ AS ] view_alias ]：指明视图名和视图的别名。 derived_table [ [ AS ] table_alias ]：是从指定的数据库和表中检索的子查询结果。 joined_table： 指明由连接查询生成的查询结果作数据源。对于多个连接，可使用圆括号来更改连接的自然顺序。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 其中joined_table定义为： < joined_table > ::= < table_source > < join_type > < table_source > ON< join_condition > | < table_source > CROSS JOIN < table_source > | [(] < joined_table > [ )] 其中join_type定义为： < join_type > ::= [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ] JOIN 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 各参数说明如下： join_type：指明连接查询操作的类型，其中： INNER：内连接，指定返回两个表中所有匹配的行，此项是默认值。 LEFT [OUTER]：左外连接，返回连接查询左边的表中所有的相应记录，在右表中对应于左表中无记录的部分用NULL表示。 RIGHT [OUTER]：右外连接，返回连接查询右边的表中所有的相应记录，在左表中对应于右表中无记录的部分用NULL表示。 FULL [OUTER]：完全外连接，返回连接查询两个表中所有的相应记录，对应于无记录的部分用NULL表示。 JOIN：指示指定的连接操作应在指定的表源或视图之间执行。 ON < search_condition >：指定连接查询的条件。 CROSS JOIN：交叉连接，返回两个表交叉查询的结果。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 1．交叉连接 交叉连接不使用任何连接条件来限制结果集合，而是对两个数据源中的行进行所有可能的组合，也就是做广义笛卡儿积。 交叉连接在查询结果集中，包含了所连接的两个表中所有行的全部组合，其结果的行数等于两个表行数之积，列数等于两个表列数之和。 例如，如果对A表和B表执行交叉连接，A表中有5行数据，B表中有12行数据，那么结果集中可以有60行数据。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 1．交叉连接 [例4-30]：对student表和course表做交叉连接 （1）用谓词表示交叉连接 SELECT student.*,course.* FROM student,course 或 SELECT * FROM student,course 执行结果：一共42行，是这两个表各行的笛卡儿积组合。部分结果如图4-30所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 1．交叉连接 （2）用JOIN关键字表示交叉连接 SELECT student.*,course.* FROM student CROSS JOIN course 执行结果同上。 需要注意的是交叉连接不能有连接条件，且不能带WHERE子句。 交叉连接一般没有有意义的语义，实际中应用不多，但是交叉连接是理解内连接和外连接的基础。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 2．内连接 内连接是用比较运算符比较表中列值，返回符合连接条件的数据行，从而将两个表连接成一个新表。 内连接根据不同的情况，可分为： 等值连接、 自然连接、 非等值连接、 自连接。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 2．内连接 （1）等值连接 在连接条件中使用等号，通过相等的字段连接起来的查询称为等值连接。 [例4-31]：查询工商系有选课记录的学生的全部信息以及他们的选课信息。 ①用谓词表示等值连接： SELECT student.*,sc.* FROM student,sc WHERE student.sdept=N'工商系' AND student.sno=sc.sno 执行结果如图4-31所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 2．内连接 （1）等值连接 ②用JOIN关键字表示等值连接： SELECT student.*,sc.* FROM student INNER JOIN sc ON student.sno=sc.sno WHERE student.sdept=N'工商系' 执行结果同上。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 2．内连接 [例4-32]查询信管系选修“C语言”课程的学生的姓名、课程名和成绩。 ①用谓词表示等值连接： SELECT sname, cname, grade FROM student S, course C, sc WHERE S.sno=sc.sno AND C.cno=sc.cno AND sdept=N'信管系'AND cname=N'C语言' 执行结果如图4-32所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 2．内连接 [例4-32]查询信管系选修“C语言”课程的学生的姓名、课程名和成绩。 ②用JOIN关键字表示等值连接： SELECT Sname, Cname, Grade FROM Student S JOIN SC JOIN Course C ON SC.Cno=C.Cno ON S.Sno=SC.Sno WHERE sdept=N'信管系'AND cname=N'C语言' 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 2．内连接 [例4-32-1]查询每个学生的绩点成绩。 SELECT sc.sno,sname, sum(grade*ccredit)/sum(ccredit) FROM student S, course C, sc WHERE S.sno=sc.sno AND C.cno=sc.cno Group by sc.sno 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 （2）自然连接（略） 若在等值连接中，把目标列中重复的字段（列）去掉则称为自然连接，它是一种特殊的等值连接。 [例4-33]：查询工商系有选课记录的学生的全部信息以及他们的选课信息，目标列中去掉重复的字段sno。 ①用谓词表示自然连接： SELECT student.*,sc.cno,sc.grade FROM student,sc WHERE student.sdept=N'工商系' AND student.sno=sc.sno 执行结果如图4-33所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 （2）自然连接 ②用JOIN关键字表示自然连接： SELECT student.*,sc.cno,sc.grade FROM student INNER JOIN sc ON student.sno=sc.sno WHERE student.sdept=N'工商系' 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 （3）非等值连接（略） 表之间的连接，如果使用除了“=”之外的连接符连接起来的查询称为非等值连接。 虽然SQL Server提供了非等值连接查询，但非等值连接查询的例子很少有实际应用价值，在此不再举例说明。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 （4）自连接（略） 连接可以是一个表与其自身进行的，这样的连接称为自连接。 若要在一个表中查询具有相同列值的行，可以使用自连接。 使用自连接时需为表指定两个别名，且对所有列的引用均要用别名来限定。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 （4）自连接（略） [例4-34]：要求根据课程信息查询每门课的间接现行课（即先行课的先行课）。 ①用谓词表示自连接： SELECT A.cno,A.cname,B.pre_cno FROM course A,course B WHERE A.pre_cno=B.cno 执行结果如图4-34所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 （4）自连接（略） ②用JOIN关键字表示自连接： SELECT A.cno,A.cname,B.pre_cno FROM course A INNER JOIN course B ON A.pre_cno=B.cno 执行结果同上。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 3．外连接 内连接的结果是从多个表的组合中筛选出符合连接条件的数据，如果数据无法满足连接条件，则将其丢弃。 在外连接中，不仅包括那些满足条件的数据，而且某些表不满足条件的数据也会显示在结果集中。也就是说，外连接只限制其中一个表的数据行，而不限制另外一个表中的数据。 外连接形式在许多情况下是非常有用的 例如在连锁超市统计报表时，不仅要统计那些有销售量的超市和商品，而且还要统计那些没有销售量的超市和商品。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 3．外连接 注意：外连接只能用于两个表中。 根据两个表的主次关系，外连接分为: 两个表有主次之分，以主表的每行数据去匹配从表的数据列，符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的从表的列将被赋予NULL值后再返回到结果集中。 根据两个表的主次关系，外连接分为: 左外连接 右外连接 完全外连接 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 3．外连接 （1）左外连接 [例4-35]：查询信管系所有学生的学号、姓名及他们所选过的课程的课程号和成绩。 SELECT student.sno, student.sname,sc.cno,sc.grade FROM student LEFT OUTER JOIN sc ON student.sno=sc.sno WHERE student.sdept=N'信管系' 执行结果如图4-35所示。 说明：“LEFT OUTER JOIN”表示左外连接，该短语左边的表是主表，右边的表是从表。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 3．外连接 （2）右外连接 [例4-36]：查询学分为2的课程的编号、名称以及选修的学生的编号和成绩 SELECT course.cno,course.cname,sc.sno,sc.grade, FROM sc RIGHT OUTER JOIN course ON sc.cno=course.cno WHERE course.ccredit=2 执行结果如图4-36所示。 说明：“RIGHT OUTER JOIN”表示右外连接，该短语左边的表是从表，右边的表是主表。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.8 连接查询 3．外连接 （3）完全外连接 使用完全外连接，结果中除了包括满足连接条件的行外，还包括两个表的所有行，不满足条件的行中的相关列取NULL值。（先做左连接，再做右连接） 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 一个SELECT-FROM-WHERE语句称为一个查询块(子查询)。 有时一个查询块无法完成查询任务，需要一个子查询块的结果作为父查询块的条件。 将一个查询块嵌套在另一个查询块的条件子句中的查询称为嵌套查询。 嵌套查询可用多个简单查询构成复杂的查询，从而增强查询功能。 SQL Server允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 嵌套查询分为“相关子查询”和“非相关子查询”。 带有EXISTS的查询称为“相关子查询”。 非相关子查询的求解顺序是由内向外，即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件（即出现在WHERE子句中）。 子查询中所存取的表可以是父查询没有存取的表，子查询选出的记录不显示。 需要特别指出的是，子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。 子查询要用圆括号括起来。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 在很多情况下嵌套查询可以写成连接的形式。 一般地说，由于嵌套查询的执行需要增加一些附加的操作例如排序，而连接不需要执行附加的操作。 应该优先使用连接。如果使用连接时语句过于复杂，再考虑使用嵌套查询。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 1．单值嵌套查询 子查询的返回结果是一个值的嵌套查询称为单值嵌套查询。 当能确切知道子查询返回的是单值时，可以用>、<、=、>=、<=、!=、<>等比较运算符将一个表达式的值与子查询返回的值进行比较运算。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 1．单值嵌套查询 [例4-37]：查询选修了“C02”的课程且成绩高于此课程平均成绩的学生的学号和成绩。 SELECT sno,grade FROM sc WHERE cno='C02' AND grade >(SELECT AVG(grade) FROM sc WHERE cno='C02') 执行结果如图4-37所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 [例4-38]：查询与学号为“2008001”的学生同系的所有学生的学号、姓名、性别及出生日期。 1．单值嵌套查询 [例4-38]：查询与学号为“2008001”的学生同系的所有学生的学号、姓名、性别及出生日期。 SELECT sno,sname,ssex,sbirthday FROM student WHERE sdept=(SELECT sdept FROM student WHERE sno='2008001') 执行结果如图4-38所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 2．多值嵌套查询 子查询的返回结果是一列值的嵌套查询称为多值嵌套查询。 （1）使用[NOT] IN运算符 IN运算符可以测试表达式的值是否出现在子查询返回结果集之中.其语法格式为： expression [ NOT ] IN ( subquery ) 其中，subquery是子查询或值列表。当表达式expression与子查询subquery的结果集中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，返回值则相反。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 [例4-39]：查询选过“c01”号课程的学生的学号、姓名和所在系。 2．多值嵌套查询 [例4-39]：查询选过“c01”号课程的学生的学号、姓名和所在系。 SELECT sno,sname,sdept FROM student WHERE sno IN (SELECT sno FROM sc WHERE cno='c01') 执行结果如图4-39所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 使用连接查询实现相同的功能： SELECT sc.sno,sname,sdept 2．多值嵌套查询 使用连接查询实现相同的功能： SELECT sc.sno,sname,sdept FROM student join sc on student.sno=sc.sno WHERE cno='c01' 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 2．多值嵌套查询 [例4-40]：查询没有选修“数据库原理与应用”课程的学生的学号、姓名和所在系。 SELECT sno,sname,sdept FROM student WHERE sno NOT IN (SELECT sno FROM sc WHERE cno IN (SELECT cno FROM course WHERE cname=N'数据库原理与应用')) 执行结果如图4-40所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 2．多值嵌套查询 也可写成： SELECT sno,sname,sdept FROM student WHERE sno IN (SELECT sno FROM sc WHERE cno NOT IN (SELECT cno FROM course WHERE cname=N'数据库原理与应用')) 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 使用连接查询实现相同的功能： 2．多值嵌套查询 SELECT s.sno,sname,sdept FROM Student S JOIN SC JOIN Course C ON SC.Cno=C.Cno ON S.Sno=SC.Sno WHERE cname<>N'数据库原理与应用' 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 2．多值嵌套查询 （2）利用谓词ANY或ALL expression { > | < | = | >= | <= | != | <> } { ANY | ALL } ( subquery ) 其中，subquery是子查询，expression是要进行比较的表达式。ANY和ALL说明对比较运算的限制，如表4-7所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 操作符 语义 >ANY 大于子查询结果中的某个值，即大于子查询结果中的最小值 <ANY 小于子查询结果中的某个值，即小于子查询结果中的最大值 >=ANY 大于等于子查询结果中的某个值，即大于等于子查询结果中的最小值 <=ANY 小于等于子查询结果中的某个值，即小于等于子查询结果中的最大值 =ANY 等于子查询结果中的某个值，即相当于IN查询 !=(或<>)ANY 不等于子查询结果中的某个值(若子查询结果是多值，通常没有实际意义) >ALL 大于子查询结果中的所有值，即大于子查询结果中的最大值 <ALL 小于子查询结果中的所有值，即小于子查询结果中的最小值 >=ALL 大于等于子查询结果中的所有值，即大于等于子查询结果中的最大值 <=ALL 小于等于子查询结果中的所有值，即小于等于子查询结果中的最小值 =ALL 等于子查询结果中的所有值(若子查询结果是多值，通常没有实际意义) !=(或<>)ALL 不等于子查询结果中的所有值，即相当于NOT IN查询 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 （2）利用谓词ANY或ALL 2．多值嵌套查询 （2）利用谓词ANY或ALL [例4-41]查询其他系中比信管系所有学生年龄小（改为出生日期小）的学生姓名和出生日期。 SELECT sname,sbirthday FROM student WHERE sdept<>N'信管系' AND sbirthday< ALL (SELECT sbirthday FROM student WHERE sdept= N'信管系')　 执行结果如图4-41所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 SELECT sname,sbirthday FROM student 2．多值嵌套查询 SELECT sname,sbirthday FROM student WHERE sdept<>N‘信管系’ AND sbirthday< (SELECT min(sbirthday) FROM student WHERE sdept= N'信管系')　 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 [例4-41-2]查询还没有选课的学生学号和姓名。 2．多值嵌套查询 [例4-41-2]查询还没有选课的学生学号和姓名。 SELECT sno,sname FROM student WHERE sno<>ALL (SELECT distinct sno FROM sc)　 执行结果如图4-41所示。 WHERE sno not in (SELECT distinct sno FROM sc) 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 3．使用谓词EXISTS的子查询 EXISTS谓词用于测试子查询的结果是否为空，即是否有查询结果。 如果子查询至少返回一行则产生逻辑真值“true”，不返回任何行则产生逻辑假值“false”。 NOT EXISTS与EXISTS的作用相反，若子查询结果为空，则返回真值，否则返回假值。 由EXISTS引出的子查询，其目标列表达式通常都用“*”表示，因为带EXISTS的子查询只用于检查是否有返回行，给出列名无实际意义。 其语法格式如下： [ NOT ] EXISTS ( subquery ) 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 3．使用谓词EXISTS的子查询（略） [例4-42]：查询选修了课程“c01”的学生姓名。 SELECT sname FROM Student WHERE EXISTS (SELECT * FROM sc WHERE sc.sno=student.sno AND cno='c01'); 执行结果如图4-42所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 3．使用谓词EXISTS的子查询（略） 这类查询与前面的嵌套查询有一个明显区别，即子查询的查询条件依赖于外层父查询的某个属性值（本例是student表的sno值），这类子查询称为“相关子查询”。 求解相关子查询不能一次将子查询求出来，然后求父查询。内层查询由于与外层查询有关，因此必须反复求值。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 3．使用谓词EXISTS的子查询（略） 一般来说，相关子查询的处理过程是： 首先取外层查询中的（student表）的第一个记录，根据它与内层查询相关的属性值（sno值）处理内层查询，若WHERE子句返回为真，则取此记录放入结果集；然后再取（student表）的下一个记录； 重复这一过程，直到外层（student表）全部检查完为止。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.9 嵌套查询 3．使用谓词EXISTS的子查询（略） [例4-43]：查询没有选课记录的学生的学号和姓名。 SELECT sno,sname FROM student WHERE NOT EXISTS(SELECT * FROM sc WHERE student.sno=sno) 执行结果如图4-43所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.10 组合查询 查询语句的结果集往往是一个包含了多行数据的集合。 集合之间可以进行并、交、差等运算。 在Microsoft SQL Server 2005系统中，两个查询语句之间也可以进行集合运算。 UNION运算符表示并集运算， EXCEPT运算符表示差运算， INTERSECT运算符表示交运算。 需要注意的是，在集合运算时，所有查询语句中列的数量和顺序必须相同，且数据类型必须兼容。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.10 组合查询 集合运算与连接运算是不同的： 在集合运算的结果集中，结果集中列的数量不发生变化，只是行的数量可能发生变化。 在连接运算的结果集中，结果集中的列数据经常会发生变化，并且行的数量也有可能发生变化。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.10 组合查询 1．并运算 UNION [ALL]用来将多个查询结果集合并起来，如果不加ALL，系统会自动去掉重复的记录；如果加上ALL，系统会保留重复的记录。 [例4-44]：查询所有的男生和信管系的学生的姓名、性别和所在系，使用UNION操作符。 SELECT sname,ssex,sdept FROM student WHERE ssex=N'男' UNION SELECT sname,ssex,sdept FROM student WHERE sdept=N'信管系' 执行结果如图4-44所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.10 组合查询 1．并运算 上面的语句相当于： SELECT sname,ssex,sdept FROM student WHERE ssex=N'男' or sdept=N'信管系' 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.10 组合查询 1．并运算 [例4-45]：查询所有的男生和信管系的学生的姓名、性别和所在系，使用UNION ALL操作符。 SELECT sname,ssex,sdept FROM student WHERE ssex=N'男' UNION ALL SELECT sname,ssex,sdept FROM student WHERE sdept=N'信管系' 执行结果如图4-45所示。 可以看出，本例使用了UNION ALL后，系统将不去掉重复的行，这与仅使用UNION略有区别。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.10 组合查询 2．差运算 EXCEPT运算符从左查询中返回右查询中没有找到的非重复值。 [例4-46]：查询所有的男性但不是信管系的学生的姓名、性别和所在系。 SELECT sname,ssex,sdept FROM student WHERE ssex=N'男' EXCEPT SELECT sname,ssex,sdept FROM student WHERE sdept=N'信管系' 执行结果如图4-46所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.10 组合查询 2．差运算 上面的语句相当于： SELECT sname,ssex,sdept FROM student WHERE ssex=N'男' and not sdept=N'信管系' 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.10 组合查询 3．交运算 INTERSECT运算符返回左右两个查询语句都包含的所有非重复值。 [例4-47]：查询所有的男性并且是信管系的学生的姓名、性别和所在系。 SELECT sname,ssex,sdept FROM student WHERE ssex=N'男' INTERSECT SELECT sname,ssex,sdept FROM student WHERE sdept=N'信管系' 执行结果如图4-47所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.1 数据查询 4.1.10 组合查询 2．交运算 上面的语句相当于： SELECT sname,ssex,sdept FROM student WHERE ssex=N'男' and sdept=N'信管系' 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2 视图及其应用 从表的角度来看，每一个表都存储了大量数据，通过表可以查看到表中的数据。但是，仅通过表操纵数据会带来一系列的性能、安全、效率等问题。 从业务数据角度来看，同一种业务数据有可能被分散在不同的表中，但是对这种业务数据的使用经常是同时使用的。连接查询、子查询、集合运算等就是解决这种问题的一种手段。但对于多个表来说，这些操作都是比较复杂的，能不能只通过一个数据库对象就可以同时看到这些分散存储的业务数据呢？ 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 从数据安全角度来看，由于工作性质和需求不同，不同的操作人员只需查看表中的部分数据，不能查看表中的所有数据。 例如，人事表中存储了员工的代码、姓名、出生日期、薪酬等信息。一般地，员工的代码和姓名是所有操作人员都可以查看的数据，但是薪酬等信息则只能由人事部门的管理人员查看，如何有效地解决这种不同操作人员查看表中不同数据的问题呢？ 从数据的应用角度来看，一个报表中的数据往往来自于多个不同的表中。在设计报表时，需要明确地指定数据的来源途径和方式。能不能采取有效手段，提高报表的设计效率呢？一次看到来自多个表的数据？ 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 解决上述问题的一种有效手段就是视图。 视图可以把表中分散存储的数据集成起来，让操作人员通过视图而不是通过表来访问数据，提高数据的安全性、报表的设计效率等。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.1 视图概述 1．视图的涵义 视图作为一种数据库对象，为用户提供了一种操作数据表中数据的方式。 用户通过视图使用数据表中感兴趣的部分或全部数据，而数据的物理存放位置仍然在表中。 视图是虚拟表，其内容由查询定义。同真实表一样，视图也包含一系列带有名称的列和行数据。列和行数据来自由定义视图的查询所引用的表或视图，并且在引用视图时动态生成。 在数据库中，存储的是视图的定义，而不是视图查询的数据。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.1 视图概述 1．视图的涵义 使用视图可以实现下列任一或所有功能： 将用户限定在数据表中的特定行上。例如，只允许学生看见学生成绩表内记录本人成绩的记录行。 将用户限定在特定列上。例如，对于那些不负责处理工资单的用户，只允许他们看见教师基本信息表中的姓名列、性别列、职称列和电话列，而不能看见任何包含工资信息的列。 将多个表中的列联接起来，使它们看起来像一个表。 提供聚合信息而非详细信息。例如，显示一个列的和，或者列的最大值和最小值。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.1 视图概述 1．视图的涵义 视图一经定义之后，就可以像基本表一样被查询、添加、修改和删除。 当对通过视图看到的数据进行添加、修改或删除时，相应的基本表中的数据也要发生变化； 同时，当基本表的数据发生变化时，这种变化也可以自动地反映到视图中。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.1 视图概述 2．使用视图的优点 使用视图可以集中、简化和定制用户的数据表显示，用户可以通过视图来访问数据，而不必直接访问数据表。 使用视图的具体优点如下： 数据保密。对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。 简化用户权限的管理。数据库所有者可以把视图的权限授予需要查询的用户，而不必将基表中某些列的查询权限授予用户。 集中用户使用的数据。视图创建了一种可以集中控制的环境，即视图的数据可以来自于不同的表，而用户可以操纵视图中显示的数据，就像操纵表中的数据那样。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.1 视图概述 2．使用视图的优点 简化查询操作，为复杂的查询建立一个视图，用户不必键入复杂的查询语句，只需针对此视图做简单的查询即可。 保证数据的逻辑独立性。视图把数据库设计的复杂性与用户的使用方式屏蔽开了。这样就为数据库开发人员提供了一种改变数据库的设计而不影响用户使用的能力。 对于视图的操作，例如查询，只依赖于视图的定义。当构成视图的数据表要修改时，只需修改视图定义中的子查询部分，而基于视图的查询不用改变。 另外，在数据库设计时，使用的名称经常是难以理解的，而在视图中可以把这些列的名称替换为容易理解的友好名称，从而为用户的使用提供了很大的便利。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.2 创建视图 1．使用对象资源管理器创建视图（略） SQL Server 2005中提供了两种创建视图的方法：使用对象资源管理器和使用Transact-SQL语句，下面分别加以介绍。 1．使用对象资源管理器创建视图（略） 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.2 创建视图 2．使用Transact-SQL创建视图 创建视图也可使用CREATE VIEW语句来实现，CREATE VIEW语句的语法格式如下： CRAETE VIEW [ database_name. ] view_name [ ( column_name [ ,…n ] ) ] AS select_statement 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.2 创建视图 2．使用Transact-SQL创建视图 各参数说明如下： database_name：指定要创建的视图所属的数据库。 view_name：指定要创建的视图名称。 column_name：指定要创建的视图中的列名。 select_statement：定义视图的SELECT语句，视图数据来源于select_statement的结果。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.2 创建视图 2．使用Transact-SQL创建视图 组成视图的列名全部省略或者全部指定。如果省略了视图的列名，则列名由SELECT-statement子句中结果集的列名组成。但在下列3种情况下必须明确指定组成视图的所有列名： （1）当视图的列名为表达式或聚合函数的计算结果，而不是单纯的列名时； （2）视图由多个表连接得到，在不同的表中存在同名列； （3）需要在视图中为某个列启用更合适的名字。 注意，在视图的定义中，SELECT子句中不能包含下面一些内容： COMPUTE或COMPUTE BY子句。 ORDER BY子句，除非SELECT语句中的选择列表中有TOP子句。 INTO关键字。 OPTION子句。 引用临时表或表变量。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.2 创建视图 2．使用Transact-SQL创建视图 [例4-49]：创建视图VW_male_student，要求包含所有男生的学号、姓名、所在系、是否党员、出生日期。 USE student GO CREATE VIEW VW_male_student (学号,姓名,所在系,是否党员,出生日期) AS SELECT sno,sname,sdept,sifdy,sbirthday FROM student WHERE ssex=N'男' 此例将SELECT查询结果作为视图中的数据。完成以上操作后，在“student”数据库中的视图选项下，右击VW_male_student，选择“打开视图”，即可看到由此视图定义所返回的数据。如图4-51。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 1．使用对象资源管理器查看与修改视图定义（略） 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 2．使用系统存储过程查看视图的定义 如果视图定义没有加密，则可以用存储过程获取该视图定义的有关信息，以了解数据从源表中的获取方式。 （1）sp_helptext 系统存储过程sp_helptext用于检索视图、触发器、存储过程的定义文本，其语法格式如下： sp_helptext [@ objname=] ‘name’ 参数说明如下： [@ objname=] ‘name’：指定当前数据库中的对象的名称，如视图的名称。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 2．使用系统存储过程查看视图的定义 [例4-50]：查看视图VW_XinGuan_student的信息。 USE student GO EXEC sp_helptext VW_XinGuan_student 命令执行完后，可以在结果区看到如图4-52所示的内容。 说明：name可以加也可以不加单引号。sp_help相同。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 2．使用系统存储过程查看视图的定义 （2）sp_help sp_help [[@objname=] name] 参数说明如下： [@objname=] name：是系统对象表中的任意对象的名称，或者是在系统类型表中任何用户定义数据类型的名称。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 2．使用系统存储过程查看视图的定义 （2）sp_help [例4-51]：执行系统存储过程sp_help，以获取有关视图VW_XinGuan_student的详细信息。 EXEC sp_help VW_XinGuan_student 在SQL编辑器中运行该命令，执行结果如图4_53所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 3．使用Transact_SQL语句修改视图定义 如果基本表发生变化，或者要通过视图查询更多的信息，都需要修改视图的定义。修改视图的定义使用ALTER VIEW语句，其语法格式如下： ALTER VIEW [database_name.] view_name [(column_name [,…n])] AS select_statement 其中各参数的含义与CREATE VIEW语句中的参数含义相同。 如果删除一个视图，然后又重新创建该视图，也可以达到修改视图的目的，但是这样则必须重新指定视图的权限。当使用ALTER VIEW语句修改视图时，视图原有的权限不会发生变化。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 3．使用Transact_SQL语句修改视图定义 [例4-52]：将VW_XinGuan_student视图修改为：信管系和会计系的学生的学号、姓名、性别、所在系、出生日期。 ALTER VIEW VW_XinGuan_student AS SELECT sno AS 学号, sname AS 姓名, ssex AS 性别, sdept AS 所在系, sbirthday AS 出生日期 FROM student WHERE sdept = N'信管系' OR sdept = N'会计系' 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 4．视图的重命名（略） 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 5．视图的删除 （1）通过对象资源管理器删除视图（略） 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 5．视图的删除 （2）使用Transact_SQL语句删除视图 可以使用DROP VIEW语句删除视图。DROP VIEW语句的语法格式如下： DROP VIEW view_name [,…n] 各参数说明如下： view_name：指定要删除的视图的名称。 n：表示可以一次删除多个视图。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.3 维护视图 5．视图的删除 （2）使用Transact_SQL语句删除视图 [例4-54]：删除视图VW_XGKJ_student。 DROP VIEW VW_XGKJ_student 视图删除后，只会删除该视图在数据库中的定义，而与视图有关的数据表中的数据不会受任何影响，由此视图导出的其他视图的定义不会删除，但已无任何意义，用户应该把这些视图删除。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.4 视图数据操作 视图定义后，就可以使用它查询基本表中的数据，也可以通过它来更新基本表中的数据，即可以添加、修改、删除数据。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.4 视图数据操作 1．查询视图数据 视图定义后，对视图的查询操作如同对数据表的查询操作一样。 [例4-55]：在视图VW_male_student中查询信管系学生的所有信息。 SELECT * FROM VW_male_student WHERE 所在系=N'信管系' 执行结果如图4-54所示。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.4 视图数据操作 2．更新视图数据 更新视图数据可以通过“对象资源管理器”或Transact_SQL语句。 当更新视图中的数据时，实际上是对数据表的数据进行更新。事实上，当从视图中插入或者删除数据时，情况也是这样。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.4 视图数据操作 2．更新视图数据 某些视图是不能更新数据的，在修改视图数据时，要注意下列条件： 不能同时影响两个或两个以上的基表。可以修改由两个或两个以上的基表得到的视图，但是每一次修改的数据只允许影响一个基表。 某些列不能修改。这些不能修改的列包括通过计算得到值的列、有内置函数的列或有聚合函数的列等。 可修改列数据的视图，必须是创建视图的SELECT语句中没有聚合函数，且没有TOP、GROUP BY、UNION子句及DISTINCT关键字。 对于基本表中需要更新而又不允许为空值的所有列，它们的值在INSERT语句或DEFAULT定义中指定。这将确保基本表中所有需要值的列都可以获取值。 如果在视图定义中使用了WITH CHECK OPTION子句，则所有在视图上执行的数据更新语句都必须符合定义视图的SELECT语句中所设定的条件。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.4 视图数据操作 2．更新视图数据 （1）插入数据 使用INSERT语句通过视图向基本表插入数据。 [例4-56]：向视图VW_XinGuan_student中添加一行记录(’2008008’，’袁峰’，’男’，’信管系’，’1992-8-17’)。 INSERT INTO VW_XinGuan_student VALUES ('2008008',N'袁峰',N'男',N'信管系','1992-8-17') 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.4 视图数据操作 2．更新视图数据 （2）修改数据 使用UPDATE语句通过视图修改基本表数据。 [例4-57]：将VW_XinGuan_student视图中的学号为“2008008”的学生的所在系改为会计系。 UPDATE VW_XinGuan_student SET 所在系=N'会计系' WHERE 学号='2008008' 命令执行后，在student表中可以看到此学生的所在系被改成了会计系。 说明：VW_XinGuan_student创建时，没有使用WITH CHECK OPTION子句，所以允许修改。若使用了WITH CHECK OPTION子句，则此命令执行失败。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

4.2视图及其应用 4.2.4 视图数据操作 2．更新视图数据 （3）删除数据 使用DELETE语句通过视图删除基本表数据。但要注意，对于依赖多个基本表的视图，不能使用DELETE语句。对于只依赖一个表的视图可以删除其中的数据，如： [例4-58]：将VW_XinGuan_student视图中的学号为“2008008”的学生信息删除。 DELETE FROM VW_XinGuan_student WHERE 学号='2008008' 命令执行后，在student表中可以看到此学生的信息被删除了。 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社

本 章 结 束 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》 山东大学出版社 2017/3/5 2017/3/5 本 章 结 束 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 高等教育“十一五”国家级规划教材 《 数据库与程序设计 》　 山东大学出版社 157