数据库系统原理与实践笔记 #3

数据库系统原理与实践 笔记 #3

中级SQL

连接表达式

• 连接(Join)操作以两个关系为输入，将另一个关系作为结果返回
• 一个链接操作是两个关系的某些元组在符合某些条件下相匹配的笛卡尔积，同时还指定了连接结果中出现的属性有哪些。
• 连接操作通常在from子句中使用

例子

• 关系 course

course_id	title	dept_name	credits
BIO-301	Genetics	Biology	4
CS-190	Game Design	Comp.Sci.	4
CS-315	Robotics	Comp.Sci.	3

• 关系 prereq

course_id	prereq_id
BIO-301	BIO-101
CS-190	CS-101
CS-347	CS-101

select *
from course natural join prereq;
-- 等价
select *
from course join prereq using(course_id);

join…on…

• Join操作的链接表达式还可以用on条件来表示，例如：

select *
from course join prereq on course.course_id = prereq.course_id;
-- 等价(查询结果保留重复属性)
select *
from course,prereq
where course.course_id = prereq.course_id;

  使用on条件的两个优点：

• 在on子句中指定连接条件，在where子句中明确其他限定条件，使得SQL语句更加简洁
• on条件子句在外连接的表现和where子句不同（例如：department left join instructor）

外连接

• 外连接(Outer Join) 是一种扩展的链接操作，可以避免连接操作结果信息的丢失
• 外连接过程：先执行连接操作，然后将两个关系中不匹配的元组全都加入到最后的结果关系中，并使用null作为属性值补全，从而保留了在连接中丢失的元组
• 内连接(Inner Join)：我们此前学的不保留未匹配元组的连接运算，连接操作默认为内连接

左外连接

• 例如：

select *
from course natural left outer join prereq;

  首先通过自然连接寻找相同字段连接，之后保留左边的未匹配元组并补全

全外连接

• 例如：

select *
from course natural full outer join prereq;

  全外连接就是全部保留

连接关系

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

视图

• 在某些情况下，让所有用户看到数据库的整个逻辑模型（存储于数据库的某个关系模式）是不合适的
• 例如：学校某职员需要知道教师的姓名及所述系别，但不让该职员知道教师的薪水（也无权知道）。符合该用户需求的关系用如下的SQL语句表达为：

select ID,name,dept_name
from instructor;

• 视图提供了向用户隐藏特定数据的机制
• 任何像这种不是逻辑模型的一部分，但作为“虚关系”对用户可见的关系称为视图

视图定义

• SQL中用create view命令定义视图

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

• 其中<查询表达式>可以是任何有效的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;

  熟悉吗？依我之见，SQL中的视图和我先前博客中C++ ranges中的视图应该是差不多一样的。

用其他视图定义视图

• 如果视图v2用于v1的定义中，我们称v1直接依赖v2
• 如果v1直接依赖v2或者从v1到v2有一条依赖路径，我们称v1依赖v2
• 如果一个视图v依赖其本身，我们称该视图v是递归的

视图展开

• 试图展开是一种定义视图含义的方法，其中视图是用其他视图来定义的
• 假设视图v1由表达式e1 定义，且e1中可能包含其他视图关系，表达式的视图展开按如下方式重复替换步骤:

repeat
找出e1中的任意视图关系vi，用定义视图vi的表达式来替换视图关系vi
until e1中不再存在任何视图关系

• 只要视图定义不是递归的，上面额循环就能够终止

物化视图

• 物化视图：特定数据库系统允许视图关系被存储，并保证用于定义视图的实际关系改变，视图也跟着修改，这样的视图被称为物化视图
• 物化一个视图：创建一个物理表，表中包含视图定义的查询结果中的所有元祖
• 如果用于定义视图的世纪关系改变，物化视图的结果也会过时

视图更新

• 在之前定义的视图faculty中新增一个元组

insert into faculty values('30765', 'Green', 'Music');

• 一般情况下，不允许对视图关系进行更新
• 如果定义视图的查询语句对下列条件都能满足，我们称SQL视图是可（插入）更新的：
  (1).from子句只有一个关系；
  (2).select子句只包含关系的属性名，不包含任何表达式、聚类函数或distinct声明；
  (3).任何没有出现在select子句中的属性内容可以取空值；
  (4).查询中不包含group by或者having子句；

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

  向history_instructors插入(‘255566’, ‘Brown’, ‘Biology’, 100000)会发生什么？

• 默认情况下，SQL允许这样的更新
• 可在视图定义加入with check option子句，用于拒绝不满足视图的where子句条件的元组的更新、插入

create view histroy_instructors as
    select *
    from instructor
    where dept_name='History'
    with check option;

事务

• 事务由查询和（或）更新语句的序列组成
• 事务的开始是隐式的，以commit或rollback结束一个事务，commit提交当前事务，持久保存事务所做的更新；rollback回滚当前事务，撤销事务的更新
• 事务具有ACID特性：原子性、一致性、隔离性和持久性
• 在大多数数据库中：每个SQL语句默认为一个事务，自动提交

完整性约束

• 完整性约束防止的是对数据的意外破坏，它保证手段用户对数据库所做的修改不会破坏数据的一致性
• 完整性约束的实例：教师工号不能为空、instructor关系中的每个系名必须在department关系中有一个对应的系名、一个支票账户的存款必须大于$10,000.00、一个银行雇员的每小时公子必须大于$4.00、客户电话号码不能为null
• 在已有表中增加约束：

alter table table-name add <constraint>

单个关系上的约束

• create table命令可以包括完整性约束，包括：not null、primary key、unique、foreign key、check(<谓词>)
• 实体完整性约束：保证关系中的每个元组都是可识别的和唯一的
• 参照完整性约束：一般是指多个实体或关系之间的关联关系，用于描述实体之间的联系
• 用户自定义完整性约束（域完整性或语义完整性约束）：关系中属性的取值范围，避免双属性的值与应用语义的矛盾。

Not Null和Unique约束

• not null
• unique($A_1,A_2,...,A_m$)：unique声明指出属性$A_1,A_2,...,A_m$形成了一个超码，候选码允许为null

check 子句

• check§，使得关系中每个元组都必须满足谓词P，可以是包括子查询在内的任意谓词，但实现的开销比较大

参照完整性

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

• 假设关系r1和r2的属性集分别为R1和R2，K1和K2分别为R1和R2的子集：
  如果要求对r2中任意元组t2，均存在r1中元组t1使得t1.K1 = t2.K2，那么称关系r2中的K2属性集参照关系r1中K1属性集

• 上述要求称之为参照完整性约束或子集依赖：若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等则是设为空或者设为默认值等。

• 推迟完整性约束检查到事务结束时进行：
  (1).在约束声明后加initially deferred;
  (2).或者对约束条件加入语句set constraints <constraint-list> deferred;

复杂check条件与断言

• 在check§子句中的谓词P可以为一个子查询

check (time_slot_id in (select time_slot_id from time_slot))

• 一般修改section关系和time_slot关系任意元组都需要检测check子句是否满足，开销比较大
• 多数DBMS中不支持在chekc子句中的子查询、或者不支持在DDL中使用chck子句的嵌套子查询；谓词，时机情况下一般可以使用触发器功能来保证完整性约束。

断言

• 断言就是一个谓词，它表达了我们希望数据库总能满足的一个条件；属性域约束和参数完整性约束是断言的特殊形式

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的数据类型与模式

类型转换

• 使用cast(e as t)表达式：表示将表达式e转换为类型t，例如：

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

• 使用coalesce()函数解决输出空值的情况：该函数接收任意数量的参数（必须是相同类型），并返回第一个非空参数

默认值

• 用default+默认值表示

创建索引

• 在关系的属性上所创建的索引是一种数据结构（如B+树），它允许数据库系统高效地找到关系中那些在索引属性上取给定值的元组

create index studentID_index on student(ID);

大对象类型

• 大对象（图片、视频等）被存储为large object（最大4GB）：blob（二进制大对象）、clob（字符大对象）
• 当一个SQL查询返回一个大对象时，往往是返回一个“定位器”而不是大对象本身，然后利用“定位器”逐步取出该对象(HANDLE)
• 优劣：不需要为大数据类型指定长度，使用方便；但与主表分表存储，影像数据库性能

用户自定义类型

• create type构造于SQL中，创建用户自定义类型：

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

drop type Dollars; -- 删除自定义类型
alter type Dollars ... -- 修改自定义类型