mysql数据库外部可连接 mysql外连接语句

MySql外连接查询

• 外连接查询

• 合并查询数据记录

• 子查询

• 带关键字IN的子查询
• 带关键字EXISTS的子查询
• 带关键字ANY的子查询
• 带关键字ALL的子查询

外连接查询

1. 当我们在查询数据时，要求返回所操作表中至少一个表的所有数据记录，通过SQL语句“OUTER JOIN…ON”来实现。外连接数据查询语法形式如下：
2. SELECT field1,field2,…,fieldn
   FROM tablename1 LEFT|RIGHT [OUTER] JOIN tablename2
   ON CONDITION
3. 在上述语句中，参数fieldn表示所要查询的字段名字，来源于所连接的表tablename1和tablename2，关键字OUTER JOIN表示表进行外连接，参数CONDITION表示进行匹配的条件。
   外连接查询可以分为以下二类：
4. 左外连接
   外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字LEFT JOIN左边的表为参考表。左连接的结果包括LEFT OUTER字句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表均为空值。
   例如：查询所有学生的学号、姓名、班级编号、性别、班级名、班主任信息，具体SQL语句如下：

mysql>  use school;   #选择数据库school                                                                                                                             
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右边的表为参考表，如果右表的某行在左表中没有匹配行，左表将返回空值。

例如：查询所有班级的所有学生信息。具体SQL语句如下：

mysql>  use school;   #选择数据库school                                                                                                                             
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,…,fieldn FROM tablename1
UNION | UNION ALL
SELECT field1,field2,…,fieldn FROM tablename2
UNION | UNION ALL SELECT field1,field2,…,fieldn ;
注意： 多个选择语句select 的列数相同就可以合并,union和union all的主要区别是union all是把结果集直接合并在一起，而union 是将union all后的结果再执行一次distinct，去除重复的记录后的结果。

mysql>  use school;   #选择数据库school                                                                                                                             
mysql>  select teacher from class union all select name from student; #查询班级表所有老师和学生表中所有学生姓名 
mysql>  select teacher as people from class union all select name as people from student; #同上，使用别名查询 
mysql>  select teacher,id from class union all select name,class_id from student; #查询班级表所有(老师、班级ID)和学生表中所有学生(姓名、班级ID)

子查询

1. 所谓子查询，是指在一个查询中嵌套了其他的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。
2. 通过子查询可以实现多表查询，该查询语句中可能包含IN、ANY、ALL和EXISTS等关键字，除此之外还可能包含比较运算符。理论上，子查询可以出现在查询语句的任意位置，但是在实际开发中子查询经常出现在WHERE和FROM子句中。
3. 带比较运算符的子查询
   子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中，<>与!=是等价的。比较运算符在子查询中使用得非常广泛，如查询分数、年龄、价格和收入等。

例如：查询student 表中“小花”所在班级班主任的名字。SQL语句如下：

mysql>  use school;   #选择数据库school                                                                                                                             
mysql>  select teacher from class where id = (select class_id from student where name='小花'); #查询“小花”所在班级班主任的姓名

带关键字IN的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这时可以使用IN关键字，SQL示例如下：
NOT IN的用法与IN相同。
例如：查询student 表中“小花”所在班级班主任的名字。SQL语句如下：

mysql>  use school;   #选择数据库school 
mysql>  select teacher from class where id in (select class_id from student where name='小花');     #查询student 表中“小花”所在班级班主任的名字                                                                                                                          
mysql>  select teacher from class where id in (select class_id from student where name like '小%'); #查询姓名以“小”字开头的学生所在班级班主任的姓名

带关键字EXISTS的子查询

关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行；如果至少返回一行，那么EXISTS的结果为true，此时外层语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。
例如：如果102班存在学生记录，就查询102班的班级信息的记录。SQL示例语句如下：

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

带关键字ANY的子查询

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

mysql>  use school;   #选择数据库school     
mysql>  create table scholarship (score  int, level varchar(64));      
mysql>  insert into scholarship values(240, '二等奖'),(257,'一等奖');                                                                                                                  
mysql>  select st.id, st.name, st.math+st.chinese+st.english  total from grade st where (math+chinese+english) >= ANY (select score from scholarship); #查询能获得奖学金的学院记录

带关键字ALL的子查询

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

mysql>  use school;   #选择数据库school                                                                                                                       
mysql>  select st.id, st.name, st.math+st.chinese+st.english  total from grade st where (math+chinese+english) >= ALL (select score from scholarship); #查询能获得一等奖学金的同学记录 
mysql>  select st.id, st.name, st.math+st.chinese+st.english  total from grade st where (math+chinese+english) < ALL (select score from scholarship); #查询不能获得奖学金的同学记录