#案例1:谁的工资比 Abel 高?#①查询Abel的工资SELECT salary
FROM employees
WHERE last_name ='Abel'#②查询员工的信息,满足 salary>①结果SELECT*FROM employees
WHERE salary>(SELECT salary
FROM employees
WHERE last_name ='Abel');#案例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id 和工资#①查询141号员工的job_idSELECT job_id
FROM employees
WHERE employee_id =141#②查询143号员工的salarySELECT salary
FROM employees
WHERE employee_id =143#③查询员工的姓名,job_id 和工资,要求job_id=①并且salary>②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#①查询公司的 最低工资SELECTMIN(salary)FROM employees
#②查询last_name,job_id和salary,要求salary=①SELECT last_name,job_id,salary
FROM employees
WHERE salary=(SELECTMIN(salary)FROM employees
);#案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资#①查询50号部门的最低工资SELECTMIN(salary)FROM employees
WHERE department_id =50#②查询每个部门的最低工资SELECTMIN(salary),department_id
FROM employees
GROUPBY department_id
#③ 在②基础上筛选,满足min(salary)>①SELECTMIN(salary),department_id
FROM employees
GROUPBY department_id
HAVINGMIN(salary)>(SELECTMIN(salary)FROM employees
WHERE department_id =50);#非法使用标量子查询SELECTMIN(salary),department_id
FROM employees
GROUPBY department_id
HAVINGMIN(salary)>(SELECT salary
FROM employees
WHERE department_id =250);
2.列子查询(多行子查询)★
#案例1:返回location_id是1400或1700的部门中的所有员工姓名#①查询location_id是1400或1700的部门编号SELECTDISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)#②查询员工姓名,要求部门号是①列表中的某一个SELECT last_name
FROM employees
WHERE department_id INL(SELECTDISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700));#案例2:返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary#①查询job_id为‘IT_PROG’部门任一工资SELECTDISTINCT salary
FROM employees
WHERE job_id ='IT_PROG'#②查询员工号、姓名、job_id 以及salary,salary<(①)的任意一个SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(SELECTDISTINCT salary
FROM employees
WHERE job_id ='IT_PROG')AND job_id<>'IT_PROG';#或SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(SELECTMAX(salary)FROM employees
WHERE job_id ='IT_PROG')AND job_id<>'IT_PROG';#案例3:返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salarySELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(SELECTDISTINCT salary
FROM employees
WHERE job_id ='IT_PROG')AND job_id<>'IT_PROG';#或SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(SELECTMIN( salary)FROM employees
WHERE job_id ='IT_PROG')AND job_id<>'IT_PROG';
仅仅支持标量子查询
#案例:查询每个部门的员工个数SELECT d.*,(SELECTCOUNT(*)FROM employees e
WHERE e.department_id = d.`department_id`) 个数
FROM departments d;#案例2:查询员工号=102的部门名SELECT(SELECT department_name
FROM departments d
INNERJOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102) 部门名;
三、from后面
将子查询结果充当一张表,要求必须起别名
#案例:查询每个部门的平均工资的工资等级#①查询每个部门的平均工资SELECTAVG(salary),department_id
FROM employees
GROUPBY department_id
SELECT*FROM job_grades;#②连接①的结果集和job_grades表,筛选条件平均工资 between lowest_sal and highest_salSELECT ag_dep.*,g.`grade_level`FROM(SELECTAVG(salary) ag,department_id
FROM employees
GROUPBY department_id
) ag_dep
INNERJOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
四、exists后面(相关子查询)
语法:
exists(完整的查询语句)
结果:
1或0SELECTEXISTS(SELECT employee_id FROM employees WHERE salary=300000);#案例1:查询有员工的部门名#inSELECT department_name
FROM departments d
WHERE d.`department_id`IN(SELECT department_id
FROM employees
)#existsSELECT department_name
FROM departments d
WHEREEXISTS(SELECT*FROM employees e
WHERE d.`department_id`=e.`department_id`);#案例2:查询没有女朋友的男神信息#inSELECT bo.*FROM boys bo
WHERE bo.id NOTIN(SELECT boyfriend_id
FROM beauty
)#existsSELECT bo.*FROM boys bo
WHERENOTEXISTS(SELECT boyfriend_id
FROM beauty b
WHERE bo.`id`=b.`boyfriend_id`);