最新数据库技术及应用指南课件PPTCH3 SQL语言教学讲义ppt.ppt 228页

'数据库技术及应用指南课件PPTCH3SQL语言 第3章SQL语言学习目标SQL(StructuredQueryLanguage，结构化查询语言)是关系数据库的标准语言几乎所有的关系型数据库管理系统均采用SQL语言标准教学目标掌握对数据库的基本操作，了解数据库管理系统的基本功能熟练掌握SQL查询语句，并运用SQL语句完成对数据库的操作2 第3章SQL语言学习方法结合课堂讲授的知识，强化上机实训，把课堂上学过的有关概念进行实训，以便加深理解，达到学习目标。学习指南重点：3.2、3.3、3.4、3.5、3.8和3.9节难点：3.3、3.4和3.5节3 3.1SQL概述3.1.1SQL发展3.1.2SQL特点3.1.3SQL查询基本概念7 3.1.1SQL发展SQL-86：第一个SQL标准，由美国国家标准局(AmericanNationalStandardInstitute，简称ANSI)公布，1987年国际标准化组织(InternationalOrganizationforStandardization，简称ISO)通过。该标准也称为SQL-1SQL-92：在1992年，由ISO和ANSI对SQL-86进行重新修订，该标准也称为SQL-2SQL-99：在1999年，该版本在SQL-2的基础上，扩展了诸多功能，包括递归、触发、面向对象技术等。该标准也称为SQL-3SQL-2003：该标准是最新的标准，也称SQL-4，于2003年发布8 3.1.1SQL发展SQL语言由4部分组成数据定义语言DDL(DataDefinitionLanguage)定义数据库的逻辑结构，包括数据库、基本表、视图和索引等，扩展DDL还支持存储过程、函数、对象、触发器等的定义DDL包括3类语言，即定义、修改和删除数据操纵语言DML(DataManipuplationLanguage)对数据库的数据进行检索和更新，其中更新操作包括插入、删除和修改数据数据控制语言DCL(DataControlLanguage)对数据库的对象进行授权、用户维护(包括创建、修改和删除)、完整性规则定义和事务定义等其它主要是嵌入式SQL语言和动态SQL语言的定义，规定了SQL语言在宿主语言中使用的规则扩展SQL还包括数据库数据的重新组织、备份与恢复等功能9 3.1.2SQL特点综合统一集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体高度非过程化面向集合的操作方式采用集合操作方式，其操作对象、查找结果都是元组的集合同一种语法结构提供两种使用方式SQL语言既是自含式语言，又是嵌入式语言。在两种不同的使用方式下，其语法结构基本上是一致的语言简洁，易学易用SQL语言的动词非常少，主要包括：数据查询SELECT；数据定义CREATE、DROP、ALTER；数据操纵INSERT、UPDATE、DELETE；数据控制GRANT、REVOKE10 3.1.3SQL查询基本概念SQL语言支持三级模式结构，外模式对应视图和部分基本表，模式对应基本表，内模式对应存储文件SQL视图1视图2基本表1基本表2基本表3基本表4存储文件1存储文件2外模式模式内模式11 3.1.3SQL查询基本概念基本表数据库中独立存在的表称为基本表在SQL中一个关系对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引索引存放在存储文件中视图指从一个或几个基本表(或视图)导出的表，是虚表只存放视图的定义而不存放对应数据查询表指查询结果对应的表存储文件指数据库中存放关系的物理文件12 目录嵌套子查询3.7SQL概述3.1简单查询3.3连接查询3.53.8聚合查询集合运算SQL查询一般格式SQL数据定义语言SQL数据更新语言视图3.23.43.63.93.1013 3.2　简单查询本章所用的数据库为学生成绩管理数据库ScoreDB，其数据库模式如图3-2、3-3、3-4、3-5所示14 3.2　简单查询15 3.2　简单查询16 3.2　简单查询17 3.2　简单查询3.2.1投影运算3.2.2选择运算3.2.3排序运算3.2.4查询表18 3.2.1投影运算SQL基本结构包括3个子句：SELECT子句对应投影运算，指定查询结果中所需要的属性或表达式FROM子句对应笛卡尔积，给出查询所涉及的表，表可以是基本表、视图或查询表WHERE子句对应选择运算，指定查询结果元组所需要满足的选择条件SELECT和FROM是必须的，其他是可选的19 3.2.1投影运算基本语法为：SELECTA1,A2,...,AnFROMR1,R2,...,RmWHEREPA1,A2,...,An代表需要查找的属性或表达式R1,R2,...,Rm代表查询所涉及的表P代表谓词(即选择条件)，如果省略WHERE子句，表示P为真SQL的查询结果中允许包含重复元组SQL执行过程：首先对R1,R2,...,Rm执行笛卡尔积然后在笛卡尔积中选择使得谓词P为真的记录再在A1,A2,...,An属性列中进行投影运算，不消除重复元组如需消除重复元组，必须使用关键字DISTINCT刚才描述的SQL查询执行过程只是逻辑上的，在具体执行时会进行优化处理，查询优化的内容详见第7章。20 3.2.1投影运算查询指定列选取表中的全部列或指定列，通过SELECT确定要查询的属性[例3.1]查询所有班级的班级编号、班级名称和所属学院SELECTclassNo,className,instituteFROMClass该查询的执行过程是：从Class表中依次取出每个元组对每个元组仅选取classNo、className和institute三个属性的值，形成一个新元组最后将这些新元组组织为一个结果关系输出该查询的结果如图3-10所示21 3.2.1投影运算消除重复元组需要消除重复元组，使用DISTINCT关键字[例3.2]查询所有学院的名称。SELECTinstituteFROMClass上述查询不消除重复元组，其查询结果如图3-11所示消除重复元组，查询结果如图3-12所示SELECTDISTINCTinstituteFROMClass图3-11图3-1222 3.2.1投影运算查询所有列可使用两种方法：将所有的列在SELECT子句中列出(可以改变列的显示顺序)；使用*符号，*表示所有属性，按照表定义时的顺序显示所有属性[例3.3]查询所有班级的全部信息。SELECTclassNo,className,classNum,grade,instituteFROMClass或SELECT*FROMClass23 3.2.1投影运算给属性列取别名可为属性列取一个便于理解的列名，如用中文来显示列名为属性列取别名特别适合经过计算的列[例3.4]查询所有班级的所属学院、班级编号和班级名称，要求用中文显示列名SELECTinstitute所属学院,classNo班级编号,className班级名称FROMClass查询结果如图3-13所示。该查询可使用AS关键字取别名：SELECTinstituteAS所属学院,classNoAS班级编号,classNameAS班级名称FROMClass24 3.2.1投影运算查询经过计算的列可使用属性、常数、函数和表达式[例3.5]查询每个班级编号、班级名称以及该班级现在为几年级，并将班级编号中大写字母改为小写字母输出SELECTlower(classNo)班级编号,className,year(getdate())-gradeAS年级FROMClass函数lower()将大写字母改为小写字母函数getdate()获取当前系统的日期函数year()提取日期中的年份查询结果如图3-14所示25 3.2.2选择运算WHERE子句可实现关系代数中的选择运算WHERE常用的查询条件有：比较运算：>、>=、<、<=、=、<>(或!=)范围查询：BETWEEN...AND集合查询：IN空值查询：ISnull字符匹配查询：LIKE逻辑查询：AND、OR、NOT26 3.2.2选择运算比较运算使用比较运算符>、>=、<、<=、=、<>(或!=)[例3.6]查询2007级的班级编号、班级名称和所属学院。SELECTclassNo,className,instituteFROMClassWHEREgrade=2007其查询结果如图3-15所示27 3.2.2选择运算该查询的执行过程可能有多种方法：全表扫描法依次取出Class表中的每个元组判断该元组的grade属性值是否等于2007若是则将该元组的班级编号、班级名称和所属学院属性取出，形成一个新元组最后将所有新元组组织为一个结果关系输出该方法适用于小表，或者该表未在grade属性列上建索引索引搜索法如果该表在grade属性列上建有索引，且满足条件的记录不多，则可使用索引搜索法来检索数据具体使用何种方法由数据库管理系统的查询优化器来选择，详见第8章内容28 3.2.2选择运算[例3.7]在学生Student表中查询年龄大于或等于19岁的同学学号、姓名和出生日期。SELECTstudentNo,studentName,birthdayFROMStudentWHEREyear(getdate())-year(birthday)>=1929 3.2.2选择运算范围查询BETWEEN...AND用于查询属性值在某一个范围内的元组NOTBETWEEN...AND用于查询属性值不在某一个范围内的元组BETWEEN后是属性的下限值，AND后是属性的上限值[例3.8]在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩SELECTstudentNo,courseNo,scoreFROMScoreWHEREscoreBETWEEN80AND90该查询也可以使用逻辑运算AND实现，见例3.2230 3.2.2选择运算[例3.9]在选课Score表中查询成绩不在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscoreNOTBETWEEN80AND90该查询也可以使用逻辑运算OR实现，见例3.2331 3.2.2选择运算集合查询IN用于查询属性值在某个集合内的元组NOTIN用于查询属性值不在某个集合内的元组IN后面是集合，可以是具体的集合，也可以是查询出来的元组集合(该部分内容详见3.4节的内容)。[例3.10]在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNoIN("001","005","003")该查询也可以使用逻辑运算OR实现，见例3.1932 3.2.2选择运算[例3.11]在学生Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号。SELECTstudentName,native,classNoFROMStudentWHEREnativeNOTIN("南昌","上海")该查询也可以使用逻辑运算AND实现，见例3.2133 3.2.2选择运算空值查询空值表示未知或不确定的值，空值表示为nullISnull用于查询属性值为空值ISNOTnull用于查询属性值不为空值IS不能用“=”替代[例3.12]在课程Course表中查询先修课程为空值的课程信息。SELECT*FROMCourseWHEREpriorCourseISNULL查询结果如图3-16所示34 3.2.2选择运算[例3.13]在课程Course表中查询有先修课程的课程信息。SELECT*FROMCourseWHEREpriorCourseISNOTNULL35 3.2.2选择运算字符匹配查询LIKE用于字符匹配查询，语法格式为：[NOT]LIKE<匹配字符串>[ESCAPE<换码字符>]查询的含义是：如果在LIKE前没有NOT，则查询指定的属性列值与<匹配字符串>相匹配的元组；如果在LIKE前有NOT，则查询指定的属性列值不与<匹配字符串>相匹配的元组。<匹配字符串>可以是一个具体的字符串，也可以包括通配符%和_%表示任意长度的字符串ab%，表示所有以ab开头的任意长度的字符串；zhang%ab，表示以zhang开头，以ab结束，中间可以是任意个字符的字符串。符号_(下划线)表示任意一个字符ab：所有以ab开头的3个字符的字符串，其中第3个字符为任意字符；a__b：所有以a开头，以b结束的4个字符的字符串，且第2、3个字符为任意字符。36 3.2.2选择运算[例3.14]在班级Class表中查询班级名称中含有会计的班级信息SELECT*FROMClassWHEREclassNameLIKE"%会计%"注意：匹配字符串必须用一对引号括起来[例3.15]在学生Student表中查询所有姓王且全名为3个汉字的同学学号和姓名SELECTstudentNo,studentNameFROMStudentWHEREstudentNameLIKE"王__"注意：在中文SQL-Server中，如果匹配字符串为汉字，则一个下划线代表一个汉字；如果是西文，则一个下划线代表一个字符。37 3.2.2选择运算[例3.16]在学生Student表中查询名字中不含有“福”的同学学号和姓名。SELECTstudentNo,studentNameFROMStudentWHEREstudentNameNOTLIKE"%福%"38 3.2.2选择运算[例3.17]在学生Student表中查询蒙古族的同学学号和姓名SELECTstudentNo,studentNameFROMStudentWHEREnationLIKE"蒙古族"注意：如果匹配字符串中不含有%和_，则LIKE与比较运算符“=”的查询结果一样该查询等价于下面的查询：SELECTstudentNo,studentNameFROMStudentWHEREnation="蒙古族"39 3.2.2选择运算如果查询字串中本身要包含%和_，必须使用“ESCAPE<换码字符>”短语，对通配符进行转义处理。[例3.18]在班级Class表中查询班级名称中含有“08_”符号的班级名称SELECTclassNameFROMClassWHEREclassNameLIKE"%08_%"ESCAPE""“ESCAPE‘’”表示为换码字符紧跟在符号后的_不是通配符，而是普通的用户要查询的符号查询结果如图3-17所示40 3.2.2选择运算如果将#字符作为换码字符，则该查询可改写为：SELECTclassNameFROMClassWHEREclassNameLIKE"%08#_%"ESCAPE"#"41 3.2.2选择运算逻辑查询SQL提供AND、OR和NOT逻辑运算符分别实现逻辑与、逻辑或和逻辑非运算[例3.19]在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNo="001"ORcourseNo="005"ORcourseNo="003"在例3.10中使用的是集合运算，本例中采用逻辑“或”运算42 3.2.2选择运算[例3.20]在Student表中查询1991年出生且籍贯为“汉族”的同学学号、姓名、出生日期。SELECTstudentNo,studentName,birthdayFROMStudentWHEREyear(birthday)=1991ANDnation="汉族"注意：在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算如在选课Score表中查询同时选修了“001”和“002”课程的同学的选课信息，如下查询是错误的，得不到结果：SELECT*FROMScoreWHEREcourseNo="001"ANDcourseNo="002"要实现该查询，需要使用连接运算或嵌套子查询通过连接运算表示该查询，参见例3.29、例3.31通过嵌套子查询，参见例3.39、例3.4043 3.2.2选择运算[例3.21]在Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号。SELECTstudentName,native,classNoFROMStudentWHEREnative!="南昌"ANDnative!="上海"[例3.22]在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore>=80ANDscore<=9044 3.2.2选择运算[例3.23]在选课Score表中查询成绩不在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore<80ORscore>9045 3.2.3排序运算使用ORDERBY子句实现排序运算，其语法为：ORDERBY<表达式1>[ASC|DESC][,<表达式2>[ASC|DESC],...]其中：<表达式1>,<表达式2>,...可以是属性、函数或表达式缺省按升序(ASC)排序按降序排序，必须指明DESC选项该运算含义是：在查询结果中首先按<表达式1>的值进行排序在<表达式1>值相等的情况下再按<表达式2>值排序依此类推46 3.2.3排序运算[例3.24]在学生Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号，并按籍贯的降序排序输出。SELECTstudentName,native,classNoFROMStudentWHEREnative!="南昌"ANDnative!="上海"ORDERBYnativeDESC查询结果如图3-18所示47 3.2.3排序运算[例3.25]在学生Student表中查询“女”学生的学号、姓名、所属班级编号和出生日期，并按班级编号的升序、出生日期的月份降序排序输出。SELECTstudentNo,studentName,classNo,birthdayFROMStudentWHEREsex="女"ORDERBYclassNo,month(birthday)DESC其中：month()函数表示提取日期表达式的月份查询结果如图3-19所示48 3.2.4查询表FROM子句后面可以是基本关系、视图，还可以是查询表[例3.26]查询1991年出生的“女”同学基本信息。分析：可以先将学生表中的女生记录查询出来，然后再对查询表进行选择、投影操作。SELECTstudentNo,studentName,birthdayFROM(SELECT*FROMStudentWHEREsex="女")ASaWHEREyear(birthday)=1991在FROM子句后是一个子查询，表示对子查询的查询结果——查询表进行查询必须为查询表取一个名称(称为元组变量)，如使用ASa取名为aFROM(SELECT*FROMStudentWHEREsex="女")a该查询等价于下面的查询：SELECTstudentNo,studentName,birthdayFROMstudentWHEREyear(birthday)=1991ANDsex="女"49 小结（1）选择运算中各种条件的表达式的正确运用。（2）在学习过程中一定要多练习，要在安装好的数据库系统上进行实际操作，检查你的答案，你查询的结果是否正确。只有通过大量练习才能真正达到举一反三的熟练程度。（3）在使用具体的SQL时，能有意识地和关系代数进行比较，了解他们各自的特点。50 目录嵌套子查询3.7SQL概述3.1简单查询3.3连接查询3.53.8聚合查询集合运算SQL查询一般格式SQL数据定义语言SQL数据更新语言视图3.23.43.63.93.1051 3.3　连接查询在实际应用中，往往会涉及到多个关系的查询，需用到连接运算或子查询连接运算是关系数据库中使用最广泛的一种运算，包括等值连接、自然连接、非等值连接、自表连接和外连接等连接查询动画演示3.3.1等值与非等值连接3.3.2自表连接3.3.3外连接52 3.3.1等值与非等值连接该运算在WHERE子句中加入连接多个关系的连接条件格式为：WHERE[<表1>.]<属性名1><比较运算符>[<表2>.]<属性名2>[<逻辑运算符>[<表3>.]<属性名3><比较运算符>[<表4>.]<属性名4>...]比较运算符包括：>、>=、<、<=、=、<>(或!=)当比较运算符为=时，表示等值连接其他运算为非等值连接WHERE子句的连接谓词中的属性称为连接属性连接属性之间必须具有可比性53 3.3.1等值与非等值连接等值连接[例3.27]查找会计学院全体同学的学号、姓名、籍贯、班级编号和所在班级名称。该查询的结果为学号、姓名、籍贯、班级编号和班级名称，在SELECT子句中必须包含这些属性由于班级名称和所属学院在班级表Class中，学号、姓名、籍贯、班级编号在学生表Student中，FROM子句必须包含Class表和Student表由于班级编号classNo既是班级表的主码，也是学生表的外码，这2个表的连接条件是claaaNo相等，在WHERE子句中必须包含连接条件Student.classNo=Class.classNo本查询要查询出会计学院的学生记录，在WHERE子句中还必须包括选择条件institute="会计学院"54 3.3.1等值与非等值连接本查询语句为：SELECTstudentNo,studentName,native,Student.classNo,classNameFROMStudent,ClassWHEREStudent.classNo=Class.classNoANDinstitute="会计学院"在连接操作中，如果涉及到多个表的相同属性名，必须在相同的属性名前加上表名加以区分如Student.classNo、Class.classNoWHERE子句中Student.classNo=Class.classNo为连接条件institute="会计学院"为选择条件55 可为参与连接的表取别名(称为元组变量)，在相同的属性名前加上表的别名。将Student表取别名为a，Class表取别名为b，班级编号分别用a.classNo和b.classNo表示。本例可以改写为：SELECTstudentNo,studentName,native,b.classNo,classNameFROMStudentASa,ClassASbWHEREa.classNo=b.classNoANDinstitute="会计学院"或者SELECTstudentNo,studentName,native,b.classNo,classNameFROMStudenta,ClassbWHEREa.classNo=b.classNoANDinstitute="会计学院"对于不同的属性名，可以不在属性名前加上表名(别名)。3.3.1等值与非等值连接56 3.3.1等值与非等值连接[例3.28]查找选修了课程名称为“计算机原理”的同学学号、姓名。查询结果为学号、姓名，在SELECT子句中必须包含这些属性学号和姓名在学生表中，课程名称在课程表中，FROM子句必须包含学生表Student、课程表Course学生表与课程表之间是多对多联系，需通过成绩表转换为两个多对一的联系，FROM子句必须包含成绩表Score课程号既是课程表的主码，也是成绩表的外码，这2个表的连接条件是课程号相等；学号既是学生表的主码，也是成绩表的外码，这2个表的连接条件是学号相等。在WHERE子句中涉及三个关系的连接，其连接条件为：Course.courseNo=Score.courseNoANDScore.studentNo=Student.studentNo查找选修“计算机原理”课程的同学，在WHERE子句中必须包括选择条件courseName="计算机原理"57 3.3.1等值与非等值连接本查询语句为：SELECTa.studentNo,studentNameFROMStudenta,Courseb,ScorecWHEREb.courseNo=c.courseNoANDc.studentNo=a.studentNoANDb.courseName="计算机原理"本例使用了元组变量，其连接条件为：b.courseNo=c.courseNoANDc.studentNo=a.studentNo58 3.3.1等值与非等值连接[例3.29]查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩，并按学号排序输出。查询结果为学号、姓名、课程号和相应成绩，在SELECT子句中必须包含这些属性学号和姓名在学生表中，课程号和成绩在成绩表中，FROM子句必须包含学生表Student和成绩表Score学号既是学生表的主码，也是成绩表的外码，这2个表的连接条件是学号相等，WHERE子句必须包含这个连接条件SELECTa.studentNo,studentName,b.courseNo,b.scoreFROMStudenta,ScorebWHEREa.studentNo=b.studentNo59 3.3.1等值与非等值连接为表示同时选修“001”和“002”课程的选择条件首先在WHERE子句中直接包含选择条件courseNo=‘001’以查找出所有选修了“001”课程的同学其次，基于成绩表Score构造一个查询表c，查找出选修了编号为“002”课程的所有同学(SELECT*FROMScoreWHEREcourseNo="002")cSELECTa.studentNo,studentName,b.courseNo,b.scoreFROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDb.courseNo=‘001’60 3.3.1等值与非等值连接为表示同时选修“001”和“002”课程的选择条件最后，将选修了编号为“001”课程的元组与查询表c的元组关于学号进行等值连接SELECTa.studentNo,studentName,b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,Scoreb,(SELECT*FROMScoreWHEREcourseNo="002")cWHEREb.courseNo="001"ANDa.studentNo=b.studentNoANDa.studentNo=c.studentNo如果连接成功，表示该同学同时选修了这两门课程61 3.3.1等值与非等值连接要求按学号排序输出，需要排序语句ORDERBY本查询语句为：SELECTa.studentNo,studentName,b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,Scoreb,(SELECT*FROMScoreWHEREcourseNo="002")cWHEREb.courseNo="001"ANDa.studentNo=b.studentNoANDa.studentNo=c.studentNoORDERBYa.studentNo62 3.3.1等值与非等值连接该查询也可以表示为：SELECTa.studentNo,studentName,b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,(SELECT*FROMScoreWHEREcourseNo="001")b,(SELECT*FROMScoreWHEREcourseNo="002")cWHEREa.studentNo=b.studentNoANDa.studentNo=c.studentNoORDERBYa.studentNo63 3.3.1等值与非等值连接该查询还可以表示为：SELECTa.studentNo,studentName,b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,Scoreb,ScorecWHEREa.studentNo=b.studentNoANDa.studentNo=c.studentNoANDb.courseNo="001"ANDc.courseNo="002"ORDERBYa.studentNo64 3.3.1等值与非等值连接自然连接SQL不直接支持自然连接，完成自然连接的方法是在等值连接的基础上消除重复列[例3.30]实现成绩表Score和课程表Course的自然连接。SELECTstudentNo,a.courseNo,score,courseName,creditHour,courseHour,priorCourseFROMScorea,CoursebWHEREa.courseNo=b.courseNo本例课程编号在两个关系中同时出现，但在SELECT子句中仅需出现1次，因此使用a.courseNo，也可以使用b.courseNo。其他列名是唯一的，不需要加上元组变量65 3.3.1等值与非等值连接非等值连接非等值连接使用的比较少。在关系代数部分已经举过了一个非等值连接的例子（P61-63，例2.16），这里就不再举例了。在数据库ScoreDB中，查找课程号为“AC001”课程的考试中比学号为“0703045”的学生考得更好的所有学生的姓名和成绩。66 3.3.2自表连接若某个表与自己进行连接，称为自表连接[例3.31]查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩，并按学号排序输出。学生姓名在学生表中，FROM子句必须包含学生表(取别名为a)可以考虑两个成绩表，分别记为b和cb表用于查询选修了编号为“001”课程的同学c表用于查询选修了编号为“002”课程的同学FROM子句还必须包含两个成绩表b和c，且在WHERE子句中包含两个选择条件：b.courseNo="001"ANDc.courseNo="002"67 3.3.2自表连接成绩表b与成绩表c在学号上做等值连接(自表连接)，如果连接成功，表示学生同时选修了编号为“001”和“002”的课程学生表与成绩表b(或成绩表c)在学号上做等值连接。WHERE子句包含两个连接条件：b.studentNo=c.studentNoANDa.studentNo=b.studentNo本查询语句为：SELECTa.studentNo,studentName,b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,Scoreb,ScorecWHEREb.courseNo="001"ANDc.courseNo="002"ANDa.studentNo=b.studentNoANDb.studentNo=c.studentNoORDERBYa.studentNo本查询结果与例3.29相同在该查询中，FROM后包含了两个参与自表连接的成绩表Score，必须定义元组变量加以区分自表连接的条件是b.studentNo=c.studentNo68 3.3.2自表连接[例3.32]在学生表Student中查找与“李宏冰”同学在同一个班的同学姓名、班级编号和出生日期。SELECTa.studentName,a.classNo,a.birthdayFROMStudenta,StudentbWHEREb.studentName="李宏冰"ANDa.classNo=b.classNo69 3.3.3外连接在一般的连接中，只有满足连接条件的元组才被检索出来，对于没有满足连接条件的元组是不作为结果被检索出来的。[例3.33]查询每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。SELECTclassName,institute,studentNo,studentNameFROMClassa,StudentbWHEREa.classNo=b.classNoORDERBYclassName70 3.3.3外连接从查询结果中可以看出：班级表中的“注册会计08_01班”、“注册会计08_03班”以及“金融管理07_01班”这3个班没有出现在查询结果中，原因是这3个班没有学生在实际应用中，往往需要将不满足连接条件的元组也检索出来，只是在相应的位置用空值替代，这种查询称为外连接查询外连接分为左外连接、右外连接和全外连接在FROM子句中，写在左边的表称为左关系，写在右边的表称为右关系71 3.3.3外连接左外连接连接结果中包含左关系中的所有元组，对于左关系中没有连接上的元组，其右关系中的相应属性用空值替代[例3.34]使用左外连接查询每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。SELECTclassName,institute,studentNo,studentNameFROMClassaLEFTOUTERJOINStudentbONa.classNo=b.classNoORDERBYclassName72 3.3.3外连接右外连接连接结果中包含右关系中的所有元组，对于右关系中没有连接上的元组，其左关系中的相应属性用空值替代[例3.35]使用右外连接查询每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出SELECTclassName,institute,studentNo,studentNameFROMStudentaRIGHTOUTERJOINClassbONa.classNo=b.classNoORDERBYclassName73 3.3.3外连接全外连接连接结果中包含左、右关系中的所有元组对左关系中没有连接上的元组，其右关系中的相应属性用空值替代对右关系中没有连接上的元组，其左关系中的相应属性用空值替代[例3.36]使用全外连接查询每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。SELECTclassName,institute,studentNo,studentNameFROMClassaFULLOUTERJOINStudentbONa.classNo=b.classNoORDERBYclassName74 小结（1）连接运算是关系数据库中使用最广泛的一种运算，包括等值连接、自然连接、非等值连接、自表连接和外连接等。（2）在学习过程中一定要多练习，要在安装好的数据库系统上进行实际操作，检查你的答案，你查询的结果是否正确。只有通过大量练习才能真正达到举一反三的熟练程度。（3）在使用具体的SQL时，能有意识地和关系代数进行比较，了解他们各自的特点。75 目录嵌套子查询3.7SQL概述3.1简单查询3.3连接查询3.53.8聚合查询集合运算SQL查询一般格式SQL数据定义语言SQL数据更新语言视图3.23.43.63.93.1076 3.4　嵌套子查询在SQL查询中，一个SELECT-FROM-WHERE查询语句称为一个查询块将一个查询块嵌入到另一个查询块的WHERE子句或HAVING子句(见3.5节)中，称为嵌套子查询子查询的结果是集合,因此使用子查询是集合成员的检查如判断元组是否属于某个集合，集合的比较运算，以及测试是否为空集等具体表现在如下几个方面：元素与集合间的属于关系集合之间的包含和相等关系集合的存在关系元素与集合元素之间的比较关系77 3.4　嵌套子查询SQL允许多层嵌套子查询，但在子查询中，不允许使用ORDERBY子句，该子句仅用于最后的输出结果排序嵌套查询分为相关子查询和非相关子查询非相关子查询指子查询的结果不依赖于上层查询相关子查询指当上层查询的元组发生变化时，其子查询必须重新执行3.4.1使用IN的子查询3.4.2使用比较运算符的子查询3.4.3使用存在量词EXISTS的子查询78 3.4.1使用IN的子查询[例3.37]查询选修过课程的学生姓名。本例查询的含义是：在学生表Student中，将学号出现在成绩表Score中(表明该学生选修过课程)的学生姓名查询出来SELECTstudentNameFROMStudentWHEREStudent.studentNoIN(SELECTScore.studentNoFROMScore)在本例中，WHERE子句用于检测元素与集合间的属于关系其中Student.studentNo为元素，IN为“属于”嵌套语句“SELECTScore.studentNoFROMScore”的查询结果为选修过课程的所有学生的学号集合该嵌套SELECT语句称为子查询79 3.4.1使用IN的子查询该查询属于非相关子查询，其查询过程为：(1)从Score表中查询出学生的学号studentNo，构成一个中间结果关系r；(2)从Student表中取出第一个元组t；(3)如果元组t的studentNo属性的值包含在中间结果关系r中（即t.studentNo∈r），则将元组t的studentName属性的值作为最终查询结果关系的一个元组；否则丢弃元组t；(4)如果Student表中还有元组，则取Student表的下一个元组t，并转第(3)步；否则转第(5)步；(5)将最终结果关系显示出来80 3.4.1使用IN的子查询该查询的执行过程可以通过图3-23来表示81 3.4.1使用IN的子查询[例3.38]查找选修过课程名中包含“系统”的课程的同学学号、姓名和班级编号。SELECTstudentNo,studentName,classNoFROMStudentWHEREstudentNoIN(SELECTstudentNoFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseNameLIKE"%系统%"))WHERE子句中的IN可以实现多重嵌套，本例是一个三重嵌套的例子，该查询的执行过程可以通过图3-24来表示82 3.4.1使用IN的子查询83 3.4.1使用IN的子查询该查询也属于非相关子查询使用IN的非相关子查询的查询过程归纳如下：首先执行最底层的子查询块，将该子查询块的结果作为中间关系；执行上一层(即外一层)查询块，对于得到的每个元组，判断该元组是否在它的子查询结果中间关系中：如果在，取出该元组中的相关属性作为最终输出结果(或该查询块的查询结果中间关系)的一个元组否则舍弃该元组如果已经执行完最上层查询块，则将最终结果作为一个新关系输出；否则返回第(2)步重复执行84 3.4.1使用IN的子查询[例3.39]查找同时选修过“计算机原理”和“高等数学”两门课程的同学学号、姓名以及该同学所选修的所有课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出。分析：需查询同时选修过“计算机原理”和“高等数学”两门课程的同学学号、姓名以及该同学所选修的所有课程的课程名和相应成绩，在SELECT子句中必须包含studentNo、studentName、courseName和score四个属性学号、姓名在学生表中，课程成绩在成绩表中，课程名在课程表中，在FROM子句中必须包含学生表、课程表和成绩表，分别为这三张表取元组变量a、b、c学生表、成绩表和课程表需做连接操作，在WHERE子句中必须包含连接条件：a.studentNo=c.studentNoANDb.courseNo=c.courseNo85 3.4.1使用IN的子查询要查询同时选修过“计算机原理”和“高等数学”两门课程的同学，在WHERE子句中必须包含如下的选择条件：对于学生表，其学号必须是选修过“计算机原理”课程的学号，使用子查询：a.studentNoIN(SELECTstudentNoFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseName="计算机原理"))对于学生表，其学号还必须是选修过“高等数学”课程的学号，使用子查询：a.studentNoIN(SELECTstudentNoFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseName="高等数学"))这两个子查询必须同时满足，使用AND逻辑运算符86 3.4.1使用IN的子查询本查询语句为：SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDa.studentNoIN(SELECTstudentNoFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseName="计算机原理"))ANDa.studentNoIN(SELECTstudentNoFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseName="高等数学"))ORDERBYa.studentNo,scoreDESC87 3.4.1使用IN的子查询该查询也可以表示为如下形式：SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDa.studentNoIN(SELECTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName="计算机原理")ANDa.studentNoIN(SELECTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName="高等数学")ORDERBYa.studentNo,scoreDESC88 3.4.1使用IN的子查询[例3.40]查找同时选修过“计算机原理”和“高等数学”两门课程的同学学号、姓名以及所选修的这两门课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出分析：只查询该同学所选修的这两门课程的课程名和相应成绩，在WHERE子句中还必须包含选择条件：课程名称必须是“计算机原理”或“高等数学”，即courseName="高等数学"ORcourseName="计算机原理"89 3.4.1使用IN的子查询本查询语句为：SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDa.studentNoIN(SELECTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName="计算机原理")ANDa.studentNoIN(SELECTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName="高等数学")AND(courseName="高等数学"ORcourseName="计算机原理")ORDERBYa.studentNo,scoreDESC请将例3.39、例3.40的查询要求与查询语句的实现形式与例3.29、例3.31进行比较。90 3.4.2使用比较运算符的子查询元素与集合元素之间还存在更为复杂的关系，如比较关系，常用到谓词ANY(或SOME)和ALLANY表示子查询结果中的某个值ALL表示子查询结果中的所有值91 3.4.2使用比较运算符的子查询注意：如果子查询中的结果关系仅包含一个元组，则可将ALL和ANY去掉，直接使用比较运算符ANY也可以用SOME替代92 3.4.2使用比较运算符的子查询[例3.41]查询所选修课程的成绩大于所有“002”号课程成绩的同学学号及相应课程的课程号和成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore>ALL(SELECTscoreFROMScoreWHEREcourseNo="002")93 3.4.2使用比较运算符的子查询[例3.42]查询年龄小于“计算机科学与技术07-01班”某个同学年龄的所有同学的学号、姓名和年龄。SELECTstudentNo,studentName,year(getdate())-year(birthday)ASageFROMStudentWHEREyear(getdate())-year(birthday)})：统计关系的元组个数或一列中值的个数；sum([DISTINCT|ALL]<列名>)：统计一列中值的总和(此列必须为数值型)；avg([DISTINCT|ALL]<列名>)：统计一列中值的平均值(此列必须为数值型)；看动画吧！max([DISTINCT|ALL]<列名>)：统计一列中值的最大值；min([DISTINCT|ALL]<列名>)：统计一列中值的最小值。指定DISTINCT谓词，表示在计算时首先消除<列名>取重复值的元组，然后再进行统计指定ALL谓词或没有DISTINCT谓词，表示不消除<列名>取重复值的元组111 3.5.1聚合函数[例3.47]查询学生总人数。SELECTcount(*)FROMStudent或SELECTcount(*)学生人数FROMStudent[例3.48]查询所有选课学生的人数。SELECTcount(studentNo)学生人数FROMScore查询结果是40由于一个学生可以选修多门课程，学号存在重复，为消除重复的元组，使用DISTINCT短语，将查询修改为：SELECTcount(DISTINCTstudentNo)学生人数FROMScore查询结果为10112 3.5.1聚合函数[例3.49]查询学号为“0800005”同学所选修课程的总学分。SELECTsum(creditHour)总学分FROMScorea,CoursebWHEREstudentNo="0800005"ANDa.courseNo=b.courseNo在聚合函数遇到空值时，除count(*)外所有的函数皆跳过空值，只处理非空值。113 3.5.2分组聚合在SQL查询中，往往需要对数据进行分类运算(即分组运算，看看动画)分组运算的目的是为了细化聚合函数的作用对象如不对查询结果分组，则聚合函数作用于整个查询结果如对查询结果进行分组，则聚合函数分别作用于每个组，查询结果按组聚合输出SQL通过GROUPBY和HAVING子句实现分组运算GROUPBY对查询结果按某一列或某几列进行分组，值相等的分为一组；HAVING对分组的结果进行选择，仅输出满足条件的组。该子句必须与GROUPBY子句配合使用114 3.5.2分组聚合[例3.50]查询每个同学的选课门数、平均分和最高分。SELECTstudentNo,count(*)门数,avg(score)平均分,max(score)最高分FROMScoreGROUPBYstudentNo结果按学号StudentNo分组，将具有相同StudentNo值的元组作为一组然后对每组进行相应的计数、求平均值和求最大值115 3.5.2分组聚合[例3.51]查询平均分在80分以上的每个同学的选课门数、平均分和最高分。SELECTStudentNo,count(*)门数,avg(score)平均分,max(score)最高分FROMScoreGROUPBYStudentNoHAVINGavg(score)>=80按学号StudentNo分组，将具有相同StudentNo值的元组作为一组然后对每组进行相应的计数、求平均值和求最大值并判断平均值是否大于等于80，如果是则输出该组，否则丢弃该组，不作为输出结果116 3.5.2分组聚合[例3.52]查询成绩最高分的学生的学号、课程号和相应成绩SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore=(SELECTmax(score)FROMScore)聚合函数可直接用在HAVING子句中(如例3.51)，也可用于子查询中(如例3.52)，但在WHERE子句中不可以直接使用聚合函数。如下语句是不正确的：SELECT*FROMScoreWHEREscore=max(score)117 3.5.2分组聚合[例3.53]查询获得的总学分(注：只有成绩合格才能获得该课程的学分)大于或等于28的同学的学号、姓名和总学分，并按学号排序输出。SELECTa.studentNo,studentName,sum(creditHour)FROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDc.courseNo=b.courseNoANDscore>=60GROUPBYa.studentNo,studentName--输出结果的需要HAVINGsum(creditHour)>=28ORDERBYa.studentNo本例输出结果中需要同时包含学号和姓名因此，GROUPBY子句需要按“a.studentNo,studentName”进行聚合，不能仅按“a.studentNo”进行聚合，否则无法输出studentName118 3.5.2分组聚合本查询既使用了WHERE子句，也使用了HAVING子句，它们都是选择满足条件的元组，但是其选择的范围是不一样的，表现在：(1)WHERE子句：作用于整个查询对象，对元组进行过滤。(2)HAVING子句：仅作用于分组，对分组进行过滤。本例的查询过程是：①首先在Score表中选择课程成绩大于等于60分的元组(只有60分及以上才能获得学分)，将这些元组与Student和Score表进行连接，形成一个新关系；②在新关系中按学号进行分组，统计每组的总学分；③将总学分大于等于28的组选择出来形成一个结果关系；④将结果关系输出。119 3.5.3复杂查询SQL语句可构造非常复杂的查询，可将选择、投影、连接、子查询、分组聚合等操作混合使用，从而完成几乎所有的查询操作[例3.54]查询至少选修了28个学分的同学的学号、姓名以及所选修各门课程的课程名、成绩和学分，并按学号排序输出分析：①本例查询结果列是学号、姓名、课程名、成绩和学分，在SELECT子句中必须包含这些属性②学号、姓名在学生表中，课程名、学分在课程表中，成绩在成绩表中，在FROM子句中必须包含这3个表，分别取元组变量a、b、c③对这3个表进行连接操作，连接条件是：a.studentNo=c.studentNoANDc.courseNo=b.courseNo120 3.5.3复杂查询④结果关系中的学生必须是选修了28个以上(含28)学分的同学，使用一个子查询，用于检索满足该条件的学号由于学分在课程表中，选课记录在成绩表中，子查询涉及这2个表的连接操作，连接条件是课程号相同；子查询还要求所选修课程的总学分在28分以上，需要使用分组聚合运算，其分组属性为studentNo，分组选择条件是sum(creditHour)>=28子查询语句为：SELECTstudentNoFROMCoursex,ScoreyWHEREy.courseNo=x.courseNoGROUPBYstudentNoHAVINGsum(creditHour)>=28本例将分组聚合用于子查询中，该子查询的含义是：查询选修了28个以上(含28)学分的同学学号121 3.5.3复杂查询⑤在结果关系中的学号必须是子查询中的学号，在WHERE子句中除了包含学生表、课程表、成绩表的连接条件外，还必须有一个选择条件该条件是学号必须是子查询结果集合中的学号由于Student表中有学号，Score表中也有学号，既可取Student表中的学号a.studentNo，也可取Score表中的学号b.studentNo⑥本例要求按学号排序输出，需使用排序子句122 3.5.3复杂查询SELECTa.studentNo,studentName,courseName,score,creditHourFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDc.courseNo=b.courseNoANDa.studentNoIN(SELECTstudentNoFROMCoursex,ScoreyWHEREy.courseNo=x.courseNoGROUPBYstudentNoHAVINGsum(creditHour)>=28)ORDERBYa.studentNo123 3.5.3复杂查询[例3.55]查询至少选修了5门课程且课程平均分最高的同学的学号和课程平均分。分析：①查询同学的学号和课程平均分，使用求平均值的聚合函数，在SELECT子句中包含学号studentNo和课程平均分avg(score)②本例只要使用成绩表，在FROM子句中仅包含成绩表③查询至少选修了5门课程且课程平均分最高的同学的学号，使用分组运算，分组属性为学号④必须对分组后的结果进行选择运算，选择至少选修了5门课程且课程平均分最高的同学的学号，使用HAVING子句在HAVING子句中的第一个条件是选课数大于等于5，使用count(*)>=5；第二个条件是平均分最高，按如下步骤构造：124 3.5.3复杂查询使用一个查询表，用于查询至少选修了5门课程的同学的学号和课程平均分，该查询表仅涉及成绩表，语句为：SELECTstudentNo,avg(score)avgScoreFROMScoreGROUPBYstudentNoHAVINGcount(*)>=5使用一个子查询，该子查询从查询表中查询最高的平均分；语句为：SELECTmax(avgScore)FROM(SELECTstudentNo,avg(score)avgScoreFROMScoreGROUPBYstudentNoHAVINGcount(*)>=5)b在HAVING子句中的第二个条件是平均分等于子查询中查询出来的最高分125 3.5.3复杂查询⑤该查询语句为：SELECTstudentNo,avg(score)avgScoreFROMScoreGROUPBYstudentNoHAVINGcount(*)>=5ANDavg(score)=(SELECTmax(avgScore)FROM(SELECTstudentNo,avg(score)avgScoreFROMScoreGROUPBYstudentNoHAVINGcount(*)>=5)b)注意：在查询表中，如果查询的列是表达式，可以给该表达式取一个别名，这样在SELECT语句中就可直接使用该别名在本例的查询表中，将表达式avg(Score)取别名avgScore126 3.5.3复杂查询[例3.56]查询选修了所有4学分课程(即学分为4的课程)的同学的学号、姓名以及所选修4学分课程的课程名和成绩。分析：①与例3.45、例3.46类似，本例也要使用双重否定。在第一重否定中查询4学分的课程，在第二重否定中查询某学生选修某门4学分的课程②该查询表达的含义是：查询这样的学生，不存在某门4学分的课程他没有选修127 3.5.3复杂查询③该查询语句为：SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDNOTEXISTS(SELECT*FROMCoursexWHEREcreditHour=4--查询4学分课程的情况ANDNOTEXISTS--判断学生a.studentNo没有选修课程x.courseNo(SELECT*FROMScoreWHEREstudentNo=a.studentNoANDcourseNo=x.courseNo))ANDcreditHour=4--只显示满足上述要求的学生所选修4学分课程的课程名和成绩128 (1)SQL查询提供了丰富的数据分类、统计和计算的功能，其统计功能是通过聚合函数来实现的，分类功能是通过分组运算来实现的，并且统计和分组往往结合在一起实现丰富的查询功能。(2)分组运算的目的是为了细化聚合函数的作用对象。如果不对查询结果进行分组，则聚合函数作用于整个查询结果；如果对查询结果进行分组，则聚合函数分别作用于每个组，查询结果是按组聚合输出。SQL语句中通过使用GROUPBY和HAVING子句来实现分组运算。(3)SQL语句可以构造非常复杂的查询，可以将选择、投影、连接、子查询、分组聚合等操作混合使用，从而完成几乎所有的查询操作。小结129 目录嵌套子查询3.7SQL概述3.1简单查询3.3连接查询3.53.8聚合查询集合运算SQL查询一般格式SQL数据定义语言SQL数据更新语言视图3.23.43.63.93.10130 3.6集合运算SQL支持集合运算SELECT语句查询的结果是集合传统的集合操作主要包括并UNION、交INTERSECT、差EXCEPT运算在执行集合运算时要求参与运算的查询结果的列数一样，其对应列的数据类型必须一致131 3.6集合运算[例3.57]查询“信息管理学院”1990年出生的同学的学号、出生日期、班级名称和所属学院以及“会计学院”1991年出生的同学的学号、出生日期、班级名称和所属学院。SELECTstudentNo,birthday,className,instituteFROMStudenta,ClassbWHEREa.classNo=b.classNoANDyear(birthday)=1990ANDinstitute="信息管理学院"UNIONSELECTstudentNo,birthday,className,instituteFROMStudenta,ClassbWHEREa.classNo=b.classNoANDyear(birthday)=1991ANDinstitute="会计学院‘看动画吧!132 3.6集合运算该查询实际上是查询“信息管理学院”1990年出生的或“会计学院”1991年出生的同学的学号、出生日期、班级名称和所属学院，上述SQL语句可以改写为：SELECTstudentNo,birthday,className,instituteFROMStudenta,ClassbWHEREa.classNo=b.classNoAND(year(birthday)=1990ANDinstitute=‘信息管理学院"ORyear(birthday)=1991ANDinstitute="会计学院")ORDERBYinstitute133 3.6集合运算[例3.58]查询同时选修了“001”号和“005”号课程的同学的学号和姓名SELECTa.studentNo,studentNameFROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDcourseNo="001"INTERSECTSELECTa.studentNo,studentNameFROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDcourseNo="005‘134 3.6集合运算[例3.58]查询同时选修了“001”号和“005”号课程的同学的学号和姓名本例也可用下面的SQL语句实现SELECTa.studentNo,studentNameFROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDcourseNo="001"ANDa.studentNoIN(SELECTstudentNoFROMScoreWHEREcourseNo="005")注意：SQLServer数据库不支持交运算INTERSECT，交运算完全可以用其它运算替代135 3.6集合运算[例3.59]查询没有选修“计算机原理”课程的同学的学号和姓名。SELECTstudentNo,studentNameFROMStudentEXCEPTSELECTDISTINCTa.studentNo,studentNameFROMStudenta,Scoreb,CoursecWHEREa.studentNo=b.studentNoANDb.courseNo=c.courseNoANDcourseName="计算机原理"136 3.6集合运算[例3.59]查询没有选修“计算机原理”课程的同学的学号和姓名。本例也可用下面的SQL语句实现SELECTstudentNo,studentNameFROMStudentWHEREstudentNoNOTIN(SELECTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName="计算机原理")注意：SQLServer数据库不支持差运算EXCEPT，差运算完全可以用其它运算替代137 目录嵌套子查询3.7SQL概述3.1简单查询3.3连接查询3.53.8聚合查询集合运算SQL查询一般格式SQL数据定义语言SQL数据更新语言视图3.23.43.63.93.10138 3.7　SQL查询一般格式SELECT共有6个子句，其中SELECT和FROM是必须的，其它是可选项，必须严格按照如下顺序排列：SELECT[ALL|DISTINCT]<目标列表达式>[AS][<别名>][,<目标列表达式>[AS][<别名>],...]FROM<表名|视图名|查询表>[AS][<别名>][,<表名|视图名|查询表>[AS][<别名>],...][WHERE<条件表达式>][GROUPBY<列名1>[,<列名2>,...][HAVING<条件表达式>]][ORDERBY<列名表达式>[ASC|DESC][,<列名表达式>[ASC|DESC],...]]139 3.7　SQL查询一般格式其中：(1)<目标列表达式>可以是下面的可选格式：[<表名|别名>.]*,[<表名|别名>.]<列名>,<函数>,<聚合函数>(2)FROM子句指定查询所涉及的表、视图或查询表。为操作方便，常给表取一个别名，称为元组变量(3)WHERE子句给出查询的条件，随后的<条件表达式>中可以使用下面的谓词运算符：比较运算符：>，>=，<，<=，=，<>，!=；逻辑运算符：AND，OR，NOT；范围运算符：[NOT]BETWEEN...AND；集合运算符：[NOT]IN；空值运算符：IS[NOT]null；字符匹配运算符：[NOT]LIKE；存在量词运算符：[NOT]EXISTS。在<条件表达式>中可以包含子查询，但不可以直接使用聚合函数，若要使用聚合函数，必须引出一个子查询，如例3.52所示。140 3.7　SQL查询一般格式[例3.60]查询每一个同学的学号以及该同学所修课程中成绩最高的课程的课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreaWHEREscore=(SELECTmax(score)FROMScoreWHEREstudentNo=a.studentNo)(4)GROUPBY子句表示的含义是：首先按<列名1>进行分组，<列名1>值相同的分为一组；在同组情况下，再按<列名2>进行分组，<列名2>值相同的分为一组；依次类推包含GROUPBY时，SELECT通常选择GROUPBY的分组属性以及聚合属性(通常将聚合函数作用于聚合属性，如avg(score)、sum(creditHour)等)输出。141 3.7　SQL查询一般格式(5)HAVING子句给出分组后的选择条件，用来选择满足条件的分组。随后的<条件表达式>中可直接使用聚合函数，也可使用子查询。[例3.61]查询学生人数不低于500的学院的学院名称及学生人数。SELECTinstitute,count(*)人数FROMStudenta,ClassbWHEREa.classNo=b.classNoGROUPBYinstituteHAVINGcount(*)>=500[例3.62]查询平均分最高的课程的课程号、课程名和平均分。SELECTa.courseNo,courseName,avg(score)最高平均分FROMCoursea,ScorebWHEREa.courseNo=b.courseNoGROUPBYa.courseNo,courseNameHAVINGavg(score)=(SELECTmax(avgScore)FROM(SELECTavg(score)avgScoreFROMScoreGROUPBYcourseNo)x)142 3.7　SQL查询一般格式(6)ORDERBY子句实现对查询结果的排序它是SQL查询的最后一个操作，必须放在最后；其中的<列名表达式>可以是列名，也可以是表达式；如果是表达式，则先计算表达式的值，然后排序输出；排序有升序ASC和降序DESC，默认为升序。(7)集合运算SELECT语句之间可以进行集合运算，包括并UNION、交INTERSECT、差EXCEPT运算。143 目录嵌套子查询3.7SQL概述3.1简单查询3.3连接查询3.53.8聚合查询集合运算SQL查询一般格式SQL数据定义语言SQL数据更新语言视图3.23.43.63.93.10144 3.8　SQL数据定义语言数据库中的关系集合必须由数据定义语言DDL来定义，包括：数据库模式、关系模式、每个属性的值域、完整性约束、每个关系的索引集合和关系的物理存储结构等SQL数据定义语言包括：数据库的定义、修改和删除；基本表的定义、修改和删除；视图的定义、修改和删除；索引的定义和删除。这些对象的定义、修改和删除方式如表3-2所示145 3.8　SQL数据定义语言3.8.1数据库的定义3.8.2基本表的定义3.8.3索引的定义146 3.8.1数据库的定义数据库保存了企业所有的数据，以及相关的一些控制信息，如安全性和完整性约束、关系的存储路径等数据库包含了基本表、视图、索引以及约束等对象，在定义这些对象之前，必须首先定义数据库，然后在数据库中定义所有的对象数据库与其对象之间的关系如图3-29所示147 3.8.1数据库的定义数据库作为一个整体存放在外存的物理文件中物理文件有两种：一是数据文件，存放数据库中的对象数据；二是日志文件，存放用于恢复数据库的企业冗余数据物理文件可以是多个，可以将一个或若干个物理文件设置为一个逻辑设备数据库可以有多个逻辑设备，必须在定义数据库时进行定义数据库的对象存放在逻辑设备上，由逻辑设备与物理文件进行联系，从而实现数据库的逻辑模式与存储模式的独立148 3.8.1数据库的定义数据库的创建一个数据库创建在物理介质的一个或多个文件上，它预先分配了将要被数据库和事务日志所使用的物理存储空间存储数据的文件叫做数据文件(datafile)存储日志的文件叫做日志文件(logfile)创建一个新的数据库时，仅创建了一个空壳，必须在这个空壳中创建对象(如表等)，才能使用这个数据库当创建了一个数据库，与该数据库相关的描述信息会存入到系统的数据字典中在定义数据库的时候，必须定义数据库的名字、逻辑设备名和物理文件名149 3.8.1数据库的定义定义数据库操作的语法为：CREATEDATABASE[ON[[PRIMARY][,...n]]--定义主逻辑设备的数据文件[,[,...n]]]--定义用户逻辑设备的数据文件[LOGON{[,...n]}]--定义数据库日志的数据文件其中：(1)：被创建的数据库的名字，满足如下要求：长度从1到30，第一个字符必须是字母，或下划线_，或字符@在首字符后的字符可以是字母、数字或者前面规则中提到的字符名称中不能有空格；数据库的大小可以被扩展或者收缩。150 3.8.1数据库的定义(2)ON：指定数据库中的数据文件：主数据文件、用户数据文件其中指数据文件，它们用逗号来隔离，用来定义逻辑设备中的数据文件；除了主逻辑设备(PRIMARY)及相关数据文件外，用户还可以定义用户的逻辑设备(FILEGROUP)及相关用户数据文件(3)定义主逻辑设备中的数据(磁盘)文件：[PRIMARY]::=([NAME=,]FILENAME=""[,SIZE=][,MAXSIZE={|UNLIMITED}][,FILEGROWTH=])(4)定义用户逻辑设备中的数据(磁盘)文件：::=FILEGROUP151 3.8.1数据库的定义(5)PRIMARY：描述在主逻辑设备中定义的相关数据文件，所有的数据库系统表存放在主(primary)逻辑设备中，同时也存放没有分配具体逻辑设备的对象在主逻辑设备中第一个文件称为主数据文件，通常包括数据库的逻辑起始位置和系统表对于一个数据库来说，只能有一个主逻辑设备如果主逻辑设备没有指明，则创建数据库时所描述的第一个文件将作为主逻辑设备成员(6)LOGON：指定数据库日志的数据(磁盘)文件指日志文件。如果没有指定LOGON，系统将自动创建单个的日志文件152 3.8.1数据库的定义[例3.63]建立学生成绩数据库ScoreDBCREATEDATABASEScoreDBON(NAME=ScoreDB,FILENAME="e:SQLDatabaseScoreDB.mdf",SIZE=2,MAXSIZE=10,FILEGROWTH=1)LOGON(NAME=ScoreLog,FILENAME="e:SQLDatabaseScoreLog.ldf",SIZE=1,MAXSIZE=5,FILEGROWTH=1)153 3.8.1数据库的定义本例的含义是：在磁盘e:SQLDatabase目录下，创建一个ScoreDB数据库，只有一个主逻辑设备，对应一个物理文件ScoreDB.mdf该文件初始大小为2M，最大可扩展为10M；如果初始文件装不下数据，自动按1M进行扩展，直到10M为止日志文件为ScoreLog.ldf，该文件初始大小为1M，最大可扩展为5M；如果初始文件装不下数据，自动按1M进行扩展，直到5M为止。154 3.8.1数据库的定义[例3.64]建立一个复杂的数据库MyTempDBCREATEDATABASEMyTempDBONPRIMARY--定义主逻辑设备及其数据文件(NAME=TempDev,FILENAME="d:TempDataTempDev.mdf",SIZE=5,FILEGROWTH=2),FILEGROUPTempHisDev--定义第1个用户逻辑设备及其数据文件(NAME=TempHisDev1,FILENAME="d:TempDataTempHisDev1.mdf",SIZE=10,FILEGROWTH=5),155 FILEGROUPTempBakDev--定义第2个用户逻辑设备及其数据文件(NAME=TempBakDev1,FILENAME="d:TempDataTempBakDev1.mdf",SIZE=5,FILEGROWTH=2),(NAME=TempBakDev2,FILENAME="d:TempDataTempBakDev2.mdf",SIZE=5,FILEGROWTH=2),(NAME=TempBakDev3,FILENAME="d:TempDataTempBakDev3.mdf",SIZE=5,FILEGROWTH=2)LOGON--定义数据库日志的数据文件(NAME="TempLogDev1",FILENAME="d:TempDataTempLogDev1.ldf",SIZE=5MB,FILEGROWTH=2MB),(NAME="TempLogDev2",FILENAME="d:TempDataTempLogDev2.ldf",SIZE=5MB,FILEGROWTH=2MB)156 3.8.1数据库的定义在本例中，日志文件包含了2个数据(磁盘)文件：TempLogDev1.ldfTempLogDev2.ldf数据文件被分为3个逻辑设备主逻辑设备只有1个数据(磁盘)文件：TempDev.mdf用户逻辑设备有2个：一个用户逻辑设备是TempHisDev，包含1个磁盘文件：TempHisDev1.mdf另一个用户逻辑设备是TempBakDev，包含3个磁盘文件：TempBakDev1.mdfTempBakDev2.mdfTempBakDev3.mdf157 3.8.1数据库的定义数据库的修改数据库在运行过程中，可以依据数据量的大小进行修改修改数据库操作的语法为：ALTERDATABASE{ADDFILE[,...n][TOFILEGROUP  ]|ADDLOGFILE[,...n]|REMOVEFILE|ADDFILEGROUP|REMOVEFILEGROUP|MODIFYFILE|MODIFYFILEGROUP}158 3.8.1数据库的定义其中：：指定被修改的数据库的名字；ADDFILE：指定添加到数据库中的数据文件；TOFILEGROUP：指定文件添加到文件组名为的文件组；ADDLOGFILE：指定添加到数据库中的日志文件；REMOVEFILE：从数据库系统表中删除文件，并物理删除该文件；ADDFILEGROUP：指定添加到数据库文件组中；REMOVEFILEGROUP：从数据库中删除文件组，并删除在这个文件组中的文件；MODIFYFILE：指定要修改的文件，包含该文件的名称、大小、增长量和最大容量。注意：一次只可以修改其中的一个选项。159 3.8.1数据库的定义[例3.65]修改MyTempDB数据库。ALTERDATABASEMyTempDBMODIFYFILE(NAME=TempHisDev1,SIZE=20MB)将逻辑文件TempHisDev1初始大小修改为20M数据库的删除删除数据库时，系统会同时从系统的数据字典中将该数据库的描述一起删除有的数据库系统会自动删除与数据库相关联的物理文件删除数据库操作的语法为：DROPDATABASE160 3.8.2基本表的定义创建数据库后，就可在数据库中建立基本表。通过将基本表与逻辑设备相关联，使得一个基本表可以放在一个物理文件上，也可以放在多个物理文件上。SQL中的基本数据类型是：整型：int(4B)，smallint(2B)，tinyint(1B)；实型：float，real(4B)，decimal(p,n)，numeric(p,n)；字符型：char(n)，varchar(n)，text；2进制型：binary(n)，varbinary(n)，image；逻辑形：bit，只能取0和1，不允许为空；货币型：money(8B,4位小数)，smallmoney(4B,2位小数)；时间型：datetime(4B,从1753.1.1开始)，smalldatetime(4B,从1900.1.1开始)其中：image为存储图象的数据类型，text存放大文本数据161 3.8.2基本表的定义创建基本表当创建了一个基本表，与该基本表相关的描述信息会存入到系统的数据字典中。创建基本表操作的语法为：CREATETABLE([default][NULL/NOTNULL], [default][NULL/NOTNULL],……[CONSTRAINT{UNIQUE/PRIMARYKEY}([,…][ON])],[CONSTRAINTFOREIGNKEY([,…])],[REFERENCE[.owner.]([,…])],...)ON)162 3.8.2基本表的定义其中：：新表的名称，最多可包含128个字符；：表中的列名，在表内唯一；：指定列的数据类型；default：为列设置缺省值，属于可选项；NULL/NOTNULL：为列设置是否允许为空值，属于可选项；：定义约束的名字，属于可选项；UNIQUE：建立唯一索引；PRIMARYKEY：建立主码；FOREIGNKEY：建立外码；ON：将对象放在指定的逻辑设备上该逻辑设备必须是在创建数据库时定义的或者使用数据库的修改命令加入到数据库中的逻辑设备缺省该项时自动将对象建立在主逻辑设备上163 3.8.2基本表的定义建议：最好不要将用户对象建立在主逻辑设备上，因为主逻辑设备存放了系统数据。[例3.66]建立学生成绩管理数据库中的4张表。CREATETABLECourse(--定义课程表CoursecourseNochar(3)NOTNULL,--课程号courseNamevarchar(30)NOTNULL,--课程名creditHournumeric(1)default0NOTNULL,--学分courseHourtinyintdefault0NOTNULL,--课时数priorCoursechar(3)NULL,--先修课程CONSTRAINTCoursePKPRIMARYKEY(courseNo),FOREIGNKEY(priorCourse)REFERENCESCourse(courseNo))164 3.8.2基本表的定义CREATETABLEClass(--定义班级表ClassclassNochar(6)NOTNULL,--班级号classNamevarchar(30)NOTNULL,--班级名institutevarchar(30)NOTNULL,--所属学院gradesmallintdefault0NOTNULL,--年级classNumtinyintNULL,--班级人数CONSTRAINTClassPKPRIMARYKEY(classNo))165 3.8.2基本表的定义CREATETABLEStudent(--定义学生表StudentstudentNochar(7)NOTNULL--学号studentNamevarchar(20)NOTNULL,--姓名sexchar(2)NULL,--性别birthdaydatetimeNULL,--出生日期nativevarchar(20)NULL,--籍贯nationvarchar(30)default"汉族"NULL,--民族classNochar(6)NULL--所属班级CONSTRAINTStudentPKPRIMARYKEY(studentNo),CONSTRAINTStudentFKFOREIGNKEY(classNo)REFERENCESClass(classNo))166 3.8.2基本表的定义CREATETABLEScore(--定义成绩表ScorestudentNochar(7)NOTNULL,--学号courseNochar(3)NOTNULL,--课程号scorenumeric(5,1)default0NOTNULL--成绩CHECK(scoreBETWEEN0.0AND100.0)/*主码由两个属性构成，必须作为表级完整性约束进行定义*/CONSTRAINTScorePKPRIMARYKEY(studentNo,courseNo),/*表级完整性约束条件，studentNo是外码，被参照表是Student*/CONSTRAINTScoreFK1FOREIGNKEY(studentNo)REFERENCESstudent(studentNo),/*表级完整性约束条件，courseNo是外码，被参照表是Course*/CONSTRAINTScoreFK2FOREIGNKEY(courseNo)REFERENCEScourse(courseNo))167 3.8.2基本表的定义上述四张表的建立都缺省了ONfilegroup_name，数据存放在主逻辑设备上。[例3.67]在MyTempDB数据库中建立TempTable表，存放在TempBakDev逻辑设备上CREATETABLETempTable(xnochar(3)NOTNULL,xnamevarchar(2)NOTNULL,PRIMARYKEY(xno))ONTempBakDev168 3.8.2基本表的定义基本表的修改通过ALTERTABLE命令来修改表的结构，如扩充列等。修改基本表操作的语法为（为要修改的表名）：增加列（新增一列的值为空值）：ALTERTABLEADD增加约束：ALTERTABLEADDCONSTRAINT删除约束：ALTERTABLEDROP修改列的数据类型：ALTERTABLEALTERCOLUMN169 3.8.2基本表的定义[例3.68]在MyTempDB数据库中为TempTable表增加一列。ALTERTABLETempTableADDxsexintdefault0[例3.69]在MyTempDB数据库中为TempTable表的xname列修改数据类型。ALTERTABLETempTableALTERCOLUMNxnamechar(10)[例3.70]在MyTempDB数据库中为TempTable表的xname列增加唯一约束ALTERTABLETempTableADDCONSTRAINTUniqueXnameUNIQUE(xname)注意：基本表在修改过程中，不可以删除列，一次仅执行一种操作。170 3.8.2基本表的定义基本表的删除删除基本表操作的语法为：DROPTABLE[RESTRICT|CASCADE]为被删除的表名若选择RESTRICT，则该表的删除有限制条件即该表不能有视图、触发器以及被其它表所引用（如检查约束CHECK，外码约束FOREIGNKEY），该项为缺省项。若选择CASCADE，则该表的删除没有限制条件在删除基本表的同时，也删除建立在该表上的所有索引、完整性规则、触发器和视图。删除表时，系统会同时从系统的数据字典中将该表的描述一起删除。171 3.8.2基本表的定义[例3.71]删除TempTable表DROPTABLETempTable注意：SQLServer不支持[RESTRICT|CASCADE]选项，其删除的限制条件是在创建基本表时定义的，详见第8章8.2节的数据库完整性。172 3.8.3索引的定义索引是加快数据检索的一种工具一张表可以建立多个索引，可从不同的角度加快查询速度；如果索引建立得较多，会给数据维护带来较大的系统开销。索引是由<搜索码值，指针>的记录构成索引逻辑上按照搜索码值进行排序，但不改变表中记录的物理顺序；索引和基本表分别存储。如在班级表中按所属学院建立的索引InstituteIdx，它与Class表之间的关系可以用图3-30来表示：173 3.8.3索引的定义数据库的索引一般按照B+树结构来组织，但也有Hash索引和位图索引等。索引的类型有聚集或非聚集两种，非聚集索引就是普通索引，一张表可以建立多个普通索引。每张表仅能建立一个聚集索引聚集索引是按搜索码值的某种顺序(升序/降序)来重新组织表记录即索引的顺序就是表记录存放的顺序聚集索引可以极大地提高查询速度，但是给数据的修改带来困难一般建立了聚集索引的表不进行更新操作，仅执行查询操作，这在数据仓库中使用得较多。创建索引后，与该索引相关的描述信息会保存到数据字典中去。174 3.8.3索引的定义索引的建立建立索引操作的语法为：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXON([ASC|DESC],[ASC|DESC],…)[ON]其中：UNIQUE：表示建立唯一索引；CLUSTERED|NONCLUSTERED：表示建立聚集或非聚集索引，默认为非聚集索引；：索引的名称，索引是数据库中的对象，因此在一个数据库中必须唯一；([ASC|DESC],[ASC|DESC],…)：指出为哪个表的哪些属性建立索引[ASC|DESC]为按升序还是降序建立索引，默认为升序；175 3.8.3索引的定义ON：指定索引文件存放在哪个逻辑设备上，该逻辑设备必须是在创建数据库时定义的，或加入到数据库中的逻辑设备。缺省该项时自动将对象建立在主逻辑设备上。[例3.72]在班级表中按所属学院建立一个非聚集索引InstituteIdxCREATENONCLUSTEREDINDEXInstituteIdxONClass(institute)[例3.73]在学生表中，首先按班级编号的升序，然后按出生日期的降序建立一个非聚集索引ClassBirthIdx。CREATEINDEXClassBirthIdxONStudent(classNo,birthdayDESC)176 3.8.3索引的定义索引的删除索引一旦建立，用户不需要管理它，由系统自动维护；可删除那些不经常使用的索引；删除索引操作的语法为：DROPINDEX删除索引时，系统会同时从系统的数据字典中将该索引的描述一起删除。[例3.74]删除InstituteIdx索引。DROPINDEXInstituteIdx177 目录嵌套子查询3.7SQL概述3.1简单查询3.3连接查询3.53.8聚合查询集合运算SQL查询一般格式SQL数据定义语言SQL数据更新语言视图3.23.43.63.93.10178 3.9　SQL数据更新语言SQL数据更新语句包括三条：插入INSERT、删除DELETE和修改UPDATE。3.9.1插入数据3.9.2删除数据3.9.3修改数据179 3.9.1插入数据插入方式有两种：一是插入单条记录，二是插入子查询的结果。后者是一次插入多条记录。插入一条元组：INSERTINTO[([,...])]VALUES([,...]])功能：将新元组插入到指定的表中。：要插入记录的表名；[[,...]]：指明被插入的元组按,,...指定的属性名称和顺序插入元组数据到表中去。该项可以省略。若省略，表示必须按照表的属性个数和属性顺序插入新元组。180 3.9.1插入数据[,...]]：指明被插入元组的具体属性值。值的个数和顺序必须与[[,...]]相对应;对于表中的属性没有在[[,...]]中出现的属性列，系统自动取空值；注意，如果不在[[,...]]中出现的属性被定义为非空值，则该插入语句会报错。[例3.75]将一个新学生元组（‘0700006’,‘李相东’,‘男’,‘1991-10-2100:00’,‘云南’,‘撒呢族’,‘CS0701’）插入到学生表Student中。INSERTINTOStudentVALUES("0700006","李相东","男","1991-10-2100:00","云南","撒呢族","CS0701")本例表名Student后没有指定列名，表示按照Student表定义的属性列的个数和顺序将新元组插入到Student表中。181 3.9.1插入数据[例3.76]将一个新学生元组（姓名：章李立，出生日期：1991-10-1200:00，学号：0700007）插入到学生表Student中。INSERTINTOStudent(studentName,birthday,studentNo)VALUES("章李立","1991-10-1200:00","0700007")本例按照指定列的顺序和列的个数向学生表Student插入一个新元组，没有列出的属性列自动取空值；插入新元组时，数据的组织可不按照表结构定义的属性个数和顺序进行插入。182 3.9.1插入数据插入多条元组：INSERTINTO[([,...])]其中：：要插入记录的表名；[[,...]]：指明被插入的元组按,,...指定的属性名称和顺序插入到中；该项可以省略，若省略则其查询出来的结果必须与表结构相同；：由SELECT语句引出的一个查询。183 3.9.1插入数据[例3.77]将少数民族同学的选课信息插入到StudentNation表中。首先创建表StudentNation：CREATETABLEStudentNation(studentNochar(7)NOTNULL,--学号courseNochar(3)NOTNULL,--课程号scorenumeric(5,1)default0NOTNULL--成绩CHECK(scoreBETWEEN0.0AND100.0)CONSTRAINTStudentNationPKPRIMARYKEY(studentNo,courseNo))然后执行如下插入语句：INSERTINTOStudentNationSELECT*FROMScoreWHEREstudentNoIN(SELECTstudentNoFROMStudentWHEREnation<>"汉族")184 3.9.1插入数据[例3.78]将汉族同学的选课信息插入到StudentNation表中。INSERTINTOStudentNation(studentNo,courseNo)SELECTstudentNo,courseNoFROMScoreWHEREstudentNoIN(SELECTstudentNoFROMStudentWHEREnation="汉族")该查询仅将汉族同学的学号和课程号插入到StudentNation表中；成绩列自动取0值，StudentNation表在定义该列时其默认值为0。185 3.9.2删除数据删除命令：DELETEFROM[WHERE]：要删除记录的表名；[WHERE]：指出被删除的记录所满足的条件该项可以省略，若省略则表示删除表中的所有记录；WHERE子句中可以包含子查询。[例3.79]删除学号为0800001同学的选课记录。DELETEFROMScoreWHEREstudentNo="0800001"186 3.9.2删除数据[例3.80]删除选修了“高等数学”课程的选课记录DELETEFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseName="高等数学")[例3.81]删除平均分在60分到70分之间的同学选课记录DELETEFROMScoreWHEREstudentNoIN(SELECTstudentNoFROMScoreGROUPBYstudentNoHAVINGavg(score)BETWEEN60AND70)187 3.9.3修改数据修改数据命令：UPDATESET=[,=...][FROM[AS][][,[AS][]...][WHERE]其中：：要进行修改记录的表名；SET=[,=...]:用表达式的值替代属性列的值一次可以修改元组的多个属性列，之间以逗号分隔；[WHERE]：指出被修改的记录所满足的条件该项可以省略，若省略，表示修改表中的所有记录；WHERE子句中可以包含子查询。188 3.9.3修改数据[例3.82]将刘方晨同学选修的005课程的成绩改为88分。UPDATEScoreSETscore=88WHEREcourseNo="005"ANDstudentNoIN(SELECTstudentNoFROMStudentWHEREstudentName="刘方晨")也可以写成：UPDATEScoreSETscore=88FROMScorea,StudentbWHEREcourseNo="005"ANDa.studentNo=b.studentNoANDstudentName="刘方晨"189 3.9.3修改数据[例3.83]将注册会计08_02班的男同学的成绩增加5分。UPDATEScoreSETscore=score+5FROMScorea,Studentb,ClasscWHEREa.studentNo=b.studentNoANDb.classNo=c.classNoANDclassName="注册会计08_02班"ANDsex="男"[例3.84]将学号为0800001同学的出生日期修改为1992年5月6日出生，籍贯修改为福州。UPDATEStudentSETbirthday="1992-5-600:00",native="福州"WHEREstudentNo="0800001"注意：插入、删除和修改操作会破坏数据的完整性，如果违反了完整性约束条件，其操作会失败。190 3.9.3修改数据[例3.85]将每个班级的学生人数填入到班级表的ClassNum列中。UPDATEClassSETclassNum=sCountFROMClassa,(SELECTclassNo,count(*)sCountFROMStudentGROUPBYclassNo)bWHEREa.classNo=b.classNo191 目录嵌套子查询3.7SQL概述3.1简单查询3.3连接查询3.53.8聚合查询集合运算SQL查询一般格式SQL数据定义语言SQL数据更新语言视图3.23.43.63.93.10192 3.10视图视图是虚表，是从一个或几个基本表(或视图)中导出的表。在系统的数据字典中仅存放视图的定义，不存放视图对应的数据。当基本表中的数据发生变化时，从视图中查询出的数据也随之改变。视图实现了数据库管理系统三级模式中的外模式。基于视图的操作包括：查询、删除、受限更新和定义基于该视图的新视图。视图的主要作用是：简化用户的操作；使用户能以多种角度看待同一数据；对重构数据库提供了一定程度的逻辑独立性；能够对机密数据提供安全保护；适当的利用视图可以更清晰的表达查询。193 3.10视图3.10.1定义视图3.10.2查询视图3.10.3更新视图3.10.4删除视图194 3.10.1定义视图定义视图：CREATEVIEW[,,...]AS[WITHCHECKOPTION]其中：：新建视图的名称，该名称在数据库中唯一；[,,...]：视图定义的列名，列名可以省略不写，列名自动取查询出来的列名，但是属于下列三种情况必须写列名：某个目标列是集函数或表达式；多表连接中有相同的列名；在视图中为某列取新的名称更合适。AS：子查询不允许含有ORDERBY子句和DISTINCT短语；[WITHCHECKOPTION]：当对视图进行插入、删除和更新操作时必须满足视图定义的谓词条件（子查询中的条件表达式）。195 3.10.1定义视图执行CREATEVIEW语句时只把视图定义存入数据字典中，并不执行其中的SELECT语句[例3.86]创建仅包含1991年出生的学生视图StudentView1991。CREATEVIEWStudentView1991ASSELECT*FROMStudentWHEREyear(birthday)=1991本例省略了视图的列名，自动取查询出来的列名。本例没有使用WITHCHECKOPTION选项，下面的语句可以执行：INSERTINTOStudentView1991VALUES("0700008","李相东","男","1992-10-2100:00","云南","撒呢族","CS0701")但是，对视图StudentView1991的查询不能查询出刚刚插入的记录。196 3.10.1定义视图[例3.87]创建仅包含1991年出生的学生视图StudentView1991Chk，并要求进行修改和插入操作时仍需保证该视图只有91年出生的学生。CREATEVIEWStudentView1991ChkASSELECT*FROMStudentWHEREyear(birthday)=1991WITHCHECKOPTION本例建立的视图StudentView1991Chk，其更新操作必须满足:修改操作：自动加上year(birthday)=1991的条件；删除操作：自动加上year(birthday)=1991的条件；插入操作：自动检查birthday属性值是否满足为1991年出生，如果不是，则拒绝该插入操作。197 3.10.1定义视图本例使用了WITHCHECKOPTION选项，下面的插入语句可以执行：INSERTINTOStudentView1991ChkVALUES("0700008","李相东","男","1991-10-2100:00","云南","撒呢族","CS0701")下面的插入语句不可以执行：INSERTINTOStudentView1991ChkVALUES("0700009","李相东","男","1992-10-2100:00","云南","撒呢族","CS0701")原因是插入的出生日期违反了出生日期必须为1991年。当视图是基于一张表，且保留了主码属性，这样的视图称为行列子集视图。视图可以建立在一张表上，也可以建立在多张表上。198 3.10.1定义视图[例3.88]创建一个包含学生学号、姓名、课程名、获得的学分和相应成绩的视图ScoreView。由于成绩必须大于等于60分才获得学分，该视图必须含有该条件。CREATEVIEWScoreViewASSELECTa.studentNo,studentName,courseName,creditHour,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDscore>=60199 3.10.1定义视图[例3.89]创建一个包含每门课程的课程编号、课程名称、选课人数和选课平均成绩的视图SourceView。CREATEVIEWSourceView(courseNo,courseName,courseCount,courseAvg)ASSELECTa.courseNo,courseName,count(*),avg(score)FROMCoursea,ScorebWHEREa.courseNo=b.courseNoGROUPBYa.courseNo,courseName本例使用聚合函数，必须为视图的属性命名，可在视图名的后面直接给出列名，也可用下面的语句替代：CREATEVIEWSourceView1ASSELECTa.courseNo,courseName,count(*)courseCount,avg(Score)courseAvgFROMCoursea,ScorebWHEREa.courseNo=b.courseNoGROUPBYa.courseNo,courseName200 3.10.1定义视图视图也可以建立在视图上。[例3.90]创建一个包含每门课程的课程编号、课程名称、选课人数和选课平均成绩的视图SourceView1，要求该视图选课人数必须在5人以上。CREATEVIEWSourceView1ASSELECT*FROMSourceViewWHEREcourseCount>=5在设计表结构时，为减少数据的冗余存放，往往仅存放基本数据凡是可以由基本数据导出的数据，在基本表中一般不存储；如在学生Student表中没有存放年龄，但可建立一个包含年龄属性的视图，这样的视图称为带表达式的视图。201 3.10.1定义视图[例3.91]创建一个包含学生学号、姓名和年龄的视图StudentAgeView。CREATEVIEWStudentAgeViewASSELECTstudentNo,studentName,year(getdate())-year(birthday)ageFROMStudent202 3.10.2查询视图查询是对视图进行的最主要的操作。从用户的角度来看，查询视图与查询基本表的方式是完全一样的。从系统的角度来看，查询视图的过程是：进行有效性检查，检查查询中涉及的表和视图是否存在；从数据字典中取出视图的定义，将视图定义的子查询与用户的查询结合起来，转换成等价的对基本表的查询；执行修正后的查询。203 3.10.2查询视图[例3.92]在StudentView1991中查询CS0701班同学的信息。SELECT*FROMStudentView1991WHEREclassNo="CS0701"对于该查询：系统首先进行有效性检查，判断视图StudentView1991是否存在；如果存在，则从系统的数据字典中取出该视图的定义；将定义中的子查询与用户的查询结合起来，转换为基于表的查询，即将视图StudentView1991的定义转换该查询为：SELECT*FROMStudentWHEREyear(birthday)=1991ANDclassNo="CS0701"然后系统执行修正后的查询。204 3.10.2查询视图[例3.93]在视图SourceView中查询平均成绩在80分以上的课程信息。SELECT*FROMSourceViewWHEREcourseAvg>=80视图SourceView是一个基于聚合运算的视图，列是courseAvg，它是经过聚合函数运算的值。由于在WHERE子句中，不允许对聚合函数进行运算，不可能转换为如下的查询：SELECTa.courseNo,courseName,count(*)courseCount,avg(Score)courseAvgFROMCoursea,ScorebWHEREa.courseNo=b.courseNoANDcourseAvg>=80GROUPBYa.courseNo,courseName205 3.10.2查询视图HAVING子句可以对聚合函数直接作用，系统会将该查询转换为如下的形式：SELECTa.courseNo,courseName,count(*)courseCount,avg(Score)courseAvgFROMCoursea,ScorebWHEREa.courseNo=b.courseNoGROUPBYa.courseNo,courseNameHAVINGavg(score)>=80[例3.94]在视图SourceView和课程Course表中查询课程平均成绩在75分以上的课程编号、课程名称、课程平均成绩和学分。SELECTa.courseNo,a.courseName,courseAvg,creditHourFROMCoursea,SourceVIEWbWHEREa.courseNo=b.courseNoANDcourseAvg>=75206 3.10.3更新视图更新视图指通过视图来插入、删除和修改基本表中的数据。视图不实际存放数据，对视图的更新，最终要转换为对基本表的更新。如果视图的定义中包含了表达式，或聚合运算，或消除重复值运算，则不能对视图进行更新操作。对视图进行更新操作，其限制条件比较多建立视图的作用不是利用视图来更新数据库中的数据，而是简化用户的查询；达到一定程度的安全性保护；尽量不要对视图执行更新操作。207 3.10.3更新视图[例3.95]在StudentView1991中，将学号为"0800004"同学的名字修改为"张小立"。UPDATEStudentView1991SETstudentName="张小立"WHEREstudentNo="0800004"对于该操作：系统首先进行有效性检查，判断视图StudentView1991是否存在;如果存在，则从系统的数据字典中取出该视图的定义；将定义中的子查询与用户的查询结合起来，转换为基于基本表的修改：UPDATEStudentSETstudentName="张小立"WHEREyear(birthday)=1991ANDstudentNo="0800004"208 3.10.3更新视图[例3.96]在视图StudentView1991中将学号为"0800004"同学的出生年份由1991年修改为1992年。UPDATEStudentView1991SETbirthday="1992-05-2000:00:00.000"WHEREstudentNo="0800004"[例3.97]在视图StudentView1991Chk中不能将出生年份修改为1992年的，因为该视图对修改操作进行了检查。对视图的插入操作见例3.87和例3.88。209 3.10.3更新视图[例3.98]在视图StudentView1991中将学号为"0800006"的同学记录删除。DELETEFROMStudentView1991WHEREstudentNo="0800006"系统将该操作转化为如下的操作：DELETEFROMStudentWHEREyear(birthday)=1991ANDstudentNo="0800006"210 3.10.3更新视图[例3.99]在视图SourceView中删除平均成绩大于80分的课程记录。DELETEFROMSourceViewWHEREcourseAvg>=80该操作数据库管理系统拒绝执行因为视图SourceView包含了聚合运算，系统无法将该视图转化为对基本表的操作。一般来讲，如果是行列子集视图，则可以对该视图进行更新操作；其它类型的视图，具体的数据库系统有具体的定义，一般不对其进行更新操作。211 3.10.4删除视图删除视图：DROPVIEW[CASCADE]其中，CASCADE为可选项，选择表示级联删除。该语句从数据字典中删除指定的视图定义如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除；删除基本表时，由该基本表导出的所有视图定义都必须显式地使用DROPview语句删除。[例3.100]删除视图及级联视图。删除视图StudentView1991：DROPVIEWStudentView1991级联删除视图SourceView：DROPVIEWSourceVIEWCASCADE212 本章结束！请同学们对本章内容进行复习、总结！！！213 敞开心扉出佳作万全区第二初级中学韩丽祥 心理描写方法 所谓心理描写，就是对人物内心的思想活动、情感活动进行描写。心理描写能反映人物的思想性格，展示人物的内心世界。 以描写一名学生在老师宣布考试成绩时的心理活动为例，可以用以下几种方法。例一：内心独白我不停地在心里念叨：阿弥陀佛，大慈大悲的观世音菩萨，保佑我吧！我再也不踢球了，不看电视，不打游戏机了。唉！都怪我自己，老想着打游戏机，考试前一天还趁父母不在家偷看了一个小时的电视。老师啊，手下留情，你让我过了这一关，我以后上课一定好好听讲，千万千万别让我不及格啊！例二：幻觉烘托我好像看见满试卷鲜红的叉组成一张巨大的网向我卷来，把我网住，使我不能动弹，不能挣扎。我又仿佛看到了老师满面的怒容，仿佛听到了父母失望的叹息声和旁人的嘲笑声。 例三：环境烘托天阴沉沉的，不时刮来阵阵冷风。风刮到我身上，我就不由自主地打颤。教室里静悄悄的，只听见“沙沙”的发试卷的声音，“哗啦！”我的心随之猛跳了一下，一个同学不小心把书碰到了地下。同桌的试卷已发下来了，72分，看着同桌哭丧的脸，我不由得心里直打鼓。例四：修辞描写我极想打开试卷，可又怕自己看见可怕的结果，就像一个渴睡的人，极想揭开床上的毛毯，又怕钻出一条响尾蛇一样。 例五：言行烘托试卷静静地反躺在桌上。我用有点颤抖的手去慢慢地掀开试卷一角，一个鲜红的“4”字映入我的眼帘，我的手一抖，试卷又合上了。我一咬牙，把手伸到试卷底下，用力一翻，随着“啪”的一声，我看到了我的分数──48，可怜的“48”，我“唉”了一声便瘫在桌上。 今天能够站在这里给大家讲课，我的心情一直不平静。想到这是一次很好的学习锻炼的机会，我很高兴，同时又有些担心：这堂课我能上好吗？最后我终于有了信心。组卷网心明眼亮评例文 今天真是一个难得的好天气。清晨，太阳刚刚从东方升起，我就起了床。马上就要去上课了，我的心情一直不平静。走在宽阔的马路上，路两旁的梧桐树伸开宽大的手掌，仿佛在向我招手，给我鼓励，给我加油；一幢一幢的高楼巍然屹立，那份泰然，那份坚毅，仿佛给我注入了无穷的力量，坚定了我的信心。看到来自全县各个学校的老师有说有笑地进入课堂，我陷入了沉思：能有这么一次学习锻炼的机会，我真幸运呀！面对那么多的同行、前辈、领导，这堂课我能上好吗？我的学生会不会怯场呢？这堂课该不会变成我的独角戏吧？当我看到你们那朝气蓬勃的样子，我的心情渐渐趋于平静：要相信学生们！我的学生一定会非常棒的！一定会和我一起努力的！会的，一定会的！我相信你们！ 我独自走在回家的路上，……仰望夜空，渐渐地，视线模糊了：难道初中三年的友谊就这样结束了？你能补充一些景物描写（如：用路灯、树、星星、月亮等）来烘托人物此时的心情吗？请说几句话。心领神会摹内心 例文欣赏我独自走在回家的路上，白天那熙熙攘攘的人群都到哪里去了？那往日温馨的路灯，此时发出的只有清冷的光芒；星星三三两两都隐去了身影；只有那一弯瘦瘦的新月，正孤独地挂在天上，凉凉的月光笼罩着大地上的一切，使我感到浑身发抖。仰望夜空，渐渐地，我的视线模糊了：难道初中三年的友谊就这样结束了？ 耶！我们赢了！赛场上顿时成了我们的天下。……感情的潮水在奔流，欢乐的海洋在澎湃。要求：请你用动作、语言、神态等刻画人物心理的方法说几句话。 耶！我们赢了！赛场上顿时成了我们的天下。同学们有的满场奔跑，有的手舞足蹈，有的用力挥舞着衣服，脸上洋溢着胜利的笑意；还有的同学紧紧抱在一起，有的甚至把同学抛起来，脸上绽放着胜利的笑容……感情的潮水在奔流，欢乐的海洋在澎湃。 心理世界似天空，多姿多彩幻无穷。景物描写来烘托，内心独白抒心胸。语言神态和动作，回忆联想加梦境。若能综合来运用，心理描写定成功！总结心理描写方法 成长的过程是多姿多彩的。同学们：还记得那次与父母激烈的冲撞吗？还记得那次与老师倾心的交谈吗？还记得那次获得成功后的喜悦吗？还记得那次被好友误解的委屈吗？请拿起手中的笔，把欢乐与喜悦、馨香与甘甜、愁苦与烦闷、失落与茫然，诉诸笔端吧。要求：把你最难忘的一段心理活动写成段，抒真情，写实感。心有灵犀出佳作 畅所欲言来互评'