只需掌握四个查询，就能解决 95% 的 SQL 问题

在数据分析和管理中，掌握复杂查询逻辑是关键技能。即使在人工智能应用中，80%以上的时间都用于数据处理。
所谓 “garbage in, garbage out” 说明了数据质量的重要性。
而写好查询是数据处理的第一步。一个错误的查询可能毁掉整个模型。

今天，我将通过一个有趣的案例，向你展示如何从自然语言到SQL查询的转化。这不仅是一次技术之旅，更是一场逻辑的魔法秀。

关系模式 (Relational Schema)

学生表：Student(Sid, Name, Major, Birthdate)
课程表：Course(Cid, CName, Dept)
课程选修表：Enroll(Sid, Cid, Quarter, Grade)

查询的自然语言描述 (Queries)

Q1: 查找那些选修了Sid 为 928521 的同学选修的课程，并且在相同学期内选修这些课程的学生。

Q2: 查找那些选修了Sid 为 928521 的同学选修的所有课程，并且在相同学期内选修这些课程的学生。

Q3: 查找那些仅选修了Sid 为 928521 的同学选修的课程，并且在相同学期内选修这些课程的学生。

Q4: 查找那些仅选修了Sid 为 928521 的同学选修的所有课程，并且在相同学期内选修这些课程的学生。

查询中的逻辑解读

韦恩图：学生 $s$ 选修的课程 与 928521 选修的课程 的关系。$U$ 表示所有学生：

Q1: $s$ 的课程要与 928521 的课程有交集。

Q2: 928521 的课程是 $s$ 的课程的子集，928521 的任何课程都被 $s$ 选修了。

Q3: $s$ 的课程是928521 的课程的子集，928521 的任何没有选修的课程，$s$ 不可能选修。

Q4: $s$ 的课程和 928521 的课程是同一个集合。

解决问题步骤

1.将自然语言查询翻译成一阶逻辑 (First Order Language) 语言表达 (一阶逻辑表达式不包含 全称量词 和 逻辑蕴含)；

2.将一阶逻辑语言翻译成关系代数 (Relational Algebra) 语言表达；

3.将关系代数语言翻译成 SQL 查询。

Step1. 自然语言 --> 一阶逻辑

这里我先规定一些变量和谓词：

• 变量：$s$ 来表示某个学生, $c$ 表示某个课程, $q$ 表示某个学期。
• 谓词：$enroll(s,c,q)$ 表达某个学生 $s$ 在某个学期 $q$ 选修了课程 $c$。为了表达式的简单，我们不单独为学生和课程定义谓词了。
  

Q1：描述一个学生集合，这些学生要选修 928521 这个同学选修了的课程，并且还是在同一学期选修的:
{ s ∣ ( ∃ c ) ( ∃ q ) [ e n r o l l ( 928251 , c , q ) ∧ e n r o l l ( s , c , q ) ] ∧ s ≠ 928251 } \{s|(\exists c)(\exists q)[enroll(928251,c,q)\land enroll(s,c,q)]\land s\not=928251\} {s∣(∃c)(∃q)[enroll(928251,c,q)∧enroll(s,c,q)]∧s=928251}

Q2：根据 $s$ 选修了 928521 的所有课程，如果 928521 选修了某门课，$s$ 必定也选修了 (因为 $s$ 上了 928521 所有的课)：
{ s ∣ ( ∀ c ) ( ∀ q ) [ e n r o l l ( 928251 , c , q ) ⇒ e n r o l l ( s , c , q ) ] ∧ s ≠ 928251 } \{s|(\forall c)(\forall q)[enroll(928251,c,q)\Rightarrow enroll(s,c,q)]\land s\not=928251\} {s∣(∀c)(∀q)[enroll(928251,c,q)⇒enroll(s,c,q)]∧s=928251}
Q3：根据 $s$ 仅 选修 928521 的课，如果 $s$ 选修了某门课，928521 必定也选修了:
{ s ∣ ( ∀ c ) ( ∀ q ) [ e n r o l l ( s , c , q ) ⇒ e n r o l l ( 928251 , c , q ) ] ∧ s ≠ 928251 } \{s|(\forall c)(\forall q)[enroll(s,c,q)\Rightarrow enroll(928251,c,q)]\land s\not=928251\} {s∣(∀c)(∀q)[enroll(s,c,q)⇒enroll(928251,c,q)]∧s=928251}
Q4：Q2 和 Q3 条件的并：
{ s ∣ ( ∀ c ) ( ∀ q ) [ e n r o l l ( s , c , q ) ⇔ e n r o l l ( 928251 , c , q ) ] ∧ s ≠ 928251 } \{s|(\forall c)(\forall q)[enroll(s,c,q)\Leftrightarrow enroll(928251,c,q)]\land s\not=928251\} {s∣(∀c)(∀q)[enroll(s,c,q)⇔enroll(928251,c,q)]∧s=928251}
$\iff$ 表示逻辑等价 (也可以用 $\equiv$ 表示)：$q\Rightarrow p\wedge p\Rightarrow q\equiv q\iff q$

Step2. 一阶逻辑 --> 关系代数

学生表：Student(Sid, Name, Major, Birthdate)
课程表：Course(Cid, CName, Dept)
课程选修表：Enroll(Sid, Cid, Quarter, Grade)

Q1: { s ∣ ( ∃ c ) ( ∃ q ) [ e n r o l l ( 928251 , c , q ) ∧ e n r o l l ( s , c , q ) ] ∧ s ≠ 928251 } \{s|(\exists c)(\exists q)[enroll(928251,c,q)\land enroll(s,c,q)]\land s\not=928251\} {s∣(∃c)(∃q)[enroll(928251,c,q)∧enroll(s,c,q)]∧s=928251} 这个一阶逻辑简化了关系代数的每个部分，我们可以把原查询拆分为多个关系代数表达式：

• $T_{11}:={\pi }_{Cid,Quarter}({\sigma }_{Sid=928251}(Enroll)))$ 对应一阶逻辑表达式 $enroll(928251,c,q)$ 。其中 $\pi$ 表示选择哪些列，$\sigma$ 表示关系选择条件。
• $T_{12}={\pi }_{Sid,Cid,Quarter}(Enroll)$ 对应一阶逻辑表达式 $enroll(s,c,q)$。
• $T_{13}={\sigma }_{Sid\ne 928521}(T_{12})$ 对应一阶逻辑表达式 $enroll(s,c,q)\wedge s\ne 928521$。
• $T_{14}=T_{11}\wedge T_{13}$ 对应一阶逻辑表达式 $enroll(928251,c,q)\wedge enroll(s,c,q)]\wedge s\ne 928251$。
• $T_{15}={\pi }_{Sid}(T_{14})$ 对应整个 Q1 的一阶逻辑表达式。

Q2: 翻译这个一阶逻辑表达式有些难度，主要在于对全称量词 $\forall$ 以及对逻辑蕴含 $\Rightarrow$ 的转化。因为关系代数没有表达 $\forall$ 的表达式，小编已知所有的查询语言都不支持 $\forall$ 表达，因为计算它的计算代价很大。在翻译之前我们需要用到：

• 全称量词和存在量词的转化
  $$\begin{array}{rl}(\forall x)[f(x)]& \equiv \neg \neg (\forall x)[f(x)]\text{双重否定}\\ & \equiv \neg (\exists x)[\neg f(x)]\end{array}$$
• 逻辑蕴含的等价
  $$p\Rightarrow q\equiv \neg (p\wedge \neg q)\equiv \neg p\vee q$$
• 摩根定律 (De Morgan’s laws)
  $$\neg (p\wedge q)\equiv (\neg p\vee \neg q),\neg (p\vee q)\equiv (\neg p)\wedge (\neg q)$$
  变换一阶逻辑表达式：
  { s ∣ ( ∀ c ) ( ∀ q ) [ e n r o l l ( 928251 , c , q ) ⇒ e n r o l l ( s , c , q ) ] ∧ s ≠ 928251 } = { s ∣ ¬ ( ∃ c ) ( ∃ q ) [ ¬ ( e n r o l l ( 928251 , c , q ) ⇒ e n r o l l ( s , c , q ) ) ] ∧ s ≠ 928251 }  全称量词转化 = { s ∣ ¬ ( ∃ c ) ( ∃ q ) [ ¬ ( ¬ e n r o l l ( 928251 , c , q ) ∨ e n r o l l ( s , c , q ) ) ] ∧ s ≠ 928251 }  逻辑蕴含等价 = { s ∣ ¬ ( ∃ c ) ( ∃ q ) [ e n r o l l ( 928251 , c , q ) ∧ ¬ e n r o l l ( s , c , q ) ) ] ∧ s ≠ 928251 }  摩根定律 \begin{aligned}&\{s|(\forall c)(\forall q)[enroll(928251,c,q)\Rightarrow enroll(s,c,q)]\land s\not=928251\}\\ =&\{s|\neg(\exists c)(\exists q)[\neg(enroll(928251,c,q)\Rightarrow enroll(s,c,q))]\land s\not=928251\}\ \text{全称量词转化}\\ =& \{s|\neg(\exists c)(\exists q)[\neg(\neg enroll(928251,c,q)\lor enroll(s,c,q))]\land s\not=928251\}\ \text{逻辑蕴含等价}\\ =& \{s|\neg(\exists c)(\exists q)[enroll(928251,c,q)\land \neg enroll(s,c,q))]\land s\not=928251\}\ \text{摩根定律} \end{aligned} ===​{s∣(∀c)(∀q)[enroll(928251,c,q)⇒enroll(s,c,q)]∧s=928251}{s∣¬(∃c)(∃q)[¬(enroll(928251,c,q)⇒enroll(s,c,q))]∧s=928251} 全称量词转化{s∣¬(∃c)(∃q)[¬(¬enroll(928251,c,q)∨enroll(s,c,q))]∧s=928251} 逻辑蕴含等价{s∣¬(∃c)(∃q)[enroll(928251,c,q)∧¬enroll(s,c,q))]∧s=928251} 摩根定律​

学生表：Student(Sid, Name, Major, Birthdate)
课程表：Course(Cid, CName, Dept)
课程选修表：Enroll(Sid, Cid, Quarter, Grade)

翻译转化后的一阶逻辑表达式 { s ∣ ¬ ( ∃ c ) ( ∃ q ) [ e n r o l l ( 928251 , c , q ) ∧ ¬ e n r o l l ( s , c , q ) ) ] ∧ s ≠ 928251 } \{s|\neg(\exists c)(\exists q)[enroll(928251,c,q)\land \neg enroll(s,c,q))]\land s\not=928251\} {s∣¬(∃c)(∃q)[enroll(928251,c,q)∧¬enroll(s,c,q))]∧s=928251} 为关系代数表达式：

• $T_{21}={\pi }_{Sid,Cid,Quarter}(Student\times Course)$ 表示全集 $U$ (所有可能的选修信息)，其中 $\times$ 表示笛卡尔积。
• $T_{22}=T_{21}-T_{12}$ 对应一阶逻辑表达式 $\neg enroll(s,c,q)$，这里需要全集 $U$ 来表达 negation ($\neg$)。
• $T_{23}=T_{11}\bowtie T_{22}$ 对应一阶逻辑表达式 $enroll(928251,c,q)\wedge \neg enroll(s,c,q)$，其中 $\bowtie$ 表示自然连接，等价于逻辑 “与” ($\wedge$)。
• $T_{24}=T_{21}-T_{23}$ 对应一阶逻辑表达式 $\neg (\exists c)(\exists q)[enroll(928251,c,q)\wedge \neg enroll(s,c,q))]$。
• $T_{25}={\sigma }_{Sid\ne 928251}(T_{24})$ 对应一阶逻辑表达式 $\neg (\exists c)(\exists q)[enroll(928251,c,q)\wedge \neg enroll(s,c,q))]\wedge s\ne 928251$。
• $T_{26}={\pi }_{Sid}(T_{25})$ 对应整个转化后的 Q2 的一阶逻辑表达式。

Q3: { s ∣ ( ∀ c ) ( ∀ q ) [ e n r o l l ( s , c , q ) ⇒ e n r o l l ( 928251 , c , q ) ] ∧ s ≠ 928251 } \{s|(\forall c)(\forall q)[enroll(s,c,q)\Rightarrow enroll(928251,c,q)]\land s\not=928251\} {s∣(∀c)(∀q)[enroll(s,c,q)⇒enroll(928251,c,q)]∧s=928251} $\equiv$ { s ∣ ¬ ( ∃ c ) ( ∃ q ) [ e n r o l l ( s , c , q ) ∧ ¬ e n r o l l ( 928251 , c , q ) ] ∧ s ≠ 928251 } \{s|\neg(\exists c)(\exists q)[enroll(s,c,q)\land \neg enroll(928251,c,q)]\land s\not=928251\} {s∣¬(∃c)(∃q)[enroll(s,c,q)∧¬enroll(928251,c,q)]∧s=928251}，这里直接给出关系代数表达式：

• $T_{31}={\pi }_{Cid,Quarter}(Student\times Course)$
• $T_{32}=T_{31}-T_{11}$ 。
• $T_{33}=T_{12}\bowtie T_{32}$ 。
• $T_{34}=T_{21}-T_{33}$ 。
• $T_{35}={\sigma }_{Sid\ne 928251}(T_{34})$
• $T_{36}={\pi }_{Sid}(T_{35})$

Q4: 答案为 Q3 与 Q2 的逻辑 “与”：
$T_{41}=T_{26}\bowtie T_{36}$。

Step3. 关系代数 --> SQL 查询

学生表：Student(Sid, Name, Major, Birthdate)
课程表：Course(Cid, CName, Dept)
课程选修表：Enroll(Sid, Cid, Quarter, Grade)

以 Q2 为例子来写查询
$T_{21}={\pi }_{Sid,Cid,Quarter}(Student\times Course)$ :

CREATE VIEW T21 AS
SELECT Sid, Cid, Quarter
FROM Student, Course;

$T_{22}=T_{21}-T_{12}$，其中 $T_{12}={\pi }_{Sid,Cid,Quarter}(Enroll)$:

CREATE VIEW T22 AS
SELECT DISTINCT T21.Sid, T21.Cid, T21.Quarter 
EXCEPT
SELECT DISTINCT Sid, Cid, Quarter FROM Enroll;

$T_{23}=T_{11}\bowtie T_{22}$，其中 $T_{11}:={\pi }_{Cid,Quarter}({\sigma }_{Sid=928251}(Enroll)))$:

CREATE VIEW T11 AS
SELECT Cid, Quater 
FROM Enroll
WHERE Sid = 928251;

CREATE VIEW T23 AS
SELECT T11.Sid, T11.Cid, T11.Quarter
FROM T11
NATURAL JOIN T22;

$T_{24}=T_{21}-T_{23}$:

CREATE VIEW T24 AS
SELECT DISTINCT *
FROM T21
EXCEPT
SELECT DISTINCT *
FROM T23;

上面的 SELECT DISTINCT * 也可以是 SELECT DISTINCT Sid, Cid, Quarter。

$T_{25}={\sigma }_{Sid\ne 928251}(T_{24})$, $T_{26}={\pi }_{Sid}(T_{25})$:

SELECT DISTINCT Sid
FROM T24
WHERE Sid != 928521;

关系代数到 SQL 的对应表总结

这里根据 set semantics (集合语义) 给出的对应表。

关系代数	SQL 语法
σ S P ( R ) = { t ∣ t ∈ R ∧ t  satisfies  S P } \sigma_{SP}(R)=\{t\mid t\in R\land t\text{ satisfies }SP\} σSP​(R)={t∣t∈R∧t satisfies SP}	SELECT DISTINCT * FROM R WHERE SP
${\pi }_{A_1,\cdots ,A_n}(R)=\{(r.A_1,\cdots ,r.A_n)\mid r\in R\}$	SELECT DISTINCT A1, ..., An FROM R
$R\times S=\{(t.A_1,\cdots ,t.A_n,r.B_1,\cdots ,r.B_n)\mid t\in R\wedge r\in S\}$	SELECT DISTINCT * FROM R, S
R ⋈ S P S = { t ∣ t ∈ R × S ∧ t  satisfies  S P } R\Join_{SP}S=\{t\mid t\in R\times S\land t\text{ satisfies }SP\} R⋈SP​S={t∣t∈R×S∧t satisfies SP}	SELECT DISTINCT * FROM R, S WHERE SP
R ⋈ S = { t = ( A 1 , ⋯   , A n ) ∣ t ∈ R × S ∧ R . A 1 = S . A 1 ∧ ⋯ ∧ R . A n = S . A n } R\Join S=\{t=(A_1,\cdots,A_n)\mid t\in R\times S\land R.A_1=S.A_1\land\cdots\land R.A_n=S.A_n\} R⋈S={t=(A1​,⋯,An​)∣t∈R×S∧R.A1​=S.A1​∧⋯∧R.An​=S.An​}	SELECT DISTINCT * FROM R NATURAL JOIN S
$R{\bowtie }_{R.A_1=S.A_1\wedge \cdots \wedge R.A_n=S.A_n}S$	SELECT DISTINCT * FROM R JOIN S USING (A1, ..., An)
$R{⟕}_{SP}S$	SELECT DISTINCT * FROM R LEFT OUTER JOIN S ON SP
$R{⟖}_{SP}S$	SELECT DISTINCT * FROM R RIGHT OUTER JOIN S ON SP
$R{⟗}_{SP}S$	SELECT DISTINCT * FROM R FULL OUTER OUTER JOIN S ON SP
R ∪ S = { t ∣ t ∈ R ∨ t ∈ S } R\cup S=\{t\mid t\in R\lor t\in S\} R∪S={t∣t∈R∨t∈S}	SELECT DISTINCT * FROM R UNION SELECT DISTINCT * FROM S
R ∩ S = { t ∣ t ∈ R ∧ t ∈ S } R\cap S=\{t\mid t\in R\land t\in S\} R∩S={t∣t∈R∧t∈S}	SELECT DISTINCT * FROM R INTERSECT SELECT DISTINCT * FROM S
R − S = { t ∣ t ∈ R ∧ t ∉ S } R- S=\{t\mid t\in R\land t\not\in S\} R−S={t∣t∈R∧t∈S}	SELECT DISTINCT * FROM R EXCEPT SELECT DISTINCT * FROM S

不定期更新专业知识和有趣的东西，欢迎反馈、点赞、加星

您的鼓励和支持是我坚持创作的最大动力！ღ( ´･ᴗ･` )

Reference

1. Introduction to Database Systems (SoSe 2024) by Maria-Esther Vidal.