中级SQL

连接表达式

students
takes


连接条件

on条件允许在参与连接的关系上设置通用的谓词。
该谓词的写法与where子句谓词类似。
on条件出现在连接表达式的末尾。


select *// student 与 takes笛卡尔积得到一个大集合// 大集合满足student.ID&＃61; takes.ID的元组被选出from student join takes on student.ID&＃61; takes.ID;// 等价select *from student, takeswhere student.ID&＃61; takes.ID;// 等价select student.ID as ID, name, dept_name, tot_cred,course_id, sec_id, semester, year, gradefrom student join takes on student.ID&＃61; takes.ID;


on条件可以表示任何SQL谓词&＃xff0c;
从而使用on条件的连接表达式就可以表示比自然连接更为丰富的连接条件。- 称为外连接的这类连接来说&＃xff0c;
on条件的表现与where条件不同。
- 如在on子句中指定连接条件&＃xff0c;
并在where子句出现其余条件&＃xff0c;这样更易读。


外连接

假设显示一个所有学生的列表&＃xff0c;显示他们的ID&＃xff0c;name&＃xff0c;dept_name和tot_cred&＃xff0c;
他们选修的课程


select *from student natural join takes;


外连接运算与我们已经学过的连接运算类似&＃xff0c;
但通过在结果中创建包含空值元组的方式&＃xff0c;保留了那些在连接中丢失的分组。
上述例子在图1和图2的情形下&＃xff0c;snow学生未选课将不会出现在natual join结果中。
为保证snow出现在结果中&＃xff0c;
可在连接结果中加入一个元组&＃xff0c;
它在来自student关系的所有属性上的值被设置为学生snow的相应值。
在所有余下的来自takes关系属性上的值被设为null。
这些属性是course_id, sec_id, semester, year
有三种外连接&＃xff1a;
- 左外连接&＃xff0c;只保留出现在左外连接运算之前【左边】的关系中的元组
- 右外连接&＃xff0c;只保留出现在右外连接运算之后【右边】的关系中的元组。
- 全外连接&＃xff0c;保留出现在两个关系中的元组。此前学习的不保留未匹配元组的连接运算称为内连接。计算左外连接&＃xff0c;
计算出内连接的结果&＃xff0c;
对内连接的左侧关系中任意一个与右侧关系中任何元组都不匹配的元组t&＃xff0c;
向连接结果中加入一个元组r&＃xff0c;
r的构造如下&＃xff1a;
- 元组r从左侧关系得到的属性被赋为t中的值
- r的其他属性被赋为空值


// 结果包含snow&＃xff0c;snow的takes中各个属性值为nullselect *from student natural left outer join takes;// 等价的右外连接// 差别是元组中各个属性出现顺序不一致select *from takes natural right outer join student;// 找出一门课程也没有选修的学生select IDfrom student natural left outer join takeswhere course id is null;


全外连接是左外连接与右外连接类型的组合。
在内连接结果出来后&＃xff0c;
左侧关系中不匹配右侧关系任何元组的元组被添上空值并加到结果中。
右侧关系中不匹配左侧关系任何元组的元组也被添上空值并加到结果中&＃xff0c;


// 显示Comp. Sci.系所有学生及他们在2009&＃xff0c;春季选修的所有课程段的列表// 2009,春季开设的未被Comp. Sci.系学生选修的课程也显示from (select *// 学生集合from student// 选出Comp. Sci系的where dept_name&＃61; ’Comp. Sci’)// 全外连接natural full outer join(select *// 选课信息from takes// 选出2009,Spring的被选课程where semester &＃61; ’Spring’ and year &＃61; 2009);// 结果集合包含&＃xff1a;// 1,对Comp.Sci每个学生&＃xff0c;// 其所选的每个在2009,Spring的课程&＃xff0c;与其自身结合成为一个结果元组// 2,对Comp.Sci每个学生&＃xff0c;// 若此学生未选择任何2009&＃xff0c;Spring的课程。// 选课各个属性为null&＃xff0c;与其自身结合成为一个结果元组// 3,对某个2009&＃xff0c;Spring的课程&＃xff0c;若没被任何Comp.Sci系学生所选// 对学生各个属性为null&＃xff0c;与其结合成为一个结果元组


on子句可和外连接一起使用。


// 与student natural left outer join takes查询相同&＃xff0c;只不过// 下面的属性ID在结果出现两次。select *from student left outer join takes on student.ID&＃61; takes.ID;// select *from student left outer join takes on true// 基于from子句外连接得到的结果集合做进一步的筛选// 外连接结果包含没有选课的学生&＃xff0c;元组中student各个属性为对应学生属性&＃xff0c;takes各个属性为null// 这样的属性会在where这里被过滤掉。where student.ID&＃61; takes.ID;


外连接只为那些对相应内连接结果没有贡献的元组补上空值&＃xff0c;并加入结果。
on条件是外连接声明的一部分。
where子句不是。


连接类型和条件

SQL中把常规连接称作内连接。
这样连接子句可用inner join来替换outer join&＃xff0c;说明使用的是常规连接。inner可选。


select *from student join takes using (ID);// 等价于select *from student inner join takes using (ID);


视图

考虑一个职员需要知道教师的标识&＃xff0c;姓名&＃xff0c;所在系名&＃xff0c;但没权限看到教师的工资值。


select ID, name, dept namefrom instructor;


除了安全考虑&＃xff0c;
还可能希望创建一个比逻辑模型更符号特定用户直觉的个人化的关系集合。


// 希望有一个关于Physics系在2009秋季学期开设的所有课程段的列表// 包括每个课程段在那栋建筑&＃xff0c;那个房间授课select course.course_id, sec_id, building, room_number// 课程和开设课程笛卡尔积from course, section// 针对每个课程&＃xff0c;// 此课程的每个开设信息&＃xff0c;与课程结合形成一个结果元组// 结果元组满足物理系&＃xff0c;2009&＃xff0c;秋季&＃xff0c;则被选入结果元组where course.course_id &＃61; section.course_idand course.dept_name &＃61; ’Physics’and section.semester &＃61; ’Fall’and section.year &＃61; ’2009’;


如果获得上述关系后&＃xff0c;将其存储下来。
一个不便时&＃xff0c;后续其所依赖的基础关系变化时&＃xff0c;存储的结果会失效。SQL允许通过查询定义"虚关系"&＃xff0c;它在概念上包含查询的结果。
虚关系并不预先计算并存储&＃xff0c;而是在使用虚关系的时候才通过执行查询被计算出来。
任何像这种不是逻辑模型的一部分&＃xff0c;但作为虚关系对用户可见的关系称为视图。


试图定义

在SQL中用create view定义视图。
为定义试图&＃xff0c;需给视图一个名称&＃xff0c;需提供计算视图的查询。


// create view命令的格式为create view v as <query expression>;


其中可以是任何合法的查询表达式&＃xff0c;v表示试图名。
重新考虑需访问instructor关系中除salary外的所有数据的职员。


// 可把视图关系faculty提供给职员// 此视图定义如下create view faculty asselect ID, name, dept_namefrom instructor;


视图关系在概念上包含查询结果中的元组&＃xff0c;但并不进行预计算和存储。
数据库系统存储与视图关系相关联的查询表达式。
视图关系被访问时&＃xff0c;其中的元组通过计算查询结果创建出来的&＃xff0c;
视图关系是在需要时候创建的。


// 视图&＃xff1a;// 列出Physics系在2009&＃xff0c;秋季开设的所有课程段// 每个课程段在那栋建筑&＃xff0c;那个房间授课create view_physics_fall_2009 asselect course.course_id, sec_id, building, room_numberfrom course, sectionwhere course.course_id &＃61; section.course_idand course.dept_name &＃61; ’Physics’and section.semester &＃61; ’Fall’and section.year &＃61; ’2009’;


SQL查询中使用视图

一旦定义了一个视图&＃xff0c;就可用视图名指代该视图生成的虚关系。


// 使用视图physics_fall_2009// 可用下面的查询找到所有于2009&＃xff0c;秋季&＃xff0c;在Watson大楼开设的Physics课程select course_idfrom physics_fall_2009where building&＃61; ’Watson’;


在查询中&＃xff0c;视图名可出现在关系名可出现的任何地方。
视图的属性名可按下述方式显式指定


// 给出了每个系中所有教师的工资总和// 显式指定视图各个属性名create view departments_total_salary(dept_name, total_salary) asselect dept_name, sum (salary)from instructorgroup by dept_name;


直觉上&＃xff0c;任何给定时刻&＃xff0c;
视图关系中的元组集是该时刻视图定义中的查询表达式的计算结果。
如果一个视图关系被计算并存储&＃xff0c;一旦用于定义该视图的关系被修改&＃xff0c;
视图就会过期。
为避免这一点&＃xff0c;
视图常这样实现&＃xff1a;
定义一个视图时&＃xff0c;数据库系统存储视图的定义本身&＃xff0c;
不存储该视图查询表达式的执行结果。
无论何时执行查询&＃xff0c;视图关系均被重新计算。一个视图可能被用到定义另一个视图的表达式中。
如下定义视图physics_fall_2009_watson&＃xff0c;


// 列出了于2009&＃xff0c;秋季&＃xff0c;在Watson大楼开设的所有Physics课程的标识和房间号create view physics_fall_2009_watson asselect course_id, room_numberfrom physics_fall_2009where building&＃61; ’Watson’;// 等价于create view physics_fall_2009_watson asselect course_id, room_numberfrom (select course.course_id, building, room_numberfrom_course, sectionwhere course.course_id &＃61; section.course_idand course.dept name &＃61; ’Physics’and section.semester &＃61; ’Fall’and section.year &＃61; ’2009’)where building&＃61; ’Watson’;


物化视图

特定数据库允许存储视图关系&＃xff0c;但它们保证&＃xff1a;
如用于定义视图的实际关系改变&＃xff0c;
视图也跟着修改。
这样的视图称为物化视图。


// 考察视图departments_total_salary// 如上述视图是物化的&＃xff0c;// 它的结果会存放在数据库中。// 如果一个instructor元组被插入到instructor关系中&＃xff0c;// 或从instructor关系中删除&＃xff0c;// 定义视图的查询结果就会变化// 结果是物化视图的内容也必须更新// 类似地&＃xff0c;如一位教师的工资被更新&＃xff0c;// departments_total_salary中对应于该教师所在系的元组必须更新


保持物化视图一直在最新状态的过程称为物化视图维护&＃xff0c;或通常简称视图维护。
构成视图定义的任何关系被更新时&＃xff0c;
可马上进行视图维护。
某些数据库系统在视图被访问时才执行视图维护&＃xff0c;
还有一些系统&＃xff0c;仅采用周期性物化视图更新方式。频繁使用视图的应用将会从视图的物化中获益&＃xff0c;
需要快速响应基于大关系上聚集计算的特定查询
也会从创建与查询相对应的物化视图中受益良多。
此时&＃xff0c;
聚集结果很可能比定义视图的大关系要小得多&＃xff0c;
其结果是利用物化视图来回答查询就很快&＃xff0c;它避免读取大的底层关系。SQL没有定义指定物化视图的标准方式&＃xff0c;
但很多数据库系统提供各自的SQL扩展来实现这项任务。


视图更新

用视图表达的数据库修改必须被翻译为对数据库逻辑模型中实际关系的修改。
设此前的faculty被提供给一个职员。


// 该职员可这样写insert into facultyvalues (’30765’, ’Green’, ’Music’);


这个插入必须表示为对instructor关系的插入&＃xff0c;
因为instructor是数据库系统用于构造图faculty的实际关系。
然而&＃xff0c;为把一个元组插入instructor&＃xff0c;
必须给出salary值。
两处解决此插入方式&＃xff1a;
- 拒绝插入&＃xff0c;返回错误消息
- 向instructor关系插入元组(&＃39;30765&＃39;, &＃39;Green&＃39;, &＃39;Music&＃39;, null)通过视图修改数据库的另一类问题发生在这样的视图上


create view instructor_info asselect ID, name, building// 每个教员和每个建筑的组合from instructor, departmentwhere instructor.dept_name&＃61; department.dept_name;// 考虑插入insert into instructor_infovalues (’69987’, ’White’, ’Taylor’);


假设没有标识为69987的教师&＃xff0c;
也没有位于Taylor大楼的系。
则向instructor和department关系插入元组的唯一可能的方法是&＃xff1a;
向instructor中插入元组
(&＃39;69987&＃39;, &＃39;White&＃39;, null, null)
向department中插入元组(null, &＃39;Taylor&＃39;, null)但这个更新并没有产生所需的结果。
故通过空值来更新instructor和department关系&＃xff0c;
以得到对instructor_info所需的更新是不可行的。由于如上所述的种种问题&＃xff0c;
除了一些有限的情况外&＃xff0c;
一般不允许对视图关系进行修改。一般说来&＃xff0c;
如果定义视图的查询对下列条件都能满足&＃xff0c;
我们称SQL视图是可更新的【即视图上可以执行插入&＃xff0c;更新&＃xff0c;删除】
- from子句中只有一个数据库关系
- select子句中只包含关系的属性名&＃xff0c;不包含任何表达式&＃xff0c;聚集或distinct声明
- 任何没有出现在select子句中的属性可以取空值。
即这些属性上没有not null约束&＃xff0c;也不构成主码的一部分。
- 查询中不含有group by或having子句。


// 该视图可执行update&＃xff0c;insert&＃xff0c;deletecreate view history instructors asselect *from instructorwhere dept name&＃61; ’History’;


即使可更新下&＃xff0c;问题仍然存在。
如向上述视图添加非历史系教师。添加结果在视图不可见。
可添加with check option来对添加做合法性检查


事务

事务有查询和(或)更新语句的序列组成。
一个&＃xff33;&＃xff31;&＃xff2c;语句被执行&＃xff0c;隐式地开始了一个事务。
下列&＃xff33;&＃xff31;&＃xff2c;语句之一会结束一个事务。
- Commit work:提交当前事物&＃xff0c;即将该事务所做的更新在数据库中持久保存。
- Rollback work:回滚当前事务。即撤销该事务中所有&＃xff33;&＃xff31;&＃xff2c;语句对数据库的更新。
一旦某事务执行了Commit work&＃xff0c;它的影响就不能用rollback work来撤销了。
数据库系统保证在发生诸如某条&＃xff33;&＃xff31;&＃xff2c;语句错误&＃xff0c;断电&＃xff0c;系统崩溃故障时&＃xff0c;
如一个事务还没有完成&＃xff43;&＃xff4f;&＃xff4d;&＃xff4d;&＃xff49;&＃xff54;　&＃xff57;&＃xff4f;&＃xff52;&＃xff4b;&＃xff0c;其将被回滚。假设&＃xff53;&＃xff54;&＃xff55;&＃xff44;&＃xff45;&＃xff4e;&＃xff54;关系中每个元组在tot_cred属性上的取值需要保持在最新状态&＃xff0c;
只要学生成功修完一门课&＃xff0c;该属性值就更新。
如&＃xff54;&＃xff41;&＃xff4b;&＃xff45;&＃xff53;更新后&＃xff0c;&＃xff53;&＃xff54;&＃xff55;&＃xff44;&＃xff45;&＃xff4e;&＃xff54;更新时发生故障。数据库中数据就是不一致的。一个事务或者在完成所有步骤后提交其行为&＃xff0c;
或者在不能成功完成其所有动作的情况下回滚其所有动作。
这样&＃xff0c;数据库提供了对事务具有原子性的抽象。很多&＃xff33;&＃xff31;&＃xff2c;实现下&＃xff0c;默认下每个&＃xff33;&＃xff31;&＃xff2c;语句自成一个事务&＃xff0c;且一执行完就提交。
&＃xff33;&＃xff31;&＃xff2c;&＃xff1a;&＃xff11;&＃xff19;&＃xff19;&＃xff19;标准规定&＃xff0c;多条&＃xff33;&＃xff31;&＃xff2c;语句用begin atomic . . . end包围时&＃xff0c;整体作为一个事务。


完整性约束

例&＃xff1a;
- 教师姓名不能为&＃xff4e;&＃xff55;&＃xff4c;&＃xff4c;
- 任意两位教师的教师标识不同
- &＃xff43;&＃xff4f;&＃xff55;&＃xff52;&＃xff53;&＃xff45;中每个系须在department关系中有一个对应的系名
- 一个系的预算必须大于&＃xff10;$
完整性约束常被看成是数据库模式设计过程的一部分&＃xff0c;在create table命令的一部分被声明。
完整性约束也可通过使用alter table table-name add constraint施加到已有关系上&＃xff0c;constraint可是关系上的任意约束。
执行上述命令时&＃xff0c;先保证目前关系满足指定的约束。
满足&＃xff0c;则约束被施加。否则&＃xff0c;拒绝执行。


单个关系上的约束

create table可包括完整性约束。
- not null
- unique
- check


not null约束

// 限定属性name和budget不能为&＃xff4e;&＃xff55;&＃xff4c;&＃xff4c;// 默认下&＃xff0c;&＃xff33;&＃xff31;&＃xff2c;禁止在关系模式的主码中出现空值name varchar(20) not nullbudget numeric(12,2) not null


unique约束

// 指明Aj1, Aj2, . . . , Ajm形成了一个候选码// 即关系中没有两个元组能在所有列出的属性上取值相同。unique (Aj1, Aj2, . . . , Ajm)


check子句

应用于关系声明时&＃xff0c;check(p)子句指定一个谓词&＃xff30;&＃xff0c;关系中的每个谓词都必须满足谓词&＃xff30;。
实际上创建了一个强大的类型系统。


// 在创建department的create table命令中&＃xff0c;check(budget>0)保证budget取值是正数create table section(course_id varchar (8),sec_id varchar (8),semester varchar (6),year numeric (4,0),building varchar (15),room_number varchar (7),time_slot_id varchar (4),primary key (course_id, sec_id, semester, year),check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’)))


参照完整性

常希望保证在一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现&＃xff0c;
这称为参照完整性。
例如&＃xff1a;


// 每个元组的dept_name属性值需要在department关系中存在 “foreign key (dept_name) references department”


更一般地&＃xff0c;令关系r1和r2的属性集分别为R1和R2
主码分别为K1和&＃xff2b;2
如要求对r2中任意元组t2&＃xff0c;均存在r1中元组t1使得t1.K1 &＃61; t2.a
我们称R2的子集a为参照关系r1中K1的外码。
这种要求称为参照完整性约束或子集依赖。
r2中&＃xff41;上的取值集合必须是&＃xff52;&＃xff11;中&＃xff2b;1上的取值集合的子集。
为使参照完整性约束有意义&＃xff0c;a和K1必须是相容的属性集&＃xff0c;
也即&＃xff0c;要么a等于K1&＃xff0c;要么它们须包含相同数目的属性&＃xff0c;且对应的属性类型必须相容。参照完整性约束通常不要求K1是r1的主码&＃xff0c;
结果是r1中可能有不止一个元组在属性&＃xff2b;1上取值相同。SQL还支持一个可显式指定被参照关系的属性列表的references子句。
这个指定的属性列表必须被声明为被参照关系的候选码&＃xff0c;要么使用primary key约束&＃xff0c;要么使用&＃xff55;&＃xff4e;&＃xff49;&＃xff51;&＃xff55;&＃xff45;约束。可用如下简写作为属性定义的一部分&＃xff0c;且声明该属性为外码
dept_name varchar(20) references department
违反参照完整性约束时&＃xff0c;通常的处理是拒绝执行导致完整性破坏的操作。【即执行更新操作的事务被回滚】
在foreign key子句中可指明&＃xff1a;如被参照关系上的删除或更新动作违反了约束&＃xff0c;
则系统需采取一些步骤通过修改参照关系中的元组来恢复完整性约束&＃xff0c;而非拒绝。


create table course( . . .// 如删除department中的元组导致此参照完整性约束被违反&＃xff0c;// 由于有&＃xff4f;&＃xff4e;语句&＃xff0c;// 删除不被系统拒绝&＃xff0c;// 而是对course关系作"级联"删除。// 类似地&＃xff0c;// 如果更新被参照字段时违反了约束&＃xff0c;// 更新操作不被拒绝&＃xff0c;// 而是将&＃xff43;&＃xff4f;&＃xff55;&＃xff52;&＃xff53;&＃xff45;中参照的元组的dept_name字段也改为新值// SQL还允许foreign key子句指明除了cascade外的其他动作&＃xff0c;如约束被违反&＃xff1a;// 可将参照域【这里为dept_name】设置为null【用set null 代替cascade】,或设置为域的默认值【用set default】foreign_key (dept_name) references departmenton delete cascadeon update cascade,. . . )


如存在涉及多个关系的外码依赖链&＃xff0c;则在链一端所做的删除或更新可能传至整个链。


事务中对完整性约束的违反

事务可能包含几个步骤&＃xff0c;在某一步之后完整性约束也许会暂时被违反。后面某一步也许就消除此违反。
&＃xff33;&＃xff31;&＃xff2c;标准允许将initially deferred子句加入到约束声明中。
这样完整性约束在事务结束时检查。
一个约束可被指定为可延迟的&＃xff0c;这意味这默认时&＃xff0c;它会被立即检查。
需要时&＃xff0c;可以延迟检查。
对声明为可延迟的约束&＃xff0c;
执行set constraints constraint-list defered语句作为事务的一部分&＃xff0c;
会导致对指定约束的检查被延迟到事务结束时执行。


复杂&＃xff43;&＃xff48;&＃xff45;&＃xff43;&＃xff4b;条件与断言

如果一个数据库支持在&＃xff43;&＃xff48;&＃xff45;&＃xff43;&＃xff4b;子句中出现子查询&＃xff0c;
就可在关系section上声明如下所示的参照完整性约束&＃xff1a;


check (time_slot_id in (select time_slot_id from time_slot))这个&＃xff43;&＃xff48;&＃xff45;&＃xff43;&＃xff4b;检测在&＃xff53;&＃xff45;&＃xff43;&＃xff54;&＃xff49;&＃xff4f;&＃xff4e;关系中每个元组的time_slot_id的确是在time_slot


我们大学模式上&＃xff0c;另一个自然的约束是&＃xff1a;
每个课程段都至少需要一位教师来讲授&＃xff0c;
一种方案是声明section关系的属性集(course_id, sec_id, semester, year)作为外码&＃xff0c;它参照了&＃xff54;&＃xff45;&＃xff41;&＃xff43;&＃xff48;&＃xff45;&＃xff53;关系中的相应属性。
如数据库系统支持在&＃xff43;&＃xff48;&＃xff45;&＃xff43;&＃xff4b;约束中出现子查询的话&＃xff0c;可用与time_slot属性类似的check约束来强制实现上述约束。一个断言就是一个谓词&＃xff0c;它表达了我们希望数据库总能满足的一个条件。
域约束&＃xff0c;参照完整性约束是断言特殊形式。


// 对student关系中的每个元组&＃xff0c;它在属性tot_cred上的取值必须等于该生所成功修完课程的学分总和// 每位教师不能在同一个学期的同一个时间段的两个不同的教室授课// SQL中断言的形式create assertion <assertion-name> check <predicate>create assertion credits_earned_constraint check(not exists (select IDfrom studentwhere tot_cred <> (select sum(credits)from takes natural join coursewhere student.ID&＃61; takes.IDand grade is not null and grade<> ’F’ )


目前还没有一个广泛使用的数据库系统支持check子句的谓词中使用子查询或create assertion结构


SQL的数据类型与模式

SQL标准支持与日期和时间相关的几种数据类型&＃xff1a;
- date&＃xff1a;日历日期&＃xff0c;包括年&＃xff0c;月&＃xff0c;日
- time&＃xff1a;一天中的时间&＃xff0c;包括小时&＃xff0c;分&＃xff0c;秒。
指定time with timezone可把时区信息连时间一起存储
- timestamp: date和time的组合。如指定with timezone&＃xff0c;则时区信息也会被存储。


date ’2001-04-25’time ’09:30:00’timestamp ’2001-04-25 10:29:01.45’


可用cast e as t形式的表达式来将一个字符串或字符串表达式e转换为类型t,
其中t是date, time, timestamp中的一种。
可利用extract(field from d)从date或time值d中提取出单独的域。
域可为year/month/day/hour/minute/second
时区信息可用timezone_hour和timezone_minute来提取。current_date返回当前日期
current_time返回当前时间【带有时区】
localtime返回当前的本地时间【不带时区】
时间戳【日期&＃xff0b;时间】由current_timestamp及localtimestamp返回。
SQL还支持interval类型。


默认值

SQL允许为属性指定默认值


create table student(ID varchar (5),name varchar (20) not null,dept_name varchar (20),tot_cred numeric (3,0) default 0,primary key (ID));insert into student(ID, name, dept_name)values (’12789’, ’Newman’, ’Comp. Sci.’);


创建索引

在关系的属性上所创建的索引是一种数据结构&＃xff0c;
允许数据库系统高效地找到关系中那些在索引属性上取给定值的元组。很多数据库支持如下创建索引


create index studentID_index on student(ID);


大对象类型

SQL提供字符数据的大对象数据类型【clob】&＃xff0c;二进制数据的大对象数据类型【blob】


book review clob(10KB)image blob(10MB)movie blob(2GB)


一个应用常用一个SQL查询来检索出一个大对象的"定位器"&＃xff0c;
然后在宿主语言中用这个定位器来操纵对象。


用户定义的类型

- 独特类型
- 结构化数据类型


create type Dollars as numeric(12,2) final;create type Pounds as numeric(12,2) final;create table department(dept_name varchar (20),building varchar (15),budget Dollars);// 一种类型的数值可被转换到另一个域cast (department.budget to numeric(12,2))


SQL提供了drop type和alter type子句来删除或修改之前创建过的类型。
域&＃xff1a;
可在基本类型上施加完整性约束


create domain DDollars as numeric(12,2) not null;


域可作为属性类型。
类型和域之间两个大的差别&＃xff1a;
- 域上可声明约束&＃xff0c;可为域类型变量定义默认值。用户定义类型上不可。
- 域不是强类型的。只要不同域基本类型相容&＃xff0c;则相互间可转换。


create domain YearlySalary numeric(8,2)// 约束命名constraint salary_value_test check(value >&＃61; 29000.00);


create table的扩展

创建与现有某个表的模式相同的表


create table temp_instructor like instructor;


书写一个复杂查询时&＃xff0c;把查询的结果存储成一个新表通常是很有用的。
这个表通常是临时的。


create table t1 as// 新表所参照的表(select *from instructorwhere dept_name&＃61; ’Music’)// 表示新表使用前面表的数据初始化with data;


模式&＃xff0c;目录与环境

当代数据库系统提供了三层结构的关系命名机制。
最顶层由目录构成&＃xff0c;每个目录都可包含模式&＃xff0c;
每个用户有一个默认的目录和模式&＃xff0c;这个组合对用户来说是唯一的。
为了唯一标识出一个关系&＃xff0c;须使用&＃xff1a;


// 目录.模式.关系catalog5.univ_schema.course


当名字的目录被认为是连接的默认目录时&＃xff0c;可省略目录部分。
如果目录和模式均为默认时&＃xff0c;均可省略。


授权

对数据的授权包括
- 授权读取数据
- 授权插入新数据
- 授权更新数据
- 授权删除数据
每种类型的授权都称为一个权限。
除了在数据上授权&＃xff0c;还可被授予在数据库模式上的权限
权限授予或撤销


权限的授予与收回

SQL标准包括select, insert, update, delete
授权


grant <privilege list>on <relation name or view name>to <user/role list>;grant select on department to Amit, Satoshi;grant update (budget) on department to Amit, Satoshi;


关系上的select权限用于读取关系中的元组
关系上的update权限允许用户修改关系中的任意元组
关系上的insert权限允许用户往关系中插入元组。
关系上的delete权限允许用户从关系中删除元组
用户名public指系统的所有当前和将来的用户。
授予权限&＃xff0c;允许权限接收者可作为接收权限授予者收回权限


revoke <privilege list>on <relation name or view name>from <user/role list>;revoke select on department from Amit, Satoshi;revoke update (budget) on department from Amit, Satoshi;


角色

create role instructor;// 基于角色授予权限grant select on takesto instructor;// 角色可授予给用户&＃xff0c;也可授予给其他角色grant dean to Amit;create role dean;grant instructor to dean;grant dean to Satoshi;


一个或一个角色的权限包括&＃xff1a;
- 直接授予用户/角色的权限
- 所有授予给用户/角色所拥有角色的权限


视图的授权

// 创建视图create view geo_instructor as(select *from instructorwhere dept_name &＃61; ’Geology’);// 实际会select　instructor需要用户有此权限select *from geo_instructor;


模式的授权

SQL标准为数据库模式指定了一种基本的授权机制&＃xff1a;
只有模式的拥有者才能够执行对模式的任何修改。如创建/删除关系&＃xff0c;增加/删除关系的属性&＃xff0c;增加/删除索引。
SQL的references权限可与update权限类似方式授予到特定属性。


// 允许Mariano创建&＃xff0c;可参照department的码dept_name的关系grant references (dept_name) on department to Mariano;


权限的转移

默认下被授予权限的用户/角色无权把得到的权限再授予另外的用户/角色
如授权时&＃xff0c;允许接受者把得到的权限再传递给其他用户&＃xff0c;
可在grant命令后附加with grant option子句。
如希望授予Amit在department上的select权限&＃xff0c;且允许Amit将该授权授予其他用户&＃xff0c;
可写&＃xff1a;


grant select on department to Amit with grant option;


一个对象【关系/视图/角色】的创建者拥有该对象上的所有权限&＃xff0c;
包括给其他用户授权的权限。例&＃xff1a;
考虑teaches关系上更新权限的授予
假设最初数据库管理员将teaches上的更新权限授给用户U1,U2和U3
他们接下来又将这一授权传递给其他用户


权限的收回

从一个用户/角色那里收回权限可能导致其他用户/角色也失去该权限。
这一行为称作级联收回。


revoke select on department from Amit, Satoshi // 此时如果存在级联收回&＃xff0c;系统返回一个错误&＃xff0c;且不执行收权动作// 可用cascade替换restrict来表示需要级联收回。// cascade可用省略&＃xff0c;它是默认行为restrict;revoke grant option for select on department from Amit;


SQL允许权限由一个角色授予&＃xff0c;而不是由用户来授予。
SQL有一个与会话所关联的当前角色概念&＃xff0c;
默认下&＃xff0c;一个会话所关联的当前角色是空的&＃xff0c;
一个会话所关联的当前角色可通过执行set role role_name来设置
指定的角色必须已经授予给用户。如果要在授予权限时将授权人设置为一个会话所关联的当前角色&＃xff0c;
且当前角色不为空的话&＃xff0c;
可在授权后加
granted by current_role假设将角色instructor授给Amit是用granted by current_role子句实现的&＃xff0c;
当前角色被设置为dean而不是授权人&＃xff0c;
则&＃xff0c;从Satoshi处收回角色/权限不会导致收回以角色dean为授权人所授予的权限。