古法数据库

作为一个码农你已经会的：

• CRUD
• 联表
• in / exists
• 范式是什么

你不会的但是可能有点用的：

• ER图

你不会的，但是没啥用的：

• 关系代数

• 存储过程

• 触发器

• 视图

• 窗口函数

• 用诡异的方法规范化范式，函数依赖

ER图

概念模型

（1）实体（Entity）。客观存在并可相互区别的事物称为实体。可以是具体的人、事、物，也可以是抽象的概念或联系。

（2）属性（Attribute）。实体所具有的某一特性称为属性。一个实体可以由若干个属性来刻画。

（3）码（Key）。唯一标识实体的属性集称为码。

（4）域（Domain）。属性的取值范围称为该属性的域。

（5）实体型（Entity Type）。用实体名及其属性名集合来抽象和刻画同类实体称为实体型。比如，学生（学号,姓名,性别,年龄,系,年级）是一个实体型。

（6）实体集（Entity Set）。同型实体的集合称为实体集。比如，全体学生、女学生。

（7）联系（Relationship）。现实世界中事物内部以及事物之间的联系，在信息世界中反映为实体（型）内部的联系和实体（型）之间的联系。

以上内容本人无版权，全部为抄袭

实体关系图

Entity-Relationship Diagram

组成元素：

1. 实体（Entity）：矩形，里面是名字
2. 属性：椭圆，实体的属性，即表中的字段，和实体用直线连接
3. 联系：菱形，实体之间存在的关系（谓词写在菱形内），菱形与实体的连线上写1/ n/ m表示关系（关系不是仅仅一对的，还可以三个实体共用一个菱形，两两为一种关系，此外关系也并非唯一的，领导和职工既可以一对多也可以一对一）
   • 一对一 1 1
   • 一对多 1 n
   • 多对一 n 1
   • 多对多 n m

注意：题目中给出实体相关的信息并非都要作为实体的属性，尽量满足范式要求，如不应在科室下面记录医生，而应该医生的外键是科室名

补充：如何填写关系菱形中的内容？

通常人作为主语：病人入住房间，学生拥有饭卡

1:n：通常1作为主语，班级容纳学生，部门拥有员工（此时和上一条好像矛盾，事实就是愿意怎么写都行）

1:1 / m:n ：靠直觉，是谁发出的动作，谁是主语

关系模型

把 ER 概念模型转换成数据库可直接使用的逻辑结构，适配关系型数据库

实体直接转一张表，实体属性为字段，实体主键为表主键

一对多 (1:N)：在 “多” 的一方表增加外键，引用 “1” 方主键

一对一 (1:1)：任意一方加外键

多对多 (M:N) 联系必须单独建一张中间表

主码就是主键，外码就是外键，

ER 图实体里带下划线的是主键，但实体可能存在其他能唯一标识的属性，那些全部合起来叫候选码。

关系代数 和 SQL

我们只能用沟槽的SQL Server哦

SQL server 常用函数

聚合函数

数学函数

日期时间函数

会个DATEDIFF就够了

然后回忆一下in 和 exists

1
2
3
4
5
6
7

-- 查询部门ID为1、2、3的员工
SELECT * FROM Employees 
WHERE DeptID IN (1,2,3);

-- 等价于 OR
SELECT * FROM Employees 
WHERE DeptID = 1 OR DeptID = 2 OR DeptID = 3;

EXISTS(子查询)：判断子查询是否有返回行，找到一行立即终止（短路）。

1
2
3
4
5
6

-- 查询存在对应订单的客户
SELECT * FROM Customers c
WHERE EXISTS (
    SELECT 1 FROM Orders o 
    WHERE o.CustomerID = c.CustomerID -- 关联外层表，核心！
);

1
2
3
4
5
6

-- 查询没有任何订单的客户
SELECT * FROM Customers c
WHERE NOT EXISTS (
    SELECT 1 FROM Orders o 
    WHERE o.CustomerID = c.CustomerID
);

NOT EXISTS 嵌套双重否定

找出所有课程都及格的学生

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

SELECT s.*
FROM Student s
WHERE NOT EXISTS (
    -- 第一层否定：不存在这样一门课
    SELECT 1 FROM Score sc
    WHERE sc.StuId = s.StuId
    AND NOT EXISTS (
        -- 第二层否定：该课程没有及格记录
        SELECT 1 FROM Score sc2
        WHERE sc2.StuId = s.StuId
        AND sc2.CourseId = sc.CourseId
        AND sc2.Score >= 60
    )
);

变量 和 循环

局部变量

1
2
3
4
5
6
7
8
9

DECLARE @变量名 数据类型 [= 默认值];

DECLARE @num INT;
SET @num = 100;
SET @num = @num + 1;

DECLARE @oId INT, @oName NVARCHAR(50);
SELECT @oId = OrderID, @oName = OrderName 
FROM Orders WHERE OrderID = 1;

输出别名

1
2

SELECT @num AS 数字, @name AS 名称;
PRINT @name; -- 打印到消息窗口

循环

1
2
3
4
5

WHILE 条件
BEGIN
    -- 循环体
    -- 记得更新条件变量，否则死循环
END

神秘游标

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

DECLARE 
@oId INT,
@oName NVARCHAR(50);

-- 1. 定义游标
DECLARE cur_order CURSOR FOR
    SELECT OrderID, OrderName FROM Orders WHERE IsDelete = 0;

-- 2. 打开游标
OPEN cur_order;

-- 3. 读取第一行
FETCH NEXT FROM cur_order INTO @oId, @oName;

-- 4. 循环读取所有行
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 每行业务逻辑
    PRINT CONCAT('ID：', @oId, ' 名称：', @oName);

    -- 读取下一行
    FETCH NEXT FROM cur_order INTO @oId, @oName;
END

-- 5. 释放资源
CLOSE cur_order;
DEALLOCATE cur_order;

触发器

只考虑DML 触发器

形如

1
2
3
4
5
6
7

CREATE TRIGGER [名字]
ON [表名字]
FOR [INSERT|UPDATE|DELETE]
AS
BEGIN
埃及把干啥干啥
END

注意需要知道 inserted 虚拟表，INSERT、UPDATE 操作时自动生成，deleted 虚拟表，DELETE、UPDATE 操作时自动生成

存储过程

形如

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE PROC 存储过程名
[
    -- 参数列表（入参/出参）
    @参数1 数据类型 [= 默认值],
    @参数2 数据类型 OUTPUT -- 输出参数
]
AS
BEGIN
    -- 业务SQL、判断、循环、事务等逻辑
END
GO

视图

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE VIEW v3
AS
SELECT 
    emp_id,
    emp_name,
    dept_id,
    entry_date
FROM Employee

grant select on v3 to '后勤经理'

数据库设计模型分析

数据依赖

关系模式标准写法：$R(U,F)$

• $R$：关系名（表名）
• $U$：全体属性集合（所有列）
• $F$ = 函数依赖集（Data Dependency） $F$ 是一张表里所有属性之间的函数依赖关系的集合，是描述表内数据约束的规则，用来判断冗余、更新异常、拆分范式（1NF/2NF/3NF/BCNF）。

函数依赖基础符号 $X \to Y$ $X$、$Y$ 是属性子集：

只要两条记录在 $X$ 上的值完全相同，它们在 $Y$ 上的值一定相同； 通俗：X 能唯一确定 Y，记作 $X \to Y$，读作 X 函数决定 Y。

$F={X_1\to Y_1,;X_2\to Y_2,;\dots,;X_n\to Y_n}$ 每一条 $X\to Y$ 都是一条函数依赖FD，全部FD放在一起构成集合 $F$。

e.g.

学生表：$R(U,F)$ $U={\text{Sno学号},\text{Sname姓名},\text{Sdept系},\text{Dname系主任}}$ 业务规则：

1. 学号确定姓名：$\text{Sno}\to\text{Sname}$
2. 学号确定系：$\text{Sno}\to\text{Sdept}$
3. 系确定系主任：$\text{Sdept}\to\text{Dname}$

则函数依赖集： $$F={;\text{Sno}\to\text{Sname},;\text{Sno}\to\text{Sdept},;\text{Sdept}\to\text{Dname};}$$ 这个 $F$ 就完整描述了这张表所有内在数据依赖关系。

人话：

R 就是表，X，Y 为列的集合（既可以是一个字段，也可以是多个字段，类似联合主键）

1. 设 $X\to Y$

• 平凡：$Y\subseteq X$（右边属性是左边子集），永远成立，无业务意义 例：$(\text{Sno,Cno})\to\text{Sno}$
• 非平凡：$Y\not\subseteq X$，业务真正有效的依赖，$F$ 只关注这类

2. 完全函数依赖 $X \stackrel{F}{\to} Y$ $X$ 的任何真子集都不能单独决定Y，必须整体组合。 选课表 $SC(\text{Sno,Cno,Grade})$ $(\text{Sno,Cno}) \stackrel{F}{\to} \text{Grade}$ 单独学号、单独课程号都不能确定成绩。

3. 部分函数依赖 $X \stackrel{p}{\to} Y$ $X$ 是多属性组合，但其中某一部分就能单独决定Y（2NF要消除）。 例：$R(\text{Sno,Cno,Sdept,Grade})$ $(\text{Sno,Cno})\stackrel{p}{\to}\text{Sdept}$ 只用 $\text{Sno}$ 就能确定系，不需要课程号参与。

4. 传递函数依赖 $X\to Y,;Y\not\to X,;Y\to Z \implies X \xrightarrow{\text{传递}} Z$ 上面学生例子：$\text{Sno}\to\text{Sdept},;\text{Sdept}\to\text{Dname}$ $\text{Sno}$ 传递决定 $\text{Dname}$（3NF要消除传递依赖）

候选码：能唯一区分记录，且不能再去掉任何一列。可以有多个

闭包

属性闭包就把属性按照F一个个往里面一次次的套，直到不变

先设这个集合为$X^{(0)}$，之后就是1，2，3，……

范式

候选码：最小主键，能唯一确定整条记录

主属性：候选码里包含的字段

非主属性：不在任何候选码里的字段

1NF 第一范式：列不能 “套娃”

2NF 第二范式：干掉「部分依赖」，所有非主属性，必须完整依赖整个候选码，不能只依赖候选码其中一小部分。（复合主键）

3NF 第三范式：干掉「传递依赖」，不能 “A 推 B，B 推 C，A 间接推 C”。如：学号→系名；系名→系主任

BCNF：表中任意一条 “谁→谁”，箭头左边必须是超码。（相比于第三范式，主键之间不能相互推导，如考生号->考场号，考生号，考场号->座位号，这个就不满足了）

1NF：一格只存一个东西

2NF：复合主键不能一半就定别的字段

3NF：普通字段之间不能互相推导，只要能推出别人，左边必须是超码（候选码、主键都属于超码，带多余字段的组合也算）