- 1Elasticsearch基础操作
- 2科大讯飞2020提前批一面凉经_科大讯飞提前批 手撕
- 35. ATF（ARM Trusted firmware）启动---bl31_atf bl31 无法启动
- 4干净的架构_干净的抽象结构
- 5掘金以奖品为诱惑坑作者之路--博客作者注意
- 6超星nm10 aarch64平台ubuntu使用移远EC200-CN4G/5G卡_ec200u-cn
- 7YOLO_v5将数据集按比例分为训练集和测试集_yolov5 数据集划分
- 8FRP 进行内网穿透，在一台客户端上配置多台服务器连接，winsw 配置多个 windows 自启动服务，Windows 远程桌面连接时目标客户端断开 WIFI连接解决办法_frp穿透双win
- 9常见的排序算法C/C++_擂台法排序c语言
- 10html爱心表白代码（最全），面试题+笔记+项目实战_html表白爱心代码
【MySQL联合查询】轻松实现数据关联_mysql两个表联合查询
赞
踩
1、联合查询
联合查询又称为多表查询，它的基本执行过程就是笛卡尔积
1.1 认识笛卡尔积
那么什么是笛卡尔积呢？
答：笛卡尔积就是将两张表放在一起进行计算，把第一张表的每一行分别取出来和第二张表的每一行进行连接，得到一个新的行。
举例说明笛卡尔积：
假设我们现在有两张表分别为学生信息表和班级信息表
现在我们把这两张表进行笛卡尔积操作：
笛卡尔积相当于乘法运算，列数是两个表列数之和，行数是两个表行数之积
注：笛卡尔积执行后产生的结果大多是无效的，此时我们就可以采用条件进行筛选
1.2 笛卡尔积进行多表查询
接下来我们就来试一下如何进行笛卡尔积操作：
首先创建两张表，分别为上述的学生信息表和班级信息表，创建完两张表后并给它们添加上述表中的内容
- select * from student;
- +----+------+---------+
- | id | name | classid |
- +----+------+---------+
- | 1 | 张三 | 1 |
- | 2 | 李四 | 2 |
- | 3 | 王五 | 1 |
- +----+------+---------+
- select * from class;
- +---------+-----------+
- | classid | classname |
- +---------+-----------+
- | 1 | 舞蹈班 |
- | 2 | 跆拳道班 |
- +---------+-----------+
然后对这两张表进行笛卡尔积操作
- select * from student,class;
- +----+------+---------+---------+-----------+
- | id | name | classid | classid | classname |
- +----+------+---------+---------+-----------+
- | 1 | 张三 | 1 | 1 | 舞蹈班 |
- | 1 | 张三 | 1 | 2 | 跆拳道班 |
- | 2 | 李四 | 2 | 1 | 舞蹈班 |
- | 2 | 李四 | 2 | 2 | 跆拳道班 |
- | 3 | 王五 | 1 | 1 | 舞蹈班 |
- | 3 | 王五 | 1 | 2 | 跆拳道班 |
- +----+------+---------+---------+-----------+
上述的笛卡尔积执行后产生的结果大多数是无效的，此时我们就可以采用条件进行筛选
当 student 表中的 classid 和 class 表中的 classid 相等时，则这条数据是有效的数据
- select * from student,class where classid = classid;
- ERROR 1052 (23000): Column 'classid' in where clause is ambiguous
如果直接用 classid = classid 则会报错，因为它无法分辨哪个classid 和 哪个 classid 进行比较
- select * from student,class where student.classid = class.classid;
- +----+------+---------+---------+-----------+
- | id | name | classid | classid | classname |
- +----+------+---------+---------+-----------+
- | 1 | 张三 | 1 | 1 | 舞蹈班 |
- | 2 | 李四 | 2 | 2 | 跆拳道班 |
- | 3 | 王五 | 1 | 1 | 舞蹈班 |
- +----+------+---------+---------+-----------+
那么此时就可以采用 表名.列名 的方式进行区分
注：多表查询除了可以加上连接条件外，还可以加上其他条件
多表查询出来用 from 多个表加逗号分隔 来连接多个表外，还可以用 join on 来连接，还可以是 inner join on
join on 实现多表查询：
- select * from student join class on student.classid = class.classid;
- +----+------+---------+---------+-----------+
- | id | name | classid | classid | classname |
- +----+------+---------+---------+-----------+
- | 1 | 张三 | 1 | 1 | 舞蹈班 |
- | 2 | 李四 | 2 | 2 | 跆拳道班 |
- | 3 | 王五 | 1 | 1 | 舞蹈班 |
- +----+------+---------+---------+-----------+
join 连接的是两个表，on 后面跟的是连接条件
inner join on 实现多表查询：
- select * from student inner join class on student.classid = class.classid;
- +----+------+---------+---------+-----------+
- | id | name | classid | classid | classname |
- +----+------+---------+---------+-----------+
- | 1 | 张三 | 1 | 1 | 舞蹈班 |
- | 2 | 李四 | 2 | 2 | 跆拳道班 |
- | 3 | 王五 | 1 | 1 | 舞蹈班 |
- +----+------+---------+---------+-----------+
inner join on 其实跟 join on 一样，此处不做过多解释
from 多个表 和 join on 的主要区别：
from 多个表只能实现内连接
join on 既可以实现内连接也可以实现外连接
1.3 内连接和外连接
内连接 和 外连接的主要区别：
当连接的两个表里面的数据是一一对应的时候，内连接和外连接其实就没啥区别
当连接的两个表里面的数据不是一一对应的时候，内连接和外连接就有区别了
1.3.1 两张表一一对应
现在有两张表，分别为 student 学生表 和 score 成绩表：
- select * from student;
- +----+------+
- | id | name |
- +----+------+
- | 1 | 张三 |
- | 2 | 李四 |
- | 3 | 王五 |
- +----+------+
- select * from scoretable;
- +-----------+-------+
- | studentId | score |
- +-----------+-------+
- | 1 | 97 |
- | 2 | 86 |
- | 3 | 73 |
- +-----------+-------+
id 和 studentId 是一一对应的，所有内连接和外连接没什么区别
内连接：
- select * from student,scoreTable where student.id = scoretable.studentId;
- +----+------+-----------+-------+
- | id | name | studentId | score |
- +----+------+-----------+-------+
- | 1 | 张三 | 1 | 97 |
- | 2 | 李四 | 2 | 86 |
- | 3 | 王五 | 3 | 73 |
- +----+------+-----------+-------+
外连接：
- select * from student join scoreTable on student.id = scoretable.studentId;
- +----+------+-----------+-------+
- | id | name | studentId | score |
- +----+------+-----------+-------+
- | 1 | 张三 | 1 | 97 |
- | 2 | 李四 | 2 | 86 |
- | 3 | 王五 | 3 | 73 |
- +----+------+-----------+-------+
1.3.2 两张表不一一对应
现在有两张表，分别为 student 学生表 和 score 成绩表：
- select * from student;
- +----+------+
- | id | name |
- +----+------+
- | 1 | 张三 |
- | 2 | 李四 |
- | 3 | 王五 |
- +----+------+
- select * from scoretable;
- +-----------+-------+
- | studentId | score |
- +-----------+-------+
- | 1 | 97 |
- | 2 | 86 |
- | 6 | 73 |
- +-----------+-------+
现在我们可以看到 student 中的 id 为 3 的在 scoretable 中没有对应的 studentId
内连接：
- select * from student,scoreTable where student.id = scoretable.studentId;
- +----+------+-----------+-------+
- | id | name | studentId | score |
- +----+------+-----------+-------+
- | 1 | 张三 | 1 | 97 |
- | 2 | 李四 | 2 | 86 |
- +----+------+-----------+-------+
当进行内连接的时候因为student中的id为3 的和scoretable中的studentId为6的不匹配所以就被筛除了没有查询出来
外连接：
当两张表不是一一对应的时候，外连接又可以分为 左外连接 和 右外连接
左外连接：left join on
- select * from student left join scoreTable on student.id = scoretable.studentId;
- +----+------+-----------+-------+
- | id | name | studentId | score |
- +----+------+-----------+-------+
- | 1 | 张三 | 1 | 97 |
- | 2 | 李四 | 2 | 86 |
- | 3 | 王五 | NULL | NULL |
- +----+------+-----------+-------+
左外连接会把左表的结果尽量显示出来，如果右表中没有对应的记录，就使用NULL填充
右外连接：right join on
- select * from student right join scoreTable on student.id = scoretable.studentId;
- +------+------+-----------+-------+
- | id | name | studentId | score |
- +------+------+-----------+-------+
- | 1 | 张三 | 1 | 97 |
- | 2 | 李四 | 2 | 86 |
- | NULL | NULL | 6 | 73 |
- +------+------+-----------+-------+
右外连接会把右表的结果尽量显示出来，如果左表中没有对应的记录，就使用NULL填充
1.4 自连接
自连接：自己和自己进行笛卡尔积
自连接使用场景：当行与行进行比较时，就可以使用自连接，将行转成列进行比较
现在有两张表，分别为 scoretable 和 course
scoretable 表：
- select * from scoretable;
- +-------+------------+-----------+
- | score | student_id | course_id |
- +-------+------------+-----------+
- | 70 | 1 | 1 |
- | 96 | 1 | 2 |
- | 97 | 1 | 3 |
- | 80 | 2 | 1 |
- | 92 | 2 | 2 |
- | 86 | 2 | 3 |
- | 91 | 3 | 1 |
- | 76 | 3 | 2 |
- | 77 | 3 | 3 |
- +-------+------------+-----------+
course 表：
- select * from course;
- +----+------+
- | id | name |
- +----+------+
- | 1 | 语文 |
- | 2 | 数学 |
- | 3 | 英语 |
- +----+------+
现在我们要查询哪些同学的语文成绩比英语成绩低
首先自连接，将行转换成列：
- select * from scoretable,scoretable;
- ERROR 1066 (42000): Not unique table/alias: 'scoretable'
自己跟自己连接名字不能重复
那表名不能重复，那还如何自连接呢？
答：可以起别名，起别名不光可以对列，还可以对表
select * from scoretable as s1,scoretable as s2;自连接排列组合的时候会产生大量无效的数据，所有就需要指定连接条件
指定连接条件，将有效数据筛选出来：
- select * from scoretable as s1,scoretable as s2
- where s1.student_id = s2.student_id;
自连接的时候只有当 student_id 相等时才表示有效数据
添加条件，将左边表的语文成绩和右边表的英语成绩查询出来：
有效成绩查询出来后，就需要加上条件查询出左边的语文成绩和右边的英语成绩
- select * from scoretable as s1,scoretable as s2
- where s1.student_id = s2.student_id
- and s1.course_id = 1 and s2.course_id = 3;
- +-------+------------+-----------+-------+------------+-----------+
- | score | student_id | course_id | score | student_id | course_id |
- +-------+------------+-----------+-------+------------+-----------+
- | 70 | 1 | 1 | 97 | 1 | 3 |
- | 80 | 2 | 1 | 86 | 2 | 3 |
- | 91 | 3 | 1 | 77 | 3 | 3 |
- +-------+------------+-----------+-------+------------+-----------+
这样就将左侧的语文成绩查询出来了，右侧的英语成绩查询出来了
添加条件，将语文成绩比英语成绩低的同学查询出来：
接下来就要查询哪些同学的语文成绩比英语成绩低
- select * from scoretable as s1,scoretable as s2
- where s1.student_id = s2.student_id
- and s1.course_id = 1 and s2.course_id = 3 and s1.score < s2.score;
- +-------+------------+-----------+-------+------------+-----------+
- | score | student_id | course_id | score | student_id | course_id |
- +-------+------------+-----------+-------+------------+-----------+
- | 70 | 1 | 1 | 97 | 1 | 3 |
- | 80 | 2 | 1 | 86 | 2 | 3 |
- +-------+------------+-----------+-------+------------+-----------+
- 2 rows in set (0.00 sec)
这样就把语文成绩比英语成绩低的学生信息查询出来了
1.5 子查询
子查询：把多个 SQL 组合成一个
在实际开发中，子查询得慎用。因为子查询可能会构造出非常复杂，非常不好理解的 SQL。
写代码一般要么追求可读性和可维护性，要么追求程序的运行速度
1.5.1 单行子查询
单行子查询：返回一行记录的子查询
student 表：
- select * from student;
- +----+----------+------+
- | id | class_id | name |
- +----+----------+------+
- | 1 | 1 | 张三 |
- | 2 | 1 | 李四 |
- | 3 | 2 | 王五 |
- | 4 | 3 | 赵六 |
- | 5 | 2 | 王七 |
- +----+----------+------+
现在查询“张三”的同班同学，根据 class_id 进行查询
分开查询：
- //查询出张三的class_id
- select class_id from student where name = '张三';
- +----------+
- | class_id |
- +----------+
- | 1 |
- +----------+
- //查询出来的张三的class_id为 1,再查询除了张三以外的class_id 为1的同学
- select * from student where class_id = 1 and name != '张三';
- +----+----------+------+
- | id | class_id | name |
- +----+----------+------+
- | 2 | 1 | 李四 |
- +----+----------+------+
单行子查询：
- select * from student where
- class_id = ( select class_id from student where name = '张三')
- and name != '张三';
- +----+----------+------+
- | id | class_id | name |
- +----+----------+------+
- | 2 | 1 | 李四 |
- +----+----------+------+
1.5.2 多行子查询
多行子查询：返回多行记录的子查询
scoretable 表：
- select * from scoretable;
- +-------+------------+-----------+
- | score | student_id | course_id |
- +-------+------------+-----------+
- | 70 | 1 | 1 |
- | 96 | 1 | 2 |
- | 97 | 1 | 3 |
- | 80 | 2 | 1 |
- | 92 | 2 | 2 |
- | 86 | 2 | 3 |
- | 91 | 3 | 1 |
- | 76 | 3 | 2 |
- | 77 | 3 | 3 |
- +-------+------------+-----------+
course 表：
- select * from course;
- +----+------+
- | id | name |
- +----+------+
- | 1 | 语文 |
- | 2 | 数学 |
- | 3 | 英语 |
- +----+------+
查询每位同学的“语文”“英语”课程的成绩信息
普通查询：
- //首先查询出语文和英语成绩对应的id
- select id from course where name = '语文' or name = '英语';
- +----+
- | id |
- +----+
- | 1 |
- | 3 |
- +----+
- //再根据查询出来的语文英语对应的id，在 scoretable表中查询
- select * from scoretable where course_id = 1 or course_id = 3;
- +-------+------------+-----------+
- | score | student_id | course_id |
- +-------+------------+-----------+
- | 70 | 1 | 1 |
- | 97 | 1 | 3 |
- | 80 | 2 | 1 |
- | 86 | 2 | 3 |
- | 91 | 3 | 1 |
- | 77 | 3 | 3 |
- +-------+------------+-----------+
多行子查询：
- select * from scoretable where course_id
- in(select id from course where name = '语文' or name = '英语');
- +-------+------------+-----------+
- | score | student_id | course_id |
- +-------+------------+-----------+
- | 70 | 1 | 1 |
- | 97 | 1 | 3 |
- | 80 | 2 | 1 |
- | 86 | 2 | 3 |
- | 91 | 3 | 1 |
- | 77 | 3 | 3 |
- +-------+------------+-----------+
1.5.3 合并查询
合并查询：就是将两个查询结果集，合并成一个
在实际应用中，为了合并多个查询的执行结果，可以使用集合操作符 union，union all。使用UNION 和UNION ALL时，前后查询的结果集中，字段需要一致
union操作符
union操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行
course 表：
- select * from course;
- +----+------+
- | id | name |
- +----+------+
- | 1 | 语文 |
- | 2 | 数学 |
- | 3 | 英语 |
- | 6 | 化学 |
- | 7 | 物理 |
- +----+------+
现在查询 id 小于等于 2 或者 name 为 "英文" 的课程信息
- select * from course where id <= 2 union select * from course where name = '英语';
- +----+------+
- | id | name |
- +----+------+
- | 1 | 语文 |
- | 2 | 数学 |
- | 3 | 英语 |
- +----+------+
看到这里大家可能有个疑问，明明可以用 or 也能实现，为什么还要用 union？
答：用 or 查询只能时来自于同一个表，如果用 union 查询可以时来自于不同的表，子要查询的结果列匹配即可，匹配就是列的类型一样、列的一样、列的名字一样
union all 操作符
union all 操作符用于取得两个结果集的并集，当使用该操作符时，不会去掉结果集中的重复行
- select * from course where id < 3 union all select * from course where name = '数学';
- +----+------+
- | id | name |
- +----+------+
- | 1 | 语文 |
- | 2 | 数学 |
- | 2 | 数学 |
- +----+------+
