---
title: "03-数据库"
created: 2026-01-11
tags:
  - 博客

---
# 数据库

## **一、数据库系统概述**

### **1.1 数据库系统阶段数据管理的特点 (P5)**

| **特点** | **说明** |
| --- | --- |
| **结构化** | 数据及其联系的集合，整体结构化 |
| **共享性高、冗余度低** | 多用户、多应用共享数据 |
| **数据独立性高** | 物理独立性和逻辑独立性 |
| **统一管理和控制** | 由DBMS统一管理，包括安全性、完整性、并发控制、恢复等 |

### **1.2 数据库系统的组成 (P7)**

- **四部分：** 硬件系统、软件系统（DBMS、OS等）、数据库（DB）、数据库用户（DBA、开发者、终端用户）。

![[image-8a8d9b23.png]]

## **二、三级模式与二级映像(P11)**

### **2.1 三级模式结构**

![[image-a2d95f17.png]]

| **模式** | **数量** | **说明** |
| --- | --- | --- |
| **外模式** | 可有多个 | 用户视图，描述用户看到的数据结构 |
| **模式** | 只有一个 | 全局逻辑结构，是所有用户的公共数据视图 |
| **内模式** | 只有一个 | 存储模式，描述数据的物理结构和存储方式 |

### **2.2 二级映像与数据独立性**

| **映像** | **作用** | **独立性类型** |
| --- | --- | --- |
| **外模式/模式映像** | 定义外模式与模式之间的对应关系 | **逻辑独立性** |
| **模式/内模式映像** | 定义模式与内模式之间的对应关系 | **物理独立性** |

核心：通过两层映像实现了数据与程序之间的解耦

> **请简述数据库系统的三级模式结构及其如何实现数据独立性。**
>
> - **三级模式**：外模式（用户视图）、模式（全局逻辑结构）、内模式（物理存储结构）
> - **数据独立性实现**：
>   - 当模式改变时，只需修改外模式/模式映像，外模式不变 → **逻辑独立性**
>   - 当内模式改变时，只需修改模式/内模式映像，模式不变 → **物理独立性**

## **三、实体联系与数据模型 (P14)**

### **3.1 两个实体之间的联系**

- **一对一 (1:1)**：如 班长与班级。
- **一对多 (1:n)**：如 班级与学生。
- **多对多 (m:n)**：如 学生与课程（需通过中间表实现）。

![[image-5c0770db.png]]

## **四、关系的完整性 (P32)**

|  |  |  |
| --- | --- | --- |
| **实体完整性** | 主码的值不能为空或部分为空 | 主码 |
| **参照完整性** | 外码值必须是主表中存在的值或为NULL | 表间关系 |
| **用户自定义完整性** | 反映具体应用的语义约束条件 | 具体应用 |

- **实体完整性：** 主码（Primary Key）不能为空，且不能重复。
- **参照完整性：** 外码（Foreign Key）要么取空值，要么必须等于主表中存在的主码值。
- **用户自定义完整性：** 针对具体业务的约束（如：年龄必须>0，性别只能是男/女）。

## **五、SQL数据定义语言**

### **5.1 数据库操作 (P60)**

```
-- 创建数据库
CREATE DATABASE 数据库名;

-- 修改数据库
ALTER DATABASE 数据库名 ...;

-- 删除数据库
DROP DATABASE 数据库名;
```

### **5.2 约束类型**

#### **唯一约束 UNIQUE (P70)**

| **特性** | **说明** |
| --- | --- |
| 允许NULL | 最多只允许出现**一个**NULL值 |
| 约束类型 | 既可作为表约束，又可作为列约束 |
| 数量限制 | 一个表可以有**多个**UNIQUE约束 |
| 字段范围 | 可定义在多个字段上 |

#### **主码约束 PRIMARY KEY (P71)**

| **特性** | **说明** |
| --- | --- |
| 作用 | 唯一标识每一行记录 |
| NULL值 | **不能为NULL**，不能重复 |
| 数量限制 | 一个表**只能定义一个**PRIMARY KEY |
| 语法 | `<字段名> <数据类型> PRIMARY KEY` |

#### **外码约束 FOREIGN KEY (P72)**

![[image-c27fba8c.png]]

## **六、SQL数据操作语言**

### **6.1 数据操作语句**

- **添加数据 (P79):**

  ```
  INSERT INTO 表名(字段1, 字段2...) VALUES(值1, 值2...);
  -- 只有当插入全部字段且顺序一致时，才可省略字段名
  ```
- **修改数据 (P80):**

  ```
  UPDATE 表名 SET 字段名 = 新值 WHERE 条件;
  -- ⚠️注意：不加WHERE会修改全表！
  ```
- **删除数据 (P81):**

  ```
  DELETE FROM 表名 WHERE 条件;
  -- ⚠️注意：不加WHERE会清空全表！
  ```

## **七、SQL查询语句 (P85-95)**

### **7.1 查询语法结构**

```sql
SELECT [DISTINCT] 字段列表
FROM 表名
[WHERE 条件]
[GROUP BY 分组字段]
[HAVING 分组后条件]    -- 必须在GROUP BY之后
[ORDER BY 排序字段 [ASC|DESC]];
```

### **7.2 条件查询示例 (P88)**

- **比较运算：** `score >= 90`
- **范围查询：** `BETWEEN 30 AND 40` (包含边界)
- **集合查询：** `NOT IN ('c4', 'c6')`
- **模糊查询：** `LIKE '%程序%'` (%代表任意个字符，\_代表一个字符)
  - *例子：* `SELECT cno, cn, ct FROM c WHERE cn LIKE '%程序%';`

```sql
-- 比较运算符
-- 查询成绩在90分及以上的选课信息
SELECT * FROM sc WHERE score >= 90;

-- AND条件
-- 查询年龄在30~40岁的教师的教师号、姓名和职称
SELECT tno AS 教师号, tn AS 姓名, prof AS 职称
FROM t
WHERE age >= 30 AND age <= 40;

-- BETWEEN...AND
-- 查询课时在30~40课时的课程的课程号、课程名和课时
SELECT cno, cn, ct
FROM c
WHERE ct BETWEEN 30 AND 40;

-- NOT IN
-- 查询除课程号“c4" 和 ”c6“之外其他课程的选课信息，包括学号、课程号和成绩
SELECT sno, cno, score
FROM sc
WHERE cno NOT IN ('c4', 'c6');

-- LIKE模糊查询
-- 查询课程名中包含”程序“的课程的课程号、课程名、课时
SELECT cno AS 课程号, cn AS 课程名, ct AS 课时
FROM c
WHERE cn LIKE '%程序%';
```

### **7.3 分组查询示例 (P95)**

- **聚合函数：** `COUNT()`, `SUM()`, `AVG()`, `MAX()`, `MIN()`
- **分组：** `GROUP BY`
- **分组后筛选：** `HAVING`
  - *重点区别：* `WHERE` 筛选行（分组前）；`HAVING` 筛选组（分组后）。
  - HAVING子句必须跟在GROUP BY之后。

```sql
-- 统计每门课程选课人数
-- 查询选课表sc中每门课程的课程号及其选课人数
SELECT cno AS 课程号, COUNT(*) AS 选课人数
FROM sc
GROUP BY cno;

-- HAVING过滤分组
-- 查询选修三门以上（含三门）课程的学生的学号和选课门数
SELECT sno AS 学号, COUNT(*) AS 选课门数
FROM sc
GROUP BY sno
HAVING COUNT(*) >= 3;

-- 排序
-- ORDER BY 字段名 [ASC|DESC] (DESC为降序)
-- 查询学号为”s2"的学生的选课信息，要求显示学号、课程号和成绩，并且按成绩降序排列
SELECT sno, cno, score
FROM sc
WHERE sno = 's2'
ORDER BY score DESC;
```

## **八、连接查询与子查询**

### **8.1 连接查询 vs 子查询**

> 请简述SQL查询中子查询与连接查询的主要区别
>
> 子查询是嵌套的SELECT语句，从内向外逐层执行，结果通常来自一个表，适用于条件值来自其他表的场景，可能执行较慢。
>
> 连接查询是多表通过JOIN连接，同时处理多表，结果可来自多个表，适用于需要显示多表字段的场景，通常性能更高。

| **比较项** | **子查询** | **连接查询** |
| --- | --- | --- |
| **结构** | 嵌套的SELECT语句 | 多表通过JOIN连接 |
| **执行方式** | 从内向外逐层执行 | 同时处理多表 |
| **结果来源** | 通常来自一个表 | 可来自多个表 |
| **适用场景** | 条件值来自其他表 | 需要显示多表字段 |
| **性能** | 可能较慢（多次执行） | 通常更高效 |

![[image-936c093d.png]]

### **8.2 INNER JOIN vs LEFT JOIN**

![[image-59d30bcd.png]]

| **类型** | **说明** | **结果** |
| --- | --- | --- |
| **INNER JOIN** | 内连接 | 只返回两表中**匹配**的记录 |
| **LEFT JOIN** | 左外连接 | 返回左表**所有**记录，右表无匹配则为NULL |

**示例**：

```sql
-- INNER JOIN：只显示有选课记录的学生
SELECT s.sno, s.sn, sc.cno
FROM s INNER JOIN sc ON s.sno = sc.sno;

-- LEFT JOIN：显示所有学生，没选课的也显示（课程号为NULL）
SELECT s.sno, s.sn, sc.cno
FROM s LEFT JOIN sc ON s.sno = sc.sno;
```

> 请列举并简要说明 INNER JOIN和 LEFT JOIN的区别
>
> INNER JOIN是内连接，只返回两表中匹配的记录，不匹配的记录不显示。
>
> LEFT JOIN是左外连接，返回左表所有记录，如果右表没有匹配的记录则显示为NULL。

## **九、视图 (P115-122)**

### **9.1 视图的创建与使用**

```
-- 创建视图（需要CREATE VIEW权限和相关SELECT权限）
CREATE VIEW s_view
AS SELECT * FROM s
WHERE dept = '信息学院';

-- 带检查选项的视图
CREATE VIEW view_name
AS SELECT ...
WITH CHECK OPTION;  -- 更新数据时检查是否满足视图条件
```

### **9.2 视图的更新**

- 可使用 **INSERT、UPDATE、DELETE** 语句更新视图数据
- `WITH CHECK OPTION` 参数会限制更新操作必须满足视图定义条件

> 请简述视图与数据表的主要区别：
>
> 数据表是实际存储数据的物理结构，占用存储空间，存储真实数据，可直接修改。
>
> 视图是虚拟表，只存储查询定义的SQL语句，不存储实际数据，数据动态从基表获取，更新有限制条件。
>
> 选择建议：需要永久存储数据用表，需要简化查询、限制访问或提供不同视角时用视图。

| **比较项** | **数据表** | **视图** |
| --- | --- | --- |
| **本质** | 实际存储数据的物理结构 | 虚拟表，只存储查询定义 |
| **存储** | 占用物理存储空间 | 不存储数据，只存SQL语句 |
| **数据** | 真实数据 | 动态从基表获取 |
| **更新** | 直接修改 | 有限制条件 |
| **用途** | 永久存储数据 | 简化查询、控制访问权限 |

**选择建议**：

- 需要存储数据 → 表
- 简化复杂查询、限制数据访问、提供不同视角 → 视图

## **十、权限管理 (P147)**

### **10.1 权限授予 GRANT**

```
GRANT 权限名称 [(字段列表)]
ON 授权级别及对象
TO '用户名'@'主机信息'
[WITH GRANT OPTION];  -- 允许被授权者继续授权给其他用户
```

### **10.2 权限回收 REVOKE**

```dockerfile
REVOKE 权限名称
ON 授权级别及对象
FROM '用户名'@'主机信息';
```

> 请简述MySQL用户管理的基本操作包括哪些
>
> MySQL用户管理基本操作包括：使用CREATE USER创建用户，使用DROP USER删除用户，使用ALTER USER修改用户密码，使用GRANT授予权限，使用REVOKE回收权限，使用SHOW GRANTS查看用户权限。

| **操作** | **说明** |
| --- | --- |
| **创建用户** | `CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';` |
| **删除用户** | `DROP USER '用户名'@'主机';` |
| **修改密码** | `ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码';` |
| **授予权限** | `GRANT 权限 ON 对象 TO 用户;` |
| **回收权限** | `REVOKE 权限 ON 对象 FROM 用户;` |
| **查看权限** | `SHOW GRANTS FOR '用户名'@'主机';` |

## **十一、事务 (P156)**

### **11.1 事务的ACID特性**

- **A (Atomicity) 原子性：** 要么全做，要么全不做。
- **C (Consistency) 一致性：** 事务前后数据状态合法。
- **I (Isolation) 隔离性：** 并发事务互不干扰。
- **D (Durability) 持久性：** 提交后永久生效。
- *并发问题：* 丢失更新、脏读、不可重复读、幻读。

![[image-708b5cf5.png]]

### **11.2 并发问题**

| **问题** | **说明** |
| --- | --- |
| **丢失更新** | 两事务同时更新，一个覆盖另一个 |
| **脏读** | 读取到未提交的数据 |
| **不可重复读** | 同一事务内两次读取结果不同 |
| **幻读** | 同一事务内两次查询记录数不同 |

## **十二、数据库备份与恢复 (P173)**

### **12.1 备份类型**

| **备份类型** | **说明** |
| --- | --- |
| **完整备份** | 备份整个数据库 |
| **差异备份** | 备份上次完整备份后的所有变化 |
| **增量备份** | 备份上次备份后的变化 |

### **12.2 备份策略**

- **备份内容**：数据、日志、代码、服务器配置文件等
- **系统数据库**：修改后立即备份
- **用户数据库**：周期性备份

### **12.3 数据导入导出 (P184)**

```
# 使用mysqlimport导入文件
mysqlimport [选项] 数据库名 文件名
```

## **十三、数据库设计范式**

### **13.1 三大范式**

![[image-055d9575.png]]

- 1NF：属性不可分（原子性）。
- 2NF：消除了非主属性对码的**部分函数依赖**（即：非主属性必须完全依赖于主键）。
- **3NF定义：** 在2NF基础上，消除了非主属性对码的**传递函数依赖**。
  - *通俗解释：* 比如 `学号 -> 系名`，`系名 -> 系主任`。存在传递依赖 `学号 -> 系主任`。要达到3NF，必须把系相关信息拆分成单独的表。

#### 第二范式 (2NF)

| **项目** | **说明** |
| --- | --- |
| **定义** | 在1NF基础上，非主属性**完全函数依赖**于主码（消除部分依赖） |
| **解决问题** | 消除非主属性对主码的部分函数依赖 |
| **作用** | 减少数据冗余，避免更新异常 |

> 请简述第二范式（2NF）的定义及其解决的问题
>
> 定义：第二范式是指在满足1NF的基础上，非主属性完全函数依赖于主码，即消除非主属性对主码的部分函数依赖。
>
> 解决的问题：
>
> (1)消除非主属性对主码的部分函数依赖
>
> (2)减少数据冗余
>
> (3)避免插入异常、删除异常、更新异常

#### 第三范式 (3NF) (P217)

| **项目** | **说明** |
| --- | --- |
| **定义** | 在2NF基础上，非主属性**不传递依赖**于主码（消除传递依赖） |
| **条件** | 每个非主属性都直接依赖于主码，不存在 A→B→C 的传递依赖 |
| **作用** | 进一步减少数据冗余，提高数据一致性，便于维护 |

> 请简述第三范式（3NF）的定义及其作用
>
> 定义：在2NF基础上，消除非主属性对主码的传递函数依赖（即不存在 A→B→C 的传递依赖）
>
> 作用：
>
> （1）进一步减少数据冗余
>
> （2）提高数据一致性
>
> （3）便于数据维护

### **13.2 逻辑结构设计 (P234)**

**E-R图转换为关系模式的原则**：

- **实体转换为表：** 属性即列，标识符即主码。
- **1:1 联系：** 可以转换为独立的关系，也可以与任意一端实体对应的关系模式合并（通常合并到访问更频繁的那一端）。
- **1:n 联系：** 将“1”方的主码纳入“n”方作为外码。
  - 联系本身的属性也放在“n”方。
- **m:n 联系：** **必须**转换为一个**新的独立关系模式**（新表）。
  - 新表的属性 = 双方实体的主码 + 联系本身的属性。
  - 新表的主码 = 双方主码的组合。

## **十四、MySQL编程 (P257)**

### **14.1 注释方式**

```
-- 单行注释（注意--后有空格）
# 单行注释
/*
   多行注释
*/
```

### **14.2 变量类型**

| **变量类型** | **前缀** | **说明** |
| --- | --- | --- |
| **局部变量** | `@` | 需用DECLARE声明 |
| **系统变量** | `@@` | MySQL自动创建 |

- **用户变量/局部变量：**
  - `DECLARE` 定义局部变量，必须在 `BEGIN...END` 中。
  - 名字通常以 `@` 开头（用户会话变量）或无前缀（局部变量）。
- **系统变量：**
  - 前缀 `@@` (如 `@@version`, `@@identity`)。

###

## **十五、存储过程与触发器**

### **15.1 存储过程 (P280)**

```
-- 创建存储过程
CREATE PROCEDURE 过程名([参数列表])
BEGIN
    -- SQL语句
END;

-- 调用存储过程
CALL 过程名([参数]);
```

### **15.2 触发器 (P308)**

| **类型** | **执行时机** |
| --- | --- |
| **BEFORE** | 在INSERT/UPDATE/DELETE**之前**执行 |
| **AFTER** | 在INSERT/UPDATE/DELETE**之后**执行 |

```
CREATE TRIGGER 触发器名
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名 FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;
```

---

## **十六、Python数据库访问 (P323)**

- Python所有数据库接口程序遵守 **Python DB API** 规范
- MySQL常用连接库：`mysql-connector-python`、 `pymysql`

```python
import pymysql

# 连接数据库
conn = pymysql.connect(host='localhost', user='root',
                       password='pwd', database='db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM table_name")
results = cursor.fetchall()
conn.close()
```

---

## **十七、数据库系统 vs 文件系统**

> **请简述数据库系统与文件系统的主要区别**
>
> 文件系统中数据记录内有结构但整体无结构，共享性差、冗余度大，数据独立性差，由应用程序管理和控制数据。
>
> 数据库系统中数据整体结构化，共享性高、冗余度低，数据独立性高，由DBMS统一管理并提供安全性、完整性、并发控制等功能。

| **比较项** | **文件系统** | **数据库系统** |
| --- | --- | --- |
| **数据结构** | 记录内有结构，整体无结构 | 整体结构化 |
| **数据共享** | 共享性差，冗余度大 | 共享性高，冗余度低 |
| **数据独立性** | 独立性差 | 独立性高 |
| **数据管理** | 由应用程序管理 | 由DBMS统一管理 |
| **数据控制** | 应用程序控制 | DBMS提供安全性、完整性、并发控制 |