（一）– 绪论

一、数据库系统概述

1.1 数据库的 4 个基本概念

  1. 数据：描述事物的符号记录

  2. 数据库

    概括地讲：数据库具有永久存储、有组织、可共享三个基本特点

    严格地讲：数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。

  3. 数据库管理系统：是帷与用户和操作系统之间的一层数据管理软件，和操作系统一样是计算机的基础软件

    主要功能：

    1. 数据定义功能
    2. 数据组织、存储和管理
    3. 数据操纵功能
    4. 数据库的事务管理和运行管理
    5. 数据库的建立和维护功能
    6. 其他功能（通信功能，数据转换功能，互访和互操作功能）

  4. 数据库系统：是由数据库、数据库管理工具（及其应用开发工具）、应用程序和数据库管理员（DBA）组成的存储、管理、处理和维护数据的系统

数据库系统

1.2、数据库管理技术的产生和发展

  1. 人工管理阶段：数据不保存、不共享、不具有独立性
  2. 文件系统阶段：可保存，但共享性差，冗余度大，独立性差
  3. 数据库管理系统：数据库系统的出现使信息系统从以 加工数据的程序为中心 转向围绕 共享的数据 为中心的新阶段

1.3 数据库系统的特点

  • 实现整体数据的结构化
  • 数据的共享性高、冗余度低且易扩充：数据共享可以大大减少数据冗余，节约存储空间
  • 数据独立性高：
    • 物理独立性高：指用户的应用程序与数据库中数据的物理存储是相互独立的
    • 逻辑独立性：指用户的应用程序与数据库的逻辑结构是相互独立的
  • 数据库由数据库管理系统统一管理和控制

二、数据模型

2.1 两类数据模型

  • 概念模型
  • 逻辑模型和物理模型

人们首先将现实世界抽象为信息世界，然后将信息世界转还为机器世界

2.2 概念模型

也称为信息模型，她是按用户观点来对数据和信息建模，主要用于数据库设计

  • 实体：客观存在并可相互区别的事物，如：人
  • 属性：实体所具有的的某一特性，如：人的鼻子、嘴巴
  • 码：唯一标识实体的属性，如：身份证号
  • 实体型：用实体名及其属性集合来抽象和刻画同类实体，如：含有鼻子和嘴巴的人
  • 实体集：统一类型实体的集合，如：学校
  • 联系：实体之间的联系通常是指不通实体集之间的联系，实体之间的联系有 一对一、一对多和多对多等多种联系

概念模型的一种表示方法：实体 - 联系方法。用 E-R 图来描述显示世界的概念模型，E-R 方法也称为 E-R 模型

E-R 图分为实体、属性、关系三个核心部分、长方形表示实体，椭圆形表示属性，菱形表示关系

E-R图

  • 逻辑模型（高级语言）：它是按计算机系统的观点对数据建模，主要用于数据库管理系统的实现
  • 物理模型（二进制）：是对数据最底层的抽象，它描述数据在系统内部的表示方法和存取方法，或在磁带或磁盘上的存储方式和存取方法，是面向计算机系统的

联系：

  1. 实体之间的联系通常是指不同实体集之间的联系，实体之间的联系有 一对一、一对多、多对多 等多种联系
  2. 实体内部的联系通常是指实体各属性之间的联系

2.3 数据模型的组成要素

  • 数据结构
  • 数据操作
  • 数据的完整性约束

2.4 常用的数据模型

  • 层次模型
  • 网状模型
  • 关系模型
  • 面向对象数据模型
  • 对象关系数据模型
  • 半结构化数据模型

2.5 层次模型

  1. 有且只有一个结点没有双亲结点，这个结点成为根结点
  2. 根结点以外的其他结点有且只有一个双亲结点

层次模型

2.6 网状模型

  1. 允许一个以上的结点没有双亲结点
  2. 一个结点可以有一个以上的双亲结点

网状模型

2.7 关系模型

通常对应的就是一张表

  • 关系：一个关系对应通常说的一张表
  • 元组：表中的一行
  • 属性：表中的一列
  • 码（主键）：也称码键，表中的某个属性组
  • 域：一组具有相同数据类型的值的集合
  • 分量：元组中的一个属性

关系模型

三、数据库系统的结构

3.1 数据库系统模式的概念

模式数据库中全体数据的逻辑结构和特征描述，它仅仅设计型的描述，不涉及具体的值，其中一个具体的值称为模式的一个实例

模式是相对稳定的，实例是相对变动的

3.2 数据系统的三级模式结构

  • 外模式

    也称子模式或用户模式，它是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示

  • 模式

    也称为逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图

  • 内模式

    也称存储模式，一个数据库只有一个内模式，它是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式

3.3 数据库系统的二级映像功能与数据独立性

二级映像：

  • 外模式/模式映像

    当模式改变时，由数据库管理员对各个 外模式/模式映像 作相应改变，可以使外模式保持不变，应用程序不必修改，保证了数据与程序的逻辑独立性

  • 模式/内模式映像

    当数据库的存储结构改变时，有数据库管理员对 模式/内模式映像 作相应改变，可以使模式保持不变，从而应用程序也不用改变，保证了数据与程序的物理独立性

数据独立性：

数据与程序之间的独立性使得数据的定义和描述可以从应用程序中分离出去。另外，由于数据的存取有数据库管理系统管理，从而简化了应用程序的编制，大大减少了应用程序的维护和修改

数据库系统结构

四、数据库系统的组成

  1. 硬件平台及数据库

    足够大的内存、磁盘或磁盘阵列的设备，较高的通道能力以提高数据的传送率

  2. 软件

    数据库管理系统，支持数据库管理系统运行的操作系统，具有与数据库结构的高级语言及其编译系统，以数据库管理系统为核心的应用开发工具，为特定应用环境开发的数据库应用系统

  3. 人员

    开发、管理和使用数据库的人员主要包括数据库系统管理员、系统分析员和数据设计人员、应用程序员和最终用户

（二）– 关系数据库

一、关系数据结构及形式化定义

1.1 关系

一张二维表

1.2 关系模式

对关系的描述

需要描述的关系的部分：

  1. 关系中有哪些属性
  2. 属性来自哪些域（来自那一行的哪个字段）
  3. 属性与域的映射关系

可以形象地表示为
R（U, D, DOM, F）
R：关系名
U：所有属性名
D：属性来自哪些域
DOM：属性和域的映射
F：属性间的依赖关系

1.3 关系数据库

关系数据库也有关系数据库模式

1.4 关系模式的存储类型

二、关系操作

2.1 基本的关系操作

关系操作：

  • 插入
  • 查询
  • 删除
  • 修改

查询操作：

  • 选择
  • 投影
  • 连接
  • 除法
  • 并
  • 差
  • 交
  • 笛卡尔积

查询操作的基本操作:

  • 选择
  • 投影
  • 并
  • 差
  • 笛卡尔积

2.2 关系数据语言的分类

  • 关系代数语言（重点中的重点）
  • SQL 语言（重点）
  • 关系演算语言（不太重要）

三、关系的完整性

3.1 实体完整性

主码（主键）唯一且非空

3.2 参照完整性

外码要么为空， 要么对应另一个表的主码（要么外键是另一个表的主键，要么没有外键）

3.3 用户定义的完整性

自己写的表的属性要写完整

四、关系代数

关系代数是一种抽象的查询语言，它用对关系的运算来表达查询

三大要素：

  1. 运算对象（关系）
  2. 运算符（集合运算符和专门的关系运算符）
  3. 运算结果（关系）

关系代数运算：（前五种是 5 种基本运算）

  • 并
  • 差
  • 笛卡尔积
  • 选择
  • 投影
  • 交
  • 连接
  • 除

4.1 传统的集合运算

集合运算

交

并

差

笛卡尔积表示行与行之间交错

笛卡尔积（表f）

4.2 专门的关系运算

关系运算

选择语句的条件运算符

选择相当于 SQL 语句中的 where

将要查询的条件写在选择符号的右下角，要查询的字段写在后面的括号里

以下语句等同于 select * from Student where Sdept = ”IS“;

其中的 = 可以换成上图的那一堆条件运算符

选择

投影相当于筛选字段，只要求 select 出指定列

将要筛选出的字段写在选择符号的右下角，要查询的字段写在后面的括号里

以下语句等同于 select Sname, Sdept from Student;

投影

以下语句等同于 select Cno from SC where Sno = "95001";

选择与投影的结合

连接相当于 SQL 中的 join

连接

自然连接相当于 SQL 中普通的 join

下面的语句等同于 R join S

  • 悬浮元祖（Dangling tuple）

    两个关系 R 和 S 在做自然连接时，关系 R 中某些元组有可能在 S 中不存在公共属性上值相等的元组，从而造成 R 中这些元组在操作时被舍弃了，这些被舍弃的元组称为悬浮元组

自然连接

  • 外连接（Outer join）

    如果把悬浮元组也保存在结果关系中，而在其他属性上补上空值 NULL，就叫做外连接

  • 左外连接（Left outer join 或 Left join）

    只保留左边关系 R 中的悬浮元祖

  • 右外连接（Right outer join 或 Right join）

    只保留右边关系 S 中的悬浮元组

除：保留 R 中满足 S 的列，而且要去掉 S 中的属性，然后去重

下图就表示除了 数据库，网络 这两个值，还要把 选修课程 这个属性也要去掉

​

除

如果是有多行的话就要同时满足

像下图这样，即使第六行的 a2 满足 b2 c3，但是第二行的 a2 并不满足 S，那也要舍去，所以最后只能剩下 a1

除

（三）– 关系数据库标准语言 SQL

一、SQL 概述

1.1 SQL 的产生与发展

关系型，放在表里面

非关系型，放在文档里面

1.2 SQL 的特点

  1. 综合统一

    独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据、建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作

  2. 高度非过程化

    用户只需提出 “做什么”，而不必指明 “怎么做”。

  3. 面向集合的操作方式

    SQL 采用集合操作方式

  4. 以同一种语法结构提供两种使用方式

    SQL 既是自含式语言，又是嵌入式语言。SQL 语句能够嵌入到高级语言程序中

  5. 语言简洁，易学易用

    SQL 语言语法简单，接近英语口语，因此容易学习，也容易使用。

1.3 SQL 的基本概念

SQL 是 Structured Query Language 的缩写 ，意思是结构化查询语言, 是一种在数据库管理系统中查询或对数据库里面的数据进行更改的语言

顾名思义，查最重要

  1. 数据定义语言 DDL (Data Definition Language)

    SQL数据定义语言主要用来定义逻辑结构，包括定义基表，视图和索引。以及删除表 定义表 修改表

  2. 数据查询语言 DQL (Data Query Language)

    SQL 的数据查询语言主要用来对数据库中的各种数据对象进行查询。

  3. 数据操纵语言 DML (Data Manipulation Language)

    SQL 的数据操纵语言，用于改变数据库中的数据，包括插入，删除，修改

  4. 四、数据控制功能 DCL (Data Control Language)
    SQL 的数据控制语言，对表和视图的授权，完整性规则的描述以及事务开始和结束等控制语句。

二、学生 - 课程数据库

三、数据定义

数据类型

数据类型

3.1 模式的定义与删除

模式定义

1
2
# create schema 模式名 authorization 用户名;
create schema "Review" authorization lgf;

模式定义 + 对应模式视图

1
2
# create schema 模式名 authorization 用户名 [表定义子句 或 视图定义子句 或 授权定义子句]
create schema "Review" authorization lgf create table stu (id int primary key, age int, name varchar(64));

模式删除

1
2
3
4
5
# drop schema 模式名 <cascade 或 restrict>;
drop schema "Review" cascade;
# cascade (全删) 与 restrict (有子目录就拒绝删) 必须二选一
# cascade, 级联删除, 删除模式的同时也罢该模式的所有数据库对象删除
# restrict, 限制删除, 如果该模式下游下属对象, 比如 表 或 视图, 就拒绝这个删除语句的执行

3.2 基本表的定义、删除与修改

表的定义

1
2
3
4
5
6
7
8
9
10
11
12
13
# create table 表名 (
# 字段名 类型 字段约束, 
# 字段名 类型 字段约束, 
# 字段名 类型 字段约束,
# ...
# );

create table user (
    name varchar(64),
    age int,
    sex char(64)
);

表的删除

1
2
3
4
5
# drop table 表名 [cascade 或 restrict];
drop table Student cascade;

# cascade, 级联删除, 如果有 外键/视图/触发器 也会强制删除
# restrict, 限制删除, 如果有 外键/视图/触发器 会拒绝执行删除指令

表的修改

1
2
3
4
# alter table 表名 [Add 新列名 数据类型 [完整性约束]] [Add 表级完整性约束];
# 				   [alter column 列名 修改位哪种类型]
alter table student add S_entrance date;
alter table student alter column Sage int;

3.3 索引的建立与删除

数据量比较大的时候, 查询消耗时间会比较长, 建立索引可以有效减少消耗时间

索引可以建立在 一列 或者 多个列 上

索引的建立

1
2
3
4
# create [unique] [cluster] index 索引名 on 表名 (列名 [次序], 列名 [次序],...);
# unique: 唯一索引
# cluster: 聚簇索引, 物理顺序与索引的逻辑顺序相同, 比如买书
create unique index Stusno on Student(Sno);

索引的修改

1
2
# alter index 旧索引名 rename to 新索引名;
alter index SCno rename to SCSno;

索引的删除

1
2
# drop index 索引名;
drop index Stusname;

3.4 数据字典

四、数据查询

4.1 单表查询

查询所有列

1
2
# select * from 表名;
select * from Student;

查询中给列起别名, 查询后显示的视图上此列也显示的是别名

1
2
# select 列名 [as] 别名 from 表名;
select username [as] 姓名 from User;

查询中给表起别名

1
2
# select 列名 from 表名 [as] 别名;
select username from User a;

查询结果去重

1
2
# select [distinct] 列名 from 表名;
select distinct title from edu_course;

条件查询

条件查询

1
2
3
4
select * from edu_course where price > 2;
select * from edu_course where price between 21 and 50;
select * from edu_course where teacher_id in (1, 2, 3);
# <> 表示不等
1
2
3
4
5
select * from edu_course where title like '%速成'; # % 可以代替多个字符
select * from edu_course where title like '_速成'; # _ 可以代替一个字符
select * from edu_course where (prifce >= 2 and teacher_id = 1) or (title = 'mysql速成');
select Cno, Create from Course where Cname like 'DB \_Design' escape '\';
# 如果要查询的字符串本身就含有 '%' 或 '_', 就需要使用 escape 指定换码字符 (转义字符)
1
2
select * from edu_course order by lesson_num;
select * from edu_course order by lesson_num desc;

聚集函数 (加上 distinct 是去除列里面重复的值来计算)

image-20230302154148073

1
2
3
4
5
select count(title) from edu_course;
select count(distinct title) from edu_course;
select avg(price) from edu_course;
select max(price) from edu_course;
select min(price) from edu_course;

分组查询 (通过 group by 进行分组, 如果要在含有 group by 的语句中进行筛选需要用 having)

1
2
select title from edu_course group by title;
select title from edu_course group by title having title = 'SSM速成';

4.2 连接查询

等值、非等值连接, 以 where 为关键词, 没有 join

1
2
3
select a.*, b.* 
from edu_teacher a, edu_course b 
where a.id = b.teacher_id;

自身连接, 一个表自身连接, 为本身这个表起两个别名, 然后进行连接操作

1
2
3
select a.version, b.view_count 
form edu_course a, edu_course b 
where a.version = b.view_count;

外连接, outer join, 把被舍弃的值也保留在结果视图中, 但是要加 null

左外连接, left [outer] join, 列出左边关系的所有元组, 右边没有匹配的就加 null

右外连接, right [outer] join, 列出右边关系的所有元组, 左边没有匹配的就加 null

1
2
3
4
5
6
select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade 
from Student 
left outer join SC 
on (
    Student.Sno = SC.Sno
);

多表查询, where, 两个及以上表的连接

1
2
3
4
select c.id ID, c.title 课程名, cp.title 章节, cd.description 课程描述 
from edu_course c, edu_chapter cp, edu_course_description cd 
where c.id = cp.course_id 
and c.id = cd.id;

4.3 嵌套查询

1
2
3
4
5
6
7
8
select c.title 课程名, c.price 价格 
from edu_course c 
where c.title 
in (
    select c2.title 
    from edu_course c2 
    where c2.title = "mysql速成"
);

带有 any all 的子查询

any all

1
2
3
4
5
6
7
select c.title 课程名, c.price 价格 
from edu_course c 
where c.price > all (
	select c2.price
	from edu_course c2
	where c2.price between 1 and 20
);
1
2
3
4
5
6
7
select c.title 课程名, c.price 价格
from edu_course c
where c.price > any (
	select c2.price
	from edu_course c2
	where c2.price between 22 and 24
);

带 exists 的子查询

not exists 如果后面的子查询没有值, 返回 1, 否则返回 0

exists 如果后面的子查询有值, 返回 1, 否则返回 0

1
2
3
4
5
6
7
8
9
10
11
12
select Sname
from Student
where not exists (
	select *
	from Course
	where not exists (
    	select * 
    	from SC
    	where Sno = Student.Sno
    	and Cno = Course.Cno
    )
);

4.4 集合查询

并, union, 相当于 or

1
2
3
4
5
6
7
select *
from Student
where Sdept = 'CS'
union
select *
from Student
where Sage <= 19;

交, intersect, 相当于 and

1
2
3
4
5
6
7
select *
from Student
where Sdept = 'CS'
intersect
select *
from Student
where Sage <= 19;

差, except, 从第一个查询中减去第二个查询

1
2
3
4
5
6
7
select *
from Student
where Sdept = 'CS'
except
select *
from Student
where Sage <=19;

4.5 基于派生表的查询

4.6 SELECT 语句的一般格式

五、数据更新

5.1 插入数据

插入, insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# insert into 表名 [(字段, 字段, 字段...)] values (值, 值, 值...);

# 标准添加 (指定所有字段, 指定所有值)
insert into stu(id, name, age, sex, classid) values(1, 'zhangsan', 20, 'm', 'lamp138');

# 指定 部分字段 添加值
insert into stu(name, classid) values('lisi', 'lamp138');

# 不指定字段添加值
insert into stu value(null, 'wangwu', 21, 'w', 'lamp143');

# 批量添加值 (以这种形式添加必须添加所有属性, 不然报错)
insert into stu values
(null, 'zhangliu', 23, 'e', 'fsadf'),
(null, 'zhangliu', 23, 'e', 'fsadf'),
(null, 'zhangliu', 23, 'e', 'fsadf'),
(null, 'zhangliu', 23, 'e', 'fsadf'),
(null, 'zhangliu', 23, 'e', 'fsadf');

5.2 修改数据

修改, update

1
2
# update 表名 set 字段 = 值, 字段 = 值... where 条件
update stu set age = 35, sex = 'm', where id = 11;

5.3 删除数据

删除, delete

1
2
# delete from 表名 [where]
delete from stu where id = 100;

六、空值的处理

七、视图

视图 (view), 也被称为虚表, 即虚拟的表, 是一组数据的逻辑表示, 其本质是对应于一条 select 语句的结果集被赋予的名字, 即视图的名字

视图本身并不包含任何数据, 它只包含映射到基表的一个查询语句, 当基表数据发生变化时, 视图的数据也会发生变化

目的: 方便, 简化数据操作

当我们业务需求要求查出多张表的数据, 这是我们可能会关联多张表进行查询

如果这个 SQL 语句复杂的话也会影响查询效率

这个时候我们就可以创建视图, 查询时只要 select * from view 即可

7.1 定义视图

1
2
3
4
5
6
7
8
# create view 视图名 [(列名, 列名..)] as 子查询 [with check option];
create view IS_Student
as
select Sno, Sname, Sage
from Student
where Sdept = 'IS'
with check option;
# with check option 表示以后对视图进行行插入/修改/删除操作时, 关系数据管理系统对自动加上 Sdept = 'IS' 的条件
1
2
# drop view 视图名 [cascade];
# 视图删除后视图的定义将从数据字典中删除, 如果该视图上还导出了其他视图, 则使用 cascade 级联删除把该视图和由他导出的视图一并删除

7.2 查询视图

1
2
3
4
# 和普通查询一样, 就是表名要写视图名
select Sno, Sage
from IS_Student
where Sage < 20;

7.3 更新视图

视图是不存在的数据, 所以我们对视图进行的操作最终会反映到基本表上

1
2
3
4
5
6
7
8
9
update IS_Student
set Sname = '刘晨'
where Sno = '20203101010';

# 上面语句相当于

update Student # 这是基本表
set Sname = '刘晨'
where Sno = '20203101010' and Sdept = 'IS' # 记得这个 with check option
1
2
3
4
5
6
7
insert into IS_Student
values('20203202020', '张毅', 20);

# 上面语句相当于

insert into Student(Sno, Sname, Sage, Sdept) # 注意这里的属性要与视图的属性相匹配
values('20203202020', '张毅', 20, 'IS'); # 还是要记得 with check option

7.4 视图的作用

（四）– 数据库安全性

一、数据库安全性概述

1.1 数据库的不安全因素

  1. 非授权对数据库的恶意存取和破坏
  2. 数据库中重要的数据泄露
  3. 安全环境的脆弱性

1.2 安全标准简介

二、数据库安全性控制

2.1 用户身份鉴别

  • 静态口令鉴别
  • 动态口令鉴别
  • 生物特征鉴别
  • 智能卡鉴别

2.2 存取控制

主要存取权限

reference 权限代表是否允许创建外键

2.3 自主存取控制方法

用户可以自定义和分配其他用户的操作

主要通过 grant, revoke 进行控制

有两个主要元素构成: 数据库对象 和 操作权限

授权: 我们定义用户的存取权限

  • grant

    1
    - grant 权限 on table 表名 to 用户

  • revoke

    1
    revoke 权限 on table 表名 from 用户

2.4 授权：授予与收回

授权

1
2
3
# grant 权限 on 表名 [(列名)] to 用户 with grant option;
# 授权命令是由数据库管理员使用的, 若给用户分配权限时带 with grant option 子句, 则普通用户获权后, 可把自己的权限授予给其他用户
grant select on Student to user1 with grant option;

收回

1
2
# revoke 权限 on 数据对象 from 用户名 cascade;
revoke select on student from user1;

2.5 数据库角色

角色是指一类人, 可以给角色授予权限

角色创建

1
2
# create role 角色名
create role CEO;

角色授权

1
2
# grant 权限 on 对象类型 对象名 to 角色1, 角色2;
grant select on Student to CEO;

把角色授权给其他用户或角色

1
2
3
# grant 角色 to <角色名 或 用户名> [with admin option]
grant CEO to lihua;
# 如果加上 with admin option 表示这个用户还可以把这个权限授予给其他角色或用户

角色权限的收回

1
2
# revoke 权限 on 对象名 from 角色名字
revoke select on Student from CEO;

2.6 强制存取控制方法

三、视图机制

为不同的用户定义不同的视图, 把不需要的数据给隐藏起来, 这样用户就不会进行误操作了

1
2
3
4
5
6
7
8
9
# 建立计算机系学生的属兔, 把对该视图的 select 权限授予王平, 把该视图上的所有操作权限授予王平
create view CS_Student
as
select * 
from Student 
where Sdept = 'IS';

grant select on CS_Student to 王平;
grant all privileges on CS_Student to 王平;

四、审计

把对数据库的所有操作都记录到审计日志中, 然后就可以通过日志审查是否有进行非法操作

1
2
# 对修改 SC 的操作进行审计
audit update on SC;
1
2
# 取消对 SC 表的一切审计
noaudit update on SC;

五、数据加密

通过一些加密算法, 把明文变成密文, 这样别人就无法查看了

六、其他安全性保护

（五）– 数据库完整性

正确性: 符合现实世界的描述

相容性: 同一对象在不同表里面是符合逻辑的, 比如年龄、地址, 在各个表里都应该一致

维护完整性:

  1. 提供定义完整性约束条件的机制
  2. 提供完整性检查的方法
  3. 进行违约处理

三大完整性:

  1. 实体完整性
  2. 参照完整性
  3. 用户定义完整性

一、实体完整性

实体完整性

1.1 定义实体完整性

1
2
create table course(id not null, time varchar(255), primary key(id));
create table course(id not null primary key, time varchar(255));

1.2 实体完整性检查和违约处理

二、参照完整性

外码要么没有, 要么只有一个

2.1 定义参照完整性

1
2
3
4
5
6
7
8
create table course(
	id int,
	time varchar(255),
	title varchar(255),
	teacher_id int,
	primary key(id),
    foreign key(id) refer人测试 course_description(course_id)
);

2.2 参照完整性检查和违约处理

三、用户定义的完整性

3.1 属性上的约束条件

非空

1
2
3
4
create table student(
	id char(9) primary key,
	age int not null
);

列值为一

1
2
3
4
create table student(
	id char(9) primary key,
	age int unique
);

3.2 元组上的约束条件

满足某一个条件表达式, 用 check 来写

1
2
3
4
5
create table student(
	id char(9) primary key,
	sex char(2) check(sex in ('男', '女')),
	age int not null
);

四、完整性约束命名子句

五、域中完整性的限制 (选学)

六、断言

创建断言

1
2
3
4
5
6
7
8
9
# create assertion 断言名 check子句
# 这里的 check 子句与 where 作用类似, 都是用来添加约束

# 限制没一门课程最多 60 名学生选修
create assertion asse_SC_CNUMI 
check (60 >= all (select count(*)
                 from SC
                 group by cno)
      );

删除断言

1
# drop assertion 断言名

七、触发器

触发器也叫做 事件 -> 条件 -> 动作 规则

当对一个表 增/删/改 的时候, 对触发器里面的条件进行检查

如果成立, 就执行触发器里面的动作, 否则不执行里面的动作

7.1 定义触发器

1
2
3
4
5
create trigger 触发器名				# 每当触发事件发生时, 该触发器被激活
before或after 触发事件 on 表名		  # 致命触发器激活的事件是在执行触发事件的前或后
referencing new或old row as 变量    # referencing 之处引用的变量
for each row或statement			   # 定义触发器的类型, 指明动作体执行的频率
[when 触发条件] 触发动作体			 # 仅当触发条件为真时才执行触发动作体

7.2 激活触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
/*
当对表 SC 的 Grade 属性进行修改时, 若分数增加了 10%, 则将此次操作记录到另一个表 SC_U (Sno、Cno、Oldgrade、Newgrade)中,其中 Oldgrade 是修改前的分数, Newgrade 是修改后的分数
*/
create trigger SC_T				# SC_T 是触发器的名字
after update of Grade on SC		# update of Grade on SC 是触发事件
	# after 是触发的时机, 表示当时 SC 的 Grade 属性修改完成后再触发下面的规则
referencing
	oldrow as OldTuple
	newrow as NewTuple
for each row					# 行级触发器, 即每执行一次 Grade 的更新, 下面的规则就执行一次
when (NewTuple.Grade >= 1.1 * OldTuple.Grade)			# 触发条件, 只有该条件为真时才执行
	insert into SC_U(Sno, Cno, OldGrade, NewGrade)		# 触发后执行下面的 insert 操作
	values(OldTuple.Sno, OldTuple.Cno, OldTuple.Grade, NewTuple.Grade)
1
2
3
4
5
6
7
8
9
10
/*
将每次对表 Student 的插入操作所增加的学生个数记录到表 StudentInsertLog 中
*/
create trigger Student_Count
aafter insert on Studnet					# 指明触发器激活的时间是在执行 insert 后
referencing
	new table delta
for each statement							# 语句执行触发器, 即执行完 insert 语句后下面的触发动作体才执行一次
	insert into StudnetInsertLog (Numbers) 	
	select count(*) from delta

7.3 删除触发器

1
# drop trigger 触发器名 on 表名

（六）– 关系数据理论

一、问题的提出

为什么引入范式？

  1. 数据冗余
  2. 更新异常
  3. 插入异常
  4. 删除异常

范式分类

设计关系数据库时，遵从不同的规范要求，这些不同的规范要求被称为不同的范式，
各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式:

  • 第一范式 (1NF)
  • 第二范式 (2NF)
  • 第三范式 (3NF)
  • 巴斯-科德范式 (BCNF)
  • 第四范式 (4NF)
  • 第五范式 (5NF，又称完美范式)。

一般来说，数据库只需满足第三范式 (3NF) 就行了。

二、规范化

2.1 函数依赖

X -> Y 是非平凡的函数依赖

非平凡函数依赖

X -> Y 是平凡的函数依赖

平凡函数依赖

完全函数依赖

部分函数依赖

2.2 码

候选码: 可以推出所有属性

如何选出候选码?

  1. 只出现在左边的一定是候选码

  2. 只出现在右边的一定不是候选码

  3. 左右都出现的不一定是候选码

  4. 左右都不出现的一定是候选码

  5. 再求确定的候选码的闭包, 如果可以推出全部, 那么当前确定的就是候选码,

    否则, 要把每一个可能的值放进当前确定的候选码里面求闭包

1
2
3
4
5
6
7
8
9
10
11
R<U, F>, U(A, B, C, D, E, F, G), F = {AB --> C, CD --> E, E --> A, A --> G}, 求候选码:

B 一定是候选码, D 一定是候选码
G 一定不是候选码
A, C, E 不一定是候选码
BD --> 推不出来, 所以要把每一个可能的求闭包
(BDA) += 可推出 C, E, A, G 所以可以推出 ABCDEFG
(BDC) += 可推出 E, A, G 所以可以推出 ABCDEFG
(BDE) += A, G, C 可以推出 ABCDEG

那么候选码最终是 {(BDA), (BDC), (BDE)};

超码: 能表示出所有属性的集合, 比如 (BDA), (BDC), (BDE), BDCA, BDEA, ABCDE

候选码是最小的超码

主码: 从候选码里面任意挑出一个作为主码

主属性: 包含在所有候选码里的属性, 比如: ABCDE

非主属性: 不包含在候选码中的属性, 上题为 G

全码: 所有的属性都是主码

2.3 范式

1 NF: 所有字段值都是不可分解的原子值

1 NF

比如某些数据库系统中需要用到 “地址” 这个属性，本来直接将 “地址” 属性设计成一个数据库表
但是如果系统经常会访问 “地址” 属性中的 “城市” 部分，
那么就非要将 “地址” 这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，
这样在对地址中某一部分操作的时候将非常方便

2.4 2NF

不包含非主属性对码的部分函数依赖, 一个表中只能保存一种数据, 不可以把多种数据保存在同一张数据库表中

订单信息表

一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中
该表中商品名称、单位、商品价格等信息不与该表的主键相关，
而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则

2 NF

2.5 3NF

不包含非主属性对码的传递函数依赖, 确保数据表中的每一列数据都和主键直接相关, 而不能间接相关

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。
而不可以在订单表中添加关于客户其它信息 (比如姓名、所属公司等) 的字段

3 NF

2.6 BCNF

消除每一属性对候选键的依赖传递, BCNF 是修正的第三范式

下表存在关系:

  • 书号 -> 书名
  • (书名、作者) -> 书号

下图关系存在传递依赖, 不能是 BCNF

不满足 BCNF

BCNF

2.7 多值依赖

依赖: 指关系中一个或一组属性的值可以决定其他属性的值 A -> B 就是一个依赖

如何求最小依赖集 ?

  1. 拆右边为多个元素的, A -> BC 拆为 A -> B 和 A -> C

  2. 除去当前元素, 求它的闭包, 把集合里面所有元素都弄完

  3. 左边最小化 (通过遮住元素来看能不能推出其他元素)

    比如 BCD, 遮住 B 能否推出 CD ? 遮住 C 能否推出 BD ? 遮住 D 能否推出 BC ?

2.8 4NF

三、数据依赖的公理系统

公理系统

四、模式的分解（选学）

准则: 无损连接, 保持函数依赖

无损: 分解后再次连接, 和分解之前一样

依赖: 依赖不变

依赖表

上表依赖: 科系 -> 系主任, 学号 -> 科系

例题

已知 R (ABCDEGH), F = {A -> D, E -> D, D -> B, BC -> D, DC -> A}, 求保持函数依赖的 3NF 的分解

  1. 求出最小函数依赖
  2. 把不在 F 里面的属性都找出来, 单独分为一类
  3. 把每一个依赖左边相同的分成一类, 没有一样的, 就把 A -> D 改为 {AD}, 如果一样 {A -> B, A -> C}, 那么就改为 {ABC}
  4. 如果候选码没出现在分离里面, 就把任意一个候选码作为一类

题解

（七）– 数据库设计

一、数据库设计概述

1.1 数据库设计的特点

1.2 数据库设计方法

1.3 数据库设计的基本步骤

  1. 需求分析
  2. 概念结构设计: E-R 图 或 设计数据字典
  3. 逻辑结构设计: 把 E-R 图转为 逻辑模型
  4. 物理结构设计: 把 逻辑模型 转为 物理模型
  5. 数据库实施: 写 SQL 代码
  6. 数据库运行维护: 性能检测

1.4 数据库设计过程中的各级模式

二、需求分析

2.1 需求分析的任务

2.2 需求分析的方法

2.3 数据字典

三、概念结构设计

3.1 概念模型

3.2 E-R 模型

E-R 图

  • 长方形 - 实体

  • 椭圆 - 属性

  • 关系 - 菱形

  • 一对一 (1 : 1)

    一对一关系是指对于实体集 A 与实体集 B, A 中的每一个实体至多与 B 中的一个实体有关系, 反之, 在实体集 B 中的每个实体至多与实体集 A 中的一个实体有关系

  • 一对多 (1 : N)

    一对多关系是指实体集 A 与实体集 B 中至少有 N (N > 0) 个实体有关系, 并且实体集 B 中每一个实体至多与实体集 A 中一个实体有关系

  • 多对多 (M : N)

    多对多关系是指实体集 A 中的每一个实体与实体集 B 中至少有 M (M > 0) 个实体有关系, 并且实体集 B 中的每一个实体与实体集 A 中的至少 N (N > 0) 个实体有关系

一对一

一对多

多对多

3.3 扩展的 E-R 模型 (选学)

3.4 UML (选学)

3.5 概念结构设计

四、逻辑结构设计

4.1 E-R 图像关系模型的转换

4.2 数据模型的优化

4.3 设计用户子模式

五、物理结构设计

5.1 数据库物理设计的内容和方法

5.2 关系模式存取方法选择

5.3 确定数据库的存储结构

5.4 评价物理结构

六、数据库的实施和维护

6.1 数据的载入和应用程序的调试

6.2 数据库的试运行

6.3 数据库的运行和维护

（八）– 数据库编程

一、嵌入式 SQL

就是把 SQL 语句嵌入到其他编程语言里面, 比如: Java

1.1 嵌入式 SQL 的处理过程

  1. 预编译转换为函数调用
  2. 主语言编译
  3. 变成主语言所编译的类型

1.2 嵌入式 SQL 语句与主语言之间的通信

  • SQL 给主语言传递状态
  • 主语言给 SQL 提供参数
  • SQL 把查询结果交给主语言处理 (游标 和 主变量实现)

1.3 不用游标的 SQL 语句

1.4 使用游标的 SQL 语句

1.5 动态 SQL

二 、过程化 SQL

2.1 过程化 SQL 的块结构

2.2 变量和常量的定义

2.3 流程控制

三、存储过程和函数

3.1 存储过程

3.2 函数

3.3 过程化 SQL 中的游标

四、ODBC 编程

4.1 ODBC 概述

4.2 ODBC 工作原理概述

4.3 ODBC API 基础

4.4 ODBC 的工作流程

五、OLE DB（选学）

六、JDBC 编程（选学）

（九）– 关系查询处理和查询优化

一、关系数据库系统的查询处理

1.1 查询处理步骤

查询过程

1.2 实现查询操作的算法示例

二、关系数据库系统的查询优化

2.1 查询优化概述

查询优化的四个阶段：

  1. 查询分析
  2. 查询检查
  3. 查询优化
    • 代数优化
    • 物理优化
  4. 查询执行

2.2 一个实例

三、代数优化

3.1 关系代数表达式等价变换规则

  • 选择运算尽量先做
  • 把投影运算和选择运算同时执行
  • 把投影同它前后的双目运算符连接起来

3.2 查询树的启发式优化

查询树

四、物理优化

4.1 基于启发式规则的存取路径选择优化

4.2 基于代价估算的优化

五、查询计划的执行（选学）

（十）– 数据恢复技术

一、事务的基本概念

事务的四大特性: ACID

  • A: 原子性, autom

    要么全做, 要么不做

  • C: 一致性, consistent

    一致性与原子性密切相关, 要么全做要么全不做, 否则就回造成数据不一致, 比如: 银行汇款

  • I: 隔离性, isolate

    一个事务的执行不能被其他事务所干扰

  • D: 持久性, duration

    数据库的改变是永久的, 比如: 存储到磁盘

二、数据库恢复概述

三、故障的种类

  1. 事务内部故障

    采取 REDO 重做 和 UNDO 撤销 技术

  2. 系统故障 DBMS

    系统突然停转, 系统要重启

  3. 介质故障

    硬件损坏

  4. 计算机病毒

四、恢复的实现技术

4.1 数据转储

对失败的事务重新执行

4.2 登记日志文件

记录事务对数据的更新操作文件

五、恢复策略

5.1 事务故障的恢复

事务异常终止, 那么就撤销之前的所有操作

5.2 系统故障的恢复

还没执行完的事务 UNDO, 丢失的事务 REDO

5.3 介质故障的恢复

重装数据库, 重做已经完成的事务

恢复子系统才去的不同策略

六、具有检查点的恢复技术

七、数据库镜像

（十一）– 并发控制（※）

一、并发控制概述

并发带来的问题：

  1. 丢失修改

    我修改的东西没生效， 比如：把票价改成 99 元，结果还是原来的 299 元

  2. 读脏数据

    如果要读取数据库中的字段 A, 字段 B, 读取时恰巧有其他用户正在更新这两个字段, 而且 , 如果那个用户更新一半就读去了, 也就是说更新了 A, 正打算要更新 B, 但尚未更新, 就读去了

  3. 不可重复读取

    一个事务中, 连续两次读取到的数据不一样

解决方法:

  1. 排它锁: 写锁, X 锁
  2. 共享锁: 读锁, S 锁

二、封锁

三、封锁协议

  1. 一级封锁协议: 解决丢失修改问题

    修改时, 必须加 X 锁, 直到结束

  2. 二级封锁协议: 解决读脏数据问题

    读的时候, 加 S 锁, 用完就放

  3. 三级封锁协议: 解决不可重复读问题

    读的时候, 加 S 锁, 直到结束

封锁协议

四、活锁和死锁

五、并发调度的可串行性

假设多种情况都可以, 然后获得结果,

如果并发执行的结果跟上面的结果里面任意一个一样就可以

事务 T1: 读取 B, A = B + 1, 写回 A

事务 T2: 读取 A, B = A + 1, 写回 B

假设先 T1, 再 T2, 则 A = 4, B = 3

假设先 T2, 再 T1, 则 A = 3, B = 4

六、两段锁协议

七、封锁的粒度

八、其他并发控制机制（选学）