数据库原理期中总结

第1章 数据库基础概念

1.1 数据管理技术发展三阶段对比

方面	人工管理阶段	文件系统阶段	数据库系统阶段
数据管理者	应用程序	文件系统	DBMS
数据面向	某个应用程序	某个应用程序	整个组织/多个应用
数据共享	无共享，冗余极大	共享性差，冗余大	共享性高，冗余小
数据独立性	无独立性	独立性差	具有高度的物理独立性和逻辑独立性
数据控制	应用程序控制	应用程序控制	由DBMS统一管理和控制
典型特点	数据不保存 数据无结构 应用程序管理	数据可长期保存 文件形式多样化 记录内有结构	数据结构化 数据独立性高 数据由DBMS统一控制

• 人工管理阶段：数据需要应用程序自己管理，数据与程序一一对应
• 文件系统进步：数据可以长期保存在外部磁盘上
• 数据库系统核心优势：数据共享、减少冗余、数据独立性

1.2 数据库系统核心概念精讲

数据库(DB)

定义：长期存储在计算机内、有组织的、可共享的大量数据的集合
特点： 
  - 数据按一定的数据模型组织、描述和存储
  - 可为各种用户共享
  - 冗余度较小
  - 数据独立性较高
  - 易扩展

数据库管理系统(DBMS)

功能定位：位于用户与操作系统之间的一层数据管理软件
主要功能：
  1. 数据定义功能：提供DDL，定义数据库中的数据对象
  2. 数据组织、存储和管理：分类组织、存储和管理各种数据
  3. 数据操纵功能：提供DML，实现对数据库的基本操作
  4. 数据库的事务管理和运行管理：保证数据的安全性、完整性
  5. 数据库的建立和维护功能

数据库系统(DBS)

组成：数据库 + 数据库管理系统（及其应用开发工具）
       + 应用程序 + 数据库管理员

1.3 三级模式结构深度解析

模式（Schema）- 逻辑模式

定义：数据库中全体数据的逻辑结构和特征的描述
特点：
  - 是所有用户的公共数据视图
  - 一个数据库只有一个模式
  - 是数据库系统模式结构的中间层
  - 与数据的物理存储细节和硬件环境无关
  - 与具体的应用程序、开发工具及高级程序设计语言无关

外模式（External Schema）- 子模式/用户模式

定义：数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述
特点：
  - 通常是模式的子集
  - 一个数据库可以有多个外模式
  - 同一外模式可以为某一用户的多个应用系统所使用
  - 对每个外模式，数据库系统都有一个外模式/模式映像

内模式（Internal Schema）- 存储模式

定义：数据物理结构和存储方式的描述，是数据在数据库内部的表示方式
内容：
  - 记录的存储方式（顺序存储、堆存储、散列存储等）
  - 索引的组织方式
  - 数据是否压缩存储
  - 数据是否加密
  - 数据存储记录结构的规定

二级映像与数据独立性

1. 外模式/模式映像
   - 定义：同一个模式可以有任意多个外模式，对于每一个外模式，数据库系统都有一个外模式/模式映像
   - 作用：保证数据的逻辑独立性
   - 当模式改变时，数据库管理员修改有关的外模式/模式映像，使外模式保持不变

2. 模式/内模式映像
   - 定义：模式/内模式映像是唯一的，定义了数据全局逻辑结构与存储结构之间的对应关系
   - 作用：保证数据的物理独立性
   - 当数据库的存储结构改变了，数据库管理员修改模式/内模式映像，使模式保持不变

1.4 数据模型完整体系

概念模型（Conceptual Model）

主要概念：
  1. 实体（Entity）：客观存在并可相互区别的事物
  2. 属性（Attribute）：实体所具有的某一特性
  3. 码（Key）：唯一标识实体的属性集
  4. 实体型（Entity Type）：用实体名及其属性名集合来抽象和刻画同类实体
  5. 实体集（Entity Set）：同一类型实体的集合
  6. 联系（Relationship）：实体内部的联系和实体之间的联系

联系的类型：
  - 一对一联系（1:1）
  - 一对多联系（1:n）
  - 多对多联系（m:n）

逻辑模型（Logical Model）主要类型

1. 层次模型（Hierarchical Model）
   - 数据结构：树形结构
   - 特点：有且只有一个结点没有双亲结点（根结点），根以外的其他结点有且只有一个双亲结点

2. 网状模型（Network Model） 
   - 数据结构：网状结构
   - 特点：允许一个以上的结点无双亲，一个结点可以有多于一个的双亲

3. 关系模型（Relational Model）
   - 数据结构：二维表
   - 特点：建立在严格的数学概念的基础上，概念单一

数据模型的三要素

1. 数据结构：描述数据库的组成对象以及对象之间的联系
2. 数据操作：对数据库中各种对象（型）的实例（值）允许执行的操作的集合
3. 数据的完整性约束条件：一组完整性规则的集合

---

第2章 关系数据库（超详细版）

2.1 关系数据结构深度解析

域（Domain）

定义：一组具有相同数据类型的值的集合
例子：
  - 整数域
  - 实数域  
  - 长度小于25字节的字符串集合
  - {男，女}  -- 性别域

笛卡尔积（Cartesian Product）

定义：给定一组域D₁, D₂, …, Dₙ，这些域中可以有相同的，D₁, D₂, …, Dₙ的笛卡尔积为：
      D₁ × D₂ × … × Dₙ = {(d₁, d₂, …, dₙ) | dᵢ ∈ Dᵢ, i=1,2,…,n}
      
所有域的所有取值的一个组合，不能重复。

关系（Relation）

定义：D₁ × D₂ × … × Dₙ的子集叫作在域D₁, D₂, …, Dₙ上的关系，表示为：
      R(D₁, D₂, …, Dₙ)
      
其中：
  - R：关系名
  - n：关系的目或度（degree）

关系的各种码详解

1. 候选码（Candidate Key）
   - 定义：若关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，则该属性组为候选码

2. 主码（Primary Key）
   - 定义：若一个关系有多个候选码，则选定其中一个为主码

3. 主属性（Prime Attribute）与非主属性（Non-Prime Attribute）
   - 候选码的诸属性称为主属性
   - 不包含在任何候选码中的属性称为非主属性

4. 全码（All-Key）
   - 定义：关系模式的所有属性组是这个关系模式的候选码

5. 外码（Foreign Key）
   - 定义：设F是基本关系R的一个或一组属性，但不是关系R的码，Kₛ是基本关系S的主码。如果F与Kₛ相对应，则称F是R的外码

2.2 关系完整性约束完整说明

实体完整性（Entity Integrity）

规则：若属性A是基本关系R的主属性，则属性A不能取空值

说明：
  - 实体完整性规则是针对基本关系而言的
  - 现实世界中的实体是可区分的，即它们具有某种唯一性标识
  - 关系模型中以主码作为唯一性标识
  - 主码中的属性即主属性不能取空值

参照完整性（Referential Integrity）

规则：若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码Kₛ相对应（基本关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须为：
      - 或者取空值（F的每个属性值均为空值）
      - 或者等于S中某个元组的主码值

用户定义的完整性（User-defined Integrity）

定义：针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求

常见约束：
  - 列值非空（NOT NULL）
  - 列值唯一（UNIQUE） 
  - 检查列值是否满足一个布尔表达式（CHECK）

2.3 关系代数完整运算体系

传统的集合运算

1. 并（Union）
   R ∪ S = { t | t ∈ R ∨ t ∈ S }

2. 差（Difference）
   R - S = { t | t ∈ R ∧ t ∉ S }

3. 交（Intersection） 
   R ∩ S = { t | t ∈ R ∧ t ∈ S }

4. 笛卡尔积（Cartesian Product）
   R × S = { tᵣ tₛ | tᵣ ∈ R ∧ tₛ ∈ S }

专门的关系运算

选择（Selection）

定义：σ_F(R) = { t | t ∈ R ∧ F(t) = '真' }
例子：σ_{Sdept='CS'}(Student)  -- 查询计算机科学系的学生

投影（Projection）

定义：π_A(R) = { t[A] | t ∈ R }
例子：π_{Sname,Sdept}(Student)  -- 查询学生的姓名和所在系

连接（Join）

1. 等值连接（Equijoin）
   R ⋈_{AθB} S = { tᵣ tₛ | tᵣ ∈ R ∧ tₛ ∈ S ∧ tᵣ[A] θ tₛ[B] }

2. 自然连接（Natural Join）
   R ⋈ S = { tᵣ tₛ [U-B] | tᵣ ∈ R ∧ tₛ ∈ S ∧ tᵣ[B] = tₛ[B] }
   
3. 外连接（Outer Join）
   - 左外连接：保留左边关系中的所有元组
   - 右外连接：保留右边关系中的所有元组  
   - 全外连接：保留两个关系中的所有元组

除（Division）

定义：R ÷ S = { tᵣ[X] | tᵣ ∈ R ∧ π_Y(S) ⊆ Yₓ }
其中：Yₓ为x在R中的象集，x = tᵣ[X]

---

第3章 SQL语言（完整语法参考）

3.1 数据定义语言（DDL）完整语法

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名称
[CHARACTER SET 字符编码] 
[COLLATE 排序规则];

示例：
CREATE DATABASE IF NOT EXISTS yootk 
CHARACTER SET UTF8 
COLLATE utf8_general_ci;

创建表（完整约束语法）

CREATE TABLE <表名> (
    <列名> <数据类型> [列级完整性约束条件]
    [, <列名> <数据类型> [列级完整性约束条件]]
    ...
    [,表级完整性约束条件]
);

-- 完整示例
CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,                    -- 主码
    Sname VARCHAR(20) NOT NULL,                 -- 非空约束
    Ssex CHAR(2) CHECK (Ssex IN ('男','女')),   -- 检查约束
    Sage SMALLINT CHECK (Sage BETWEEN 15 AND 45),
    Sdept VARCHAR(20) DEFAULT '计算机系',       -- 默认值
    -- 表级完整性约束条件
    CONSTRAINT StudentKey PRIMARY KEY(Sno),
    CONSTRAINT C1 UNIQUE(Sname)
);

常用数据类型

-- 数值类型
INT, SMALLINT, BIGINT, NUMERIC(p,s), FLOAT, REAL

-- 字符串类型  
CHAR(n), VARCHAR(n), TEXT

-- 日期时间类型
DATE, TIME, DATETIME, TIMESTAMP

-- 二进制类型
BLOB, LONGBLOB

修改表结构

-- 增加列
ALTER TABLE Student ADD Semail VARCHAR(30);

-- 修改列数据类型
ALTER TABLE Student ALTER COLUMN Sage INT;

-- 删除列
ALTER TABLE Student DROP COLUMN Semail;

-- 增加约束
ALTER TABLE Student ADD UNIQUE(Sname);

-- 删除表
DROP TABLE Student [CASCADE | RESTRICT];

3.2 数据查询语言（DQL）完整语法

SELECT语句完整结构

SELECT [ALL|DISTINCT] <目标列表达式> [别名] [,<目标列表达式> [别名]]...
FROM <表名或视图名> [别名] [,<表名或视图名> [别名]]...
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]]
[LIMIT <行数1> [OFFSET <行数2>]];

单表查询详细示例

-- 1. 选择表中的若干列
SELECT Sno, Sname FROM Student;                    -- 查询指定列
SELECT * FROM Student;                             -- 查询全部列
SELECT Sname, 2023-Sage AS BirthYear FROM Student; -- 查询经过计算的值

-- 2. 选择表中的若干元组
SELECT DISTINCT Sdept FROM Student;                -- 消除重复行

-- 3. WHERE条件查询
SELECT Sname FROM Student WHERE Sdept='CS';        -- 比较
SELECT Sname FROM Student WHERE Sage BETWEEN 20 AND 23; -- 范围
SELECT Sname FROM Student WHERE Sdept IN ('CS','MA');   -- 集合
SELECT Sname FROM Student WHERE Sname LIKE '刘%';       -- 模糊匹配
SELECT Sno, Cno FROM SC WHERE Grade IS NULL;            -- 空值
SELECT Sname FROM Student WHERE Sdept='CS' AND Sage<20; -- 多重条件

-- 4. ORDER BY排序
SELECT Sno, Grade FROM SC WHERE Cno='1' ORDER BY Grade DESC;

-- 5. 聚集函数
SELECT COUNT(*) FROM Student;                      -- 统计元组个数
SELECT AVG(Grade) FROM SC WHERE Cno='1';           -- 计算平均值
SELECT MAX(Grade) FROM SC WHERE Cno='1';           -- 求最大值

-- 6. GROUP BY分组
SELECT Sdept, COUNT(*) FROM Student GROUP BY Sdept;
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>3;

连接查询详细示例

-- 等值连接
SELECT Student.Sno, Sname, Cno, Grade 
FROM Student, SC 
WHERE Student.Sno = SC.Sno;

-- 自然连接
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC 
WHERE Student.Sno = SC.Sno;

-- 自身连接（查询每一门课的间接先修课）
SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;

-- 外连接
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno = SC.Sno);

-- 多表连接
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

嵌套查询详细示例

-- 1. 带有IN谓词的子查询
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN (
    SELECT Sdept 
    FROM Student 
    WHERE Sname='刘晨'
);

-- 2. 带有比较运算符的子查询
SELECT Sno, Cno
FROM SC x
WHERE Grade >= (
    SELECT AVG(Grade) 
    FROM SC y 
    WHERE y.Sno = x.Sno
);

-- 3. 带有ANY(SOME)或ALL谓词的子查询
SELECT Sname, Sage
FROM Student
WHERE Sage < ANY (
    SELECT Sage
    FROM Student
    WHERE Sdept='CS'
) AND Sdept <> 'CS';

-- 4. 带有EXISTS谓词的子查询
SELECT Sname
FROM Student
WHERE EXISTS (
    SELECT *
    FROM SC
    WHERE Sno = Student.Sno AND Cno='1'
);

3.3 数据更新语言（DML）完整语法

插入数据

-- 插入元组
INSERT INTO Student (Sno, Sname, Ssex, Sdept, Sage)
VALUES ('201215128', '陈冬', '男', 'IS', 18);

-- 插入子查询结果
INSERT INTO Dept_age(Sdept, Avg_age)
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept;

修改数据

-- 修改某一个元组的值
UPDATE Student
SET Sage = 22
WHERE Sno = '201215121';

-- 修改多个元组的值
UPDATE Student
SET Sage = Sage + 1;

-- 带子查询的修改语句
UPDATE SC
SET Grade = 0
WHERE Sno IN (
    SELECT Sno
    FROM Student
    WHERE Sdept = 'CS'
);

删除数据

-- 删除某一个元组的值
DELETE FROM Student
WHERE Sno = '201215128';

-- 删除多个元组的值
DELETE FROM SC;

-- 带子查询的删除语句
DELETE FROM SC
WHERE Sno IN (
    SELECT Sno
    FROM Student
    WHERE Sdept='CS'
);

---

第4章 数据库安全（完整控制机制）

4.1 用户身份鉴别

-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 修改密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

-- 删除用户
DROP USER 'username'@'host';

4.2 授权与权限回收完整语法

GRANT授权语法

-- 授予表权限
GRANT <权限>[,<权限>]...
ON <对象类型> <对象名>
TO <用户>[,<用户>]...
[WITH GRANT OPTION];

-- 具体示例
GRANT SELECT, INSERT ON Student TO U1;
GRANT ALL PRIVILEGES ON Student TO U2 WITH GRANT OPTION;
GRANT UPDATE(Sno) ON Student TO U3;

REVOKE回收权限

REVOKE <权限>[,<权限>]...
ON <对象类型> <对象名>
FROM <用户>[,<用户>]... [CASCADE | RESTRICT];

-- 具体示例
REVOKE INSERT ON SC FROM U1;
REVOKE UPDATE(Sno) ON Student FROM U3;

4.3 数据库角色

-- 创建角色
CREATE ROLE R1;

-- 给角色授权
GRANT SELECT, UPDATE ON Student TO R1;

-- 将角色授予用户
GRANT R1 TO U1, U2, U3;

-- 角色权限的收回
REVOKE SELECT ON Student FROM R1;

---

第5章 数据库完整性（完整约束机制）

5.1 完整性约束命名子句

CREATE TABLE Student (
    Sno CHAR(9) CONSTRAINT C1 CHECK (Sno BETWEEN '10000000' AND '29999999'),
    Sname CHAR(20) CONSTRAINT C2 UNIQUE,
    Ssex CHAR(2) CONSTRAINT C3 CHECK (Ssex IN ('男','女')),
    Sage SMALLINT,
    Sdept CHAR(20),
    CONSTRAINT StudentKey PRIMARY KEY(Sno)
);

-- 修改表中的完整性限制
ALTER TABLE Student DROP CONSTRAINT C1;
ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN '900000' AND '999999');

5.2 触发器完整语法

创建触发器

CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
FOR EACH ROW
[WHEN <触发条件>]
<触发动作体>;

-- 具体示例
CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
FOR EACH ROW
WHEN (NEW.Grade >= 1.1 * OLD.Grade)
BEGIN
    INSERT INTO SC_U(Sno, Cno, OldGrade, NewGrade)
    VALUES(OLD.Sno, OLD.Cno, OLD.Grade, NEW.Grade);
END;

触发器执行顺序

1. 执行该表上的BEFORE触发器
2. 激活触发器的SQL语句
3. 执行该表上的AFTER触发器

---

SQL关键字快速参考

操作类型	主要关键字	用途
数据定义	CREATE, DROP, ALTER	定义和管理数据库对象
数据查询	SELECT, FROM, WHERE	数据检索
数据更新	INSERT, UPDATE, DELETE	数据修改
权限控制	GRANT, REVOKE	权限管理

关系代数运算速查

运算	符号	SQL等价	说明
选择	σ	WHERE	横向筛选元组
投影	π	SELECT	纵向选择属性
连接	⋈	JOIN	表关联
并	∪	UNION	集合并
差	-	EXCEPT	集合差
交	∩	INTERSECT	集合交
除	÷	复杂查询	包含全部

完整性约束速查

约束类型	实现方式	违反处理
实体完整性	PRIMARY KEY	拒绝操作
参照完整性	FOREIGN KEY	级联/置空/拒绝
用户定义	CHECK, NOT NULL	拒绝操作

---