【复习】数据库

目录

2.0 数据库概论

2.1 数据模型与关系代数

2.1.1 关系代数运算

2.1.2 关系模型

2.1.3 键

2.1.4 关系代数查询

2.1.5 数据库模式

2.2 SQL

2.2.1 DDL

2.2.2 DML

2.2.3 约束 & 触发器

2.2.4 复杂计算

2.2.5 视图、索引、存储过程

2.3 数据库设计与规范化理论

2.3.1 数据库设计

2.3.2 规范化理论

2.3.3 ER图

2.4 数据库对象

2.4.1 数据库完整性

2.4.2 视图、索引、存储过程

2.4.3 数据库安全性

2.4.4 授权

2.5 并发控制与日志恢复

2.5.1 数据库恢复

3.5.2 并发控制

---

真题：

查找所有至少选择了一门Song老师课程的男学生

2.0 数据库概论

数据库：信息的集合

DBMS：数据库管理系统（SQL）

DDL：数据定义语言，定义数据库，建立、修改、撤销

DML：数据操作语言，插入、删除、更新、查找

DCL：数据控制语言，授权、撤销授权

2.1 数据模型与关系代数

（关系模型的基本理论）

2.1.1 关系代数运算

集合运算：交、并、差、积

关系运算：投影、选择、除、连接

2.1.2 关系模型

关系模型：是一种基于表的数据模型（就是用表来表示关系？）

分量：元组的每一个成分叫做它的分量

例子：

2.1.3 键

候选键：能唯一确定一条元组的属性

主关键字：人为确定，用在属性下方添加下划线来表示

外关键字：本关系R1的属性a为关系R2中的主关键字，则a为R1的外关键字

**注意：外键不是另一个表格的主键，但必须是唯一性索引；所以通常都采用主键来当外键

2.1.4 关系代数查询

原始运算：

并Union	U		消除了两个集合重复的元组（集合的性质），新关系（元组集合）中每个元组是唯一的。
差Minus	-		在R中但不在S中的元组的集合。R和S必须同类型（属性集、次序相同，属性名可以不同）。新关系中每个元组是唯一的。
交intersect			同时出现在两个关系中的元组的集合。R和S必须满足同类型。
广义笛卡尔积	X		R与S的无条件连接，使得任意两个关系的信息可以组合在一起。
选择运算	σ		F是选择的条件，从R中选出满足F为真的元组组成新元组。选择运算针对的是行，注意需要去除重复的分组。
投影运算			从关系R中取若干列组成新的关系（集合）。这是对列的运算，注意也需要去除重复的行。
改名运算	ρ		改变关系名和属性名，将关系R改名为S。
连串Concatenation			属性名的拼接，这种拼接是有顺序的（A和B不能交换）。这时，我们的笛卡尔积表达式就可以表示为：
连接	θ		从两个关系的广义笛卡尔积中选取给定属性间满足一定条件的元组。
自然连接			从两个关系的广义笛卡儿积中选取在相同属性列B上取值相等的元组，并去掉重复的行
外连接			为避免自然连接时因失配而发生的信息丢失，可以假定往参与连接的一方表中附加一个取值全为空值的行，它和参与连接的另一方表中的任何一个未匹配上的元组都能匹配，称之为外连接。
除法运算
赋值运算	←	临时关系变量 ← 关系代数表达式	临时关系变量之所以叫临时关系变量是因为其不会对数据库作出实质的修改。
广义投影		选取了P#:教工名，和SAL:工资两个属性作为新关系，但是选取之后还要对SAL这一列上所有值*5/100，得到的就是所得税。	即用算术表达式对投影进行扩展。

域计算的运算：

Sum
Count
Average（avg）
Maximum（max）
Minimum（min）	同理max

2.1.5 数据库模式

2.2 SQL

（定义、查询、更新）

2.2.1 DDL

1. 创建

CREATE TABLE Product(
        maker    CHAR(10),
        model    INT PRIMARY KEY,
        color BOOLEAN,        // 布尔值，只能取0或1
        speed DECIMAL(4,2),    // 精确数字类型，最大位数为4，最多有2位小数
        born        DATE,
        movie_exec_cert# INT,  
        FOREIGN KEY (movie_exec_cert#) REFERENCES MovieExec(cert#)// movie_exec_cert#是外键
            ON DELETE CASCADE        //当本子表一行被删除时，父表也被删除
);
CREATE TABLE Movies (
    title     CHAR(100) ,
    year        INT ,
    PRIMARY KEY (title,year), // title和year一起作为主键
    length    INT ,
    genre    CHAR(10) ,
    studioName    CHAR(30) ,
    producerC#    INT REFERENCES MovieExec（cert#）//producerC#是表格MovieExec里与cert#对应的外键
        ON DELETE SET NULL  //当MovieExec表中被cert#列引用的某一行被删除时，producerC#列中对应的值将被设置为NULL
        ON UPDATE SET NULL//当MovieExec表中被cert#列引用的某一行被修改时，producerC#列中对应的值将被设置为NULL
) ;

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name); // 添加主键约束  
ALTER TABLE table_name DROP CONSTRAINT constraint_name;  //删除约束

1. 修改& 撤销

ALTER TABLE Printer DROP color;    //从表格Printer里删除color的属性
ALTER TABLE Laptop ADD od CHAR (10) DEFAULT ‘none’; //向表格Laptop里添加一列属性od类型是CHAR（10），默认值是none
RENAME TABLE old_table_name TO new_table_name;       // 重命名表格
ALTER TABLE table_name MODIFY COLUMN column_name datatype; //修改列的数据类型  
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;//重命名列并可能更改其数据类型。

2.2.2 DML

1. 插入

INSERT INTO TableName( TitleName ) VALUES(.TupleValue..);

INSERT INTO StarIn(movieTitle,movieYear,starName)
VALUES('The Maltese Falcon', 1942, 'Sydey Green');

1. 删除

DELETE FROM TableName WHERE(...条件...);

DELETE FROM StarIn
WHERE movieTitle = 'The Maltese Falcon' 
    AND MovieYear = 1942 
    AND starName = 'Sydey Green';

1. 更新UPDATE

UPDATE TbaleName SET <新值赋值> WHERE <条件>; 

UPDATE MovieErec
SET name='Pres.' || name    // 字符连接，表示在name前面加上Pres.
WHERE cert# IN (SELECT presC# FROM Studio);

1. 查找

SELECT DISTINCT ...     // 消除重复行 

// 正则表达
// 通配符：%任意字符（可以为0），_单个字符
##查找名字中有“腾”顾客
select name
from user_id
where name like '%腾%'
 
##查找名字的姓为“张”的顾客
select name
from user_id
where name like '张%'
 
##查找名字的最后一个字为“峰”的顾客
select name
from user_id
where name like '%峰'

// 查找空
SELECT name FROM Students WHERE gender IS NULL;

2.2.3 约束 & 触发器

1. check

CREATE TABLE Product(
    length INT CHECK (length>60 AND length<120)
);

//电影明星不会出现在出生年份之前的电影中
CREATE TABLE StarsIn(
    starName CHAR(30)
        CHECK ( 
            starName IN (
                SELECT name FROM MovieStar 
                WHERE YEAR (birthdate)> movieYear  // 从数据类型DATE中访问年份
            )
        )
);
CREATE TABLE MovieStar(
    name CHAR(100) CHECK (
        name NOT IN (
            SELECT name FROM MovieExec
        )
    ),
    address CHAR(100)
);
// 某人如果即使电影的制作人又是电影公司的经理，那他必须是制作这个电影的电影公司的经理
CREATE TABLE Movies(
    title CHAR(10),
    producerC# INT CHECK(
        producerC# NOT IN 
            (SELECT presC# FROM Studio)
        OR studioName IN 
            (SELECT name FROM Studio WHERE presC#=producerC#)
    )
);
CREATE TABLE Studio(
    name     CHAR(100),
    adresss    CHAR(100) CHECK (
        adress IS UNIQUE
    )
);

一个影星性别为女或者名字不是以男士开头-> 即，性别和称呼关于男女的含义不可以有冲突，经常使用or来表示这种多个条件之间的约束。

1. 断言assertion

断言条件必须永保为真，任何不符合断言的更改都不被允许

CREATE ASSERTION RichPres CHECK(
    NOT EXISTS(
        SELECT Studio.name FROM Studio,MovieExec
        WHERE PresC#=Cert# AND newWorth<10000000    
    )
);

DROP ASSERTION <断言名>

1. 触发器Trigger

触发器仅当被声明的操作发生时被激活，通常是插入、删除、修改等

被触发后，触发器几乎可以进行任何操作，包括与触发条件完全无关的

CREATE TRIGGER changeStarsInTrigger
AFTER INSERT ON StarsIn     // 触发器激活条件INSERT ON
REFERENCING 
    NEW ROW AS NewRow     // 插入所以只有NewRow，如果是UPDATE还可以命名OldRow
FOR EACH ROW 
WHEN (                     // 触发器触发条件
    NewRow.starName NOT EXISTS (SELECT name FROM MovieStar)
    )
    INSERT INTO MovieStar(name) VALUES(NewRow.starName);
                        // 若触发行为：向表格MovieStar插入一行，name值为NewRow.starName

CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth On MovieExec  // 触发器激活条件UPDATE OF ... ON
REFERENCING 
    OLD ROW AS OldTuple,    // 中间用“，”隔开，末尾不加符号
    NEW ROW AS NewTuple     
WHEN ( OldTuple.netWorth > NewTuple.netWorth )
    UPDATE MovieErec
    SET netWorth = OldWorth.netWorth
    WHERE cert# = NewTuple.cert#; 
    // 操作：若符合->触发行为：把netWorth的值改回去，相当于阻止了更新操作

// 模板 & 选项
CREATE TRIGGER TriggerName
AFTER|BEFOR UPDATE-OF|INSERT|DELETE ON ... ||选择1：操作时机
                                      ||选择2：触发事件
REFERENCING 
    OLD ROW|TABLE AS OldRow|OldStuff,               ||选择5：语句命名。UPDATE-OLD+NEW；INSERT-new；DELETE-OLD
    NEW ROW|TABLE AS NewRow|NewStuff                ||选择7：触发级别-元组|语句
[FOR EACH ROW|STATEMENT]            ||选择6：可缺省，默认STATEMENT语句级，ROW行级。
[WHEN( ... )]            ||选择3：有没有执行条件，WHEN可缺省
    [BEGIN]                      ||选择4：操作数量,单个操作可以不加begin和end;多个操作之间用“，”分隔
        
    [END];

Before触发器的重要性：

2.2.4 复杂计算

// COUNT & ALL & GROUP BY
WHEN ( 1 > ALL (
    SELECT COUNT(*) FROM StarIn s, MovieStar m
    WHERE s.starName = m.name
    GROUP BY s.movieTitle, m.gendar)
)

子查询：

查询了StarIn和MovieStar两个table，两个table会自然连接，并保留其中s.starName = m.name的行。

然后以s.movieTitle, m.gendar进行分组，只有s.movieTitle, m.gendar两个属性都一样的行才会被分到一个组。

再计数每一个分组有多少行【可以将*改为条件，那么只计数满足条件的行】->求得每一部电影里面，有男性多少人和女性多少人。

比较：

子查询的结果相当于是一个人数的表格，只有这一个属性和分量。ALL表示1必须大于子查询结果列表中的每一个数

// AVG
SELECT AVG(length) FROM Movies
    GROUP BY year;

求平均值。要指定求平均值的对象是哪一个属性。

2.2.5 视图、索引、存储过程

1. 视图

CREATE VIEW <ViewName> AS  <视图定义>

CREATE VIEW ViewName[(movieTitle,proName)可选项，重命名] AS
    SELECT title,name
    FROM Movies,MovieExec
    WHERE producerC# = cert#;

DROP VIEW ViewName;

可以把视图当成一个Table来进行查询；

可更新视图：一般视图不可以插入、删除、修改；但一些特定的视图可以——可更新视图。把对视图的更改替换为对基本表的更改。可更新视图可以理解为：该视图是由单个关系R选出的一些属性组成，且满足下列三个技术要点：

WHERE子句在子查询中不可以使用R；SELECT语句不包含定义为非空或没有默认值的属性；FROM语句只有一个关系R

替换触发器：把对视图的操作更改为对相应的table的操作

CREATE TRIGGER DisneyComedyInsert 
INSTEAD OF INSERT ON DisneyComedies         // 操作对象：视图
REFERENCING 
    NEW ROW AS NewRow
FOR EACH ROW
    INSERT INTO Movies(title, year, length, studioName, genre)
    VALUES(NewRow.title, NewRow.year, NewYear.length, ‘Disney’, ‘comedy’);

CREATE TRIGGER DisneyComedyUpdate 
INSTEAD OF UPDATE ON DisneyComedies 
REFERENCING
    NEW ROW AS NewRow
FOR EACH ROW
    UPDATE Movies SET length NewRow.length
    WHERE title = NewRow.title AND year = NEWROW.year AND studionName = ‘Disney’ AND genre = ‘comedy’;

1. 索引

CREATE INDEX <IndexName> ON TableName(title1,title2);
DROP  INDEX IndexName;

10改为100：

物化视图

1. 存储过程

2.3 数据库设计与规范化理论

2.3.1 数据库设计

ER：实体关系模型

（数据库的规范化理论）

2.3.2 规范化理论

数据依赖有： 函数依赖FD、多值依赖MVD

例子:学生表(学号、姓名、性别、年龄、班级、系)

超码：能唯一标识一条记录的属性或属性集（比候选码少一个最小子集的要求）

候选码：某个属性组的值能唯一标识一个元组，而其子集不能

主码：一个关系有多个候选码，选择一个作为其主码

全码：关系模式的所有属性是这个关系的候选码,则称该属性组为全码

主属性：候选码属性组之间的各个属性,例如,候选码:学号,主属性为学号,非主属性为姓名,性别,年龄,班级,系

1. 关系规范化的异常

删除异常：不该删除的被删除了

插入异常：应该插入的没有插入

数据冗余太大：一个信息多次存储

数据更新异常：一个数据要修改的时候，所有副本都要修改

1. 范式（1NF、2NF、3NF、BCNF、4NF、W4NF、5NF）

1NF：所有属性都不可再分

2NF：1NF+每一个非主属性完全函数依赖于任何一个候选码（有且仅有一个主键，其他数据项与主键一一对应，没有部分依赖）

候选码：可以唯一表示一个元组的属性组，其子集不能；多个候选码可指定主码。

主属性： 所有候选码的属性称为主属性。不包含在任何候选码中的属性称为非主属性或非码属性。

3NF：既没有部分依赖（2FN），还没有传递依赖

BCNF：每一个决定因素都含有关系模式R的码（任意一个候选键都行）

规范化过程（大题！！！）

1. 判断无损连接

分解后的关系模式能够还原出原关系模式，就是无损分解，不能还原就是有损。

1. 保持函数依赖

对于关系模式R，有依赖集F，若对R进行分解，分解出来的多个关系模式，保持原来的依赖集不变，则为保持函数依赖的分解。另外，注意要消除掉亢余依赖（如传递依赖）。

（ER图建立概念模型）

2.3.3 ER图

1. 强弱

强：实体、连接线、主键、关系等，统统单线和实线

弱：实体、连接线、主键、关系等，统统双线和虚线

1. 箭头

A->B 表示对于一个B可以找到他唯一对应的A

2.4 数据库对象

（数据库完整性）

2.4.1 数据库完整性

1. 定义

任何情况下的正确性、有效性、一致性

广义：并发控制、安全控制、故障恢复

狭义：语义完整性

1. 原因：输入错误、操作失误、程序处理错误等
2. 解决方法：定义完整性约束规则DDL；完整性控制程序（DB更新时自动检查）
3. 完整性约束条件

分类：对象：域/类型完整性约束（说明给定类型的合法取值，一列）；关系完整性约束条件（说明关系的合法取值，多列）

来源：结构约束（关心数值，如实体完整性，参照完整性，函数依赖）；内容约束（关系取值范围，用户自定义完整性）

状态：静态约束（任何时候满足）；动态约束（状态的改变满足）

SQL的完整性实现：

静态：列完整性+表完整性（域完整性约束+关系完整性约束）

动态：触发器(一种过程完整性约束)

（视图、索引、存储过程的使用）

2.4.2 视图、索引、存储过程

（1）视图：未被存储的外模式+E-C映像，方便检索

不可更新的情况：有聚合函数列；有group by；有distinct；有伪列；有计算表达式定义的列

（2）索引：辅助存储结构，提高效率，但增大存储空间的负担

主索引、稠密索引QA2、辅助索引、聚簇索引、倒排索引、多级索引、散列索引

主索引：一个索引项对应一个存储块，通常与文件的主码对应

聚集索引：通过在表中创建索引，将表中的数据物理上按照索引的键值顺序进行排序，并以此为依据进行访问和处理。聚集索引仅能有一个，并且它的顺序决定了整个表中记录的物理存储顺序。

全文索引：使用倒排索引实现，记录关键词到其它所在文档的映射

哈希索引：O(1)的时间复杂度，但失去了有序性，只支持精确查找，不支持排序或分组

只满足=，IN，<=>查询，不能使用范围查询。

优点：减少服务器需要扫描的数据行数；避免服务器进行排序和分组操作，以避免临时表的创建；将随机IO变成了顺序IO。

缺点：降低INSERT和DELETE的效率，因为可能重建索引；需要额外的磁盘空间；

（3）存储过程：是一组了完成特定功能的SQL语句

create procedure 存储过程名
call sp_name 参数名

（数据库安全性）

2.4.3 数据库安全性

1. 定义：DBMS应保证DB的特性，免受非法、非授权用户的使用、泄露、更改和破坏。
2. 相关机制

自主安全性机制：存取控制（存储矩阵、视图）

强制安全性机制：推断控制机制

数据加密机制

（用户授权机制）

2.4.4 授权

grant 权力 on 表/视图 to 用户
revoke 权力 on 表/视图 from 用户

2.5 并发控制与日志恢复

（数据库恢复和并发控制技术）

2.5.1 数据库恢复

1. 事务：满足ACID特性的一组操作

commit work        // 将事务中的更新写回到磁盘，事务正常结束
rollback work      // 数据库返回事务开始的状态

特性ACID：

A原子性：事务的一组更新操作是原子不可分的

C一致性：数据库在事务执行前后保持一致性状态，所有事务对一个数据的读取结果相同

I隔离性：事物的修改在最终提交之前对于其他事务是不可见的

D持久性：已提交事务的影响永远保存在数据库中，崩溃也不能丢失

原子性和持久性是由数据库恢复系统保证的；一致性和隔离性是由数据库并发控制保证的。

1. 故障

事务故障（程序运行错误）

系统故障（掉电关机）

介质故障（介质故障）

1. 恢复

事务故障：撤销 / 重做undo redo

系统故障：运行日志，检查点

介质故障：冗余备份，转储点

1. undo log & redo log

示例：

注意点：开头<START T>在一开始;结尾<COMMIT T>在OUTPUT全部结束以后

修改值<T, A, 8>等在WRITE语句时。

<ABORT T>指示某个事务T被终止，需要回滚

遇到action FLASHLOG，前一排要COMMIT

1. undo/redo logging

记录：<Ti, X, v, w> 事务T改变了数据库元素X的值，值从v变成了w

规则：日志先行。在修改磁盘上任何数据库元素X之前，由T做的更改的log必须出现在磁盘上。

策略：已提交的事物最早优先重做；未提交的事务最晚优先撤销

过程：

向后传递。（日志结束->开始，最近优先）构造已提交事务合集S，撤销未提交事务合集U；

向后传递。撤销挂起的事务，跟踪U中正在使用的事务撤销链

向前传递。（开始->日志结束，最早优先）重做S中的事务

checkpoint检查点：

保持数据库的一致性（完全检查点）。将脏数据写到磁盘。

缩短实例恢复时间（增量检查点）。恢复数据库时，可以从检查点开始分析

3.5.2 并发控制

1. 不一致性判断

丢失修改（同时修改）：两个程序修改，只保留了后一次

不能重复读（读时修改）：一个程序两次读之间，另一个程序修改了，使得两次读的结果不同

脏读（读取失败的修改）：一个程序修改，另一个程序读，此时第一个程序撤销修改，第二个程序再读后的数不一样）

幻影读（读时插入）：当同一查询在不同时间产生不同的行集时，在事务内读到的结果不一致的问题。

解决方案：并发控制，封锁

可串行性调度：相邻的微观操作可以互换顺序

冲突串行化（冲突序列化）：一个schedule，可以通过将其不冲突的操作互换，把它变成事务的串行序列。且这两个序列，冲突等价。

优先级图：

根据时间表，画优先级图。步骤如下

1. 根据数字角标确定有几个事务，根据英文字母确定有几种资源

1. 对每一种资源：若不同事务对其操作中有W，那这两个事物有先后关系。

1. 封锁类型

封锁粒度：行级锁和表级锁（锁开销和并发程度的权衡）

排他锁X和共享锁S：都可以读，但不能写

更新锁U：初始初始读，以后可以写

意向锁IX/IS：事务在加S之前，要获得IS或者更强的锁；事务在加X之前，要获得IX

1. 死锁

原因：资源竞争和进程间推进顺序非法

解决：

• • 排查，kiil spid 杀死进程，破坏死锁的必要条件4：循环等待
  • 设定锁请求超时，破坏死锁的必要条件2：请求与保持条件

SET LOCK_TIMEOUT timeout_period        // 默认没有超时期限timeout_period==-1
SELECT @@LOCK_TIMEOUT                 // 查看

• • 锁监视器线程执行死锁检查，检查到死锁后，回滚开销最小的事务

1. 封锁协议：

两段锁协议：同一事务对任何数据进行读写之前必须对该数据加锁；在释放一个封锁之后，事务不再申请和获得其他任何锁。

三级封锁协议：

一级：事务写开始前加X锁，事务结束才释放（不能处理可重复读和脏读）二级：事务读数据之前对其加S锁，读完释放S锁 + 一级（不能处理可重复读）

三级：一级+事务读数据之前对其加S锁，事务结束才释放

两段锁协议：生长阶段（加锁）和衰退阶段（解封）。事务在commit或rollback后进入衰退阶段

基于时间戳的并发控制：先执行与后执行 | 托马斯规则

基于有效性确认的并发控制：