Level 5 多表单联合

PreviousLevel 4 时间 Next外键

Last updated 4 years ago

Was this helpful?

Level 5 多表单联合

1. FOREIGN KEY

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

让我们通过一个实例来解释外键。请看下面两个表：

"teachers" 教师表：

"courses" 课程表：

注意:

"courses" 表中的 "teacher_id" 列指向 "teachers" 表中的 "id" 列。

"teachers" 表中的 "id" 列是 "teachers" 表中的 PRIMARY KEY。

"courses" 表中的 "teacher_id" 列是 "courses" 表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的行为。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

1.1 创建表时创建 FOREIGN KEY 约束

下面的 SQL 在 "courses" 表创建时在 "teacher_id" 列上创建 FOREIGN KEY 约束：

MySQL

CREATE TABLE courses
  (
     id       INT NOT NULL,
     name     VARCHAR(64) NOT NULL,
     student_count   INT NOT NULL,
     created_at      DATE NOT NULL,
     teacher_id       INT NOT NULL,
     PRIMARY KEY (id),
     FOREIGN KEY (teacher_id) REFERENCES teachers(id)
  )

SQL Server/ Oracle/ MS Access

CREATE TABLE courses
  (
     id       INT NOT NULL PRIMARY KEY,
     name     VARCHAR(64) NOT NULL,
     student_count   INT NOT NULL,
     created_at      DATE NOT NULL,
     teacher_id       INT FOREIGN KEY REFERENCES teachers(id)
  )

其中

NOT NULL 表示该字段不为空

REFERENCES 表示引用一个表

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE courses
  (
     id       INT NOT NULL,
     name     VARCHAR(64) NOT NULL,
     student_count   INT NOT NULL,
     created_at      DATE NOT NULL,
     teacher_id       INT NOT NULL,
     PRIMARY KEY (id),
     CONSTRAINT fk_teachers FOREIGN KEY (teacher_id) REFERENCES teachers(id)
  )

其中

CONSTRAINT 表示约束，后面接约束名称，常用于创建约束和删除约束。

1.2 创建表后通过 ALTER TABLE 创建 FOREIGN KEY 约束

当 "courses" 表已被创建时，如需在 "course_id" 列创建 FOREIGN KEY 约束，请使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE courses
ADD FOREIGN KEY (teacher_id) REFERENCES teachers(id)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE courses
ADD CONSTRAINT fk_teachers FOREIGN KEY (teacher_id) REFERENCES teachers(id)

1.3 撤销 FOREIGN KEY 约束

如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：

MySQL

ALTER TABLE courses
DROP FOREIGN KEY fk_teachers

SQL Server / Oracle / MS Access

ALTER TABLE courses
DROP CONSTRAINT fk_teachers

2. AS

通过使用 SQL，可以为表名称或列名称指定别名。

基本上，创建别名是为了让列名称的可读性更强。

列的SQL别名语法

SELECT column_name AS alias_name
FROM table_name;

表的SQL别名语法

SELECT column_name(s)
FROM table_name AS alias_name;

AS 关键词可以忽略不写，用空格隔开即可。

演示数据库

在本教程中，我们将使用 LintCode 样本数据库。

下面是选自 "teachers" 教师表的数据：

下面是 "courses" 课程表的数据：

2.1 列的别名实例Ⅰ

下面的 SQL 语句指定了两个别名，一个是 name 列的别名为 ‘ teacher_name’，一个是 country 列的别名为 ‘ teacher_country’。

提示：

如果别名包含空格，要求使用双引号或方括号，建议不使用空格，而使用 '_'。

SELECT name AS teacher_name, country teacher_name
FROM teachers;

执行结果输出

mysql> SELECT name AS teacher_name, country teacher_name
    -> FROM teachers;
+------------------+--------------+
| teacher_name     | teacher_name |
+------------------+--------------+
| Eastern Heretic  | CN           |
| Northern Beggar  | CN           |
| Western Venom    | USA          |
| Southern Emperor | CN           |
| Linghu Chong     | CN           |
+------------------+--------------+
5 rows in set (0.00 sec)

练习题：列的别名Ⅰ

查询所有教师的 id 和姓名（name），并为 name 起别名为 teacher_name。

SELECT id, name AS teacher_name
FROM teachers;

执行结果输出

+----+------------------+
| id | teacher_name     |
+----+------------------+
|  1 | Eastern Heretic  |
|  2 | Northern Beggar  |
|  3 | Western Venom    |
|  4 | Southern Emperor |
|  5 | Linghu Chong     |
+----+------------------+

2.2 列的别名实例Ⅱ

在下面的 SQL 语句中，我们把两个列（age 和 country）结合在一起，并创建一个名为 "teacher_info" 的别名。

SELECT name, CONCAT(age, ', ', country) AS teacher_info
FROM teachers;

其中

CONCAT 方法可以将所有参数进行拼接成一个字符串。

执行结果输出

mysql> SELECT name, CONCAT(age, ', ', country) AS teacher_info
    -> FROM teachers;
+------------------+--------------+
| name             | teacher_info |
+------------------+--------------+
| Eastern Heretic  | 20, CN       |
| Northern Beggar  | 21, CN       |
| Western Venom    | 28, USA      |
| Southern Emperor | 21, CN       |
| Linghu Chong     | 18, CN       |
+------------------+--------------+
5 rows in set (0.00 sec)

练习题：列的别名Ⅱ

查询所有教师的 id 和姓名（name）和 age，将 age 后面拼接一个字符 ‘ 岁’ ，并为 age 起别名为 teacher_age

SELECT id, name, CONCAT(age, '岁') AS teacher_age
FROM teachers;

执行结果输出

+----+------------------+-------------+
| id | name             | teacher_age |
+----+------------------+-------------+
|  1 | Eastern Heretic  | 20岁        |
|  2 | Northern Beggar  | 21岁        |
|  3 | Western Venom    | 28岁        |
|  4 | Southern Emperor | 21岁        |
|  5 | Linghu Chong     | 18岁        |
+----+------------------+-------------+

2.3 表的别名实例

下面的 SQL 语句查询所有课程的 id ，课程名和对应教师的姓名。我们使用 "teachers" 和 "courses" 表，并分别为它们指定表别名 "t" 和 "c"（通过使用别名让 SQL 更简短）：

-- 不使用别名写法 --
SELECT courses.id, courses.name, teachers.name
FROM teachers, courses where teachers.id = courses.teacher_id;

-- 使用别名的写法 --
SELECT c.id, c.name, t.name
FROM teachers t, courses c where t.id = c.teacher_id;

执行输出结果

mysql> SELECT c.id, c.name, t.name
    -> FROM teachers t, courses c where t.id = c.teacher_id;
+----+-------------------------+------------------+
| id | name                    | name             |
+----+-------------------------+------------------+
|  1 | Senior Algorithm        | Southern Emperor |
|  2 | System Design           | Western Venom    |
|  3 | Django                  | Western Venom    |
|  4 | Web                     | Southern Emperor |
|  5 | Big Data                | Eastern Heretic  |
|  6 | Artificial Intelligence | Western Venom    |
|  7 | Java P6+                | Western Venom    |
|  8 | Data Analysis           | Eastern Heretic  |
| 10 | Object Oriented Design  | Southern Emperor |
| 12 | Dynamic Programming     | Eastern Heretic  |
+----+-------------------------+------------------+
10 rows in set (0.00 sec)

练习题：表的别名

查询所有课程的 id ，课程名和对应教师的姓名。我们使用 "teachers" 和 "courses" 表，并分别为它们指定表别名 "t" 和 "c"，因为课程名和教师姓名的列名都是 'name' ，要求为课程名起别名为 'course_name'，为教师姓名起别名为 'teacher_name' ：

SELECT c.id, c.name course_name, t.name teacher_name
FROM teachers t, courses c where t.id = c.teacher_id;

执行输出结果

+----+-------------------------+------------------+
| id | course_name             | teacher_name     |
+----+-------------------------+------------------+
|  1 | Senior Algorithm        | Southern Emperor |
|  2 | System Design           | Western Venom    |
|  3 | Django                  | Western Venom    |
|  4 | Web                     | Southern Emperor |
|  5 | Big Data                | Eastern Heretic  |
|  6 | Artificial Intelligence | Western Venom    |
|  7 | Java P6+                | Western Venom    |
|  8 | Data Analysis           | Eastern Heretic  |
| 10 | Object Oriented Design  | Southern Emperor |
| 12 | Dynamic Programming     | Eastern Heretic  |
+----+-------------------------+------------------+

2.4 AS 总结

在下面的情况下，使用别名很有用：

在查询中涉及超过一个表
在查询中使用了函数
列名称很长或者可读性差
需要把两个列或者多个列结合在一起

3. JOIN

SQL JOIN 连接子句用于将数据库中两个或者两个以上表中的记录组合起来。

不同的 SQL JOIN

在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：

INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

3.1 INNER JOIN

最常用也最重要的连接形式是内连接，有时候也被称作“EQUIJOIN”（等值连接）。

内连接根据连接谓词来组合两个表中的字段，以创建一个新的结果表。SQL 查询会比较逐个比较表 1 和表 2 中的每一条记录，来寻找满足连接谓词的所有记录对。当连接谓词得以满足时，所有满足条件的记录对的字段将会结合在一起构成结果表。

3.1.1 INNER JOIN 基本语法

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

或

SELECT table1.column1, table2.column2...
FROM table1
JOIN table2
ON table1.common_field = table2.common_field;

INNER JOIN 中 INNER 可以省略不写

演示数据库

在本教程中，我们将使用 LintCode 样本数据库。

下面是选自 "teachers" 表的数据：

下面是 "courses" 课程表的数据：

3.1.2 INNER JOIN 实例

请注意，"courses" 表中的 "teacher_id" 列指向 "teachers" 表中的字段 "id"。上面这两个表是通过 "teacher_id" 列联系起来的。

然后，如果我们运行下面的 SQL 语句（包含 INNER JOIN）：

SELECT c.id, c.name AS course_name, t.name AS teacher_name
FROM courses c
    INNER JOIN teachers t ON c.teacher_id = t.id;

执行输出结果

mysql> SELECT c.id, c.name AS course_name, t.name AS teacher_name
    -> FROM courses c
    ->  INNER JOIN teachers t ON c.teacher_id = t.id;
+----+-------------------------+------------------+
| id | course_name             | teacher_name     |
+----+-------------------------+------------------+
|  1 | Senior Algorithm        | Southern Emperor |
|  2 | System Design           | Western Venom    |
|  3 | Django                  | Western Venom    |
|  4 | Web                     | Southern Emperor |
|  5 | Big Data                | Eastern Heretic  |
|  6 | Artificial Intelligence | Western Venom    |
|  7 | Java P6+                | Western Venom    |
|  8 | Data Analysis           | Eastern Heretic  |
| 10 | Object Oriented Design  | Southern Emperor |
| 12 | Dynamic Programming     | Eastern Heretic  |
+----+-------------------------+------------------+
10 rows in set (0.05 sec)

练习题：INNER JOIN

查询 “Eastern Heretic” 老师所教的所有课程的课程名和课程id。

代码输入框：

SELECT c.id, c.name AS course_name, t.name AS teacher_name
FROM teachers t
    INNER JOIN courses c ON c.teacher_id = t.id AND t.name = 'Eastern Heretic';

执行输出结果

+----+---------------------+-----------------+
| id | course_name         | teacher_name    |
+----+---------------------+-----------------+
|  5 | Big Data            | Eastern Heretic |
|  8 | Data Analysis       | Eastern Heretic |
| 12 | Dynamic Programming | Eastern Heretic |
+----+---------------------+-----------------+

3.2 LEFT JOIN

左连接返回左表中的所有记录，即使右表中没有任何满足匹配条件的记录。这意味着，如果 ON 子句在右表中匹配到了 0 条记录，该连接仍然会返回至少一条记录，不过返回的记录中所有来自右表的字段都为 NULL。

这就意味着，左连接会返回左表中的所有记录，加上右表中匹配到的记录，或者是 NULL （如果连接谓词无法匹配到任何记录的话）。

3.2.1 LEFT JOIN 基本语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

演示数据库

在本教程中，我们将使用 LintCode 样本数据库。

下面是选自 "teachers" 教师表的数据：

下面是 "courses" 课程表的数据：

3.2.2 LEFT JOIN 实例

下面的 SQL 语句将返回所有教师的名称和所教的课程名称 (没有教授课程则课程名为 NULL)。

以下实例中我们把 teachers 作为左表，courses作为右表：

SELECT c.name AS course_name, t.name AS teacher_name
FROM teachers t
    LEFT JOIN courses c ON c.teacher_id = t.id;

执行输出结果

mysql> SELECT c.name AS course_name, t.name AS teacher_name
    -> FROM teachers t
    ->  LEFT JOIN courses c ON c.teacher_id = t.id;
+-------------------------+------------------+
| course_name             | teacher_name     |
+-------------------------+------------------+
| Big Data                | Eastern Heretic  |
| Data Analysis           | Eastern Heretic  |
| Dynamic Programming     | Eastern Heretic  |
| NULL                    | Northern Beggar  |
| System Design           | Western Venom    |
| Django                  | Western Venom    |
| Artificial Intelligence | Western Venom    |
| Java P6+                | Western Venom    |
| Senior Algorithm        | Southern Emperor |
| Web                     | Southern Emperor |
| Object Oriented Design  | Southern Emperor |
| NULL                    | Linghu Chong     |
+-------------------------+------------------+
12 rows in set (0.06 sec)

练习题：LEFT JOIN

使用 LEFT JOIN 查询所有 ' country' 等于 'CN' 的教师的名称和所教的课程名称 (没有教授课程则课程名为 NULL)。

代码输入框：

SELECT c.name AS course_name, t.name AS teacher_name
FROM teachers t
    LEFT JOIN courses c ON c.teacher_id = t.id
WHERE t.country = 'CN';

执行输出结果

+------------------------+------------------+
| course_name            | teacher_name     |
+------------------------+------------------+
| Big Data               | Eastern Heretic  |
| Data Analysis          | Eastern Heretic  |
| Dynamic Programming    | Eastern Heretic  |
| NULL                   | Northern Beggar  |
| Senior Algorithm       | Southern Emperor |
| Web                    | Southern Emperor |
| Object Oriented Design | Southern Emperor |
| NULL                   | Linghu Chong     |
+------------------------+------------------+

3.3 RIGHT JOIN

右连接返回右表中的所有记录，即是左表中没有任何满足匹配条件的记录。这意味着，如果 ON 子句在左表中匹配到了 0 条记录，该连接仍然会返回至少一条记录，不过返回的记录中所有来自左表的字段都为 NULL。

这就意味着，右连接会返回右表中的所有记录，加上左表中匹配到的记录，或者是 NULL （如果连接谓词无法匹配到任何记录的话）。

3.3.1 RIGHT JOIN 基本语法

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

演示数据库

在本教程中，我们将使用 LintCode 样本数据库。

下面是选自 "teachers" 教师表的数据：

下面是 "courses" 课程表的数据：

3.3.2 RIGHT JOIN 实例

下面的 SQL 语句将返回所有教师的名称和所教的课程名称 (没有教授课程则课程名为 NULL)。

以下实例中我们把 courses 作为左表，teachers 作为右表：

SELECT c.name AS course_name, t.name AS teacher_name
FROM courses c
    RIGHT JOIN teachers t ON c.teacher_id = t.id;

执行输出结果

mysql> SELECT c.name AS course_name, t.name AS teacher_name
    -> FROM courses c
    ->  RIGHT JOIN teachers t ON c.teacher_id = t.id;
+-------------------------+------------------+
| course_name             | teacher_name     |
+-------------------------+------------------+
| Big Data                | Eastern Heretic  |
| Data Analysis           | Eastern Heretic  |
| Dynamic Programming     | Eastern Heretic  |
| NULL                    | Northern Beggar  |
| System Design           | Western Venom    |
| Django                  | Western Venom    |
| Artificial Intelligence | Western Venom    |
| Java P6+                | Western Venom    |
| Senior Algorithm        | Southern Emperor |
| Web                     | Southern Emperor |
| Object Oriented Design  | Southern Emperor |
| NULL                    | Linghu Chong     |
+-------------------------+------------------+
12 rows in set (0.00 sec)

练习题：RIGHT JOIN

使用 RIGHT JOIN 查询所有 ' country' 等于 'CN' 的教师的名称和所教的课程名称 (没有教授课程则课程名为 NULL)。

代码输入框：

SELECT c.name AS course_name, t.name AS teacher_name
FROM  courses c
    RIGHT JOIN teachers t ON c.teacher_id = t.id
WHERE t.country = 'CN';

执行输出结果

+------------------------+------------------+
| course_name            | teacher_name     |
+------------------------+------------------+
| Big Data               | Eastern Heretic  |
| Data Analysis          | Eastern Heretic  |
| Dynamic Programming    | Eastern Heretic  |
| NULL                   | Northern Beggar  |
| Senior Algorithm       | Southern Emperor |
| Web                    | Southern Emperor |
| Object Oriented Design | Southern Emperor |
| NULL                   | Linghu Chong     |
+------------------------+------------------+

3.4 OUTER JOIN

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

注：MySQL 数据库不支持全连接，想要实现全连接可以使用 UNION ALL 来将左连接和右连接结果组合在一起

3.4.1 OUTER JOIN 基本语法

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

演示数据库

在本教程中，我们将使用 LintCode 样本数据库。

下面是选自 "teachers" 教师表的数据：

下面是 "courses" 课程表的数据：

3.4.2 OUTTER JOIN 实例

下面的 SQL 语句查询所有的课程姓名和对应的教师姓名。

MySQL中不支持 FULL OUTER JOIN，你可以在 SQL Server 测试以下实例。

SELECT c.name AS course_name, t.name AS teacher_name
FROM courses c
    FULL OUTER JOIN teachers t ON c.teacher_id = t.id;

注释：FULL OUTER JOIN 关键字返回左表（courses）和右表（teachers）中所有的行。如果 "teachers" 表中的行在 "courses" 中没有匹配或者 "courses" 表中的行在 "teachers" 表中没有匹配，也会列出这些行。

OUTER JOIN 在 MySQL 中的写法，可以通过 UNION 合并LEFT JOIN 和

SELECT c.name AS course_name, t.name AS teacher_name
FROM courses c
    LEFT JOIN teachers t ON c.teacher_id = t.id
UNION
SELECT c.name AS course_name, t.name AS teacher_name
FROM courses c
    RIGHT JOIN teachers t ON c.teacher_id = t.id;

执行输出结果

mysql> SELECT c.name AS course_name, t.name AS teacher_name
    -> FROM courses c
    ->  LEFT JOIN teachers t ON c.teacher_id = t.id
    -> UNION
    -> SELECT c.name AS course_name, t.name AS teacher_name
    -> FROM courses c
    ->  RIGHT JOIN teachers t ON c.teacher_id = t.id;
+-------------------------+------------------+
| course_name             | teacher_name     |
+-------------------------+------------------+
| Senior Algorithm        | Southern Emperor |
| System Design           | Western Venom    |
| Django                  | Western Venom    |
| Web                     | Southern Emperor |
| Big Data                | Eastern Heretic  |
| Artificial Intelligence | Western Venom    |
| Java P6+                | Western Venom    |
| Data Analysis           | Eastern Heretic  |
| Object Oriented Design  | Southern Emperor |
| Dynamic Programming     | Eastern Heretic  |
| NULL                    | Northern Beggar  |
| NULL                    | Linghu Chong     |
+-------------------------+------------------+
12 rows in set (0.01 sec)

PreviousLevel 4 时间 Next外键

Last updated 4 years ago

Was this helpful?