MySQL 联合查询

笛卡尔积

笛卡尔积：多表查询的核心操作。 笛卡尔积的计算很简单，就类似于排列组合。笛卡尔积是针对任意两张表之间的运算。
举例：


笛卡尔积计算过程：先拿第一张表的第一条记录，和第二张表的每个记录，分别组合，得到一组新的记录。然后再拿第一张表的第二条记录，和第二张表的每条记录，分别组合，又得到新的记录，最终得到的记录就是笛卡尔积。针对两张表计算笛卡尔积，笛卡尔积的列数，就是 A 的列数 + B 的列数。笛卡尔积的行数，就是 A 的行数 * B 的行数。笛卡尔积的效率不高，但是可以借助来完成发复杂的操作。
笛卡尔积用法：select * from 后面跟上多个表名，表名之间用逗号隔开。
两张表中都有 班级Id 这一列，班级Id 的值。班级Id 就是连接条件。如果笛卡尔积的两个列名相同，在写条件的时候，就可以通过 表名.列名 的方式来访问。如果列不会混淆，就可以省略表名。代码如下：

select student.name, class.name from student, class where student.classId = class.classId;

通过 表名.列名 就可以区分了。

多表查询一份数据

先建表，并且插入数据：

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) ,
                      classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

建立四个表，然后插入数据：

insert into classes(name, `desc`) values
                                      ('计算机系2020级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
                                      ('中文系2020级3班','学习了中国传统文学'),
                                      ('自动化2020级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values
                                                       ('09982','黑旋风李逵','xuanfeng@qq.com',1),
                                                       ('00835','菩提老祖',null,1),
                                                       ('00391','白素贞',null,1),
                                                       ('00031','许仙','xuxian@qq.com',1),
                                                       ('00054','不想毕业',null,1),
                                                       ('51234','好好说话','say@qq.com',2),
                                                       ('83223','小菜鸡',null,2),
                                                       ('09527','老外学中文','foreigner@qq.com',2);

insert into course(name) values ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- 小菜鸡
(80, 7, 2),(92, 7, 6);

查找许仙成绩，先进行笛卡尔积，不过要指定 student.id = score.student_id; 这两个匹配，不然就会有很多无效信息。代码如下：

select * from student,score where student.id = score.student_id;

结果如下：

这样就不会产生无效信息了。所以下一步，我们只需要留下名字是 ”许仙“ 的信息了，所以加一个筛选条件就好了，代码如下：

select * from student,score where student.id = score.student_id and student.name = '许仙';

这样的结果就是剩 ”许仙“ 了：

最后要的是成绩，所以只保留 名字 和 成绩 部分就好了：

select student.name,score.score from student,score where student.id = score.student_id and student.name = '许仙';

运行结果如下，这样就完成了多表查询一份数据了：

join 实现多表查询

通过 join 这样的关键字，也可以实现多表查询，代码如下：

select student.name, score.score from student join score on student.id = score.student_id and student.name = '许仙';

运行结果如下：

join 查询多个表

通过 select from 表1 join 表2 on 条件 join 表3 on 条件。代码如下：

select student.name, course.name, score.score from student join score on student.id = score.student_id
   join course on score.course_id = course.id;

运行结果如下：

日常使用多表的时候，建议使用 from 多个表 where。

内外连接

使用 join on 可以做到 from where 做不到的事情。上面的 from 多个表 where 写法叫做 “内连接”，使用 join on 的写法，既可以表示内连接，还可以表示外连接。

内连接：select 列 from 表1 inner join 表2 on 条件。 inner 表示内连接，其中 inner 可以省略

外连接：

• select 列 from 表1 left join 表2 on 条件; 左外连接
• select 列 from 表1 right join 表2 on 条件; 右外连接

多表查询的时候，内连接用的最多，但是外连接也会用到。创建两个表：

create table student(id int, name varchar(20), classId int);
create table class(id int,name varchar(20));

然后插入数据：

insert into student values (1, '张三', 1);
insert into student values (2, '李四', 1);
insert into student values (3, '王五', 2);
insert into student values (4, '赵六', 3);

insert into class values (1, '计算机一班');
insert into class values (2, '计算机二班');

内连接

内连接就是要求两个表里面都要有的数据，代码如下：

select * from student, class where student.classId = class.id;

运行结果如下：

这里的结果少了赵六，因为赵六，只在一个表里面存在。

外连接

左外连接：就是 left join 会以左边表的记录为主，尽可能的把记录都列出来，对于没有的右边数据，就改成 null。代码如下：

select * from student left join class on student.classId = class.id;

运行结果如下：

右外连接：就是 right join 会尽量以右边表的记录为主，尽可能的吧记录都列出来，大不了左边改成 null。

自连接

自己和自己笛卡尔积，处理特殊场景的问题。就是把行转化为列。因为也是笛卡尔积，所以还是并不高效的方法。不过要指定别名，不然自连接就会重名。在自连接的时候，使用多个条件来完成筛选。代码如下：

select * from score as s1, score as s2 where s1.student_id = s2.student_id;

这样筛选之后的就是 id 相等的结果了，如果再加一些条件的话，筛选效果就更明显了：

select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id=3 and s2.course_id=1;

这样筛选之后的结果就更精确了，运行结果如下：

子查询

子查询就是把拆分好的代码合成一个，单行子查询：返回一条记录。例如：查询之前 “不想毕业” 同学的同班同学。代码如下：

select classes_id from student where name = '不想毕业';

这里就是先查询出班级，然后再查询班级为 1 的同学就好了：

select name from student where classes_id = 1;

运行结果如下：

将 SQL 语句合并在一起，也就是子查询：

select name from student where classes_id = (select classes_id from student where name = '不想毕业');

运行结果如下：

使用注意

在使用子查询的时候，要把 = 换成 in 来操作。例如要查询课程成绩，先查询语文和英语的课程，然后再查询课程的成绩。代码如下：

select id from course where name = '语文' or name = '英文';

这里得到课程 id ：

然后查询对应的课程 id 的成绩就可以了：

select * from score where course_id in (4,6);

运行结果如下：

然后再合并成一句，代码如下：

select * from score where course_id in (select id from course where name = '语文' or name = '英文');

运行结果如下：

合并查询

合并查询：把多个查询语句的结果合并到一起了。通过 union 来实现：把查询的结果放到一起。对两个查询结果取并集。可以自动去重。代码如下：

select * from course where name = '英文' union select * from course where id < 3;

运行结果如下：

也可以使用 or 实现，不过使用 or 的时候必须保证是针对同一个表来进行操作的。代码如下：

select * from course where name = '英文' or id < 3;

运行结果如下：