MySQL 联合查询

联合查询

笛卡尔积

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


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

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

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

多表查询一份数据

先建表，并且插入数据：

createtable classes (id intprimarykeyauto_increment, name varchar(20),`desc`varchar(100));createtable student (id intprimarykeyauto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20),
                      classes_id int);createtable course(id intprimarykeyauto_increment, name varchar(20));createtable score(score decimal(3,1), student_id int, course_id int);

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

insertinto classes(name,`desc`)values('计算机系2020级1班','学习了计算机原理、C和Java语言、数据结构和算法'),('中文系2020级3班','学习了中国传统文学'),('自动化2020级5班','学习了机械自动化');insertinto student(sn, name, qq_mail, classes_id)values('09982','黑旋风李逵','[email protected]',1),('00835','菩提老祖',null,1),('00391','白素贞',null,1),('00031','许仙','[email protected]',1),('00054','不想毕业',null,1),('51234','好好说话','[email protected]',2),('83223','小菜鸡',null,2),('09527','老外学中文','[email protected]',2);insertinto course(name)values('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');insertinto 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 条件; 右外连接

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

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

然后插入数据：

insertinto student values(1,'张三',1);insertinto student values(2,'李四',1);insertinto student values(3,'王五',2);insertinto student values(4,'赵六',3);insertinto class values(1,'计算机一班');insertinto class values(2,'计算机二班');

内连接

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

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

运行结果如下：

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

外连接

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

select*from student leftjoin 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=3and 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 ='英文'unionselect*from course where id <3;

运行结果如下：

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

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

运行结果如下：