SQL复习

SQL复习及经典实现总结

重要语法复习

— PART I —

DISTINCT

SELECT DISTINCT 列名称 FROM 表名称

*不要加括号

WHERE

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

*文本使用单引号，并且大部分数据库系统支持双引号

**部分数据库系统不等于可以用!=代替

INSERT INTO

INSERT INTO 表名称 VALUES (V1, V2, V3, ...)

# 插入指定列
INSERT INTO table_name (Col1, Col2) VALUES (Val1, Val2)

UPDATE

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

# 更新一列
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' 

# 更新若干列
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'

DELETE

DELETE FROM 表名称 WHERE 列名称 = 值

— PART II —

LIKE

SELECT * FROM Persons

# N开头的城市
WHERE City LIKE 'N%'

# g结尾的城市
WHERE City LIKE '%g'

# 包含lon的城市
WHERE City LIKE '%lon%'

# 不包含lon的城市
WHERE City NOT LIKE '%lon%'

通配符

通配符	含义
%	代表零个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	不在字符列中的任何单一字符

IN

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

BETWEEN … AND …

# 介于 "Adams"（包括）和 "Carter"（不包括）之间的人

SELECT * FROM Persons
WHERE LastName
[NOT] BETWEEN 'Adams' AND 'Carter'

JOINs

JOIN

*即INNER JOIN

INNER JOIN

*INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 “Persons” 中的行在 “Orders” 中没有匹配，就不会列出这些行。

LEFT JOIN

*LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

**即LEFT OUTER JOIN

RIGHT JOIN

*RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行，即使在左表 (Persons) 中没有匹配的行。

*即RIGHT OUTER JOIN

FULL JOIN

*FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 “Persons” 中的行在表 “Orders” 中没有匹配，或者如果 “Orders” 中的行在表 “Persons” 中没有匹配，这些行同样会列出。

*即FULL OUTER JOIN

UNION

*默认去重，保留全部用UNION ALL

INTERSECT

MINUS

ALL

ANY

— PART III —

SELECT INTO

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

CONSTRAINTS

NOT NULL

*非空

UNIQUE

*唯一

PRIMARY KEY

*唯一标识，候选键中选一个

FOREIGN KEY

*防止破坏约束关系

CHECK

*预设条件

DEFAULT

*默认值

AUTO_INCREMENT

*自增

VIEW

CREATE VIEW 视图名称 AS
SELECT * FROM Products

DATE

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式 YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
• YEAR - 格式 YYYY 或 YY

窗口函数

https://blog.csdn.net/weixin_39010770/article/details/87862407

— Part IV —

BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF

First Normal Form

1NF：保证数据的原子性，一列一数据，否则要新建实体，同时要有主键

e.g. 以下表即违反了第一范式

student	course
Sam	{C1, C2, C5}
Kitty	{C3}

$\Rightarrow$改为

student	course
Sam	C1
Sam	C2
Sam	C3
Kitty	C3

Second Normal Form

Definition: A relation $R$ with a set of functional dependencies $\Sigma$ is in 2NF $⟺$ every non-prime attribute is fully dependent on each candidate key.

每个非主元素都必须完全依赖于每一个候选键（消除部分依赖）

Theorem: for every X → { A } ∈ Σ + X \rightarrow \{A\} \in \Sigma^{+} X→{A}∈Σ+

• X → { A } X\rightarrow \{A\} X→{A} is trivial or
• $A$ is a prime attribute or
• $X$ is not a proper subset of a candidate key

(It is sufficient to look at $\Sigma$)

e.g. 以下表即违反了第二范式

number	name	course	lecturer
1XU3	Dewi Srijaya	Discrete Mathematics	Godfrey Hardy
5CT4	Axel Bayer	Discrete Mathematics	Godfrey Hardy
1XU3	Dewi Srijaya	Number Theory	Godfre Hardy
7HG5	Eric Wei	Anthropology	Broniaslaw Malinowski
7HG5	Eric Wei	Philosophy	Max Stirner
8HG5	Sylvia Tok	Philosophy	Max Stirner

可以注意到$lecturer$作为非主属性，并不完全依赖于候选键之一 { n u m b e r , c o u r s e } \{number, course\} {number,course}，因为 { c o u r s e } → { l e c t u r e r } \{course\}\rightarrow\{lecturer\} {course}→{lecturer}，只是部分依赖。

带来的问题：

• 数据冗余：N个学生选一门课，course&lecturer就会被重复N-1次
• 更新异常：一门课的lecturer换人，需要更新所有选这门课的记录中的lecturer
• 插入异常：开设一门新课，因为还没有人人选，就没法登记这门课的course&lecturer
• 删除异常：学生结课了，删除记录后会导致插入异常，即失去这门课的course&lecturer记录

$\Rightarrow$分解为两张表

number	name	course
1XU3	Dewi Srijaya	Discrete Mathematics
5CT4	Axel Bayer	Discrete Mathematics
1XU3	Dewi Srijaya	Number Theory
7HG5	Eric Wei	Anthropology
7HG5	Eric Wei	Philosophy
8HG5	Sylvia Tok	Philosophy

course	lecturer
Discrete Mathematics	Godfrey Hardy
Number Theory	Godfre Hardy
Anthropology	Broniaslaw Malinowski
Philosophy	Max Stirner

Boyce-Codd Normal Form

Definition: A relation $R$ with a set of functional dependencies $\Sigma$ is in BCNF $⟺$ for every attribute set $S\cup R$, if any attribute of $R $ not in $S$ is functionally dependent on $S$, then all attributes in $R$ are functionally dependent on $S$.

（消除主属性对键的传递依赖）在3NF的基础上消除了主属性对键的依赖

Theorem: for every X → { A } ∈ Σ + X \rightarrow \{A\} \in \Sigma^{+} X→{A}∈Σ+

• X → { A } X\rightarrow \{A\} X→{A} is trivial or
• $X$ is a superkey

(It is sufficient to look at $\Sigma$)

e.g. 以下例子即违反了BC范式

假设仓库管理关系表为StorehouseManage(warehouse_id, item_id, admin_id, quant)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：

{ w a r e h o u s e , i t e m } → { a d m i n , q u a n t } \{warehouse, item\} \rightarrow \{admin, quant\} {warehouse,item}→{admin,quant}

{ a d m i n , i t e m } → { w a r e h o u s e , q u a n t } \{admin, item\} \rightarrow \{warehouse, quant\} {admin,item}→{warehouse,quant}

所以，(warehouse, item)和(admin, item)都是StorehouseManage的候选关键字，表中的唯一非关键字段为quant，它是符合第三范式的。但是，由于存在如下决定关系：

{ w a r e h o u s e } → { a d m i n } \{warehouse\} \rightarrow \{admin\} {warehouse}→{admin}

{ a d m i n } → { w a r e h o u s e } \{admin\} \rightarrow \{warehouse\} {admin}→{warehouse}

• 删除异常：当仓库被清空后，所有"item"和"quant"信息被删除的同时，"warehouse"和"admin"信息也被删除

• 插入异常：当仓库没有存储任何物品时，无法给仓库分配管理员

• 更新异常：如果仓库换了管理员，则表中所有行的admin都要修改

Third Normal Form

BCNF有一种例外：

R = { A , B , C } R=\{A, B, C\} R={A,B,C}

Σ = { { A , B } → { C } , { C } → { B } } \Sigma=\{\{A, B\}\rightarrow \{C\}, \{C\}\rightarrow \{B\}\} Σ={{A,B}→{C},{C}→{B}}

The candidate keys are { A , B } \{A,B\} {A,B} and { A , C } \{A, C\} {A,C}

注意 { C } → { B } \{C\}\rightarrow\{B\} {C}→{B}不是冗余的，且 { C } \{C\} {C}不是一个超键

同时无法在不损失依赖的前提下再简化/分解。

Relax BCNF requirements for prime attributes to cover the cases.

Definition:

（消除非主属性对键的传递依赖）

Theorem: for every X → { A } ∈ Σ + X \rightarrow \{A\} \in \Sigma^{+} X→{A}∈Σ+

• X → { A } X\rightarrow \{A\} X→{A} is trivial or
• $X$ is a superkey or
• $A$ is a prime attribute

(It is sufficient to look at $\Sigma$)

---

---

Decomposition Algorithm

特点：不损失数据，但不能保证不丢失依赖

假设$\Sigma$中有$X\to Y$依赖关系，若不满足$BCNF$，则将其分解，步骤如下：

(1) $R_1=X^{+}$, ${\Sigma }_1=ProjectedFDsin\Sigma$, $R2=(R-X^{+})\cup X$, ${\Sigma }_2=ProjectedFDsin\Sigma$

(2) 分别验证$R_1,{\Sigma }_1,R_2,{\Sigma }_2$是否满足$BCNF$，若是则结束，若否则继续分解

Synthesis Algorithm

原则：不损失数据，不丢失依赖

Synthesis Algorithm is guaranteed to find a lossless decomposition in 3NF.

当一个关系不满足3NF，我们可以从依赖集合的最小闭包中同步(Synthesize)一个满足3NF的关系

• For each functinal dependency $X\to Y$ in the minimal cover create a relation $R_i=X\cup Y$unless it already exists or is subsumed by another relation.

• If none of the created relations contain one of the keys, pick a candidate key and create a relation with that candidate key.

(1) 对于最小闭包中的每个函数依赖，初始化$R_i=X\cup Y$，如果$R_j\subset R_i$，消去$R_j$

(2) 如果存在元素$X\in R$但是$X\notin R_i\cup ...\cup R_j$，将一个包含$X$的候选键补充为$R_{j+1}$

Fourth Normal Form Decomposition

If $X\twoheadrightarrow Y$ is a $4NF$ violation for relation $R$, we can decompose $R$ using the same
technique as for $BCNF$

1. $X\cup Y$ is one of the decomposed relations.
2. All but $Y-X$ is the other.

初始化$R_i=X\cup Y$，如果$R_j\subset R_i$，消去$R_j$

(2) 如果存在元素$X\in R$但是$X\notin R_i\cup ...\cup R_j$$，将一个包含$X$的候选键补充为$R_{j+1}$

Fourth Normal Form Decomposition

If $X\twoheadrightarrow Y$ is a $4NF$ violation for relation $R$, we can decompose $R$ using the same
technique as for $BCNF$.

1. $X\cup Y$ is one of the decomposed relations.
2. All but $ Y - X $is the other.