【Mysql数据库基础05】子查询 where、from、exists子查询、分页查询

1 where子查询

1.1 where后面的标量子查询

1.谁的工资比Abel高？

select *
from employees
where salary > (select salaryfrom employeeswhere last_name = 'Abel'
);

2.返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资

select last_name,job_id,salary
from employees
where job_id = (select job_idfrom employeeswhere employee_id = 141
)
and salary > (select salaryfrom employeeswhere employee_id = 143
);

3.返回公司工资最少的员工的last_name,job_id和salary

select last_name,job_id,salary
from employees
where salary = (select min(salary)from employees
);

1.1.1 having后的标量子查询

查询最低工资大于50号部门最低工资的部门id和其最低工资

可以拆分去考虑
1.查询50号部门最低工资

select min(salary)
from employees
where department_id = 50;

2.查询每个部门的最低工资

select department_id,min(salary)
from employees
group by department_id;

3.合并

select department_id,min(salary)
from employees
group by department_id
having min(salary) > (select min(salary)from employeeswhere department_id = 50
);

1.2 where后面的列子查询

单列多行
IN/NOT IN 任意一个
ANY/SOME 某一个
ALL 所有

IN 等于 = ANY

1.返回location_id是1400或1700的部门中的所有员工姓名

select last_name
from employees 
where department_id in (select distinct department_idfrom departmentswhere location_id in (1400,1700)
);

2.返回其它工种中比job_id为’IT_PROG’工种任一工资低的员工的,工号、姓名、job_id 以及salary

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

1.3 where后面的行子查询（了解即可）

一行多列或者多行多列

查询员工编号最小并且工资最高的员工信息

select *
from employees
where (employee_id,salary) = (select min(employee_id),max(salary)from employees
);

2 from子查询

查询每个部门的平均工资的工资等级

select avg_dep.department_id,avg_dep.avg,g.grade_level
from job_grades g
inner join (select department_id,avg(salary) as avgfrom employeesgroup by department_id
)as avg_dep
on avg_dep.avg between g.lowest_sal and g.highest_sal;

3 exists子查询（相关子查询）

只关心有没有这个值

查询有员工的部门名

select department_name
from departments d
where exists(select *from employees ewhere d.department_id = e.department_id
);

4 分页查询

limit offset,size
offset 要显示条目的索引

1.查询前五条的员工信息

select *
from employees
limit 0,5; #起始索引是0，一共显示5条

2.查询第11条~第25条的员工信息

select *
from employees
limit 10,15;

3.有奖金的员工信息，并且工资较高的前10名显示出来

select *
from employees
where commission_pct is not null
order by salary desc
limit 10;

5 联合查询

查询中国用户中男性的信息以及外国用户中年男性的用户信息

select * from t_ca where csex='男'
union
select * from t_ua where tGender = 'male';

联合查询的特点：
1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句查询的每一列的类型和顺序最好一致
3.union会去重
不想去重的话，可以使用union all关键字

6 练习

1.查询和zlotkey相同部门的员工姓名和工资

select last_name,salary
from employees
where department_id = (select department_idfrom employeeswhere last_name = 'Zlotkey'
);

2查询工资比公司平均工资高的员工的员工号，姓名和工资。

select employee_id,last_name,salary
from employees
where salary > (select avg(salary)from employees
);

3查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资

select employee_id,last_name,salary
from employees e
inner join (select department_id,avg(salary) as agfrom employeesgroup by department_id
) avg_dep
on avg_dep.department_id = e.department_id
where salary > avg_dep.ag;

4.查询管理者是king的员工姓名和工资

select last_name,salary
from employees
where manager_id in(select employee_idfrom employeeswhere last_name = 'K_ing'
);

5.查询工资最高的员工的姓名，要求first_name和iast_name显示为一列，列名为 姓.名

select concat(first_name,last_name) as '姓.名'
from employees
where salary = (select max(salary)from employees
);