Java_SQL多表查询之连接查询、子查询

编译软件：Intellij IDEA

多表查询的常用查询为连接查询和子查询。

数据准备：

use oa;
-- 创建部门表
create table oa_dept(
    id int primary key auto_increment,
    name varchar(64)
);
-- 创建员工表
create table oa_emp(
    id int primary key auto_increment,
    name varchar(64),
    gender char(10),
    salary decimal(10,2),
    hire_date date,
    dept_id int
);

-- 增加部门信息

insert into oa_dept values(null,'研发部'),(null,'市场部'),(null,'行政部');
select * from oa_dept;

-- 增加员工共信息

insert into oa_emp values(null,'tony','男',25000.00,'2013-12-12',1),
                          (null,'jack','男',26000.00,'2017-08-12',1),
                          (null,'jessica','女',25000.00,'2015-12-12',2),
                          (null,'tiffany','女',15000.00,'2017-12-12',2),
                          (null,'aui','女',7000.00,'2017-03-12',3);

select * from oa_emp;

1. 连接查询之交叉查询

交叉查询就是把若干张表(至少是2张表)没有条件的连接在一起查询。
语法：

select [*] [列名] from a,b;
-- a,b 分别表示两个表
select a.* ,b.* from a,b;
-- 查询a的列和b的列
select *from a,b;
--查询两张表的所有信息

交叉查询：

-- 交叉查询 员工信息和部门信息
select * from oa_dept,oa_emp;

统计交叉查询的数量：

select count(*) from oa_dept,oa_emp;

统计交叉查询的数量是15，15怎么来的呢？实际上是两个表的笛卡尔乘积。
交叉查询的结果是错的，大部分都是无用的

2. 链接查询之内连接查询

在交叉查询基础上加上过滤条件。过滤条件就是多个表之间的关联联系，即主外键关系，去掉笛卡尔乘积中错误的数据。
内连接查询分：隐式内连接和显式内连接。
特点：查询公共的部分，既满足连接条件的部分，使用主外键关系作为连接条件的目的就是为了去掉无用的信息。

2.1 隐式内连接查询

语法：

select a.*,b.* from a,b where a.主键字段名=b.外键字段名 [and 其他条件];

a是主表，即oa_dept表
b是从表，即oa_emp表

select * from oa_dept as dept ,oa_emp as emp where dept.id=emp.dept_id;

2.2 显式内连接查询

select a.*,b.* from a [inner] join b on a.主键字段名=b.外键字段名 [where 其它条件];

select emp.id,emp.name,emp.gender,emp.salary,emp.hire_date,dept.name
from
     oa_dept dept inner join oa_emp emp on dept.id=emp.dept_id;

3. 链接查询之外连接查询

内连接查询的结果是公共的，如果要保证某个表的数据全部展示的情况下进行连接查询，那么就需要使用外连接查询，外连接查询分为左外连接和右外连接两种。

3.1 左外连接查询

左外连接是以join左表的表作为主表，展示主表的所有数据。根据条件查询连接右边的数据，如果满足条件则展示，如果不满足条件则显示null。
左外连接是在内连接基础上保证左表的数据全部表示。

select [*] [字段] from a left [outer] join b on a.主键字段名=b.外键字段名 [where 其他条件];

-- 左外连接

select * from oa_dept dept left outer join oa_emp emp on dept.id=emp.dept_id;

3.2 右外连接

右外连接是以join右边的表为主表，展示主表所有数据，根据条件查询join左边表的数据，如果满足则展示，不满足会议null展示。

select [*] [字段] from a right [outer] join b on a.主键字段名=b.外键字段名 [where 其他条件];

-- 右外连接
select * from oa_dept dept right outer join oa_emp emp on dept.id=emp.dept_id;

4. 子查询

连接查询不能解决复杂业务，需要使用子查询。
子查询是一个查询语句至少要有两个select语句，一个查询语句的查询结果还作为另外一个查询语句的条件。
子查询有查询的嵌套，子查询要使用()包含起来。共有三种情况：

• 子查询返回单行单列的值
• 子查询返回单列多行的值
• 子查询返回多列多行的值

4.1 子查询返回单行单列的值

子查询返回单行单列的值是，肯定是在where字句后面作为条件。

select 字段 from 表名 where 字段 运算符(子查询语句)

-- 查询最高工资的员工信息
select * from oa_emp where salary=(select max(salary) from oa_emp);

4.2 子查询返回多行单列的值

子查询返回多行单列的值，肯定是在where 字句后作为条件，因为是单列多行，所以一般使用 in。

select 字段 from 表名 where 字段 in (子查询语句);

-- 查询工资大于10000的员工在哪个部门
select dept.name from oa_dept where oa_dept.id in (select * from oa_emp where oa_emp.salary>=10000);

4.3 子查询返回多列多行的值

其实就是一张虚拟表，肯定是跟在from后面作为表。
虚拟表是需要取别名的。

select 列名 from (子查询) 别名 where 条件;

子查询返回多列多行一般是和内连接和外连接查询一起使用。
子查询返回多列多行与内连接查询的语法：

select 列名 from 表名 别名 inner join (子查询) 别名 on 关联条件 [where 条件];

• 左外连接：

select 列名 from 表名 别名 left join (子查询) 别名 on 关联条件 [where 条件];

• 右外连接：

select 列名 from 表名 别名 right join (子查询) 别名 on 关联条件 [where 条件];

关联条件就是：主表的主键(oa_dept.id)等于从表的外键(oa_emp.dept_id)

-- 查询2017年后入职的员工信息，包含部门信息
select * from oa_dept dept inner join
    (select * from oa_emp emp where emp.hire_date>'2017-01-01') emp on dept.id=emp.dept_id;