MySQL子查询

./*
子查询：
分类：
按子查询出现的位置
		select 后面：
			仅仅支持标量子查询
		from 后面：
			支持表子查询
		where或having后面：
			标量子查询（单行）
			列子查询（）
			行子查询
		exists后面（相关子查询）：
			表子查询
按结果的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集只有一行多列）
	表子查询（结果集一般为多行多列）
*/
#一。where或having后面
/*
1.标量子查询（单行子查询）
2.列子查询（多行子查询）
3.行子查询（多行多列）
特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询：一般搭配着单行操作符使用
> < >= <= = <>
列子查询：一般搭配着多行操作符使用
in  any/some、 all
*/

#1。标量子查询（一列一行）
#案例：谁的工资比Abel高
#1 查询abl的工资
use myemployees;
select salary from employees where last_name ='Abel';
#查询员工工资比able的高的员工信息
select  * from employees where salary>(
select salary from employees where last_name ='Abel'
);

#案例二：返回job_id与141员工相同，salary比143号员工多的员工 姓名，job_id和工资
select last_name ,job_id,salary from employees where job_id =(
select job_id from employees where employee_id =141
) and salary >(
select salary from employees where employee_id=143
);

#案例三：返回公司工资最少的员工的last_name,job_id 和salary
select last_name,job_id,salary from employees where salary=(select min(salary) from employees);

#查询最低工资大于50号部门最低工资的部门id和最低工资
select department_id,min(salary) from employees group by department_id having min(salary)>(
select min(salary) from employees where department_id=70
);

#2.列子查询（一列多行）
#案例：返回location_id 是1400或1700的部门的所有员工姓名
#第一种是用左外连接
select last_name,l.location_id from employees e left outer join  departments d on d.department_id=e.department_id
left outer join locations l on d.location_id =l.location_id where l.location_id in (1400,1700); 

#第二种
select last_name from employees where department_id in(
select department_id from departments where location_id in(1400,1700)
);

#案例2：返回其他部门中比job_id 为‘IT_PROG’部门任一工资低的员工的员工号，姓名，job_id以及salary

select employee_id,job_id,last_name,salary from employees where 
salary <any(select salary from employees where job_id='IT_PROG') and job_id <>'IT_PROG';

select employee_id,job_id ,last_name,salary from employees where salary <(select min(salary) from employees where job_id='IT_PROG')
and job_id <>'IT_PROG';

#行子查询（一行多列，多行多列）
#查询员工编号最小，工资最高的员工信息
select * from employees where (employee_id,salary)=(select min(employee_id),max(salary) from employees);

#查询每个部门的员工个数
select d.*,count(employee_id) from employees e right outer join departments d on d.department_id =e.department_id group by department_id;

#select 后面
select d.* ,(select count(*) from employees e where  e.department_id=d.department_id) '个数' from departments d;

#查询员工号=102的部门号
select (select department_name from departments d inner join employees  e on e.department_id=d.department_id where e.employee_id =102) '部门号';


#4.exists(相关子查询)
/*
特点：bool类型
*/
#查询员工名和部门名
select last_name，department_name from employees e left outer join departments d on d.department_id=e.department_id;

#查询有员工名的部门名
select department_name from departments d where d.department_id in(select department_id from employees);

#作业：查询和Zlotkey相同部门的员工姓名和工资
select last_name ,salary from employees where department_id=(
select department_id from employees where last_name='Zlotkey'
);

#作业：查询工资比公司平均工资高的员工的员工号，姓名和工资
select employee_id,last_name,salary from employees where salary>(
select avg(salary) from employees 
);

#查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
select employee_id,last_name,salary from employees e
inner join (select avg(salary) '平均工资' ,department_id from  employees group by department_id) avg_1 on  e.department_id=avg_1.department_id
where e.salary>avg_1.平均工资 ;

#查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#找出相同部门的员工名和部门名
select last_name,department_id from employees e where e.department_id in(select department_id from departments d group by department_id );

select last_name,department_id from employees e where e.department_id in(select department_id from departments d group by department_id )
and last_name like '%u%' order by e.department_id desc;

use myemployees;
#查询在部门location_id 为1700的部门工作员工的员工号
#第一种：用内连接
select employee_id,d.department_id,l.location_id from employees e inner join departments d on e.department_id=d.department_id
inner join locations l on d.location_id=l.location_id where l.location_id=1700;

#第二种：用子查询
select employee_id,e.department_id from employees e inner join (
select department_id ,location_id from departments where location_id=1700 
) d on d.department_id =e.department_id;

#查询管理者是K_ing 的员工姓名和工资
#自连接
select e.last_name,e.salary from employees e inner join employees m on e.manager_id=m.employee_id where m.last_name='K_ing';

#子查询
select last_name,salary from employees where manager_id in(
select employee_id from employees  where last_name ='K_ing' 
);


#查询工资最高的员工名，要去first_name和last_name显示一列，列名为姓名
select max(salary) ,concat(last_name,'_',first_name) '姓名' from employees;
select concat(last_name,'_',first_name) '姓名' from employees where salary =(select max(salary) from employees);

/*
测试题3：
已知 表sudentinfo
id学员
name ，
email 比如john@qq.com
sex
age
gradeId
已知表 grade
id 年纪编号
gradeName 年级名称

一。 查询所有学员的邮箱用户名
select substring（email，1，instr（email,'@'）-1）from studentinfo;

二，查询男生和女生的个数
select  count(sex) from studentinfo group by sex;

三，查询年龄大于18岁的所有学员的姓名和年级名称
select name,gradeName from studentinfo s inner join grade g  on g.id=s.gradeIdn where age>18;

select name,(select gradeName from grade ) from studentinfo where afge>18;


四，查询哪个年级的学员的最小年龄>20岁
select name,age,gradeName from studentinfo s inner join (select gradeName,id from grade g  group by gradeName ) table1 on s.gradeId=table1.id where min(age)>20
 







*/

use myemployees;
#查询工资最低的员工信息：salary
select salary from employees where salary=(select min(salary) from employees );

#查询平均工资最低的部门信息
select d.* ,min(pj) '最小平均工资'from departments d left outer join (select avg(salary) 'pj',department_id from employees e group by department_id ) table2
on table2.department_id =d.department_id ;

#查询平均工资最低的部门信息和该部门的平均工资
select d.* ,min(pj) '最小平均工资' ,table2.pj '平均工资'from departments d left outer join (select avg(salary) 'pj',department_id from employees e group by department_id ) table2
on table2.department_id =d.department_id ;


#查询平均工资最高的job信息
select avg(salary) from employees group by job_id order by job_id desc limit 1;


#查询平均工资高于公司平均工资的部门有哪些
use myemployees;
select avg(salary) ,department_id from employees e group by department_id having avg(salary)>(
select avg(salary) from employees
); 

#查询公司中所有manager的详细信息
select * from employees where employee_id in(select manager_id from employees);

select distinct e.* from employees e inner join employees m on e.employee_id =m.manager_id; 

#各个部门中最高工资中最低的哪个部门最低工资是多少
select max(salary) '最高工资',department_id  from employees  group by department_id ;
select 最高工资 最低工资,department_id 
from (select max(salary) '最高工资',department_id  from employees  group by department_id ) table1
order by 最高工资 asc limit 1;

#查询平均工资最高的部门manager的详细信息：last_name，department_id,email, salary
#先求出每个部门的平均工资，并且求出最高工资部门ID
select department_id from employees group by department_id order by avg(salary) desc limit  1;
select last_name,e.department_id,email,employee_id ,e.manager_id,salary from employees e join 
departments d on d.manager_id=e.employee_id where d.department_id=(
select department_id from employees group by department_id order by avg(salary) desc limit  1
);