数据库系统概念4中级SQL

4.1 连接表达式

连接谓词（join predicate）

4.1.1 连接条件

join……using子句，是一种自然连接的形式，只需要在指定属性上的取值匹配。
on条件允许在参与链接的关系上设置通用的谓词。该谓词写法与where子句类似。与using条件一样，on条件出现在连接表达式的末尾。

select *
from student join takes on student.ID = takes.ID;

在自然连接中，把on子句中的谓词移动到where子句中，与使用on子句是等价的
但是引入on有两个优点：

• 外连接on的表现与where条件是不同的
• 如果在on子句中指定连接条件，并在where子句中出现其余条件，这样的SQL查询通常更容易让人读懂

4.1.2

**外连接（outer join）**可以在结果中创建包含空值元组的方式，保留了哪些在自然连接中丢失的元组

有三种形式的外连接

• **左外连接（left outer join）**只保留出现在运算左边的关系中元组
• **右外连接（right outer join）**只保留出现在右边的关系中元组
• **全外连接（full outer join）**保留出现在两个关系中的元组
  按照如下的方式计算左外连接：首先计算出内连接的结果；然后再内连接的左侧关系中任意一个与右侧关系中任意元组都不匹配的元组t，向连接结果中加入一个元组r，r的结构如下：
  • 元组r从左侧关系得到的属性被赋为t中的值
  • r的其他属性被赋为空值
    on和where在外连接中的表现是不同的，其原因是外连接知识哪些对响应内连接结果没有贡献的元组补上空值并加入结果。on条件是外连接生命的一部分，单where子句却不是。
    假设把on子句谓词换成where子句，并使用on条件true

select *
from student left outer join takes on true
where student.ID = takes.ID

早先使用on条件的左外连接结果包含元组（70577，Snow，Physics，0，null，null，null，null，null，null），因为takes中没有ID=70557的值，所以产生的结果中就没哟uID=70557的元组

4.1.3 连接类型和条件

SQL中把常规连接成为内连接（inner join），关键词inner是可选的，模式连接类型是inner join
连接类型：

• inner join
• left outer join
• right outer join
• full outer join

连接条件：

• natural
• on
• using

任意连接形式可以和任意的链接条件进行组合

4.2 视图

让用户看到整个逻辑模型是不合适的，处于安全考虑可能需要向用户隐藏特定的数据，比如工资值，除了安全考虑可能希望创建一个比逻辑模型更适合特定用户直觉的个人化的关系集合
SQL允许通过查询来定义“虚关系”，在概念上包含查询的结果，虚关系并不与计算并存储，而是在使用虚关系的时候才通过执行查询计算出来。
任何像这种不是逻辑模型的一部分，但作为虚关系对用户课件的关系称为视图（view），任何给定的实际关系集合上能够支持大量视图。

4.2.1 视图定义

create view v as <query expression>;

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

4.2.2 SQL查询中使用视图

在查询中，视图名可以出现在关系名可以出现的任何地方
视图的属性名可以按下述方式显示指定

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

视图实现原理：当定义一个视图时，数据库系统存储视图的定义本身，而不存储定义盖世兔的查询表达式的结果。一旦视图关系出现在查询中，它就被已存储的查询表达式代替。
视图可能被用到定义另一个视图的表达式中。

4.2.3 物化视图

特定数据库允许存储视图关系，但是他们保证：如果用于定义视图的实际关系改变，视图也跟着修改。这样的视图被称为物化视图（materialized view）

保持物化视图一直在最新状态的过程称为物化视图维护（materialized view maintenance），或者通常简称视图维护（view maintenance）

常见的物化视图的更新方式包括：修改相关的关系时立即更新，视图被访问时立即更新以及周期性更新，某些数据库系统允许数据库管理员来控制在这个物化视图上需要采取的方式。

SQL没有定义指定物化视图的标准方式。很多数据库系统提供了各自的SQL扩展来实现这项任务。

物化视图的好处：频繁使用视图的场景，获取大关系的聚集计算结果很小

4.2.4 视图更新

视图的更新会使相关的关系被更新，视图可能只是包含关系的部分字段，更新时无法提供全部必填字段，另外视图创建的时候可能设置的一些过滤条件，而更新的记录不满足这些过滤条件，那么更新的结果也不会反映在最新的视图中。基于这些原因除了一些有限的情况之外，一般不允许对视图关系进行修改。不同的数据库系统指定了不同的条件以允许更新视图关系。

如果定义视图的查询同时满足下列条件，则该视图是**可更新（updatable）**即视图上可执行插入、更新或删除

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

举例，下面的视图是允许update、insert和delete操作

create view history_instructors as
select *
from instructor
where dept_name = 'History';

SQL1999 对应何时可以在视图上执行插入、更新和删除有更复杂的规则

4.3 事务

事务（transaction）由查询或更新语句的序列组成。SQL标准规定当一条SQL语句被执行，就隐式地开始了一个事务。下列SQL语句之一会结束一个事务

• Commit work：提交当前事务
• Rollback work：回滚当前事务

默认方式下每个SQL语句自成一个事务，且一旦执行完就提交。如果一个事务要执行多条sQL语句，就必须关闭单独SQL语句的自动提交。

4.4 完整性约束

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

• 教师姓名不能为null
• 任意两位教师不能有相同的教师标识
• course关系中的每个系名必须在department关系中有一个对应的系名
• 一个系的预算必须大于0.00美元

完整性约束可以在创建关系的create table命令的一部分被声明。也可以通过alter table table-name add constraint命令事假到已有关系上。系统首先保证关系满足指定的约束。如果满足，那么约束就被施加到关系上；如果不满足，则决绝执行上述命令。

4.4.1 单个关系上的约束

除了主码约束之外，还有许多其他可以包括在create table命令中的约束，允许的完整性约束包括：

• not null
• unique
• check（<谓词>）

4.4.2 not null 约束

SQL禁止在关系模式的主码出现空值。因此它不必显式声明为not null

unique约束

unique(A1,A2,……,An)

unique声明指出属性A1,A2……An形成了一个候选码，即在关系中没有两个元组能在所有列出的属性上取值相同。然后候选码属性可以为null，除非它们已经被显式地声明为not null

4.4.4 check子句

当应用于关系声明时，check§子句指定一个谓词P，关系中的每个元组都必须满足谓词P。通常用check子句来保证属性值满足指定的条件。

create table section
  (course_id varchar(8),
    sec_id varchar(8),
    semester varchar(8),
    year numertic(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')));

当前还没有一个广泛使用的诗句哭产品允许包含子查询的谓词。

4.4.5 参照完整性

保证在一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现。这种情况称为参照完整性（referential integrity）

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

由于有了与外码声明相关联的on delete cascade子句，如果删除department中的元组导致此参照完整性约束被违反，系统不是直接决绝，而是对course关系做“级联”删除，即删除参照了被删除系的元组。SQL还允许foreign key子句指明除cascade意外的其他动作，如果约束被违反：可将参照域（dept_name）置为null（用set null代替cascade），或者置为域默认值（用set default）

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

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

事务可能包括几个步骤，在某一步之后完整性约束也许会暂时被违反，但是后面的某一步也许就会消除这个违反。
举例：一个主码为name的person关系，还有一个属性是spouse，并且spouse是person上的一个外码，约束要求spouse属性必须包含在person表里出现的名字。假设需要插入两个元组，一个关于john的，另一个是关于mary的，这两个配置属性分别设置为Mary和john，无论先插入哪个元组插入第一个元组的时候都会违反外码约束。
为了处理这种情况，SQL标准允许将initially deferred子句加入到约束声明中，这样完整性约束不是在事务的中间步骤上检查，而是在事务结束的时候检查。
执行set constraints constraint-list deferred语句作为事务的一部分，会导致会指定约束的检查被延迟到该事务结束时执行。

4.4.7 复杂check条件与断言

复杂check检查还没有被大多数数据库系统支持。例如：

check (time_slot_id in (select time_slot_id from time_slot))

复杂check条件在确保完整性约束是很有用，但其检测开销可能会很大。

4.5 SQL的数据类型与模式

4.5.1 SQL中的日期和时间类型

• date：日历日期，包括年（四位）、月和日
• time：一天中的时间，包括小时、分和秒，可以用变量time（p）表示秒的小数点后的数字危数。通过指定time with timezone，还可以把时区信息连同时间一起存储
• timestamp：date和time的组合。可以用变量timestamp（p）来表示秒的小数点后的数字位数，如果指定with timezone，则时区信息会被存储

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返回当前的本地时间（不带时区）
current_timestamp（带有时区）返回时间戳
localtimestamp（本地日期和时间，不带时区）
interval数据类型，允许在日期、时间和时间间隔上进行计算。假设x和y都是date类型，那么x-y就是时间间隔类型

4.5.2 默认值

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

4.5.3 创建索引

索引（index），它允许数据库系统高效地找到关系中那些在索引属性上取给定值的元组，而不用扫描关系中的所有元组。

create index studentID_index on student(ID);

4.5.4 大对象类型

SQL提供字符数据的大对象数据类型（clob）和二进制数据的大对象数据类型（blob）
字符“lob”代表“Large Object”

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

把整个大对象放入内存中是非常低效和不显示的。相反，一个应用通常用一个SQL查询来检索出一个大对象的“定位器”，然后再宿主语言中用这个定位起来操纵对象。

4.5.5 用户定义的类型

SQ支持两种形式的用户定义数据类型。第一种称为独特类型（distinct type），另一种称为结构化数据类型（structured data type），允许创建具有嵌套记录结构、数组和多重集的复杂数据类型。
把一个教室的姓名赋给一个系名可能是一个程序上的错误；类似地，把一个以美元表示的货币值直接与一个以英镑表示的货币值进行比较几乎可以肯定是程序上的错误。一个好的类型系统应该能检测出这类赋值比较。为了支持这种检测，SQL提供了独特类型（distinct type）的概念
可以用create type子句类定义新类型

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

一种类型的数值可以被**转换（也即cast）**到另一个域

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

SQL提供了drop type和alter type子句来删除或修改以前创建过的类型
SQL1999之前，SQL有一个相似但稍有不同的概念：域domain

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

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

create domain YearlySalary numeric(8,2)
    contraint salary_value_test check(value > 29000.00);

create domain degree_level varchar(10)
    contraint degree_level_test
        check(value in ('Bachelors', 'Masters' or 'Doctorate'));

PostgreSQL支持 create domain结构，但是其create type结构具有不同的语法和解释。

4.5.6 create table的扩展

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

create table temp_instructor like instructor;

把查询的结果存储成一个新表

create table t1 as
    (select *
     from instructor
     where dept_name = 'Music')
with data;

在默认情况下，列的名称和数据类型是从查询结果中推到出来的。
不同数据库系统实现不同语法支持create table……和create table……as
create table……as语句与create view语句非常相似，两者主要区别在于当表创建时的内容被加载，但视图的内容总是反映当前查询的结果。

4.5.7 模式、目录与环境

早期数据库系统也只为所有关系提供一个命名空间。用户不得不相互协调以保证他们没有对不同的关系使用同样的名字。当代数据库系统提供了三层结构的关系命名机制。最顶层由目录（catalog）构成，每个目录都可以包含模式（schema）。注入关系和视图那样的SQL对象都包含在模式中。(一些数据实现用术语“数据库”代替术语“目录”)

要再数据库上做任何操作，用户都必须先连接数据库。为了验证用户身份，用户必须提供用户名以及密码。每个用户都有一个默认的目录和模式，这个组合对影虎来说是唯一的。
为了唯一标识出一个关系，必须使用一个名字，它包含三部分

catalog5.univ_shema.course

如果用户想访问存在于另外的模式中的关系，而不是该用户的默认模式、那就必须指定模式的名字。如果一个关系存在于特定用户的默认模式中，那么连模式的名字也可以省略。

create schema
drop schema

4.6 授权

可以给一个用户在数据库的某些部分授予几种形式的权限。对数据的授权包括：

• 授权读取数据
• 授权插入新数据
• 授权更新数据
• 授权删除数据
  每种类型的手段都称为一个权限（privilege）。可以在数据库的某些特定部分（如一个关系或试图）上授权给用户所有这些类型的权限、或者完全不授权、或者这些权限的补一个组合。
  除了数据上的授权之外，用户还可以被授予在数据库模式上的权限。可以允许创建、修改或删除关系。拥有某些形式的权限的用户还可以把这样的权限转授（授予）给其他用户，或者撤销（收回）一种此前授出的权限。
  最大的授权形式是被授予数据库管理员的。数据库管理员可以授权新用户、重构数据库。等等

4.6.1 权限的授予与收回

SQL标准包括select、insert、update和delete权限，权限所有权限（all privileges）可以用作所有允许权限的简写形式。一个创建了新关系的用户将自动被授予该关系上的所有权限。
SQL包括授予和授权权限。grant用来授予权限

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

如果grant语句中包括update权限，将被授予update权限的属性列表可以出现在紧跟着关键字update的括号中。属性列表是可选项，如果省略属性列表，则授予的是关系中所有属性上的update权限

grant update(budget) on department to Amit, Satahi;

用户名public指系统的所有当前用户和将来的用户。因此，对public的授权隐含着对所有当前用户和将来用户的授权

使用revoke语句来收回权限

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

4.6.2 角色

在数据库中建立一个角色集，可以给角色授予权限，就和给每个用户授权的方式完全一样。每个数据库用户被授予一组他有权扮演的角色。这样就无需为每个用户都重新授予权限。
角色可以授予给用户，也可以授予给其他角色

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

一个用户或一个角色的权限包括：

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

当一个用户登录到数据库系统时，在此回话中用户执行的动作拥有所有直接授予该用户的权限，以及所有（直接地或通过其他角色间接）授予该用户所有角色的权限。

4.6.3 视图的授权

通过创建视图被将其访问权限授予用户，可以控制用户对定义视图的关系的访问
创建视图的用户不需要获得该视图上的所有权限。
如果用户创建一个视图，而词用户在该视图上不能获得任何权限，系统会拒绝这样的视图创建请求。视图创建者必须在关系表上具有select权限。
默认情况下，函数和过程具有其创建者所拥有的所有权限，在效果上，该函数或过程的运行就像其被创建者调用了那样。如果函数定义有一个额外的sql security invoker子句，那么他就再调用该函数的用户的权限下执行，而不是在函数定义者的权限下执行。

4.6.4 模式的授权

只有模式的拥有者才能执行对模式的任何修改，诸如创建和删除关系，增加或删除关系的属性，以及增加或删除索引。
SQL的references权限可以与update权限类似的方式授予到特定属性上，允许用户mariano创建这样的关系，他能够参照department关系的码dept_name

grant references (dept_name) on department to Mariano;

4.6.5 权限的转移

如果在授权时允许接受者把得到的权限再传递给其他用户，可以在相应的grant命令后面加with grant option子句。举例：授予Amit在department上的select权限，并且允许Amit将该权限授予其他用户

grant select on department to Amit with grant option;

一个对象（关系/视图/角色）的创建者拥有该对象上的所有权限，包括给其他用户授权的权限。

4.6.6 权限的收回

从一个用户/角色那里收回权限可能导致其他用户/角色失去该权限，这一行为成为级联收回，级联是默认行为，然后revoke可以申明restrict来防止级联收回

revoke select on department from Amit, Satoshi restrict;

这种情况下，如果㛮任何级联收回，系统会返回一个错误，并且不执行收权动作。
cascade代表需要级联收回，cascade可以省略，他是默认行为

为了处理级联回收，SQL允许权限由一个角色授予，而不是由用户授予。SQL有一个与绘画所关联的当前角色的概念。在默认情况下，一个绘画所关联的当前橘色是空的。一个绘画所关联的当前角色可以通过set role role_name来设置。指定的角色必须已经授予给用户否则set role语句执行失败。
如果要再授予权限时将授权人设置为一个会话所关联的当前角色，并且当前角色不为空的话，可以在授权语句后面加上

granted by current_role