数据库系统概论（个人笔记）

4、中间的SQL

---

4.1 连接表达式

Join Expressions

---

Joined Relations

连接操作 Join operations 接受两个关系并返回另一个关系作为结果。

连接操作是笛卡尔积，它要求两个关系中的元组匹配（在某些条件下）。它还指定了出现在连接结果中的属性

连接操作通常用作 from 子句中的子查询表达式

连接条件 Join condition ——定义两个关系中的哪些元组匹配，以及在连接结果中出现哪些属性

连接类型 Join type ——定义如何处理每个关系中与其他关系中任何元组不匹配的元组（基于连接条件）

---

Natural Join in SQL

自然连接 Natural Join 为所有公共属性匹配具有相同值的元组，并且只保留每个公共列的一个副本。

列出每个学生所修的课程：

• select name, course_id
  from  students, takes
  where student.ID = takes.ID;
  

在SQL中使用“自然连接”构造进行相同的查询:

• select name, course_id
  from student natural join takes;
  

from 子句可以有多个使用自然连接组合的关系:

select  A1, A2, … An
from  r1  natural join r2 natural join .. natural join rn
where  P ;

---

Student Relation

---

Takes Relation

---

student natural join takes

---

Dangerous in Natural Join

谨防不相关的相同名称的属性被错误地等同

Example——列出学生的名字以及他们所修课程的名称

• 不正确的版本

  select name, title
  from student natural join takes natural join course;
  

  student( ID, name dept_name, tot_cred)
  course(course_id, title, dept_name, credits))
  takes(ID, course_id, sec_id, semester, year, grade)

• 正确的版本

  select name, title
  from student natural join takes, course
  where takes.course_id = course.course_id;
  

  • 该查询省略了学生在该学生所在系以外的系中学习课程的所有（学生姓名、课程名称）对。
  • 正确的版本（如上），正确输出这样的匹配。

---

Natural Join with Using Clause

为了避免错误地等同属性的危险，我们可以使用“using”构造，它允许我们精确地指定应该等同哪些列。

查询示例

select name, title
from  (student natural join takes)  join course using (course_id)

---

Join Condition

on 条件允许在连接的关系上使用一般谓词

该谓词的编写与 where 子句谓词类似，只是使用了关键字 on

查询示例

select *
from  student join takes on student_ID  = takes_ID

• 上面的 on 条件指定了一个来自 student 的元组与来自 take 的元组匹配，如果它们的 ID 值相等。

等价于:

select *
from  student , takes
where  student_ID  = takes_ID

---

Outer Join

连接操作的扩展，可避免信息丢失。

计算连接，然后将一个关系中不匹配另一个关系中的元组的元组添加到连接的结果。

使用空值 null。

外连接 Outer Join 的三种形式:

• 左外连接 left outer join
• 右外连接 right outer join
• 全外连接 full outer join

---

Outer Join Examples

---

Left Outer Join

---

Right Outer Join

---

Full Outer Join

---

Joined Types and Conditions

连接操作 Join operations 接受两个关系并返回另一个关系作为结果。

这些额外的操作通常用作 from 子句中的子查询表达式。

连接条件 Join condition ——定义两个关系中哪个元组匹配。

连接类型 Join type ——定义如何处理每个关系中与其他关系中任何元组不匹配的元组（基于连接条件）。

---

Joined Relations – Examples

---

4.2 视图

Views

在某些情况下，不希望所有用户都看到整个逻辑模型（即存储在数据库中的所有实际关系）。

考虑一个人需要知道导师的名字和部门，但不知道薪水。这个人应该看到 SQL 中描述的关系：

 select ID, name, dept_name
 from instructor

视图 view 提供了一种机制，可以在某些用户的视图中隐藏某些数据。

任何不属于概念模型但作为“虚拟关系”对用户可见的关系都称为视图 view。

---

View Definition

视图是使用 create view 语句定义的，该语句具有如下形式：

create view v as <query expression>

其中 是任何合法的SQL表达式。视图名用 v 表示。

一旦定义了视图，就可以使用视图名称来引用视图生成的虚拟关系。

视图定义与通过计算查询表达式来创建新关系不同

• 相反，视图定义会导致表达式的保存；该表达式被替换到使用视图的查询中。

---

View Definition and Use

教师没有他们工资的一个视图：

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;

---

Views Defined Using Other Views

一个视图可以在定义另一个视图的表达式中使用

如果在定义 v1 的表达式中使用了 v2 ，则认为视图关系 v1 直接依赖于 depend directly on 视图关系 v2

如果视图关系 v1 直接依赖于 v2 ，或者存在从 v1 到 v2 的依赖路径，则称视图关系 v1 依赖于 depend on 视图关系 v2

如果视图关系 v 依赖于自身，则称其为递归 recursive。

create view physics_fall_2017 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 = '2017’;

create view physics_fall_2017_watson as
	select course_id, room_number
	from physics_fall_2017
	where building= 'Watson';

---

View Expansion

展开视图（将上述两个结合起来）:

create view physics_fall_2017_watson as
    select course_id, room_number
    from (select course.course_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 = '2017')
    where building= 'Watson';

定义根据其他视图定义的视图的含义的一种方法。

让视图 v1 由表达式 e1 定义，表达式 e1 本身可以包含视图关系的用法。

表达式的视图展开重复以下替换步骤:

repeat

​ 查找e1中任何视图关系vi

​ 用定义vi的表达式替换视图关系vi，

until e1中不再存在视图关系

只要视图定义不是递归的，这个循环就会终止

---

Materialized Views

某些数据库系统允许对视图关系进行物理存储。

• 定义视图时创建的物理副本。
• 这样的视图被称为物化视图 Materialized Views。

如果更新了查询中使用的关系，则物化视图结果将过期

• 需要通过在底层关系更新时更新视图来维护 maintain视图。

---

Update of a View

向前面定义的教员 faculty 视图添加一个新的元组

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

这个插入必须通过插入到教员关系中来表示

• 必须有工资的价值

两种方法

• 拒绝插入
• 将元组（‘30765’, ‘Green’, ‘Music’, null）插入到讲师关系中

---

Some Updates Cannot be Translated Uniquely

create view instructor_info as
	select ID, name, building
	from instructor, department
	where instructor.dept_name= department.dept_name;

insert into instructor_info 
             values ('69987', 'White', 'Taylor');

问题：

• 如果 Taylor 有多个部门，那是哪个部门?
• 如果 Taylor 没有部门呢?

---

And Some Not at All

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

如果我们将 (‘25566’, ‘Brown’, ‘Biology’, 100000) 插入history_instructors 会发生什么？

---

View Updates in SQL

大多数SQL实现只允许对简单视图进行更新

• from 子句只有一个数据库关系。
• select 子句只包含关系的属性名，没有任何表达式、聚合或不同的 distinct 规范。
• 任何未在 select 子句中列出的属性都可以设置为null。
• 查询没有 group by 或 having 子句。

---

4.3 事务

Transactions

事务 Transaction 由一系列查询和/或更新语句组成，是一个“工作单元”

SQL 标准指定在执行 SQL 语句时隐式地开始事务。

交易必须以下列语句之一结束:

• 提交工作 Commit work。事务执行的更新将永久保存在数据库中。
• 回滚的工作 Rollback work。事务中 SQL 语句执行的所有更新都将撤消。

原子事务 Atomic transaction

• 要么完全执行，要么回滚，就像从未发生过一样

隔离并发事务 Isolation from concurrent transactions

---

4.4 完整性约束

Integrity Constraints

完整性约束通过确保对数据库的授权更改不会导致数据一致性丢失，从而防止对数据库的意外损坏。

• 支票账户的余额必须大于10,000美元
• 银行职员的工资必须至少是每小时4美元
• 客户必须有一个（非空）电话号码

---

Constraints on a Single Relation

Constraints on a Single Relation 单个关系上的约束

• not null
• primary key
• unique
• check （P), where P is a predicate P是谓词

---

Not Null Constraints

not null

• 声明名称和预算不为空 not null

  name varchar(20) not null
  budget numeric(12,2) not null
  

---

Unique Constraints

unique（A1, A2, …, Am）

• 唯一规范声明属性 A1，A2，…，Am 构成一个候选键。
• 候选键允许为空（与主键相反）。

---

The check clause

check（P)子句指定一个谓词P，关系中的每个元组都必须满足该谓词P。

Example：确保这个学期是秋季、冬季、春季或夏季之一

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

---

Referential Integrity

Referential Integrity 参照完整性

确保在一个关系中出现的值对于给定的一组属性也出现在另一个关系中的一组属性中。

• Example：如果“生物学”是出现在教员关系中某个元组中的院系名，则在院系关系中存在“生物学”的元组。

设 A 是一组属性。设 R 和 S 是两个包含属性A的关系，其中 A 是 S 的主键。如果对于出现在 R 中的 A 的任何值，这些值也出现在 S 中，则称 A 是 R 的外键 foreign key。

外键可以指定为 SQL create table 语句的一部分

foreign key (dept_name) references department

默认情况下，外键引用被引用表的主键属性。

SQL 允许显式地指定引用关系的属性列表。

 foreign key (dept_name) references department (dept_name)

---

Cascading Actions in Referential Integrity

Cascading Actions in Referential Integrity 引用完整性中的级联操作

当引用完整性约束被违反时，正常的过程是拒绝导致违反的操作。

在删除或更新的情况下，另一种选择是级联:

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

我们可以使用以下命令代替 cascade :

• set null
• set default

---

Integrity Constraint Violation During Transactions

Integrity Constraint Violation During Transactions 在事务期间违反完整性约束

考虑如下情况：

create table person (
    ID char(10),
    name char(40),
    mother char(10),
    father char(10),
    primary key ID,
    foreign key father references person,
    foreign key mother references person)

如何插入元组而不造成约束违反?

• 在插入 person 之前插入 person 的父亲和母亲
• 或者，将父属性和母属性初始设置为空，在插入所有人员后更新（如果声明父属性和母属性不为空 not null，则不可能）
• 或者推迟约束检查

---

Complex Check Conditions

Complex Check Conditions 复杂检查条件

检查子句中的谓词可以是任意谓词，可以包含子查询。

check (time_slot_id in (select time_slot_id from time_slot))

检查条件声明 section 关系中每个元组中的 time_slot_id 实际上是 time_slot 关系中一个时隙的标识符。

• 不仅在 section 中插入或修改元组时必须检查条件，而且在关系 time_slot 发生变化时也必须检查条件

---

Assertions

断言 assertion 是表示我们希望数据库始终满足的条件的谓词。

以下约束可以用断言来表示:

对于学生关系中的每个元组，属性 tot_cred 的值必须等于该学生成功完成的课程的学分总和。

一个教师不能在一个学期的同一时间段在两个不同的教室授课

SQL中的断言采用以下形式:

create assertion <assertion-name> check (<predicate>);

---

4.5 SQL数据类型和模式

SQL Data Types and Schemas

---

Built-in Data Types in SQL

date: 日期，包含（4位）年、月和日期

• Example：date ‘2005-7-27’

time: 一天中的时间，以小时、分钟和秒为单位。

• Example：time ‘09:00:30’ time ‘09:00:30.75’

timestamp: 日期加上时间

• Examlpe：timestamp ‘2005-7-27 09:00:30.75’

interval: 时间段

• Example：interval ‘1’ day
• 从一个日期/时间/时间戳值 date/time/timestamp 减去另一个值得到一个间隔值
• 间隔值可以添加到日期/时间/时间戳 date/time/timestamp 值中

---

Large-Object Types

大对象 Large objects （照片、视频、CAD文件等）作为大对象存储:

• blob：二进制大对象——对象是未解释的二进制数据的大集合（其解释留给数据库系统外的应用程序）
• clob：字符大对象——对象是字符数据的大集合

当查询返回一个大对象时，返回的是指针而不是大对象本身。

---

User-Defined Types

SQL中的 create type 构造创建用户定义的类型：

create type Dollars as numeric (12,2) final

Example:

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

---

Domains

SQL-92 中的 create domain construct 创建用户定义的域类型：

create domain person_name char(20) not null

类型和域是相似的。域上可以指定约束，比如不为空。

Example:

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

---

4.6 SQL中的索引定义

Index Definition in SQL

---

Index Creation

许多查询只引用表中一小部分记录。

对于系统来说，读取每条记录来查找具有特定值的记录是低效的

关系属性上的索引 index 是一种数据结构，它允许数据库系统在关系中有效地找到具有该属性指定值的元组，而无需扫描关系的所有元组。

我们使用 create index 命令创建索引:

create index <name> on <relation-name> (attribute);

我们还可以删除带有索引名称的索引:

drop index <name>;

---

Index Creation Example

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

create index studentID_index on student(ID)

（以下的语句）可以通过使用索引查找所需的记录来执行查询，而不必查看 student 的所有记录:

select *
from student
where ID = '12345'

---

Basic Concepts

用于加快对所需数据访问的索引机制。

• 例如，图书馆的作者目录

搜索键属性 Search Key：用于查找文件中记录的一组属性。

索引文件 index file 由以下形式的记录（称为索引条目 index entries ）组成

search-key	pointer

索引文件通常比原始文件小得多

两种基本指标:

• 有序索引 Ordered indices：搜索键按排序顺序存储
• 哈希索引 Hash indices：使用“ hash function ”在“ buckets ”中均匀分布搜索键。

---

Example of Hash Index

---

Example of B+ - Tree

---

Type of Index

不同数据类型和条件类型的索引

查询类型	索引类型
Point queries（score=90）	Hash、B±tree
Range queries（score>60）	B±tree
Multi-condition queries (gender=male & rating=5)	Bitmap
Spatial range queries (115.7°<lag<117.4°& 39.4°<long<41.6°)Nearest neighbor queries(KNN)	Multidimensional index (R-tree、Quadtree、KD-tree)

是否支持持久化和快速恢复

• 内存索引和磁盘索引

---

4.7 授权

Authorization

我们可以对数据库的某些部分为用户分配几种形式的授权。

• 读取 Read ——允许读取，但不允许修改数据。
• 插入 Insert ——允许插入新数据，但不修改现有数据。
• 更新 Update ——允许修改，但不删除数据。
• 删除 Delete ——允许删除数据。

这些授权类型中的每一种都称为特权 privilege。我们可以对数据库的指定部分(如关系或视图)授予用户全部、无或这些类型的特权的组合。

修改数据库模式的授权形式

• 索引 Index ——允许创建和删除索引。
• 资源 Resources ——允许创建新的关系。
• 修改 Alteration ——允许在关系中添加或删除属性。
• 删除 Drop ——允许删除关系。

---

Authorization Specification in SQL

grant 语句用于授予授权

grant <privilege list> on <relation or view > to <user list>

为:

• a user-id
• Public，它允许所有有效用户获得授予的特权
• A role

Example:

• grant  select on  department to Amit,  Satoshi
  

在视图上授予特权并不意味着在底层关系上授予任何特权。

特权授予者必须已经拥有对指定项的特权（或者是数据库管理员）。

---

Privileges in SQL

选择 select：允许对关系进行读访问，或者使用视图进行查询

• Example：授权用户U1、U2 和 U3 选择 select 教员关系:

  grant select on instructor to U1, U2, U3
  

插入 insert：插入元组的能力

更新 update：使用SQL update 语句进行更新的能力

删除 delete：删除元组的能力

所有特权 all privileges：用作所有允许的特权的缩写形式

---

Revoking Authorization in SQL

revoke 语句用于撤销授权：

revoke <privilege list> on <relation or view> from <user list>

Example:

revoke select on student from U1, U2, U3

可以是 all，用来撤销被撤销者可能拥有的所有特权。

如果<revoke -list>包含 public，则除了显式授予的权限外，所有用户都将失去权限。

如果同一用户被不同的授予者授予了两次相同的权限，那么该用户在被撤销后可以保留该权限。

依赖于被撤销特权的所有特权也将被撤销。

---

Roles

角色 role 是区分不同用户的一种方法，这些用户可以访问/更新数据库中的内容。

要创建一个角色，我们使用:

create a role <name>

Example:

•  create role instructor
  

创建角色后，我们可以使用以下命令为角色分配“users”:

• grant  <role> to <users>
  

---

Roles Example

create role instructor;

grant instructor to Amit;

权限可以授予角色:

• grant select on takes to instructor
  

角色可以授予用户，也可以授予其他角色

• create role teaching_assistant
  

• grant teaching_assistant to instructor
  

  • 讲师继承助教的所有特权

角色链

• create role dean;
  

• grant instructor to dean;
  

• grant dean to Satoshi;
  

---

Authorization on Views

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
  

如果

• geo_staff 对 instructor 没有权限?
• 视图创建者对 instructor 没有某些权限?

---

Other Authorization Features

引用 refrences 特权创建外键

• grant reference (dept_name) on department to Mariano;
  

• 为什么需要这样做?

转让特权

• grant select on department to Amit with grant option;
  

• revoke select on department from Amit, Satoshi cascade;
  

• revoke select on department from Amit, Satoshi restrict;
  

• And more!