【MySQL】多表联合查询、连接查询、子查询

【1】连接查询

连接查询的意义: 在用户查看数据的时候,需要显示的数据来自多张表.

内连接查询

内连接查询是最常见的连接查询，内连接查询可以查询两张或两张以上的表

内连接：[inner] join：从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留.

基本语法：左表 [inner] join 右表 on 左表.字段 = 右表.字段;
on表示连接条件: 条件字段就是代表相同的业务含义(如my_student.c_id和my_class.id)

当两个表中存在相同意义的字段的时候，就可以通过该字段来连接查询这两个表，当该字段的值相同时就可以查出该记录。

内连接可以没有连接条件: 没有on之后的内容,这个时候系统会保留所有结果。
例如：

-- 查询每个部门的所属员工
SELECT
   *
FROM
    dept3 d
    INNER JOIN emp3 e ON d.deptno = e.dept_id

 外连接查询

以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL。

左连接

left join: 左外连接(左连接), 以左表为主表
基本语法: from 左表 left join 右表 on 左表.字段 = 右表.字段;

左表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,右表的字段都置NULL。

例如：

SELECT
    *
FROM
    dept3 d
    Left JOIN emp3 e ON d.deptno = e.dept_id

 右连接

right join: 右外连接(右连接), 以右表为主表
基本语法: from 左表 right join 右表 on 左表.字段 = 右表.字段;
右表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,左表的字段都置NULL。

例：

SELECT
    *
FROM
    dept3 d
    RIGHT JOIN emp3 e ON d.deptno = e.dept_id

 【2】联合查询

联合查询
联合查询结果是将多个select语句的查询结果合并到一块因为在某种情况下需要将几个select语句查询的结果合并起来显示。比如现在需要查询两个公司的所有员工的信息，这就需要从甲公司查询所有员工信息，再从乙公司查询所有的员工信息，然后将两次的查询结果进行合并。

可以使用union和union all关键字进行操作

语法格式如下:

select 语句1
union[union 选项]
select 语句2
union|[union 选项]
select 语句n

其中union选项有两个选项可选
all：表示无论重复都输出
distinct: 去重(整个重复)（默认的）

-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id 
union  
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

 联合查询的意义:

1. 查询同一张表,但是需求不同 如查询学生信息, 男生身高升序, 女生身高降序
2. 多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的.

联合查询order by的使用
在联合查询中: order by不能直接使用（不能出现两次）,需要对查询语句使用括号才行；

select *from student 
where sex="woman" 
order by score
union
select *from 
student where sex="man" 
order by score;

这种情况是会报错的。因为语句中不允许出现两个order by。

select *from student where sex="woman" 
union
select *from student where sex="man" order by score;

如果是上边这样只出现一次他的意义就是等合并完成之后再进行排序就没有任何意义了，因为又把前边sex分好的类打乱了

(select *from student 
where sex="woman" 
order by score )
union
(select *from 
student where sex="man" 
order by score;)

这种方式的目的是为了让两个结果集先分别order by，然后再对两个结果集进行union。但是你会发现这种方式虽然不报错了，但是两个order by并没有产生最后的效果，所以应该改成如下：

select *from
(select *from student 
where sex="woman" 
order by score)student
union
select *from
(select *from student 
where sex="man" 
order by score)student ;

也就是说，order by不能直接出现在union的子句中，但是可以出现在子句的子句中。

 【3】子查询

通常我们在查询的SQL中嵌套查询，称为子查询。子查询通常会使复杂的查询变得简单，但是相关的子查询要对基础表的每一条数据都进行子查询的动作，所以当表单中数据过大时，一定要慎重选择

带in关键字的子查询

使用in关键字可以将原表中特定列的值与子查询返回的结果集中的值进行比较
如果某行的特定列的值存在，则在select语句的查询结果中就包含这一行。

例:查询成绩大于80的学生的所有信息，
先在子查询中查出成绩大于80的结果集，然后将原成绩表中的成绩与结果集进行比较，如果存在，就输出这条学生的记录。
 

select *
from student 
where score in
(select score from student where score>80);

-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄   (在一个表中的子查询)在一个表中的子查询
-- 1、查询最大年龄
select max(age) from emp3
-- 2、让每个员工的年龄与最大员工的年龄比较，相等则满足
select eid 员工号,ename 员工名字,age 员工年龄 from emp3 where age=(select max(age) from emp3) 
  
-- 查询年研发部和销售部的员工信息，包含员工号、员工名字
-- 方式一：关联查询
select * from dept3 d inner join emp3 e on d.deptno=e.dept_id and name in('研发部','销售部')
-- 方式二：子查询
-- 1、先查询研发部和销售部的部门号  deptno  1001和1002
select deptno from dept3  where name ='研发部' or name='销售部'
-- 2、查询那个员工的部门号是1001或者是1002
select * from emp3  where dept_id in(select * from dept3 where name in('研发部','销售部'))   这样写是错误的，子查询中最好不要查询全部字段，否则出错
select * from emp3  where dept_id in(select deptno from dept3  where name ='研发部' or name='销售部')
select eid 员工号,ename 员工名字 from emp3 where dept_id in('1001','1002')  上面括号中的语句其实就相当于‘1001’‘1002’   所以不用等号要用in
  
-- 查询研发部20岁以下的员工信息,包括员工号、员工名字，部门名字
-- 方式一：子查询（查询返回的结果是一张临时表）
-- 1、先在部门查询出研发部
select * from dept3 where name='研发部'  #查询返回的结果是一张临时表
-- 2、查询员工表中年龄<20以下的员工
select * from emp3 where age<20     #查询返回的结果是一张临时表
-- 3、将上两步合并（关联），将上两步查询结果当作表使用
select * from (select * from dept3 where name='研发部') d inner join (select * from emp3 where age<20) e on d.deptno=e.dept_id
-- 方式二：连接查询
select * from dept3 d left join emp3 e on d.deptno=e.dept_id where (d.name='研发部' and e.age<20)
select * from dept3 d inner join emp3 e on d.deptno=e.dept_id and (d.name='研发部' and e.age<20)

带比较运算符的子查询

如果可以确认子查询返回的结果只包含一个单值，那么可以直接使用比较运算符连接子查询。
经常使用的比较运算符包括等于（=）、不等于（<>或!=）、小于（<）、大于（>）、小于等于（<=）和大于等于（>=）。

select *
from student 
where score> 
(select score 
from scholarship
where dengji=1);

查询奖学金等级为1的学生信息

带exists的子查询

exists: 是否存在的意思, exists子查询就是用来判断某些条件是否满足(跨表),
exists是接在where之后
exists返回的结果只有0和1.

例：如果存在成绩大于90的人则列出整个表的记录

select *
from student 
where exists
(select *from student where score>90);

带any关键字的子查询

any关键字表示满足其中的任意一个条件，使用any关键字时，只要满足内层查询语句结果的的任意一个，就可以通过该条件来执行外层查询语句。

select *
from student 
where addrid<any
(select addrid 
from addr);

 带all关键字的子查询

all和any刚好是相反的，all关键字表示满足所有结果，使用all关键字，要满足内层查询语句的所有结果，才可以通过该条件来执行外层查询语句。

select *
from student 
where addrid>all
(select addrid 
from addr);