数据库6版讲稿第四章----中级SQL(1)

1、4.1 连接表达式连接表达式l基本分类基本分类 连接成分连接成分l包括两个输入关系、连接条件、连接类型包括两个输入关系、连接条件、连接类型 连接条件连接条件l决定两个关系中哪些元组相互匹配，以及连接决定两个关系中哪些元组相互匹配，以及连接结果中出现哪些属性结果中出现哪些属性 连接类型连接类型l决定如何处理与连接条件不匹配的元组决定如何处理与连接条件不匹配的元组关系的连接关系的连接natureon using (A1, A2 , An)inner joinleft outer joinright outer joinfull outer join连接条件连接条件连接类型连接类型关系的连接关系的连

2、接 自然连接自然连接l出现在结果关系中的两个连接关系的元组在公共属性出现在结果关系中的两个连接关系的元组在公共属性上取值相等，且公共属性只出现一次上取值相等，且公共属性只出现一次 on on Pl出现在结果关系中的两个连接关系的元组在公共属性出现在结果关系中的两个连接关系的元组在公共属性上取值满足谓词条件上取值满足谓词条件P P，且公共属性出现两次，且公共属性出现两次 using (Ausing (A1 1, A, A2 2 , A , An n) )l (A (A1 1, A, A2 2 , A , An n) )是两个连接关系的公共属性的子集，是两个连接关系的公共属性的子集，元组在元组在(

3、A(A1 1, A, A2 2 , A , An n) )上取值相等，且上取值相等，且(A(A1 1, A, A2 2 , A , An n) )只出现一次只出现一次关系的连接关系的连接 内连接内连接l舍弃不匹配的元组舍弃不匹配的元组 左外连接左外连接l内连接内连接+ +左边失配的元组（缺少的右边关系属性用左边失配的元组（缺少的右边关系属性用nullnull） 右外连接右外连接l内连接内连接+ +右边失配的元组（缺少的左边关系属性用右边失配的元组（缺少的左边关系属性用nullnull） 全外连接全外连接l内连接内连接 + + 左边失配的元组（缺少的右边关系属性用左边失配的元组（缺少的右边关系属

4、性用nullnull）+ + 右边失配的元组（缺少的左边关系属性用右边失配的元组（缺少的左边关系属性用nullnull）关系的连接关系的连接 cross joincross joinl两个关系的笛卡儿积两个关系的笛卡儿积 union joinunion joinl左边关系中失配的元组左边关系中失配的元组+ + 右边关系中失配的元组右边关系中失配的元组l对于外连接，连接条件是必须的对于外连接，连接条件是必须的l对于内连接，连接条件是可选的，没有连接条件等价于两对于内连接，连接条件是可选的，没有连接条件等价于两个关系的笛卡儿积个关系的笛卡儿积关系的连接关系的连接 列出老师的教工号、姓名、工资、所教

5、课程号列出老师的教工号、姓名、工资、所教课程号select PNOselect PNO，PNAMEPNAME，SALSAL，CNOCNOfrom from （PROF nature left outer join PCPROF nature left outer join PC）关系的连接关系的连接ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3R inner join inner join S on on R.C = S.CABCCDa1b1c1c1d1a2b2c2c2d2RS关系的连接关系的连接R left outer join left outer join S o

6、n on R.C = S.CABCCDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnullR nature right outer join nature right outer join SABCDa1b1c1d1a2b2c2d2nullnullc4d3关系的连接关系的连接R full outer join full outer join S onon R.C = S.CABR.CS.CDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnullnullnullnullc4d34.2 视图视图l定义视图定义视图create viewcreate view view_

7、name( view_name(列名列名 ，列名，列名 ) ) asas （查询表达式）（查询表达式） 视图的属性名缺省为子查询结果中的属性名，也可视图的属性名缺省为子查询结果中的属性名，也可以显式指明以显式指明 视图名可以出现在任何关系名可以出现的地方视图名可以出现在任何关系名可以出现的地方l撤消视图撤消视图drop viewdrop view view_name view_name视图视图l示例示例create view COMPUTER_PROFcreate view COMPUTER_PROFas (select PNO , PNAME , SAL as (select PNO , P

8、NAME , SAL from PROF from PROF，DEPTDEPT where PROF.DNO = DEPT.DNO where PROF.DNO = DEPT.DNO and DEPT.DNAME = “ and DEPT.DNAME = “计算机系计算机系”）视图视图create viewcreate view DEPTSAL( DNO, LOW, HIGH, AVERAGE, TOTAL )asas ( selectselect DNO, minmin(SAL), maxmax(SAL), avgavg(SAL), sumsum(SAL) fromfrom PROF gro

9、up by group by DNO )视图视图 给出计算机系工资超过给出计算机系工资超过800800的老师姓名的老师姓名select PNAMEselect PNAMEfrom COMPUTER_PROFfrom COMPUTER_PROFwhere SAL 800where SAL 800 给出计算机系最低、最高、平均工资以及工资总额给出计算机系最低、最高、平均工资以及工资总额select LOW , HIGH , AVERAGE , TOTALselect LOW , HIGH , AVERAGE , TOTALfrom DEPTSAL , DEPTfrom DEPTSAL , DEPT

10、where DEPTSAL.DNO = DEPT.DNOwhere DEPTSAL.DNO = DEPT.DNO and DEPT.DNAME =“ and DEPT.DNAME =“计算机系计算机系”视图更新视图更新 视图更新最终是对所涉及的实表的更新。视图更新最终是对所涉及的实表的更新。 更新的条件更新的条件lFrom From 是一个表是一个表lSelect Select 中没有广义投影、聚集、中没有广义投影、聚集、distinctdistinctl限制限制null null l不含不含group by group by 或或 having having 子句子句 4.2 完整性约束完整

11、性约束l完整性完整性 数据的正确性和相容性数据的正确性和相容性l完整性检查完整性检查 DBMS必须提供一种机制来检查数据库中的数据是必须提供一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据是正确的，否满足规定的条件，以保证数据库中数据是正确的，避免非法的不合语义的错误数据的输入和输出，即避免非法的不合语义的错误数据的输入和输出，即所谓的所谓的“垃圾进垃圾出垃圾进垃圾出”(Garbage In Garbage Out)所造成的无效操作和错误结果所造成的无效操作和错误结果完整性约束完整性约束l完整性子系统完整性子系统 数据库的非法更新数据库的非法更新l数据本身是错误的数据本身是错

12、误的l数据原来是正确的，操作或程序错误导致输入数据错误数据原来是正确的，操作或程序错误导致输入数据错误l由于系统故障，导致数据错误由于系统故障，导致数据错误l事务的并发执行产生不正确结果事务的并发执行产生不正确结果l人为故意破坏人为故意破坏 完整性子系统功能完整性子系统功能l监督事务执行，检查是否违反完整性规则监督事务执行，检查是否违反完整性规则l如有违反，采取相应措施（拒绝、报告、改正如有违反，采取相应措施（拒绝、报告、改正）完整性控制完整性控制l完整性规则完整性规则 完整性规则集完整性规则集l由由DBADBA或程序员事先提供的有关数据约束的一组规则或程序员事先提供的有关数据约束的一组规则

13、规则的构成规则的构成l约束条件：要检查什么样的错误（性别为男女）约束条件：要检查什么样的错误（性别为男女）l触发条件：什么时候使用规则进行检查（插入元组）触发条件：什么时候使用规则进行检查（插入元组）lELSEELSE子句：若检查出错误，该怎样进行处理（显示错误）子句：若检查出错误，该怎样进行处理（显示错误） 规则的分类规则的分类l域完整性规则：属性取值范围域完整性规则：属性取值范围l域联系规则：域联系规则：在一个或多个关系中在一个或多个关系中, ,属性间的联系影响约束属性间的联系影响约束l关系完整性规则：更新操作对数据库中值的影响和限制关系完整性规则：更新操作对数据库中值的影响和限制4.4

14、完整性约束完整性约束l在属性值上的约束在属性值上的约束 非空约束：要求某属性取值不能为空值非空约束：要求某属性取值不能为空值lSNAME CHAR(8) SNAME CHAR(8) NOT NULLNOT NULL 基于属性的检查子句基于属性的检查子句lCHECKCHECK(AGE15)(AGE15) 域约束子句域约束子句l用用CREATE DOMAINCREATE DOMAIN定义域时，可以出现定义域时，可以出现 CHECKCHECKlCREATE DOMAINCREATE DOMAIN AGE SMALLINT AGE SMALLINT CHECKCHECK(VALUE = 15) AND

15、 VALUE = 15) AND VALUE = 25)完整性控制完整性控制l主码约束主码约束 主码值不允许空，也不允许出现重复主码值不允许空，也不允许出现重复 意义：关系对应到现实世界中的实体集，元组对应意义：关系对应到现实世界中的实体集，元组对应到实体，实体是相互可区分的，通过主码来唯一标到实体，实体是相互可区分的，通过主码来唯一标识，若主码为空，则出现不可标识的实体，这是不识，若主码为空，则出现不可标识的实体，这是不容许的容许的 主码定义形式主码定义形式l主码子句：主码子句：PRIMARY KEYPRIMARY KEY(Sno)(Sno)l主码短语：主码短语：Sno CHAR(4) Sn

16、o CHAR(4) PRIMARY KEYPRIMARY KEY完整性控制完整性控制l外码约束（参照完整性）外码约束（参照完整性） 关系关系R R中的一个属性组，它不是中的一个属性组，它不是R R的码，但它与另一个的码，但它与另一个关系关系S S的码相对应，则称这个属性组为的码相对应，则称这个属性组为R R的外部码，的外部码， 如如 S S关系中的关系中的DnoDno属性属性 如果关系如果关系R R2 2的外部码的外部码F Fk k与关系与关系R R1 1的主码的主码P Pk k相对应，则相对应，则R R2 2中的每一个元组的中的每一个元组的F Fk k值或者等于值或者等于R R1 1 中某个

17、元组的中某个元组的P Pk k 值，值，或者为空值或者为空值 意义：如果关系意义：如果关系R R2 2的某个元组的某个元组t t2 2参照了关系参照了关系R R1 1的某的某个元组个元组t t1 1，则，则t t1 1必须存在必须存在 例如关系例如关系S S在在DnoDno上的取值有两种可能上的取值有两种可能 空值，表示该学生尚未分到任何系中；若非空值，则必须是空值，表示该学生尚未分到任何系中；若非空值，则必须是DEPTDEPT关关系中某个元组的系中某个元组的DnoDno值，表示该学生不可能分到一个不存在的系中值，表示该学生不可能分到一个不存在的系中完整性控制完整性控制 定义形式定义形式l在在

18、SCSC表中，定义表中，定义 FOREIGN KEYFOREIGN KEY (Sno) (Sno) REFERENCES REFERENCES S(Sno) S(Sno)lSnoSno在在SCSC中是外码，在中是外码，在S S中是主码，中是主码，SnoSno在在SCSC中取中取值或为空或为值或为空或为S S中某个中某个SnoSno值值l作为主码的关系称为基本（参照）关系，作为外作为主码的关系称为基本（参照）关系，作为外码的关系称为依赖关系码的关系称为依赖关系完整性控制完整性控制 删除基本关系元组删除基本关系元组lRESTRICTRESTRICT方式方式 只有当依赖关系中没有一个外码值与要删除的

19、基本关系的主码只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时，才可以删除该元组，否则系统拒绝此删除操作值相对应时，才可以删除该元组，否则系统拒绝此删除操作lCASCADECASCADE方式方式 将依赖关系中所有外码值与基本关系中要删除的主码值所对应将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除的元组一起删除lSET NULLSET NULL方式方式 删除基本关系中元组时，将依赖关系中与基本关系中被删主码删除基本关系中元组时，将依赖关系中与基本关系中被删主码值相对应的外码值置为空值值相对应的外码值置为空值l如如FOREIGN KEY (Sno) REFER

20、ENCES S(Sno) ON FOREIGN KEY (Sno) REFERENCES S(Sno) ON DELETE CASCADE | SET NULL DELETE CASCADE | SET NULL 完整性控制完整性控制 修改基本关系主码修改基本关系主码lRESTRICTRESTRICT方式方式 只有当依赖关系中没有一个外码值与要修改的基本关系的主码只有当依赖关系中没有一个外码值与要修改的基本关系的主码值相对应时，才可以修改该元组主码，否则系统拒绝此次修改值相对应时，才可以修改该元组主码，否则系统拒绝此次修改lCASCADECASCADE方式方式 将依赖关系中所有与基本关系中要修

21、改的主码值所对应的外码将依赖关系中所有与基本关系中要修改的主码值所对应的外码值一起修改为新值值一起修改为新值lSET NULLSET NULL方式：方式： 修改基本关系中元组主码时，将依赖关系中与基本关系中被修修改基本关系中元组主码时，将依赖关系中与基本关系中被修改主码值相对应的外码值置为空值改主码值相对应的外码值置为空值l如如FOREIGN KEY (Sno) REFERENCES S(Sno) ON FOREIGN KEY (Sno) REFERENCES S(Sno) ON UPDATE CASCADE | SET NULL UPDATE CASCADE | SET NULL 完整性控制

22、完整性控制l全局约束全局约束 全局约束涉及多个属性间的或多个关系间的联系全局约束涉及多个属性间的或多个关系间的联系CREATE TABLE SCCREATE TABLE SC(Sno CHAR(4),(Sno CHAR(4), Cno CHAR(4), Cno CHAR(4), GRADE SMALLINT, GRADE SMALLINT, PRIMARY KEY (Sno, Cno), PRIMARY KEY (Sno, Cno), CHECK CHECK(Sno IN (SELECT Sno FROM S),(Sno IN (SELECT Sno FROM S), CHECK CHECK(

23、Cno IN (SELECT Cno FROM C)(Cno IN (SELECT Cno FROM C)S中删除元组，不会触发CHECK子句，只有对SC表的更新才会触发完整性控制完整性控制l对约束的命名、撤消和添加对约束的命名、撤消和添加 命名命名CONSTRAINTCONSTRAINT 约束名约束名 示例示例Sno CHAR(4) Sno CHAR(4) CONSTRAINTCONSTRAINT S_PK S_PK PRIAMRY KEYPRIAMRY KEYAGE SAMLLINT AGE SAMLLINT CONSTRAINTCONSTRAINT AGE_VAL AGE_VAL CHE

24、CKCHECK(AGE = 15 AND AGE = 15 AND AGE = 25)完整性控制完整性控制 关系上约束的撤消与添加关系上约束的撤消与添加撤消用撤消用alter dropalter drop添加用添加用alter addalter add 示例示例alteralter table S table S drop constraintdrop constraint S_PK S_PKalteralter table SC table SC add constraintadd constraint SC_CHECK SC_CHECK checkcheck(Sno in select S

25、no from S)(Sno in select Sno from S)完整性控制完整性控制 域约束的创建、撤消与添加域约束的创建、撤消与添加create domaincreate domain AGE_DOMAIN smallint AGE_DOMAIN smallintconstraintconstraint DC_AGE DC_AGE checkcheck(value = 25 and value (value = 15)= 15)alter domainalter domain AGE_DOMAIN AGE_DOMAINadd constraintadd constraint DC_A

26、GE check(value = 35 and DC_AGE check(value = 15)value = 15)alter domainalter domain AGE_DOMAIN AGE_DOMAINdrop constraindrop constrain DC_AGE DC_AGE完整性控制完整性控制 关系上约束的撤消与添加关系上约束的撤消与添加撤消用撤消用alter dropalter drop添加用添加用alter addalter add 示例示例alteralter table S table S drop constraintdrop constraint S_PK S_

27、PKalteralter table SC table SC add constraintadd constraint SC_CHECK SC_CHECK checkcheck(Sno in select Sno from S)(Sno in select Sno from S)完整性控制完整性控制 域约束的创建、撤消与添加域约束的创建、撤消与添加create domaincreate domain AGE_DOMAIN smallint AGE_DOMAIN smallintconstraintconstraint DC_AGE DC_AGE checkcheck(value = 25 an

28、d value (value = 15)= 15)alter domainalter domain AGE_DOMAIN AGE_DOMAINadd constraintadd constraint DC_AGE check(value = 35 and DC_AGE check(value = 15)value = 15)alter domainalter domain AGE_DOMAIN AGE_DOMAINdrop constraindrop constrain DC_AGE DC_AGE完整性控制完整性控制l断言断言 定义：定义：CREATE ASSERTIONCREATE ASSE

29、RTION CHECKCHECK 断言是谓词，表达数据库总应该满足的条件断言是谓词，表达数据库总应该满足的条件 一旦定义了断言，系统验证其有效性，并且对每个一旦定义了断言，系统验证其有效性，并且对每个可能违反该断言的更新操作都进行检查可能违反该断言的更新操作都进行检查 这种检查会带来巨大的系统负载，因此应该谨慎使这种检查会带来巨大的系统负载，因此应该谨慎使用断言用断言 对断言对断言“所有所有X, P(X)”X, P(X)”，是通过检查，是通过检查“not exists Xnot exists X， P(X)”P(X)”来实现的来实现的完整性控制完整性控制 示例：不允许男同学选修张老师课程 cr

30、eate assertioncreate assertion ASSE2 ASSE2 checkcheck(not exists(not exists(select (select * * from SC from SC where Cno in where Cno in(select Cno(select Cno from C from C where TEACHER = where TEACHER = 张张) ) and Sno in and Sno in(select Sno(select Sno from S from S where SEX = M) where SEX = M)完整

31、性控制完整性控制 示例：每门课最多示例：每门课最多5050名男同学选修名男同学选修create assertioncreate assertion ASSE1 ASSE1 check check(50 = all(50 = all (select count(SC.Sno)(select count(SC.Sno) from S, SC from S, SC where S.Sno = SC.Sno where S.Sno = SC.Sno and SEX = Mand SEX = M group by Cno) group by Cno) 撤消：撤消：drop assertiondrop a

32、ssertion 断言名断言名drop assertiondrop assertion ASSE1 ASSE14.6 授权授权l安全性控制定义安全性控制定义 安全性控制保护数据库以防止不合法的使用安全性控制保护数据库以防止不合法的使用所造成的数据泄露和破坏。其基本措施是所造成的数据泄露和破坏。其基本措施是存存取控制取控制l安全性措施安全性措施 物理级物理级 人际级人际级 操作系统级操作系统级 网络级网络级 数据库系统级数据库系统级数据控制功能数据控制功能l权限的转授和回收 允许用户把已获得的权限转授给其他用户，也可以把已授给其他用户的权限再回收上来l权限图 结点是用户，根结点是DBA，有向边UiUj，表示用户Ui把某权限授给用户Uj 一个用户拥有权限的充分必要条件是在权限图中有一条从根结点到该用户结点的路径数据控制功能数据控制功能DBAU1U2U3U4U5DBAU1U2U3U4