数据库理论 03 intermediateSQL——基于《数据库系统概念》第七版

本文链接：https://blog.csdn.net/JamSlade/article/details/127203986

连接表达式

Join以两个关系输入，结果返回
得到笛卡尔积
通常在from子句中使用

select * from 
course natural join prereq

select * from
course join prereq using(course_id);

两个句子等价，因为结果没有重复属性

join… on

select * from
course c join prereq p on c.course_id = p.course_id

select *
from course c, prereq p
where  c.course_id = p.course_id

也是等价的

on的优点

SQL语句简洁易懂
on条件子句在外连接与where子句有区别

外连接

外连接（Outer Join）是一种扩展的连接操作，可以
避免连接操作结果信息的丢失

先执行连接操作，然后将两个关系中不匹配的元组都加入到最后的结果关系中，并使用null作为属性值补全

连接操作默认为内连接 Inner Join

左外连接

select * from course natural left outer join prereq;

在这里插入图片描述

右外连接

select * from course natural right outer join prereq;

在这里插入图片描述
全外连接

select * from course natural full outer join prereq;

在这里插入图片描述

概念

连接操作：以两个关系为输入，将另一个关系作为结果返
连接类型（Join types）－决定了如何处理连接条件(属性)不匹配的元组
连接条件（Join Conditions）－决定了两个关系中哪些属性相匹配，以及连接结果中是否出现重复属性

需要注意：
natural 会把名字相同的列“融合”在一起，而join … on不会

视图

在某些情况下，让所有用户看到数据库的整个逻辑模型（存储
于数据库的所有关系模式）是不合适的

比如不应该让学生知道老师的工资

select ID, name, dept_name
from instructor；

视图：向用户隐藏特定数据

任何像这种不是逻辑模型的一部分，但作为“虚关系”对用户可见的关系称为视图

定义

create view

create view v [(<列名1>, <列名2>,…)]
as <查询表达式>
[with check option];

有别于with子句

<查询表达式> 为SQL表达式
v表示视图名字

视图的定义 有别于 通过查询表达式创建一个新关系

视图导致表达式被吃醋呢，使用这个视图查询过程的表达式会被带入使用

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

# 使用视图
select name from faculty # 视图
where dept_name = 'biology';

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

还可以利用视图创造视图

视图v2用于v1定义中，称v1直接依赖v2
v1直接依赖v2或v1到v2由依赖路径，称v1依赖v2
若v依赖自己，称v是递归的

视图展开

View expansion 视图用其他视图定义

若v1由表达式e1定义且e1可能包含其他视图关系，视图展开可以用如下命令

repeat 
	找出e1任意关系视图vi
	定义视图vi的表达式替换视图vi
until e1不存在视图关系

使用这个命令的前提是视图不是递归的

物化视图

创建一个物理表(关系)，表中包含视图定义的查询结果中的所有元组

特定数据库系统允许视图关系被存储，并保证用于定义视图的实际关系改变，视图也跟着修改，这样的视图被称为物化视图

定义视图的实际关系改变，物化视图会outdated，所以需要实时更新

保持物化视图一直在最新状态的过程称为物化视图维护，也称视图维护
及时的视图维护 / 延迟的视图维护 / 周期性的视图维护
增量的视图维护

更新视图

新增

nsert into faculty 
values (’30765’, ’Green’, ’Music’);

一般不允许对视图关系更新

更新需要满足以下条件

from 子句只有一个关系
select 只包含属性名，不包含表达式、聚集函数、distinct
未出现在select子句的属性可以取null
不包含group by和having子句

可在视图定义加入with check option子句，用于拒绝不满足视图的where子句条件的元组更新、插入

 create view history_instructors as
select *
from instructor
where dept_name= ’History’
with check option;
# 检查视图更新SQL语句是否满足视图定义的where语句

这样更新语句

update history_instructors set salary = 80000
where ID = '25566'
# 转换为
update instructors set salary = 80000
where ID = '25566' and dept_name = 'history'

create view history_instructors as
select *
from instructor
where dept_name= ’History’
with check option;


insert into history_instructors (ID, name, salary) 
values (’69987’, ’White’, 80000);
# 转换为
insert into instructors (ID, name, salary, dept_name) 
values (’69987’, ’White’, ‘80000’, ‘History’);

事务

Transaction 是查询 “和” “或”更新语句的序列组成
commit或者rollback结束一个事务

commit 提交当前事务，持久保持事务更新
rollback 回滚当前事务，撤销更新

事务的ACID性

原子性
一致性
隔离性
持久性

每个SQL语句默认一个事务
多个sql也可构成一个事务

begin atomic ... end

语句，但只有少数SQL数据库支持

完整性约束

防止的是对数据的意外破坏，它保证授权用户对数据库所做的修改不会破坏数据的一致性

比如：
支票账户存款大于1w
客户电话号码不能为空

已有表加约束

alter table table_name add <constraint>

create table的完整性约束

not null
primary key
unique
foreign key
check (<谓词>)

实体完整性约束：每个元素可识别且唯一
参照完整性约束：多个实体关系之间的关联关系
用户自定义完整性约束（域完整性或语义完整性）：关系中属性取值范围，避免属性值与应用语义矛盾

not null
声明非空

unique(A1,A2,……，An)

指出这个元组行成超码
候选码允许为null （有别于primary key
重复元组存在性测试unique结构

check 子句

check(P)关系中每个元组必须满足谓词P
P 可以是包括子查询在内的任意谓词，但实现开销较大
如确保semester时四季之一

create table a
(
semester verchar(6),
primary key(semester),
check(semester in ('FALL', 'WINTER', 'SPRING', 'SUMMER'))
)

参照完整性

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

比如instructor里面有‘biology’，department一定会存在有关‘biology’的元素

详细定义：
关系 $r_1, r_2$ 属性集为 $R_1,R_2$ ,有 $K_1 \sube R_1, K_2 \sube R_2$
$\forall t_2 \in r_2,\ \ \exists t_1 \in r_1，有t_1.K_1 = t_2.K_2$
我们称r2中K2属性集参照r1中的K1属性集

上述要求称为参照完整性约束or子集依赖

K1如果是r1的主码，K2时参照关系r1中K1的外码

级联操作

 create table course (
…
dept_name varchar(20),
foreign key (dept_name) references department
on delete cascade
on update cascade,
… )；

意思为：department删除元组，即删除course中参照被删除系的元组

若把cascade换set null,set default
表示department删除元组，九八course参照被删除系设为null或默认值

如何不违反完整性约束插入元组？

配偶信息设置为null
插入配偶元组后再更新
（注意配偶信息不能设置为not null）

法二：
推迟完整性约束检查事务结束时进行

约束声明后加入initially deferred
或者对约束条件加入set constrants <>

复杂check条件与断言

check(P)的我i此P可以作为子查询
检测在关系section中每个元组的time_slot_id的确是在time_slot关系中某个时间段的标识

(
	time_slot_id in
	(
		select time_slot_id
		from time_slot
	)
)

不用foreign key的原因是time_slot_id不是time_slot关系的主码

修改section关系和time_slot关系任意元组都须检测check子句是否满足，因此，开销较大。
大多数DBMS不支持check子句嵌套子查询，一般可以使用触发器保证完整性约束

断言
一个为此，属性与约束和参照完整性约束时断言的特殊形式

create assertion <assertion_name> check <predicate>;

student在tot_cred的取值必须等于其选修完毕的课程学分总和

create assertion credits_earned_constraint check
(
	not exists (
		select ID from student
		where tot_cred 
		<>
		(
			select sum(credits)
			from takes natural join course
			where student.ID = takes.ID
			and grade is not null 
			and grade <> ‘F’
		)
	)
);

SQL的数据类型与模式

date 日历日期，年月日如 date’1999-01-01’
time 如 time ‘09:11:11’ time(2)‘09:09:09.30’
timestamp date和time的结合
interval 一段时间

支持算术运算
date,time, timestamp相减同类值获得interval值

type translation

cast(e as t)把表达式e转换为类型t

select cast(ID as numeric(5)) as inst_id 
from instructor
order by inst_id;

不输出空值
可以使用coalesce()避免输出空值，他接受任意数量参数（参数需要同类型）返回第一个非空参数

select ID， coalesce(salary, 0) as salary
from instructor;

默认值
create table的时候在变量后面加上
default 0 表示默认值为0

创建索引
索引是一种数据结构（如B+树）

它允许数据库系统高效地找到关系中那些在索引属性上取给定值的元组

example

create index studentID_index on student(ID);

后续使用where ID = '111’就不需要读取关系所有元组，直接找ID‘111’的记录

大对象类型
照片视频储存为large object

blob 二进制大对象
clob 字符大对象

返回大对象的时候通常返回一个定位器(可理解为HANDLE)

➢ 优势与劣势：
✓无需为大对象数据类型指定长度，使用方便；
✓一般需与主表分表存储；影响数据库性能；谨慎使用

自定义类型
create type

create type dollars as numeric(12,2) final;
# final 无 实际意义

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

类似typedef
强制类型转换cast (department.budget as numeric(12,2) );

域和属性域
create domain

create domain person_name char(2) not null;

域可以有约束 or 默认值
如not null
domain上可以用check约束

create domain degree_level varchar(10)
constraint degree_level_test
check
(
	value in ('bachelors', 'masters', 'doctorate')
);

domain不是强类型，基本类型相容的域类型值可以被赋予另一个域类型

拓展create table
拓展某个表模式相同表

create table temp_instructor like instructor;

模式、目录、环境

目录：用户或应用，一个管理员可以有多个数据库模式一个数据库有多个关系模式、视图

SQL标准未提供目录操作，但有对模式操作

create schema
drop schema

L环境包括目录、模式和用户标识（授权标识符），用户提交的SQL语句在该环境中运行

授权

数据库用户在数据上权限形式

select 读取但不能修改
insert 可插入但不可修改
update 可更新不能删除
delete 可以删除

修改数据库模式权限

index - 创建删除索引
resources 创建新关系
alteration 添加or删除关系属性
drop 删除关系

授权规范

grant

grant <权限列表>
on <关系名 or 视图名>
to <用户/角色列表>
[with grant option];

用户角色范围

ID
public，当前和将来所有有效用户
角色

对视图授权不带表对视图相关的实际关系授权
权限授予人本身要有制定项目权限

权限列表

select: 允许读取关系，或者使用视图完成查询的权限
insert: 插入元组的权限，可指定属性列
update: 使用SQL update语句更新的权限，可指定属性列
delete: 删除元组的权限
all: 允许所有权限

grant insert (ID) on instructor to U4 
with grant option；
# 允许用户U4可以将在instructor关系ID属性上的insert权限授予其他用户

用户具有权限的充分必要条件是：当且仅当存在从授权图的根（即代表数据库管理员的顶点）到代表该用户顶点的路径

回收权限

revoke <权限列表>
on <关系名或视图名> from <用户/角色列表>
[ restrict | cascade ];

<权限列表> 可能是all，收回被收回用户所持有的所有权限
<用户列表> 是public, （除了那些隐含授权的用户）其他用户的权限将都被收回
默认级联收回权限（cascade），restrict可用于避免一些不合适的权限级联收回

示例:

revoke update （budget） on department from U1, U2, U3；

revoke grant option for select on department from U1, U2, U3；

如果某些权限被不同的授权者授予同一个用户两次，那么在一次权限回收后该用户可能仍保有这个权限
一个权限被回收后，基于这一权限的其他权限（如视图）也将被回收

角色授权

• 权限可以被授予给角色:
– grant select on takes to instructor;
• 角色可以被授予给用户，同时也可以被授予给其他角色

– grant instructor to Amit ;
– create role teaching_assistant ;
grant teaching_assistant to instructor;

•
Instructor 具有teaching_assistant 的所有权限
• 角色链

– create role dean;
grant instructor to dean;
grant dean to Satoshi;

• SQL允许权限由一个角色授予（p86）
– [granted by current_role]

view授权

➢ 大学地理系工作人员的视图授权示例
➢

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

grant select on geo_instructor to geo_staff ;
➢ 一个geo_staff 成员的查询操作可以写为：

select *
from geo_instructor ;

模式授权

SQL标准为数据库模式指定了一种基本的授权机制：只有模式的拥有者才能够执行对模式的任何修改

➢ SQL提供了references权限，允许用户在创建关系时声明外码

grant references (dept_name) on department 
to Mariano;

小结

➢ 什么是内连接、外连接、全连接？
➢ 什么是视图？如何定义视图关系？
➢ 什么是事务？事务的特性有哪些？
➢ 什么是完整性约束？参照完整性约束？外码约束？
➢ SQL提供时间日期类型和用户自定义类型
➢ 如何对数据库、视图进行授权？什么是权限的转移？