MySQL--008 多表查询2

需要用到的员工表和部门表

 

1.联合查询：

use test;
select * from company where salary<200000
union all #不去重
select * from company where age<25;

select * from company where salary<200000
union   #去重
select * from company where age<25;

 2.子查询：SQL语句中嵌套select语句，称为嵌套查询，又称子查询

2.1标量子查询：返回的结果是单个值

-- 1.查询销售部的所有员工信息

select id from dept where name='销售部';
select * from company where dept_id=4;

select * from company where dept_id=(select id from dept where name='销售部');

 

-- 2.查询在陈赫入职日期之后的员工信息
select entrydate from company where name='陈赫';
select * from company where entrydate>'2022-05-02';

select * from company where entrydate>(select entrydate from company where name='陈赫');

 

2.2列子查询:子查询返回的结果是一列，可以是多行

-- 1.查询销售部和市场部的所有员工信息
select id from dept where name='销售部' or name='市场部';
select * from company where dept_id in (2,4);
select * from company where dept_id in (select id from dept where name='销售部' or name='市场部');

 

-- 2.查询比财务部所有人工资都高的员工信息
select *from company where salary>all(select salary from company where dept_id=(select id from dept where name='财务部'));

 

 

-- 3.查询比研发部的任意一人工资高的员工信息
select *from company where salary>any(select salary from company where dept_id=(select id from dept where name='研发部'));

 

 

 

2.3行子查询：子查询返回的结果是一行，可以是多列

 1.查询与曾斌的薪资及直属领导相同的员工信息
select salary,managerid from company where name='曾斌';
select * from company where(salary,managerid) =(20000,4);


select * from company where(salary,managerid) =(select salary,managerid from company where name='曾斌');

2.4表子查询：返回的结果是一个表

-- 1.查询与曾斌，段俊的职位和薪资相同的员工信息
select job ,salary from company where name in('曾斌','段俊');
select * from company where (job,salary) in (select job ,salary from company where name in('曾斌','段俊'));

2.查询入职日期在2022-05-06之后的员工信息，及其部门信息
select * from company where entrydate>'2022-05-06';
select c.*,d.* from(select * from company where entrydate>'2022-05-06') c left join dept d on c.dept_id=d.id