【数据库】|第四章_中级SQL

4.1 连接表达式

4.1.1 连接条件on条件

• 允许在连接的关系上设置通用的谓词
• 出现在连接表达式的末尾

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

• 表明：来自student的元组和一个来自takes的元组在ID上的取值相同，那么它们是匹配的
• 与student natrual join takes几乎一样
• 查询结果中，ID出现两次
• 与select * from student,takes where student.ID=takes.ID等价

4.1.2外连接

• 内连接：不保留未匹配元组的连接运算
• 外连接：在结果中创建包含空值元组的方式来保留在连接中丢失的元组
  • 左外连接（left outer join）：只保留出现在左外连接运算之前（左边）的关系中的元组
  • 右外连接（right outer join）：只保留出现在右外连接运算之后（右边）的关系中的元组
  • 全外连接（full outer join）：保留出现在两个关系中的元组
  • 左、右外连接是对称的
• 左外连接运算操作：
  • 计算内连接结果
  • 对于在内连接左侧关系中任意一个与右侧关系中任何元组都不匹配的元组t，向连接结果中加入元组r，r的构造：
    • 元组r从左侧关系得到的属性被赋为t中的值
    • r的其他属性被赋为空值
• on和where在外连接中的表现不同：
  • 外连接只为那些对相应内连接结果没有贡献的元组补上空值并加入结果
  • on条件是外连接声明的一部分，where不是

显示Comp.Sci系所有学生以及他们在2009年春季选秀的所有课程段的列表。2009年春季开设的所有课程段都必须显示，即使没有Comp.Sci系的学生选秀这些课程段

select*
from(select *
	from student
	where dept_name='Comp.Sci')
natural full outer join
(select*
from takes
where semester='Spring' and year='2009')

4.1.3 连接类型和条件

• inner join表示内连接
• 当join子句没有outer前缀，默认的连接类型是inner join
• 任意的连接形式（内连接、左外连接、右外连接、全外连接）可以和任意的连接条件（自然连接、using条件连接、on条件连接）进行组合
  

4.2 视图

1. 视图（view）：不是逻辑模型的一部分，但作为虚关系对用户可见的关系称为视图
2. 虚关系：不预先计算并存储，而是在使用虚关系的时候才通过执行查询被计算出来

4.2.1视图定义

格式：create view 视图名 as <query expression>

创建一个视图，列出Physics系在2009年秋季学期所开设的所有课程段，以及每个课程段在哪栋建筑哪个房间授课的信息

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查询中使用视图

• 可以用视图名指代该视图生成的所有虚关系
• 视图的属性名可以显式指定：

每个系中所有教师的工资总和
sum(salary)的属性名是在视图定义中显式指定的

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

• 视图的实现：
  • 当定义视图时，数据库系统存储视图的定义本身，不存储定义该视图的查询表达式的执行结果。
  • 当视图关系出现在查询中，就被已存储的查询表达式代替
  • 无论何时执行这个查询，视图都会被重新计算

4.2.3 物化视图

• 物化视图：如果用于定义视图的实际关系改变，视图也跟着修改。
• （物化）视图维护：保持物化视图一直在最新状态的过程

4.2.4 视图更新

• 视图是可更新的：（视图上可以执行插入、更新、删除）
  • from子句中只有一个数据库关系
  • select子句中只包含关系的属性名，不包含任何表达式、聚集或distinct的声明
  • 任何没有出现在select子句中的属性可以取空值；即这些属性没有not null约束，也不构成主码的一部分
  • 查询中不含有group by或having子句
• 可更新的视图支持update、insert、delete操作
• 在视图定义的末尾包含with check option子句：若对试图进行的操作不满足where子句的条件，操作会被拒绝

4.3事务

• 事务（transaction）由查询、更新语句 序列组成
  • Commit work：提交当前事务，即将该事务所做的更新在数据库中永久保存。事务提交后，一个新事务自动开始。
  • Rollback work：回滚当前事务，即撤销事务中所有SQL语句对数据库的更新。数据库恢复到执行该事务第一条语句之前的状态
• 将多条SQL语句包含在begin atomic ...end之间。所有在关键字之间的语句构成一个单一事务。

4.4 完整性约束

用于保证授权用户对数据库所做的修改不会破坏数据的一致性

4.4.1 单个关系上的约束

（详看下面几节）

4.4.2 not null约束

• 声明禁止在该属性上插入空值
• 主码不能为空值，因此若属性声明为主码不用再显式声明not null

name varchar(20) not null;

4.4.3 unique约束

• 结构：$unique(A_1,A_2,...,A_m)$
• 表示属性$A_1,A_2,...,A_m$形成了一个超码：即在关系中没有两个元组能在所有列出的属性上取值相同，唯一属性可为null (空值不等于任何其他值）

4.4.4check子句

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

模拟一个枚举类型

create table section
       (course_id varchar(8),sec_id varchar(8),semester varchar(6),…,
        primary key (course_id, sec_id, semester, year),
        check(semester in ('Fall','Winter','Spring','summer')))

4.4.5 参照完整性

• 定义：保证一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现
• 参照完整性约束/子集依赖：令关系r1、r2的属性集分别为R1、R2，主码分别为K1、K2.对r2中的任意元组t2，均存在r1中元组t1使得$t1\cdot K1=t2\cdot a$，称R2的子集a为参照关系r1中K1的外码

主码约束

• 属性名 数据类型（域值） primary key
• 主码值不能为空，也不允许重复
• 基本（被参照）关系

外码约束

• foreign key（外码） references 关系名（主码）
• 取值：
  • 为空
  • references后关系中某个对应的主码取值
• 依赖（参照）关系

删除或修改基本关系上的元组

• on delete cascade:将依赖关系中与要修改的基本关系中主码相对应的一起修改/删除
• on delete set null：将依赖关系中相对应的置空
• on delete set default ：将对应的设为默认值

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

• 事务的中某一步骤会暂时违反完整性约束，但是后面的某一步会消除这个违反
• 在约束声明中加入initially deferred子句，在事务结束的时候检查
• 对于可延迟的约束,执行 set constraints constrain-list deferred 命令作为事务一部分,延迟到事务结束时检查。
• 多数不支持

4.4.7复杂check条件与断言

断言assertion

• 是一个谓词，表达了我们希望数据库总能满足的一个条件
• 形式：
  • 建立断言：create assertion 断言名 check 条件
  • 撤销断言：drop assertion 断言名

4.5 SQL的数据类型与模式

4.5.1日期和事件类型

• date:日历日期，包括年(四位)、月、日
• time:一天中的时间，包括时、分、秒
  • 可用time(p)表示秒的小数点后位数(默认0)
  • 指定time with timezone,可以储存时区信息
• timestamp: date与time的组合可用
  • timestamp(p)表示秒的小数点后位数(默认6)
  • 指定with timezone,可以储存时区信息
• 必须按照年月日的顺序

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

• 字符串类型转换
  • cast e to t(t可为date,time,timestamp)
• 从日期、时间中提取单独的域
  • extract (field from d)
    • field 可以是year，month，day，hour，minute，second
    • 时区中的信息用timezone_hour，timezone_minute等来提取
• 获取当前日期、时间的函数
  • 返回当前日期：current_date
  • 返回当前时间（带时区）：current_time
  • 返回当前本地时间（不带时区）：localtime
  • 时间戳:current_timestamp,locatetimestamp
• 支持interval数据类型，用来计算日期、时间、时间间隔

4.5.2 默认值

• 取值为default规定的默认值

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创建索引

• 通过索引可以更加快速高效地查询关系中具有该属性指定值的元组数据
• create index 索引名 on 关系名（属性名）
  (详细将在第十章）

4.5.4 大对象类型

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

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

4.5.5用户定义的类型

• 独特类型：
  • 形式：create type 新类型对应的名称 as 新类型的具体内容 final
  • 删除：drop type
  • 修改：alter type
• 类型和域的两个差别：
  • 在用户自定义类型上不能声明约束或缺省值，在域上可以。用户自定义类型不仅被设计用来指定属性类型，而且还被用在不能施加约束的地方以对SQL进行过程扩展
  • 域并不是强类型的。其结果是，一个域类型的值可以被赋值给另一个域类型（cast），只要它们的基本类型是相容的

4.5.6 create table的扩展

• 创建与现有某表模式相同的表：create table 新表 like 现有某表
• 创建包含查询的结果的表：

例：

create table t1 as (select * 
                    from instructor 
                    where dept_name = 'Music') 
with data
/*很多数据库实现在省略with data子句时，默认加载数据*/

4.5.7模式、目录与环境

• 当代数据库系统提供三层结构的关系命名机制：目录catalog 、模式schema、关系/视图对象
• 用户连接到数据库（验证身份）后，有一个默认的目录和模式，默认目录和默认模式可以省略
• 每个数据库连接会建立SQL环境：包括目录、模式和用户授权标识
  • 大多数数据库系统，环境随用户账户创建而自动创建，此时模式名被置为用户账户名
  • 用create schema和drop schema语句创建、删除模式

4.6授权

4.6.1权限的授予与追回

• 格式：grant <权限列表> on <关系名/视图名> to <用户/角色名>
• update,insert,select既可以在关系上的所有属性上授予，又可以只在某些属性上授予
• public指系统的所有当前用户和将来的用户
• 不允许对一个关系的指定元组授权
• 收回权限：

revoke 表级权限 on <关系名/视图名>  
from <用户/角色列表/public,用户/角色列表,用户/角色列表,...> 

角色

• 授权前首先要确定一个角色集，可以给角色授予权限（角色是用户的虚拟体现）
• 任何可以授予用户的权限都可以授予角色
• 角色的权限包括
  • 直接授予该用户/角色的所有权限
  • 授予该用户/角色所拥有的角色的所有权限

视图的授权

视图的访问会转换成实际关系的访问，所以创建视图的用户在视图上的权限，不会超越在实际关系上的已有权限

模式的授权

• 模式的基本授权：模式的拥有者才拥有对模式的修改权限
• references权限，允许用户在创建关系时声明外码，此权限可以授予到指定属性上（因为外码会限制其他用户对被参照关系将来的行为，所以需要授权）

权限的转移

• 用户具有权限的充分必要条件

  • 当且仅当存在从根结点到该用户结点的路径（结点是用户，根结点是DBA，有向边Ui→Uj，表示用户Ui把某权限授给用户Uj）

• 允许接受者把得到的权限在传递给其他用户：在grant命令后面加入with grant option

权限的收回

• 格式

注意：收回权限时，若该用户已将权限授予其它用户，则也一并收回。授权路径的起点一定是DBA

revoke 表级权限 on <关系名/视图名>  
from <用户/角色列表/public,用户/角色列表,用户/角色列表,...> 

• 级联收回（默认）：收回权限时，若该用户已将权限授予其它用户，则也一并收回
• restrict防止级联收回：
  • 如果存在任何级联收回，返回错误，不执行任何收权动作
  • 用关键字cascade替换restrict，表示需要级联收回

revoke select on department from Amit,Satoshi restrict

• 仅仅收回grant option，保留其他权限
  • 即只回收授权选项，不真正回收权限
• 通过角色授权，避免用户授权的级联收回
  • (1)设置会话的当前角色为已定义角色：set role role_name
  • (2)由当前角色授权（角色不为空），授权语句后加：grant by current_role