数据库技术及应用第3单元2

第三单元

Oracle与SQL语言宋佳兴计算机系网络所jxsong@主要内容Oracle系统简介Oracle系统结构Oracle系统安装SQL语言概述SQL语言详解Page2SQL语言详解SQL数据定义SQL数据查询单表查询连接查询嵌套查询集合查询SQL数据更新SQL视图操作SQL数据控制Page3SQL数据定义如要对数据进行查询、添加、修改等各项操作，则应先运用SQL数据定义语言定义基本表、索引和视图。SQL数据定义用到的命令主要有三个：CREATE（创建）、DROP（删除）、ALTER（修改），索引依赖于基本表，视图也产生于基本表，因此SQL通常不提供修改索引和视图的操作，用户如果想修改这两个部分，只能先删再建。而对于存放数据对象的整个数据库创建来说，使用SQL语句比使用某一数据库系统的管理器要复杂，因而一般情况下都使用管理器进行创建。有的数据库系统在安装时已经创建了一个缺省的数据库，只需对其参数进行适当调整即可。Page4SQL数据定义Oracle常用数据类型（详细数据类型见参考资料）Page5类型含义CHAR(length)存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。VARCHAR2(length)存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字节。NUMBER(p,s)既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p默认是38位），s是指小数位数。DATE存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。TIMESTAMP不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区。CLOB存储大的文本，比如存储非结构化的XML文档等。最大4GBBLOB存储二进制对象，如图形、视频、声音等。最大4GBSQL数据定义（基本表）创建基本表（CreateTable）CREATETABLE<表名>(<列名><数据类型>[<列级完整性约束条件>][,<列名><数据类型>[<列级完整性约束条件>]]…[,<表级完整性约束条件>]);<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件Page6SQL数据定义（基本表）创建基本表（CreateTable）列级完整性约束NULL|NOTNULL：允许取空值|不允许取空值。DEFAULT<值>：指定默认值。UNIQUE：唯一性约束，取值不能重复PRIMARYKEY：声明为主键FOREIGNKEY：参照完整性的外键约束。CHECK子句：实现用户定义完整性约束。表级完整性约束UNIQUE：唯一性约束，取值不能重复PRIMARYKEY：声明为主键。FOREIGNKEY：参照完整性的外键约束。CHECK子句：实现用户定义完整性约束。Page7SQL数据定义（基本表）建立学生成绩管理数据库ScoreDB中的4张表CREATETABLECourse(--定义课程表CoursecourseNochar(3)NOTNULL,--课程号

courseNamevarchar2(30)NOTNULL,--课程名

creditHournumber(1)default0NOTNULL,--学分

courseHournumber(3)default0NOTNULL,--课时数

priorCoursechar(3)NULL,--先修课程

CONSTRAINTCoursePKPRIMARYKEY(courseNo),CONSTRAINTCourseFKFOREIGNKEY(priorCourse)REFERENCESCourse(courseNo));Page8SQL数据定义（基本表）建立学生成绩管理数据库ScoreDB中的4张表CREATETABLEClass(--定义班级表ClassclassNochar(6)

NOTNULL,

--班级号

classNamevarchar2(30)

NOTNULL,

--班级名

institutevarchar2(30)

NOTNULL,

--所属学院

gradenumber(4)default0

NOTNULL,--年级

classNumnumber(3)

NULL,

--班级人数

CONSTRAINTClassPKPRIMARYKEY(classNo));Page9SQL数据定义（基本表）建立学生成绩管理数据库ScoreDB中的4张表CREATETABLEStudent(--定义学生表Student

studentNochar(7)

NOTNULL,--学号

studentNamevarchar2(20)

NOTNULL,--姓名

sexchar(2)

NULL,--性别

birthdaydateNULL,--出生日期

nativevarchar2(20)

NULL,--籍贯

nationvarchar2(30)default'汉族'NULL,

--民族

classNochar(6)

NULL,--所属班级

CONSTRAINTStudentPKPRIMARYKEY(studentNo),

CONSTRAINTStudentFKFOREIGNKEY(classNo)REFERENCESClass(classNo));Page10SQL数据定义（基本表）建立学生成绩管理数据库ScoreDB中的4张表CREATETABLEScore(--定义成绩表ScorestudentNochar(7)

NOTNULL,--学号

courseNochar(3)

NOTNULL,--课程号

scorenumber(5,1)default0

NOTNULL,--成绩

CONSTRAINTScoreCKCHECK(scoreBETWEEN0.0AND100.0),

/*主码由两个属性构成，必须作为表级完整性约束进行定义*/CONSTRAINTScorePKPRIMARYKEY(studentNo,courseNo),

/*表级完整性约束条件，studentNo是外码，被参照表是Student*/CONSTRAINTScoreFK1FOREIGNKEY(studentNo)REFERENCESstudent(studentNo),

/*表级完整性约束条件，courseNo是外码，被参照表是Course*/CONSTRAINTScoreFK2FOREIGNKEY(courseNo)REFERENCEScourse(courseNo));Page11SQL数据定义（基本表）修改基本表（AlterTable）ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][MODIFY<列名><数据类型>];<表名>：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件MODIFY子句：用于修改列名和数据类型Page12SQL数据定义（基本表）修改基本表（AlterTable）建立一个测试用的临时表CREATETABLETempTable(xnochar(3)NOTNULL,xnamevarchar2(2)NOTNULL,CONSTRAINTTempPKPRIMARYKEY(xno));Page13SQL数据定义（基本表）修改基本表（AlterTable）【例】为TempTable表增加一列。ALTERTABLETempTableADDxsexnumber(1)default0;【例】为TempTable表的xname列修改数据类型。ALTERTABLETempTableMODIFYxnamevarchar2(10);【例】为TempTable表的xname列增加唯一约束。ALTERTABLETempTableADDCONSTRAINTUniqueXnameUNIQUE(xname);Page14SQL数据定义（基本表）删除基本表（DropTable）当某个基本表不再需要时，应当及时删除，以节省空间和减少出错等问题。DROPTABLE<表名>基表一旦删除，表中的数据、该表上建立的索引都将自动被删除。另外，有的系统如Oracle，删除基表后建立在该表上的视图仍然保留在数据字典中，但用户引用时就出错，所以还需手动删除相应所有视图。【例】删除TempTable表DROPTABLETempTable;Page15SQL数据定义（索引）建立索引是加快查询速度的有效手段建立索引是一个数据库所必须的建立索引DBA或表的属主（建表人）根据需要建立有些DBMS自动建立以下属性列上的索引PRIMARYKEYUNIQUE维护索引

DBMS自动完成

使用索引

DBMS自动选择是否使用索引以及使用哪些索引Page16SQL数据定义（索引）创建索引（CreateIndex）CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…);用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明要建立的索引是唯一索引CLUSTER表示要建立的索引是聚簇索引Page17SQL数据定义（索引）创建索引（CreateIndex）UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表明要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。用户可以在最常查询的字段上建立聚簇索引以提高查询效率。显然在一个基表上最多只能建立一个聚簇索引。建立后，更新索引字段数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的不宜建立聚簇索引。Page18SQL数据定义（索引）创建索引（CreateIndex）【例】在班级表中按所属学院建立一个非聚集索引InstituteIdx。CREATEINDEXInstituteIdxONClass(institute);【例】在学生表中，首先按班级编号的升序，然后按出生日期的降序建立一个非聚集索引ClassBirthIdx。CREATEINDEXClassBirthIdxONStudent(classNo,birthdayDESC);Page19SQL数据定义（索引）删除索引（DropIndex）可删除那些不经常使用的索引删除索引操作的语法为：DROPINDEX<索引名>

【例】删除InstituteIdx索引。DROPINDEXInstituteIdx;Page20SQL数据定义（视图）创建视图（CreateView）删除视图（DropView）

创建视图需要用到SQL数据查询，本部分内容放到SQL数据查询之后在详细介绍。Page21SQL语言详解SQL数据定义SQL数据查询单表查询连接查询嵌套查询集合查询SQL数据更新SQL视图操作SQL数据控制Page22SQL数据查询SQL数据查询（Select）SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…FROM<表名或视图名>[,<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]];Page23SQL数据查询SQL数据查询（Select）SELECT子句：指定要显示的属性列FROM子句：指定查询对象（基本表、视图或查询表）WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，属性列值相等的元组为一个组。通常会在每组中使用集函数HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序Page24SQL数据查询单表查询只涉及一个基本表或者视图的查询连接查询涉及两个或者两个以上基本表或者视图的查询嵌套查询将一个子查询嵌套在另一个查询WHERE子句或HAVING短语条件中的一种查询集合查询对多个查询的结果进行集合操作Page25SQL数据查询（实例ScoreDB）Page26SQL数据查询（实例ScoreDB）Page27SQL数据查询（实例ScoreDB）Page28SQL数据查询（实例ScoreDB）Page29SQL语言详解SQL数据定义SQL数据查询单表查询连接查询嵌套查询集合查询SQL数据更新SQL视图操作SQL数据控制Page30SQL数据查询（单表查询）SELECT子句可以实现关系代数中的投影运算查询指定列选取表中的指定列，通过SELECT确定要查询的属性【例】查询所有班级的班级编号、班级名称和所属学院SELECTclassNo,className,instituteFROMClass;Page31SQL数据查询（单表查询）消除重复元组需要消除重复元组，使用DISTINCT关键字【例】查询所有学院的名称SELECTinstituteFROMClass;【例】查询所有学院的名称并消除重复元组SELECTDISTINCTinstituteFROMClass;Page32SQL数据查询（单表查询）查询所有列可使用两种方法：将所有的列在SELECT子句中列出（可以改变列的显示顺序）；使用*号，*表示所有属性，按照表定义时的顺序显示所有属性。【例】查询所有班级的全部信息SELECTclassNo,className,classNum,grade,instituteFROMClass;【例】查询所有班级的全部信息SELECT*

FROMClass;Page33SQL数据查询（单表查询）给属性列取别名可为属性列取一个便于理解的列名，如用中文来显示列名，为属性列取别名特别适合经过计算的列。【例】查询所有班级的所属学院、班级编号和班级名称，要求用中文显示列名SELECTinstitute所属学院,classNo班级编号,className班级名称FROMClass;【例】该查询可使用AS关键字取别名SELECTinstituteAS所属学院,

classNoAS班级编号,

classNameAS班级名称FROMClass;Page34SQL数据查询（单表查询）查询经过计算的列可使用属性、常数、函数和表达式【例】查询每个班级编号、班级名称以及该班级现在为几年级，并将班级编号中大写字母改为小写字母输出SELECTlower(classNo)lowerclassNo,className,To_number(To_char(Sysdate,'YYYY'))-gradeclassgradeFROMClass;Oracle内置函数Sysdate返回系统当前时间函数lower()将大写字母改为小写字母函数To_char()将日期转换为指定格式的字符串函数To_number()将字符串转化为数值Page35SQL数据查询（单表查询）WHERE子句可以实现关系代数中的选择运算WHERE常用的查询条件有：比较运算：>、>=、<、<=、=、<>(或!=)范围查询：BETWEEN...AND集合查询：IN空值查询：ISnull字符匹配查询：LIKE逻辑查询：AND、OR、NOTPage36SQL数据查询（单表查询）比较运算使用比较运算符>、>=、<、<=、=、<>(或!=)

【例】查询2007级的班级编号、班级名称和所属学院。SELECTclassNo,className,instituteFROMClassWHEREgrade=2007;【例】在学生Student表中查询年龄大于或等于22岁的同学学号、姓名和出生日期。SELECTstudentNo,studentName,birthdayFROMStudentWHEREmonths_between(sysdate,birthday)/12>=22;Page37SQL数据查询（单表查询）范围查询BETWEEN...AND用于查询属性值在某一个范围内的元组NOT

BETWEEN...AND用于查询属性值不在某一个范围内的元组BETWEEN后是属性的下限值，AND后是属性的上限值【例】在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩SELECTstudentNo,courseNo,scoreFROMScoreWHEREscoreBETWEEN80AND90;Page38SQL数据查询（单表查询）范围查询BETWEEN...AND用于查询属性值在某一个范围内的元组NOT

BETWEEN...AND用于查询属性值不在某一个范围内的元组BETWEEN后是属性的下限值，AND后是属性的上限值【例】在选课Score表中查询成绩不在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscoreNOTBETWEEN80AND90;Page39SQL数据查询（单表查询）集合查询IN用于查询属性值在某个集合内的元组NOTIN用于查询属性值不在某个集合内的元组

IN后面是集合，可以是具体的集合，也可以是查询出来的元组集合。【例】在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNoIN('001','005','003');Page40SQL数据查询（单表查询）集合查询IN用于查询属性值在某个集合内的元组NOTIN用于查询属性值不在某个集合内的元组

IN后面是集合，可以是具体的集合，也可以是查询出来的元组集合。【例】在学生Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号。SELECTstudentName,native,classNoFROMStudentWHEREnativeNOTIN('南昌','上海');Page41SQL数据查询（单表查询）空值查询空值表示未知或不确定的值，空值表示为NULLISNULL用于查询属性值为空值，IS不能用“=”替代ISNOTNULL用于查询属性值不为空值【例】在课程表中查询先修课程为空值的课程信息。SELECT*FROMCourseWHEREpriorCourseISNULL;【例】在课程表中查询有先修课程的课程信息。SELECT*FROMCourseWHEREpriorCourseISNOTNULL;Page42SQL数据查询（单表查询）字符匹配查询LIKE用于字符匹配查询，语法格式为：[NOT]LIKE<匹配字符串>[ESCAPE<换码字符>]查询的含义是：如果LIKE前没有NOT，则查询指定的属性列值与<匹配字符串>相匹配的元组；如果LIKE前有NOT，则查询指定的属性列值不与<匹配字符串>相匹配的元组。<匹配字符串>可以是一个具体的字符串，也可以包括通配符，符号%表示任意长度的字符串，符号_(下划线)表示任意一个字符。Page43SQL数据查询（单表查询）字符匹配查询LIKE用于字符匹配查询，语法格式为：[NOT]LIKE<匹配字符串>[ESCAPE<换码字符>]字符匹配例子ab%，表示所有以ab开头的任意长度的字符串；zhang%ab，表示以zhang开头，以ab结束，中间可以是任意个字符的字符串。ab_，表示所有以ab开头的3个字符的字符串，其中第3个字符为任意字符；a__b表示所有以a开头，以b结束的4个字符的字符串，且第2、3个字符为任意字符。Page44SQL数据查询（单表查询）字符匹配查询【例】在班级Class表中查询班级名称中含有会计的班级信息。SELECT*FROMClassWHEREclassNameLIKE'%会计%';【例】在学生Student表中查询所有姓王且全名为3个汉字的同学学号和姓名。SELECTstudentNo,studentNameFROMStudentWHEREstudentNameLIKE'王__';【例】在学生Student表中查询名字中不含有“福”的同学学号和姓名。SELECTstudentNo,studentNameFROMStudentWHEREstudentNameNOTLIKE'%福%';Page45SQL数据查询（单表查询）字符匹配查询【例】在学生Student表中查询蒙古族的同学学号和姓名SELECTstudentNo,studentNameFROMStudentWHEREnationLIKE'蒙古族';注意：如果匹配字符串中不含有%和_，则LIKE与比较运算符“=”的查询结果一样，该查询等价于下面查询：SELECTstudentNo,studentNameFROMStudentWHEREnation='蒙古族';Page46SQL数据查询（单表查询）字符匹配查询如果查询字串中本身要包含%和_，必须使用“ESCAPE<换码字符>”短语，对通配符进行转义处理。【例】在班级Class表中查询班级名称中含有“08_”符号的班级名称SELECTclassNameFROMClassWHEREclassNameLIKE'%08\_%'ESCAPE'\';“ESCAPE‘\’”表示\为换码字符，紧跟在\符号后的_不是通配符，而是普通的用户要查询的符号，如果将#字符作为换码字符，则该查询可改写为：SELECTclassNameFROMClassWHEREclassNameLIKE'%08#_%'ESCAPE'#';Page47SQL数据查询（单表查询）逻辑查询SQL提供AND、OR和NOT逻辑运算符分别实现逻辑与、逻辑或和逻辑非运算【例】在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNo='001'ORcourseNo='005'ORcourseNo='003';也可采用集合运算IN实现，本例中采用逻辑“或”运算Page48SQL数据查询（单表查询）逻辑查询【例】在Student表中查询1991年出生且籍贯为“汉族”的同学学号、姓名、出生日期。SELECTstudentNo,studentName,birthdayFROMStudentWHERETo_char(birthday,'YYYY')='1991'ANDnation='汉族';【例】在Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号。SELECTstudentName,native,classNoFROMStudentWHEREnative!='南昌'ANDnative!='上海';Page49SQL数据查询（单表查询）逻辑查询【例】在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore>=80ANDscore<=90;【例】在选课Score表中查询成绩不在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore<80OR

score>90;Page50SQL数据查询（单表查询）排序运算使用ORDERBY子句实现排序运算，其语法为：ORDERBY<表达式1>[ASC|DESC]

[,<表达式2>[ASC|DESC],...]基本说明：<表达式1>,<表达式2>,...可以是属性、函数或表达式缺省按升序(ASC)排序，按降序排序，必须指明DESC选项运算含义：在查询结果中首先按<表达式1>的值进行排序在<表达式1>值相等的情况下再按<表达式2>值排序依此类推Page51SQL数据查询（单表查询）排序运算【例】在学生Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号，并按籍贯的降序排序输出。SELECTstudentName,native,classNoFROMStudentWHEREnative!='南昌'ANDnative!='上海'ORDERBYnativeDESC;【例】在学生Student表中查询“女”学生的学号、姓名、所属班级编号和出生日期，并按班级编号的升序、出生日期的月份降序排序输出。SELECTstudentNo,studentName,classNo,birthdayFROMStudentWHEREsex='女'ORDERBYclassNo,to_char(birthday,'MM')DESC;Page52SQL数据查询（单表查询）查询表FROM子句后面可以是基本表、视图，还可以是查询表【例】查询1991年出生的“女”同学基本信息。可以先将学生表中的女生记录查询出来，然后再对查询表进行选择、投影操作。SELECTstudentNo,studentName,birthdayFROM(SELECT*FROMStudentWHEREsex='女')aWHERETo_char(birthday,'YYYY')='1991';在FROM子句后是一个子查询，表示对子查询的查询结果（查询表）进行查询，必须为查询表取一个名称（称为元组变量），如取名为aPage53SQL数据查询（单表查询）聚合查询SQL查询提供了丰富的数据分类、统计功能统计功能通过聚合函数来实现分类功能通过分组子句来实现统计和分组结合在一起实现丰富的查询功能Page54SQL数据查询（单表查询）聚合函数SQL提供的聚合函数包括：count([DISTINCT|ALL]{*|<列名>})：统计关系的元组个数或一列中值的个数；sum([DISTINCT|ALL]<列名>)：统计一列中值的总和；avg([DISTINCT|ALL]<列名>)：统计一列中值的平均值；max([DISTINCT|ALL]<列名>)：统计一列中值的最大值；min([DISTINCT|ALL]<列名>)：统计一列中值的最小值。指定DISTINCT谓词，表示在计算时首先消除<列名>取重复值的元组，然后再进行统计指定ALL谓词或没有DISTINCT谓词，表示不消除<列名>取重复值的元组Page55SQL数据查询（单表查询）聚合函数【例】查询学生总人数。SELECTcount(*)学生人数FROMStudent;【例】查询所有选课学生的人数。SELECTcount(studentNo)

学生人数FROMScore;查询结果是40，由于一个学生可以选修多门课程，学号存在重复，为消除重复的元组，使用DISTINCT短语，将查询修改为：SELECTcount(DISTINCTstudentNo)学生人数FROMScore;查询结果为10Page56SQL数据查询（单表查询）聚合函数【例】查询学号为“0800005”同学所选修课程成绩的最高分SELECTmax(score)最高分FROMScoreWHEREstudentNo=

'0800005';【例】查询学号为“0800005”同学所选修课程成绩的平均分SELECTavg(score)平均分FROMScoreWHEREstudentNo='0800005';在聚合函数遇到空值时，除count(*)外所有的函数皆跳过空值，只处理非空值。Page57SQL数据查询（单表查询）分组运算在SQL查询中，往往需要对数据进行分组运算，分组运算的目的是为了细化聚合函数的作用对象。如不对查询结果分组，则聚合函数作用于整个查询结果。如对查询结果进行分组，则聚合函数分别作用于每个组，查询结果按组聚合输出。SQL通过GROUPBY和HAVING子句实现分组运算GROUPBY：对查询结果按某一列或某几列进行分组，值相等的分为一组；HAVING：对分组的结果进行选择，仅输出满足条件的组。该子句必须与GROUPBY子句配合使用。Page58SQL数据查询（单表查询）分组运算【例】查询每个同学选课门数、平均分和最高分。SELECTstudentNo,

count(*)门数,

avg(score)平均分,max(score)最高分FROMScoreGROUPBYstudentNo;结果按学号StudentNo分组，将具有相同StudentNo值的元组作为一组，然后对每组进行相应的计数、求平均值和求最大值Page59SQL数据查询（单表查询）分组运算【例】查询平均分在80分以上的每个同学的选课门数、平均分和最高分。SELECTStudentNo,count(*)门数,avg(score)平均分,

max(score)最高分FROMScoreGROUPBYStudentNoHAVINGavg(score)>=80;按学号StudentNo分组，将具有相同StudentNo值的元组作为一组，然后对每组进行相应的计数、求平均值和求最大值，并判断平均值是否大于等于80，如果是则输出该组，否则丢弃该组，不作为输出结果Page60SQL数据查询（单表查询）分组运算【例】查询成绩表Score中成绩最高分的学生的学号、课程号和相应成绩SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore=(SELECTmax(score)FROMScore);聚合函数可直接用在HAVING子句中，也可用于子查询中，但在WHERE子句中不可以直接使用聚合函数。如下语句是不正确的：SELECT*FROMScoreWHEREscore=max(score)Page61SQL语言详解SQL数据定义SQL数据查询单表查询连接查询嵌套查询集合查询SQL数据更新SQL视图操作SQL数据控制Page62SQL数据查询（连接查询）连接查询在WHERE子句中加入连接多个表的连接条件语句格式如下：WHERE[<表1>.]<列名1><比较运算符>[<表2>.]<列名2>[<逻辑运算符>

[<表3>.]<列名3><比较运算符>[<表4>.]<列名4>...]比较运算符包括：>、>=、<、<=、=、<>（或!=）当比较运算符为=时，表示等值连接，其他运算为非等值连接WHERE子句的连接谓词中的属性称为连接属性，连接属性之间必须具有可比性Page63SQL数据查询（连接查询）连接查询（等值连接）【例】查找会计学院全体同学的学号、姓名、籍贯、班级编号和所在班级名称。SELECTstudentNo,studentName,native,Student.classNo,classNameFROMStudent,ClassWHEREStudent.classNo=Class.classNoANDinstitute='会计学院';在连接操作中，如果涉及到多个表的相同属性名，必须在相同的属性名前加上表名加以区分Page64SQL数据查询（连接查询）连接查询（等值连接）可为参与连接的表取别名（称为元组变量），在相同的属性名前加上表的别名。将Student表取别名为a，Class表取别名为b，班级编号分别用a.classNo和b.classNo表示。上例可以改写为：SELECTstudentNo,studentName,native,b.classNo,classNameFROMStudenta,ClassbWHEREa.classNo=b.classNoANDinstitute='会计学院';对于不同属性名，可以不在属性名前加上表名或别名。Page65SQL数据查询（连接查询）连接查询（等值连接）【例】查找选修了课程名称为“计算机原理”的同学学号、姓名。查询语句为：SELECTa.studentNo,studentNameFROMStudenta,Courseb,ScorecWHEREb.courseNo=c.courseNo

AND

c.studentNo=a.studentNoAND

b.courseName='计算机原理';本例使用了元组变量，其连接条件为：b.courseNo=c.courseNoANDc.studentNo=a.studentNoPage66SQL数据查询（连接查询）连接查询（等值连接）【例】查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩，并按学号排序。SELECTa.studentNo,studentName,

b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,Scoreb,(SELECT*FROMScoreWHEREcourseNo='002')cWHEREb.courseNo='001'AND

a.studentNo=b.studentNoAND

a.studentNo=c.studentNoORDERBYa.studentNo;Page67SQL数据查询（连接查询）连接查询（等值连接）【例】查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩，并按学号排序。该查询也可以表示为：SELECTa.studentNo,studentName,

b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,(SELECT*FROMScoreWHEREcourseNo='001')b,(SELECT*FROMScoreWHEREcourseNo='002')cWHEREa.studentNo=b.studentNoAND

a.studentNo=c.studentNoORDERBYa.studentNo;Page68SQL数据查询（连接查询）连接查询（等值连接）【例】查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩，并按学号排序。该查询还可以表示为：SELECTa.studentNo,studentName,

b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,Scoreb,ScorecWHEREa.studentNo=b.studentNoAND

a.studentNo=c.studentNoAND

b.courseNo='001'AND

c.courseNo='002'ORDERBYa.studentNo;Page69SQL数据查询（连接查询）连接查询（自然连接）SQL不直接支持自然连接，完成自然连接的方法是在等值连接的基础上消除重复列。【例】实现成绩表Score和课程表Course的自然连接。SELECTstudentNo,a.courseNo,score,courseName,creditHour,courseHour,priorCourseFROMScorea,CoursebWHEREa.courseNo=b.courseNo;本例课程编号在两个关系中同时出现，但在SELECT子句中仅需出现1次，因此使用a.courseNo，也可以使用b.courseNo。其他列名是唯一的，不需要加上元组变量Page70SQL数据查询（连接查询）连接查询（自表连接）若某个表与自己进行连接，称为自表连接。【例】在学生表Student中查找与“李宏冰”同学在同一个班的同学姓名、班级编号和出生日期。SELECTa.studentName,

a.classNo,

a.birthdayFROMStudenta,StudentbWHEREb.studentName='李宏冰'AND

a.classNo=b.classNo;Page71SQL数据查询（连接查询）连接查询（外连接）在一般的连接中，只有满足连接条件的元组才被检索出来，对于没有满足连接条件的元组是不作为结果被检索出来的。【例】查询每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。SELECTclassName,institute,studentNo,studentNameFROMClassa,StudentbWHEREa.classNo=b.classNoORDERBYclassName;Page72SQL数据查询（连接查询）连接查询（外连接）从查询结果中可以看出：班级表中的“注册会计08_01班”、“注册会计08_03班”以及“金融管理07_01班”3个班没有出现在查询结果中，原因是这3个班没有学生。在实际应用中，往往需要将不满足连接条件的元组也检索出来，只是在相应的位置用空值替代，这种查询称为外连接查询。外连接分为左外连接、右外连接和全外连接。在FROM子句中，写在左边的表称为左关系，写在右边的表称为右关系。Page73SQL数据查询（连接查询）连接查询（外连接）左外连接连接结果中包含左关系中的所有元组，对于左关系中没有连接上的元组，其右关系中的相应属性用空值替代。【例】使用左外连接查询每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。SELECTclassName,institute,studentNo,studentNameFROMClassaLEFTOUTERJOINStudentbONa.classNo=b.classNoORDERBYclassName;Page74SQL数据查询（连接查询）连接查询（外连接）右外连接连接结果中包含右关系中的所有元组，对于右关系中没有连接上的元组，其左关系中的相应属性用空值替代。【例】使用右外连接查询每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。SELECTclassName,institute,studentNo,studentNameFROMStudentaRIGHTOUTERJOINClassbONa.classNo=b.classNoORDERBYclassName;Page75SQL数据查询（连接查询）连接查询（外连接）全外连接连接结果中包含左、右关系中的所有元组对左关系中没有连接上的元组，其右关系中的相应属性用空值替代对右关系中没有连接上的元组，其左关系中的相应属性用空值替代【例】使用全外连接查询每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。SELECTclassName,institute,studentNo,studentNameFROMClassaFULLOUTERJOINStudentbONa.classNo=b.classNoORDERBYclassName;Page76SQL语言详解SQL数据定义SQL数据查询单表查询连接查询嵌套查询集合查询SQL数据更新SQL视图操作SQL数据控制Page77SQL数据查询（嵌套查询）在SQL语言中，可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。一个SELECT-FROM-WHERE查询语句称为一个查询块，将一个查询块嵌入到另一个查询块的WHERE子句或HAVING子句中，称为嵌套查询。在一个嵌套语句中，上层的查询块称为外层查询或父查询，下层的查询块称为内层查询或子查询。SQL语言允许多层嵌套查询，但在子查询中，不允许使用ORDERBY子句，该子句仅用于最后的输出结果排序。Page78SQL数据查询（嵌套查询）嵌套查询（使用IN/NOTIN）子查询中使用最多的是谓词IN和NOTIN，含义为当查询的指定属性值包含（或不包含）在子查询结果属性值表中时条件为真。【例】查询选修过课程的学生姓名。本例查询的含义是：在学生表Student中，将学号出现在成绩表Score中（表明该学生选修过课程）的学生姓名查询出来。SELECTstudentNameFROMStudentWHEREStudent.studentNo

IN

(SELECTScore.studentNoFROMScore);Page79SQL数据查询（嵌套查询）嵌套查询（使用IN/NOTIN）【例】查询没有选修过任何课程的学生学号和姓名。本例查询的含义是：在学生表Student中，将学号没有出现在成绩表Score中的学生学号和姓名查询出来。SELECTstudentNo,

studentNameFROMStudentWHEREStudent.studentNo

NOTIN

(SELECTScore.studentNo

FROMScore);Page80SQL数据查询（嵌套查询）嵌套查询（使用IN/NOTIN）【例】查找选修过课程名中包含“系统”的课程的同学学号、姓名和班级编号。SELECTstudentNo,studentName,classNoFROMStudentWHEREstudentNo

IN(SELECTstudentNoFROMScoreWHEREcourseNo

IN

(SELECTcourseNoFROMCourseWHEREcourseNameLIKE'%系统%'));WHERE子句中的IN可以实现多重嵌套，本例是一个三重嵌套的例子。Page81SQL数据查询（嵌套查询）嵌套查询（使用IN/NOTIN）【例】查找选修过课程名中包含“系统”的课程的同学学号、姓名和班级编号。SELECTstudentNo,studentName,classNoFROMStudentWHEREstudentNo

IN

(SELECTDISTINCTstudentNo

FROMScorea,CoursebWHEREa.courseNo=b.courseNoANDb.courseNameLIKE'%系统%');本例是上例的另外一种实现方法，没有使用多重嵌套Page82SQL数据查询（嵌套查询）嵌套查询（使用IN/NOTIN）【例】查找同时选修过“计算机原理”和“高等数学”两门课程的同学学号、姓名以及该同学所选修的所有课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出。SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDa.studentNo

IN(SELECTDISTINCTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName='计算机原理')ANDa.studentNo

IN

(SELECTDISTINCTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName='高等数学')ORDERBYa.studentNo,scoreDESC;Page83SQL数据查询（嵌套查询）嵌套查询（使用IN/NOTIN）【例】查找同时选修过“计算机原理”和“高等数学”两门课程的同学学号、姓名以及该同学所选修的这两门课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出。SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDa.studentNo

IN(SELECTDISTINCTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName='计算机原理')ANDa.studentNo

IN

(SELECTDISTINCTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName='高等数学')

AND(courseName='高等数学'ORcourseName='计算机原理')ORDERBYa.studentNo,scoreDESC;Page84SQL数据查询（嵌套查询）嵌套查询（使用ANY（或SOME）和ALL）ANY的含义指当所指定运算符对于子查询返回的结果集合中的任何一个值为真时，父查询WHERE子句的值为真。SOME与ANY是同义词，早期用ANY，现在多用SOME。ALL的含义指当子查询返回0行，或所指定比较运算符对于子查询返回的所有结果均为真时，父查询WHERE子句的值为真。ANY/SOME和ALL谓词与集函数及IN谓词的等价转换关系Page85=<>或!=<<=>>=ANY/SOMEIN--<MAX<=MAX>MIN>=MINALL--NOTIN<MIN<=MIN>MAX>=MAXSQL数据查询（嵌套查询）嵌套查询（使用ANY（或SOME）和ALL）【例】查询所选修课程成绩大于所有课程编号“002”号课程成绩的同学学号及相应课程的课程号和成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore>ALL(SELECTscoreFROMScoreWHEREcourseNo='002');Page86SQL数据查询（嵌套查询）嵌套查询（使用ANY（或SOME）和ALL）【例】查询年龄小于“计算机科学与技术07_01班”某个同学年龄的所有同学的学号、姓名和生日。SELECTstudentNo,studentName,birthdayFROMStudentWHEREsysdate-birthday<ANY

(SELECTsysdate-birthdayFROMStudenta,ClassbWHEREa.classNo=b.classNoAND

b.className='计算机科学与技术07_01班'

);Page87SQL数据查询（嵌套查询）嵌套查询（使用EXISTS/NOTEXISTS）谓词EXISTS/NOTEXISTS用于测试表中记录数据的存在性，而不管数据值是什么。EXISTS指当子查询至少返回一个值时就取“真”，不论该值是什么（甚至可以是空值NULL）；NOTEXISTS指子查询返回的是一个无行无列的空表时就取“真”。Page88SQL数据查询（嵌套查询）嵌套查询（使用EXISTS/NOTEXISTS）【例】查询选修了“计算机原理”课程的同学姓名、所在班级编号。该查询可直接通过连接运算实现，也可以通过IN子查询来实现。还可以通过EXISTS实现：SELECTstudentName,classNoFROMStudentxWHEREEXISTS

(SELECT*FROMScorea,CoursebWHEREa.courseNo=b.courseNoAND

a.studentNo=x.studentNoANDb.courseName='计算机原理');Page89SQL数据查询（嵌套查询）嵌套查询（使用EXISTS/NOTEXISTS）【例】查询没有选修过任何课程的学生的学号和姓名。实际上也就是要查询出在成绩表中没有记录的学生的学号和姓名。SELECTstudentNo,studentNameFROMStudentxWHERENOTEXISTS

(SELECT*FROMScoreyWHEREy.studentNo=x.studentNo);Page90SQL数据查询（嵌套查询）嵌套查询（使用EXISTS/NOTEXISTS）【例】查询已选修课程的学生中没有不及格课程的学生的学号和姓名。SELECTDISTINCTx.studentNo,x.studentNameFROMStudentx,scoreyWHEREx.studentNo=y.studentNoAND

NOTEXISTS(SELECT*FROMScorezWHEREz.studentNo=y.studentNoAND

z.score<60);Page91SQL数据查询（嵌套查询）嵌套查询（使用EXISTS/NOTEXISTS）【例】查询已选修课程的学生中没有不及格课程的学生的学号和姓名。（使用NOTIN改写上例）SELECTDISTINCTx.studentNo,x.studentNameFROMStudentx,scoreyWHEREx.studentNo=y.studentNoAND

y.studentNo

NOTIN

(SELECTz.studentNoFROMScorezWHEREz.studentNo=y.studentNoAND

z.score<60);Page92SQL语言详解SQL数据定义SQL数据查询单表查询连接查询嵌套查询集合查询SQL数据更新SQL视图操作SQL数据控制Page93SQL数据查询（集合查询）SQL支持集合运算SELECT语句查询的结果是集合Oracle的集合运算并：UNION（去掉重复记录）并：UNIONALL（不去掉重复记录）交：INTERSECT差：MINUS集合运算的要求参与运算的查询结果的列数一样对应列的数据类型必须一致Page94SQL数据查询（集合查询）集合运算【例】查询“信息管理学院”1990年出生同学的学号、出生日期、班级名称和所属学院以及“会计学院”1991年出生同学的学号、出生日期、班级名称和所属学院。SELECTstudentNo,birthday,className,instituteFROMStudenta,ClassbWHEREa.classNo=b.classNoANDTo_char(birthday,'YYYY')='1990'ANDinstitute='信息管理学院'UNIONSELECTstudentNo,birthday,className,instituteFROMStudenta,ClassbWHEREa.classNo=b.cla