数据库系统概论期末复习笔记

参考视频： B站拯救者数据库速成。

参考书籍：《数据库系统概论第5版》王珊著。

Chapter 1：绪论

1.1 数据库的四个基本概念：

数据（data）：符号记录，数据的含义称为语义；
数据库（DataBase，DB）：长期存储在计算机内、有组织的可共享的大量数据的集合；
数据库管理系统（DataBase management System，DBMS）：位于用户和OS之间的一个基础软件；

包括几个功能：数据库定义语言DDL，数据库操纵语言DML，数据库的建立和维护等等。
数据库系统（DataBase System，DBS）：由DB、DBMS、应用程序和数据库管理员DBA组成的系统。

1.2 数据库管理的三个阶段：

人工管理阶段：没有OS，数据不保存，不共享，没有独立性，冗余度大；
文件系统阶段：有文件系统，数据保存，不共享，独立性差，冗余度大；
数据库系统阶段：数据结构化，共享性高，冗余度小，具有高度的物理独立性和一定的逻辑独立性。

1.3 数据模型：

包括两类，概念（信息）模型以及逻辑模型、物理模型。

概念模型：将现实世界的数据抽象建模。

实体（客观存在的东西），属性，码（唯一标识实体的属性集），实体型，实体集。

实体间的联系：一对一，一对多，多对多。

常用E-R模型。

逻辑模型：包括层次模型（树）、网状模型（图）、关系模型（表），面向对象模型等。

物理模型：数据的存储方式，存取方法。

1.4 数据库系统的三级模式结构

三级模式结构：外模式、模式和内模式。

模式：也称逻辑模式，所有用户的公共逻辑视图。一个数据库只有一个模式。

外模式：也称用户模式，是数据库用户的数据视图，与某一应用有关。每个用户只能访问相应外模式的数据。

内模式：也称存储模式，一个数据库只有一个内模式。是数据物理结构和存储方式的描述。

1.5 数据库的二级映像与数据独立性

外模式/模式映像：一个模式可以有多个外模式，对于每个外模式有一个外模式/模式映像，

数据的逻辑独立性：当模式改变时，只需要改变外模式/模式映像，可以使外模式保持不变。

应用程序是根据外模式编写的，所以应用程序不变，保证了数据和程序的逻辑独立性。

模式/内模式映像：模式/内模式映像是唯一的。

数据的物理独立性：当存储结构改变时，只需要改变模式/内模式映像，可以使模式保持不变。

所以应用程序不变，保证了数据和程序的物理独立性。

Chapter 2：关系数据库

2.1 关系的相关概念

关系就是一张二维表。

域（domin）：属性的取值范围。域的不同取值个数称为基数。

笛卡尔积：几组域相乘，得到的每条记录叫做一个元组，每个元组由分量组成。

笛卡尔积的基数是各个域的基数相乘的结果。

关系是笛卡尔积的子集。

关系的目或度：n个域做笛卡尔积，目就为n。单元关系，n = 1。

候选码：候选码的值能唯一标识一个元组，它的子集不能。

主码：候选码中的一个。

主属性：候选码中的属性。非主属性：相反。

2.2 关系的完整性

实体完整性：主属性不能取空值；
参照完整性：若属性（组）F是关系R的外码，它与关系S的主码K相对应，则对于R中每个元组在F上的值：或者取空值，或者等于S中某个元组的主码值；
用户定义完整性：具体情况具体分析，比如用户姓名必须唯一，用户年龄必须大于0等等。

2.3 关系代数

传统的集合运算：二目运算，包括交、并、差和笛卡尔积。

对于并、差和交运算，两个运算的关系必须有相同的目，且相应属性取自同一个域。

对于笛卡尔积，两个n目和m目的关系运算后得到(n+m)目的关系。

专门的关系运算：包括选择、投影、连接、除运算。

象集：

解释：对于 x1 在 R 中的象集 Zx1，找到 R 中所有元组中 X 属性值 x1 对应的 Z 属性的值的集合，

就是{Z1,Z2,Z3}。

选择：行操作，针对筛选条件找出符合条件的元组（行）；

投影：列操作，找出对应属性的列；

连接：从两个关系的笛卡尔积中选取满足筛选条件的元组（行）。

等值连接：找到对应属性列的值相等的元组。

自然连接：一种特殊的等值连接，在等值连接的基础上删去重复列后的结果。从行和列两个角度考虑。

等值连接和自然连接的区别：

1）等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。

2）等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。

悬浮元组：自然连接时被舍弃的元组。

外连接：左、右两个关系的悬浮元组都保留的连接。

除运算：从行和列两个角度考虑。

设关系R除以关系S的结果为关系T，则T包含所有在R但不在S中的属性及其值，且T的元组与S的元组的所有组合都在R中。

Chapter 3：关系数据库标准语言SQL

3.1 数据定义

主要数据定义语句：

一个关系数据库管理系统的实例中可以建立多个数据库，一个数据库中可以建立多个模式，一个模式通常包括多个表、视图、索引等。

模式的定义与删除

定义模式实际上定义了一个命名空间。

在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。

--[] 表示可选参数，schema-name表示模式名，下文类似
-- 不加模式名则默认模式名为用户名user-name
CREATE SCHEMA [schema-name] AUTHORIZATION user-name; 
-- 创建模式的语句可以同时在模式中创建表，视图，授权的语句
-- | 表示在多个选择中任意选择其中一个
CREATE SCHEMA [schema-name] AUTHORIZATION user-name
[CREATE TABLE(...) | CREATE VIEW view-name AS ...| GRANT ... ON ... TO ...];
-- CASCADE表示级联，删除模式的同时删除它下面的所有数据库对象（表，视图等）
-- RESTRICT表示限制，如果该模式中已经定义了下属的数据库对象，则拒绝该DROP语句执行
-- 两者必选其一！
DROP SCHEMA schema-name <CASCADE | RESTRICT>;

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

基本表的定义：

CREATE TABLE table-name
(
    column1-name <type> [PRIMARY KEY],
    column2-name <type> [UNIQUE],
    column3-name <type> [NOT NULL], -- 添加列级完整性约束
    [PRIMARY KEY(column2-name),] -- 添加表级完整性约束
    [FOREIGN KEY(column2-name,column3-name) REFERENCES table2-name(column4-name)]
);
-- 示例：
CREATE TABLE Student
(
    Sno CHAR(11) PRIMARY KEY,
    Sname VARCHAR(20) NOT NULL,
    Sage SMALLINT,
    Sbirth DATE
);

CREATE TABLE Course
(
    Cno CHAR(4) PRIAMRY KEY,
    Cname VARCHAR(12) UNIQUE,
    Cpno CHAR(4),
    Ccredit SMALLINT,
    FOREIGN KEY(Cpno) REFERENCES Course(Cno) --外键，同一张表参照自己
);

每一个基本表都属于一个模式，定义基本表时可以显式加上模式名，

如CREATE TABLE schema-name.table-name();

也可以在定义模式的同时定义表，前面说过；

还有一种方式，设置好所属的模式，然后隐式的定义基本表。

基本表的修改：

ALTER TABLE table-name
[ADD [COLUMN] column-name <type> [NOT NULL]] -- 添加新列
[ADD <PRIMARY KEY(column-name)>] -- 添加表级完整性约束
[DROP [COLUMN] column-name [CASCADE | RESTRICT]] -- 删除列
[DROP CONSTRAINT constraint-name [CASCADE | RESTRICT]] -- 删除完整性约束
[ALTER COLUMN column-name <type>]; -- 修改列

基本表的删除：

-- CASCADE表示级联，删除表没有限制，同时删除它相关的依赖对象（视图等）
-- RESTRICT表示限制，该表不能被其他表的约束所引用（如外键等），不能有视图，触发器等等
-- 参数可加可不加，默认为RESTRICT
DROP TABLE table-name [CASCADE | RESTRICT];

索引的定义、修改与删除

索引的定义：

-- UNIQUE表示该索引的每一个索引值只对应唯一的记录
-- CLUSTER表示建立聚簇索引
-- 索引可以建立在表的一列或者多列之上
-- 每个列名后面可以加上可选参数次序，ASC表示升序，DESC表示降序
CREATE [UNIQUE] [CLUSTER] INDEX index-name
ON table-name(column1-name [ASC | DESC] [,column2-name, ...]);

索引的修改：

1 2	-- 对索引重命名 ALTER INDEX index-old_name RENAME TO index-new_name;

索引的删除：

1	DROP INDEX index-name;

3.2 数据查询

-- DISTINCT 去掉列表达式所得结果的重复行
-- ORDER BY每个列名后面可以加上可选参数次序，ASC表示升序，DESC表示降序
SELECT [ALL | DISTINCT] <column expression1> [,column expression2]
FROM table-name/view-name [AS alias | alias]
[WHERE <condition expression>]
[GROUP BY column-name [HAVING <condition expression>]]
[ORDER BY column-name [ASC | DESC]];
-- 示例：
SELECT Sname,Sdept,Grade
FROM Student AS st
WHERE Sdept NOT IN('CS','IC') AND Sname LIKE '_阳'
ORDER BY Grade DESC;
-- LIKE 表达式中，%表示任意长度字符，_表示任意单个字符，\表示转义；相应地有NOT LIKE

聚集函数：

COUNT(*) -- 统计元组个数
COUNT( [DISTINCT | ALL] column-name)
SUM( [DISTINCT | ALL] column-name)
AVG( [DISTINCT | ALL] column-name)
MAX( [DISTINCT | ALL] column-name)
MIN( [DISTINCT | ALL] column-name)
-- 示例：
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >= 3;

注意：聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句，不能用于WHERE中的条件表达式！

count(1)、count(*)与count(列名)的执行区别详解参考文章。

count(*)、count(1)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NUL。

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为NULL。

连接查询：

-- 示例：自然连接，可以是两张以上的表，表可以相同或者不同，还可以是嵌套查询
SELECT st.Sno,st.Sname
FROM Student st,SC sc
WHERE st.Sno = sc.Sno AND sc.Cno = '2' AND sc.Grade > 90;
-- 示例：外连接，这里的SQL标准写法和MySQL写法稍有不同
SELECT st.Sname,st.Sno
FROM Student st LEFT OUTER JOIN SC sc ON (st.Sno = sc.Sno);

带有ANY(SOME)或ALL谓词的子查询：

<comparison operator> ANY/ALL -- 比较运算符可以是>，<，=，!=之类的
-- 示例：
SELECT Sname,Sage
FROM Student
WHERE Sage > ANY(
SELECT Sage FROM Student WHERE Sdept='CS')
AND Sdept != 'CS';

带有EXISTS/NOT EXISTS谓词的子查询：结果返回逻辑值真或者假。

-- EXISTS若内层查询结果非空，则外层的WHERE子句返回真值；NOT EXISTS相反。
-- 示例：查询选修了全部课程的学生姓名；等价于查询这样的学生的姓名，没有一门课程是他不选修的
SELECT Sname
FROM Student
WHERE NOT EXISTS(
SELECT *
FROM Course
WHERE NOT EXISTS(
SELECT *
FROM SC
WHERE Sno = Student.Sno
AND Cno = Course.Cno));

集合查询：

-- 集合操作主要包括并操作UNION，交操作INTERSECT，差操作EXCEPT
-- 示例：
SELECT *
FROM Student
WHERE Sdept = 'CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage > 18;

3.3 数据更新

注意区别数据修改和基本表属性列的修改！

插入数据：

-- 插入一个元组，注意：MySQL中可以一次插入一条或多条数据，逗号分隔 values(...),(...);
INSERT INTO table-name [(column1-name[,column2-name])]
VALUES (value1[,value]);
-- 属性列是可选参数，不加默认为表的全部属性列
-- 示例：
INSERT INTO Student(Sname,Sage)
VALUES ('lihua',19);

插入子查询结果：可以批量生成数据

-- 示例：
INSERT INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;

修改数据：

UPDATE table-name
SET column1-name = <expression1>[,column2-name = <expression2>] ...
[WHERE <condition expression>]; -- 可选参数
-- 示例：
UPDATE SC
SET Grade = 0; -- SC中所有元组的Grade都置零

UPDATE SC
SET Grade = 0
WHERE Sno IN(
SELECT Sno
FROM Student
WHERE Sdept IN('CS','MA'));

删除数据：

DELETE
FROM table-name
[WHERE <condition expression>]; -- 可选参数
-- 示例：
DELETE
FROM Student
WHERE Sno = '1';

DELETE
FROM SC; -- 删除SC表中所有的记录

3.4 视图

视图是从一个或多个表中导出的虚表。数据库中只存放视图的定义，数据任存放在原来的基本表中。

建立视图：

视图可以建立在一个或多个基本表之上，也可以建立在一个或多个视图之上，或者视图和基本表之上。

行列子集视图：从单个基本表导出的，仅仅去掉某些行和列，保留主码。

-- WITH CHECK OPTION表示对视图进行增删改操作时保证操作的元组满足sub select中的条件表达式
-- 若省略视图的各个属性列名，则默认该视图由sub select中目标列组成
CREATE VIEW view-name [(column1-name[,column2-name])]
AS <sub select>
[WITH CHECK OPTION];
-- 示例：
CREATE VIEW view(Sno,Sname)
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept = 'IS'
WITH CHECK OPTION; -- 以后对view进行增删改操作时数据库系统会自动加上 Sdept = 'IS'

删除视图：

1	DROP VIEW view-name [CASCADE];

查询视图：和查询基本表一样。

对视图的查询，首先会检查视图是否存在。如果存在，则取出视图的定义，然后把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后执行查询。这一转换过程称为视图消解（view resolution）。

对于非行列子集视图的查询不一定能做正常的转换，如下图所示。

也可以用如下的基于派生表的查询来完成：

SELECT *
FROM (SELECT Sno,AVG(Grade)
     FROM SC
     GROUP BY Sno) AS TEMP(Sno,Gravg)
WHERE Gravg >= 90;

视图与派生表的区别：视图定义之后持久化保存在数据字典中，而派生表只是临时定义，类似局部变量。

更新视图：类似查询视图，对视图的更新操作也是通过视图消解转换为对基本表的更新。

更新视图的语句和更新基本表一样，如果定义视图时加上WITH CHECK OPTION可选参数，则视图消解时会自动附带定义视图时子查询中的条件表达式。

更新视图的限制：

一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新；
允许对行列子集视图进行更新；
对其他类型视图的更新不同系统有不同限制。

视图的作用：

Chapter 4：数据库安全性

4.1 数据存取权限

4.2 授予权限

-- WITH GRANT OPTION表示获得某种权限的用户还可以把这种权限再授予其他的用户，不加表示没有传播权限
-- 接收权限的用户可以是一个或多个具体用户，也可以是PUBLIC，即全体用户
GRANT <privilege>[,<privilege>]...
ON <object> object1-name[,<object> object2-name]...
TO user1-name[,user2-name]...
[WITH GRANT OPTION];
-- 示例：
-- 授予对Sno列的更新权限和对Student表的查询权限
-- 对属性列授予权限必须明确指出相应地属性列名
GRANT UPDATE(Sno),SELECT
ON TABLE Student
TO U2
WITH GRANT OPTION;
-- U2可以传播得到的权限
GRANT SELECT
ON TABLE Student
TO U3;

SQL标准允许具有WITH GRANT OPTION的用户传递具有的权限，但不允许循环授权，即被授权者不能将权限授回给授权者。

4.3 收回权限

-- REVOKE语句和GRANT语句类似，将TO换成FROM
-- CASCADE表示级联，将收回所有传播的权限
-- RESTRICT表示限制，该表不能被其他表的约束所引用（如外键等），不能有视图，触发器等等
REVOKE <privilege>[,<privilege>]...
ON <object> object1-name[,<object> object2-name]...
FROM user1-name[,user2-name]...
[CASCADE | RESTRICT];

4.4 数据库角色

数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合。

使用角色来简化管理数据库权限。

角色的创建：

1
2
3

CREATE ROLE role-name;
-- 示例：
CREATE ROLE R1;

给角色授权：类似给用户授权。

GRANT <privilege>[,<privilege>]...
ON <object> object-name
TO role1-name[,role2-name]...;
-- 示例：
GRANT SELECT,INSERT
ON TABLE Student
TO R1;

将一个或多个角色授予其他的角色或用户：

-- 被授予者获得的权限是授予它的全部角色所包含权限的总和
-- WITH ADMIN OPTION表示获得某种权限的角色或用户还可以把权限再授予其他的角色
GRANT role1-name[,role2-name]...
TO role3-name[,user1-name]...
[WITH ADMIN OPTION];
-- 示例：
GRANT R1
TO R2,u1
WITH ADMIN OPTION;

角色权限的收回：

REVOKE <privilege>[,<privilege]...
ON <object> object-name
FROM role1-name[,role2-name]...;
-- 示例：
REVOKE R1 
FROM U1; -- 一次性收回U1从R1处拿到的权限

REVOKE SELECT
ON TABLE Student
FROM R1;

Chapter 5：数据库完整性

5.1 实体完整性

实体完整性在定义基本表时用主键定义。可以定义为列级或表级约束条件。

每当用户对基本表插入记录或对主键列进行更新操作时，将检查主键值是否唯一，主键各属性是否为空。

5.2 参照完整性

参照完整性在定义基本表时用外键定义。

对被参照表和参照表进行增删改时有可能破坏参照完整性。

当发生不一致时，有三种处理策略：

拒绝（NO ACTION）执行
级联（CASCADE）操作
设置为空值（NULL）

5.3 用户定义完整性

用户定义完整性在定义基本表时定义属性上的约束条件。

主要包括：

列值非空（NOT NULL）
列值唯一（UNIQUE）
检查列值是否满足 CHECK 条件表达式

-- 示例：
CREATE TABLE Student
(
	Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2) CHECK(Ssex IN('男','女')),
    Sleader CHAR(9),
    FOREIGN KEY(Sleader) REFERENCES Student(Sno),
    CHECK(Sname NOT LIKE 'MS.%')
);

5.4 完整性约束命名子句

-- constraint condition 包括NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK 短语等
CONSTRAINT constraint-name <constraint condition>;
-- 示例：
CREATE TABLE Student
(
	Sno CHAR(9)
    CONSTRAINT C1 CHECK(Sno BETWEEN 10001 AND 20001),
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2)
    CONSTRAINT C2 CHECK(Ssex IN('男','女')),
    Sleader CHAR(9),
    CONSTRAINT C3 PRIMARY KEY(Sno)
);