数据库系统概论第3章课件

第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1 SQL概述概述3.2 数据定义数据定义3.3 数据查询数据查询3.4 数据更新数据更新3.5 视图视图第三章第三章 关系数据关系数据库标库标准准语语言言SQL3.1 SQL概述概述13.1 SQL概述概述SQL（StructuredQueryLanguage）结构化查询语言，是关系数据库的标准语言数据查询数据定义数据操纵数据控制3.1 SQL概述概述SQL（Structured Query 2数据数据库库系系统统概概论论第第3章章课课件件SQL视图视图2视图视图1基本表基本表2基本表基本表1基本表基本表3基本表基本表4存储文件存储文件2存储文件存储文件1外模式外模式模模 式式内模式内模式SQL支持关系数据库三级模式结构支持关系数据库三级模式结构SQL视图视图2视图视图1基本表基本表2基本表基本表1基本表基本表3基本表基本表4存存储储文件文件243.2 数据定义数据定义 SQL的数据定义功能的数据定义功能:3.2 数据定数据定义义 SQL的数据定的数据定义义功能功能:53.2.1 模式的定义与删除模式的定义与删除CREATESCHEMAAUTHORIZATION|CREATESCHEMAStuCourseAUTHORIZATIONWANG;DROPSCHEMADROPSCHEMAStuCourseRESTRICT3.2.1 模式的定模式的定义义与与删删除除CREATE SCHEMA 模模63.2.2 基本表的定义、删除与修改基本表的定义、删除与修改（1）基本表的定义基本表的定义CREATETABLE（，）；3.2.2 基本表的定基本表的定义义、删删除与修改（除与修改（1）基本表的定）基本表的定义义7课程表课程表Course 例 建立一个“课程”表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY，CnameCHAR(40)，CpnoCHAR(4)，CcreditSMALLINT，FOREIGNKEY(Cpno)REFERENCESCourse(Cno);先修课先修课 Cpno是外码是外码 被参照表是被参照表是Course被参照列是被参照列是Cno课课程表程表Course 例例 建立一个建立一个“课课程程”表表Course先先8问题问题下面建立一个下面建立一个“学生选课学生选课”表表SCSC方法是否正确？方法是否正确？CREATESC(SnoCHAR(9)PRIMARYKEYnull，CnoCHAR(4)PRIMARYKEY，GradeSMALLINTnull，FOREIGNKEY(Sno)REFERENCESStudent(Sno)，FOREIGNKEY(Cno)REFERENCESCourse(Cno)，);问题问题下面建立一个下面建立一个“学生学生选课选课”表表SC方法是否正确？方法是否正确？9Sql server 主要数据类型主要数据类型数数 据据 类类 型型符符 号号 标标 识识数数 据据 类类 型型符符 号号 标标 识识整数型bigint,int,smallint,tinyintUnicode字符型nchar,nvarchar、精确数值型decimal,numeric文本型text,ntext浮点型float,real二进制型binary,varbinary货币型money,smallmoney日期时间类型datetime,smalldatetime,date,time位型bit时间戳型timestamp字符型char,varchar图像型image其他cursor,sql_variant,table,uniqueidentifier,xmlSql server 主要数据主要数据类类型数型数 据据 类类 型符型符 号号 标标 3.2.2 基本表的定义、删除与修改基本表的定义、删除与修改（2）修改基本表ALTERTABLEADD完整性约束DROPALTERCOLUMN；例：向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDS_entranceDATE；3.2.2 基本表的定基本表的定义义、删删除与修改（除与修改（2）修改基本表）修改基本表113.2.2 基本表的定义、删除与修改基本表的定义、删除与修改（3）删除基本表DROPTABLERESTRICT|CASCADE；例:删除Student表，同时删除表上定义的索引等其他对象DROPTABLEStudentCASCADE;3.2.2 基本表的定基本表的定义义、删删除与修改（除与修改（3）删删除基本表除基本表123.2.3 索引的建立与删除索引的建立与删除索引的建立CREATEUNIQUECLUSTERINDEXON(,)；CREATECLUSTERINDEXStusnameONStudent(Sname)；索引的删除DROPINDEXStusname；3.2.3 索引的建立与索引的建立与删删除索引的建立除索引的建立133.3数据查询数据查询SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；3.3数据数据查询查询SELECT ALL|DISTINCT，=，=，!=，!，!；NOT+上述比较运算符确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空 值IS NULL，IS NOT NULL多重条件（逻辑运算）AND，OR，NOT选择选择表中的若干元表中的若干元组组（2）查查 询询 条条 件件谓谓 词词比比 比较大小比较大小SELECTDISTINCTSnoFROMSCWHEREGrade60；确定范围确定范围SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；比比较较大小大小20确定集合确定集合SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS);字符匹配字符匹配SELECT*FROMStudentWHERESnameLIKE刘%；确定集合确定集合21空值的查询空值的查询SELECTSno，CnoFROMSCWHEREGradeISNULL多重条件查询多重条件查询SELECTSnameFROMStudentWHERESdept=CSANDSage20；空空值值的的查询查询22ORDER BY子句子句 可以按一个或多个属性列排序SELECT*FROMStudentORDERBYSdept，SageDESC；当排序列含空值时nASC：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示ORDER BY子句子句 可以按一个或多个属性列排序可以按一个或多个属性列排序23聚集函数聚集函数 计数COUNT（DISTINCT|ALL）计算总和SUM（DISTINCT|ALL）计算平均值AVG（DISTINCT|ALL）最值 MAX（DISTINCT|ALL）MIN（DISTINCT|ALL）聚集函数聚集函数 计计数数24例查询学生总人数。SELECTCOUNT(*)FROMStudent；例查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；例例 查询查询学生学生总总人数。人数。25GROUP BY子句子句 对查询的中间结果表进行分组，以细化聚集函数的作用例求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；CnoCOUNT(Sno)122234344433548GROUP BY子句子句 对查询对查询的中的中间结间结果表果表进进行分行分组组，以，以细细化聚集化聚集26having子句子句对分组后的数据进行筛选，选出满足条件的组例查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3；having子句子句对对分分组组后的数据后的数据进进行行筛选筛选，选选出出满满足条件的足条件的组组27问题问题Select、from、where、orderby、groupby、having子句在查询过程中的执行顺序？问题问题Select、from、where、order by、g283.3.2 连接查询连接查询 连接条件或连接谓词：用来连接两个表的条件.BETWEEN.AND.连接操作的执行方法嵌套循环法(NESTED-LOOP)排序合并法(SORT-MERGE)索引连接(INDEX-JOIN3.3.2 连连接接查询查询 连连接条件或接条件或连连接接谓词谓词：用来：用来连连接两个表的条接两个表的条29连接查询的种类连接查询的种类一、等值与非等值连接查询二、自身连接三、外连接四、复合条件连接连连接接查询查询的种的种类类一、等一、等值值与非等与非等值连值连接接查询查询 30等值与非等值连接查询等值与非等值连接查询 查询学生的姓名及获得的成绩SELECTStudent.sname,SC.gradeFROMStudent,SCWHEREStudent.Sno=SC.Sno注意：当两个以上的表具有相同的列名时，一定注意：当两个以上的表具有相同的列名时，一定要再列名前加上表名作为限制要再列名前加上表名作为限制问题：此查询有多问题：此查询有多少条记录满足条件少条记录满足条件？等等值值与非等与非等值连值连接接查询查询 查询查询学生的姓名及学生的姓名及获获得的成得的成绩问题绩问题：此：此查询查询31自身连接自身连接 查询存在间接先行课程的课程号及其间接先行课程号SELECTFIRST.Cno，SECOND.CpnopcnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；CnoPcno173556自身自身连连接接 查询查询存在存在间间接先行接先行课课程的程的课课程号及其程号及其间间接先行接先行课课程号程号Cn32外连接外连接(1)查询每门课程号及其间接先行课程号（如果存在的话）SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRSTLEFTOUTJOINCourseSECONDON（FIRST.Cno=SECOND.Cpno)FoSecond.cpno172null354null566null7null外外连连接接(1)查询查询每每门课门课程号及其程号及其间间接先行接先行课课程号（如果存在的程号（如果存在的话话）33外连接（外连接（2）左外连接列出左边关系（如本例Student）中所有的元组右外连接列出右边关系中所有的元组全外连接列出两个连接表中的所有元组外外连连接（接（2）左外左外连连接接34复合条件连接复合条件连接复合条件连接：WHERE子句中含多个连接条件查询选修2号课程且成绩在90分以上的所有学生SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=2ANDSC.Grade90；复合条件复合条件连连接复合条件接复合条件连连接：接：WHERE子句中含多个子句中含多个连连接条件接条件353.3.3嵌套查询嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块查询块嵌套查询:嵌套两个以上的查询块SELECTSname/*外层查询/父查询*/FROMStudentWHERESnoIN（SELECT Sno /*内层查询/子查询*/FROM SC WHERE Cno=2）；3.3.3嵌套嵌套查询查询一个一个SELECT-FROM-WHERE语语句句36带有比较运算符的子查询（带有比较运算符的子查询（1）当能确切知道内层查询返回单值单值时，可用比较运算符（，=，=，!=或）。与ANY、some、ALL谓词配合使用带带有比有比较较运算符的子运算符的子查询查询（1）当能确切知道内当能确切知道内层查询层查询返回返回单值时单值时37带有比较运算符的子查询（带有比较运算符的子查询（2）查询与刘晨同一学院的学生SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=刘晨)；带带有比有比较较运算符的子运算符的子查询查询（2）查询查询与刘晨同一学院的学生与刘晨同一学院的学生38带有比较运算符的子查询（带有比较运算符的子查询（3）例41找出每个学生超过他选修课程平均成绩的课程号。SELECTSno，CnoFROMSCxWHEREGrade=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno);相关子查询相关子查询 带带有比有比较较运算符的子运算符的子查询查询（3）例）例41找出每个学生超找出每个学生超过过他他选选修修39带有带有ANY（SOME）或）或ALL谓词的子查询谓词的子查询（1）某一值需要与多值比较时的妥协方法（配合比较运算符使用）比较比较含义含义比较比较含义含义ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值=ALL小于等于子查询结果中的所有值ANY小于子查询结果中的某个值 =ANY等于子查询结果中的某个值 =ANY大于等于子查询结果中的某个值 !=（或）ANY不等于子查询结果中的某个值=ALL大于等于子查询结果中的所有值!=（或）ALL不等于子查询结果中的任何一个值带带有有ANY（SOME）或）或ALL谓词谓词的子的子查询查询（1）某一）某一值值需要与需要与40带有带有ANY（SOME）或）或ALL谓词的子查询谓词的子查询(2)例查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄SELECTSname，SageFROMStudentWHERESageANY(SELECTSageFROMStudentWHERESdept=CS)ANDSdeptCS;带带有有ANY（SOME）或）或ALL谓词谓词的子的子查询查询(2)例例 查查41带有带有EXISTS谓词的子查询谓词的子查询(1)EXISTS()-trueEXISTS()-FalseNOTEXISTS()-trueNOTEXISTS()-false带带有有EXISTS谓词谓词的子的子查询查询(1)EXISTS()-42例查询所有选修了1号课程的学生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1)；例例查询查询所有所有选选修了修了1号号课课程的学生姓名。程的学生姓名。43例 查询选修了全部课程的学生姓名。SELECTSnameFROMStudentWHERENOT EXISTS（SELECT*FROMCourseWHERENOT EXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno）；例例 查询选查询选修了全部修了全部课课程的学生姓名。程的学生姓名。44例查询至少选修了学生200215122选修的全部课程的学生号码。SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno=200215122ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno)；例例查询查询至少至少选选修了学生修了学生200215122选选修的全部修的全部课课程的程的453.3.4 集合查询集合查询并SELECTSnoFROMSCWHERECno=1UNIONSELECTSnoFROMSCWHERECno=2；交交SELECT SnoFROM SCWHERE Cno=1 INTERSECTSELECT SnoFROM SCWHERE Cno=2；差差SELECT SnoFROM SCWHERE Cno=1 EXCEPTSELECT SnoFROM SCWHERE Cno=2；3.3.4 集合集合查询查询并交差并交差463.3.5 SELECT语句的一般格式语句的一般格式SELECTALL|DISTINCT别名，别名FROM别名，别名WHEREGROUPBYHAVING ORDERBYASC|DESC3.3.5 SELECT语语句的一般格式句的一般格式 SELECT AL473.4 数数 据据 更更 新新 3.4.1 插入数据插入数据3.4.2 修改数据修改数据3.4.3 删除数据删除数据3.4 数数 据据 更更 新新 3.4.1 插入数据插入数据483.4.1 插入数据（插入数据（1）插入单行将一个新学生元组（学号：201115128；课程号：2；成绩：95）插入到SC表中。INSERT INTO SC(Sno,Cno,grade)VALUES(201115128，2，95)；3.4.1 插入数据（插入数据（1）插入）插入单单行行 INSERT INT493.4.1插入数据（插入数据（2）同时插入多行计算每个系的学生的平均年龄，并把结果存入在表avgage（Sdept，age）（假定表avgage已经存在）INSERTINTOavgage（sdept，age）SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept3.4.1插入数据（插入数据（2）同）同时时插入多行插入多行503.4.2 修改数据修改数据语句格式UPDATESET=，=WHERE；UPDATEStudentSETSage=22WHERESno=200215121；注意：修改后的数据必须满足表的完整性约束注意：修改后的数据必须满足表的完整性约束3.4.2 修改数据修改数据语语句格式句格式513.4.3 删除数据删除数据语句格式DELETEFROMWHERE；问题：删除课程表中学分小于等于0的课程信息该怎么实现？DeleteFromcourseWhereccredit=03.4.3 删删除数据除数据语语句格式句格式523.5 视视 图图是基于基本表的虚表，是生成表基于视图的操作查询删除受限更新定义基于该视图的新视图3.5 视视 图图是基于基本表的虚表，是生成表是基于基本表的虚表，是生成表533.5 视视 图图3.5.1 定义视图定义视图3.5.2 查询视图查询视图3.5.3 更新视图更新视图3.5.4 视图的作用视图的作用3.5 视视 图图3.5.1 定定义视图义视图543.5.1 定义视图定义视图-创建创建CREATEVIEW(，)ASWITHCHECKOPTION；CREATEVIEWIS_Student(Sno，Sname，Sage)ASSELECTSno，Sname，SageFROMStudentWHERESdept=IS；问题问题:为什么子查询不允许含有为什么子查询不允许含有ORDER BY子句和子句和DISTINCT短语？短语？3.5.1 定定义视图义视图-创创建建CREATE VIEW 553.5.1 定义视图定义视图-删除删除DROPVIEW；DROPVIEWIS_S13.5.1 定定义视图义视图-删删除除DROP VIEW 视图视图名名563.5.2 查询视图查询视图对视图的查询方法与对基表的查询基本相同。对视图的查询大多数时候可以转换为对基表的直接查询。请举例。什么情况下对视图的查询不能转换为对基表的直接查询？3.5.2 查询视图对视图查询视图对视图的的查询查询方法与方法与对对基表的基表的查询查询基本相同基本相同573.5.3 更新视图更新视图-受限受限对于一些不能唯一地唯一地有意义有意义地转换成对相应基本表的更新的视图是不能更新的。不同的DBMS对此有私有的规定。如Sqlserver规定，当视图依赖于多个基表时，该视图是不可更新的。3.5.3 更新更新视图视图-受限受限对对于一些不能唯一地有意于一些不能唯一地有意义义地地转换转换成成583.5.4 视图的作用视图的作用1.视图能够简化用户的操作2.视图使用户能以多种角度看待同一数据3.视图对重构数据库提供了一定程度的逻辑独立性4.视图能够对机密数据提供安全保护5.适当的利用视图可以更清晰的表达查询3.5.4 视图视图的作用的作用1.视图视图能能够简够简化用化用户户的操作的操作59