数据库

一、数据库基本概念
二、数据库系统结构
三、关系模型与关系代数
四、SQL语言
五、数据库设计
六、事务与并发控制
七、数据库备份与恢复
八、NoSQL数据库
九、SQL Server 特有关注点

一、数据库基本概念

1. 核心概念区分

数据 (Data): 描述事物的符号记录,数据库存储的基本对象。
- 数据与其语义（数据的含义）是不可分的
数据库 (Database, DB): 长期存储在计算机内、有组织的、可共享的大量数据集合
- 数据按一定的数据模型组织、描述和存储
- 可为各种用户共享
- 冗余度较小
- 数据独立性较高
- 易扩展
数据库管理系统 (DBMS): 位于用户和操作系统之间的数据管理软件
数据库系统 (DBS): 由DB、DBMS、应用程序、DBA和用户组成的整体

关系链: DBMS 管理 DB，二者都是 DBS 的组成部分

2. 数据管理发展的三个阶段

阶段	硬件水平	软件水平	应用程序与数据的对应关系	处理方式	特点	存在问题
人工管理	无直接存取存储设备	没有操作系统	一个应用程序对应一个数据集	批处理	管理者是用户，数据不保存没有专门管理软件数据面向应用程序无结构	数据不共享，冗余大；不独立，
文件系统	磁盘，磁鼓	有文件系统	应用程序->文件管理系统->文件	批处理，联机实时处理	管理者是文件系统，数据可长期保存数据面向应用程序记录内有结构，整体无结构	共享性差，冗余度大，独立性差，不支持对文件的并发访问无安全控制功能
数据库系统	大容量磁盘，磁盘阵列	有数据库管理系统	应用程序->DBMS->数据库	联机实时处理，分布处理，批处理	数据结构化，共享性高，冗余度低，独立性高，统一管理控制	–

只有人工管理阶段没有专门的软件管理数据

新一代数据库管理系统

分布式数据库，空间数据库，NoSQL数据库，面向对象数据库，信息存储与检索，数据挖掘与数据仓库

3. 数据库系统的主要特点

数据结构化 (主要特征之一，与文件系统的本质区别)
- 数据的结构用数据模型描述，无需程序定义和解释
- 数据可以变长
- 数据的最小存取单位是数据项
数据的共享性高，冗余度低，易扩充
- 数据面向整个系统
- 共享好处：
  - 较少数据冗余，节约内存空间
  - 避免数据之间的不相容性与不一致性
  - 使系统易于扩充
数据独立性高 (包括物理独立性和逻辑独立性)
- 由二级映像功能保证
数据由DBMS统一管理和控制 (安全性检查、完整性检查、并发控制、恢复)

4. DBMS的主要功能

数据定义功能 (DDL)
数据组织、存储和管理
数据操纵功能 (DML)
数据库的事务管理和运行管理
数据库的建立和维护功能

5.数据库系统的组成

最基本组成部分:

数据库
数据库管理系统（核心）
应用系统（硬件及软件环境）
- 硬件：
  - 要有足够大的内存
  - 要有足够大的硬盘内存
  - 对硬件资源要求很高
- 软件：
  - DBMS
  - OS
  - 具有数据库访问接口的高级语言及其编程环境
  - 实用工具，一般是数据库厂商提供的

6.数据库系统中包含的人员

数据库管理员（DBA）：决定数据库信息内容、决定存储结构和存取策略、定义完整性约束、监控使用和运行、改进和重组
系统分析员：负责应用系统的需求分析和规范说明
数据库设计人员：负责数据库中数据的确定、数据库各级模式的设计
应用程序员：编写应用程序
最终用户：通过应用接口使用数据库

二、数据库系统结构

1. 三级模式结构 (核心!)

一个数据库只有一个模式

模式级别	别名	描述	数量	用户
外模式	子模式、用户模式	数据库用户能看见和使用的局部数据的逻辑结构和特征描述	多个	最终用户和应用程序员	保证数据库安全性
模式	逻辑模式	数据库中全体数据的逻辑结构和特征的描述	一个	DBA
内模式	存储模式	数据在数据库内部的表示方式 (存储方式、索引组织)	只有一个	系统程序员

2. 两级映像与数据独立性

映像		作用	保证的独立性
外模式/模式映像	每个外模式，数据库系统都有一个外模式/模式映像	当模式改变时，DBA修改此映像使外模式保持不变定义了该外模式与概念模式之间的对应关系	逻辑数据独立性 (应用程序不必修改)
模式/内模式映像	映像唯一	当内模式改变时，DBA修改此映像使模式保持不变	物理数据独立性 (应用程序和模式不必修改)

重要结论: 三级模式 + 两级映像 = 数据独立性

3. 视图 (View)

属于外模式
虚表，数据库中只存放视图的定义，不存放视图对应的数据。
作用:
- 使用视图可以简化查询语句的编写
- 提供一定程度的数据逻辑独立性
- 对机密数据提供安全保护
视图更新限制:
- 若视图来自多个基本表，一般不允许更新
- 若视图包含聚合函数、GROUP BY等，不允许更新
- 若视图更新违反基本表完整性约束，不允许更新
- 不一定能加快查询速度，有时可能降低查询效率。

三、关系模型与关系代数

0.数据模型

概念：使对现实世界数据特征的模拟和抽象，用来描述数据是如何组织、存储和操作的

条件：
- 能比较真实地模拟现实世界
- 容易为人所理解
- 便于在计算机上实现
分类
- 现实世界->概念模型，数据库设计人员完成(第一层抽象)
- 概念模型->逻辑模型，数据库设计人员完成（第二层抽象）
  - 既面向用户，又面向系统
- 逻辑模型->物理模型，DBMS完成（具体实现）

组成要素
- 数据的结构
- 数据完整性约束条件（比如年龄范围）
- 数据操作（查询修改等）

维度	概念模型	逻辑模型	物理模型
定义	从业务视角对现实世界的数据需求进行高度抽象，描述核心概念及其关系，独立于任何技术实现。	将概念模型转化为具体的数据结构（如关系型、层次型等），明确定义数据元素、关系及约束，但仍独立于具体DBMS。	描述数据在特定数据库管理系统（DBMS）中的具体实现，包括存储结构、索引、分区等物理细节。
主要组件	实体、属性、关系（通常用ER图表示，无具体数据类型或键细节）。	在关系模型中：表、字段、主键、外键、约束（如唯一、非空）、规范化结构等。	表空间、数据文件、索引、视图、存储过程、分区、存储参数（如页面大小、填充因子）等。
抽象级别	最高，关注业务概念与语义。	中等，关注数据结构与规则。	最低，关注物理存储与性能细节。
技术独立性	完全独立于DBMS和硬件。	独立于具体DBMS，但依赖于所选的数据模型类型（如关系模型、NoSQL模型）。	高度依赖于特定DBMS（如Oracle、MySQL、SQL Server）及硬件环境。
关注重点	业务需求、数据含义、实体间关系。	数据结构、数据完整性、减少冗余（如遵循规范化理论）。	性能、存储效率、访问速度、安全性、备份恢复等实施细节。
设计阶段	需求分析阶段，与用户沟通并达成共识。	系统设计模式阶段，将概念模型转化为逻辑结构。	数据库实现阶段，在逻辑模型基础上进行物理部署设计。

0.5.关系数据库

概念：建立在关系理论上的数据库，在关系数据库中，实体和实体之间的联系均以关系（表格）的形式进行描述
- 元组
- 属性
- 域
- 码
  - 外码
  - 主码取值唯一且不能为空，可由一个或多个属性组成，能唯一标识元组。

关系数据库系统采用关系模型作为数据的组织方式

关系操作是面向集合的操作。

0.75关系的性质

列是同质的：每一列中的分量是同一类型的数据，来自同一个域

不同的列可出自同一个域，但需给予不同的属性名

列的顺序无关紧要：列的次序可以任意交换

任意两个元组的候选码不能完全相同

行的顺序无关紧要：行的次序可以任意交换

分量必须取原子值：每一个分量都必须是不可分的数据项

1. 关系数据结构术语

术语	关系模型	一般表格
关系	一张二维表	表
元组	表中的一行	记录
码 (键)	唯一标识元组的属性(集)	关键字
域	属性的取值范围	数据类型

关系中的元组必须互不相同，不能全同。

2. 关系完整性约束 (重点!)

关系完整性约束是为保证数据库中数据的正确性和相容性，对关系模型提出的某种约束条件或规则。

外码注意事项:

外码不一定是本表的主属性
外码可以为空 (除非它也是本表的主属性)
外码的数据类型必须与被参照的主码相同

数据库完整性规则总览

完整性类型	核心目的	判断方法（如何满足/实现）	通俗解释与示例
实体完整性 (您提到的“主体完整性”)	确保表中的每一条记录（实体）都是唯一且可标识的。	1. 定义主键：为表指定一个主键。 2. 主键约束：主键字段的值必须满足： – 非空（NOT NULL） – 唯一（UNIQUE）	示例：学生表(学号(PK), 姓名, …)`学号`不能为空，且不能重复。
参照完整性 (引用完整性)	确保表与表之间引用关系的一致性，防止出现“孤立记录”或无效引用。	1. 定义外键：在从表（子表）中建立指向主表（父表）主键的外键。 2. 外键约束：外键字段的值必须满足以下之一： – 为空（NULL，如果允许） – 等于主表中某个已存在的主键值	示例：`选课表(学号(FK), 课程号(FK), 成绩)`。其中的`学号`必须在`学生表`中存在，`课程号`必须在`课程表`中存在。
域完整性	确保表中每个字段（列）的值都符合其定义的数据类型、格式和有效范围。	1. 选择合适的数据类型：如整数(INT)、字符(VARCHAR)、日期(DATE)。 2. 使用约束： – 非空（NOT NULL） – 检查约束（CHECK） – 默认值（DEFAULT） – 数据类型自带的格式（如日期格式）	比如“年龄”字段只能填数字，“性别”字段只能填‘男’或‘女’。示例：`员工表(性别 CHAR(2) CHECK (性别 IN (‘男’， ‘女’))，年龄 INT CHECK (年龄 >= 18))`。
用户自定义完整性	根据具体业务需求制定的特殊规则，是上述三种完整性无法覆盖的。	1. 业务逻辑检查：在应用程序代码中实现。 2. 数据库高级对象：使用触发器、存储过程或复杂的检查约束来实现。	示例1：`订单表`的“发货日期”必须晚于“下单日期”。（可用CHECK约束）示例2：VIP客户的订单金额必须超过1000元。（可能需要触发器判断客户等级和金额）

3. 关系代数运算

(1) 传统集合运算 (要求关系R和S具有相同的目，对应属性取自同一个域)

并 (R∪S): 所有属于R或属于S的元组
差 (R-S): 所有属于R但不属于S的元组
交 (R∩S): 既属于R又属于S的元组
笛卡尔积 (R×S): R的每个元组与S的每个元组连接

(2) 专门的关系运算

运算	符号	说明	SQL对应
选择	σ	从关系中选择满足条件的元组	WHERE
投影	π	从关系中选择若干属性列	SELECT
连接	⋈	从两个关系的笛卡尔积中选取满足条件的元组	JOIN

连接类型:

等值连接: 连接条件为相等比较
自然连接: 特殊的等值连接，自动比较所有同名属性，并去掉重复列
外连接: 保留悬浮元组 (左外、右外、全外)

重要考点: 自然连接的两个关系必须有同名属性

4. 函数依赖与范式

概念
- 设X.Y是关系R的两个属性集合，当任何时刻R中的任意两个元组中的X属性值相同时，则它们的Y属性值也相同，则称X函数决定Y，或Y函数依赖于X，记为X一Y。

(1) 函数依赖类型

完全函数依赖: 如果X→Y，且对于X的任何真子集X’，都不满足X’→Y
部分函数依赖: 如果X→Y，但Y不完全依赖于X，即存在X的真子集X’满足X’→Y
传递函数依赖: 如果X→Y，Y→Z，且Y⊈X，Z⊈Y，则Z传递依赖于X
平凡函数依赖：当关系中属性集合Y是属性集合X的子集时(Y<X)，存在函数依赖X→Y即一组属性函数决定它的所有子集，
非平凡函数依赖：当关系中属性集合Y不是属性集合X的子集时，存在函数依赖X→Y，

(2) 规范化范式 (重点!)

范式	要求	解决的问题	问题
1NF	属性不可再分 (原子性)	最基本要求	存在插入异常、删除异常、数据冗余度大、修改复杂
2NF	在1NF基础上，消除非主属性对码的部分函数依赖	部分依赖导致的异常
3NF	在2NF基础上，消除非主属性对码的传递函数依赖	传递依赖导致的异常
BCNF	在3NF基础上，消除主属性对码的部分和传递函数依赖	更严格的3NF

范式判断流程:

找出所有候选码
确定主属性和非主属性
检查是否存在部分依赖 (判断2NF)
检查是否存在传递依赖 (判断3NF)

范式与异常关系:

1NF: 可能存在插入、删除、更新异常
2NF: 消除了部分依赖，但仍可能有传递依赖导致的异常
3NF: 消除了传递依赖，但仍可能存在少量异常
3NF不一定消除所有插入和删除异常

四、SQL语言

0.特点

综合统一
高度非过程化
面向集合的操作方式
语言简捷，易学易用
以同一种语法结构提供两种使用方式：独立和嵌入

0.5.数据类型

数值类型
- 准确型：指在计算机中能够精确存储的数据
- 近似型：指近似的数值数据类型
文本类型
- 普通字符编码
- Unicode字符编码
日期时间类型
二进制类型

1. 数据定义语言 (DDL)

-- 创建表
CREATE TABLE Student (
    s_id CHAR(10) PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    sex CHAR(2),
    birthday DATE,
    sdepartment VARCHAR(30)
);

-- 创建带外键的表
CREATE TABLE Grade (
    s_id CHAR(10),
    c_id INT,
    grade INT CHECK(grade BETWEEN 0 AND 100),
    PRIMARY KEY(s_id, c_id),
    FOREIGN KEY(s_id) REFERENCES Student(s_id),
    FOREIGN KEY(c_id) REFERENCES Course(c_id)
);

-- 创建表
CREATE TABLE Student (
    s_id CHAR(10) PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    sex CHAR(2),
    birthday DATE,
    sdepartment VARCHAR(30)
);

-- 创建带外键的表
CREATE TABLE Grade (
    s_id CHAR(10),
    c_id INT,
    grade INT CHECK(grade BETWEEN 0 AND 100),
    PRIMARY KEY(s_id, c_id),
    FOREIGN KEY(s_id) REFERENCES Student(s_id),
    FOREIGN KEY(c_id) REFERENCES Course(c_id)
);

重要约束:

约束类型	关键字	主要作用	是否允许NULL	是否允许多个
主键约束	`PRIMARY KEY` (唯一且非空)	唯一标识表中的每一行，确保实体完整性	不允许	每表一个
外键约束	`FOREIGN KEY`	确保引用完整性，限制只能引用另一个表中存在的值	允许（除非外码也是主属性）	每表多个
唯一约束	`UNIQUE`	确保列中的所有值都不同（唯一性）	允许（多个NULL）	每表多个
检查约束	`CHECK`	限制列中值的取值范围，确保域完整性	允许（除非同时有NOT NULL）	每列多个
默认约束	`DEFAULT`	当插入行未指定列值时，使用默认值	允许（本身就是处理NULL）	每列一个
非空约束	`NOT NULL`	确保列不能包含NULL值	不允许	-（属性级别）

2. 数据查询语言 (DQL)

(1) 基本查询

-- 选择所有列
SELECT * FROM Student;

-- 选择特定列
SELECT s_id, sname FROM Student;

-- 去重
SELECT DISTINCT sdepartment FROM Student;

-- 使用别名/友好列标题
SELECT sname AS 姓名, sdepartment AS 院系 FROM Student;

-- Top关键字 只显示前n条数据
Select Top 3 * from Student

-- 选择所有列
SELECT * FROM Student;

-- 选择特定列
SELECT s_id, sname FROM Student;

-- 去重
SELECT DISTINCT sdepartment FROM Student;

-- 使用别名/友好列标题
SELECT sname AS 姓名, sdepartment AS 院系 FROM Student;

-- Top关键字 只显示前n条数据
Select Top 3 * from Student

(2) 条件查询

-- WHERE 条件
SELECT * FROM Student WHERE sdepartment = '信息工程学院';

-- LIKE 模糊查询
SELECT * FROM Student WHERE sname LIKE '张%';  -- 姓张
SELECT * FROM Student WHERE sname=='_小%' or sname=='_大'; -- 第二个字为"小"或‘大’

-- NULL 判断
SELECT * FROM Student WHERE bonus IS NULL;
SELECT * FROM Student WHERE bonus IS NOT NULL;

-- And/Or
Select * from Student  where Ssex-='女' and Sage>21

-- Between and//Not Between and 使用同理
Select Sno.Sname,Ssex from Student wherre Sno between 'J2016001' and 'J2016004'-- 要比较的值是介于某个范围而且包含边界值

-- In关键字
Select * from Teacher where Tport in('教授','副教授')

-- DISTINCT表示检索时显示不重复的信息
Select DISTINCT TPort from Teacher

-- IS NULL//IS NOT NULL
Select * from spt_values where low IS NULL-- 执行此语句需要先转到Master数据库

-- Order by排序,可以使用逗号隔开多个列
Select * from Course Order by Cname Desc

-- 算术表达式
Select 3*5,SQRT(2)-- 3*5的结果以及根号2的值

-- WHERE 条件
SELECT * FROM Student WHERE sdepartment = '信息工程学院';

-- LIKE 模糊查询
SELECT * FROM Student WHERE sname LIKE '张%';  -- 姓张
SELECT * FROM Student WHERE sname=='_小%' or sname=='_大'; -- 第二个字为"小"或‘大’

-- NULL 判断
SELECT * FROM Student WHERE bonus IS NULL;
SELECT * FROM Student WHERE bonus IS NOT NULL;

-- And/Or
Select * from Student  where Ssex-='女' and Sage>21

-- Between and//Not Between and 使用同理
Select Sno.Sname,Ssex from Student wherre Sno between 'J2016001' and 'J2016004'-- 要比较的值是介于某个范围而且包含边界值

-- In关键字
Select * from Teacher where Tport in('教授','副教授')

-- DISTINCT表示检索时显示不重复的信息
Select DISTINCT TPort from Teacher

-- IS NULL//IS NOT NULL
Select * from spt_values where low IS NULL-- 执行此语句需要先转到Master数据库

-- Order by排序,可以使用逗号隔开多个列
Select * from Course Order by Cname Desc

-- 算术表达式
Select 3*5,SQRT(2)-- 3*5的结果以及根号2的值

(3) 聚合函数与分组

COUNT()返回匹配指定条件的行数

COUNT(*)返回表中的记录数

COUNT(column_name)返回指定列的值的数目（NULL不计入）

-- 常用聚合函数
SELECT COUNT(*) FROM Student;  -- 计数
SELECT AVG(grade) FROM Grade;  -- 平均值
SELECT MAX(grade), MIN(grade) FROM Grade; -- 最大最小值
SELECT SUM(ccredit) FROM Course; -- 求和

-- GROUP BY 分组
SELECT sdepartment, COUNT(*) FROM Student GROUP BY sdepartment;-- 统计学生表中各系的学生人数

-- HAVING 分组后筛选
SELECT sdepartment, COUNT(*) 
FROM Student 
GROUP BY sdepartment 
HAVING COUNT(*) > 10;

-- 常用聚合函数
SELECT COUNT(*) FROM Student;  -- 计数
SELECT AVG(grade) FROM Grade;  -- 平均值
SELECT MAX(grade), MIN(grade) FROM Grade; -- 最大最小值
SELECT SUM(ccredit) FROM Course; -- 求和

-- GROUP BY 分组
SELECT sdepartment, COUNT(*) FROM Student GROUP BY sdepartment;-- 统计学生表中各系的学生人数

-- HAVING 分组后筛选
SELECT sdepartment, COUNT(*) 
FROM Student 
GROUP BY sdepartment 
HAVING COUNT(*) > 10;

(4) 多表查询：连接查询

分类
- 内连接（最典型最常用，存在主外键关系）
- 外连接
  - 左连接
  - 右连接
  - 完全外连接 full join 或 full outer join
- 交叉连接

-- 内连接 (显式)
SELECT Student.s_id, sname, cname, grade
FROM Student 
INNER JOIN Grade ON Student.s_id = Grade.s_id
INNER JOIN Course ON Grade.c_id = Course.c_id;

-- 内连接 (隐式)
SELECT Student.s_id, sname, cname, grade
FROM Student, Grade, Course
WHERE Student.s_id = Grade.s_id 
  AND Grade.c_id = Course.c_id;

-- 左外连接
SELECT Student.s_id, sname, grade
FROM Student 
LEFT JOIN Grade ON Student.s_id = Grade.s_id;

-- 右外连接
SELECT Course.c_id, cname, grade
FROM Grade 
RIGHT JOIN Course ON Grade.c_id = Course.c_id;

-- 交叉连接
Select * from 
Student CROSSn JOIN Course

-- 内连接 (显式)
SELECT Student.s_id, sname, cname, grade
FROM Student 
INNER JOIN Grade ON Student.s_id = Grade.s_id
INNER JOIN Course ON Grade.c_id = Course.c_id;

-- 内连接 (隐式)
SELECT Student.s_id, sname, cname, grade
FROM Student, Grade, Course
WHERE Student.s_id = Grade.s_id 
  AND Grade.c_id = Course.c_id;

-- 左外连接
SELECT Student.s_id, sname, grade
FROM Student 
LEFT JOIN Grade ON Student.s_id = Grade.s_id;

-- 右外连接
SELECT Course.c_id, cname, grade
FROM Grade 
RIGHT JOIN Course ON Grade.c_id = Course.c_id;

-- 交叉连接
Select * from 
Student CROSSn JOIN Course

(5) 子查询(qian)

-- IN /Not In子查询(结果是包含零个值或多个值的列表)
SELECT sname FROM Student 
WHERE s_id IN (SELECT s_id FROM Grade WHERE grade > 90);

-- EXISTS 子查询(是否存在 结果是TRUE或FALSE)
SELECT cname FROM Course 
WHERE EXISTS (SELECT * FROM Grade WHERE Grade.c_id = Course.c_id);

-- 比较子查询（ANY）
SELECT sname, grade FROM Grade 
WHERE grade >= (SELECT AVG(grade) FROM Grade WHERE c_id = 1) 
  AND c_id = 1;
  
  
-- ALL
Select * from Teacher
Where Tsex ='男'and
Tage > ALL(Select Tage from Teacher where Tsex='女')--检索比任何一个女教师年龄都大的男教师

-- IN /Not In子查询(结果是包含零个值或多个值的列表)
SELECT sname FROM Student 
WHERE s_id IN (SELECT s_id FROM Grade WHERE grade > 90);

-- EXISTS 子查询(是否存在 结果是TRUE或FALSE)
SELECT cname FROM Course 
WHERE EXISTS (SELECT * FROM Grade WHERE Grade.c_id = Course.c_id);

-- 比较子查询（ANY）
SELECT sname, grade FROM Grade 
WHERE grade >= (SELECT AVG(grade) FROM Grade WHERE c_id = 1) 
  AND c_id = 1;
  
  
-- ALL
Select * from Teacher
Where Tsex ='男'and
Tage > ALL(Select Tage from Teacher where Tsex='女')--检索比任何一个女教师年龄都大的男教师

(6) 集合查询

SELECT语句必须拥有相同数量的列

-- UNION 并集 (自动去重)
SELECT s_id FROM Student WHERE sex = '男'
UNION
SELECT s_id FROM Grade WHERE grade > 90;

-- UNION ALL 并集 (不去重)
SELECT s_id FROM Student WHERE sex = '男'
UNION ALL
SELECT s_id FROM Grade WHERE grade > 90;

-- 交运算
Select Sno,Sname,Sage,Smajor from Student
where Smajor like'计算机%'
Intersect
Select Sno,Sname,Sage,Smajor from Student
Where Sage=21

-- 差运算
Select Sno,Sname,Sage,Smajor from Student
where Smajor like'计算机%'
Except
Select Sno,Sname,Sage,Smajor from Student
Where Sage=21

-- UNION 并集 (自动去重)
SELECT s_id FROM Student WHERE sex = '男'
UNION
SELECT s_id FROM Grade WHERE grade > 90;

-- UNION ALL 并集 (不去重)
SELECT s_id FROM Student WHERE sex = '男'
UNION ALL
SELECT s_id FROM Grade WHERE grade > 90;

-- 交运算
Select Sno,Sname,Sage,Smajor from Student
where Smajor like'计算机%'
Intersect
Select Sno,Sname,Sage,Smajor from Student
Where Sage=21

-- 差运算
Select Sno,Sname,Sage,Smajor from Student
where Smajor like'计算机%'
Except
Select Sno,Sname,Sage,Smajor from Student
Where Sage=21

3. 数据操纵语言 (DML)

-- 插入数据
INSERT INTO Student VALUES ('2023001', '张三', '男', '2000-01-01', '计算机学院');

-- 插入部分数据
INSERT INTO Student(s_id, sname, sex) VALUES ('2023002', '李四', '女');

-- 更新数据
UPDATE Course SET chours = chours - 8 WHERE cname = '数据结构';

-- 删除数据
DELETE FROM Student WHERE s_id = '2023001';

-- 删除表约束
ALTER TABLE Student DROP CONSTRAINT CK_Stu_Age;

-- 插入数据
INSERT INTO Student VALUES ('2023001', '张三', '男', '2000-01-01', '计算机学院');

-- 插入部分数据
INSERT INTO Student(s_id, sname, sex) VALUES ('2023002', '李四', '女');

-- 更新数据
UPDATE Course SET chours = chours - 8 WHERE cname = '数据结构';

-- 删除数据
DELETE FROM Student WHERE s_id = '2023001';

-- 删除表约束
ALTER TABLE Student DROP CONSTRAINT CK_Stu_Age;

4. 视图

优点
- 视图隐藏了底层的表结构，简化了数据访问操作。
- 因为隐藏了底层的表结构，所以大大加强了安全性，用户只能看到视图提供的数据。
- 使用视图，方便了权限管理，让用户对视图有权限而不是对底层表有权限进一步加强了安全性。
- 视图提供了一个用户访问的接口，当底层表改变后，改变视图的语句来进行适应，使已经建立在这个视图上客户端程序不受影响。

-- 创建视图
CREATE VIEW V_CS_Students AS
SELECT * FROM Student WHERE sdepartment = '计算机科学学院';

-- 使用视图
SELECT * FROM V_CS_Students;

-- 删除视图
DROP VIEW V_CS_Students;

-- 创建视图
CREATE VIEW V_CS_Students AS
SELECT * FROM Student WHERE sdepartment = '计算机科学学院';

-- 使用视图
SELECT * FROM V_CS_Students;

-- 删除视图
DROP VIEW V_CS_Students;

5. 索引

分类
- 主键索引
- 唯一索引
- 聚集索引
- 非聚集索引

-- 创建索引
CREATE INDEX idx_sname ON Student(sname);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON Student(email);

-- 删除索引
DROP INDEX idx_sname ON Student;

-- 创建索引
CREATE INDEX idx_sname ON Student(sname);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON Student(email);

-- 删除索引
DROP INDEX idx_sname ON Student;

索引注意事项:

提高查询速度，但降低更新速度
一个表只能有一个聚集索引
经常在WHERE、JOIN、ORDER BY中使用的列适合建索引
更新频繁的表不宜建太多索引

6. 存储过程

-- 创建存储过程
CREATE PROCEDURE prcFindByName
    @name VARCHAR(20),
    @total_credit INT OUTPUT
AS
BEGIN
    SELECT @total_credit = SUM(ccredit)
    FROM Student s
    JOIN Grade g ON s.s_id = g.s_id
    JOIN Course c ON g.c_id = c.c_id
    WHERE s.sname = @name
    GROUP BY s.s_id;
END;

-- 执行存储过程
DECLARE @credit INT;
EXEC prcFindByName '张三', @credit OUTPUT;
PRINT @credit;

-- 创建存储过程
CREATE PROCEDURE prcFindByName
    @name VARCHAR(20),
    @total_credit INT OUTPUT
AS
BEGIN
    SELECT @total_credit = SUM(ccredit)
    FROM Student s
    JOIN Grade g ON s.s_id = g.s_id
    JOIN Course c ON g.c_id = c.c_id
    WHERE s.sname = @name
    GROUP BY s.s_id;
END;

-- 执行存储过程
DECLARE @credit INT;
EXEC prcFindByName '张三', @credit OUTPUT;
PRINT @credit;

7. 权限控制

-- 授予权限
GRANT SELECT, UPDATE ON Student TO user1;

-- 授予所有权限
GRANT ALL PRIVILEGES ON Student TO user1;

-- 收回权限
REVOKE UPDATE ON Student FROM user1;

-- 拒绝权限
DENY DELETE ON Student TO user1;

-- 授予权限
GRANT SELECT, UPDATE ON Student TO user1;

-- 授予所有权限
GRANT ALL PRIVILEGES ON Student TO user1;

-- 收回权限
REVOKE UPDATE ON Student FROM user1;

-- 拒绝权限
DENY DELETE ON Student TO user1;

权限控制三步:

创建登录名: CREATE LOGIN login1 WITH PASSWORD = '123456'
创建数据库用户: CREATE USER user1 FOR LOGIN login1
授予权限: GRANT SELECT ON Student TO user1

五、数据库设计

1. E-R模型

(1) E-R图基本元素

元素	表示	说明
实体	矩形	客观存在并可相互区别的事物
属性	椭圆	实体所具有的某一特性
联系	菱形	实体之间或实体内部的关系

(2) 联系类型

1:1 (一对一): 如班长-班级
1:n (一对多): 如班级-学生
m:n (多对多): 如学生-课程

(3) E-R图合并冲突

属性冲突: 同一属性在不同E-R图中类型、取值范围不同
命名冲突: 同名异义或异名同义
结构冲突: 同一对象在不同E-R图中抽象不同 (如一个作为实体，一个作为属性)

2. E-R图向关系模型的转换规则

情况	转换规则
一个实体型	转换为一个关系模式，属性→关系的属性，码→关系的码
1:1联系	可以转换为独立关系，也可以与任意一端实体合并
1:n联系	可以转换为独立关系，也可以与n端实体合并
m:n联系	必须转换为独立的关系模式，两端实体的码作为关系的组合码
三个及以上实体间的多元联系	转换为独立的关系模式，各实体的码组成关系的码或作为外码

重要考点: m:n联系必须单独转换为一个关系模式

3. 规范化设计实例

原关系模式: Student(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)

函数依赖:

学号 → 姓名, 年龄, 所在学院
所在学院 → 学院地点, 学院电话

问题: 存在传递依赖 (学号 → 所在学院 → 学院地点)

规范化到3NF:

学生(学号, 姓名, 年龄, 所在学院)
学院(学院, 地点, 电话)

六、事务与并发控制

1. 事务的特性 (ACID)

1）恢复和并发控制的基本单位

特性	描述
原子性	事务中的操作要么都做，要么都不做
一致性	事务执行的结果必须使数据库从一个一致性状态变到另一个一致性状态
隔离性	一个事务的执行不能被其他事务干扰
持久性	事务一旦提交，对数据库的改变就是永久的

2）事务的两种方式

事务的方式	事务处理模型	特点
显式事务	ISO事务处理模型	明尾暗头
	T-SQL事务处理模型	明头明尾
隐式事务

3）事务的特征

重要任务：保证事务的ACID特性
ACID被破坏的可能原因
- 多个事务并行运行时，不同事务的操作有交叉情况（并发控制）
- 事务在运行过程中被迫停止（数据恢复）

2. 并发操作

2.1可能带来的问题

问题	描述	示例
丢失修改	两个事务同时修改同一数据，后提交的覆盖了先提交的	T1和T2都读A=10，T1写A=5，T2写A=2，最后A=2
不可重复读	事务两次读取同一数据，结果不同	T1读A=10，T2修改A=20并提交，T1再读A=20
读脏数据	读取了未提交的数据，然后该事务被回滚	T1修改A=20(未提交)，T2读A=20，T1回滚，A恢复为10

3. 封锁机制

(1) 锁的类型

共享锁 (S锁/读锁): 事务T对数据对象A加S锁，可以读A但不能修改A
排他锁 (X锁/写锁): 事务T对数据对象A加X锁，可以读和修改A

锁的相容矩阵:

请求\现有	S锁	X锁	无锁
S锁	√	×	√
X锁	×	×	√

(2) 封锁协议

协议级别	内容	解决的问题
一级封锁协议	事务在修改数据前必须加X锁，直到事务结束释放	丢失修改
二级封锁协议	在一级基础上，读取数据前必须加S锁，读完后立即释放	丢失修改 + 读脏数据
三级封锁协议	在一级基础上，读取数据前必须加S锁，直到事务结束释放	丢失修改 + 读脏数据 + 不可重复读

(3) 两段锁协议 (2PL)

扩展阶段: 只能获得锁，不能释放锁
收缩阶段: 只能释放锁，不能获得锁

重要结论: 遵守两段锁协议的事务是可串行化的，但可能死锁

(4) 死锁处理

预防: 一次封锁法、顺序封锁法
诊断: 超时法、等待图法
解除: 选择代价最小的事务回滚

七、数据库备份与恢复

1. 故障类型

事务内部故障
- 可以预期的事务故障
- 非预期1的事务故障
系统故障
介质故障 (最严重)
计算机病毒

2. 备份类型

备份类型	描述	优点	缺点
完全备份	备份整个数据库	恢复简单	备份时间长，占用空间大
差异备份	备份自上次完全备份以来变化的数据	备份时间较短	恢复需要完全备份+最后一次差异备份
事务日志备份	备份事务日志	备份时间短，可恢复到任意时间点	恢复过程复杂

3.转储

方法

转储方法	描述	优点	缺点
静态转储	在转储期间，系统停止运行应用程序，不允许对数据库进行任何存取、修改操作	实现简单	降低了数据库的可用性转储必须等用户事务结束新的事务必须等转储结束
动态转储	转储期间允许应用程序继续运行，可对数据库进行存取、修改操作	不用等待正在运行的用户事务结束不会影响新事务的运行	不能保证副本中的数据正确有效
海量转储	每次转储全部数据库数据	1. 恢复过程简单直接 2. 恢复时间较短(相对于增量恢复) 3. 管理简单	1. 存储空间需求大 2. 转储时间长 3. 网络带宽占用大
增量转储	只转储上一次转储后更新过的数据	1. 存储空间需求小 2. 转储时间短 3. 网络带宽占用少	1. 恢复过程复杂 2. 恢复时间长(需按顺序恢复) 3. 管理复杂

4. 恢复策略

恢复顺序必须严格遵守:

恢复最近的完全备份
恢复该完全备份之后最近的差异备份 (如果有)
按时间顺序恢复自完全备份或差异备份之后的所有事务日志备份

示例恢复过程:

完全备份1 (1月1日)
差异备份1 (1月2日)
日志备份1 (1月3日)
差异备份2 (1月4日)
日志备份2 (1月5日)
日志备份3 (1月6日)

1月7日发生故障，恢复顺序: 完全备份1 → 差异备份2 → 日志备份2 → 日志备份3

5. 日志文件

作用: 记录事务对数据库的所有更新操作
用途：
- 事务故障的恢复
- 系统故障的恢复
- 介质故障的恢复
登记原则:
1. 登记的次序严格按照事务执行的时间次序
2. 先写日志文件，后写数据库 (WAL原则保证数据库是可恢复的)

八、NoSQL数据库

1. NoSQL概念

非关系型数据库，不保证ACID特性
适用于大规模数据存储和高并发访问
数据模型灵活，易于扩展

2. NoSQL分类

类型	数据模型	典型产品	适用场景
键值存储	Key-Value对	Redis, Memcached	缓存、会话存储
文档型	类似JSON的文档	MongoDB, CouchDB	内容管理、用户配置
列存储	按列存储数据	HBase, Cassandra	大数据分析、日志处理
图形数据库	图结构	Neo4j	社交网络、推荐系统

重要考点: 关系数据库不是NoSQL类型

3. NoSQL适用场景

需要灵活性更强的IT系统
数据模型比较简单
对数据库性能要求更高
不需要高度的数据一致性
海量数据的存储和处理

九、SQL Server 特有关注点

1. SQL Server 系统数据库

数据库	作用
master	记录所有系统级信息
model	模板数据库，新建数据库的模板
msdb	代理服务使用，存储作业、警报等信息
tempdb	临时数据库，存储临时对象

2. T-SQL 特殊语法

-- TOP 限制返回行数
SELECT TOP 5 * FROM Student ORDER BY birthday DESC;

-- 局部变量声明
DECLARE @name VARCHAR(20);
SET @name = '张三';
SELECT * FROM Student WHERE sname = @name;

-- 全局变量
SELECT @@VERSION;  -- SQL Server版本
SELECT @@ROWCOUNT; -- 受影响的行数

-- TOP 限制返回行数
SELECT TOP 5 * FROM Student ORDER BY birthday DESC;

-- 局部变量声明
DECLARE @name VARCHAR(20);
SET @name = '张三';
SELECT * FROM Student WHERE sname = @name;

-- 全局变量
SELECT @@VERSION;  -- SQL Server版本
SELECT @@ROWCOUNT; -- 受影响的行数

3. 触发器

-- 创建触发器
CREATE TRIGGER tr_UpdateProduct
ON Orders
AFTER INSERT
AS
BEGIN
    UPDATE Products 
    SET iQuantityOnHand = iQuantityOnHand - inserted.Quantity
    FROM inserted
    WHERE Products.ProductID = inserted.ProductID;
END;

-- 创建触发器
CREATE TRIGGER tr_UpdateProduct
ON Orders
AFTER INSERT
AS
BEGIN
    UPDATE Products 
    SET iQuantityOnHand = iQuantityOnHand - inserted.Quantity
    FROM inserted
    WHERE Products.ProductID = inserted.ProductID;
END;

触发器类型:

INSTEAD OF 触发器: 代替操作执行

AFTER 触发器: 在操作执行后触发

于2025年12月16日2025年12月16日由Aisssky发布