Mysql联合查询

1.联合查询

定义及数据准备

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
关于笛卡尔积通过一个例子理解：
如在a表中有a1,a2,a3,在b表中有b1,b2,b3等数据，因此笛卡尔积就为
a1b1,a1b2,a1b3
a2b1,a2b2,a2b3,
a3b1,a3b2,a3b3
表a中的所有数据都能跟表b的所有数据有所对应；

首先把准备好三张表和其对应的数据：
班级表

学生表：

课程表：

分数表：

2.内连接

通过两张表共有的列来筛选信息，达到尽快查询的目的
语法：
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 链接条件 and 其他条件
select 字段 from 表1 别名1,表2 别名2 where 链接条件 and 其他条件

2.1 例子1：

查询许仙的成绩；
具体看看整个查询的过程；
首先直接进行学生表和分数表的链接查询：

select * from student inner join score；

以及

查询到的表中共有189行数据，每个人都对应了成绩表中的所有成绩，也就是说每个人有了21个成绩，所以9个学生有189条数据，与上述的笛卡尔积相互对应起来，
接下来要给查询加上条件:我们知道在score表中，有student_id和course_id，因此要从score表中筛选出sutdnet_id和student中的id相等的分数

select * from student inner join score on student.id=score.student_id;

此时，我们看到的表中，所有人的成绩和本人都对应起来，现在就可以去筛选出许仙了；

select * from student inner join score on student.id=score.student_id and student.id =4;

到这一步其实查询已经结束了，我们已经获得了许仙的各科成绩了，但是只有课程id不知道课程的名字
进行改进：

select stu.sn,stu.name,sco.score,cou.name from student stu inner join score sco on stu.id=sco.student_id
inner join course on score.course_id=course.id and student.id=4;

分析上述代码，要查询的是学生表中的学生sn和name，以及成绩表中的score，以及课程表中的name；
首先通过分数表中的学生id和学生表中的id对应查到当前每个学生的成绩，然后通过分数表中的课程id在课程表中查询相对应的课程名；
上述代码也可以不使用inner join来查询：

select stu.sn,stu.name,sco.score,cou.name as 课程名 from student stu,score sco,course cou
where stu.id=sco.student_id
and sco.course_id = cou.id
and stu.name='许仙';

2.2 查询所有学生的总成绩

重点关注查询的条件，首先stu.id=sco.student_id，此条件用于查询属于每个学生的对应成绩，

select stu.sn,stu.name,sum(score) from student stu,score sco where stu.id=sco.student_id;

这样子查询到的结果并没有将学生分开，而是查询到了单个学生的成绩，并且也不是单个学生的总成绩，而是将成绩表中的所有的成绩进行了加和，但是为什么只显示了李逵呢，去掉sum再试试

select stu.sn,stu.name,score from student stu,score sco where stu.id=sco.student_id;

由此可以看出，因为没有设置对单个学生的查询条件，显示结果将所有的score加和到了一起，并且只显示了第一个学生
对上述代码进行改进：加上分组将每个学生的成绩都分开；

select stu.sn,stu.name,sum(score) from student stu,score sco where stu.id=sco.student_id
group by stu.id

3.外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
首先是左连接，以左表为主表，显示左表中所有的内容，

select stu.id,stu.name,sco.score from student stu left join score sco on stu.id=sco.student_id group by stu.id;

右连接，以右表为主，显示右表中有的内容：

select stu.id,stu.name,sco.score from student stu right join score sco on stu.id=sco.student_id group by stu.id;

可以看出左链接中显示了左表也就是student表的全部内容而右链接，显示了右表score中的全部内容，null的内容不显示，如果不指定左右链接，就会显示两张表的交集都不为空的情况

再来看看如何区分左右表：

 select stu.id,stu.name,sco.score from score sco left join student stu  on stu.id=sco.student_id group by stu.id;

在from之后将score和student的位置进行了互换，可以看出当前结果以score表为主表，显示其所有内容，null的就不显示

4. 自连接

例子：查询所有计算机原理比java成绩高的分数信息
首先看看课程表中的信息：

可以看到：计算机原理的id=3；java的id=1；
因此我们需要在score表中去搜索id=3的成绩>id=1的成绩
需要进行自己与自己的内容进行对比，这种方式成为自链接
自链接的实现:
需要注意的是：我们在实现该次查询的时候，要注意学生的id需要相等，因为我们查询的是同一个学生的计算机原理比java成绩高的成绩信息

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

查询结果种只显示了计算机原理的成绩，这是由于我们联合查询中 select s2.*；条件中查询的是 s2.course_id=3的课程信息，
以上查询可以表示为，查询s1和s2表中，学生id相等的情况下；表1中的course_id=1和course_id=3且1的成绩小于3的成绩，并显示表二中信息，
其实就是在对比表1和表2中的信息，首先筛选出学生id相等的信息，然后筛选出表2中的courseid=3的成绩大于表1中coureseid=1的信息，并将其显示出来

5.子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
例子：查询许仙的同班同学；
首先得知道许仙在哪个班级；

select   *  from student where classes_id = (select classes_id from student stu where stu.name='许仙');

多行子查询：
查询语文或英文课程的成绩信息：
关键字IN和NOT IN
结合上述的嵌套做法，先得知道语文或英文的课程id

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

6.合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。
重点：查询的结果格式需要保持一致：

• 查询id小于3，或者名字为“英文”的课程：

union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

• 案例：查询id小于3，或者名字为“Java”的课程