MySQL数据库-数据查询-联合查询

内连接查询

在实际开发中，我们会联合多个表来实现查询，比如把班级表和学生表联合起来就同时可以看到班级，老师学员的信息。

班级表：

ID	名称	班主任
101	一班	Mr.Wang
102	萌新二班	Mr.Zhang
103	萌新三班	Mr.Li

对应的学生表：

姓名	班级ID
小花	101
小红	102
小军	102
小白	101

将两个表中的ID相同的记录关联气啦组成一个新的"列表"，这就是联合查询：

CLASS_ID	名称	班主任	姓名
101	一班	Mr.Wang	小花
102	二班	Mr.Zhang	小红
102	二班	Mr.Zhang	小军
101	一班	Mr.Wang	小白

这种只有2张表匹配的行才能显示的连接方式在MySQL之中称为内连接：INNER JOIN

在MySQL中内连接数据查询通过“INNER JOIN...ON”语句来实现，具体语法形式如下：

SELECT field1,field2...FROM tablename1

INNER JOIN tablename2[INNER JOIN tablenamen]ON CONDITION;

其中，参数field表示要查询的字段名，来源于所连接的表tablename，关键字INNER JOIN表示表进行内连接，参数CONNDITION表示进行匹配条件。

代码示例：

mysql> create table class(id int not null unique,grade varchar(64),name varchar(64));    #创建班级表
mysql> insert class values(101,'一班','Mr.Wang'),(102,'二班','Mr.Zhang'),(103,'三班','Mr.Li');    #插入数据
mysql> create table student(class_id int ,name varchar(64));    #创建学生表
mysql> insert student values(101,'小花'),(102,'小红'),(102,'小军'),(101,'小白');    #插入数据
mysql> select*from class;    #显示class表中所有数据
mysql> select*from student;    #显示student表中所有的数据
mysql> select*from class a inner join student b where a.id=b.class_id;    ##查询class 表和student 表中班级字段相同的记录并进行内连接 

 这里我们创建成功班级表和学生表后，将这两张表中id字段相同的记录并进行内连接，并给class表起了别名为‘a’,student表起了别名为‘b’。

为表取别名

当表名特别长时，直接使用表名很不方便，或者在实现自然连接操作时，直接使用表名无法区别表。为了解决这一类问题，MySQL提供了一种机制来为表取别名，具体语法如下：

SELECT field1,field2...[AS]otherfield

FROM tablename1[AS]othertablename1

...

tablenamen[AS]othertablenamen

WHERE othertablename1.field=othertablename.field...;

其中，参数tablename为表原来的名字，参数othertablename为新表名，之所以要设置新的名字，是为了让SQL语句代码更加直观，更加人性化和更加复杂的功能。

自连接

内连接查询中存在一种特殊的等值连接—自连接，就是指表与其自身进行连接。

如：查询学生“小红”所在班级的其他学生都有谁。

代码如下：

mysql> select t1.class_id, t1.name from student t1 inner join student t2 on t1.class_id=t2.class_id and t2.name='小红';    #查询学生"小红"所在班级的其他学生 

 只有当class_id都相同时，指定姓名小红就可以查询出小红所在班级的其他学生。

等值连接

内连接查询中的等值连接就是在关键字ON后的匹配条件中通过等于关系运算符（=）来实现等值条件。

代码示例：

mysql> select*from class as a inner join student as b on a.id=b.class_id;    

 不等值连接

内连接查询中的不等连接就是在关键字ON后的匹配条件中通过除了等于关系运算符来实现不等条件外，还可以使用关系运算符包含“>”“>=”“<”“<=”和“!=”等运算符号。

代码示例：

mysql> select*from class as a inner join student as b where a.id!=b.class_id;

外连接查询

当我们在查询数据时，要求返回所操作表中至少一个表的所有数据记录，通过SQL语句“OUTER JOIN...ON”来实现。外连接数据查询语法如下：

SELECT field1,field2,...

FROM tablename1 LEFT|RIGHT [OUTER]JOIN tablename2

ON CONDITION;

在上述语句中，参数field表示所要查询的字段名字，来源于所要连接的表tablename,关键字OUTERJOIN表示表进行外连接，参数CONDITION表示进行匹配的条件。

外连接查询可以分为以下二类：

左外连接

外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字LEFI JOIN左边的表为参考表。做连接的结果包括LEFT OUTER字句中指定的左表中的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表均为控制。

代码示例：

mysql> select*from student as a left join class as b on a.class_id=b.id;    #左连接查询所有学生对应的班级信息
mysql> select*from class as a left join student as b on a.id=b.class_id;    #左连接查询所有班级的学员信息

查询所有学员对应的班级信息：

查询所有班级对应的学员信息：

右外连接

外连接查询中的右外连接在新关系中执行匹配条件时，以关键字RIGHT JOIN右边的表为参考表，如果右表的某行在左边中没有匹配行，左表将返回NULL。

代码示例：

mysql> select*from student as a right join class as b on a.class_id=b.id;    #右连接查询所有学生对应的班级信息
mysql> select*from class as a right join student as b on a.id=b.class_id;    #右连接查询所有班级的学员信息

右连接查询所有班级对应的学生信息：

右连接查询所有学员对应的班级信息：

合并查询数据记录

在MySQL中通过关键字UNION来实现合并操作，即可以通过其将多个SELECT语句的查询结果合并在一起组成新的关系。在MySQL中，合并查询数据记录可以通过SQL语句UNION来实现，具体语法如下：

SELECT field1,field2...FROM tablename1

UNION|UNION ALL

SELECT field1,field2,... FROM tablename2

UNION|UNION ALL

....;

注意：多个选择语句select的列数相同就可以合并，union和union all的主要区别是union all是把结果集直接合并在一起，而union是将union all后的结果再执行一次distinct，去除重复的记录后的结果。

代码示例：

mysql> select name from class union all select name from student;    #查询班级表所有老师和学生表中所有学生姓名 

子查询

所谓的子查询，是指在一个查询中嵌套了其他的若干查询，即在一个SELECT语句的WHERE或FROM字句包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE字句中的SELECT查询被称为子查询，也被称为嵌套查询。

通过子查询可以实现多表查询，该查询语句中可能包含IN,ANY,ALL和EXISTS等关键字，除此之外还可能包含比较运算符。理论上，子查询可以出现在查询语句的任意位置，但是在实际开发子查询经常出现在WHERE和FROM子句中。

带比较运算符的子查询

子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中<>与!=是等价的。比较运算符子啊子查询中使用的非常广泛，如查询分数，年龄，价格和收入等。

例如：查询student表中“小花”所在班级班主任的姓名。

代码示例：

mysql> select name from class where id=(select class_id from student where name='小花');    #查询“小花”的班主任

注意：使用比较运算符时，select子句会的的记录数不能大于1条！

带关键字IN的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这时可以使用IN关键字。例如：查询“小花”所在班级班主任的姓名。

代码如下：

mysql> select name from class where id in(select class_id from student where name='小花');

带关键字EXISTS的子查询

关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回，如果至少返回一行，那么EXISTS的结果为true,此时外层语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

代码示例：

mysql> select* from class where id=102 and exists(select*from student where class_id=102);     #如果102班存在学生记录，就查询102班的班级信息

这里class_id=102在表student是存在的因此返回true。

带关键字ANY的子查询

关键字ANY表示满足其中任一条件。使用关键ANY时，只要满足内层查询语句返回的结果中的任何一个就可以通过该条件来执行外层查询语句。例如，需要查询哪些学生可以获取奖学金，那么首先要有一张奖学金表，从表中查询出各种奖学金要求的最低分，只要一个同学的乘积大于等于不同奖学金最低分的任何一个，这个同学就可以获得奖学金。关键字ANY通常和比较运算符一起使用。例如，“>ANY”表示大于任何一个值，“=ANY”表示等于任何一个值。

代码示例：

mysql> create table prize(score int,name varchar(64));    #创建奖学金表
mysql> insert into prize values(95,'一等奖'),(85,'二等奖');    #插入数据
mysql> create table cj(name varchar(64),score int);    #创建学生成绩表
mysql> insert into cj values('Mr.Wang',95),('Mr.Li',86);    #插入数据
mysql> select *from cj where score>=any(select score from prize);    #查询能获得奖学金的学院记录 

这样就可以查出所有可以拿奖学金的学生。

带关键字ALL的子查询

键字ALL表示满足所有条件。使用关键字ALL时，只有满足内层查询语句返回的所有结果才可以执行外层查询语句。例如，需要查询哪些同学能够获得一等奖学金，首先要从奖学金表中查询出各种奖学金要求的最低分。因为一等奖学金要求的分数最高，只有当成绩高于所有奖学金最低分时，这个同学才可能获得一等奖学金。关键字ALL也经常与比较运算符一起使用。例如，“>ALL”表示大于所有值，“<ALL”表示小于所有值。

mysql> select *from cj where score>=all(select score from prize);    #查询获得一等奖学金的同学

 这样就可以查出只获得一等奖的学生。