元某人也元某人也

数据库原理及其应用

数据库原理：关系模型、范式与 SQL 应用笔记。

本文目录

数据库原理及其应用

一、数据库系统概述

考点一：数据库基本概念

数据库概念：

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

数据库的基本特点：

永久储存，有组织，可共享

数据库管理系统（DBMS）：

用户和操作系统之间的数据管理软件，属于计算机的基础软件，也是大型复杂的软件系统，主要功能包括以下几个方面：

  1. 数据定义功能(ddl 对数据库中的数据对象进行定义)
  2. 数据库操纵功能（增删改查）
  3. 数据组织，存储和管理功能
  4. 数据库的运行管理与控制功能（保证数据的安全性）
  5. 数据库建立和维护功能

数据库系统：

是指在计算机系统中引入数据库后的系统，是由数据库，数据库管理系统（及应用开发工具），应用程序和数据库管理员组成的管理，处理和维护数据的系统。

考点二：数据管理技术的发展

数据管理技术的发展阶段：

  1. 人工管理阶段：数据不保存，不共享，不具有独立性
  2. 文件系统阶段：可保存，共享性差，冗余大，独立性差
  3. 数据库系统阶段：能解决数据独立性问题，实现数据的统一管理，达到数据共享的目的

数据库系统特点：

  1. 数据结构化：采用一定的数据模型

  2. 数据的共享性比较高，冗余度低，易扩展：可被多个用户，多个应用共同使用

  3. 数据的独立性高：数据的独立性包括物理独立性和逻辑独立性。

    物理独立性是指用户的应用程序与存储在磁盘上的数据库中的数据是相互独立的。 逻辑独立性是指用户的应用程序与数据库的逻辑结构是相互独立的。

  4. 数据由 DBMS 统一管理和控制。

  5. 数据的最小存取单位是数据项。

考点三：数据库系统的结构

数据库的三级模式

从数据库管理系统角度看，数据库系统通常采用三级模式结果，分别包括：外模式，模式，内模式。

image-20251212164951465

  1. 外模式：

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

    外模式通常是模式的子集，一个数据库可以有多个外模式。

  2. 模式：

    模式也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有数据的公共数据视图，他是数据库系统模式结构的中间层，一个数据库只有一个模式。

  3. 内模式：

    内模式也称存储模式，一个数据库只有一个内模式，他是数据库物理结构和存储方式的描述，是数据在数据库内部的表示方法，比如记录的存储方式是对存储，还是按照某个属性的升降等。

数据库的两级映像

  1. 外模式/模式映像：

    定义了外模式与模式之间的对应关系，当模式改变时（如新增关系、属性，改变属性数据类型等），由数据库管理员对各个外模式/模式映像做相应的改变，可以使外模式保持不变，保证了数据与程序的逻辑独立性。

  2. 内模式/模式映像：

    定义了模式与内模式之间的对应关系，当数据库存储结构发生改变时，由数据库管理员对各个模式/内模式映像做相应的改变，可以使模式保持不变，保证了数据与程序的物理独立性。

考点四：信息的三个世界

信息的三个世界

  1. 现实世界：各种事物及其之间的联系，事物的发生变化过程。
  2. 信息世界：人们对客观事物的认识。选择后的抽象
  3. 计算机世界（也称数据世界）：进入计算机必须进行数据化

考点五：数据模型及其分类

数据模型是数据库的组织基础分为三个层次：概念数据模型，逻辑数据模型，物理数据模型。

概念数据模型

也称为信息模型，是对现实世界的 第一层抽象，按照用户的观点对数据进行建模，主要用于数据库的设计

  1. 实体：客观存在并可以相互区别的事物（学生）

  2. 属性：实体具有的某一个特性(学生学号)

  3. 联系：反映的是实体与实体之间的联系（老师和学生的关系，一对一，一对多，多对多）

    E_R图：实体用矩形来表示，属性用椭圆来表示，联系是用菱形来表示

逻辑数据模型和物理数据模型

逻辑数据模型包括网状模型，层次模型，关系模型，面向对象数据模型，对象关系数据模型，半结构化数据模型等。啊按照计算机系统的观点对数据建模，用于DBMS实现。

物理数据模型对数据最低层的抽象，描述数据在系统内部的表示方式和存取方法，是面向计算机系统的。

常见的的数据模型

  1. 层次模型：

    树形结构来表示各类实体及实体之间的关系，表示数据之间的从属关系

    使用的两个条件：

    1. 有且只有一个节点没有双亲节点，这个节点被称为根节点。
    2. 根节点之外的其他节点有且只有一个节点

  2. 网状模型：

    网状模型用网状结构来表示是实体与实体之间的联系

    使用的两个条件:

    1. 允许一个以上节点无双亲
    2. 一个节点可以有多个双亲

  3. 关系模型

    关系模型是建立在严格的数学概念的基础上的.无论是实体还是实体之间的联系都是关系来表示的,每一个关系的数据结构是一张二维表

二、关系数据库

考点一:关系的概念

域

域是一组具有相同数据类型的值的集合

笛卡尔积

将域中的元素进行组合

关系

笛卡尔积D1D2D3的子集叫做相应域的元关系，表示为R（D1，D2，D3）.R表示关系的名字，n是关系的目或者度（就是有多少行）

  1. 关系：一个关系对应一个二维表，
  2. 元组：二维表中一行是元组，
  3. 属性：一列是属性，
  4. 分量：元组中的一个取值是分量

码

  1. 候选码：关系中能唯一的表示一个元组的属性组
  2. 主码：若一个关系中有多个候选码，则其中一个为主码
  3. 主属性与非主属性：候选码属性为主属性，不包含任何候选码的属性为非主属性。

考点二：关系的数据模型

关系模型及其要素

关系数据模型是用二维表格结构来表示实体集及实体之间联系的模型，

关系数据模型三要素：

  1. 关系模型的数据结构：关系模型的数据结构是一个“二维表框架”组成的集合，每个二维表又可称为关系，所以关系模型是“关系框架“的集合
  2. 关系操作：主要包括数据更新、数据控制和数据查询、数据更新设计数据的插入、修改和删除等数据维护。
  3. 关系完整性约束： 1. 实体完整性：要求关系中不能出现相同的元组，且主属性的值不能为空。 2. 参照完整性：属性X在R1中不是主码，是R2的主码，则该属性被称为R1的外码，R1为参照关系，R2为被参照关系 3. 用户定义完整性：针对具体的关系数据库的约束条件。反应的是某一具体应用所涉及的数据必须满足的语义要求

关系的类型

关系的类型有三种：基本表，查询表，视图表

  1. 基本表：实际存在的表， 是实际存储数据的逻辑表示
  2. 查询表：查询结果对应的表（查询表是临时输出结果，退出窗口后就要重新写sql语句，不存储在数据库中。）
  3. 视图表：由基本表或者其他视图表导出来的表，是虚表（视图表是基于表的查询结果生成的虚拟结构，可以用指令调用，可重复使用但不会存储数据）

考点三：关系代数

关系代数

关系代数是对关系进行集合代数运算，是基于关系代数的而数据操作语言成为关系代数语言，简称关系代数。（元组进行的运算）

image-20251214101537793

专门的关系运算：

  1. 选择/限制：（σ）——查找结果是行

    可以选择列名或者列数来进行查询。

    image-20251214103502802

  2. 投影(II)——查找结果是列

    image-20251214103739302

  3. 连接——分为一般链接，等值连接和自然连接

    image-20251214104243475

  4. 除运算

    简单说，只要 S 有属性能和 R 匹配、且能通过象集+投影的逻辑计算，就满足除运算前提，这俩关系完全可以除 。

    这里是指只有A1的象集包含了S在（B，C）上所有投影，并不是只包含一个就可以的

    image-20251214105031462

三、关系数据库规范理论

考点一：规范化的概念

​ 一个关系模式是一个五元组,R(U,D,DOM,F),其中R为关系名,U为一组属性,属性组U中属性来自域D,属性到U的映射为DOM,属性U上的一组数据依赖F。但由于D和DOM对模式设计关系不大,因而一般都把关系模式看成一个个三元组R(U,F)。 ​ 从上式可以看出,数据依赖是关系模式的重要因素,

数据依赖：是一个关系内部属性与属性之间的一种约束关系。

  1. 函数依赖：设R（U）是属性集U上的关系模式，XY是U的子集。若对于R（U）的任意一个关系r，r中不可能存在两个元组在X上的属性相等，而在Y上面的属性值不相等，则称X函数确定Y或者Y函数依赖于X，记作->Y。

    • 平凡与不平凡函数依赖：X->Y，但是Y不属于X
    • 完全函数依赖与部分函数依赖：X的真子集可以完全映射Y为部分依赖，不能则为完全依赖image-20251215134918715
    • 传递函数依赖：X->Y(Y不属于X),Y不能决定X,Y->Z,Z不属于Y，则Z对X传递函数依赖

考点二：关系模式的规范化

image-20251215082206415

第一季的一级范式的关系模式通过模式分解可以转换为若干个高一级范式的关系模式的集合，这种过程就叫做规范化

模式分解：将一个 仅符合1NF的关系模式转换为两个符合2NF的关系

image-20251215140148928

1NF

image-20251215195627256

关系模式的R的所有属性都是不可分的基本数据项（不满足第一范式的数据库模式不能称为关系数据库）

一个属性下面只能有一个这样的列

2NF

image-20251215195555727

每一个非主属性都完全函数依赖与任何一个候选码

候选码和非主属性为完全函数依赖关系

关系模式R不属于2NF会产生以下问题

image-20251215135908357

image-20251215135836230

  1. 插入异常：需要同时满足两个候选码都不为空才可插入
  2. 删除异常：除主码外的候选码若只有一个，且被删除时主码也会被删除
  3. 数据冗余度大：
  4. 修改复杂：

3NF

image-20251215195524836

就是在原有的2NF关系模式中上每一个非主属性与候选码之间不存在传递依赖关系，将关系分解消除2NF的传递依赖即可

BCNF

image-20251215195444007意思就是X→Y中X包含所有候选码,少一个候选码或多一个非候选码都不是BCNF

每一个决定因素都必须包含码

规范化的目的

尽量消除插入、删除异常，修改复杂，数据冗余。

关系模式的规范化过程是通过对关系模式的分解来实现的,即把低一级的关系模式分解为若干个高一级的关系模式。

image-20251215200437417

四、关系数据库标准语言SQL

考点一：SQL概述

SQL的特点：

  1. 综合统一：SOL集数据定义语言DDL、数据操作语言DML、数据控制语言DCL的功能于一体,语言风格统一,可以独立完成数据库生命周期中的全部活动。（数据定义，数据操纵，数据控制）
  2. ==高度非过程化：SOL进行数据操作,只需提出"做什么",而无须指明"怎么做",因此不需要了解存储路径。==
  3. 面向集合的操作方式：SOL采用集合操作方式,不仅操作对象、查找结果可以是元元组的集合,而且一次插入、删除、更新操作的对象都可以是元组的集合。
  4. 以一种语法提供多种使用方法：SQL既是独立的而语言，又是嵌入的语言。
  5. 语言简洁，医学易用：核心功能只用了9个动词

考点二：数据的定义（DDL）

模式定义

模式定义语句格式：

1

CREATE SCHEMA< 模式名称>AUTHORIZATION 用户名

模式删除语句格式：

1

DROP SCHEMA <模式名><CASCADE|RESTRICT>

CASCADE(级联)

删除模式同时把该模式中的所有的数据库对象全部删除；

RESTRICT(限制)

如果该模式中定义了数据库对象（如表、视图），则拒绝该语句的执行。

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

  1. 定义基本表：

1
2
3
4
5

Create Table<表名>(<列名><数据类型>[列级完整性约束条件],
				 <列名><数据类型>[列级完整性约束条件],
				 ...
				 [,<表级完整性约束条件>]/*如果涉及多个属性列，则必须定义在表级上*/
				 ) ;

例子：建立一个选课表

1
2
3
4
5
6
7
8

Create Table SC (
		Sno Char (9),	
		Cno Char (4),
		Grade SmallInt,
		Primary Key(Sno,Cno),/*主码由两个属性构成,必须作为表级完整性定义*/
		Foreign Key(Sno) References Student(Sno),/*表级完整性约束条件,Sno为外码,被参照表是Student*/
		Foreign Key(Cno) Reference Course(Cno)
);
  1. 基本表
1
2
3
4
5
6

Alter Table<表名>
[Add[Column]<新列名><数据类型>[完整性约束]]
[Add<表级完整性约束>]
[Drop [Column] <列名>[Cascade | Restrict]]
[Drop Constraint<完整性约束名>[Restrict | Cascade]]
[Alter Column<列名><数据类型>];
  1. 删除基本表
1

[Drop Table <表名>[Cascade | Restrict]]

若选择Restrict,则该表的删除是有限制条件的,欲删除的基本表不能被其他表的约束所引用,不能有视图、触发器、存储过程或者函数等等,如果存在这些依赖关系,则此表不能删除。

索引的建立与删除

1
2
3
4

CREATE [UNIQUE] INDEX <索引名>
ON <表名> (<列名> [, <列名>] ...);

DROP INDEX <索引名> ON <表名>;
类型说明
聚集索引数据物理顺序与索引顺序一致（如 SQL Server 主键）
非聚集索引索引结构与数据存储分离
唯一索引UNIQUE，列值不重复

索引加快查询，但增加插入/更新/删除开销与存储空间。

考点三：数据操纵（DML）

插入

1
2
3
4
5
6

INSERT INTO <表名> [(<列名> [, <列名>] ...)]
VALUES (<常量> [, <常量>] ...);

-- 批量插入
INSERT INTO SC (Sno, Cno, Grade)
SELECT Sno, Cno, 0 FROM Student, Course WHERE ...;

修改

1
2
3

UPDATE <表名>
SET <列名> = <表达式> [, <列名> = <表达式> ...]
[WHERE <条件>];

删除

1

DELETE FROM <表名> [WHERE <条件>];

无 WHERE 则删除全表数据（表结构仍在）。

考点四：数据查询（SELECT）

基本结构

1
2
3
4
5

SELECT [ALL | DISTINCT] <目标列表达式> [, ...]
FROM <表名或视图列表>
[WHERE <条件>]
[GROUP BY <列名> [HAVING <条件>]]
[ORDER BY <列名> [ASC | DESC] [, ...]];
子句作用
SELECT投影，选列
FROM确定数据源
WHERE行级条件（分组前）
GROUP BY分组
HAVING组级条件（分组后）
ORDER BY排序

常用查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 条件
SELECT * FROM Student WHERE Sdept = 'CS' AND Sage > 20;

-- 模糊查询
SELECT Sname FROM Student WHERE Sname LIKE '张%';

-- 空值
SELECT * FROM Student WHERE Grade IS NULL;

-- 排序
SELECT Sno, Grade FROM SC ORDER BY Grade DESC;

-- 聚合
SELECT Cno, AVG(Grade), COUNT(*) FROM SC GROUP BY Cno HAVING AVG(Grade) > 80;

连接查询

类型说明
内连接 INNER JOIN只保留两表匹配行
左外连接 LEFT JOIN保留左表全部行
右外连接 RIGHT JOIN保留右表全部行
全外连接 FULL JOIN保留两表所有行（部分 DB 支持）
1
2
3
4

SELECT Student.Sname, Course.Cname, SC.Grade
FROM Student
INNER JOIN SC ON Student.Sno = SC.Sno
INNER JOIN Course ON SC.Cno = Course.Cno;

嵌套查询与子查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- IN
SELECT Sname FROM Student
WHERE Sno IN (SELECT Sno FROM SC WHERE Cno = '2');

-- 比较运算符 + 子查询（单行）
SELECT Sname, Sage FROM Student
WHERE Sage > (SELECT AVG(Sage) FROM Student);

-- EXISTS
SELECT Sname FROM Student s
WHERE EXISTS (SELECT * FROM SC WHERE SC.Sno = s.Sno AND Cno = '1');

集合运算

UNION（并）、INTERSECT（交）、EXCEPT（差）——要求参与运算的列数及类型兼容。

考点五：视图

1
2
3
4
5

CREATE VIEW <视图名> [(<列名> [, ...])]
AS <子查询>
[WITH CHECK OPTION];

DROP VIEW <视图名> [CASCADE | RESTRICT];
特点说明
虚表不单独存储数据，存定义
作用简化查询、保密（只暴露部分列/行）
更新部分视图可 INSERT/UPDATE/DELETE，受限制

考点六：数据控制（DCL）

1
2
3
4
5
6
7
8

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

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

常见权限：SELECT、INSERT、UPDATE、DELETE、ALL PRIVILEGES。

五、数据库事务与并发控制

考点一：事务的概念

事务（Transaction） 是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做。

ACID 性质

性质含义
原子性 A事务是不可分割的工作单位
一致性 C事务前后数据库处于一致状态
隔离性 I并发事务互不干扰（按隔离级别）
持久性 D提交后结果永久保存
1
2
3
4

BEGIN TRANSACTION;   -- 或 START TRANSACTION
-- SQL 操作
COMMIT;              -- 提交
ROLLBACK;            -- 回滚

考点二：并发问题

问题说明
丢失更新两事务先后写同一数据，后写覆盖先写
不可重复读同一事务内两次读同一行，结果不同（中间被别的事务修改）
读脏数据读到尚未提交的数据
幻影读同一条件两次查询，行数不同（别的事务插入/删除）

考点三：封锁与隔离级别

基本封锁

类型说明
排他锁 X写锁，不兼容其他 X/S
共享锁 S读锁，可与 S 共存，与 X 不兼容

SQL 标准隔离级别（由低到高）

级别丢失更新脏读不可重复读幻读
READ UNCOMMITTED可能可能可能可能
READ COMMITTED可能不会可能可能
REPEATABLE READ不会不会不会可能
SERIALIZABLE不会不会不会不会

MySQL InnoDB 默认 REPEATABLE READ；SQL Server 默认 READ COMMITTED。

六、数据库安全与完整性

考点一：完整性约束（与 SQL 对应）

类型SQL 实现示例
实体完整性PRIMARY KEY、UNIQUE、NOT NULL
参照完整性FOREIGN KEY ... REFERENCES ...
用户定义CHECK、DEFAULT、自定义触发器
1
2
3
4
5
6

CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) NOT NULL,
    Sage SMALLINT CHECK (Sage BETWEEN 15 AND 50),
    Sdept CHAR(20) DEFAULT 'CS'
);

考点二：数据库安全

机制说明
用户与权限GRANT / REVOKE
视图隐藏敏感列
审计记录操作日志
加密存储加密、传输 SSL

七、数据库设计

考点一：设计步骤

  flowchart LR
    A[需求分析] --> B[概念设计 E-R]
    B --> C[逻辑设计 关系模式]
    C --> D[物理设计 索引/存储]
    D --> E[实施与维护]

考点二：E-R 图转关系模式

联系类型转换规则
1:1可合并到任一方，或单独关系表含双方主码
1:n在 n 端实体加入 1 端主码作外码
m:n单独建关系表，主码为两端主码组合
多值属性单独建表
弱实体主码 = 依赖实体主码 + 部分码

考点三：设计评价

  • 用规范化（1NF→2NF→3NF→BCNF）减少冗余与异常
  • 必要时反规范化（适度冗余）换查询性能

考点速查

主题关键词
三级模式外模式 / 模式 / 内模式；两级映像 → 逻辑/物理独立
关系运算σ 选择行，π 投影列，⋈ 连接，÷ 除
范式1NF 原子；2NF 无部分依赖；3NF 无传递依赖；BCNF 决定因素含码
SQL 顺序SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
事务ACID；COMMIT / ROLLBACK
并发脏读、不可重复读、幻读；隔离级别
仅用于交流学习，记录学习心得