数据库基础教程PPT课件

1、第四章SQL,.,2,提纲,SQL概述SQL数据定义功能SQL数据查询功能SQL数据修改功能SQL数据控制功能嵌入式SQL,.,3,SQL概述,历史SQL:StruceuredQueryLanguang1974年，由Boyce和Chamber提出1975-1979年，在SystemR上实现，由IBM的SanJose研究室研制，称为Sequel,.,4,SQL概述,用户1,用户2,用户4,视图V1,视图V1,用户3,基本表B1,基本表B2,基本表B4,基本表B3,存储文件S1,存储文件S2,存储文件S4,存储文件S3,SQL数据库体系结构,SQL用户,VIEW,BaseTable,Storedf

2、ile,.,5,SQL概述,标准化有关组织ANSI(AmericanNaturalStandardInstitute)ISO(InternationalOrganizationforStandardization)有关标准SQL-86“数据库语言SQL”SQL-89“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持SQL-92“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。SQL-3正在讨论中的新的标准，将增加对面向对象模型的支持,.,6,SQL概述,特点一体化集DDL，DML，DCL于一体单一的结构-关系

3、，带来了数据操作符的统一面向集合的操作方式一次一集合高度非过程化用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径两种使用方式，统一的语法结构SQL既是自含式语言（用户使用），又是嵌入式语言（程序员使用）语言简洁，易学易用,.,7,SQL概述,.,8,示例关系,DEPT(D#,DNAME,DEAN)S(S#,SNAME,SEX,AGE,D#)COURSE(C#,CN,PC#,CREDIT)SC(S#,C#,SCORE)PROF(P#,PNAME,AGE,D#,SAL)PC(P#,C#),.,9,SQL数据定义功能,域定义基本表的定义索引的定义数据库的建立与撤消SQL数据定义特点,.,

4、10,域定义,域类型（SQL-92）char（n）：固定长度的字符串varchar（n）：可变长字符串int：整数smallint：小整数类型numeric（p，d）：定点数，小数点左边p位，右边q位real：浮点数doubleprecision：双精度浮点数date：日期（年、月、日）time：时间（小时、分、秒）interval：两个date或time类型数据之间的差,.,11,域定义,域定义格式createdomain域名数据类型示例createdomainperson-namechar（20）类似C语言中：typedefADDRESS_LISTcharname10;charteleph

5、one20;charlocation20charemail20;ADDRESS_LISTtom;,.,12,基本表的定义,基本表的定义（CREATE）格式createtable表名（列名数据类型default缺省值notnull，列名数据类型default缺省值notnull，primarykey（列名，列名），foreignkey（列名，列名）references表名（列名，列名），check（条件）,.,13,基本表的定义,示例CREATETABLES(S#CHAR（4）,SNAMECHAR（8）NOTNULL,AGESMALLINT,SEXCHAR（1）,PRIMARYKEY(S#),C

6、HECK(SEX=0ORSEX=1),.,14,基本表的定义,CREATETABLEC(C#CHAR（4）,CNAMECHAR（10）NOTNULL,TEACHERCHAR（8）,PRIMARYKEY(C#),),.,15,基本表的定义,CREATETABLESC(S#CHAR（4）,C#CHAR（4）,GRADESAMLLINT,PRIMARYKEY(S#，C#),FOREIGNKEY(S#)REFERENCESS(S#),FOREIGNKEY(C#)REFERENCESC(S#),CHECK(GRADEISNULL)ORGRADEBETWEEN0AND100),.,16,基本表的定义,示例

7、createdomainperson_namechar(20)createtablePROF(PNOchar10,person_namePNAMEnotnull,SALint,AGEint,DNOchar10,primarykey(PNO),foreignkey(DNO)referencesDEPT(DNO),check(SAL0),.,17,基本表的定义,修改基本表定义（ALTER）格式：altertable表名add子句增加新列drop子句删除列modify子句修改列定义示例altertablePROFaddLOCATIONchar30,.,18,基本表的定义,撤消基本表定义（drop）格

8、式droptable表名示例droptableDEPTDANGER撤消基本表后，基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除,.,19,索引的定义,索引的定义格式createunique/distinctclusterindex索引名on表名(列名asc/desc,列名asc/desc)unique（distinct）：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新cluster：聚集索引，表中元组按索引项的值排序并物理地聚集在一起。一个基本表上只能建一个聚集索引asc/desc：索引表中

9、索引值的排序次序，缺省为asc,.,20,索引的定义,示例：createclusterindexs-indexonS（S#）索引的删除格式dropindex索引名,.,21,索引的定义,索引的有关说明可以动态地定义索引，即可以随时建立和删除索引不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性应该在使用频率高的、经常用于连接的列上建索引一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率,.,22,数据库的建立与撤消,有的数据库系统支持多库建立一个新数据库createdatabase数据库名撤消一个数据库dropdat

10、abase数据库名指定当前数据库database数据库名指定当前数据库closedatabase数据库名,.,23,SQL数据定义特点,SQL中，任何时候都可以执行一个数据定义语句，随时修改数据库结构。而在非关系型的数据库系统中，必须在数据库的装入和使用前全部完成数据库的定义。若要修改已投入运行的数据库，则需停下一切数据库活动，把数据库卸出，修改数据库定义并重新编译，再按修改过的数据库结构重新装入数据数据库定义不断增长（不必一开始就定义完整）数据库定义随时修改（不必一开始就完全合理）可进行增加索引、撤消索引的实验，检验其对效率的影响,.,24,SQL数据查询功能,SQL数据查询基本结构sele

11、ct子句重复元组的处理from子句where子句更名运算字符串操作元组显示顺序集合操作,.,25,SQL数据查询功能,分组和聚集函数空值嵌套子查询派生关系视图关系的连接,.,26,SQL数据查询基本结构,基本结构selectA1,A2,Anfromr1,r2,rmwherePA1,A2,An(p(r1r2rm),.,27,SQL数据查询基本结构,示例给出所有老师的姓名selectPNAMEfromPROF,.,28,select子句,目标列形式可以为列名，*，算术表达式，聚集函数“*”：表示“所有的属性”给出所有老师的信息select*fromPROF,.,29,select子句,带，的算术表

12、达式给出所有老师的姓名及税后工资额selectPNAME，SAL0.95fromPROF,.,30,重复元组的处理,语法约束缺省为保留重复元组，也可用关键字all显式指明。若要去掉重复元组，可用关键字distinct或unique指明示例找出所有选修课程的学生selectdistinctSNOfromSC,.,31,from子句,说明from子句列出查询的对象表当目标列取自多个表时，在不混淆的情况下可以不用显式指明来自哪个关系示例例：找出工资低于500的职工的姓名、工资、系别selectPNAME,SAL,DNAMEfromPROF,DEPTwhereSALP2.SAL,.,38,字符串操作,

13、命令格式格式列名notlike“字符串”找出满足给定匹配条件的字符串,.,39,字符串操作,匹配规则“%”匹配零个或多个字符“”匹配任意单个字符Escape定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待如escape“”，定义作为转义字符，则可用%去匹配%，用去匹配,思考：用什么去匹配？,.,40,字符串操作,示例列出姓名以“张”打头的教师的所有信息select*fromPROFwherePNAMElike“张%”,.,41,字符串操作,列出名称中含有4个字符以上，且倒数第3个字符是d，倒数第2个字符是_的系的所有信息select*fromPROFwherePNAMElike“

14、%d”,.,42,元组显示顺序,命令orderby列名asc|desc示例按系名升序列出老师姓名，所在系名，同一系中老师按姓名降序排列selectDNAME，PNAMEfromPROF，DEPTwherePROF.DNO=DEPT.DNOorderbyDNAMEasc，PNAMEdesc,.,43,集合操作,命令集合并：union集合交：intersect集合差：except提示集合操作自动去除重复元组，如果要保留重复元组的话，必须用all关键词指明,.,44,集合操作,示例求选修了001或（且）002号课程的学生号(selectSNOfromSCwhereCNO=001)union(inte

15、rsect)(selectSNOfromSCwhereCNO=002),.,45,集合操作,求选修了001和002号而没有选003号课程的学生号(selectSNOfromSCwhereCNO=001orCNO=002)except(selectSNOfromSCwhereCNO=003),.,46,分组和聚集函数,分组命令groupby列名having条件表达式groupby将表中的元组按指定列上值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值having则对分组进行选择，只将聚集函数作用到满足条件的分组上,.,47,分组和聚集函数,列出每个学生的平均成绩,列出每门课程的平均成绩,

16、groupbyS#,groupbyC#,92,85,90,92,92,90,.,48,分组和聚集函数,聚集函数平均值：avg最小值：min最大值：max总和：sum记数：count,count（*）与count（列名）的差别,.,49,分组和聚集函数,示例列出各系的老师的最高、最低、平均工资selectDNO，max(SAL)，min(SAL)，avg(SAL)fromPROFgroupbyDNO,.,50,分组和聚集函数,火眼金睛求选修了课程的学生人数selectcount(SNO)fromSCselectPNAME，max(SAL)fromPROFselectDNO，avg(SAL)fro

17、mPROFgroupbyDNOwhereAGE60,.,51,分组和聚集函数,列出及格的学生的平均成绩selectSNO，avg(SCORE)fromSCgroupbySNOhavingmin(SCORE)=60selectSNO，avg(SCORE)fromSCwhereSCORE=60groupbySNO,哪个正确？,.,52,分组和聚集函数,列出每一年龄组中男学生（超过50人）的人数selectAGE，count(S#)fromSwhereSEX=MgroupbyAGEhavingcount(*)50,.,53,空值,空值测试isnotnull测试指定列的值是否为空值示例找出年龄值为空的

18、老师姓名selectPNAMEfromPROFwhereAGEisnull不可写为whereAGE=null,.,54,空值,注意事项除isnotnull之外，空值不满足任何查找条件如果null参与算术运算，则该算术表达式的值为null如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null,.,55,空值,例：selectsum(G)fromSC例：selectcount(*)fromSC,360,6,.,56,嵌套子查询,集合成员资格集合之间的比较集合基数的测试测试集合是否为空测试集合

19、是否存在重复元组,.,57,集合成员资格,in子查询表达式notin（子查询）判断表达式的值是否在子查询的结果中示例列出张军和王红同学的所有信息select*fromSwhereSNAMEin（“张军”，“王红”）,.,58,集合成员资格,选修了001号课程的学生的学号及姓名selectSNO，SNAMEfromSwhereSNOin(selectSNOfromSCwhereCNO=001）,.,59,集合成员资格,列出选修了001号和002号课程的学生的学号selectSNOfromSCwhereSC.CNO=001andSNOin（selectSNOfromSCwhereCNO=002）,

20、.,60,集合之间的比较,some/all子查询表达式比较运算符some（子查询）表达式的值至少与子查询结果中的一个值相比满足比较运算符表达式比较运算符all（子查询）表达式的值与子查询结果中的所有的值相比都满足比较运算符,.,61,集合之间的比较,示例找出平均成绩最高的学生号selectSNOfromSCgroupbySNOhavingavg(SCORE)=all（selectavg(SCORE)fromSCgroupbySNO）,.,62,集合基数的测试,测试集合是否为空notexists（子查询）判断子查询的结果集合中是否有任何元组存在,in后的子查询与外层查询无关，每个子查询执行一次，

21、而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询,.,63,集合基数的测试,列出选修了01号课程的学生的学号及姓名selectSNO，SNAMEfromSwhereexists（select*fromSCwhereCNO=01andSNO=S.SNO）,.,64,集合基数的测试,列出选修了001号和002号课程的学生的学号selectSNOfromSCSC1whereSC1.CNO=001andexists（selectSNOfromSCSC2whereSC2.CNO=002andSC2.SNO=SC1.SNO）,.,65,列出选修了全部课程的学生姓名selectSNAM

22、EfromSwherenotexists（selectC#fromCwherenotexists（select*fromSCwhereSC.C#=C.C#andSC.S#=S.S#）,任意课程，所求学生选之不存在任何一门课程，所求学生没有选之,集合基数的测试,列出至少选修了001号学生选修的所有课程的学生名selectSNAMEfromSwherenotexists（selectCNOfromCOURSEwhereexists（select*fromSCwhereSC.CNO=COURSE.CNOandSC.SNO=001）andnotexists（select*fromSCwhereSC.C

23、NO=COURSE.CNOandSC.SNO=S.SNO）,任意课程，001号学生选之，所求学生选之不存在任何一门课程，001号学生选之，所求学生没有选之,.,67,集合基数的测试,测试集合是否存在重复元组unique(子查询）如果子查询结果中没有重复元组，则返回true,.,68,集合基数的测试,示例找出所有只教授一门课程的老师姓名selectPNAMEfromPROFwhereunique（selectPNOfromPCwherePC.PNO=PROF.PNO),.,69,集合基数的测试,找出至少选修了两门课程的学生姓名selectSNAMEfromSwherenotunique（sele

24、ctSNOfromSCwhereSC.SNO=S.SNO),toTRUEornottoTRUE,thatisthequestionunique(a,b,null),(a,b,null)?,.,70,派生关系,命令（子查询）as关系名（列名，列名，）SQL-92中，允许在from子句中使用子查询表达式，这时可将该子查询的结果命名为一个临时关系加以引用,派生关系Vs视图？,.,71,派生关系,示例找出平均成绩及格的学生先求出每个学生的平均成绩，再从中找出及格的学生selectSNAME，avg（SCORE）fromS，SCwhereSC.SNO=S.SNOgroupbySC.SNO,.,72,派生

25、关系,selectSNAME，AVG_SCOREfrom(selectSNAME，avg（SCORE）fromS，SCwhereSC.SNO=S.SNOgroupbySC.SNO)asresult(SNAME，AVG_SCORE)whereAVG_SCORE=60,.,73,视图,定义视图createviewview_name(列名，列名)as（查询表达式）withcheckoption视图的属性名缺省为子查询结果中的属性名，也可以显式指明withcheckoption指明当对视图进行insert，update时，要检查是否满足视图定义中的条件撤消视图dropviewview_name,.,7

26、4,视图,示例createviewCOMPUTER_PROFas(selectPNO,PNAME,SALfromPROF，DEPTwherePROF.PNO=DEPT.PNOandDEPT.DNAME=“计算机系”）,.,75,视图,createviewDEPTSAL(DNO,LOW,HIGH,AVERAGE,TOTAL)as(selectDNO,min(SAL),max(SAL),avg(SAL),sum(SAL)fromPROFgroupbyDNO),.,76,视图,给出计算机系工资超过800的老师姓名selectPNAMEfromCOMPUTER_PROFwhereSAL800给出计算机

27、系老师的最低、最高、平均工资以及工资总额selectLOW,HIGH,AVERAGE,TOTALfromDEPTSAL,DEPTwhereDEPTSAL.DNO=DEPT.DNOandDEPT.DNAME=“计算机系”,.,77,关系的连接,基本分类连接成分包括两个输入关系、连接条件、连接类型连接条件决定两个关系中哪些元组相互匹配，以及连接结果中出现哪些属性连接类型决定如何处理与连接条件不匹配的元组,.,78,关系的连接,.,79,关系的连接,自然连接出现在结果关系中的两个连接关系的元组在公共属性上取值相等，且公共属性只出现一次on出现在结果关系中的两个连接关系的元组在公共属性上取值满足谓词条

28、件P，且公共属性出现两次using(A1,A2,An)(A1,A2,An)是两个连接关系的公共属性的子集，元组在(A1,A2,An)上取值相等，且(A1,A2,An)只出现一次,.,80,关系的连接,内连接舍弃不匹配的元组左外连接内连接+左边关系中失配的元组（缺少的右边关系属性值用null表示）右外连接内连接+右边关系中失配的元组（缺少的左边关系属性值用null表示）全外连接内连接+左边关系中失配的元组（缺少的右边关系属性值用null表示）+右边关系中失配的元组（缺少的左边关系属性值用null表示）,.,81,关系的连接,crossjoin两个关系的笛卡儿积unionjoin左边关系中失配的元

29、组+右边关系中失配的元组对于外连接，连接条件是必须的对于内连接，连接条件是可选的，没有连接条件等价于两个关系的笛卡儿积,.,82,关系的连接,列出老师的教工号、姓名、工资、所教课程号selectPNO，PNAME，SAL，CNOfrom（PROFnatureleftouterjoinPC）,.,83,关系的连接,RinnerjoinSonR.C=S.C,R,S,.,84,关系的连接,RleftouterjoinSonR.C=S.C,RnaturerightouterjoinS,.,85,关系的连接,RfullouterjoinSonR.C=S.C,.,86,SQL的数据修改功能,插入删除修改视

30、图更新,.,87,插入操作,命令insertinto表名（列名，列名values（值，值）插入一条指定好值的元组insertinto表名（列名，列名（子查询）插入子查询结果中的若干条元组,.,88,插入操作,示例insertintoPROFvalues(P123,“王明”,35,D08,498)insertintoPROF(PNO,PNAME,DNO)values(P123,“王明”,D08)思考：SAL取何值？,如何防止插入带有空值的元组？,.,89,插入操作,将平均成绩大于90的学生加入到EXCELLENT中insertintoEXCELLENT(SNO,GRADE)selectSNO,a

31、vg(SCORE)fromSCgroupby(SNO)havingavg(SCORE)90,.,90,插入操作,不支持修改在子查询中出现的表,insertintoPROFselect*fromPROF,若支持，则完成查询后，再执行修改操作,.,91,删除操作,命令deletefrom表名where条件表达式从表中删除符合条件的元组，如果没有where语句，则删除所有元组,.,92,删除操作,示例清除所有选课记录deletefromSC删除王明老师所有的任课记录deletefromPCwherePNOin(selectPNOfromPROFwherePNAME=“王明”),.,93,删除操作,删

32、除低于平均工资的老师记录deletefromPROFwhereSAL=15)ANDVALUE=15ANDAGE=all(selectcount(SC.S#)fromS,SCwhereS.S#=SC.S#andSEX=MgroupbyC#)撤消：dropassertion断言名dropassertionASSE1,.,128,SQL中完整性约束,触发器触发器是一条语句，当对数据库做修改时，它自动被系统执行触发器的定义指明什么条件下触发器被执行指明触发器执行的动作是什么触发器的作用示警满足特定条件时自动执行某项任务触发器事件Insert、delete、update,.,129,SQL中完整性约束,

33、.,130,SQL中完整性约束,EMP(ENO,ENAME,SAL,JOB)职工工资增幅不得超过10%createtriggerRAISE_LIMITafterupdateofSALonEMPreferencingnewrowasnrowoldrowasorowforeachrowwhen(nrow.SAL1.1*orow.SAL)beginatomicsignalSQLSTATE7500(“Salaryincrease10%)end,.,131,SQL中完整性约束,当帐户透支时，将帐户余额设为0，并建一笔贷款，其金额为透支额createtriggeroverdraft-triggerafte

34、rupdateonaccountreferencingnewrowasnrowforeachrowwhennrow.balance0：取到主变量的值发生了截断，指示变量的值是截断前的字符串的实际长度,.,140,需要解决的几个问题,指示变量的用法声明与宿主变量的声明方式一样，在数据操纵语句中，在宿主变量和指示变量之间加(:)或关键字indicatorEXECSQLBEGINDECLARESECTIONintprof_no;charprof_name30;intsalary;shortname_id;shortsal_id;EXECSQLENDDECLARESECTIONEXECSQLselec

35、tPNAME,SALinto:prof_name:name_id,:salary:sal_idfromPROFwherePNO=prof_no;,.,141,需要解决的几个问题,SQL与主语言之间操作方式的协调执行方式的差别SQL：一次一集合C语言：一次一记录游标在查询结果的记录集合中移动的指针若一个SQL语句返回单个元组，则不用游标若一个SQL语句返回多个元组，则使用游标,.,142,需要解决的几个问题,不需要游标的数据操作结果是一个元组的select语句EXECSQLselectPNAME,SALinto:prof_name:name_id,:salary:sal_idfromPROFwh

36、erePNO=prof_no;,.,143,需要解决的几个问题,insert语句EXECSQLinsertintoPROFvalues(:prof_no,:prof_name,:salary,:dept_no,:salary);delete语句EXECSQLdeletefromPROFvaluesPNO:prof_no;update语句EXECSQLupdatePROFsetSAL=:salarywherePNO=:prof_no;,.,144,需要解决的几个问题,需要游标的数据操作当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组活动集：selecT语句返回的元组的集合当前行：活动集中