mysql入门_多表查询

2022-09-22 16:48:33

多表查询

什么是/为什么要多表查询:

需要得到的结果在多张表中，且这些表的数据之间需要关联。数据库表结构设计有约定的范式，可以自行查询。

比如要查询学生的成绩信息就需要使用到student和score两张表才能完整展示学生的所有信息以及该学生对应课程的成绩。

多表查询基本原理：

概念

集合：

纯数学概念，简单理解为把不重复分元素放在一起，整个元素的整体就是一个集合，对于数据库来说可以理解为一张表就是一个集合，表里的每行记录就是集合中的元素。

笛卡尔积：

多个集合所有关系的组合。对应到数据库中就是两张或者多张表之间记录关联的所有可能。

集合A(1, 2, 3) 集合B(a, b, c)那么集合A和集合B的笛卡尔积为(1a, 1b, 1c, 2a, 2b, 2c, 3a, 3b, 3c)

基础语法：

隐式内连接

select * from table_namea, table_nameb; 查询两张表记录组成的笛卡尔积

student和score表的笛卡尔积：mysql> select * from student,score;

mysql> select * from student,score;
+-----+--------+------+---------------------+-------+----------+----+-----+-------+--------+
| sno | sname | ssex | sbirthday | class | romemate | id | sno | cno | degree |
+-----+--------+------+---------------------+-------+----------+----+-----+-------+--------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | 103 | 1 | 103 | 3-245 | 86 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 1 | 103 | 3-245 | 86 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 1 | 103 | 3-245 | 86 |

上面全表查询笛卡尔积的结果是有大量无效数据且两张表没有主次顺序，多表查询使用where条件对笛卡尔积结果过滤出需要的正确数据

查询每个学生的每门课程的成绩，需要使用student和score两张表进行关联；对笛卡尔积数据进行过滤的条件是学生表的学生编号和成绩表学生编号一致，查询语句如下

mysql> select st.*,sc.cno,sc.degree from student as st,score as sc where st.sno=sc.sno;
+-----+--------+------+---------------------+-------+----------+-------+--------+
| sno | sname | ssex | sbirthday | class | romemate | cno | degree |
+-----+--------+------+---------------------+-------+----------+-------+--------+
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-245 | 86 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-245 | 75 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-245 | 68 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-105 | 92 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-105 | 88 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-105 | 76 |

内连接

上面的查询语句在数据库中称之为隐式内连接，这种写法两张表没有主次顺序，一般生产中会使用显示内连接的语法书写，还是查询学生成绩的语法如下。

mysql> select st.*,sc.cno,sc.degree from student as st inner join score as sc on st.sno=sc.sno;
+-----+--------+------+---------------------+-------+----------+-------+--------+
| sno | sname | ssex | sbirthday | class | romemate | cno | degree |
+-----+--------+------+---------------------+-------+----------+-------+--------+
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-245 | 86 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-245 | 75 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-245 | 68 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-105 | 92 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-105 | 88 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-105 | 76 |

外连接

问题：有内连接对两张表进行关联查询为什么还要有外连接呢？外连接什么时候会用到，作用是什么。

以上面查询学生成绩为例说明，单独查询学生表和使用多表查询学生成绩结果对比发现多表查询结果中没有出现学生编号为111的记录。这是因为这个学生在score表内没有相关记录；这时候如果需要多表查询结果将student表的内容没有遗漏的完全显示就需要使用外连接

mysql> select * from student;
+-----+--------+------+---------------------+-------+----------+
| sno | sname | ssex | sbirthday | class | romemate |
+-----+--------+------+---------------------+-------+----------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | 103 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 108 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | 109 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 |
| 111 | jIMe | Man | NULL | NULL | 101 |
+-----+--------+------+---------------------+-------+----------+
7 rows in set (0.00 sec)

mysql> select st.*,sc.cno,sc.degree from student as st inner join score as sc where st.sno=sc.sno;
+-----+--------+------+---------------------+-------+----------+-------+--------+
| sno | sname | ssex | sbirthday | class | romemate | cno | degree |
+-----+--------+------+---------------------+-------+----------+-------+--------+
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-245 | 86 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-245 | 75 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-245 | 68 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-105 | 92 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-105 | 88 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-105 | 76 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-105 | 64 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-105 | 91 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-105 | 78 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 6-166 | 85 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 6-166 | 79 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 6-166 | 81 |
+-----+--------+------+---------------------+-------+----------+-------+--------+
12 rows in set (0.00 sec)

分类：外连接分为左外连接和右外连接，A表左外连接 B表表示A表内容要全部显示在查询结果中，A表右外连接 B表表示B表内容要全部显示在查询结果中。

实际只需要掌握一种即可，下面是左外连接多表查询学生成绩示例。student在score表中没有数据的记录会补齐null

mysql> SELECT st.*,sc.cno,sc.degree FROM student AS st LEFT JOIN score AS sc ON st.sno=sc.sno;
+-----+--------+------+---------------------+-------+----------+-------+--------+
| sno | sname | ssex | sbirthday | class | romemate | cno | degree |
+-----+--------+------+---------------------+-------+----------+-------+--------+
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-245 | 86 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-245 | 75 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-245 | 68 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-105 | 92 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-105 | 88 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-105 | 76 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-105 | 64 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-105 | 91 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-105 | 78 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 6-166 | 85 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 6-166 | 79 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 6-166 | 81 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | 103 | NULL | NULL |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 108 | NULL | NULL |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | 109 | NULL | NULL |
| 111 | jIMe | Man | NULL | NULL | 101 | NULL | NULL |

自连接

自连接可以理解为外连接的一种，就是连接的两张表是同一张表；这种情况一般出现在表里面的数据有父子关系。student表中有室友关系，当前字段存储的是室友的学号（数据随便插的，仅供查询使用），现在需要将室友的名字显示出来

mysql> SELECT st1.*,st2.sname FROM student AS st1 LEFT JOIN student AS st2 ON st1.sno=st2.romemate;
+-----+--------+------+---------------------+-------+----------+--------+
| sno | sname | ssex | sbirthday | class | romemate | sname |
+-----+--------+------+---------------------+-------+----------+--------+
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 李军 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | 103 | 陆君 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | 109 | 匡明 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | 109 | 王丽 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 曾华 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | 109 | 王芳 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | 103 | jIMe |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | NULL |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 108 | NULL |
| 111 | jIMe | Man | NULL | NULL | 101 | NULL |

复杂多表查询

查询每个老师负责课程的课程名和选最该课程的学生以及对应的成绩，需要用到teacher course score三张表关联进行多表查询

mysql> SELECT t.*,c.cno,c.cname,sc.sno,sc.degree FROM teacher t LEFT JOIN course c ON t.tno=c.tno LEFT JOIN score sc ON c.cno=sc.cno;
+-----+--------+------+---------------------+-----------+-----------------+-------+-----------------+------+--------+
| tno | tname | tsex | tbirthday | prof | depart | cno | cname | sno | degree |
+-----+--------+------+---------------------+-----------+-----------------+-------+-----------------+------+--------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 | 3-245 | 操作系统 | 103 | 86 |
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 | 3-245 | 操作系统 | 105 | 75 |
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 | 3-245 | 操作系统 | 109 | 68 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 | 3-105 | 计算机导论 | 103 | 92 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 | 3-105 | 计算机导论 | 105 | 88 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 | 3-105 | 计算机导论 | 109 | 76 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 | 3-105 | 计算机导论 | 103 | 64 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 | 3-105 | 计算机导论 | 105 | 91 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 | 3-105 | 计算机导论 | 109 | 78 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 | 6-166 | 数字电路 | 103 | 85 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 | 6-166 | 数字电路 | 105 | 79 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 | 6-166 | 数字电路 | 109 | 81 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 | 9-888 | 高等数学 | NULL | NULL |
+-----+--------+------+---------------------+-----------+-----------------+-------+-----------------+------+--------+

基表内容

mysql> select * from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)

mysql> select * from course;
+-------+-----------------+-----+
| cno | cname | tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)

mysql> select * from score;
+----+-----+-------+--------+
| id | sno | cno | degree |
+----+-----+-------+--------+
| 1 | 103 | 3-245 | 86 |
| 2 | 105 | 3-245 | 75 |
| 3 | 109 | 3-245 | 68 |
| 4 | 103 | 3-105 | 92 |
| 5 | 105 | 3-105 | 88 |
| 6 | 109 | 3-105 | 76 |
| 7 | 103 | 3-105 | 64 |
| 8 | 105 | 3-105 | 91 |
| 9 | 109 | 3-105 | 78 |
| 10 | 103 | 6-166 | 85 |
| 11 | 105 | 6-166 | 79 |
| 12 | 109 | 6-166 | 81 |
+----+-----+-------+--------+

码农公寓