结构化查询语言(Structured Query Language)
数据存储与规范化
数据应该以一种在数据库中不存在冗余信息的方式进行存储。例如，如果我们的数据库包含一组人，这些人在每种情况下都追求相同的爱好，那么我们宁愿避免在每条关于某个爱好爱好者的记录中重复存储相同的静态爱好详细信息。同样，我们也宁愿避免在每条关于某个人的爱好的记录中重复存储同一个爱好者的详细信息。相反，我们创建独立的 person 和 hobby 表，并通过指向彼此的方式进行关联。这种将数据分组到独立、无冗余表中的技术称为数据库规范化。这种分离也有助于简化逻辑，并增强为特定目的精确组装所需项的灵活性。这种组装是通过 JOIN 操作完成的。
概念
在我们的示例数据库中，有两个表：person 和 contact。contact 表包含 person_id 列，该列与 person 表的主键列 id 相关联。通过评估列值，我们可以将联系信息和个人信息关联起来。
person 表 P
| ID | LASTNAME | FIRSTNAME | ... |
|---|---|---|---|
| 1 | Goldstein | Larry | ... |
| 2 | Burton | Tom | ... |
| 3 | Hamilton | Lisa | ... |
| 4 | Goldstein | Kim | ... |
| ... | ... | ... | ... |
| ... | ... | ... | ... |
contact 表 C
| ID | PERSON_ID | CONTACT_TYPE | CONTACT_VALUE |
|---|---|---|---|
| 1 | 1 | fixed line | 555-0100 |
| 2 | 1 | larry.goldstein@acme.xx | |
| 3 | 1 | lg@my_company.xx | |
| 4 | 1 | icq | 12111 |
| 5 | 4 | fixed line | 5550101 |
| 6 | 4 | mobile | 10123444444 |
| ... | ... | ... | ... |
| ... | ... | ... | ... |
连接后的（虚拟）表
| P.ID | P.LASTNAME | P.FIRSTNAME | ... | C.ID | C.PERSON_ID | C.CONTACT_TYPE | C.CONTACT_VALUE |
|---|---|---|---|---|---|---|---|
| 1 | Goldstein | Larry | ... | 1 | 1 | fixed line | 555-0100 |
| 1 | Goldstein | Larry | ... | 2 | 1 | larry.goldstein@acme.xx | |
| 1 | Goldstein | Larry | ... | 3 | 1 | lg@my_company.xx | |
| 1 | Goldstein | Larry | ... | 4 | 1 | icq | 12111 |
| 2 | Burton | Tom | ... | ? | ? | ? | ? |
| 3 | Hamilton | Lisa | ... | ? | ? | ? | ? |
| 4 | Goldstein | Kim | ... | 5 | 4 | fixed line | 5550101 |
| 4 | Goldstein | Kim | ... | 6 | 4 | mobile | 10123444444 |
| ... | ... | ... | ... | ... | ... | ... | ... |
因此，存在于存储的 person 表中的 Larry Goldstein 仅出现一次，但在连接后的虚拟表中却列出了四次，每次与他的四项联系信息中的一项组合。同样，Kim Goldstein 及其两项联系信息也是如此。
但 Tom Burton 和 Lisa Hamilton 的联系信息不可用。这意味着尝试将他们的个人数据与不存在的联系信息关联时可能会遇到一些问题。目前，我们用问号标记了这种情况。如何将问题转化为解决方案的详细解释将在本页稍后部分出现。
基本语法
显然，JOIN 操作需要指定两件事：
- 相关表的名称
- 相关列的名称
基本语法通过这两部分扩展了 SELECT 命令：
SELECT <things_to_be_displayed> -- 如常
FROM <tablename_1> <table_1_alias> -- 表的别名
JOIN <tablename_2> <table_2_alias> ON <join condition> -- 连接条件
... -- 可选的其他 SELECT 命令元素
;
让我们进行第一次尝试。
SELECT *
FROM person p
JOIN contact c ON p.id = c.person_id;
其中一个表名在 FROM 关键字之后引用（如前所述），另一个表名在新的 JOIN 关键字之后引用，这（毫不奇怪）指示 DBMS 执行连接操作。接下来，ON 关键字引入列名及其比较运算符（或一般条件，如后文所述）。列名以各自表别名 p 和 c 为前缀。这是必要的，因为多个表中可能存在相同名称的列（如 id）。
当 DBMS 执行此命令时，它会返回包含两个表中所有列的“某物”，包括各自（person 和 contact）表中的两个 id 列。结果包含九行，每行对应一个现有的 person 和 contact 组合；即，由于 ON 表达式，未关联任何联系记录的 person 记录不会出现在结果中。
返回的“某物”看起来像一个新表；事实上，它具有与表相同的结构、行为和数据。如果它是从视图创建的或作为子选择的结果，我们甚至可以在其上执行新的 SELECT。但这与表有一个重要区别：它的组装数据并未作为实体存储在 DBMS 中；相反，数据是在运行时从真实表的值中计算得出的，并且仅在 DBMS 运行您的程序时暂存在内存中。
这个关键特性——从简单表组装复杂信息——是通过两个简单的关键字 JOIN 和 ON 实现的。正如您将看到的，语法可以扩展以构建非常复杂的查询，从而可以为连接条件的规范添加许多额外的细化。
有时，当结果与您的意图不符时，可能会感到困惑。如果发生这种情况，请尝试简化查询，如此处所示。困惑往往源于 JOIN 语法本身可能变得相当复杂。此外，连接可以与 SELECT 命令的所有其他语法元素结合使用，这也可能导致不清晰。
以下示例展示了连接语法与其他语言元素的组合。
仅显示重要列
SELECT p.firstname, p.lastname, c.contact_type AS "Kind of Contact", c.contact_value AS "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id;
仅显示所需行
SELECT p.firstname, p.lastname, c.contact_type AS "Kind of Contact", c.contact_value AS "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile');
应用排序
SELECT p.firstname, p.lastname, c.contact_type AS "Kind of Contact", c.contact_value AS "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile')
ORDER BY p.lastname, p.firstname, c.contact_type DESC;
使用函数：min() / max() / count()
SELECT count(*)
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile');
自连接（JOIN a table with itself）。示例：查找具有相同姓氏的不同人
SELECT p1.id, p1.firstname, p1.lastname, p2.id, p2.firstname, p2.lastname
FROM person p1
JOIN person p2 ON p1.lastname = p2.lastname -- 对于 person 的第二个实例，必须使用不同的别名
WHERE p1.id != p2.id
ORDER BY p1.lastname, p1.firstname, p2.firstname;
连接多个表。示例：查找具有相同姓氏的不同人的联系信息
SELECT p1.id, p1.firstname, p1.lastname, p2.id, p2.firstname, p2.lastname, c.contact_type, c.contact_value
FROM person p1
JOIN person p2 ON p1.lastname = p2.lastname
JOIN contact c ON p2.id = c.person_id -- 从 person2 获取联系信息。p1.id 会导致 person1
WHERE p1.id != p2.id
ORDER BY p1.lastname, p1.firstname, p2.lastname;
总结
通过规范化和使用 JOIN 操作，我们可以有效地管理和查询数据库中的数据，避免冗余，提高数据一致性，并增强查询的灵活性和效率。掌握这些基本概念和语法对于构建健壮的数据库系统至关重要。
您可以根据需要复制/粘贴这些示例，以帮助您学习和实践 SQL。
四种连接类型
在本页早些时候，我们看到了一个连接结果的示例，其中一些行包含人物姓名，但没有联系信息——相应地在后者的列中显示了问号。如果使用了 JOIN 操作的基本语法，这些（问号）行将被过滤掉。这种（带有排他性结果的基本语法）称为 INNER 连接。还有另外三种不同类型的 OUTER 连接。OUTER 连接的结果不仅包含 INNER 连接结果中的所有完整数据行，还包含部分数据行，即在两个存储表中一个或两个表中都未找到数据的行；因此，它们被称为 LEFT OUTER、RIGHT OUTER 和 FULL OUTER 连接。
因此，我们可以将基本的 JOIN 语法扩展为四种选项：
- [INNER] JOIN
- LEFT [OUTER] JOIN
- RIGHT [OUTER] JOIN
- FULL [OUTER] JOIN
方括号 [ ] 中的关键字是可选的。解析器会根据 LEFT、RIGHT 或 FULL 推断 OUTER，而普通的（即基本语法的）JOIN 默认为 INNER。
内连接（Inner Join）
内连接可能是四种类型中最常用的一种。正如我们所见，它仅产生完全匹配 ON 后面条件的那些行。下面是一个示例，展示如何创建一个包含人物及其联系信息的列表。
-- 人物及其联系信息列表
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
JOIN contact c ON p.id = c.person_id -- 与 INNER JOIN 意义相同
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
最重要的是，没有任何联系信息的人的记录不会出现在结果中。
左外连接（Left Outer Join）
有时我们需要更多的信息；例如，我们可能希望获取所有人物记录的列表，包括可能存在的任何联系信息记录。注意这与上面的示例有何不同：这一次，结果将包含所有人物记录，即使那些人没有任何联系信息记录。
-- 所有人物及其联系信息列表
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
LEFT JOIN contact c ON p.id = c.person_id -- 与 LEFT OUTER JOIN 意义相同
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
在联系信息不可用的情况下，DBMS 会用“空值”或“空特殊标记”替代（不要将其与字符串类型的“null value”或“null”以及二进制的 0 混淆）。不过，具体实现细节在此不重要。空特殊标记将在后面的章节中讨论。
总而言之，左外连接是内连接加上每个左侧匹配但右侧没有对应记录的行。
考虑“左”这个词。它指的是公式的左侧，即 FROM <table_1> LEFT JOIN
<table_2>，更具体地说，是左侧的表（此处为 table_1）；这意味着该表的每一行至少会在结果中出现一次，无论右侧表（此处为 table_2）中是否存在对应的记录。
另一个示例：
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM contact c
LEFT JOIN person p ON p.id = c.person_id -- 与 LEFT OUTER JOIN 意义相同
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
有什么不同？我们改变了表名的顺序。注意我们仍在使用 LEFT 连接，但因为 contact 现在是“左”引用（即 FROM 子句中的对象），所以联系数据现在被视为主要重要；因此，所有联系记录都会出现在结果中——以及可能存在于 person 表中的任何对应信息。实际上，在我们使用的数据库中，每个联系记录都对应一个人物记录，因此在这种情况下，结果相当于如果使用内连接时的结果。然而，它们与前一个左连接示例的结果不同。
右外连接（Right Outer Join）
右连接遵循与左连接相同的规则，但顺序相反。现在，引用在连接子句中的表的每条记录都会出现在结果中，包括那些在另一个表中没有对应记录的记录。同样，DBMS 会用空特殊标记填充每个空的右列单元格。唯一的区别在于，表的评估顺序是反向进行的，或者换句话说，两个表的角色被交换。
-- 所有联系记录及其对应的人物数据列表，即使没有对应的人物记录
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
RIGHT JOIN contact c ON p.id = c.person_id -- 与 RIGHT OUTER JOIN 意义相同
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
全外连接（Full Outer Join）
全连接检索左表和右表中的每一行，无论在各自相反的表中是否存在对应的记录。
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
FULL JOIN contact c ON p.id = c.person_id -- 与 FULL OUTER JOIN 意义相同
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
给定下表 table_1 和 table_2：
table_1
| ID | X |
|---|---|
| 1 | 11 |
| 2 | 12 |
| 3 | 13 |
table_2
| ID | TABLE_1_ID | Y |
|---|---|---|
| 1 | 1 | 21 |
| 2 | 5 | 22 |
全连接：
SELECT *
FROM table_1 t1
FULL JOIN table_2 t2 ON t1.id = t2.table_1_id;
将产生以下结果：
| T1.ID | T1.X | T2.ID | T2.TABLE_1_ID | T2.Y |
|---|---|---|---|---|
| 1 | 11 | 1 | 1 | 21 |
| 2 | 12 | null | null | null |
| 3 | 13 | null | null | null |
| null | null | 2 | 5 | 22 |
这些结果包含了一个匹配的行，以及原始两个表中所有其他记录各一行。由于这些其他行的数据仅存在于一个表中，因此它们缺少一些数据，对应的单元格包含空特殊标记。
总结
通过规范化和使用 JOIN 操作，我们可以有效地管理和查询数据库中的数据，避免冗余，提高数据一致性，并增强查询的灵活性和效率。掌握这些基本概念和语法对于构建健壮的数据库系统至关重要。
您可以根据需要复制/粘贴这些示例，以帮助您学习和实践 SQL。
注意：并非所有 DBMS 都支持全连接（FULL JOIN）。然而，由于它不是一个原子操作，始终可以通过多个 SELECT 与 SET 操作的组合来创建所需的结果。
笛卡尔积（交叉连接）
在内连接中，可以省略 ON。SQL 会将其解释为一个语法上正确的请求，将左表的每条记录与右表的每条记录组合起来。这将返回大量的行：两个表行数的乘积。
这种特定类型的内连接称为笛卡尔积或 CROSS
JOIN。笛卡尔积是关系代数的基本操作，是所有关系型数据库管理系统（rDBMS）实现的基础。
-- 将所有人物与所有联系信息组合在一起（某些实现将关键字 'JOIN' 替换为逗号）
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
JOIN contact c -- 缺少 ON 关键字：将创建 p X c
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
-- 计算结果行数
SELECT count(*)
FROM person p
JOIN contact c;
因此要小心；如果无意中省略了 ON 条件，结果将比预期的大得多。例如，如果第一个表包含 10,000 条记录，第二个表包含 20,000 条记录，则输出将包含 200,000,000 行。
n:m 情况
我们如何创建一个人物及其爱好的列表？请记住：一个人可能有多个爱好，多个人人可能有相同的爱好。因此，人物与爱好之间没有直接连接。在这两个表之间，我们创建了第三个表 person_hobby。它包含人物的 id 以及爱好的 id。
我们必须从 person 到 person_hobby，然后再到 hobby。
-- 人物及其爱好列表
SELECT p.id p_id, p.firstname, p.lastname, h.hobbyname, h.id h_id
FROM person p
JOIN person_hobby ph ON p.id = ph.person_id
JOIN hobby h ON ph.hobby_id = h.id
ORDER BY p.lastname, p.firstname, h.hobbyname;
请注意，person_hobby 表的任何列都不会出现在结果中。该表仅在中间执行步骤中起作用。甚至它的 id 列也不感兴趣。
有些人没有爱好。由于我们执行了内连接，这些人不会出现在上述列表中。如果我们希望列表中也显示没有爱好的人，我们必须像之前一样：使用左外连接（LEFT OUTER JOIN）而不是内连接（INNER JOIN）。
-- 所有人物及其爱好（如果存在）
SELECT p.id p_id, p.firstname, p.lastname, h.hobbyname, h.id h_id
FROM person p
LEFT JOIN person_hobby ph ON p.id = ph.person_id
LEFT JOIN hobby h ON ph.hobby_id = h.id
ORDER BY p.lastname, p.firstname, h.hobbyname;
提示：如有必要，我们可以将任何类型的连接与其他类型的连接按任何所需的顺序组合，例如：LEFT OUTER 与 FULL OUTER 与 INNER 等。
更多细节
连接操作的条件不限于通常的形式：
SELECT ...
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t2.fk
...
首先，我们可以使用任何列，而不仅仅是主键和外键列。在上述某些示例中，我们使用了 lastname 进行连接。lastname 是字符类型，且没有任何键的含义。为了避免性能差，一些 DBMS 限制仅对具有索引的列使用连接。
其次，比较符不限于等号。我们可以使用任何有意义的运算符，例如数值的“大于”（>）。
-- 哪个人物的体重更大 —— 为了清晰，限制为 'de Winter'
SELECT p1.id, p1.firstname as "is heavier", p1.weight, p2.id, p2.firstname as "than", p2.weight
FROM person p1
JOIN person p2 ON p1.weight > p2.weight
WHERE p1.lastname = 'de Winter'
AND p2.lastname = 'de Winter'
ORDER BY p1.weight DESC, p2.weight DESC;
第三，我们可以使用任意函数。
-- 短姓氏 vs 长姓氏
SELECT p1.firstname, p1.lastname as "shorter lastname", p2.firstname, p2.lastname
FROM person p1
JOIN person p2 ON LENGTH(p1.lastname) < LENGTH(p2.lastname)
-- 同样，ORDER BY 也可以使用函数
ORDER BY LENGTH(p1.lastname), LENGTH(p2.lastname);
您可以根据需要复制/粘贴这些示例，以帮助您学习和实践 SQL。