数据库系统原理 期末复习（一）

第一章

绪论

数据库是用来有效管理数据的工具

文件系统从一定程度上没有屏蔽数据的逻辑存储结构，带来了前端与数据的不独立，而且会造成数据冗余，有数据不一致的可能；还有并发问题、备份问题

数据库三层结构

物理层

描述物理存储结构（物理模式）

内模式

逻辑层

描述逻辑存储结构以及之间的关系（概念模式）

视图层（View level）

面向某一类用户的逻辑信息

• 外模式可能有多个，全部综合起来构成的整体就是数据库的逻辑模式

外模式

三层模式需要在之间建立映射关系（两级Mapping），形成了数据的独立性

数据库的独立性

物理数据的独立性（完备）

面对物理数据的改变，前端操作不需要进行更改

逻辑数据独立性（不完备）

逻辑方式的改变，前端应用不一定需要进行更改

只需要改视图与逻辑模式之间的映射（映像）

如果说**模式（Schema）是数据的结构，那么实例（Instance）**就是某一个时候数据库包含的内容

实例是相对来说比较动态的

数据模型

数据结构

• 基于对象的模型
  • 实体关系模型
  • 面向对象模型
• 基于记录的模型
  • 层次数据模型
  • 网状数据模型
  • 关系数据模型

数据库语言

数据定义语言（Data Definition Language，DDL）

数据操作语言（Data Manipulation Language，DML）

query，insert，delete，update

SQL是非过程化的语言

此外，还有数据控制语言DCL（负责权限控制）

数据库三层结构图

• data：数据
• indices：索引
• data dictionary：数据字典，存放的是元数据，也就是管理数据的数据（关键常驻内存）
• query processor & storage manager：DBMS

程序结构

C/S：client/server结构，上图都是c/s结构

B/S：browser/server/database结构

第二章 关系模型

关系模型的数据结构

关系 relation

关系的定义：一系列域的笛卡尔积的子集

域：取值范围，数学意义上可以是无限的

要注意笛卡尔积在数学意义上不满足交换律，位置信息是有含义的

关系在数学意义上可以是无限的

注意：在数据库中，为每个域引入了一个名称（也就是属性），此时该关系满足交换律；且关系在数据库中是各个域的笛卡尔积的有限的，有意义的子集

模式 relation schema

$$用R()或R=()来表示关系模式，如studentSchema(name,id,sex)\\$$

$$r(R)是在关系模式R上的关系$$

实例 relation instance

可以用二维表来表示建立在一个关系上的关系实例

• 表头即为属性

• 一行即为一条记录

• 行的顺序不需要严格要求（可以乱序）

• 列的顺序也不需要严格要求，因为关系满足交换律

• 属性都是原子的（atomic），不再可分 （关系数据库的第一范式）

• 属性名在同一张表中不能重复

  User.Database.Table.Attribute

• 行不能重复（实际应用中可以选择重复）

• 不同的属性可来自相同取值域

关系模型的概念

空值 NULL

在数据库中代表两个情况：

• 数据不存在
• 还没有查到数据

超键 SuperKey

R(U) is a relation schema , K ⊆ U;

K is a superkey(超键）for R:

​ for any r, no two distinct tuples have the same values on K. That is ,if t1 and t2 are in r and t1≠t2, then t1[k] ≠t2[k];

就是可以唯一确定元组的属性集合

候选键 CandidateKey

是可以唯一确定元组的最小超键

也就是说，去掉候选键后就无法唯一确定元组了

• 唯一性
• 最小性

主键 PrimaryKey

可能存在若干个候选键，可以选择其中一个作为主键

主键的选择一般根据前端应用需求或数据特点决定

主键的取值不能为空（NULL）

外键 ForeignKey

对两张表R1，R2，如果R1包含了R2的主键X，那么X被称为参照R2的外键

• 外键X在R1中

• 参照的是R2中的主键

• 外键的取值可以为NULL

• 外键的命名不一定需要和它参照的主键名相同，可以在系统中指定

• 外键可以定义在同一张表上，也就是参照自己这张表的主键

完整性约束 Integerty Constraint

实体完整性约束 Integrity Constraint of Primary Key

主键不能为空，即它的属性都不能为空

指的是主键的任意一列都不能为空

参照完整性约束 Referential Integrity Constraint

外键取值要么为NULL，要么一定来源于参照的主键的值

除此之外，用户也可以自定义完整性约束

---

关系代数 Relational Algebra

• 选择 Select
• 投影 Project
• 并 Union
• 差 Set Difference
• 笛卡尔积 Cartesian product
• 更名 Rename

真正的操作对象是表中的数据

选择 Select

一元操作，只对一张表操作，结果由满足条件的元组构成

• $σ_p(r)$ p is called the selection predicate(选择断言条件)
• σ_p(r) = \{t | t ∈ r \and p(t)\}

Eg.

从班级中选择出女同学

$σ_{sex=female}(class)$

投影 Project

一元操作，只对一张表操作

• $\Pi A_1,A_2,...,A_k(r)$

A是属性

r是关系

选择出的是满足属性条件的列

关系代数中，由于关系的记录不能重复，所以当结果存在相同元组时，结果只会存在一条

而SQL提供是否需要去除重复项的功能

集合 Union

二元操作，对两张表操作

• $r\cup s$

• $r\cup s = \{t|t\in r \ or\ t\in s\}$

• 结果是两张表的合并

• 属性个数要一致

  属性名称不必一致，因为操作对象是数据

• 域要相容

关系代数中，由于关系的记录不能重复，所以当结果存在相同元组时，结果只会存在一条

而SQL提供是否需要去除重复项的功能

差 Set

二元操作

• $r-s$

• $r - s = \{t|t\in r \ and\ t\notin s\}$

• 属性个数要一致

  属性名称不必一致，因为操作对象是数据

• 域要相容

区分不等于操作<>，不等于操作只会筛选出不等于的数据

（一个人同时选了01，02两门课，不等于01的操作还是会把这个人选择到）

而差操作可以实现选出没有选择01的人，此时上文这个人将不会被选择到

笛卡尔积 Cartesian-Product

• $r \times s$

• $r \times s = \{tq|t\in r \ and\ q\in s\}$

• 任意组合，m条*n条记录，a+b个属性（名字相同也不去重）

• 无要求

更名 Rename

可以返回一个属性重命名的表达式

• $\rho _x(E)$

附加操作

交 Set-Intersection

二元操作，对两张表操作

• $r\cap s$

• $r\cap s = \{t|t\in r \ and\ t\in s\}$

• 结果是两张表的公共部分

• 属性个数要一致

  属性名称不必一致，因为操作对象是数据

• $r\cap s = r-(r-s)$

连接 Join

有条件的笛卡尔积

• $r\Join _{A\vartheta B}s$
• $r\Join _{A\vartheta B} s=\{tq|t\in r \ and\ q\in s\ and\ (t[A]\ \vartheta \ t[B])\}$
• $σ_{A\vartheta B}(r\times s)$
• $\vartheta\in\{=,\neq,\ge,\le,>,<\}$

自然连接 Natural-Join

也叫内连接

• 无需自己定义条件的自然连接，自然认为条件是均有的属性

• $r\Join s = \Pi _{Att(r)\cup(Att(s)-\{B\})}\sigma_{r.B=s.B}(r\times s)$

• B是r和s均有的属性

除法 Division

它不讲我不学，快乐北邮每一天

赋值操作 Alignment

就是赋值

外连接 Outer Join

其实就是内连接，但是会将没有连接的行也一样放入结果中返回，没有连接上的记录值用null代替

• ⟕ 左外连接，会将左边表没有连接的行也一样放入结果中返回
• ⟖右外连接，会将右边表没有连接的行也一样放入结果中返回
• ⟗全外连接，会将左、右表没有连接的行放入结果中返回

空值 NULL

任何含有NULL的代数表达式的结果都是NULL

• Comparisons with null values return the special truth value: unknown

• Three-valued logic using the truth value unknown:

• • OR: (unknown or true) = true,
    (unknown or false) = *unknown
    (*unknown or unknown) = unknown
  • AND: (true and unknown) = unknown,
    (false and unknown) = false,
    (unknown and unknown) = unknown
  • NOT*: (not* unknown) = unknown

• Result of select predicate is treated as false if it evaluates to unknown

---

数据库的调整操作

删除 Deletion

$$r \leftarrow r - E\\ r:关系\\ E:关系代数$$

插入 Insertion

$$r \leftarrow r \cup E\\ r:关系\\ E:关系代数$$

更新 Updating

更新表中对应属性的值

$$r \leftarrow \Pi_{F_1,F_2,..,F_i}(r)\\ r:关系\\ E:关系代数$$

---

第三-四章 SQL语句

建表

create table R (
  id int,
  name varchar(20) not null,
  day date default '1970-01-01',
  sex varchar(6),
  primary key(id),
  foreign key(day) references T
  					on delete cascade
  					on update cascade,
  check (sex in('male','female'))
);

删表

drop table R

添加属性

alter table R add A TYPE
eg. alter table R add sex varchar(10)

选择

select [distinct] name,id 
from R
where id between 5000 and 7500
and
name like '% Mike %'

%:任意字符匹配

_:单个字符匹配

[]：匹配括号中的任意字符

更名

select name
from R as A, R as B
where A.salary > B.salary

排序

select id
from R
order by id desc

asc:升序

desc：降序

集合操作 - 并、交、差

(select id from A) 
union
(select id from B)

(select id from A)
intersect
(select id from B)

(select id from A)
except
(select id from B)

运算操作 avg、min、max、count

select avg(score)
from student

全部的运算操作中，只有count * 不忽略null值

分组 group by

select id,avg(salary)
from R
group by sex
having id > 5000

注意：使用group by后，select出的只能是having的属性或运算后的属性

having子句中的运算操作在组形成之后使用，而where子句中的运算操作在组形成之前使用

子语句

select name
from A
where A.id in (
  select id from B
)

some all

select name
from A
where A.id > some / all (
  select id from B
)

Exists

select name
from A
where exists (
  select name from B
)

如果子句的结果不为空，exists会返回true

unique

select name
from A
where unique (
  select name from B
)

如果子句的结果没有冲突，unique会返回true

with

with get_salary(value) as (
  select salary
  from R
  where value > 5000
)
select name
from B,get_salary
where get_salary.value = B.salary

with提供的实际上是函数的功能，对外暴露的属性放置在括号中。

删除记录 delete

delete from R
where R.name == "Mike"

插入记录 insert

insert into R
values("Mike",114514)

Add all instructors to the student relation with tot_creds set to 0

insert into Student
select ID,name,dept_name,0
from instructor

更新记录

update R 
set salary = salary * 1.03
where salary > 10000

case when

update R
set salary = 
	case when salary <= 1000 
			 then salary * 1.05
			 else salary * 1.03
	end

索引 index

create [unique] index R_index on R(name)

drop index R_index

连接 join

select *
from student 
join takes
on student.id = takes.stu_id

自然连接 natural join

select *
from student
natural join takes

using

要指定自然连接的依据，可以使用using

select *
from student
natural join takes 
using(id)

inner join

内连接，不会保留含有属性值为null的记录

left/right/full outer join

外连接

以左外连接为例

select *
from student
left outer join takes
on student.id = takes.id

这样得到的记录中，会存在student的属性有记录而takes的属性为空的记录

视图 views

创建视图

create view stuView(ID,name,avg_grade) as
select ID,name,avg(grade)
from student

更新视图

insert into stuView values
(114514,'Mike',100)

此时，等同于对原表使用insert，但是相对于原表，视图中没有的属性将会表现为null，如(114514,‘Mike’,100,null)

权限

授予权限

grant select on R to U1,U2

select,insert,update,delete,read,index,resources,alteration,drop

收回权限

revoke select on R from U1,U2

如果有多个权限授予源，授予多次相同的权限，只收回一次权限并不会一次性去掉全部相同的权限

角色

create role instructor
grant select on R to instructor

grant teacher to instructor

事件

create trigger credits_earned after update of takes on (grade)
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade <> ’F’ and nrow.grade is not null
and (orow.grade = ’F’ or orow.grade is null)
begin atomic
	update student
	set tot_cred= tot_cred + (
  	select credits
  	from course
  	where course.course_id= nrow.course_id
)
	where student.id = nrow.id;
end;