MySQL 查询命令（二）

hjhcos

已于 2022-06-18 18:00:12 修改

阅读量2.5k

点赞数

分类专栏： SQL 文章标签： mysql 数据库

于 2022-03-09 00:00:10 首次发布

hjhcos

本文链接：https://blog.csdn.net/hjh_cos/article/details/123361290

版权

SQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

文章目录

📚前言
📚多表查询
📚视图
- 📕流程图
- 💡通过视图实现子查询
参考资料

📚前言

在前面我记录了一下如何进行单表查询，如果还没了解可以点这个链接哟 MySQL查询命令。

这篇文章我将会继续巩固 MySQL 查询命令的使用，也将会为你介绍如何多表查询、子查询、视图创建 so on。

📚多表查询

多表查询可以让分散的数据通过特定条件汇集在一起，以达到将你所关注数据（数据来自不同的表）汇聚在一起。

📕笛卡尔集

集合的一种
假设 A 和 B 都是集合，A 和 B 的笛卡尔积用 A X B 来表示，是所有有序偶（a,b）的集合，其中 a 属于 A ， b 属于 B。

可能上面的说法并不是很好了解，但是多表查询就是在这个基础上进行条件筛选从而满足你的查询需求，为了让你理解下面用个例子来说明！

假设我有两个表分别为 A 表和 B 表，A 表的字段为 name、age、sex，B 表的字段为 name、type 且 A 表的字段 name 与 B 表的字段 name 相互对应上。

mysql> select * from java.A;
+--------+------+------+
| name   | age  | sex  |
+--------+------+------+
| hjhcos |   12 | 0    |
| hjh    |   13 | 1    |
| cos    |   14 | 1    |
| cn     |   15 | 0    |
+--------+------+------+
4 rows in set (0.00 sec)

mysql> select * from java.B;
+--------+------+
| name   | type |
+--------+------+
| hjhcos | 1    |
| hjh    | 1    |
| cos    | 0    |
| cn     | 0    |
+--------+------+
4 rows in set (0.00 sec)

这两个表的每条数据用 a 和 b 进行表示，例如 hjhcos, 12, 0 为 a，hjhcos, 1 为 b。

mysql> select * from java.A, java.B;
+--------+------+------+--------+------+
| name   | age  | sex  | name   | type |
+--------+------+------+--------+------+
| hjhcos |   12 | 0    | hjhcos | 1    |
| hjh    |   13 | 1    | hjhcos | 1    |
| cos    |   14 | 1    | hjhcos | 1    |
| cn     |   15 | 0    | hjhcos | 1    |
| hjhcos |   12 | 0    | hjh    | 1    |
| hjh    |   13 | 1    | hjh    | 1    |
| cos    |   14 | 1    | hjh    | 1    |
| cn     |   15 | 0    | hjh    | 1    |
| hjhcos |   12 | 0    | cos    | 0    |
| hjh    |   13 | 1    | cos    | 0    |
| cos    |   14 | 1    | cos    | 0    |
| cn     |   15 | 0    | cos    | 0    |
| hjhcos |   12 | 0    | cn     | 0    |
| hjh    |   13 | 1    | cn     | 0    |
| cos    |   14 | 1    | cn     | 0    |
| cn     |   15 | 0    | cn     | 0    |
+--------+------+------+--------+------+
16 rows in set (0.00 sec)

现在我将两个表的数据汇聚在一起就会产生新的数据且数据量也变大了。

通过观察每条数据你会发现，A 表的每行数据与 B 表的每行数据搭配在一起，其数据的总数量正好是 A 的数据量乘以 B 的数据量，即 4 X 4 = 16。

这就是 A 表与 B 表产生的笛卡尔集。

显然 A 表和 B 表产生的笛卡尔集的数据并不正确，例如 A 表的字段 name 和 B 表字段的 name 对应不上，因此需要对数据进行筛选。

📕条件表达式筛选

多表查询如果希望数据正常显示，那么查询条件不能小于表的个数 - 1。

根据上面的描述，我需要将 A 表的 name 与 B 表的 name 不匹配的数据都过滤掉。

mysql> select * from java.A, java.B where java.A.name = java.B.name;
+--------+------+------+--------+------+
| name   | age  | sex  | name   | type |
+--------+------+------+--------+------+
| hjhcos |   12 | 0    | hjhcos | 1    |
| hjh    |   13 | 1    | hjh    | 1    |
| cos    |   14 | 1    | cos    | 0    |
| cn     |   15 | 0    | cn     | 0    |
+--------+------+------+--------+------+
4 rows in set (0.00 sec)

显然这样数据量没有那么多了且两个表的 name 不匹配的问题也解决了，但是相同的 name 只需要显示一个就行了，因此这里还需要改进一下。

mysql> select java.A.name, java.A.age, java.A.sex, java.B.type  from java.A, java.B where java.A.name = java.B.name;
+--------+------+------+------+
| name   | age  | sex  | type |
+--------+------+------+------+
| hjhcos |   12 | 0    | 1    |
| hjh    |   13 | 1    | 1    |
| cos    |   14 | 1    | 0    |
| cn     |   15 | 0    | 0    |
+--------+------+------+------+
4 rows in set (0.00 sec)

📕子查询

如果我想知道和 hjhcos 的 type 的值相同的有哪些并且需要显示 A 表对应的数据，那么就可以利用子查询来获取。

mysql> select * from java.A, java.B 
    -> where java.B.type in (
    -> select java.B.type from java.B where java.B.name = 'hjhcos') 
    -> and java.A.name = java.B.name;
+--------+------+------+--------+------+
| name   | age  | sex  | name   | type |
+--------+------+------+--------+------+
| hjhcos |   12 | 0    | hjhcos | 1    |
| hjh    |   13 | 1    | hjh    | 1    |
+--------+------+------+--------+------+
2 rows in set (0.00 sec)

在上面的 SQL 语句中，可以将小括号里面的语句单独提出来使用，你会发现它是一个查询 hjhcos 的 type 的值是什么的 SQL 语句。

mysql> select * from java.A, java.B 
    -> where (java.B.type, java.A.sex) in (
    -> select java.B.type, java.A.sex from java.B, java.A  where java.B.name = 'hjhcos' and java.A.name = 'hjhcos')
    -> and java.A.name = java.B.name;
+--------+------+------+--------+------+
| name   | age  | sex  | name   | type |
+--------+------+------+--------+------+
| hjhcos |   12 | 0    | hjhcos | 1    |
+--------+------+------+--------+------+
1 row in set (0.01 sec)

如果查询 hjhcos 的 sex 对应的值来进行筛别拥有相同 type 和 sex 值的数据，那么你将需要在 select java.B.type from java.B ...' 这条语句中添加查询字段select java.B.type, java.A.sex from java.B, java.A...。
在这里插入图片描述
如果添加了不需要的字段，SQL 就会报错并提醒你查询的字段的个数。

mysql> select * from (select * from java.A) as A_copy;
+--------+------+------+
| name   | age  | sex  |
+--------+------+------+
| hjhcos |   12 | 0    |
| hjh    |   13 | 1    |
| cos    |   14 | 1    |
| cn     |   15 | 0    |
+--------+------+------+
4 rows in set (0.00 sec)

子查询可以构建 Derived Table，将其应用到表里面，需要注意的是每个 Derived Table 都需要 alias（即通过 as 取得别名）。

📚视图

虚拟表

虚拟表的数据是由实际表（一个或多个）通过某些查询条件来创建数据，也可以由虚拟表（一个或多个）通过某些查询条件来创建数据。

通过上面的方式查询数据，发现代码非常长且不方便理解，那么有没有什么办法可以解决这个问题❓

这个时候视图就登场了。

📕流程图

创建视图

create view 视图名 as select语句;

查看视图

DESC 视图名;

查询数据

show * from 视图名;

修改视图

alter view 视图名 as select语句;

删除视图

drop view 视图名;

💡通过视图实现子查询

在查询与 hjhcos 拥有相同 type 和 sex 值的数据时，我需要先获取到 hjhcos 对应的 type 和 sex 的值才能查询到与 hjhcos 相同 type 和 sex 值的数据，可阅读性比较差。

现在我可以通过视图将子查询创建的数据集转换为虚拟表，简化实际使用时的重复代码。

mysql> create view java.A_B_TS_view as select java.B.name, java.B.type, java.A.sex from
java.B, java.A  where java.B.name = 'hjhcos' and java.A.name = java.B.name;
Query OK, 0 rows affected (0.00 sec)

使用视图时，需要先创建视图。

mysql> select * from java.A_B_TS_view;
+--------+------+------+
| name   | type | sex  |
+--------+------+------+
| hjhcos | 1    | 0    |
+--------+------+------+
1 row in set (0.00 sec)

接下来可以看到 SQL 语句简短了不少，这样将 hjhcos 的 type 和 sex 的值聚焦在一起了。

mysql> select * from java.A, java.B where
    -> (java.A.sex, java.B.type) = (select sex, type from java.A_B_TS_view)
    -> and java.A.name = java.B.name;
+--------+------+------+--------+------+
| name   | age  | sex  | name   | type |
+--------+------+------+--------+------+
| hjhcos |   12 | 0    | hjhcos | 1    |
+--------+------+------+--------+------+
1 row in set (0.00 sec)