数据库系统概念：中级SQL，视图与授权

CH4 Intermediate SQL

文章目录

• • CH4 Intermediate SQL
  • • 4.2 视图
    • • 需求
      • 虚关系
      • 4.2.1 视图定义
      • 4.2.2 SQL查询中使用视图
      • • 1.视图的直接使用
        • 2.视图中的属性的指定
        • 3.在另一个视图的定义中使用该视图
      • 4.2.3 视图更新
    • 4.3 事务
    • • 4.3.1 事务的回滚
      • 4.3.2 事务的原子性
    • 4.4 完整性约束
    • • 4.4.1 单个关系上的约束
      • 4.4.2 `not null`约束
      • 4.4.3 `unique`约束
      • 4.4.5 `check`子句
      • 4.4.5 参照完整性
      • • 1.参照完整性
        • 2.外码声明
        • 3.参照完整性约束和外码约束
        • 4.违反参照完整性约束的处理
        • • a.通常处理
          • b.级联处理
          • c.置空
          • d.置为默认
      • 4.4.6 事务中对完整性约束的违反
      • 4.4.7 复杂`check`条件与断言
      • • 1.复杂`check`
        • 2.断言
    • 4.5 SQL的数据结构与模式
    • • 4.5.1 SQL的日期和时间类型
      • • 1.date
        • 2.time
        • 3.timestamp
        • 4.字符串到时间的转换
        • 5.时间的获取
      • 4.5.2 默认值
      • 4.5.3 创建索引
      • 4.5.4 大对象类型
      • 4.5.5 用户定义的数据类型
      • • 1.用户定义类型
        • 2.域
        • 3.类型和域的不同
    • 4.6 授权
    • • • 数据授权
        • 模式授权
      • 4.6.1 权限的授予与收回
      • • 1.SQL标准中的权限
        • 2.权限的授权
        • 3.权限的收回
      • 4.6.2 角色 Roles
      • 4.6.3 视图的授权
      • 4.6.4 模式的授权
      • 4.6.5 权限的转移
      • 4.6.6 权限的收回

4.2 视图 需求

• 让用户看到整个的逻辑模型是不合适的（即存储在数据库中的所有实际关系）。必要时要向用户隐藏特定的数据。

• 有特定的需求，如下：

  比如提取物理系的所有课程，以及课程的具体信息。可以写下面的关系：

  select course.course_id, sec_id, building, room_number
  from course, section
  where course.course_id = section.course_id and
  	course.dept_name = 'Physics' and
  	section.semester = 'Fall' and
  	section.year = '2009';
  	
  +-----------+--------+----------+-------------+
  | course_id | sec_id | building | room_number |
  +-----------+--------+----------+-------------+
  | PHY-101   | 1      | Watson   | 100         |
  +-----------+--------+----------+-------------+
  

  然后把得到的关系存储起来提供给用户。

  但这样会带来问题：一旦course或者section关系中的底层数据发生变化，那么所存储的查询结果就不再与在这些关系上重新执行查询的结果进行匹配。造成数据不一致问题。

虚关系

SQL通过查询来定义“虚关系”，在概念上包含查询的结果。

虚关系不是预先计算出来的，而是在使用虚关系的时候才通过执行查询被计算出来。

4.2.1 视图定义

视图：不是逻辑模型的一部分，但作为虚关系对用户可见的关系称为视图。

在SQL中视图的定义如下：

create view v as ;

其中v表示视图名，表示任何合法的查询表达式。

那么针对一开始的两个需求，视图的定义如下：

create view faculty as
select ID, name, dept_name
from instructor;

create view physics_fall_2009 as
	select course.course_id, sec_id, building, room_number
	from course, section
	where course.course_id = section.course_id and
	course.dept_name = 'Physics' and
	section.semester = 'Fall' and
	section.year = '2009';

数据库系统中存储的是与视图关系相关联的查询表达式。视图关系被访问时，其中的元组是通过计算查询结果而被创建出来的。

4.2.2 SQL查询中使用视图 1.视图的直接使用

定义视图后可以使用视图名指代生成的虚关系。

例：

找到所有于2009年秋季学期在Watson大楼开设的Physics课程。

select course_id, building
from physics_fall_2009
where building = 'Watson';

2.视图中的属性的指定

可以在定义视图的时候指定视图中的各个属性的名称

create view department_total_salary(dept_name, total_salary) as
select dept_name, sum(salary)
from instructor
group by dept_name;

3.在另一个视图的定义中使用该视图

例：

定义视图于2009年秋季学期在Watson大楼开设的Physics课程

create view physics_fall_2009_waston as
select course_id, building
from physics_fall_2009
where building = 'Watson';

4.2.3 视图更新

视图若被用来表达更新，插入或者删除，将会带来严重问题。

假如创建了一个视图instructor_info

create view instructor_info as
select ID, name, building
from instructor, department
where instructor.dept_name = department.dept_name;

下面向该视图进行插入操作：

insert into instructor_info 
values('69987','White','Taylor');

假如没有69987的教师，也没有Taylor大楼的系。那么插入元组的唯一可能办法是向instructor中插入教师：('69987','White',null,null)，并向department中插入元组(null, 'Taylor',null)。我们得到这样的关系：

但是经过这样的操作，视图中仍不会出现这个新插入的元组，所以通过空值来更新instructor和department以更新视图instructor_info是不可行的。

基于种种情况，视图一般不允许被修改。

一般来说，如果定义视图的查询对下面的条件都能满足，我们称SQL视图是可更新的：

• from子句中只有一个数据库关系
• select子句中只包含关系的属性名，不包含任何表达式，聚集或者distinct声明
• 任何没有出现在select子句中的属性可以取空值，即这些属性上没有not null约束，也不构成主码
• 查询中不含有group by和having子句

4.3 事务 4.3.1 事务的回滚

事务由查询或者更新语句的序列组成。
事 务 的 开 始 与 结 束 { S Q L 语 句 被 执 行 ： 隐 式 地 开 始 了 一 个 事 务 结 束 事 务 { Commit work：提交当前事务，将该事务所做的更新在数据库中持久保存。当事务被提交后，一个新事物自动开始 Rollback work：回滚当前事务，撤销该事务中所有的SQL语句对数据库的更新。数据库恢复到执行该事务前的状态 事务的开始与结束begin{cases} SQL语句被执行：隐式地开始了一个事务\ 结束事务begin{cases}text{Commit work：提交当前事务，将该事务所做的更新在数据库中持久保存。当事务被提交后，一个新事物自动开始}\text{Rollback work：回滚当前事务，撤销该事务中所有的SQL语句对数据库的更新。数据库恢复到执行该事务前的状态}end{cases} end{cases} 事务的开始与结束⎩⎪⎨⎪⎧​SQL语句被执行：隐式地开始了一个事务结束事务{Commit work：提交当前事务，将该事务所做的更新在数据库中持久保存。当事务被提交后，一个新事物自动开始Rollback work：回滚当前事务，撤销该事务中所有的SQL语句对数据库的更新。数据库恢复到执行该事务前的状态​​
关键词work在这两条语句中都是可选的。

回滚的作用：

• 事务执行过程中检测到错误，事务回滚是有用的
• 某条SQL语句错误，断电，系统崩溃这些故障，若事务未commit work，那么将被回滚，回滚后重新执行。

4.3.2 事务的原子性

事务原子性：不可分割性，要么事务的所有影响被反映到数据库中，要么任何影响都没有。

事务在完成所有步骤后提交，或者在不能成功完成其所有的动作，通过这种方式数据库提供了对事物具有原子性的抽象。

SQL实现：

• 默认每个SQL语句自动生成一个事务，且一执行结束就提交。
• 如果一个事务要执行多条SQL语句，就必须关闭单独SQL语句的自动提交。

4.4 完整性约束

完整性约束：保证授权用户对数据库所做的修改不会破坏数据的一致性。

完整性约束防止的是对数据的意外破坏。

完整性约束的示例：

• 教师姓名不能是null
• 任意两位教师不能有相同的教师标识
• 一个系的预算必须大于0.00美元

完整性约束的施加：

• 完整性约束一般是数据库设计过程中的一部分，它用于创建关系的create table命令的一部分被声明。3.2.2
• 可以通过使用alter table table-name add constraint命令施加到已有的关系上。

4.4.1 单个关系上的约束

create table中允许的完整性约束包括：

• not null
• unique
• check(<谓词>)

4.4.2 not null约束

默认情况下，空值是所有域的成员，每个属性的合法值。

某些情况下希望禁止空值：

name varchar(20) not null
budget numeric(12,2) not null

not null禁止在该属性上插入空值。若插入会报错。

特别的，若属性已有主码约束，那么就不用显式地加入not null约束。

4.4.3 unique约束

unique(Ai1,Aj2,...,Ajm)

unique声明指出 A i 1 , A j 2 , . . . , A j m A_{i1},A_{j2},...,A_{jm} Ai1​,Aj2​,...,Ajm​形成了一个候选码：在关系中没有两个元组能在所有列出的属性上取值相同。

候选码与主码不同的地方：

• 候选码属性值可以是null，除非被显式的声明为not null

4.4.5 check子句

check(P)子句指定一个谓词P，关系中的每个元组都必须满足谓词P。

通常使用**check子句来保证属性值满足指定的条件**。

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', 'Summer', 'Spring')));

这里使用check子句模拟了一个枚举类型，指定是四个值其中之一。

check(budget > 0)保证预算取值为正。

4.4.5 参照完整性 1.参照完整性

参照完整性 referential integrity：保证一个关系中给定属性集上的取值也在另一关系的特定属性集的取值之中出现。

2.外码声明

外码声明：foreign key(dept_name) references department

表明每个课程元组中的系名必须在department关系中存在。

外码约束定义：

令关系r1和r2的属性集分别为R1和R2，主码分别为K1和K2。如果要求对r2中的任意元组t2，均存在r1中的元组t1，使得
t 1 . K 1 = t 2. α t_1.K_1 = t2.alpha t1​.K1​=t2.α
我们称R2的子集 α alpha α为参照关系r1中的外码。

3.参照完整性约束和外码约束

不同于外码约束，参照完整性约束通常不要求K1是R1的主码。其结果是r1中可能有不止一个元组在属性K1上取值相同。

默认情况下，SQL中外码参照的是被参照表中的主码属性。

SQL支持显式指定被参考关系的属性列表的reference子句。这个指定的属性列表必须声明为被参考关系的候选码。
候 选 码 { p r i m a r y   k e y u n i q u e 候选码begin{cases} primarytext{ }key\ unique end{cases} 候选码{primary keyunique​

dept_name varchar(20) references department;

在属性定义中写法，以及声明该属性为外码。

4.违反参照完整性约束的处理 a.通常处理

通常处理：拒绝执行改操作，进行回滚等。

b.级联处理

特别的：foreign key中可以指定，如果被参照关系上的删除或更新操作违反了约束，系统采取步骤修改关系中的元组来恢复完整性约束，而不是拒绝这样的动作。

create table course
(...
foreign key (dept_name) references department
	on delete cascade
	on update cascade
...);

由于有了与外码关联的on delete cascade子句。如果删除department中的元组导致了此参照完整性约束被违反，则删除并不被系统拒绝，而是采取级联删除。相似的，如果更新违反参照完整性约束，那么更新不被拒绝，而是采取级联更新。

如果一个级联更新或者删除导致的对约束的违反不能通过进一步的级联操作解决，则系统终止该事务。于是该事务所做的所有改变及级联动作将被撤销。

c.置空

将参照域，即dept_name置空，set null代替cascade

d.置为默认

set default代替cascade

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

事务可能包括几个步骤，在某一步之后完整性约束也许会暂时被违反，但是后面的某一步也许就会消除这个违反。

例：

有主码为name的person关系，还有一个属性是spouse,并且spouse是在person上的一一个外码。也就是说，约束要求spouse属性必须包含在person表里出现的名字。
假设我们希望在上述关系中插入两个元组，一个是关于John的，另一个是关于Mary的，这两个元组的配偶属性分别设置为Mary和John,以此表示John和Mary彼此之间的婚姻关系。无论先插人哪个元组，插人第一个元组的时候都会违反外码约束。在插人第二个元组后，外码约束又会满足了。

解决方案：

• 添加约束initially deferrsd子句。

  完整性约束不是在事务的中间步骤上检查，而是在事务结束的时候检查。一个约束可以被指定为可延迟的( deferrable)。

  set constraints constraint-list deferred
  

• 默认的方式是立即检查约束，而且许多数据库实现不支持延迟约束检查。
  如果spouse属性可以被赋为null,我们可以用另一种方式来避开在上面例子中的问题：在插人John和Mary元组时，我们设置其spouse属性为null,然后再更新它们的值。然而，这个技术需要更大的编程量，而且如果属性不能设为null 的话，此方法就不可行。

4.4.7 复杂check条件与断言 1.复杂check

check(P)，谓词P可以是包含子查询的任意谓词：

check (time_slot_id in (select time_slot_id from time_slot))

check子句的检查时刻：

• 在section中插入元组时需要检测
• time_slot改变时也需要检测

复杂check的检测开销会很大。

2.断言

一个断言就是一个谓词，表达了我们希望数据库总能满足的一个条件。

断言的特殊形式：

• 域约束
• 参照完整性约束

特别的约束：

• student的所有元组，他在属性tot_cred上的值必须等于该学生成功修完所有课程的学分总和
• 每位教师不能在同一学期的同一个时间段在两个不同的教室授课

SQL中的断言：

create assertion  check 

比如特别约束第一个的写法：

create assertion credits_earned_constraint check
(no exists (select ID 
           from student
           where tot_cred < > (select sum(credits)
                              from takes
                              where takes.ID = student.ID and
                              grade is not null and grade < > 'F')));

特别约束的第二个的写法：

create assertion teache_time_constraint check
(unique (select I.ID, T.semester, T.time
            from instructor I
         	left outer join teaches T
         	on I.ID = T.ID)     	
)

4.5 SQL的数据结构与模式

除了3.2中的基本数据类型，还有内置的一些数据类型

4.5.1 SQL的日期和时间类型 1.date

日历日期，包括年（四位），月和日

date '2001-04-25'

2.time

一天中的时间，包括小时，分和秒。

可以用变量time(p)来表示秒后面的小数点后的数字位数：默认为0

通过指定time with timezone可以把时区信息连同时间一起存储。

time '09:30:00'

3.timestamp

date和time的组合。可以用变量timestamp(p)来表示秒后面的小数点后的数字位数：默认为6.

timestamp '2001-04-25 10:29:01.45'

4.字符串到时间的转换

cast e as t形式将e转换为t。t是上述三种的一种，其中字符串格式必须满足要求。

5.时间的获取

• extract(field from d)从date或者time值中提取单独的域：year，month，day，hour等

• interval数据类型，允许在时间，日期，时间间隔上进行计算。

  假设x和y都是date类型，那么x - y就是时间间隔类型。

4.5.2 默认值

SQL允许指定属性为默认值，在create table语句中如下：

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));

这样tot_cred的默认值即为0。当我们插入一个元组进入student，如果没有给定tot_cred的值，那么会自动赋0.

insert into student(ID, name, dept_name) 
values('12789', 'New')

4.5.3 创建索引

在关系的属性上创建的索引是一种数据结构，允许数据库系统高效的找到关系中的那些在索引属性上取给定值的元组，而不用扫描所有的元组。

创建索引的数据结构：

create index studentID_index on student(ID);

如果用户提交的查询可以从索引的使用中获益，那么SQL查询处理器会自动使用索引。

4.5.4 大对象类型

需求：存储很大（KB，GB级别的）属性。

SQL提供字符数据的大对象数据类型（clob）和二进制数据的大对象数据类型（blob）。

属性声明：

book_review clob(10KB)
image blob(10MB)
movie blob(2GB)

关于大对象的存储：

直接存储大对象到内存中十分不划算，所以通常用SQL查询检索出一个大对象的“定位器”。在宿主语言中使用定位器操纵对象。

4.5.5 用户定义的数据类型

SQL支持两种用户定义数据类型：

• 独特类型（distinct type）
• 结构化数据类型（structured data type）

1.用户定义类型

设有下面的关系模式：

create table student
(ID varchar(10),
 name varchar(10),
 dept_name varchar(10),
balance_pounds numeric(3,1),
balance_rmb numeric(3,1));

假如我们把dept_name赋值给name，或者是将balance_pounds和balance_rmb进行比较，那么都是无意义或者是错误的。我们希望数据库能检测出这样的错误。于是有了用户定义类型：

create type Dollars as numeric(12,2) final;
create type Pounds as numeric(12,2) final;

有了这样的定义，那么我们可以将department定义为：

create table department
(dept_name varchar(10),
 building varchar(15),
 budget Dollars);

注意的是，定义了这种类型后将会有强类型检查，表达式(department.budget + 20)将不会被接受。

一种类型的数值可以转换到另一个域。

cast(department.budget to numeric(12,2));

同时使用drop type和alter type可以删除或者修改类型。

2.域

域domain：在基本类型上施加完整性约束。

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

这样DDollars可以作属性类型。

可以把**check子句用在域上**，允许模式设计者指定一个谓词，被声明为来自该域的任何变量都必须满足这个谓词。

create domain YearySalary numeric(8,2)
constraint salary_value_test check(value >= 29000.00);

3.类型和域的不同

• 域上可以声明约束，如not null。类型上不能施加约束。
• 域不是强类型的。一个域类型的值可以被赋给另一个域类型，只要他们的基本类型是相容的。

4.6 授权 数据授权

对数据的授权包括：

• 授权读取数据
• 授权插入新数据
• 授权更新数据
• 授权删除数据

每种类型的授权称为一个权限（privilege）。

在数据库的特定部分，如关系或视图上授权给用户这些类型的权限或不授权或者权限的组合。

模式授权

• 允许创建/修改/删除权限
• 权限转让
• 撤销之前授出的权限

最大的授权形式是被授予数据库管理员的。

4.6.1 权限的授予与收回 1.SQL标准中的权限

• select
• insert
• update
• delete

all priviledges可以用做所有允许权限的简写形式。一个创建了新关系的用户将被自动授权该关系上的所有权限。

2.权限的授权

grant语句用来授予权限。基本形式：

grant <权限列表>
on <关系名或者视图名>
to <用户/角色列表>

如：授权给Amit和Simith在department上的select权限：

grant select on department to Amint, Simith;

关系上的update权限可以授权在整个元组上，也可以授权在某些特定属性上。如果没有特别说明，则授权在所有属性上：

grant update(budget) on department to Amit, Simith

关系上的insert权限允许用户往关系中添加元组。insert也可以指定属性列表。

若指定属性授权update，那么插入时只针对这些属性，其余的属性将使用默认值（如果指定了默认值），或者赋予null。

关系删给的delete允许用户从关系中删除元组。

3.权限的收回

使用revoke权限来收回权限。和grant类似：

revoke <权限列表>
on <关系或视图名>
from <用户/角色列表>

如果要收回我们之前赋予的权限，可以写下面的语句：

revoke select, update(budget)
on department
from Amit, Simith

如果要收回权限的角色已经把权限转让给了别人，那权限的收回将会变得复杂。

4.6.2 角色 Roles

大学里不同的人具有真实世界的角色，创建角色：

create role instructor;

每一个教师必须具有同种类型的权限。

授予权限给某个教师的理想方式：指明每个教师应有的权限，每个角色都应该被打上标签：是不是教师。

角色概念：数据库中创建角色集，可以给角色授予权限。

create role instructor;
grant select on takes to instructor;

角 色 的 授 权 { 用 户 其 他 角 色 角色的授权 begin{cases} 用户\ 其他角色 end{cases} 角色的授权{用户其他角色​

grant dean to Amit;
create role dean;
grant instructor to dean;
grant dean to Simith;

因此，一个用户或者一个角色拥有的权限包括：

• 直接授权给用户/角色的权限
• 所有授权给用户/角色所拥有的角色的权限

4.6.3 视图的授权

例：

某工作人员需要知道Geology系的所有员工的工资，但他不能直接访问instructor关系。若他要访问Geology系的信息，就必须得到一个视图上的访问权限。

我们将这个视图称为geo_instructor，其中仅仅有Geology系的instructor。视图定义：

create view geo_instructor as
    (select * 
    from instructor 
    where dept_name = 'Geology');

grant select on geo_instructor to geo_staff;

可以通过下面请求得到信息：

select * from geo_instructor;

注意：用户在视图上拥有的权限不会超过他在关系本身上具有的权限。

例：如果视图的创建用户在定义视图的关系上没有update权限的话，那么他不能得到视图上的update权限。

4.6.4 模式的授权

基本授权机制：只有模式的拥有者才能执行对模式的任何修改，诸如创建或者删除关系的属性。以及增加或者删除索引。

外码权限的授权：

grant references(dept_name) on department to Mario;

4.6.5 权限的转移

获得了某些形式授权的用户可能被允许将此授权传递给其他用户。

默认情况下，权限不允许转移给其他的用户/角色。

授权时，加上with grant option可以将权限转移给其他用户。

grant select on department to Amit with grant option;

4.6.6 权限的收回

revoke语句收回权限

• cascade表示级联收回，可以省略，因为级联收回是默认行为
• restrict表示不需要级联收回。

revoke select on department from Amit restrict;

revoke grant option for select on department from Amit;

上面的语句只是收回grant option并不是收回select。