SQL2005数据库应用与开发_05Transact-SQL语句的高级应用

1、清华大学出版社. SQL Server 2005数据库应用与开发 第第06章章 Transact-SQL语句的高级应用语句的高级应用 清华大学出版社. SQL Server 2005数据库应用与开发 第第06章章 Transact-SQL语句的高级应用语句的高级应用 清华大学出版社. SQL Server 2005数据库应用与开发 6.1.1 连接概述连接概述n连接可以根据各个表之间的逻辑关系来利连接可以根据各个表之间的逻辑关系来利用一个表中的数据选择另外的表中的行。用一个表中的数据选择另外的表中的行。n连接条件可在连接条件可在 FROM 或或 WHERE 子句中子句中指定。连接条件与指定。连

2、接条件与WHERE和和HAVING搜搜索条件组合，用于控制索条件组合，用于控制FROM子句引用的子句引用的基表中所选定的行。基表中所选定的行。 清华大学出版社. SQL Server 2005数据库应用与开发 6.1.1 连接概述连接概述SQL Server处理连接时，查询引擎从多种可能处理连接时，查询引擎从多种可能的方法中选择最高效的方法处理连接。尽的方法中选择最高效的方法处理连接。尽管不同连接的物理执行采用多种不同的优管不同连接的物理执行采用多种不同的优化，但是逻辑序列都应用下列子句实现：化，但是逻辑序列都应用下列子句实现： nFROM 子句中的连接条件。子句中的连接条件。nWHERE 子

3、句中的连接条件和搜索条件。子句中的连接条件和搜索条件。nHAVING 子句中的搜索条件。子句中的搜索条件。 清华大学出版社. SQL Server 2005数据库应用与开发 6.1.2 内连接内连接内连接内连接(INNER JOIN) 通过比较数据源表间共通过比较数据源表间共享列的值，从多个源表检索符合条件的行享列的值，从多个源表检索符合条件的行的操作。可以使用等号运算符的连接，也的操作。可以使用等号运算符的连接，也可以连接两个不相等的列中的值可以连接两个不相等的列中的值 例例6.1 6.1 查询选修课程号为查询选修课程号为c05109c05109的学生的学号、的学生的学号、姓名和期末成绩。姓

4、名和期末成绩。分析：分析：本例中要求所输出的列分别在本例中要求所输出的列分别在student student 表表和和scorescore表中，可以通过表中，可以通过studentnostudentno列、使用内连接列、使用内连接的方式连接两个表，找出选修课程号为的方式连接两个表，找出选修课程号为c05109c05109的行。的行。程序中两个表存在相同的列，引用时需要标明该列程序中两个表存在相同的列，引用时需要标明该列所属的源表。所属的源表。清华大学出版社. SQL Server 2005数据库应用与开发 程序代码如下：程序代码如下：SELECT student.studentno,sname

5、,final SELECT student.studentno,sname,final FROM student INNER JOIN score FROM student INNER JOIN score ON student.studentno=score.studentno ON student.studentno=score.studentnoWHERE score.courseno = c05109WHERE score.courseno = c05109程序执行结果如下：程序执行结果如下：studentno sname finalstudentno sname final- - -

6、 - -0822111208 0822111208 韩吟秋韩吟秋 91.0091.000824113307 0824113307 崔岩坚崔岩坚 79.0079.00 0935222201 0935222201 夏文斐夏文斐 92.0092.000937221508 0937221508 平靖平靖 91.0091.00(7 (7 行受影响行受影响) )清华大学出版社. SQL Server 2005数据库应用与开发 6.1.2 内连接内连接例例6.26.2 查询选修课程号为查询选修课程号为c05103c05103且平时成绩高于且平时成绩高于8080分的学生的学号、姓名、平时成绩和期末成绩。分的学

7、生的学号、姓名、平时成绩和期末成绩。分析：本例通过分析：本例通过studentnostudentno列连接两个表，找出选列连接两个表，找出选修课程号为修课程号为c05103c05103的行。同时要求输出行中的平时的行。同时要求输出行中的平时成绩高成绩高8080分，则可以使用不是用等号的比较运算符分，则可以使用不是用等号的比较运算符实现。关键词实现。关键词INNERINNER也可以省略。也可以省略。程序代码如下：程序代码如下：SELECT student.studentno,sname,usually,final SELECT student.studentno,sname,usually,fi

8、nal FROM student JOIN score FROM student JOIN score ON student.studentno= ON student.studentno= score.studentno and usually80 score.studentno and usually80 WHERE score.courseno = c05103WHERE score.courseno = c05103程序执行结果如下：程序执行结果如下：studentno sname usually finalstudentno sname usually final- - - - -0

9、823210007 0823210007 宿致远宿致远 82.00 69.0082.00 69.000824113307 0824113307 崔岩坚崔岩坚 85.00 77.0085.00 77.000922210009 0922210009 许海冰许海冰 87.00 82.0087.00 82.000922221324 0922221324 何影何影 88.00 62.0088.00 62.00(4 (4 行受影响行受影响) )清华大学出版社. SQL Server 2005数据库应用与开发 6.1.3 外连接外连接外部连接（外部连接（outer join）包括满足搜索条件的连）包括满足搜

10、索条件的连接表中的所有行，甚至包括在其他连接表接表中的所有行，甚至包括在其他连接表中没有匹配行的一个表中的行。中没有匹配行的一个表中的行。 （1）左外连接（）左外连接（left outer join）。包括）。包括 JOIN子句中左侧表中的所有行。右表中的行与子句中左侧表中的所有行。右表中的行与左表中的行不匹配时，将为来自右表的所左表中的行不匹配时，将为来自右表的所有结果集列赋以有结果集列赋以 NULL 值。值。清华大学出版社. SQL Server 2005数据库应用与开发 6.1.3 外连接外连接例例6.36.3利用左外连接方式查询利用左外连接方式查询0808级学生的学号、姓级学生的学号、

11、姓名、平时成绩和期末成绩。名、平时成绩和期末成绩。分析：左外连接方式将会对右表中的行与左表中的分析：左外连接方式将会对右表中的行与左表中的行不匹配时，将右表的所有结果集列赋以行不匹配时，将右表的所有结果集列赋以NULL NULL 值。值。程序代码如下：程序代码如下：SELECT student.studentno,sname,usually,final SELECT student.studentno,sname,usually,final FROM student LEFT JOIN score FROM student LEFT JOIN score ON student.studentn

12、o=score.studentno ON student.studentno=score.studentno WHERE substring(student.studentno,1,2)= 08WHERE substring(student.studentno,1,2)= 08程序执行结果如下：程序执行结果如下：studentno sname usually finalstudentno sname usually final- - - - - - -0822111208 0822111208 韩吟秋韩吟秋 85.00 91.0085.00 91.000822111208 0822111208

13、 韩吟秋韩吟秋 89.00 95.0089.00 95.000824113307 0824113307 崔岩坚崔岩坚 66.00 82.0066.00 82.000828261367 0828261367 赵毓赵毓 NULL NULLNULL NULL(10 (10 行受影响行受影响) )清华大学出版社. SQL Server 2005数据库应用与开发 6.1.3 外连接外连接（2）右外连接）右外连接(right outer join)。n外部连接的一种，其中包含外部连接的一种，其中包含 JOIN 子句中子句中最右侧表的所有行。最右侧表的所有行。n如果右侧表中的行与左侧表中的行不匹配，如果右侧

14、表中的行与左侧表中的行不匹配，则将为结果集中来自左侧表的所有列分配则将为结果集中来自左侧表的所有列分配 NULL 值。值。清华大学出版社. SQL Server 2005数据库应用与开发 6.1.3 外连接外连接例例6.4 6.4 利用右外连接方式查询教师的排课情况。利用右外连接方式查询教师的排课情况。分析：右外连接方式将会对左表中的行与右表中分析：右外连接方式将会对左表中的行与右表中的行不匹配时，将左表的所有结果集列赋以的行不匹配时，将左表的所有结果集列赋以NULL NULL 值。值。程序代码如下：程序代码如下：SELECT courseno,tname,teacher.teacherno,

15、majorSELECT courseno,tname,teacher.teacherno,majorFROMFROM teach_class RIGHT JOIN teacher teach_class RIGHT JOIN teacher ON teach_class.teacherno=teacher.teacherno ON teach_class.teacherno=teacher.teacherno程序执行结果如下：程序执行结果如下：courseno tname major teachernocourseno tname major teacherno- - - - - - -c05

16、109 c05109 韩晋升韩晋升 软件工程软件工程 t05001t05001 c06172 c06172 姚思远姚思远 铸造工艺铸造工艺 t06023t06023NULL NULL 马爱芬马爱芬 经济管理经济管理 t07019t07019c08123 c08123 田有余田有余 金融管理金融管理 t08017t08017c08106 c08106 韩慧娟韩慧娟 货币学货币学 t08019t08019(10 (10 行受影响行受影响) )清华大学出版社. SQL Server 2005数据库应用与开发 6.1.3 外连接外连接（3）完全外连接。）完全外连接。n 若要通过在连接的结果中包括不匹配

17、的行若要通过在连接的结果中包括不匹配的行来保留不匹配信息，请使用完全外部连接。来保留不匹配信息，请使用完全外部连接。n SQL Server 提供了完全外部连接运算符提供了完全外部连接运算符 FULL OUTER JOIN，它将包括两个表中，它将包括两个表中的所有行，不论另一个表中是否有匹配的的所有行，不论另一个表中是否有匹配的值。值。清华大学出版社. SQL Server 2005数据库应用与开发 6.1.3 外连接外连接例例6.5 6.5 利用完全外连接方式查询教师的排课情况。利用完全外连接方式查询教师的排课情况。分析：完全外部连接是右外连接与左外连接的并分析：完全外部连接是右外连接与左外

18、连接的并集。无论是左表中的行还是右表中的行不匹配时，集。无论是左表中的行还是右表中的行不匹配时，将所有结果集中没有匹配值的列赋以将所有结果集中没有匹配值的列赋以NULLNULL值。值。程序代码如下：程序代码如下：SELECT courseno,tname,major,teacher.teachernoSELECT courseno,tname,major,teacher.teachernoFROM teach_class FULL JOIN teacherFROM teach_class FULL JOIN teacher ON teach_class.teacherno=teacher.te

19、acherno ON teach_class.teacherno=teacher.teacherno 程序执行结果如下：程序执行结果如下：courseno tname major teachernocourseno tname major teacherno- - - - - - -c05109 c05109 韩晋升韩晋升 软件工程软件工程 t05001t05001c05127 c05127 韩晋升韩晋升 软件工程软件工程 t05001t05001c08106 c08106 韩慧娟韩慧娟 货币学货币学 t08019t08019c05109 NULL NULL NULLc05109 NULL N

20、ULL NULLNULL NULL 马爱芬马爱芬 经济管理经济管理 t07019t07019(11 (11 行受影响行受影响) )清华大学出版社. SQL Server 2005数据库应用与开发 6.1.4 交叉连接（笛卡尔连接）交叉连接（笛卡尔连接）交叉连接交叉连接(Cross Join )是在没有是在没有WHERE子句子句的情况下的情况下,产生的表的笛卡儿积。两个表作产生的表的笛卡儿积。两个表作交叉连接时，结果集大小为二者行数之积。交叉连接时，结果集大小为二者行数之积。该种方式在实际过程中用的很少。该种方式在实际过程中用的很少。清华大学出版社. SQL Server 2005数据库应用与开

21、发 6.1.4 交叉连接交叉连接例例6.6 6.6 显示显示student student 表和表和scorescore表的笛卡儿积。表的笛卡儿积。分析：分析：student student 表表1010数据，数据，scorescore表有表有2424行数据，行数据，其结果集应为其结果集应为240240行数据。行数据。程序代码如下：程序代码如下：SELECT student.studentno,sname,score.SELECT student.studentno,sname,score.* * FROM student CROSS JOIN scoreFROM student CROSS

22、JOIN score程序执行结果如下：程序执行结果如下：studentno sname studentno courseno usually studentno sname studentno courseno usually finalfinal- - - - - - - - - -0822111208 0822111208 韩吟秋韩吟秋 0822111208 c05109 85.00 91.000822111208 c05109 85.00 91.000822111208 0822111208 韩吟秋韩吟秋 0822111208 c06108 89.00 95.000822111208 c

23、06108 89.00 95.000937221508 0937221508 平靖平靖 0937221508 c08106 78.00 95.000937221508 c08106 78.00 95.000937221508 0937221508 平靖平靖 0937221508 c08123 78.00 89.000937221508 c08123 78.00 89.000937221508 0937221508 平靖平靖 0937221508 c08171 88.00 98.000937221508 c08171 88.00 98.00(240 (240 行受影响行受影响) )清华大学出版社

24、. SQL Server 2005数据库应用与开发 6.1.5 连接多个表连接多个表 n使用使用SELECT语句进行连接的表数目没有上限。语句进行连接的表数目没有上限。但在一条但在一条SELECT语句中连接的表多于语句中连接的表多于10个，那个，那么数据库就很可能达不到最优化设计，么数据库就很可能达不到最优化设计，SQL Server 2005引擎的执行计划会变得非常繁琐引擎的执行计划会变得非常繁琐 。n需要注意需要注意：对于：对于3个以上关系表的连接查询，一个以上关系表的连接查询，一般遵循下列规则：连接般遵循下列规则：连接n个表至少需要个表至少需要n-1个连接个连接条件，以避免笛卡儿积的出现

25、。为了缩小结果集，条件，以避免笛卡儿积的出现。为了缩小结果集，采用多于采用多于n-1个连接条件或使用其他条件都是允个连接条件或使用其他条件都是允许的。许的。清华大学出版社. SQL Server 2005数据库应用与开发 6.1.5 连接多个表连接多个表例例6.76.7 查询查询0808级学生的学号、姓名、课程名、期级学生的学号、姓名、课程名、期末成绩及学分。末成绩及学分。分析：本例要求输出的各项分别存在于分析：本例要求输出的各项分别存在于studentstudent、 coursecourse和和 scorescore等等3 3个表中，因此至少需要创建个表中，因此至少需要创建2 2个个连接条

26、件。连接条件。程序代码如下：程序代码如下：SELECT student.studentno,SELECT student.studentno, sname,cname,final,credit sname,cname,final,creditFROM score JOIN student ON FROM score JOIN student ON student.studentno=score.studentno student.studentno=score.studentno JOIN course ON JOIN course ON score.courseno=course.cours

27、eno score.courseno=course.coursenowhere where substring(student.studentno,1,2)=08substring(student.studentno,1,2)=08 程序执行结果如下：程序执行结果如下：studentno sname cname final creditstudentno sname cname final credit- - - - - - -0822111208 0822111208 韩吟秋韩吟秋 C C语言语言 91.00 4.591.00 4.50822111208 0822111208 韩吟秋韩吟秋

28、机械制图机械制图 95.00 3.595.00 3.50824113307 0824113307 崔岩坚崔岩坚 数据结构数据结构 87.00 4.087.00 4.00824113307 0824113307 崔岩坚崔岩坚 机械制图机械制图 82.00 3.582.00 3.5(9 (9 行受影响行受影响) )清华大学出版社. SQL Server 2005数据库应用与开发 6.1.5 连接多个表连接多个表例例6.86.8 查询计算机学院的老师的教师号、姓名、查询计算机学院的老师的教师号、姓名、上课班级号、课程名和学分。上课班级号、课程名和学分。分析：本例要求输出的各项分别存在于分析：本例要求

29、输出的各项分别存在于teacherteacher、 classclass和和coursecourse等等3 3个表中，因为各个表要通过个表中，因为各个表要通过teach_classteach_class纽带表进行连接，因此至少需要创建纽带表进行连接，因此至少需要创建3 3个连接条件。个连接条件。 程序代码如下：程序代码如下：SELECT SELECT teacher.teacherno,tname,class.classno,cname,creditteacher.teacherno,tname,class.classno,cname,creditFROM teach_class JOIN t

30、eacher FROM teach_class JOIN teacher ON teach_class.teacherno=teacher.teacherno ON teach_class.teacherno=teacher.teacherno JOIN class ON JOIN class ON teach_class.classno=class.classnoteach_class.classno=class.classno JOIN course ON JOIN course ON teach_class.courseno=course.coursenoteach_class.cour

31、seno=course.coursenowhere teacher.department=where teacher.department=计算机学院计算机学院 程序执行结果如下：程序执行结果如下：teacherno tname classno cname creditteacherno tname classno cname credit- - - - - - - - -t05001 t05001 韩晋升韩晋升 090502 090502 数据结构数据结构 4.04.0t05003 t05003 刘元朝刘元朝 090501 090501 数据结构数据结构 4.04.0t05011 t0501

32、1 海封海封 090502 090502 软件工程软件工程 3.03.0t05017 t05017 卢明欣卢明欣 090501 090501 数据结构数据结构 4.04.0(4 (4 行受影响行受影响) )清华大学出版社. SQL Server 2005数据库应用与开发 6.1.6 合并多个结果集合并多个结果集 UNION操作符可以将多个操作符可以将多个SELECT语句的返回结语句的返回结果组合到一个结果集中。该结果集包含联合查询果组合到一个结果集中。该结果集包含联合查询中的所有查询的全部行。下面列出了使用中的所有查询的全部行。下面列出了使用 UNION 合并两个查询结果集的基本规则：合并两个

33、查询结果集的基本规则： n所有查询中的列数和列的顺序必须相同。所有查询中的列数和列的顺序必须相同。n数据类型必须兼容。数据类型必须兼容。 UNION操作符基本语法格式如下：操作符基本语法格式如下： SELECT_statement UNION all SELECT_statement清华大学出版社. SQL Server 2005数据库应用与开发 6.1.5 连接多个表连接多个表例例6.9 6.9 合并结果集示例。合并结果集示例。分析：虽然分析：虽然2 2个表的结构不同，但需要合并的两个结果集结构个表的结构不同，但需要合并的两个结果集结构和列的数据类型兼容。和列的数据类型兼容。程序代码如下：程

34、序代码如下：CREATE TABLE t1 (a int, b nchar(4), c nchar(4)CREATE TABLE t1 (a int, b nchar(4), c nchar(4)INSERT INTO t1 VALUES (1, aaa, jkl)INSERT INTO t1 VALUES (1, aaa, jkl)INSERT INTO t1 VALUES (2, bbb, mno)INSERT INTO t1 VALUES (2, bbb, mno)INSERT INTO t1 VALUES (3, ccc, pqr)INSERT INTO t1 VALUES (3, c

35、cc, pqr)CREATE TABLE t2 (a nchar(4), b float)CREATE TABLE t2 (a nchar(4), b float)INSERT INTO t2 VALUES(kkk, 1.000)INSERT INTO t2 VALUES(kkk, 1.000)INSERT INTO t2 VALUES(mmm, 3.000)INSERT INTO t2 VALUES(mmm, 3.000)SELECT a, b FROM t1 UNION SELECT b, a FROM t2SELECT a, b FROM t1 UNION SELECT b, a FRO

36、M t2程序执行结果如下：程序执行结果如下：a ba b- - -1 aaa 1 aaa 1 kkk 1 kkk 2 bbb 2 bbb 3 ccc 3 ccc 3 mmm 3 mmm (5 (5 行受影响行受影响) )清华大学出版社. SQL Server 2005数据库应用与开发 6.2.1 子查询介绍子查询介绍n子查询就是一个嵌套在子查询就是一个嵌套在SELECT、INSERT、UPDATE或或DELETE语句或其他子查询中的查询语句或其他子查询中的查询子查询可以把一个复杂的查询分解成一系列的逻子查询可以把一个复杂的查询分解成一系列的逻辑步骤，利用单个语句的组合解决复杂的查询问辑步骤，利

37、用单个语句的组合解决复杂的查询问题。题。n可以使用连接替代子查询，也可以使用子查询替可以使用连接替代子查询，也可以使用子查询替代表达式。代表达式。nSQL Server 2005对嵌套查询的处理过程是从内层对嵌套查询的处理过程是从内层向外层处理，即先处理最内层的子查询，然后把向外层处理，即先处理最内层的子查询，然后把查询的结果用于其外查询的查询条件，再层层向查询的结果用于其外查询的查询条件，再层层向外求解，最后得出查询结果。外求解，最后得出查询结果。清华大学出版社. SQL Server 2005数据库应用与开发 6.2.1 子查询介绍子查询介绍使用子查询时应该注意如下的事项：使用子查询时应该

38、注意如下的事项：n子查询需要用括号括起来。子查询需要用括号括起来。n当需要返回一个值或一个值列表时，可以利用子当需要返回一个值或一个值列表时，可以利用子查询代替一个表达式。也可以利用子查询返回含查询代替一个表达式。也可以利用子查询返回含有多个列的结果集替代表或连接操作相同的功能。有多个列的结果集替代表或连接操作相同的功能。n子查询不能够检索数据类型为子查询不能够检索数据类型为varchar(max)、nvarchar(max) 和和 varbinary(max)的列。的列。n子查询中可以再包含子查询，嵌套层数可以达到子查询中可以再包含子查询，嵌套层数可以达到16层。层。清华大学出版社. SQL

39、 Server 2005数据库应用与开发 6.2.2利用子查询做表达式利用子查询做表达式n在在Transact-SQL语句中，可以把子查询的结果当语句中，可以把子查询的结果当成一个普通的表达式来看待，用在其外查询的选成一个普通的表达式来看待，用在其外查询的选择条件中。此时子查询必须返回一个值或单个列择条件中。此时子查询必须返回一个值或单个列值列表，此时的子查询可以替换值列表，此时的子查询可以替换WHERE子句中子句中包含包含IN关键字的表达式。关键字的表达式。例例6.10 查询学号为的学生的入学成绩、所有学生查询学号为的学生的入学成绩、所有学生的平均入学成绩及该学生成绩与所有学生的平均的平均入

40、学成绩及该学生成绩与所有学生的平均入学成绩的差。入学成绩的差。分析：利用子查询求学生的平均入学成绩，作为分析：利用子查询求学生的平均入学成绩，作为SELECT语句的输出项表达式。语句的输出项表达式。清华大学出版社. SQL Server 2005数据库应用与开发 6.2.2利用子查询做表达式利用子查询做表达式程序代码如下：程序代码如下：SELECT studentno,sname,point ,(SELECT AVG(point) FROM student )AS 平均成绩平均成绩 ,point -(SELECT AVG(point) FROM student ) AS 分数差值分数差值FRO

41、M student WHERE studentno=0828261367程序运行结果如下：studentno sname point 平均成绩 分数差值- - - - -0828261367 赵毓 887 788 99(1 行受影响)清华大学出版社. SQL Server 2005数据库应用与开发 6.2.2利用子查询做表达式利用子查询做表达式例例6.11 获取期末成绩中含有高于获取期末成绩中含有高于93分的学生的学分的学生的学号、姓名、电话和号、姓名、电话和Email。分析：利用操作符分析：利用操作符IN可以允许指定一个表达式可以允许指定一个表达式（或常量）集合，可以利用（或常量）集合，可以

42、利用SELECT语句的子查语句的子查询输出表达式（或常量）集合。询输出表达式（或常量）集合。程序代码如下：程序代码如下：SELECT studentno,sname,phone,EmailFROM student WHERE studentno IN ( SELECT studentno FROM score WHERE final93 )程序运行结果如下：程序运行结果如下：studentno sname phone Email- - - -0822111208 韩吟秋韩吟秋0925111109 敬秉辰敬秉辰0937221508 平靖平靖 1

43、2367823453 (3 行受影响行受影响)清华大学出版社. SQL Server 2005数据库应用与开发 6.2.2利用子查询做表达式利用子查询做表达式例例6.12查询选修课程的多于查询选修课程的多于2门、且期末成绩均在门、且期末成绩均在85分以分以上的学生的学号、姓名、电话和上的学生的学号、姓名、电话和Email。分析：在分析：在score表中通过表中通过studentno列分组，同时利用列分组，同时利用WHERE限定限定85分以上、利用分以上、利用HAVING句检测选修课程的句检测选修课程的多于多于2门的学生，符合条件的输出相关选项。门的学生，符合条件的输出相关选项。程序代码如下：程

44、序代码如下：SELECT studentno,sname,phone,EmailFROM studentWHERE studentno IN (SELECT studentno FROM score WHERE final85 GROUP BY studentno HAVING count(*)2)程序运行结果如下：程序运行结果如下：studentno sname phone Email- - - -0925111109 敬秉辰敬秉辰0937221508 平靖平靖 12367823453 (2 行受影响行受影响)清华大学出版社. SQL Server 2005数据库应

45、用与开发 6.2.3利用子查询关联数据利用子查询关联数据n子查询可以作为动态表达式，该表达式可以随着子查询可以作为动态表达式，该表达式可以随着外层查询的每一行的变化而变化。外层查询的每一行的变化而变化。 n创建关联子查询时，外部查询有多少行，子查询创建关联子查询时，外部查询有多少行，子查询就执行多少次。就执行多少次。 例例6.13查询期末成绩比该选修课程平均期末成绩查询期末成绩比该选修课程平均期末成绩低的学生的学号、课程号和期末成绩。低的学生的学号、课程号和期末成绩。分析：在本例中，对分析：在本例中，对score表采用别名形式，一表采用别名形式，一个表就相当于个表就相当于2个表。子查询执行时使

46、用的个表。子查询执行时使用的a.courseno相当于一个常量。在别名为相当于一个常量。在别名为b的表中根的表中根据分组计算平均分。然后与外层查询的值进行比据分组计算平均分。然后与外层查询的值进行比较。该过程很费时间。较。该过程很费时间。 清华大学出版社. SQL Server 2005数据库应用与开发 6.2.3利用子查询关联数据利用子查询关联数据程序代码如下：程序代码如下：SELECT studentno,courseno,finalFROM score as aWHERE final 85) AS TT WHERE TT.final*0.8+TT.usually*0.290程序运行结果如

47、下：程序运行结果如下：studentno courseno 总评成绩总评成绩- - -0822111208 c06108 93.8000937221508 c08106 91.6000937221508 c08171 96.000(6 行受影响行受影响)清华大学出版社. SQL Server 2005数据库应用与开发 6.2.5利用子查询修改表数据利用子查询修改表数据 n利用子查询修改表数据就是利用一个嵌套在利用子查询修改表数据就是利用一个嵌套在INSERT、UPDATE或或DELETE语句的子查询成语句的子查询成批的添加、更新和删除表中的数据。批的添加、更新和删除表中的数据。nINSERT

48、语句中的语句中的 SELECT 子查询可用于将一个子查询可用于将一个或多个其他的表或视图的值添加到表中。使用或多个其他的表或视图的值添加到表中。使用 SELECT 子查询可同时插入多行。子查询可同时插入多行。例例6.15创建一个表创建一个表sc，将，将score表中学生的相关数表中学生的相关数据添加到据添加到sc表中，并要求计算总评成绩。表中，并要求计算总评成绩。分析：子查询的选择列表必须与分析：子查询的选择列表必须与 INSERT 语句语句列的列表匹配。如果列的列表匹配。如果INSERT 语句没有指定列的语句没有指定列的列表，则选择列表必须与正向其插入的表或视图列表，则选择列表必须与正向其插

49、入的表或视图的列匹配且顺序一致。的列匹配且顺序一致。 清华大学出版社. SQL Server 2005数据库应用与开发 6.2.3利用子查询关联数据利用子查询关联数据程序代码如下：程序代码如下：CREATE TABLE sc(studentno nchar(10) not null, courseno nchar(6) not null, total numeric (6,2) not null)GOINSERT INTO sc(studentno, courseno,total) SELECT studentno, courseno,final*0.8+usually*0.2 FROM sc

50、ore WHERE substring(studentno,1,2)=08GOSELECT * FROM sc程序运行结果如下：程序运行结果如下：studentno courseno total- - -0822111208 c05109 89.800824113307 c05127 88.200824113307 c06108 78.80(9 行受影响行受影响)清华大学出版社. SQL Server 2005数据库应用与开发 6.2.5利用子查询修改表数据利用子查询修改表数据 nUPDATE语句中的语句中的SELECT子查询可用于将一个子查询可用于将一个或多个其他的表或视图的值进行更新。使用

51、或多个其他的表或视图的值进行更新。使用 SELECT子查询可同时更新多行数据。子查询可同时更新多行数据。n在在DELETE语句中利用子查询可以删除符合条件语句中利用子查询可以删除符合条件的数据行。实际上是通过将子查询的结果作为删的数据行。实际上是通过将子查询的结果作为删除条件表达式中的一部分。除条件表达式中的一部分。 例例6.16 将将sc表中含有总分低于表中含有总分低于80课程的所有学生课程的所有学生总分增加总分增加5%。分析：利用分析：利用UPDATE成批修改表数据，可以在成批修改表数据，可以在WHERE子句的利用子查询实现。子句的利用子查询实现。清华大学出版社. SQL Server 2

52、005数据库应用与开发 6.2.5利用子查询修改表数据利用子查询修改表数据 程序代码如下：程序代码如下：UPDATE scSET total=total*1.05WHERE courseno in (SELECT courseno FROM sc where total80 ) select * from sc程序运行结果如下：程序运行结果如下：studentno courseno total- - -0822111208 c05109 89.800822111208 c06108 98.490822111208 c06127 72.660823210007 c05103 75.1808232

53、10007 c05109 86.200824113307 c05103 82.530824113307 c05109 80.800824113307 c06108 82.74(8 行受影响行受影响)清华大学出版社. SQL Server 2005数据库应用与开发 6.2.6 EXISTS和和NOT EXISTS子句子句 nEXISTS 是是SQL语句中的运算符号，在子查询中，语句中的运算符号，在子查询中，如果存在一些匹配的行，结果为如果存在一些匹配的行，结果为TURE。n在执行过程中，一旦查找到第在执行过程中，一旦查找到第1个匹配的行，查个匹配的行，查询就结束。询就结束。nNOT EXISTS

54、 与与 EXISTS 的工作方式类似。的工作方式类似。 例例6.17查询查询student表中是否存在表中是否存在1990年年12月月12日日以后出生的学生，如果存在，输出学生的学号、以后出生的学生，如果存在，输出学生的学号、姓名、生日和电话。姓名、生日和电话。分析：只要存在一行数据符合条件，则分析：只要存在一行数据符合条件，则WHERE条件就返回条件就返回TURE，于是输出所有行。，于是输出所有行。 清华大学出版社. SQL Server 2005数据库应用与开发 6.2.6 EXISTS和和NOT EXISTS子句子句程序代码如下：程序代码如下：SELECT studentno,sname

55、,birthday,phoneFROM studentWHERE EXISTS ( SELECT * FROM student WHERE birthday 1990-12-12 )程序运行结果如下：程序运行结果如下：studentno sname birthday phone- - - -0822111208 韩吟秋韩吟秋 1989-12-18 00:00:00.0000823210007 宿致远宿致远 1988-12-07 00:00:00.000 12545678998 0935222201 夏文斐夏文斐 1990-08-09 00:00:00.000 1597

56、8945645 0937221508 平靖平靖 1989-12-17 00:00:00.000 12367823453 (10 行受影响行受影响)清华大学出版社. SQL Server 2005数据库应用与开发 6.3.1游标的概念游标的概念nSELECT语句一般返回的是包含多条记录的、存语句一般返回的是包含多条记录的、存放在客户机内存中的结果集。当用户需要访问一放在客户机内存中的结果集。当用户需要访问一个结果集中的某条具体记录时，就需要使用游标个结果集中的某条具体记录时，就需要使用游标功能。功能。nSQL Server 2005使用英文单词使用英文单词CURSOR来表示来表示游标。使用关键字

57、游标。使用关键字GLOBAL和和LOCAL表示一个表示一个游标声明为全局游标和局部游标。游标声明为全局游标和局部游标。 清华大学出版社. SQL Server 2005数据库应用与开发 6.3.1游标的概念游标的概念n作为全局游标，一旦被创建就可以在任何位置作为全局游标，一旦被创建就可以在任何位置上访问，当多个不同的过程或函数需要访问和上访问，当多个不同的过程或函数需要访问和管理同一结果集时，应使用全局游标。管理同一结果集时，应使用全局游标。n局部游标管理起来更容易一些，因而其安全性局部游标管理起来更容易一些，因而其安全性也相对较高。局部游标可以在一个存储过程、也相对较高。局部游标可以在一个存

58、储过程、触发器或用户自定义的函数中声明。触发器或用户自定义的函数中声明。清华大学出版社. SQL Server 2005数据库应用与开发 6.3.1游标的概念游标的概念使用游标使用游标(CURSOR)的步骤如下。的步骤如下。n声明游标。在使用游标之前，首先需要声明游标。声明游标。在使用游标之前，首先需要声明游标。n打开游标。打开一个游标意味着在游标中输入了相关打开游标。打开一个游标意味着在游标中输入了相关的记录信息。的记录信息。n获取记录信息。首先将游标当前指向的记录保存到一获取记录信息。首先将游标当前指向的记录保存到一个局部变量中，然后游标将自动移向下一条记录。将个局部变量中，然后游标将自动

59、移向下一条记录。将一条记录读入某个局部变量后，就可以根据需要对其一条记录读入某个局部变量后，就可以根据需要对其进行处理了。进行处理了。n关闭游标。释放游标锁定的记录集。关闭游标。释放游标锁定的记录集。n释放游标。释放游标自身所占用的资源。释放游标。释放游标自身所占用的资源。清华大学出版社. SQL Server 2005数据库应用与开发 6.3.2 游标的运用游标的运用 1. 声明游标声明游标声明游标的格式如下：声明游标的格式如下：DECLARE cursor_name CURSOR LOCAL | GLOBAL FORWARD_ONLY| SCROLL STATIC|KEYSET|DYNAMIC| FAST_FORWARD READ_ONLY | SCROLL_LOCKS | OPTIMISTIC TYPE_WARNING FOR select_statement FOR UPDATE OF column_name ,.n ;清华大学出版社. SQL Server 2005数据库应用与开发 6.3.2 游标的运用游标的运用 1. 声明游标声明游标例例6.18 使用使用STATIC关键字声明全局游标关键字声明全局游标cEmploy。分析分析: 使用使用STATIC关键字声明全局游标关键字声明全局游标cEmploy，该游标与表，该游标与表student中的所有男生记录中的所有