进阶7:子查询
/*
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句:称为主查询或外查询
分类:
称子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
表子查询
where或having后面:
标量子查询,
也支持列子查询
exists后面(相关子查询):
支持表子查询
按结果集的行列数不同
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有多行多列)
表子查询(结果集一般为多行多列)
*/#一、where或having后面/*1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)
特点
1.子查询放小括号内
2.子查询一般放在条件右侧
3.标量子查询,一般搭配在单行操作符使用
>< >= <= = <>
列子查询,一般搭配多行操作符使用
in 、any、some、all*/#1.标量子查询#案例1:谁的工资比abel高?USE myemployees ;SELECT*FROM
employees
WHERE salary >(SELECT
salary
FROM
employees
WHERE last_name ='Abel');#案例2:job_id与141号员工相同,salary比143号员工多的袁姓名,job_id和工资SELECT
job_id,
salary,CONCAT(LAST_NAME , FIRST_NAME)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和salarySELECT last_name,job_id,salary
FROM employees
WHERE salary =(SELECTMIN(salary)FROM employees
);#案例4; 查询最低工资大于50号部门最低工资的部门id和其最低工资SELECTMIN(salary)AS m,
department_id
FROM
employees
GROUPBY department_id ;HAVING m>(SELECTMIN(salary)FROM employees
WHERE department_id =50);#二、列子查询(多行子查询)#案例1返回location_id是1400或1700的部门中的所有员工姓名SELECT last_name
FROM employees
WHERE department_id IN(SELECT department_id
FROM departments
WHERE location_id IN(1400,1700))#返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及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';#案例3:返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary SELECT 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';#3、行子查询 (使用较少) #二、放在select后面 /*
支持标量子查询,输出结果仅为一行一列
*/#案例:查询每个部门员工的个数SELECT d.*,(SELECTCOUNT(*)FROM employees e
WHERE e.department_id = d.`department_id`)FROM departments d ;#案例2:查询员工号=102的部门名SELECT d.`department_name`FROM employees e
INNERJOIN departments d
ON e.`department_id`=d.`department_id`WHERE employee_id =102;#三、from后面#案例:查询每个部门平均工资的工资等级/*
将我们的查询结果变成一张表必须起别名
*/SELECT 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或者0
*/#案例1:查询员工的部门名SELECT department_name
FROM departments d
WHEREEXISTS(SELECT*FROM employees e
WHERE d.`department_id`=e.`department_id`);