数据表的联结与视图

1.实验介绍

1.1 实验内容

在本节内容中，我们将介绍数据表的联结和视图的操作。

1.2 实验知识点

  • 联结
  • 内联结和外联结
  • 视图

2. 联结

以我们上一节给出的选课数据库为例，我们在一张表中存储全部的信息会是什么样子：

学号 课程号 学生姓名 学生年龄 学生性别 课程名 课时 成绩
1001 3 shiyanlou001 10 man c 10 70
1001 1 shiyanlou001 10 man java 13 20
1001 2 shiyanlou001 4 man spark 15 90
... ... ... ... ... ... ...

大致如上所示，这里我只给出了简单的几条数据，再对比上一节内容中将选课的信息，划分为三张表进行存储，我们不用存储更多重复的信息，明显后者要高效的多。

但是划分为多张表之后，我们怎么使用 SELECT 语句从多个表中查询到我们需要的信息呢。

在上一节的内容中，我们介绍了查询的一些基础操作，并且在最后介绍了子查询，但是子查询对于多个表的信息提取也无能为力。

而在 MySQL 中，我们还有另一种更为简单的方式，就是联结（也被称为连接）。联结基于关系表，可以用来关联多个表，因此被称为联结。

以我们上一节创建的选课数据库为例，如果我们想要获得上述在一张表中呈现所有的信息，可以使用如下语句：

# 当两张表中同时存在的表项是需要以这种方式标注：表名.表项
mysql> SELECT sc.s_id,sc.c_id,s_name,c_name,grade,s_age,s_sex,c_time FROM student,course,sc WHERE student.s_id=sc.s_id AND course.c_id=sc.c_id;
+------+------+---------------+--------+-------+-------+-------+--------+
| s_id | c_id | s_name        | c_name | grade | s_age | s_sex | c_time |
+------+------+---------------+--------+-------+-------+-------+--------+
| 1001 |    1 | shiyanlou1001 | java   |    20 |    10 | man   |     13 |
| 1001 |    3 | shiyanlou1001 | c      |    70 |    10 | man   |     10 |
| 1001 |    4 | shiyanlou1001 | spark  |    96 |    10 | man   |     15 |
| 1002 |    1 | shiyanlou1002 | java   |   100 |    20 | woman |     13 |
| 1002 |    2 | shiyanlou1002 | python |    80 |    20 | woman |     12 |
| 1002 |    4 | shiyanlou1002 | spark  |    80 |    20 | woman |     15 |
| 1003 |    3 | shiyanlou1003 | c      |    75 |    18 | man   |     10 |
+------+------+---------------+--------+-------+-------+-------+--------+

让我们简化一些，只从每张表中列出其它表中关键的信息，如下所示，我们可以得到学生比较直观的选课信息：

mysql> SELECT sc.s_id, sc.c_id, s_name, c_name, grade FROM student, course, sc WHERE student.s_id=sc.s_id AND course.c_id=sc.c_id;
+------+------+---------------+--------+-------+
| s_id | c_id | s_name        | c_name | grade |
+------+------+---------------+--------+-------+
| 1001 |    3 | shiyanlou1001 | c      |    70 |
| 1001 |    1 | shiyanlou1001 | java   |    20 |
| 1001 |    4 | shiyanlou1001 | spark  |    96 |
| 1002 |    1 | shiyanlou1002 | java   |   100 |
| 1002 |    2 | shiyanlou1002 | python |    80 |
| 1002 |    4 | shiyanlou1002 | spark  |    80 |
| 1003 |    3 | shiyanlou1003 | c      |    75 |
+------+------+---------------+--------+-------+

我们也可以对数据表使用别名，跟前面介绍的别名使用方法一致，注意，为了增加可读性，建议显示的使用 AS ，对应上面查询语句的语句如下：

SELECT sc.s_id, sc.c_id, s_name, c_name, grade FROM student AS s, course AS c, sc WHERE s.s_id=sc.s_id AND c.c_id=sc.c_id;

细心的同学会发现，对于 s_id 或者 c_id 在要查询的字段中，我们给出了相应的表名，这是因为在 student 和 sc 表中都有 s_id，为了不引起歧义，指定从某一张表中读取该字段。

2.1 内联结

上面我们使用的是简单语法，需要注意的是，我们实际是将 student 表和 course 表与 sc 表建立了等值联结，即在 WHERE 语句中设定相应的条件。

对应简单语法，还可以用 SQL 的标准语法，需要用到 JOIN 来进行操作。

对于上述的使用值的相等性测试而言，为等值联结，又称为内联结，使用 ...INNER JOIN...ON 的语法格式。

  • INNER 是内部的意思，用在 JOIN 之前，指定联结格式，即为内联结，也可以直接使用 JOIN，但是大多数时候建议加上它。
  • ON 则可以使用任何可以在 WHERE 中使用的子句。使用标准语法格式的语句如下：
mysql> SELECT sc.s_id, sc.c_id, s_name, c_name, grade FROM sc INNER JOIN (student AS s, course AS c) ON s.s_id=sc.s_id AND c.c_id=sc.c_id;
+------+------+---------------+--------+-------+
| s_id | c_id | s_name        | c_name | grade |
+------+------+---------------+--------+-------+
| 1001 |    3 | shiyanlou1001 | c      |    70 |
| 1001 |    1 | shiyanlou1001 | java   |    20 |
| 1001 |    4 | shiyanlou1001 | spark  |    96 |
| 1002 |    1 | shiyanlou1002 | java   |   100 |
| 1002 |    2 | shiyanlou1002 | python |    80 |
| 1002 |    4 | shiyanlou1002 | spark  |    80 |
| 1003 |    3 | shiyanlou1003 | c      |    75 |
+------+------+---------------+--------+-------+

而对于简单的等值查询，除了 ON 之外，我们还可以使用 USING ，即 ...INNER JOIN...USING。 USING 用于指定列，并且该列在多个表中都存在，如下所示：

mysql> SELECT sc.s_id, sc.c_id, s_name, c_name, grade FROM sc INNER JOIN (student AS s, course AS c) USING(s_id,c_id);
+------+------+---------------+--------+-------+
| s_id | c_id | s_name        | c_name | grade |
+------+------+---------------+--------+-------+
| 1001 |    3 | shiyanlou1001 | c      |    70 |
| 1001 |    1 | shiyanlou1001 | java   |    20 |
| 1001 |    4 | shiyanlou1001 | spark  |    96 |
| 1002 |    1 | shiyanlou1002 | java   |   100 |
| 1002 |    2 | shiyanlou1002 | python |    80 |
| 1002 |    4 | shiyanlou1002 | spark  |    80 |
| 1003 |    3 | shiyanlou1003 | c      |    75 |
+------+------+---------------+--------+-------+
7 rows in set (0.36 sec)

在简单语法的查询中我们使用了 WHERE 进行等值判断，而对于不使用相关的限定条件内联结的结果为两个集合的笛卡儿积，如下示例，student 表中的每一行都会连接到 course 表中的每一行：

mysql> SELECT * FROM student INNER JOIN course;
+------+---------------+-------+-------+------+--------+--------+
| s_id | s_name        | s_sex | s_age | c_id | c_name | c_time |
+------+---------------+-------+-------+------+--------+--------+
| 1001 | shiyanlou1001 | man   |    10 |    1 | java   |     13 |
| 1001 | shiyanlou1001 | man   |    10 |    2 | python |     12 |
| 1001 | shiyanlou1001 | man   |    10 |    3 | c      |     10 |
| 1001 | shiyanlou1001 | man   |    10 |    4 | spark  |     15 |
| 1002 | shiyanlou1002 | woman |    20 |    1 | java   |     13 |
...
...
| 1005 | shiyanlou1005 | man   |    17 |    1 | java   |     13 |
| 1005 | shiyanlou1005 | man   |    17 |    2 | python |     12 |
| 1005 | shiyanlou1005 | man   |    17 |    3 | c      |     10 |
| 1005 | shiyanlou1005 | man   |    17 |    4 | spark  |     15 |
+------+---------------+-------+-------+------+--------+--------+
20 rows in set (0.00 sec)

对于内联结而言，我们需要使用正确的 WHERE 或者 ON 子句，得到想要的结果。

2.2 外联结

除了内联结之外，我们还可以使用外联结。以上面的查看学生选课的信息为例，我们只能查看有选课记录的学生的选课信息，有时，我们需要将未选课的学生信息也列举出来。即没有关联行的内容，这时我们就可以使用外联结。

外联结又分为左外联结和右外联结，使用的大致格式如下：

...{LEFT|RIGHT} [OUTER] JOIN...

与内联结的 INNER 对应的外联结为 OUTER ，同样，OUTER 也为可选项，我们只需指定使用左外联结 LEFT 还是使用右外联结 RIGHT 即可。

两种外联结的区别在于，LEFT 对于查看没有关联行的内容，以 JOIN 语句左侧的数据表为准，而右联结则相反。

如下所示的示例，得到相同的结果，修改 student 和 sc 表的位置，分别使用左右外联结得到相同的结果：

mysql> SELECT student.s_id,s_name,c_id,grade FROM student LEFT JOIN sc ON student.s_id=sc.s_id;
+------+---------------+------+-------+
| s_id | s_name        | c_id | grade |
+------+---------------+------+-------+
| 1001 | shiyanlou1001 |    1 |    20 |
| 1001 | shiyanlou1001 |    3 |    70 |
| 1001 | shiyanlou1001 |    4 |    96 |
| 1002 | shiyanlou1002 |    1 |   100 |
| 1002 | shiyanlou1002 |    2 |    80 |
| 1002 | shiyanlou1002 |    4 |    80 |
| 1003 | shiyanlou1003 |    3 |    75 |
| 1004 | shiyanlou1005 | NULL |  NULL |
| 1005 | shiyanlou1005 | NULL |  NULL |
+------+---------------+------+-------+
9 rows in set (0.06 sec)

mysql> SELECT student.s_id,s_name,c_id,grade FROM sc RIGHT JOIN student ON student.s_id=sc.s_id;
+------+---------------+------+-------+
| s_id | s_name        | c_id | grade |
+------+---------------+------+-------+
| 1001 | shiyanlou1001 |    1 |    20 |
| 1001 | shiyanlou1001 |    3 |    70 |
| 1001 | shiyanlou1001 |    4 |    96 |
| 1002 | shiyanlou1002 |    1 |   100 |
| 1002 | shiyanlou1002 |    2 |    80 |
| 1002 | shiyanlou1002 |    4 |    80 |
| 1003 | shiyanlou1003 |    3 |    75 |
| 1004 | shiyanlou1005 | NULL |  NULL |
| 1005 | shiyanlou1005 | NULL |  NULL |
+------+---------------+------+-------+
9 rows in set (0.00 sec)

同样，我们也可以使用 USING ，如下所示

mysql> SELECT student.s_id,s_name,c_id,grade FROM sc RIGHT JOIN student USING(s_id);
+------+---------------+------+-------+
| s_id | s_name        | c_id | grade |
+------+---------------+------+-------+
| 1001 | shiyanlou1001 |    1 |    20 |
| 1001 | shiyanlou1001 |    3 |    70 |
| 1001 | shiyanlou1001 |    4 |    96 |
| 1002 | shiyanlou1002 |    1 |   100 |
| 1002 | shiyanlou1002 |    2 |    80 |
| 1002 | shiyanlou1002 |    4 |    80 |
| 1003 | shiyanlou1003 |    3 |    75 |
| 1004 | shiyanlou1005 | NULL |  NULL |
| 1005 | shiyanlou1005 | NULL |  NULL |
+------+---------------+------+-------+
9 rows in set (0.00 sec)

2.3 自然联结

在之前使用联结的示例中，我们都是查询的指定字段，并没有使用 去查询所有字段，如下所示，我们使用 查询所有字段：

mysql> SELECT * FROM course JOIN sc;
+------+--------+--------+------+------+-------+
| c_id | c_name | c_time | s_id | c_id | grade |
+------+--------+--------+------+------+-------+
|    1 | java   |     13 | 1001 |    1 |    20 |
|    2 | python |     12 | 1001 |    1 |    20 |
|    3 | c      |     10 | 1001 |    1 |    20 |
...

如上所示，我们看到有两个 c_id 的列出现，对于这种情况，我们可以使用自然联结（NATURAL），它会将多个表中同时出现的列作为联结的标准，即关联的行：

mysql> SELECT * FROM course NATURAL JOIN sc;
+------+--------+--------+------+-------+
| c_id | c_name | c_time | s_id | grade |
+------+--------+--------+------+-------+
|    1 | java   |     13 | 1001 |    20 |
|    1 | java   |     13 | 1002 |   100 |
|    2 | python |     12 | 1002 |    80 |
|    3 | c      |     10 | 1001 |    70 |
|    3 | c      |     10 | 1003 |    75 |
|    4 | spark  |     15 | 1001 |    96 |
|    4 | spark  |     15 | 1002 |    80 |
+------+--------+--------+------+-------+
7 rows in set (0.00 sec)

对于上述使用自然联结的方式，在语义上和下面这种方式等同，如下：

SELECT * FROM course INNER JOIN sc USING(c_id);

或者如下的语句也能得到同样的结果：

SELECT * FROM course INNER JOIN sc USING(c_id) INNER JOIN student USING(s_id);
SELECT * FROM course NATURAL JOIN sc NATURAL JOIN student;

表的联结操作视频：

3. 视图

在上面的内容中，我们通过使用联结来获取相关的信息。如果我们需要经常使用上述查询的内容，在 MySQL 中，可以通过定义视图来实现。

视图（View）是从一个或多个表（这里的表指基本表和视图）导出的表。为了区分视图和表，所以表有时又被称为“基本表”。

对于视图来说，数据库中只保存有视图的定义，而通过视图获得的数据，都来自与它相关的基本表，视图本身是没有数据的。因此，如果我们对视图的数据进行操作，其实也就是对基本表的数据进行操作，而这种操作也是有一定的限制。

3.1 创建视图

通过之前的学习我们可以观察到，在使用查询语句进行查询时，返回的结果以一种二维表的形式存在，而视图就是在它的基础上创建的。如下所示，创建视图的基本语法：

CREATE [OR REPLACE] VIEW view_name [(column_list)]
    AS select_statement

view_name 代表视图的名字，select_statement 代指的是查询语句。[OR REPLACE] 可选项代表如果视图已存在，则替换它。

对于视图的列，我们可以为其定义相关的名字，为可选项 (column_list)，如果未使用该选项，则视图的列为使用 select_statement 语句检索的列名。因此定义 (column_list) 时，也需与检索的列一一对应。

如下所示，我们根据上面的联结查询创建一个名为 all_info 的视图了：

mysql> CREATE VIEW all_info AS SELECT sc.s_id,sc.c_id,s_name,c_name,grade,s_age,s_sex,c_time FROM student,course,sc WHERE student.s_id=sc.s_id AND course.c_id=sc.c_id;
Query OK, 0 rows affected (0.25 sec)

这时如果我们需要获取所有的信息，就可以直接通过视图 all_info 去操作了，如下所示：

mysql> SELECT * FROM all_info;
+------+------+---------------+--------+-------+-------+-------+--------+
| s_id | c_id | s_name        | c_name | grade | s_age | s_sex | c_time |
+------+------+---------------+--------+-------+-------+-------+--------+
| 1001 |    1 | shiyanlou1001 | java   |    20 |    10 | man   |     13 |
| 1001 |    3 | shiyanlou1001 | c      |    70 |    10 | man   |     10 |
| 1001 |    4 | shiyanlou1001 | spark  |    96 |    10 | man   |     15 |
| 1002 |    1 | shiyanlou1002 | java   |   100 |    20 | woman |     13 |
| 1002 |    2 | shiyanlou1002 | python |    80 |    20 | woman |     12 |
| 1002 |    4 | shiyanlou1002 | spark  |    80 |    20 | woman |     15 |
| 1003 |    3 | shiyanlou1003 | c      |    75 |    18 | man   |     10 |
+------+------+---------------+--------+-------+-------+-------+--------+
7 rows in set (0.00 sec)

这时我们也可以通过 DESC view_name 去查看视图的列以及使用 SHOW CREATE VIEW view_name 去查看视图的创建语句。

如下所示：

mysql> DESC all_info;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| s_id   | int(11)             | NO   |     | 0       |       |
| c_id   | int(11)             | NO   |     | 0       |       |
| s_name | varchar(20)         | NO   |     | NULL    |       |
| c_name | varchar(20)         | NO   |     | NULL    |       |
| grade  | int(11)             | YES  |     | NULL    |       |
| s_age  | int(11)             | NO   |     | NULL    |       |
| s_sex  | enum('man','woman') | YES  |     | man     |       |
| c_time | int(11)             | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+

在视图被定义后，我们可以像使用基本表一样进行查询，修改，删除，和更新等操作。

但是视图的定义也会受到一些限制，如下：

  • 不能在 select_statement 包含子查询（该限制为 MySQL 5.7.7 版本之前，5.7.7 之后无该限制）
  • 定义不能引用一个临时表（TMPPORARY)
  • 在定义视图之后删除定义时引用的表或视图，将会导致视图不可用
  • 在定义时 select_statement 不能引用系统变量以及用户自定义的变量

3.2 修改视图定义

对于视图定义的修改可以考虑直接使用替换创建，或者使用下面的修改语句，两者并无太大区别：

ALTER VIEW view_name [(column_list)] AS select_statement

如下所示，修改 all_info 的定义：

mysql> ALTER VIEW all_info AS SELECT sc.s_id, sc.c_id, s_name, c_name, grade FROM student, course, sc WHERE student.s_id=sc.s_id AND course.c_id=sc.c_id;
Query OK, 0 rows affected (0.02 sec)

mysql> DESC all_info;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| s_id   | int(11)     | NO   |     | 0       |       |
| c_id   | int(11)     | NO   |     | 0       |       |
| s_name | varchar(20) | NO   |     | NULL    |       |
| c_name | varchar(20) | NO   |     | NULL    |       |
| grade  | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

对比上面的内容，我们可以看到视图的定义被修改

3.3 增删改

对于视图的查询操作，我们可以像使用基本表一样进行查询。

但是对于视图中数据的更新操作（增删改），会有一定的区别。由于对视图中的数据进行更新操作，其实就是更新与其相关的基本表的数据，所以只有对满足可更新条件的视图才能进行更新操作。

因此，视图又可以分为可更新视图和不可更新视图。可更新视图中，视图的行必须与基本表中的行有一对一的对应关系。

而对于一个视图中只要包含下列任何一种，它都是不可更新的：

  • 聚合函数
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION
  • 子查询
  • ...

3.4 删除视图

查看数据库中存在的视图我们可以使用前面介绍的 SHOW [FULL] TABLES 语句，如下所示：

mysql> SHOW TABLES;
+------------------------+
| Tables_in_shiyanlou001 |
+------------------------+
| all_info               |
| course                 |
| sc                     |
| student                |
+------------------------+
4 rows in set (0.00 sec)

mysql> SHOW FULL TABLES;
+------------------------+------------+
| Tables_in_shiyanlou001 | Table_type |
+------------------------+------------+
| all_info               | VIEW       |
| course                 | BASE TABLE |
| sc                     | BASE TABLE |
| student                | BASE TABLE |
+------------------------+------------+

删除视图的语法为：

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...

例如，这里我们删除上面创建的 all_info 视图：

mysql> DROP VIEW all_info;
Query OK, 0 rows affected (0.08 sec)

mysql> show full tables;
+------------------------+------------+
| Tables_in_shiyanlou001 | Table_type |
+------------------------+------------+
| course                 | BASE TABLE |
| sc                     | BASE TABLE |
| student                | BASE TABLE |
+------------------------+------------+

表的视图操作视频：

4. 总结

results matching ""

    No results matching ""