第3章SQL语言PPT课件

.,第三章关系数据库标准语言SQL,StructuredQueryLanguage结构化查询语言非过程性查询语言,3.1SQL的特征,3.2SQL的数据定义,3.3SQL的数据查询,3.4SQL的数据更新,3.5视图,第三章关系数据库标准语言SQL,3.6数据安全控制,第三章关系数据库语言SQL,3.1SQL的特征1.SQL的主要标准SQL-86SQL-89SQL-92(SQL2)SQL-99(SQL3)2.SQL的功能特点功能：数据定义数据操纵数据控制,特点：综合统一高度非过程化灵活的使用方式（自含、嵌入式）简洁、通用、功能强表3.1：SQL语言的动词,SQL语言功能和动词,数据查询：检索SELECT数据定义：创建CREATE，删除DROP，修改ALTER数据操纵：插入INSERT，删除DELETE，更改UPDATE数据控制：授权GRANT，回收REVOKE,3.1SQL的特征,3.SQL的基本组成（1）SQL数据库层次结构,术语对照：一般关系模型SQL外模式-视图（VIEW)模式-基本表(TABLE)内模式-存储文件（索引）元组-行(ROW)属性-列(COLUMN),3.1SQL的特征,（2）SQL语言的组成数据定义语言（DDL）数据操纵语言（DML）数据控制语言（DCL）,3.1SQL的特征,（3）存储过程存储过程是存储在SQL服务器上的预编译好的一组为了完成特定功能的SQL语句集。通过指定存储过程的名字并给出参数来执行它。可分为两类：系统存储过程：由系统自动创建，完成的功能主要是从系统表中获取信息。用户定义的存储过程：由用户为完成某一特定功能而编写的存储过程。使用存储过程的好处：可减少网络流量。增强代码的重用性和共享性。加快系统运行速度。保证数据安全性。,3.2SQL的数据定义,1.数据类型,3.2SQL的数据定义,2.表的建立和删除（1）表的建立命令格式：CREATETABLE模式名.(，)；例：CREATETABLEStudent(snoCHAR(5)NOTNULLUNIQUE，snameCHAR(20)UNIQUE，sexCHAR(1)，ageINT，deptCHAR(15)；,主码,3.2SQL的数据定义,完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级，也可以定义在表级。,注意：,例：定义P.76的三个表结构，并指定相应的数据完整性约束条件。,分析,外码：sno,cno,主码：sno,姓名：非空,性别：男、女两值,Student表：,Course表：,主码：cno,课程名：非空,外码：pcno,SC表：,主码：(sno,cno),成绩：0100,3.2SQL的数据定义,CREATETABLEStudent(snoCHAR(5),snameCHAR(8)NOTNULL,sexCHAR(2),ageSMALLINT,deptCHAR(20),PRIMARYKEY(sno),CHECKsexIN(男,女);,列级完整性约束条件,实体完整性约束条件,用户自定义完整性约束条件,3.2SQL的数据定义,CREATETABLECourse(cnoCHAR(4),cnameCHAR(10)NOTNULL,pcnoCHAR(4),creditSMALLINT,PRIMARYKEY(cno),FOREIGNKEY(pcno)REFERENCESCourse(cno);,参照完整性约束条件,3.2SQL的数据定义,CREATETABLESC(snoCHAR(5),cnoCHAR(4),gradeSMALLINT,PRIMARYKEY(sno,cno),FOREIGNKEY(sno)REFERENCESStudent(sno),FOREIGNKEY(cno)REFERENCESCourse(cno),CHECK(gradeISNULL)OR(gradeBETWEEN0AND100);,3.2SQL的数据定义,（2）表的删除格式：DROPTABLECASCADE|RESTRICT例：DROPTABLESt-quitCASCADE；3.表的扩充和修改（1）在现存表中增加新列格式：ALTERTABLEADD(，)例：ALTERTABLEStudentADD(placeCHAR(20)，addrCHAR(20)；,3.2SQL的数据定义,（2）删除已存在的某个列格式：ALTERTABLEDROPCASCADE|RESTRICT例：ALTERTABLEStudentDROPaddr；（3）修改原有列的类型格式：ALTERTABLEMODIFY；例：ALTERTABLEStudentMODIFYplaceCHAR(8)；,Cascade|Restrict,Cascade：在基本表中删除某属性时，所有引用到该属性的视图和约束也要一起自动地删除；Restrict：表示在没有视图或约束引用该属性时，才能在基本表中删除该属性，否则拒绝删除操作。,3.2SQL的数据定义,（4）补充定义主码格式：ALTERTABLEADDPRIMARYKEY(),（5）删除主码格式：ALTERTABLEDROPPRIMARYKEY,3.2SQL的数据定义,5.索引的建立与删除建立索引的目的：基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。命令格式：CREATEUNIQUECLUSTERINDEXON(，)；次序：升序（ASC，缺省）降序（DESC）UNIQUE:每一个索引值只对应惟一的数据记录。CLUSTER:建立聚簇索引，即索引项的顺序与表中记录的物理顺序一致。,3.2SQL的数据定义,注意：在一个基本表上最多只能建立一个聚簇索引。经常更新的列不宜建立聚簇索引。所建索引放何处？例:为Student表按学号升序建惟一聚簇索引。为SC表按学号升序和课程号降序建惟一索引。CREATEUNIQUECLUSTERINDEXStnoONStudent(Sno)；CREATEUNIQUEINDEXScnoONSC(Sno，CnoDESC)；删除索引一般格式为：DROPINDEX；例：DROPINDEXStno；删除索引时，系统会同时从数据字典中删去有关该索引的描述。,3.3SQL的数据查询,一般形式：SELECTFROMWHEREGROUPBYHAVINGORDERBYASC|DESC;,基本语句含义:根据F，从表或视图中找出满足条件的元组，再从中选出目标属性值形成结果表。,查询目标,数据来源,选择元组的条件,将结果按的值进行分组,满足条件的组才予输出,按排序查询结果,3.3SQL的数据查询,其中：表示其中的成分为任选项。：表示其中的成分由用户具体给定。|：表示其中并列的成分只能择一。查询目标：ALL：表示保留满足条件的所有元组（缺省）。DISTINCT：表示去掉重复元组。目标列：可以为属性名、表达式、通配符*（表示所有属性列）。,3.3SQL的数据查询,学生-课程数据库:Student(sno，sname，sex，age，dept)Course(cno，cname，pcno，credit)SC(sno，cno，grade)3.3.1单表查询一、选择表中的列例：查询所有学生的姓名、学号、所在系。,SELECTsname，sno，deptFROMStudentWHERE.T.;,目标A来源R条件F,snamesnodept王萧虎200101信息院,3.3SQL的数据查询,例：查全体学生的姓名及其出生年份。SELECTsname，2005-ageFROMStudent；,sname2005-age王萧虎1987,Birthday,别名,Birthday,二、选择表中的行（1）比较例：查考试成绩不及格的学号。,SELECTsnoFROMSCWHEREgrade=60,3.3SQL的数据查询,二、对查询结果分组：GROUPBY子句将查询结果表按某一(多)列值分组，值相等的为一组。目的：细化集函数的作用对象。例：求每个学生（号）的平均成绩。SELECTsno，AVG(grade)FROMSCGROUPBYsno；,HAVING短语：在各组中选择满足条件的小组。WHERE子句：在表中选择满足条件的元组。,WHERE?,并将其超过88分的输出。,HAVINGAVG(grade)88；,3.3SQL的数据查询,练习：查询至少选修4门课程的学生学号。例：找出选课学生超过30人的课程的平均成绩及选课人数。SELECTcno,AVG(grade),COUNT(*)ASst_numberFROMSCGROUPBYsnoHAVINGst_number=30,3.3SQL的数据查询,例：求学生关系中女生的每一年龄组（超过20人）有多少，要求查询结果按人数升序排列，人数相同时按年龄降序排列。SELECTage,COUNT(sno)ASnumberFROMStudentWHEREsex=女GROUPBYageHAVINGnumber20ORDERBYnumber,ageDESC,3.3SQL的数据查询,3.3.3多表查询1.子查询（嵌套查询）查询块：SELECTFROMWHERE子查询（嵌套查询）：一个查询块嵌套在另一查询块中作为条件的查询。子查询一般跟在IN、SOME（ANY）、ALL和EXIST等谓词后面,3.3SQL的数据查询,例:查询选修了数据库课程的学生号、成绩。SELECTsno，gradeFROMSCWHEREcnoIN(SELECTcnoFROMCourseWHEREcname=数据库)；,例：找出年龄超过平均年龄的学生姓名。SELECTsnameFROMStudentWHEREage(SELECTAVG(age)FROMStudent)；,3.3SQL的数据查询,带SOME(ANY)、ALL谓词的子查询SOME大于子查询结果中的某个值=ALL(SELECTAVG(grade)FROMSCGROUPBYsno)；,3.3SQL的数据查询,SELECTStudent.sno，sname，sex，age，dept，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.sno；,2.条件连接查询连接条件的一般格式为：.当连接运算符为=时，称为等值连接。例:查询每个学生的情况及其选课成绩。,3.3SQL的数据查询,例：找出籍贯为湖北或河北，选课成绩为90分以上的学生的姓名、课号和成绩。SELECTsname，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.snoANDplaceIN(湖北，河北)ANDgrade90；,3.3SQL的数据查询,例:查询选修了数据库的学生号、成绩SELECTsno，gradeFROMSCWHEREcnoIN(SELECTcnoFROMCourseWHEREcname=数据库)；,学分？,SELECTsno，gradeFROMSC，CourseWHERESC.cno=CoANDcname=数据库；,，Ccredit,=,子查询,连接查询,3.3SQL的数据查询,例：按平均成绩的降序给出所有课程都及格的学生（号、名）及其平均成绩，其中成绩统计时不包括008号考查课。SELECTStudent.sno，sname，AVG(grade)ASavg_gFROMStudent，SCWHEREStudent.sno=SC.snoANDcno008GROUPBYsnoHAVINGMIN(grade)=60ORDERBYavg_gDESC；,3.3SQL的数据查询,3.自身连接查询例：找出年龄比“王迎”同学大的同学的姓名及年龄。SELECTs1.sname，s1.ageFROMSASs1，SASs2WHEREs1.ages2.ageANDs2.sname=王迎；,其他方法？,练习：查询每一门课的间接先修课（即先修课的先修课）。SELECTFIRST.cno,SECOND.pcnoFROMCourseFIRST,CourseSECONDWHEREFIRST.pcno=SECOND.cno;,3.3SQL的数据查询,4.FROM子句中的子查询在FROM子句中可以使用子查询，其查询的结果表称为导出关系（DerivedRelation）。例：查出选课成绩在80分以上的女学生的姓名、课程名和成绩。SELECTsname，cname，gradeFROM(SELECTsname，cname，gradeFROMStudent，SC，CourseWHEREStudent.sno=SC.snoANDSC.cno=CoANDsex女)ASTemp(sname，cname，grade)WHEREgrade=80；,导出关系,3.3SQL的数据查询,例：查询所有选修了1号课程的学生姓名。SELECTsnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHEREsno=Student.SnoANDcno=1)；,5.相关子查询当一个子查询的判断条件涉及到一个来自外部查询的列时，称为相关子查询。带存在谓词的子查询只产生逻辑值存在谓词EXISTS作用:若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。,没有选修,NOT,其他方法？,3.3SQL的数据查询,例:查询选修了全部课程的学生姓名。转换成：查询这样的学生，没有一门课程是他不选修的。(x)P(x(P),SELECTsnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHEREsno=Student.snoANDcno=Co)；,每门课都没选,某学生选某课,3.3SQL的数据查询,练习：查询至少选修了学号为200101的学生选修的全部课程的学生学号。SELECTsnoFROMStudentXWHERENOTEXISTS(SELECT*FROMSCYWHEREY.sno=200101ANDNOTEXISTS(SELECT*FROMSCZWHEREX.sno=Z.snoANDY.cno=Z.cno),3.3SQL的数据查询,6.集合运算（1）UNION（并）（2）INTERSECT（交）（3）EXCEPT（差）例：求选修了001或002号而没有选003号课程的学生号。(SELECTsnoFROMSCWHEREcno=001ORcno=002)EXCEPT(SELECTsnoFROMSCWHEREcno=003)；,3.3SQL的数据查询,3.3.4自然连接与外连接1.自然连接(NATURALJOIN)例：查籍贯为湖北、课程成绩在90分以上的学生档案及其成绩情况。SELECT*FROMStudentNATURALJOINSCWHEREplace=湖北ANDgrade=90；2.外连接左外连接。LEFTOUTERJOIN，保留左关系的所有元组。右外连接。RIGHTOUTERJOIN，保留右关系的所有元组。全外连接。FULLOUTERJOIN，保留左右两关系的所有元组。,3.3SQL的数据查询,教师表：Teacher(教师号，姓名，所属大学，职称)任职表：Post(编号，姓名，职务)例：SELECT*FROMTeacherFULLOUTERJOINPost；例：SELECT*FROMTecherLEFTOUTERJOINPost；例：SELECT*FROMTecherRIGHTOUTERJOINPost；,3.3SQL的数据查询,3.3.5SQL中的空值处理1.空值的含义值暂未知。值不适用。值需隐瞒。2.空值的若干规则（1）空值与数值型数据进行算术运算，结果为空值。（2）空值和任何值(包括空值)进行比较运算，结果为UNKNOWN。,3.3SQL的数据查询,（3）空值的布尔运算UANDT=U；UANDF=F；UANDU=U；UORT=T；UORF=U；UORU=U；NOTU=U。空值的测试：ISNULL和ISNOTNULL。例：查询缺少选课成绩的学生号和相应的课程号。SELECTSno，CnoFROMSCWHEREgradeISNULL；,3.4SQL的数据更新,一.插入数据1.插入单个元组格式：INSERTINTO（，)VALUES(,);说明：（1）若插入全部属性，则属性列可省略。（2）表定义中说明为NOTNULL的列不能取空值。（3）属性值与相对应的属性列的数据类型要匹配。（4）向参照表中插入元组，关系系统自动支持：实体完整性参照完整性,3.4SQL的数据更新,2.插入子查询结果格式：INSERTINTO(,)子查询；二.修改数据格式：UPDATESET=,=WHERE;三.删除数据格式：DELETEFROMWHERE;,3.5视图,一、视图的概念及作用是从一个或几个基本表(或视图)导出的表，是虚表。与表一样可被查询。对视图的更新操作有一定的限制。对视图的一切操作最终将转换为对基本表的操作。视图的作用：（1）简化结构及复杂操作。（2）多角度地、更灵活地共享。（3）提高逻辑独立性。（4）提供安全保护。,3.5视图,二、创建与使用视图1.创建视图一般格式：CREATEVIEW(,)ASWITHCHECKOPTION；,更新操作时要保证操作的行满足视图定义中的谓词条件。,其中：子查询不允许含有ORDERBY子句和DISTINCT短语。,3.5视图,其中：子查询不允许含有ORDERBY子句和DISTINCT短语。,CREATEVIEWAuthorsByNameASSELECTTOP100PERCENT*FROMauthorsORDERBYau_lname,au_fname,3.5视图,例：建立信息院学生的视图，要求进行更新时仍保证该视图只有信息院的学生。CREATEVIEWD_StudentASSELECTsno，sname，ageFROMStudentWHEREdept=信息院；,WITHCHECKOPTION；行列子集视图：视图是从单个基本表导出，并且只是去掉了基本表的某些行和某些列，但保留了码。,CreateView计算机类AsSelect*FromBookWhereBookType=计算机;在该视图上可以插入、删除、修改所有类型的图书。CreateView计算机类AsSelect*FromBookWhereBookType=计算机WITHCHECKOPTION;在该视图上只能插入、删除、修改计算机类的图书。,3.5视图,带虚拟列的视图：是指设置了一些基本表中并不存在的派生列(虚拟列)的视图。例：定义一个反映学生出生年份的视图。CREATEVIEWBT_S(sno，sname，sbirth)ASSELECTsno，sname，2005-ageFROMStudent；分组视图：带有集函数和GROUPBY子句查询所定义的视图。例：将学生的学号及其平均成绩定义为一个视图。CREATEVIEWS_G(sno，gavg)ASSELECTsno，AVG(grade)FROMSCGROUPBYsno；,3.5视图,三、查询视图视图消解把视图定义中的子查询和用户查询结合起来，转换成等价的对基本表的查询过程。四、更新视图最终要转换为对基本表的更新。注意：并不是所有的视图都可以更新的。行列子集,二、删除视图DROPVIEWCASCADE|RESTRICT注意：视图删除后，视图的定义将从数据字典中自动删除。但要删除该视图导出的其他视图应用CASCADE。,合理使用视图带来许多好处,1.视图能够简化用户的操作2.视图对重构数据库提供了一定程度的逻辑独立性数据的逻辑独立性是指当数据库重构造时，如增加新的关系或对原有关系增加新的字段等，用户和用户程序不会受影响。3.视图能够对机密数据提供安全保护,4.视图对重构数据库提供了一定程度的逻辑独立性例如：将学生关系Student(Sno,Sname,Ssex,Sage,Sdept)分为SX(Sno,Sname,Sage)和SY(Sno,Ssex,Sdept)如果我们建立一个视图Student：CREATEVIEWStudent(Sno,Sname,Ssex,Sage,Sdept)ASSELECTSX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.SdeptFROMSX,SYWHERESX.Sno=SY.Sno;尽管数据库的逻辑结构改变了，用户的应用程序通过视图仍然能够查找数据,定义视图属性,必须明确指定组成视图的所有列名的情况：（1）其中某个目标列不是单纯的属性名，而是集函数或列表达式（2）多表连接时选出了几个同名列作为视图的字段；（3）需要在视图中为某个列启用新的更合适的名字注意：组成视图的属性列名，或者全部省略或者全部指定，没有第三种选择。,视图的作用,视图可以提供附加的安全层；视图可以隐藏数据的复杂性视图有助于命名的简洁：建立视图时，可以对字段名重新命名，以适应用户操作。视图带来更改的灵活性：可更改组成视图的一个或多个表的内容而不用更改应用程序。,SQL中的数据控制功能包括事务管理功能和数据保护功能，即：事务管理和数据库恢复数据库并发控制数据完整性控制数据安全性保护这里主要讨论安全性控制功能：某个用户对某类数据具有何种操作权利是政策问题而非技术问题。DBMS的功能是保证则这些决定的执行，为此应具有以下功能：把授权的决定告知系统，由GRANT和REVOKE来完成。把授权的结果存入数据字典。当用户提出操作请求时，根据字典进行查询，以决定是否执行操作请求。,3.6SQL的数据控制功能(SQL_DCL),数据库系统中保证数据安全性的主要措施是进行存取控制，即规定不同用户对于不同数据对象所允许执行的操作，并控制各用户只能存取他有权存取的数据。数据控制关键字：Grant（授权）REVOKE（收权）,GRANT：将对指定操作对象的指定权限授予指定的用户。1、对不同类型的操作对象（属性列、视图、基本表或数据库）有不同的操作权限。参见表3.4（p130）。2、接受权限的用户可以是一个或多个具体用户，也可以是PUBLIC，即全体用户。3、如果指定了WITHGRANTOPTION子句，则获得该权限的用户还可以把这种权限再授予其他的用户。否则不能传播该权限。REVOKE：授予用户的权限可以由DBA或其他授权者用该语句收回。1、回收权限的操作回级联下去。2、所有授予出去的权利在必要时都可以用REVOKE语句收回。总之：DBA拥有对数据库中所有对象的所有权限，并可授权和回收。,权限管理,权限类型：表3.4一般情况下，创建关系模式的用户，自动拥有对该关系数据操作的各种权限。权限的客体数据对象的单位：属性、基表、视图、存储过程,授权的语法格式：GrantOnToWithGrantOptionWithGrantOption表示该用户同时拥有将权限授予他人的许可。收权REVOKE语句的一般格式为：REVOKE,.ONFROM,.;,授权、收权举例,允许用户罗鹏可以检索、插入、修改和删除关系BOOK中的数据；GRANTselect,insert,update,deleteONbookTO罗鹏允许刘辉南至可以检索BOOK中的数据；GRANTselectONbookTO刘辉南允许魏功良可以检索和插入BOOK中的数据。GRANTselect,insertONbookTO魏功良收权REVOKEselectONbookFROM刘辉南REVOKEselect,insertONbookFROM魏功良,把对Student表和Course表的全部权限授予用户U2和U3GRANTALLPRIVILIGESONTABLEStudent,CourseTOU2,U3;把对表SC的查询权限授予所有用户GRANTSELECTONTABLESCTOPUBLIC;把查询Student表和修改学生学号的权限授给用户U4GRANTUPDATE(Sno),SELECTONTABLEStudentTOU4;把用户U4修改学生学号的权限收回REVOKEUPDATE(Sno)ONTABLEStudentFROMU4;,把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户GRANTINSERTONTABLESCTOU5WITHGRANTOPTIONDBA把在数据库S_C中建立表的权限授予用户U8GRANTCREATETABONDATABASES_CTOU8,4个样本表,Student.dbf学生信息表：,teacher.dbf教师信息表：,course.dbf课程表：,score.dbf成绩表：,练习题,1、查询教师所在的单位，即不重复的depart列。2、查询SCORE表中成绩在60到80之间的所有记录。3、以CNO升序、DEGREE降序查询SCORE表的所有记录。4、查询3-105号课程的平均分。5、查询SCORE表中至少