mysql多表联合查询
文章目录
1：什么是多表联合查询
前面所讲的查询语句都是针对一个表的，但是在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。
在 MySQL 中，多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种。
2：交叉连接
2.1 笛卡尔积
交叉连接(CROSS JOIN)：有两种，显式的和隐式的2种，一般用来返回连接表的笛卡尔积。
笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。
例如，有 A 和 B 两个集合，它们的值如下：
两个集合相乘，不满足交换率，即 A×B≠B×A。
A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。
多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。在实际应用中，应避免使用笛卡尔积，因为笛卡尔积中容易存在大量的不合理数据，简单来说就是容易导致查询结果重复、混乱
2.2交叉连接
交叉连接的语法格式如下：
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句];
字段名：需要查询的字段名称。
<表1><表2>：需要交叉连接的表名。
WHERE 子句：用来设置交叉连接的查询条件。
注意：多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。
例：
查询 tb_students_info 表中的数据
mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | dany | 25 | 男 | 160 | 1 |
| 2 | green | 23 | 男 | 158 | 2 |
| 3 | henry | 23 | 女 | 185 | 1 |
| 4 | jane | 22 | 男 | 162 | 3 |
| 5 | jim | 24 | 女 | 175 | 2 |
| 6 | john | 21 | 女 | 172 | 4 |
| 7 | lily | 22 | 男 | 165 | 4 |
| 8 | susan | 23 | 男 | 170 | 5 |
| 9 | thomas | 22 | 女 | 178 | 5 |
| 10 | tom | 23 | 女 | 165 | 5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
查询 tb_course 表中的数据
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | java |
| 2 | mysql |
| 3 | python |
| 4 | go |
| 5 | c++ |
+----+-------------+
5 rows in set (0.00 sec)
使用 CROSS JOIN 查询出两张表中的笛卡尔积
mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name | age | sex | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
| 1 | java | 1 | dany | 25 | 男 | 160 | 1 |
| 2 | mysql | 1 | dany | 25 | 男 | 160 | 1 |
| 3 | python | 1 | dany | 25 | 男 | 160 | 1 |
| 4 | go | 1 | dany | 25 | 男 | 160 | 1 |
| 5 | c++ | 1 | dany | 25 | 男 | 160 | 1 |
| 1 | java | 2 | green | 23 | 男 | 158 | 2 |
| 2 | mysql | 2 | green | 23 | 男 | 158 | 2 |
| 3 | python | 2 | green | 23 | 男 | 158 | 2 |
| 4 | go | 2 | green | 23 | 男 | 158 | 2 |
| 5 | c++ | 2 | green | 23 | 男 | 158 | 2 |
| 1 | java | 3 | henry | 23 | 女 | 185 | 1 |
| 2 | mysql | 3 | henry | 23 | 女 | 185 | 1 |
| 3 | python | 3 | henry | 23 | 女 | 185 | 1 |
| 4 | go | 3 | henry | 23 | 女 | 185 | 1 |
| 5 | c++ | 3 | henry | 23 | 女 | 185 | 1 |
| 1 | java | 4 | jane | 22 | 男 | 162 | 3 |
| 2 | mysql | 4 | jane | 22 | 男 | 162 | 3 |
| 3 | python | 4 | jane | 22 | 男 | 162 | 3 |
| 4 | go | 4 | jane | 22 | 男 | 162 | 3 |
| 5 | c++ | 4 | jane | 22 | 男 | 162 | 3 |
| 1 | java | 5 | jim | 24 | 女 | 175 | 2 |
| 2 | mysql | 5 | jim | 24 | 女 | 175 | 2 |
| 3 | python | 5 | jim | 24 | 女 | 175 | 2 |
| 4 | go | 5 | jim | 24 | 女 | 175 | 2 |
| 5 | c++ | 5 | jim | 24 | 女 | 175 | 2 |
| 1 | java | 6 | john | 21 | 女 | 172 | 4 |
| 2 | mysql | 6 | john | 21 | 女 | 172 | 4 |
| 3 | python | 6 | john | 21 | 女 | 172 | 4 |
| 4 | go | 6 | john | 21 | 女 | 172 | 4 |
| 5 | c++ | 6 | john | 21 | 女 | 172 | 4 |
| 1 | java | 7 | lily | 22 | 男 | 165 | 4 |
| 2 | mysql | 7 | lily | 22 | 男 | 165 | 4 |
| 3 | python | 7 | lily | 22 | 男 | 165 | 4 |
| 4 | go | 7 | lily | 22 | 男 | 165 | 4 |
| 5 | c++ | 7 | lily | 22 | 男 | 165 | 4 |
| 1 | java | 8 | susan | 23 | 男 | 170 | 5 |
| 2 | mysql | 8 | susan | 23 | 男 | 170 | 5 |
| 3 | python | 8 | susan | 23 | 男 | 170 | 5 |
| 4 | go | 8 | susan | 23 | 男 | 170 | 5 |
| 5 | c++ | 8 | susan | 23 | 男 | 170 | 5 |
| 1 | java | 9 | thomas | 22 | 女 | 178 | 5 |
| 2 | mysql | 9 | thomas | 22 | 女 | 178 | 5 |
| 3 | python | 9 | thomas | 22 | 女 | 178 | 5 |
| 4 | go | 9 | thomas | 22 | 女 | 178 | 5 |
| 5 | c++ | 9 | thomas | 22 | 女 | 178 | 5 |
| 1 | java | 10 | tom | 23 | 女 | 165 | 5 |
| 2 | mysql | 10 | tom | 23 | 女 | 165 | 5 |
| 3 | python | 10 | tom | 23 | 女 | 165 | 5 |
| 4 | go | 10 | tom | 23 | 女 | 165 | 5 |
| 5 | c++ | 10 | tom | 23 | 女 | 165 | 5 |
+----+-------------+----+--------+------+------+--------+-----------+
50 rows in set (0.00 sec)
由运行结果可以看出，tb_course 和 tb_students_info 表交叉连接查询后，返回了 50 条记录。可以想象，当表中的数据较多时，得到的运行结果会非常长，而且得到的运行结果也没太大的意义。所以，通过交叉连接的方式进行多表查询的这种方法并不常用，我们应该尽量避免这种查询。
例
查询 tb_course 表中的 id 字段和 tb_students_info 表中的 course_id 字段相等的内容
mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info
-> WHERE tb_students_info.course_id = tb_course.id;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name | age | sex | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
| 1 | java | 1 | dany | 25 | 男 | 160 | 1 |
| 2 | mysql | 2 | green | 23 | 男 | 158 | 2 |
| 1 | java | 3 | henry | 23 | 女 | 185 | 1 |
| 3 | python | 4 | jane | 22 | 男 | 162 | 3 |
| 2 | mysql | 5 | jim | 24 | 女 | 175 | 2 |
| 4 | go | 6 | john | 21 | 女 | 172 | 4 |
| 4 | go | 7 | lily | 22 | 男 | 165 | 4 |
| 5 | c++ | 8 | susan | 23 | 男 | 170 | 5 |
| 5 | c++ | 9 | thomas | 22 | 女 | 178 | 5 |
| 5 | c++ | 10 | tom | 23 | 女 | 165 | 5 |
+----+-------------+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
如果在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。因此，表的数量较多时，交叉连接会非常非常慢。一般情况下不建议使用交叉连接。
在 MySQL 中，多表查询一般使用内连接和外连接，它们的效率要高于交叉连接。
3 内连接
MySQL的INNER JOIN子句可以将一个表中的行与其他表中的行进行匹配，并允许从两个表查询包含列的行记录，效果与CROSS JOIN语句类似，其具体的语法格式如下所示。、
select 查询字段 from 表1 [inner] join 表2 on 连接条件1 [inner] join 表3 on 连接条件2 where 查询条件
例
在 tb_students_info 表和 tb_course 表之间，使用内连接查询学生姓名和相对应的课程名称
mysql> select s.name,c.course_name from tb_students_info s inner join tb_cou
rse c on c.id = s.course_id;
+--------+-------------+
| name | course_name |
+--------+-------------+
| dany | java |
| green | mysql |
| henry | java |
| jane | python |
| jim | mysql |
| john | go |
| lily | go |
| susan | c++ |
| thomas | c++ |
| tom | c++ |
+--------+-------------+
10 rows in set (0.00 sec)
4外连接
内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。
外连接可以分为左外连接和右外连接2种，下面根据实例分别介绍左外连接和右外连接。
也就是说外连接，外连接会将一个表作为主表，一个表作为副表，查时会查所有主表的内容，而副表只查与主表有匹配行的内容，如果没有就返回为null。
4.1左连接
左连接语法格式
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;
例
在 tb_students_info 表和 tb_course 表中查询所有学生姓名和相对应的课程名称，包括没有课程的学生
mysql> select c.name,s.course_name from tb_students_info c left outer join tb_course s on s.id=c.course_id;
+----------+-------------+
| name | course_name |
+----------+-------------+
| dany | java |
| henry | java |
| green | mysql |
| jim | mysql |
| jane | python |
| john | go |
| lily | go |
| susan | c++ |
| thomas | c++ |
| tom | c++ |
| xuanning | NULL |
| nianxia | NULL |
+----------+-------------+
12 rows in set (0.00 sec)
4.2右连接
语法
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;
outer可省略
表二为主表表一为副表
例
在 tb_students_info 表和 tb_course 表中查询所有课程，包括没有学生的课程
mysql> select c.name,s.course_name from tb_students_info c right outer join tb_course s on s.id=c.course_id;
+----------+-------------+
| name | course_name |
+----------+-------------+
| dany | java |
| green | mysql |
| henry | java |
| jane | python |
| jim | mysql |
| john | go |
| lily | go |
| susan | c++ |
| thomas | c++ |
| tom | c++ |
| xuanning | html |
| nianxia | swift |
| NULL | php |
+----------+-------------+
13 rows in set (0.00 sec)
多个表左/右连接时，在 ON 子句后连续使用 LEFT/RIGHT OUTER JOIN 或 LEFT/RIGHT JOIN 即可。
使用外连接查询时，一定要分清需要查询的结果，是需要显示左表的全部记录还是右表的全部记录，然后选择相应的左连接和右连接。
5:分组查询
在 MySQL 中，GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。
语法如下
GROUP BY <字段名>
5.1单独使用group by
单独使用查询结果只会输出每个分组的第一条记录
例·
mysql> select sex,name from tb_students_info group by sex;
+------+-------+
| sex | name |
+------+-------+
| 女 | henry |
| 男 | dany |
+------+-------+
2 rows in set (0.00 sec)
此处前面如果报错或出现数据全为字段名，则加上聚合函数或去配置文件/etc/my.cnf添加一行
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
然后重启服务，即可
5.2 GROUP BY 与聚合函数
在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。
聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。其中，COUNT() 用来统计记录的条数；SUM() 用来计算字段值的总和；AVG() 用来计算字段值的平均值；MAX() 用来查询字段的最大值；MIN() 用来查询字段的最小值
例：
下面根据 tb_students_info 表的 sex 字段进行分组查询，使用 COUNT() 函数计算每一组的记录数
mysql> select sex,count(sex) from tb_students_info group by sex;
+------+------------+
| sex | count(sex) |
+------+------------+
| 女 | 5 |
| 男 | 7 |
+------+------------+
2 rows in set (0.01 sec)
5.3 GROUP BY 与 WITH ROLLUP
WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。
例
下面根据 tb_students_info 表中的 sex 字段进行分组查询，并使用 WITH ROLLUP 显示记录的总和：
mysql> select sex,group_concat(name) from tb_students_info group by sex with rollup;
+------+-----------------------------------------------------------------------+
| sex | group_concat(name) |
+------+-----------------------------------------------------------------------+
| 女 | henry,jim,john,thomas,tom |
| 男 | dany,green,jane,lily,susan,xuanning,nianxia |
| NULL | henry,jim,john,thomas,tom,dany,green,jane,lily,susan,xuanning,nianxia |
+------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)
6:子查询
套娃查询
子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。
语法如下
WHERE <表达式> <操作符> (子查询)
其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。
1）IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。
2）EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反
例
使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名
mysql> select name from tb_students_info where course_id in(select id from tb_course where course_name = 'java');
+-------+
| name |
+-------+
| dany |
| henry |
+-------+
2 rows in set (0.00 sec)
例
查出javaid
mysql> select id from tb_course where course_name = 'java';
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
例
在 SELECT 语句中使用 NOT IN 关键字，查询没有学习 Java 课程的学生姓名
mysql> select name from tb_students_info where course_id not in (select id from tb_course where course_name ='java');
+----------+
| name |
+----------+
| green |
| jane |
| jim |
| john |
| lily |
| susan |
| thomas |
| tom |
| xuanning |
| nianxia |
+----------+
10 rows in set (0.00 sec)
例
使用=运算符，在 tb_course 表和 tb_students_info 表中查询出所有学习 Python 课程的学生姓名
mysql> select name from tb_students_info where course_id = (select id from tb_course where course_name = 'swift');
+---------+
| name |
+---------+
| nianxia |
+---------+
1 row in set (0.00 sec)
套娃查询中（）里面的为子查询，查询时会优先查询里面的，外面的叫父查询，查完子查询再查子查询。
9984
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
