数据库系统概念-----中级SQL
本节目录
1、连接表达式
在之前简单介绍了自然连接运算，SQL提供了连接运算的其他形式，包括能够指定显式的连接谓词，能够在结果中包含被自然连接排除在外的元组。
1.1 连接条件
前面介绍的join...using子句，是一种自然连接的形式，只需要在指定属性上的取值匹配，SQL支持另一种形式的连接，其中可以指定任意的连接条件。
on条件允许在参与连接的关系上设置通用的谓词，该谓词的写法与where子句谓词类似，只不过使用关键字on而不是where，与using条件一样，on条件出现在连接表达式的末尾。如下查询：
上述on条件表明，如果一个来自student的元组和一个来自takes的元组在ID上的取值相同，那么它们是匹配的。上例中连接表达式与自然连接几乎是一样的。关系名用来区分属性名ID。
on条件可以表示任何SQL谓词，因此使用on条件的连接表达式就可以表示比自然连接更为丰富的连接条件。使用带有on条件的连接表达式查询可以用不带on的等价表达式来替换，但是on并不是多余的，相反，on有两个优点，首先对于外连接的这类连接来说，on条件的表现与where条件不同，其次，如果在on子句中指定连接条件，并在where子句中出现其余条件，这样的查询更容易让人读懂。
1.2 外连接
如果要一个显示出所有学生的列表，并且显示出它们的在不同关系中的属性。如下：
遗憾的是，上述查询会出现一定的问题，如果有一些学生，他没有选任何课程，那么在takes关系中没有他的ID，这样查询结果会抛弃掉这个学生，也就是说他不会出现在自然连接的结果中。外连接运算与我们学过的连接运算类似，但是在结果中创建包含空值的元组的方式，保留了那些在连接中丢失的元组。
例如，为了保证前面的学生出现在结果中，可以连接结果中加入一个元组，它来自student关系的属性的值设置为该学生的值，剩下takes的属性的值设置为null。
实际上有三种形式的外连接。
*左外连接：只保留出现在左外连接运算之前的关系中的元组。
*右外连接：只保留出现在右外连接运算之后的关系中的元组。
*全外连接：保留出现在两个关系中的元组。
为了与外连接运算区分，此前学习的不保留未匹配元组的连接运算称作内连接运算。左连接运算：首先像前面那样计算出内连接的结果，然后对内连接的左侧关系中任意一个与右侧关系中任何元组都不匹配符的元组t，向连接结果中加入一个元组r，r的构造如下：
*元组r从左侧关系得到的属性被赋给t中的值。
*r的其他属性被赋值为空值。
其查询语句如下：
右外连接和左外连接是对称的，来自右侧关系中的不匹配左侧关系任何元组的元组被补上空值，并加入到右外连接的结果中。这样如果用右外连接重写前面的查询，只需要交换关系的次序。如下：
全外连接是左外连接和右外连接的组合，在内连接结果计算出来后，左侧关系中不匹配右侧关系的人和元组都被添上空值并加到结果中。类似的，右侧关系中不匹配左侧关系任何的元组也被添加上空值被加到结果中。on子句可以和外连接一起使用。前面提到，on和where在外链接中表现不同，原因就是外连接只为那些对应内连接结果没有贡献的元组补上空值并加入结果，on条件是外连接声明的一部分，但where子句不是。
1.3 连接类型和条件
为了把常规连接和外连接区分开，SQL中把常规连接称为内连接，这样的连接子句可以用inner join来替换outer join，说明适用的是常规连接，关键字inner 是可选的，join子句中没有使用outer 前缀也就是其连接类型是inner join。
2、视图
在前面所有的例子中，我们一直在逻辑模型层操作，也就是我们假定了给定的集合中的关系都是实际存储在数据库中的。让所有用户看到整个逻辑模型是不合适的，出于安全考虑，可能要向用户隐藏特定的数据。
SQL允许通过查询来定义虚关系，它在概念上包含查询的结果，婿关系并不预先计算并存储，而是在是用虚关系的时候才通过执行查询被计算出来。
任何像这种不是逻辑模型的一部分，但作为虚关系对用户可见的关系称为视图。
2.1 视图定义
在SQL中用create view 定义视图，为了定义视图，必须给视图一个名称，并且必须提供计算视图的查询，create view 命令的格式为：
其中<query expression>可以是任何合法的查询表达式，v表示视图名。例如，在一个关系中，不想将所有属性都授权，那么可以建立一个视图，如下：
前面说过，视图关系在概念上包含查询结果重的元组，但是不进行预计算和存储，相反数据库系统存储与视图关系相关联的查询表达式，当视图关系被访问时，其中元组是通过计算查询结果而创建出来的，从而视图关系是在需要的才被创建的。
2.2 SQL查询中使用视图
一旦定义了一个视图，就可以使用视图名指代该视图生成的虚关系。在查询中，试图可以出现在任何关系名可以出现的地方。视图的属性名可以按照下面的方式显式指定：
上面视图给出了每个系中所有教师的工资总和，因为表达式sum(salary)没有名称，其属性名是在视图定义中显式指定的。直觉上，在任何给定时刻，视图关系中的元组集是该时刻视图定义中查询表达式的计算结果，因此一个视图关系被计算存储，一旦用于定义该视图的关系被修改，视图就会过期。为了避免这点，视图通常这样实现：当我们定义一个视图时，数据库系统存储视图的定义本身，而不存储定义该视图的查询表达式的执行结果，一旦视图关系出现在查询中，它就被已存储的查询表达式代替，因此，无论什么时候执行这个查询，视图关系都被重新计算。
2.3 物化视图
如果用于定义视图的实际关系改变，视图也跟着修改，这样的视图称为物化视图。保持物化视图一直在最新状态的过程称为物化视图维护，或者通常称为视图维护。频繁地使用视图的应用会从视图的物化中获益。
2.4 视图更新
对于查询来说，视图是一个有用的工具。但是如果用它来表达更新、插入或删除，可能会带来严重的问题，困难在于用视图表达的数据库修改必须翻译成对数据库逻辑模型中实际关系的修改。
所以，除了一些有限的情况，一般不允许对视图关系进行修改，不同的数据库指定了不同的条件以允许更新视图关系。一般来说，如果定义视图的查询对下列条件都能满足则称SQL视图是可更新的（视图上可以执行插入、更新或删除）：
*from子句只有一个数据库关系。
*select子句中只包含关系的属性名，不包含任何表达式、聚集或distinct声明。
*任何没有出现在select子句中的属性可以取空值，也就是这些属性没有not null约束，也不构成主码的一部分。
*查询中不含有group by或having子句。
在这些限制下的视图可以进行更新。
3、事务
事务由查询或更新语句的序列组成，SQL标准规定当一条SQL语句被执行，就隐式的开始一个事务，下列SQL语句会结束一个事务。
*Commit work：提交当前事务，也就是将该事务所做的更新在数据库中持久保存，在事务被提交后，一个新的事务自动开始。
*Rollback work：回滚当前事务，即撤销该事物中所有SQL语句对数据库的更新，这样数据库就恢复到执行该事务第一条语句之前的状态。
关键字work在两条语句中都是可选的。其实，事务提交类似于对编辑的文档进行保存，而回滚就像是不保存变化直接退出。
一个事务在完成所有步骤后提交其行为，或者在不能完成其所有动作情况下回滚其所有动作，通过这种方式数据库提供了对事务具有原子性的抽象，原子性也就是不可分割性。
4、完整性约束
完整性约束保证授权用户对数据库做的修改不会破坏数据的一致性，因此完整性约束防止的是对数据的意外破坏。
4.1 单个关系上的约束
create table 命令不仅用来定义关系表，还可以包括完整性约束语句，除主码约束外，还有许多其他可以包括在create table命令中的约束，允许的完整性约束包括：not null 、unique、check。
4.2 not null 约束
not null 声明禁止在该属性上插入控制，任何可能导致向一个声明为not null 的属性插入空值的数据库修改都会产生错误诊断信息。在许多情况下，SQL禁止在关系模式的主码中出现空值。
4.3 unique 约束
SQL还支持下面这种完整性约束：
unique声明指出属性Aj1、Aj2...形成一个候选码，即在关系中没有两个元组能在所有列出的属性上取值相同，然而候选码可以是null。除非它们被显式的声明为not null。空值不等于任何值。
4.4 check 子句
当应用于关系声明时，check(P)子句指定一个谓词P，关系中的每个元组都满足谓词P。通过用check子句保证属性值满足指定的条件，实际上创建了一个强大的类型系统，如下：
在上面的创建关系中，利用check子句模拟了一个枚举类型，通过制定semester必须是‘Fall’，‘Winter’....中的一个来实现，这样check子句允许以有力的方式对属性域加以限制，这是大多数编程语言类型系统都不允许的。
4.5 参照完整性
我们通常希望保证在一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现，这种情况称为参照完整性。
令关系r1和r2的属性集分别为R1和R2，主码分别为K1和K2，如果要求对r2中任意元组t2均存在r1中元组t1使得t1.k1=t2.a，我们称R2的子集a，为参照关系r1中K1的外码。
这种要求称为参照完整性约束或子集依赖。r2中a上的取值集合必须是r1中K1上的取值集合的子集。默认情况下，SQL外码参照的是被参照表中的主码属性SQL还支持一个可以显示指定被参照关系的属性列表的references子句，然而这个指定的属性列表必须声明为被参照关系的候选码，要么使用primary key约束，要么使用unique约束。在更为普遍的参照完整性约束形式中，被参照的属性不必是候选码，这样的形式还不能在SQL中直接声明。SQL标准提供了另外的结构用于实现这样的约束。
我们通常使用下面的方式作为属性定义的一部分，并声明该属性为外码：
当违反参照性约束时，通常的处理是，拒绝执行导致完整性破坏的操作。但是在foreign key子句中可以致命：如果被参照关系上的删除或更新动作违反了约束，那么系统可以采取一些步骤通过修改参照关系中的元组来恢复完整性约束，而不是拒绝这样的动作。如下：
由于有了与外码声明相关联的on delete cascade子句，如果删除department中的元组导致了此参照完整性约束被违反，则删除并不被系统拒绝，而是对course关系作级联删除，即删除参照了被删除系的元组。
如果存在涉及多个关系的外码依赖链，则在链一端所做的删除或更新可能传至整个链。
空值使得SQL中参照约束的语义复杂化，外码中属性允许为null。只要他们没有被声明为not null。
4.6 事务中对完整性约束的违反
事务可能包括几个步骤，在某一步之后的完整性约束也许会暂时被违反，但是在后面的某一步也许就消除这个违反。为了处理这样的情况，SQL标准允许将initially deferred 子句加入到约束声明中，这样完整性约束不是在事务的中间步骤上检查而是在事务结束的时候检查。一个约束可以被制定为可延迟的。这意味着默认情况下它会被立即检查但是在需要的时候可以延迟检查。对于声明为延迟的约束，执行set constrains constraint-list deferred 语句作为事务的一部分，会导致对指定的约束检查被延迟到该事务结束时执行。
一般默认是立即检查约束，许多数据库不支持延迟检查。
4.7 复杂check 条件与断言
这一节的许多结构还没有被大多数数据库系统支持。
check 子句中的谓词可以是包含子查询的任意谓词，如果一个数据库实现支持在check 子句中出现子查询，那么我们就可以在关系section 上声明如下的参照完整性约束：
这个check条件检测在section关系中的每个元组的time_slot_id的确实在time_slot关系中某个时间段的标识，因此这个条件不仅在section中插入或修改元组时需要检测，而且在time_slot关系改变时也需要检查。
复杂的check条件在我们希望确保数据完整性的时候是很有用的，但是其检测开销极可能会很大。
一个断言就是一个谓词，他表达了我们希望数据库总能满足的一个条件。域约束和参照完整性约束是断言的特殊形式。
SQL中的断言形式如下：
当创建断言时，系统要检测其有效性，如果有效，则以后只有不破坏断言的数据库修改才被允许。如果断言较复杂，则检测会带来相当大的开销。目前，支持check子句谓词中使用子查询或创建断言结构的数据库还比较少。
5、SQL中的数据类型与模式
5.1 SQL中的时期与时间类型
SQL中支持与日期和时间相关的几种数据类型：
*date：日历时间，包括年、月、日
*time：一天中的时间，包括小时、分、秒，可以用变量time(p)来表示秒的小数点后的数字位，默认为0，指定with timezone 可以把时区信息连同时间一起存储。
*timestamp：date和time的组合，可以用变量timetamp(p)来表示秒的小数点后的数字位（这里默认为6），同样可以指定with timezone。
日期和时间类型按如下方式说明：
我们可以使用cast e as t形式的表达式来将一个字符串或字符串表达式e转化为类型t。其中t是date、time、timestamp中的一种。字符串必须符合正确的格式。
可以利用extract(field from d)，从date或time值d中提取出单独的域，域可以是年月日天时分秒中的任意一种，时区信息用timezone_hour或timezone_minute提取。
current_date返回当前日期，current_time返回当前时间（带时区），localtime返回当前本地时间（不带时区），current_timestamp（带有时区）、localtimestamp（本地时期和时间，不带时区）返回时间戳。
SQL允许上面列出所有类型上进行比较运算。
5.2 默认值
SQL允许为属性指定默认值，如下的create table语句所示：
tot_cred默认为0。这样当一个元组插入到student关系中，如果没有给出tot_cred属性的值，那么该元组在此属性上的取值为0。如下插入省略了tot_cred。
5.3 创建索引
在属性的关系上所创建的索引是一种数据结构，允许数据库系统高效的找到那些在索引属性上取给定值的元组，而不用扫描关系中的所有元组。
SQL中没有给出创建索引的正式语法定义，但很多数据库都支持适用如下形式创建索引：
上述语句在student关系的属性ID上创建了一个名为studentID_index的索引。
5.4 大对象类型
许多数据库应用需要存储可能很大的属性，SQL提供字符数据的大对象数据类型（clob）和二进制数据的大对象数据类性（blob）在这些数据类型中字符“lob”表示：large object。例如可以声明属性：、
对于包含大对象的结果元组而言，把整个大对象放入内存中是非常低效和不现实的。一个应用通常用一个SQL查询来检索来一个大对象的定位器，然后在宿主语言中用这个定位器来操纵对象，应用本身也是用宿主语言书写的。
5.5 用户定义的类型
SQL支持两种形式的用户定义数据类型，第一种是独特类型，另一种称为结构化数据类型，允许创建具有嵌套记录结构、数组和多重集的复杂数据类型。
SQL提供了独特类型。可以用create type子句来定义新类型，如下：
把两个用户定义类型定义为总共12位数字的十进制数，其中两位放在小数点后。
SQL提供了drop type 和alter type子句来删除或修改以前创建过的类型。
5.6 create table
应用常常要求创建与现有的某个表的模式相同的表，SQL提供了一个create table like的扩展来支持这项任务，
上述语句创建了一个与instructor具有相同模式的新表temp_instructor。
当书写一个复杂查询时，把查询的结果存储成一个新表通常是有用的，这个表通常是临时的，这里需要两条语句，一条用于创建表（具有合适的列），另一条用于把查询结果插入到表中。SQL2003提供了一种更简单的技术来创建包含查询结果的表，如下：
上面语句创建了表t1，该表包含一个查询结果，在默认情况下，列的名称和数据类型是从查询结果中推到出来的，通过在关系名后面列出列名，可以给列显式指派名字。如果省略with data子句，表会被创建，但不会载入数据。
5.7 模式、目录与环境
当代数据库提供三层结构的关系命名机制，最顶层由目录构成，每个目录都包含模式，如关系、视图那样的SQL对象都包含在模式中。
要在数据库上做任何操作，用户或程序都必须先连接到数据库，位验证用户身份，用户必须提供用户名以及密码，每个用户都有一个磨人的目录和模式，这个组合对用户来说是唯一的，当一个用户连接到数据库系统时，将为该连接设置好默认的目录和模式，这对英语当用户登录进一个操作系统时，把当前目录设置为用户的主目录。为了标识出一个关系，必须使用一个名字，它包含三部分，如下：
当名字的目录部分被认为是连接的默认目录时，可以省略目录部分。如果用户想访问存在与另外的模式中的关系，而不是该用户的默认模式，那必须指定模式的名字。当多个目录和模式可用时，不同应用和不同用户可以独立工作而不必担心命名冲突。一个应用的多个版本可以在同一个数据库上运行。
默认目录和模式是为每个连接建立的SQL环境的一部分，环境还包括用户标识，所有的SQL语句都在一个模式的环境中运行。
我们可以用create schema和drop schema语句来创建和删除模式。
6、授权
我们可能给一个用户在数据库的某些部分授权几种形式的权限，对数据的授权包括：
*授权读取数据
*授权插入新数据
*授权更新数据
*授权删除数据
每种类型的授权都成为一个权限，我们可以在数据库的某些特定部分上授权给用户所有的这些类型的权限，或者完全不授权，或者这些权限的一个组合。当用户提交查询或更新时，SQL执行先基于用户曾获得的权限检查此查询或更新是否是授权过的，如果没有授权，那么被拒绝执行。
除了数据上的授权用户还可以被授予在数据库模式上的权限，例如可以允许用户创建、修改或删除关系。
6.1 权限的授予和收回
SQL标准包括select、insert、update和delete权限，权限所有权限可以用作所有允许权限的简写形式。一个创建了新关系的用户将自动被授予该关系上的所有权限。SQL数据定义语言包括授予和收回权限的命令，grant语句用来授予权限，此语句的基本形式为：
权限列表使得一个命令可以授予多个权限。
关系上的select权限用于读取关系中的元组，如下语句：
授予用户Amit、Satoshi在department关系上的执行查询。
关系上的update权限允许用户修改关系中的任意元组，update权限既可以在关系的所有属性上授予，又可以只在某些属性上授予，如果grant语句包括update权限，将被授予update权限的属性列表可以出现在紧跟关键字update的括号中，属性列表是可选项，如果省略属性列表，则授予的是关系中所有属性上的update权限。
下面的grant语句授予用户Amit和Satoshi在department关系的budget属性上的更新权限：
关系上的insert权限允许用户往关系上中插入元祖，insert也可以指定属性列表，对关系所做的任何插入必须只针对这些属性，系统将其余属性要么赋默认值要么赋null。
关系上的delete权限允许用户从关系中删除元组。
用户名public指系统的所有当前用户和将来的用户。因此对public的授权隐含着对所有当前用户和将来用户的授权。SQL授权机制可以对整个关系或一个关系的指定属性授予权限，但是他不允许对一个关系的指定元组授权。
我们是用revoke语句来收回权限，此语句的形式与grant几乎是一样的。、
要收回前面的权限可以写如下语句：
6.2 角色
在数据库中建立一个角色集，可以给角色授予权限，就和给每个用户授权的方式完全一样，每个数据库用户被授予一组它有权扮演的角色。例如，在一个大学数据库系统里，只要将一个用户设置为教师，那么他就会拥有所有教师的权限，而不用一个一个设置。
在SQL中创建角色如下：
然后可以像用户那样授予权限，如在这样的语句中：
角色可以授予给用户，也可以授予其他角色：
因此，一个用户或一个角色的权限包括：所有直接授予用户、角色的权限。所有授予给用户、角色所拥有角色的权限。
6.3 视图的授权
如果有一个工作人员需要知道一个给定的系里所有员工的工资，他无权查看其他系中员工的相关信息，因此该工作人员对这个关系的访问应该是禁止的，但是他要访问给定的系的信息，就必须得到在一个视图上的访问权限，它可以由下语句店定义：
创建视图的用户不需要获得该视图上的所有权限。
6.4 模式的授权
SQL标准为数据库模式指定了一种基本的授权机制：只有模式的拥有者才能执行对模式的任何修改，如创建或删除关系、增加或删除关系的属性、以及增加或删除索引。
SQL提供一种references权限，允许用户创建关系时声明外码，SQL的references权限可以与update权限类似的方式授予到特定的属性上，下面的grant语句允许用户Mariano创建这样的关系，他能够参照department的码dept_name：
6.5 权限的转移
获得某些形式授权的用户可能被允许将此授权传递给其他用户，在默认方式下，被授予权限的用户、角色无权把得到的权限再授予另外的用户和角色。如果我们在授权时允许接受者把得到的权限再传递给其他用户，我们可以在相应的grant命令后面附加上with grant option子句，如下：
我们将查询权限授予Amit并且允许它将该权限授予其他用户。指定权限从一个用户到另一个用户的传递可以表示为授权图。图中的顶点是用户。
6.6 权限的收回
如上图，假设数据库管理员决定收回U1的授权，那么U4的授权也会被收回，但是U5又从U2处获得过授权，U5还继续拥有授权。
从一个用户或者角色那里收回权限可能导致其他用户角色失去权限，这一行为称为级联收回，在大多数数据库中，级联是默认行为。revoke语句可以申明restrict防止级联收回。
这种情况下如果存在级联收回系统返回一个错误，并且不进行收权动作。可以用关键字cascade来替换restrict，表示需要级联收回，然而cascade可以省略。他是默认行为。
下面的revoke语句仅仅收回grant option而不是真正收回select权限。
级联收回很多情况下是不合适的。为了处理这种情况，SQL允许权限由一个角色授予，而不是由用户来授予，SQL有一个与绘画所关联的当前角色可以通过执行set role role_name来设置。指定的角色必须已经授予用户，否则会失败。
如果要在授权时将授权人设置为一个会话所关联的当前角色，并且当前角色不为空的，可以在授权语句后面加上
子句。
7、总结
*SQL支持包括内连接、外连接在内的几种连接类型。
*视图关系可以定义包含查询结果的关系，视图可以隐藏不需要的信息，可以把信息从多个关系收集到一个单一的视图中。
*事务是一个查询和更新的序列，它们共同执行某项任务，事务可以被提交或回滚。
*完整性约束保证授权用户对数据库所做的改变不会导致数据一致性被破坏。
*参照完整性约束保证出现在一个关系的给定属性集的值同样出现在另一关系的特定属性集上。
*域约束指定了在一个属性上可能取值的集合，这种约束也可以禁止在特定属性上使用空值。
*断言是描述性表达式，他指定我们要求总是为真的谓词。
*SQL数据定义语言提供了定义诸如date和time那样的固有域类型以及用户定义域类型的支持。
*通过SQL授权机制，可以按照在数据库中不同数据值上数据库用户所允许的访问类型对它们进行区分。
*获得了某种形式授权的用户可能允许将此授权传递给其他用户，但是传递权限时应该很小心，因为权限可能在将来某个时间被收回。
*角色有助于根据用户在组织机构中所扮演的角色，把一组权限分配给用户。
浙公网安备 33010602011771号