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

1 where子查询

1.1 where后面的标量子查询

1.谁的工资比Abel高？

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

2.返回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
);

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