数据库原理课件-03-SQL复习

数据库系统概论AnIntroductiontoDatabaseSystem第三章关系数据库标准语言SQL第三章

关系数据库标准语言SQL3.1SQL概述3.2学生—课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结3.1.1SQL的产生与发展3.1SQL概述3.1.2SQL的特点

1、综合统一：SQL集数据定义语言DDL、数据操纵DML、数据控制语言DCL的功能于一体。2、高度非过程化：只要提出“做什么”，而无须指明“怎么做”。3、面向集合的操作方式：采用集合操作方式，操作对象和结果都是元组的集合。4、以同一种语法结构提供两种使用方法：既是独立的语言，又是嵌入式语言。3.1.2SQL的特点（续）5、语言简洁，易学易用：类似于英语的自然语言简洁易用。核心功能只用9个动词。3.1.3SQL的基本概念SQL语言支持关系数据库三级模式结构，外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。3.2学生—课程数据库本章用到了三个表：学生表：Student(Sno,Sname,Ssex,Sdept)课程表：Course(Cno,Cname,Cpno,Ccredit)学生选课表：SC(Sno,Cno,Grade)返回3.3数据定义

3.3.1模式的定义与删除定义模式实际上定义了一个命名空间CREATESCHEMA<模式名>AUTHORIZATION<用户名>[<表定义子句>|<视图定义子句>]3.3.2基本表的定义、删除与修改1.定义基本表CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；学生选课表SC例7：建立一个“学生选课”表SC

CREATETABLESC(SnoCHAR(9)，

CnoCHAR(4)，

GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，

/*主码由两个属性构成，必须作为表级完整性进行定义*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)，

/*表级完整性约束条件，Sno是外码，被参照表是Student*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)

/*表级完整性约束条件，Cno是外码，被参照表是Course*/ );定义基本表（续）常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束：

FOREIGNKEY（）REFERENCES2.修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][DROPCOLUMN<列名>][ALTERCOLUMN<列名><数据类型>]；3.删除基本表

DROPTABLE<表名>[RESTRICT|CASCADE]；

3.4数据查询

3.4.1概述3.4.2单表查询3.4.3连接查询3.4.4嵌套查询3.4.5集合查询3.4.6SELECT语句的一般格式

返回3.4.2单表查询

查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句

返回一、选择表中的若干列查询指定列[例1]查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；

[例5.1]使用列别名改变查询结果的列标题SELECTSnameNAME，‘YearofBirth:’

BIRTH，

2000-SageBIRTHDAY，

LOWER(Sdept)DEPARTMENTFROMStudent；返回输出结果：

NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------

李勇YearofBirth:1976cs

刘晨YearofBirth:1977is

王名YearofBirth:1978ma

张立YearofBirth:1977is二、选择表中的若干元组

1.消除取值重复的行[例6]查询选修了课程的学生学号。SELECTSnoFROMSC或SELECTALLSnoFROMSC

结果：Sno-------9500195001950019500295002(2)SELECTDISTINCTSnoFROMSC;结果：

Sno-------95001950022.查询满足条件的元组WHERE子句常用的查询条件查询条件谓词比较=,>,<,>=,<=,<>,!>,!<,NOT+上述比较运算符确定范围BETWEENAND,NOTBETWEENAND确定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重条件（逻辑运算）AND,OR,NOT(1)比较大小[例8]查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage<20；或

SELECTSname，SageFROMStudentWHERENOTSage>=20；

(2)确定范围[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

(3)确定集合使用谓词IN<值表>，NOTIN<值表><值表>：用逗号分隔的一组取值[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]<匹配串>：指定的匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词，用!=或<>运算符取代NOTLIKE谓词例题1)匹配模板为固定字符串[例14]查询学号为95001的学生的详细情况。

SELECT*FROMStudentWHERESnoLIKE'95001'；等价于：

SELECT*FROMStudentWHERESno='95001'；2)匹配模板为含通配符的字符串[例15]查询所有姓刘学生的姓名、学号和性别。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘刘%’；(5)涉及空值的查询

使用谓词ISNULL或ISNOTNULL

注意：“ISNULL”不能用“=NULL”代替[例21]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；(6)多重条件查询用逻辑运算符AND和OR来联结多个查询条件

AND的优先级高于OR

可以用括号改变优先级[例23]查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；三、ORDERBY子句

可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；SnoGrade9501095024950079295003829501082950097595014619500255四、使用聚集函数

计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>） 计算平均值AVG（[DISTINCT|ALL]<列名>）四、使用聚集函数

求最大值MAX（[DISTINCT|ALL]<列名>） 求最小值MIN（[DISTINCT|ALL]<列名>）[例26]

查询学生总人数SELECTCOUNT(*)FROMStudent；

[例28]

计算1号课程的学生平均成绩。

SELECTAVG(Grade)FROMSCWHERECno='1'；五、对查询结果分组

使用GROUPBY子句将查询结果按某一列或者多列的值进行分组，值相等的为一组。

[例30]

求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

CnoCOUNT(Sno) 122 234 344 433 5483.4.3连接查询

同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词

连接条件或连接谓词一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>比较运算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>连接查询示例例：查询每个学生及其选修课程的情况。 SELECTStudent.*,SC.* FROMStudent,SC WHEREStudent.sno=SC.sno两个表间的联系由公共属性sno实现StudentSCStudent.sno=SC.sno连接查询（续）SQL中连接查询的主要类型

广义笛卡尔积

等值连接(含自然连接)

非等值连接查询

自身连接查询

外连接查询

复合条件连接查询返回SELECTStudent.*,SC.*FROMStudent,SC

SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno例：SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno<>SC.SnoSELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=SECOND.Cno

SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno)；SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno3.4.4嵌套查询嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词

返回嵌套查询(续)嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询不相关子查询：子查询的查询条件不依赖于父查询相关子查询：子查询的查询条件依赖于父查询引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询返回一、带有IN谓词的子查询[例39]

查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘刘晨’)此查询为不相关子查询。DBMS求解该查询时也是分步去做的。带有IN谓词的子查询（续）

可以用自身连接完成本查询要求SELECTS1.Sno,S1.Sname,S1.SdeptFROMStudentS1,StudentS2WHERES1.Sdept=S2.SdeptAND

S2.Sname='刘晨'为数据库起别名[例41]找出每个学生超过他选修课程平均成绩的课程号。二、带有比较运算符的子查询SELECTSno,CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade) FROMSCy WHEREy.Sno=x.Sno)返回相关子查询带有比较运算符的子查询（续）SELECTSno,CnoFROMSCxWHEREGrade>=

(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno)返回⑴从外层查询中取出SC的一个元组x，将元组x的Sno值（200215121）传送给内层查询。SELECTAVG(Grade)FROMSCyWHEREy.Sno=‘200215121’⑵执行内层查询，得到值88，用该值代替内层查询，得到外层查询：SELECTSno,CnoFROMSCxWHEREGrade>=88⑶执行这个查询，得到：（200215121，1）（200215121，3）⑷外层查询取出下一元组重复上述1至3步骤的处理，直到外层的SC元组全部处理完毕。三、带有ANY或ALL谓词的子查询[例42]

查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄SELECTSname,SageFROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='IS')

ANDSdept<>'IS'

/*注意这是父查询块中的条件*/四、带有EXISTS谓词的子查询[例44]

查询所有选修了1号课程的学生姓名。思路分析：本查询涉及Student和SC关系。在Student中依次取每个元组的Sno值，用此值去检查SC关系。若SC中存在这样的元组，其Sno值等于该Student.Sno值，并且其Cno='1'，则取此Student.Sname送入结果关系。带有EXISTS谓词的子查询(续)用嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSC/*相关子查询*/WHERESno=Student.SnoANDCno='1')SC.SnoSnoSnameSsexSageSdept95001

李勇

男20CS95002

刘晨

女19IS95003

王敏

女18MA95004

张立

男19ISSC表Student表

SnoCnoGrade9500119295001285950013889500229095002380Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS9500119295001李勇男20CS9500128595001李勇男20CS9500138895002刘晨女19IS9500229095002刘晨女19IS95002380

带有EXISTS谓词的子查询(续)[例45]

查询没有选修1号课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')思考：此题用连接运算如何实现？SnoSnameSsexSageSdept95001

李勇

男20CS95002

刘晨

女19IS95003

王敏

女18MA95004

张立

男19ISSC表Student表

SnoCnoGrade9500119295001285950013889500229095002380Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS9500119295001李勇男20CS9500128595001李勇男20CS9500138895002刘晨女19IS9500229095002刘晨女19IS95002380

SnoSnameSsexSageSdept95001

李勇

男20CS95002

刘晨

女19IS95003

王敏

女18MA95004

张立

男19ISSC表Student表

SnoCnoGrade9500119295001285950013889500229095002380Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS9500119295001李勇男20CS9500128595001李勇男20CS9500138895002刘晨女19IS9500229095002刘晨女19IS95002380

95003王敏女18MANULLNULLNULL95004张立男19ISNULLNULLNULL带有EXISTS谓词的子查询(续)用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

(x)P≡(x(P))带有EXISTS谓词的子查询(续)用EXISTS/NOTEXISTS实现逻辑蕴涵(难点)SQL语言中没有蕴涵(Implication)逻辑运算可以利用谓词演算将逻辑蕴涵谓词等价转换为：

pq≡p∨q51常见的视图形式行列子集视图基于多个基表的视图基于视图的视图带表达式的视图分组视图返回521.行列子集视图[例1]

建立信息系学生的视图。返回CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'从单个基本表导出保留了码只是去掉了基本表的某些行和某些列531.行列子集视图（续）例：视图GOOD_SC(修课成绩在平均成绩之上的元组)返回CREATEVIEWGOOD_SCASSELECTSno，Cno，GradeFROMSCWHEREGrade>(SELECTAVG(Grade)FROMSC)542.基于多个基表的视图[例3]建立信息系选修了1号课程的学生视图。返回CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'553.基于视图的视图 [例4]

建立信息系选修了1号课程且成绩在90分以上的学生的视图。返回CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90564.带表达式的视图[例5]

定义一个反映学生出生年份的视图。返回CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2011-SageFROMStudent设置一些派生属性列,也称为虚拟列--Sbirth

带表达式的视图必须明确定义组成视图的各个属性列名575.建立分组视图[例6]

将学生的学号及他的平均成绩定义为一个视图

CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno58

注意：一类不易扩充的视图以SELECT*

方式创建的视图可扩充性差，应尽可能避免[例7]将Student表中所有女生记录定义为一个视图CREATEVIEWF_Student1(stdnum,name,sex,age,dept)ASSELECT*FROMStudentWHERESsex='女'缺点：修改基表Student的结构后，Student表与F_Student1视图的映象关系被破坏，导致该视图不能正确工作。59一类不易扩充的视图（续）可改为:

CREATEVIEWF_Student2(stdnum，name，sex，age，dept)ASSELECTSno，Sname，Ssex，Sage，SdeptFROMStudentWHERESsex='女'为基表Student增加属性列不会破坏Student表与F_Student2视图的映象关系。60删除视图DROPVIEW<视图名>该语句删除指定的视图定义由该视图导出的其他视图定义仍然存在，但已不能使用，必须显式删除删除基表时，由该基表导出的所有视图定义都必须显式删除返回[例9]

删除视图IS_S1DROPVIEWIS_S1613.6.2查询视图从用户角度：查询视图与查询基本表相同DBMS实现视图查询的方法实体化视图（ViewMaterialization）有效性检查：检查所查询的视图是否存在执行视图定义，将视图临时实体化，生成临时表查询视图转换为查询临时表查询完毕删除被实体化的视图(临时表)62查询视图（续）视图消解法（ViewResolution）进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询执行修正后的查询63查询视图（续）[例1]

在信息系学生的视图中找出年龄小于20岁的学生。

SELECTSno，SageFROMIS_StudentWHERESage<20IS_Student视图的定义(视图定义例1)：

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'64查询视图（续）视图实体化法视图消解法对[例1]，转换后的查询语句为：SELECTSno，SageFROMStudentWHERESdept='IS'ANDSage<2065查询视图（续）[例2]查询信息系选修了1号课程的学生SELECTSno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno='1'66查询视图（续）视图消解法的局限有些情况下，视图消解法不能生成正确查询。采用视图消解法的DBMS会限制这类查询。

67查询视图（续）[例3]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。S_G视图定义：

CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSnoSELECT*FROMS_GWHEREGavg>=9068查询转换错误：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno正确：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90返回693.6.3更新视图用户角度：更新视图与更新基本表相同更新视图最终要转换成对基本表的更新指定WITHCHECKOPTION子句后DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新70更新视图（续）[例1]

将信息系学生视图IS_Student中学号95002的学生姓名改为“刘辰”。UPDATEIS_StudentSETSname='刘辰'WHERESno='95002'转换后的语句：UPDATEStudentSETSname='刘辰'WHERESno='95002'ANDSdept='IS'71更新视图（