MySQL进阶 — 联合查询（外连接、内连接、子连接、合并查询）

• 联合查询：对两张及两张以上的表进行查询，就叫联合查询。
• 联合查询的原因：所要获得的数据来自于多张表，联合查询才能得到。

首先在数据库中我们创建四张表：班级表、学生表、课程表以及分数表

mysql> desc classes;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| desc  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.08 sec)

mysql> desc student;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sn         | int(11)     | YES  |     | NULL    |                |
| name       | varchar(30) | YES  |     | NULL    |                |
| qq_mail    | varchar(30) | YES  |     | NULL    |                |
| classes_id | int(11)     | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> desc course;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc score;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| score      | decimal(10,0) | YES  |     | NULL    |                |
| student_id | int(11)       | YES  |     | NULL    |                |
| course_id  | int(11)       | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

内连接

当两张或两张以上的表进行查询，不指定查询条件时，查到的就是表的笛卡尔积，指定了条件之后，查到的就是表之间的交集。
语法：
select 字段 from 表1 别名1 inner join 表2 别名2 on 连接条件 and 其他条件；
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

比如我们查询许仙同学的成绩，此时需要关联学生表和分数表：
select * from student inner join score on student.id = score.student_id and student.id=4;

mysql> select * from student inner join score on student.id = score.student_id and student.id = 4;
+----+------+--------+---------------+------------+----+-------+------------+-----------+
| id | sn   | name   | qq_mail       | classes_id | id | score | student_id | course_id |
+----+------+--------+---------------+------------+----+-------+------------+-----------+
|  4 |   31 | 许仙   | xuxian@qq.com |          1 | 10 |    67 |          4 |         1 |
|  4 |   31 | 许仙   | xuxian@qq.com |          1 | 11 |    23 |          4 |         3 |
|  4 |   31 | 许仙   | xuxian@qq.com |          1 | 12 |    56 |          4 |         5 |
|  4 |   31 | 许仙   | xuxian@qq.com |          1 | 13 |    72 |          4 |         6 |
+----+------+--------+---------------+------------+----+-------+------------+-----------+
4 rows in set (0.09 sec)

如果只是查询指定的字段，就可以写成：
select student.name, score.score from student inner join score on student.id = score.student_id and student.id = 4;

假如还要查找分数对应的课程名（学生表、分数表与课程表三张表联合）：

mysql> select student.name, score.score, course.name from student inner join score on student.id = score.student_id inner join course on score.course_id = course.id and student.id = 4;
+--------+-------+-----------------+
| name   | score | name            |
+--------+-------+-----------------+
| 许仙   |    67 | Java            |
| 许仙   |    23 | 计算机原理      |
| 许仙   |    56 | 高阶数学        |
| 许仙   |    72 | 英文            |
+--------+-------+-----------------+
4 rows in set (0.00 sec)

此时我们发现结果虽然查出来了，但是有两个name字段，这样容易混淆，因此别名就派上用场了！

mysql> select stu.name 学生名, sco.score, cou.name 课程名 from student stu inner join score sco on stu.id = sco.student_id inner join course cou on sco.course_id = cou.id and stu.id = 4;
+-----------+-------+-----------------+
| 学生名    | score | 课程名          |
+-----------+-------+-----------------+
| 许仙      |    67 | Java            |
| 许仙      |    23 | 计算机原理      |
| 许仙      |    56 | 高阶数学        |
| 许仙      |    72 | 英文            |
+-----------+-------+-----------------+
4 rows in set (0.00 sec)

我们对表和字段都设置了别名，查询结果看起来一目了然！

此外使用第二种内连接查询方式也是可以的，比如下面的SQL语句：
select stu.name, sco.score, cou.name 课程名 from student stu, score sco, course cou where stu.id = sco.student_id and sco.course_id = cou.id and stu.name = “许仙”;

mysql> select stu.name, sco.score, cou.name 课程名 from student stu, score sco, course cou where stu.id = sco.student_id and sco.course_id = cou.id and stu.name = "许仙";
+--------+-------+-----------------+
| name   | score | 课程名          |
+--------+-------+-----------------+
| 许仙   |    67 | Java            |
| 许仙   |    23 | 计算机原理      |
| 许仙   |    56 | 高阶数学        |
| 许仙   |    72 | 英文            |
+--------+-------+-----------------+
4 rows in set (0.08 sec)

注意：
1.在对多张表进行联合查询时，去找每个表之间的关系，也就是表之间的关联字段；
2.inner可以省略；
3.设置别名时的as可以省略；

练习：查询每个同学的总成绩以及同学的个人信息

假如没有写连接条件：

mysql> select stu.id, stu.name, sum(sco.score) from student stu inner join score sco group by stu.id;
+----+-----------------+----------------+
| id | name            | sum(sco.score) |
+----+-----------------+----------------+
|  1 | 黑旋风李逵      |           1307 |
|  2 | 菩提老祖        |           1307 |
|  3 | 白素贞          |           1307 |
|  4 | 许仙            |           1307 |
|  5 | 不想毕业        |           1307 |
|  6 | 好好说话        |           1307 |
|  7 | tellme          |           1307 |
|  8 | 老外学中文      |           1307 |
+----+-----------------+----------------+
8 rows in set (0.00 sec)

这样查询到的每个学生的总成绩就是成绩表中所有人的总成成绩了。。。

mysql> select stu.id, stu.name, sum(sco.score) from student stu inner join score sco on stu.id = sco.student_id group by stu.id;
+----+-----------------+----------------+
| id | name            | sum(sco.score) |
+----+-----------------+----------------+
|  1 | 黑旋风李逵      |            301 |
|  2 | 菩提老祖        |            120 |
|  3 | 白素贞          |            200 |
|  4 | 许仙            |            218 |
|  5 | 不想毕业        |            118 |
|  6 | 好好说话        |            178 |
|  7 | tellme          |            172 |
+----+-----------------+----------------+
7 rows in set (0.08 sec)

执行顺序：from求得笛卡尔积，on查出交集，然后group对查到的结果分组，最后得到每个学生的信息。

外连接

外连接：分为左连接和右连接，进行联合查询时，如果左边得表完全显示我们就说是左外连接，右边的表完全显示我们就说是右外连接。外连接是求并集的操作。
语法：
左外连接：
select 字段 from 表名1 left join 表名2 on 连接条件;
右外连接：
select 字段 from 表名1 right join 表名2 on 连接条件;

比如：查询所有同学的各科成绩以及同学的个人信息，如果该人没有成绩，也需要显示出来。

mysql> select stu.id, stu.name, sco.score from student stu, score sco where stu.id = sco.student_id group by stu.id;
+----+-----------------+-------+
| id | name            | score |
+----+-----------------+-------+
|  1 | 黑旋风李逵      |    71 |
|  2 | 菩提老祖        |    60 |
|  3 | 白素贞          |    33 |
|  4 | 许仙            |    67 |
|  5 | 不想毕业        |    81 |
|  6 | 好好说话        |    56 |
|  7 | tellme          |    80 |
+----+-----------------+-------+
7 rows in set (0.00 sec)

可以看出学生表中id为8的学生没有显示出来！采用外连接才可以显示出来

左连接

mysql> select stu.id, stu.name, sco.score from student stu left join score sco on stu.id = sco.student_id group by stu.id;
+----+-----------------+-------+
| id | name            | score |
+----+-----------------+-------+
|  1 | 黑旋风李逵      |    71 |
|  2 | 菩提老祖        |    60 |
|  3 | 白素贞          |    33 |
|  4 | 许仙            |    67 |
|  5 | 不想毕业        |    81 |
|  6 | 好好说话        |    56 |
|  7 | tellme          |    80 |
|  8 | 老外学中文      |  NULL |
+----+-----------------+-------+
8 rows in set (0.00 sec)

右连接

mysql> select stu.id, stu.name, sco.score from score sco right join student stu on stu.id = sco.student_id group by stu.id;
+----+-----------------+-------+
| id | name            | score |
+----+-----------------+-------+
|  1 | 黑旋风李逵      |    71 |
|  2 | 菩提老祖        |    60 |
|  3 | 白素贞          |    33 |
|  4 | 许仙            |    67 |
|  5 | 不想毕业        |    81 |
|  6 | 好好说话        |    56 |
|  7 | tellme          |    80 |
|  8 | 老外学中文      |  NULL |
+----+-----------------+-------+
8 rows in set (0.00 sec)

注意：
左连接以左表为准，右连接以右表为准；当左表中内容在右表中没有内容与其对应时，也要显示出来。

自连接

自连接：把一张表当作两张表来使用。

对于同一个同学而言，显示所有“计算机原理”比“Java”成绩高的成绩信息：


可以看出满足条件为学生1和学生3，那么这个SQL语句该怎么写？
select s1.* 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;

mysql> select s1.* 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;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
|  1 |    71 |          1 |         1 |
|  7 |    33 |          3 |         1 |
+----+-------+------------+-----------+
2 rows in set (0.00 sec)

子查询

子查询：嵌入在其他sql语句中的select语句，也称为嵌套查询

单行子查询：

查询“不想毕业”的同学同班同学：

按照常规思路来说，先把这个人所在的班级查出来，然后根据班级再查班级中的人。
select classes_id from student stu where stu.name = ‘不想毕业’;
select * from student where classes_id = 1;

mysql> select * from student where classes_id = (select classes_id from student stu where stu.name = '许仙');
+----+------+-----------------+-----------------+------------+
| id | sn   | name            | qq_mail         | classes_id |
+----+------+-----------------+-----------------+------------+
|  1 | 9982 | 黑旋风李逵      | xuanfeng@qq.com |          1 |
|  2 |  835 | 菩提老祖        | NULL            |          1 |
|  3 |  391 | 白素贞          | NULL            |          1 |
|  4 |   31 | 许仙            | xuxian@qq.com   |          1 |
|  5 |   54 | 不想毕业        | NULL            |          1 |
+----+------+-----------------+-----------------+------------+
5 rows in set (0.00 sec)

这样就查到了与不想毕业一个班的同学！

多行子查询：

查询语文或者英文课程的成绩信息：

使用in

mysql> select * from score where course_id in (select id from course where name = '语文' or name = '英文');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
|  4 |    98 |          1 |         6 |
| 13 |    72 |          4 |         6 |
| 17 |    43 |          6 |         4 |
| 18 |    79 |          6 |         6 |
| 20 |    92 |          7 |         6 |
+----+-------+------------+-----------+
5 rows in set (0.00 sec)

使用not in

mysql> select * from score where course_id not in (select id from course where name != '语文' and name != '英文');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
|  4 |    98 |          1 |         6 |
| 13 |    72 |          4 |         6 |
| 17 |    43 |          6 |         4 |
| 18 |    79 |          6 |         6 |
| 20 |    92 |          7 |         6 |
+----+-------+------------+-----------+
5 rows in set (0.00 s

合并查询

• union

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

查询id小于3，或者名字为“英文”的课程：
select * from course where id < 3 union select * from course where name = ‘英文’;

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

mysql> select * from course where id < 3 union select * from course where name = '英文';
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | Java               |
|  2 | 中国传统文化       |
|  6 | 英文               |
+----+--------------------+
3 rows in set (0.07 sec)

mysql> select * from course where id < 3 or name = '英文';
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | Java               |
|  2 | 中国传统文化       |
|  6 | 英文               |
+----+--------------------+
3 rows in set (0.00 sec)

• unionall

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

mysql> select * from student where id < 3 union all select * from student where name = '白素贞';
+----+------+-----------------+-----------------+------------+
| id | sn   | name            | qq_mail         | classes_id |
+----+------+-----------------+-----------------+------------+
|  1 | 9982 | 黑旋风李逵      | xuanfeng@qq.com |          1 |
|  2 |  835 | 菩提老祖        | NULL            |          1 |
|  3 |  391 | 白素贞          | NULL            |          1 |
+----+------+-----------------+-----------------+------------+
3 rows in set (0.00 sec)