3第3章数据库系统.ppt

3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL,第三章关系数据库标准语言SQL,3.1关系数据库标准语言SQL概述,SQL是StructuredQueryLanguage的缩写，即结构化查询语言。,3.1.1SQL语言特点,（1）一体化特点SQL提供了一系列完整的数据定义、数据操纵和数据控制功能，用SQL可以实现数据库生命周期中的全部活动。,（2）高度非过程化SQL是非过程语言，即用户依据“做什么”来说明操作，而无须说明“怎么做”，存取路径的选择和SQL语句的操作过程由系统自动完成。,（3）面向集合的操作方式SQL语言对关系进行操作，其操作对象和结果都是二维表，特点是一次一集合（set-ata-time),（4）统一的语法结构实现两种使用方式无论是联机交互使用方式，还是嵌入到高级语言中使用，其语法结构基本一致。,（5）语言简洁，易学易用SQL语言功能极强，但由于设计巧妙，语言十分简洁，完成核心功能只用了九个动词，如下表所示。,3.1.2SQL语言的基本概念SQL语言支持关系数据库三级模式结构如图所示。,基本表是本身独立存在的表，在SQL中一个关系对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引存放在存储文件中。所有基本表的逻辑结构的总和构成数据库的模式。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。,视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图以及部分基本表构成数据库的外模式。,3.2数据定义关系数据库由模式、外模式和内模式组成，即关系数据库的基本对象是表、视图和索引。因此SQL的数据定义功能包括定义表、定义视图和定义索引。如表所示：,修改,ALTERTABLE,3.2.1定义、删除与修改基本表(参考第5章),数据类型一般有：CHAR（字符型）NUMBER（数值型）、INT（整型）、SMALLINT（整型）、MONEY（货币型）DATE（日期型）等。有的系统可能还会提供BINARY（二进制型）、DATETIME（日期时间型）、TIME（时间型）、TEXT（文本型）和IMAGE（图象型）等,1.定义基本表CREATETABLE(列级完整性约束条件,列级完整性约束条件,);,（1）是所要定义的基本表的名字。一个表它可以由一个或多个属性组成。（2）一般取有实际意义的名字。（3）可以是前面介绍的数据类型。（4）SQL中实现完整性控制的方法：实体完整性、参照完整性、用户定义完整性。,实体完整性：主键约束，用primarykey定义。,CREATETABLEStudent(SnoCHAR(5)PRIMARYKEY,SnameCHAR(20),SsexCHAR(2),SageINT,SdeptCHAR(15),CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(20),CpnoCHAR(4),CcreditINT),CREATETABLESC(SnoCHAR(5),CnoCHAR(4),GradeINT,PRIMARYKEY(Sno,Cno),1、检查主码值是否唯一？2、检查主码各个属性是否为空？,参照完整性：外键约束，用foreignkey定义。,CREATETABLESC(SnoCHAR(5),CnoCHAR(4),GradeINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(sno)REFERENCESStudent(Sno),FOREIGNKEY(cno)REFERENCESCourse(Cno),1、SC表插入一个元组，用到的sno和cno在学生表和课程表中必须存在。2、设置级联操作后，修改或删除被参照表中的主键值后，SC表（参照表）中对应的所有sno或cno跟着修改或删除对应的所有元组。3、没有设置级联，修改或删除被参照表中的主键值后，参照表中对应的属性值将被置空。（注意这里的sno和cno是不可以的，为什么？）,CREATETABLESC(SnoCHAR(5)，CnoCHAR(4),GradeINT,PRIMARYKEY(Sno,Cno)，FOREIGNKEYREFERENCESStudent(Sno)ONDELETECASCADEONUODATECASCADE,FOREIGNKEYREFERENCESCourse(Cno)ONDELETEACTIONONUODATECASCADE)说明：ACTION拒绝级连操作，CASCADE进行级连操作。,级连操作设置,2.修改基本表SQL语句用ALTERTABLE语句修改基本表，其一般格式为：ALTERTABLEADD完整性约束DROPALTERCOLUMN;其中ADD子句用来增加新列和新的完整新约束条件,DROP子句用于删除指定的完整性约束条件,ALTER子句用于修改原有的列定义.,例、向Student表增加“入学时间列”,其数据类型为日期型。ALTERTABLEStudentADDScomeDATE;例、将年龄的数据类型改为小整数。ALTERTABLEStudentALTERCOLUMNSageSMALLINT；,用户定义完整性：1、不允许取空值ALTERTABLEStudentALTERCOLUMNSnameCHAR(20)NOTNULL；2、列值唯一ALTERTABLEStudentaddUNIQUE(Sname)3、检查约束ALTERTABLEStudentaddcheck(ssexin(男,女)ALTERTABLESCaddcheck(grade=0andgrade=.,!,!,NOT+上述比较运算符逻辑运算符：and,or查询时，如需满足多个条件，那么可用逻辑运算符将条件连起来形成多重条件。例6、查计算机系全体学生的信息。,*,Student,Sdept=CS；,信息系,IS；,例7、查询信息系(IS系)全体女生本题查询的信息需要同时满足两个条件，两个条件之间的关系是“与”的关系,*,Student,Sdept=IS,ssex=女；,例8、查询信息系年龄小于20岁的学生学号，姓名。,selectsno,snamefromStudentwheresdept=“IS”andsage20；,关系代数表达式为：sno,sname(sdept=“IS”sage20(Student),例9、查询选修了“3”号课程的学生的学号、成绩。,selectsno,gradefromSCwherecno=3；,关系代数表达式：(sno,grade(cno=3(SC),例10、查询选修了“3”号课程且成绩在60分以下的学生的学号、成绩。selectsno,gradefromSCwherecno=3andgrade60；(sno,grade(cno=3grade60(SC)例11、查询数据库这门课的先行课号。SelectcpnoFromcourseWherecname=数据库；,练习题一,1、查询计算机系（CS）所有学生的姓名、性别和年龄。2、查询数学系（MA）小于18岁的学生的学号、姓名以及性别和年龄3、查询成绩不及格的学生的学号、课程号和成绩。4、查询选修了c02课程的学生的学号和成绩5、查询9531101学生的成绩在80分以上的选课情况。6、查询数学系学生的学号、姓名和出生年份，要求对各个字段进行重新命名。,1、查询计算机系（CS）所有学生的姓名、性别和年龄。Selectsname,ssex,sageFromstudentWheresdept=CS2、查询数学系（MA）小于18岁的学生的学号、姓名以及性别和年龄SELECTsno,sname,ssex,sageFromstudentWheresdept=MAANDsage=80andgrade3；,例37、查询每个系的学生的人数，列出学生人数大于200的系的名字与人数。,第一步：Selectsdept,count(sno)FromstudentGroupbysdept,Havingcount(sno)200,第二步：,例38、查询平均成绩大于80分的且最低成绩高于75分的学生的平均成绩,并按平均成绩降序排列。,第一步：Selectsno,avg(grade)，min(grade)FromscGroupbysno,Havingavg(grade)80andmin(grade)75,第二步：,Orderbyavg(grade)DESC,第三步：,例39、查询选修人数小于15个的课程号,第一步：Selectcno，count(sno)FromscGroupbycno,Havingcount(sno)=90,Sc表,最后做投影运算，投影到学号和姓名，即我们所要的信息,其结果表为：,Student.sno,sname,Student,sc,Student.sno=sc.sno,cno=2,grade=90；,例45、查询每个学生的姓名、所选课程名，成绩,前两个表连接，连接条件是：Student.sno=SC.sno,Sc表,Course表,再与第三个表连接，连接条件是：SC.cno=Co,最后，做投影，投影到sname,cname,grade,相应的查询语句为：Selectsname,cname,gradeFromstudent,sc,courseWherestudent.sno=o=o,小结：(1)如果查询涉及到两张表以上（即所求信息来自多张表或所求信息和查询条件来自不同的表或者条件涉及多张表），则必须要进行连接查询，再where子句中有连接条件。(2)from后面有N张表，则连接条件有N-1个(3)通过多张表的连接查寻，我门实现了从多张表查询出更多综合信息的目标。,习题三1、求选了1号课程的学生的学号、姓名、系别。2、求选修了2号课程且分数大于90分的会计系学生的名字、学号、课程号与成绩。3、求数学系或选修了3号课程的学生学号，姓名、系别。4、求数学系且选修了3号课程的学生学号，姓名、系别。5、查询至少选修了一门其直接先行课号为“6”号的课程的学生的学号。6、查询选修数据库这门课程的学生的学号、姓名、系别。,1、求选了1号课程的学生的学号、姓名、系别。Selectstudent.sno,sname,sdeptFromstudent,scWherestudent.sno=sc.snoandcno=12、求选修了2号课程且分数大于90分的会计系学生的名字、学号、课程号与成绩。Selectstudent.sno,sname,cno,gradeFromstudent,scWherestudent.sno=sc.snoand(cno=2andgrade90andsdept=会计),3、求数学系或选修了3号课程的学生学号，姓名、系别。Selectstudent.sno,sname,sdeptFromstudent,scWherestudent.sno=sc.snoand(sdept=maorcno=3)4、求数学系且选修了3号课程的学生学号，姓名、系别。Selectstudent.sno,sname,sdeptFromstudent,scWherestudent.sno=sc.snoand(sdept=maandcno=3),5、查询至少选修了一门其直接先行课号为“6”号的课程的学生的学号。SelectsnoFromsc,courseWo=oandcpno=66、查询选修数据库这门课程的学生的学号、姓名、系别。Selectsno,sname,sdeptFromstudent,sc,courseWherestudent.sno=o=oandcname=“数据库”,3.3.3嵌套查询(子查询)将一个查询块嵌套在另一个查询块的WHERE字句或HAVING短语的条件中的查询.例:SELECTSnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno=2);注意：内层查询不能含有ORDERBY子句，ORDERBY永远只能对最终查询结果排序。,外层查询或父查询或主查询,子查询或内层查询,1.带有IN谓词的子查询父查询和子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果集中。例47、查询与“刘晨”在同一个系学习的学生.传统做法可以分步进行：(1)确定“刘晨”所在的系SELECTSdeptFROMStudentWHERESname=刘晨;其结果是集合：IS.,(2)查找所有在IS系学习的学生.SELECTSno,Sname,SdeptFROMStudentWHERESdeptINIS;用第一步中的查询语句代替集合IS，且用小括号将该语句括起来。得到嵌套的SQL查询语句为:SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨);,用连接查询，得到：Selects1.sno,s1.sname,s1.sdeptFromstudents1,students2Wheres1.sdept=s2.sdeptands2.sname=“刘晨”,例48、求选了1号课程的学生的学号、姓名、系别。Selectsno,sname,sdeptFromstudentWheresnoin(selectsnofromscwherecno=1),例49、求选修了2号课程且分数大于90分的计算机系学生的名字、学号。Selectsno,snameFromstudentWheresnoin(selectsnofromscwherecno=2andgrade90)andsdept=CS,例50、查询选修数据库这门课程的学生的学号、姓名、系别Selecsno,sname,sdeptFromstudentWheresnoin(selectsnofromscwherecnoin(selectcnofromcoursewherecname=数据库),例50_2、查询没有选修一门先行课为6号的课程的学生的学号、姓名、系别Selecsno,sname,sdeptFromstudentWheresnonotin(selectsnofromscwherecnoin(selectcnofromcoursewherecpno=6),2.带有比较运算符的子查询带有比较运算符的子查询就是父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回单个结果时，可以用,=,等比较运算符。,如在例47中，查询与“刘晨”在同一个系学习的学生，因为一个学生只能在一个系学习，所以内层查询求出“刘晨”所在的系肯定只有一个结果，查询语句可写为：SELECTSno,Sname,SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=刘晨);,又在例50中查询选修数据库这门课程的学生的学号、姓名、系别，最内层求数据库课程的课程号时，结果也是唯一的，所以起SQL语句也可以写为：Selecsno,sname,sdeptFromstudentWheresnoin(selectsnofromscwherecno=(selectcnofromcoursewherecname=数据库),求出选了2号课程的且分数小于平均成绩的学生的学号，姓名与系别。Selectsno,sname,sdeptFromstudentWheresnoin(selectsnofromscwherecno=2andgrade85）,3、查询学生人数在100人以下的系别。SelectsdeptFromstudentGroupbysdeptHavingcount(sno)150),5、查询选修课程门数在三门以下的学生的学号、姓名、系别。Selectsno,sname,sdeptFromstudentWheresnoin(selectsnofromscgroupbysnohavingcount(cno)=85),例61、查询选修了95002所选的90分以上的全部课程的学生的名字。SelectsnameFromstudentWherenotexists(select*fromscsc1wheresno=95002andgrade=90andnotexists(select*fromscsc2wheresno=o=o),5.集合查询集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。例62、查询计算机科学系的学生及年龄不大于19岁的学生。SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19;,解法2、Select*FromstudentWheresdept=CSorsage=19,例63、查询计算机科学系的且年龄不大于19岁的学生。(即求计算机科学系学生集合与不大于19岁的学生的交集)Select*FromstudentWheresdept=CSandsage=90,例7、将student表的所有女生记录建一个视图。Createviewf_student(stdnum,name,sex,age,dept)AsSelect*FromstudentWheressex=“女”由于该视图一旦建立后，student表就构成了视图定义的一部分，如果以后修改了基本表student的结构，则student和fstudent表的映象关系遭到破坏，因而该视图就不能正确工作了。,2.删除视图DROPVIEW;一个视图被删除后,由该视图导出的其他视图也将失效。例8、将女生数据视图删除。Dropviewf_student,3.5.3查询视图视图定义后,用户就可以像对基本表进行查询一样对视图进行查询了。例1中建立信息系学生的视图如下：CREATEVIEWIS-StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=IS;,例9、在信息系学生的视图中，找出年龄小于20岁的学生的学号、姓名、年龄。SELECTSno,Sname,SageFROMIS_StudentWHEREsage=90Groupbysno,但实际上，WHERE子句中是不能用集函数作为条件表达式的，因此执行此修正后的查询将会出现语法错误。正确的查询语句是：Selectsno,avg(grade),sum(grade),min(grade)FromscGroupbysnohavingavg(grade)=90,3.5.3更新视图,更新视图包括插入、删除、和修改三种。由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。为防止用户通过视图对数据进行增、删、改时，无意或故意操作不属于视图范围内的基本表数据，可在定义视图时加上WITHCHECKOPTION子句，这样，在视图上增、删、改数据时，DBMS会进一步检查视图定义中的条件，若不满足此条件，则拒绝该操作。,例11、将信息系学生视图IS_student中学号为95002的学生姓名改为“刘辰”（理论和实际都可以）UpdateIS_studentSetsname=“刘辰”Wheresno=95002转化为：UpdatestudentSetsname=“刘辰”Wheresno=95002andsdept=IS,例12、向信息系学生视图IS_student中插入一个新的学生记录，其中学号为95029，姓名为赵新，年龄为20岁。（理论可以，实际不可以）InsertintoIS_studentValues(95029,赵新,20)转换后：InsertintostudentValues(95029,赵新,20,IS),但并不是所有的视图都是可更新的，因为有些视图的更新不能唯一有意义转换为对相应基本表的更新。（理论和实际都不可以）例如，updatestu_avgsetsavg=90wheresno=95001对这个视图的更新是无法转换成对基本表SC的更新的，因为系统无法修改各科成绩，以使平均成绩为90。所以stu_avg视图是不可更新的。目前各个关系系统一般都只允许对行列子集视图的更新。,3.5.4视图的用途视图是定义在基本表之上的，对视图的一切操作最终也要转换为对基本表的操作。虽然对于非行列视图进行查询或更新时还有可能出现问题，但合理的视图有以下优点：1、视图能够简化用户的操作2、视图使用户能以多种角度看待同一数据3、视图对重构数据库提供了一定程度的逻辑独立性4、视图能够对机密数据提供安全保护,3.6数据库安全控制,数据库的安全性是指保护数据库，防止不合法的使用所造成的数据泄露和破坏。用户名和口令自主存取控制数据库中保证数据安全性的主要措施是进行存取控制，即规定不同用户对于不同数据对象所允许执行的操作，并控制各用户只能存取他有权存取的数据。不同的用户对不同的数据应具有何种操作权力，是由DBA和表的建立者根据具体情况决定的，SQL语言则为DBA和表的属主定义与回收这种权力提供了手段。,1、授权SQL语言用GRANT语句向用户授予操作权限，GRANT语句的一般格式为：GRANT，ONTO，WITHGRANTOPTION;其功能是：把对指定对象的指定的操作权限授给指定的用户。,不