《数据库系统教学课件：第7讲-中级SQL》由会员分享，可在线阅读，更多相关《数据库系统教学课件：第7讲-中级SQL（12页珍藏版）》请在金锄头文库上搜索。
1、第第第第7 7 7 7讲讲讲讲: : : :( ( ( (第第第第4 4 4 4章章章章) ) ) ) 中级中级SQLSQL课程名称课程名称: : 数据库系统数据库系统 -2一一一一 SQLSQLSQLSQL支持的表间连接方式支持的表间连接方式支持的表间连接方式支持的表间连接方式 P.63P.63P.63P.63（方式4）(P.63)select *from student join takes on student.id=takes.id; select *from student, takeswhere student.ID = takes.ID;（方式（方式1） (p.39)select
2、 name, course_idfrom instructor, teacheswhere instructor.ID= teaches.ID;（方式2） (p.39)select name, course_idfrom instructor natural join teaches;（方式3）(p.40)select name, titlefrom (instructor natural join teaches) join course using(course_id);1.(1.(自然自然/ /等值等值) )连接的不同方式连接的不同方式 P.63P.63有何不同？有何不同？有何不同？有何
3、不同？有何不同？有何不同？查询结果表虽然相同，查询结果表虽然相同，但但Where允许按指定允许按指定属性属性(可不同名可不同名)连接，连接，且在且在连接表连接表中连接属性中连接属性都会出现都会出现(ID两次两次) 。虽然都是按相同属性连接，虽然都是按相同属性连接，但但using允许按指定属性、允许按指定属性、而非两表所有同名属性连接而非两表所有同名属性连接作用作用及查询结果表都相同，及查询结果表都相同，在在连接表连接表中连接属性都会出中连接属性都会出现现(ID重复出现两次重复出现两次)。on连接示例连接示例3ONONONON连接示例连接示例连接示例连接示例 p.64p.64p.64p.64一一
4、 SQLSQL支持的表间连接方式支持的表间连接方式42.2.2.2.外连接的不同方式外连接的不同方式外连接的不同方式外连接的不同方式3）外连接也可与）外连接也可与on和和using一起使一起使用吗，其作用？用吗，其作用？ 一一 SQLSQL支持的表间连接方式支持的表间连接方式2）给出这两个表左外）给出这两个表左外连接、右外连接和全外连接、右外连接和全外连接的结果？连接的结果？ courseprereq可以，作用与前面可以，作用与前面join情形类似情形类似(p.67)(这这4种连接类型和种连接类型和3种条件可任意组合种条件可任意组合)5二二二二 视图视图视图视图1）什么是视图，）什么是视图，如
5、何产生？如何产生？ 1）可以在任何）可以在任何QL语句中像表一样的被使用！语句中像表一样的被使用！2）增强查询能力且方便）增强查询能力且方便(用户用户/程序员程序员)使用！使用！3）还可以提供数据访问安全控制）还可以提供数据访问安全控制(隐藏数据隐藏数据)！4）作为外模式）作为外模式(1级映射级映射)有利于应用独立性！有利于应用独立性！ create view physics_fall_2009 as (P.68) select course.course_id, sec_id, building, room_number from course, section where course.c
6、ourse_id = section.course_id and course.dept_name = Physics and section.semester = Fall and section.year = 2009; create view physics_fall_2009_watson as select course_id, room_number from physics_fall_2009 where building= Watson; (P.68) create view faculty as select ID, name, dept_name from instruct
7、or ; (P.68)3）你认为视图有）你认为视图有哪些作用哪些作用(好处好处)？ 2）在视图上还也）在视图上还也可以定义新的视图可以定义新的视图吗？吗？ 视图采用视图采用create view语句定义，语句定义，可以定义为任何一个可以定义为任何一个SQL语句语句,无实际数据，无实际数据，虚表虚表，有利于数据一致性！，有利于数据一致性！视图上可以在定义新的视图！视图上可以在定义新的视图！4）(讲解讲解)可以通过可以通过视图更新数据吗？视图更新数据吗？ 仅在少数简单视图仅在少数简单视图(updatable)上可以更新数据！上可以更新数据！P.72讨论讨论2. 什么是视什么是视图，有何作用？图，有
8、何作用？ 视图视图关系模式关系模式1级映射级映射2级映射级映射6三三三三 事务事务事务事务p.73p.73p.73p.731）怎么解决）怎么解决上述问题？上述问题？ 讨论讨论3：什么：什么是事务，有何是事务，有何作用？作用？ 通过银行卡为校园一卡通充值的处理过程：通过银行卡为校园一卡通充值的处理过程：1）输入充值金额100元2）银行卡下账100元3）一卡通上账100元异常现象异常现象:若此时操作失败(因停电、网断等)，导致银行卡少100元，而一卡通钱仍未增加！事务：事务： SQL允许将多个数据操作捆绑为一个逻辑单元允许将多个数据操作捆绑为一个逻辑单元 要么这些操作全部完成，要么一个也未执行！要
9、么这些操作全部完成，要么一个也未执行！例子：(银行卡下账100元，一卡通上账100元)两事务操作-1个事务SQL提供的相关命令为：提供的相关命令为：Commit work-提交事务；Rollback work-撤销事务；Begin atomic-事务(操作语句)开始； End-事务(操作语句)结束；7四四四四 完整性约束完整性约束完整性约束完整性约束p.72-73p.72-73p.72-73p.72-73 check(time_slot_id in(select time_slot_id from time_slot);Check (salary10000)，unique，讨论讨论4. SQL
10、提供提供哪些类型的完整哪些类型的完整性约束，作用？性约束，作用？ 1. 键完整性约束（主码/主键） 关系(模式)必需有一个主码，来区分不同元组！ SQL采用primary key来定义！2.参照完整性约束（外码/外键） 用另一关系的主码，来约束属性取值的有效性！ SQL采用foreign key references 来定义！1）SQL还提供哪还提供哪些方式的数据完整些方式的数据完整性约束？性约束？ 键完整性约束键完整性约束参照完整性约束参照完整性约束属性属性(非空非空)完整性约束完整性约束属性属性(范围范围)完整性约束完整性约束属性属性(唯一唯一)完整性约束完整性约束复杂条件复杂条件(来自他
11、表来自他表select结果限定取值范围结果限定取值范围)完整性约束完整性约束复杂条件复杂条件(标量集合限定取值范围标量集合限定取值范围)完整性约束完整性约束(注：复杂条件甚至可以是对表上多个属性间表达式的限制约束注：复杂条件甚至可以是对表上多个属性间表达式的限制约束)3.其它数据完整性约束：1. 完整性约束完整性约束82.2.2.2.外键约束方式外键约束方式外键约束方式外键约束方式create table course ( (P.73-74)dept_name varchar(20),foreign key (dept_name) references department on delete
12、 cascade on update cascade,);2）这里的）这里的on约束起到什么约束起到什么作用？作用？ -2)连带删除连带删除(修改修改)set nul/set defualt -3)设置为空设置为空/默认值默认值1)不写时拒绝删除不写时拒绝删除三种参照约束方式：三种参照约束方式：create assertion check ;例子：例子：约束要求：约束要求：student每个元组的每个元组的tot_cred(学生的总学分学生的总学分)取值取值应等于该生所修完课程的学分总和应等于该生所修完课程的学分总和(关系关系takescourse的的credits)3）(讲解讲解)什么什么是
13、断言，有什么是断言，有什么作用？作用？ p.76四四 完整性约束完整性约束p.73-76 当数据更新时，当数据更新时，保持谓词为真。保持谓词为真。(否则拒绝更新否则拒绝更新)但作用有利有弊但作用有利有弊3.3.断言断言9五五五五 授权授权授权授权 p.81p.81讨论讨论5. 在开放环境在开放环境中，如何限制用户对中，如何限制用户对数据的合法访问？数据的合法访问？ 1）这些语句说）这些语句说明了什么样的访明了什么样的访问权限变化？问权限变化？1. 1. 表表( (关系关系) )上的授权上的授权；public ？；通过授权！通过授权！只有授权用户才能查看(/插入/修改/删除)相关表中的数据.注:
14、表的创建者表的创建者, 自然拥有表上的一切权限.用户在用户在branch表上的表上的查看查看()权被收回。权被收回。作用及好处？作用及好处？还可以创建子角色；将instructor表上的查看(插入/)权授予用户。所有用户所有用户可以建立角色roll(用户群)。当将某权限授予角色时，该用户群均有该使用权限。角色名角色名用户名用户名简化权限管理简化权限管理103.3.3.3.授权授权授权授权转移转移转移转移 图图4-10示例：表示例：表teaches上的上的update更新权更新权作用：1）描述在一张表上某种授权的当前状态，便于系统动态管理授权；2）当DBA或具有权限的用户(树上节点)进行授权时，
15、树扩展(生长)； 3）当DBA或具有权限的用户(树上节点)回收权限时，树收缩(枯萎)；变化示例变化示例1：U2执行语句:grant update on teaches To U6;U6变化示例变化示例2：U1执行语句：revoke update on teaches from U5;DBA执行语句：revoke update on teaches from U2;112. 2. 2. 2. 视图视图视图视图( ( ( (虚关系虚关系虚关系虚关系) ) ) )上的授权上的授权上的授权上的授权P.82P.82五五 授权授权 p.81create view geo_instructor as (se
16、lect * from instructor where dept_name=Geology);select *from geo_instructor;如果如果该用户在instructor上没有获得select授权，则他仍然看不到看不到数据！grant select on geo_instructor to geo_staff(roll角色角色)3）geo_staff中中用户用户能通过该语能通过该语句查看到数据吗句查看到数据吗？ 2）视图上也可以）视图上也可以授权授权(查看查看/修改修改/删除数据删除数据)？在表instructor上创建一个视图geo_instructor 将视图上的查看权授
17、予一个角色geo_staff注：注：1）视图的创建者视图的创建者，自然拥有该视图上的所有权限！，自然拥有该视图上的所有权限！ 2）函数与过程的创建者函数与过程的创建者，自然拥有其上所有权限！，自然拥有其上所有权限！(p.83) 用户可以函数与过程用户可以函数与过程(&5.2)，并可对其他用户授予并可对其他用户授予execute执行权。执行权。12UDT(User-Defined Types)&DomianUDT(User-Defined Types)&DomianUDT(User-Defined Types)&DomianUDT(User-Defined Types)&Domian六六 * *
18、( (略讲略讲) )用户自定义的类型用户自定义的类型 p.78-79create domain person_name char(20) not null;create domain degree_level varchar(10)constraint degree_level_testcheck (value in (Bachelors, Masters, Doctorate);create type Dollars as numeric (12,2) final;create table department(dept_name varchar (20),building varchar (15),budget Dollars);定义一个新类型：定义一个新类型：定义一个新域：定义一个新域：域与类型的区别：域与类型的区别：1）域上允许声明(定义)约束；2）域不是强制类型，一个域类型可以被赋予另一个域类型， 只有它们的基本类型是相容的（可强制转换）；讨论讨论6. *什么是自什么是自定义类型，如何定定义类型，如何定义和使用？义和使用？
