#进阶八、子查询/*
出现在其他语句中的select语句,那么select语句称为子查询/内查询,外面的其他语句称为主查询/外查询。
子查询不一定只出现在select语句的内部,在以后的学习中,子查询有可能出现在insert、update、delete语句中。
分类:
按子查询出现的位置进行分类:
1、select后面(用的最少)
要求:子查询的结果为单行单列(标量子查询)
2、from后面
要求:子查询的结果可以为多行多列(表子查询)
3、where、having后面(用的最多)
要求:子查询的结果必须为单列(列子查询、标量子查询)
4、exists后面
要求:(相关子查询)
特点:
1、当子查询在where、having的后面时,要求必须放在条件的右侧;
2、子查询一般放在小括号中;
3、子查询的执行优先于主查询
4、标量子查询对应单行操作符,>、<、>=、<=、=、<>
5、列子查询对应多行操作符,any/some、in、all
*/#举例SELECT first_name
FROM employees
WHERE department_id IN(SELECT department_id
FROM departments
WHERE location_id =1700);-- 一、where、having后面#1、标量子查询#谁的工资比Abel高SELECT e.*FROM employees e
WHERE salary >(SELECT salary
FROM employees
WHERE last_name ='Abel');#返回job_id为141号员工相同,salary比143号员工多的员工姓名、工资、job_idSELECT e.`last_name`, e.`salary`, e.`job_id`FROM employees e
WHERE e.`job_id`=(SELECT job_id FROM employees WHERE employee_id =141)AND
e.`salary`>(SELECT salary FROM employees WHERE employee_id =143);#返回工资最少的员工信息last_name、job_id和salarySELECT last_name, job_id, salary
FROM employees
WHERE salary =(SELECTMIN(salary)FROM employees
);#查询最低工资大于50号部门最低工资的部门id和其最低工资SELECT department_id 部门编号,MIN(salary) 最低工资
FROM employees
GROUPBY department_id
HAVING 最低工资 >(SELECTMIN(salary)FROM employees
WHERE department_id =50)ORDERBY 最低工资 ASC;#2、列子查询(单列多行)/*
列子查询需要搭配多行操作符来执行
IN / NOT IN: 等于 / 不等于列表中的任意一个
ANY / SOME : 和子查询返回的某一个值进行比较
ALL:和子查询返回的所有值进行比较
*/#返回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、以及salarySELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <ANY(SELECTDISTINCT salary
FROM employees
WHERE job_id ='IT_PROG')AND job_id <>'IT_PROG';#返回其他工种中比job_id为'IT_PROG'工种所有员工工资低的员工:员工号、姓名、job_id以及salarySELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <ALL(SELECT salary
FROM employees
WHERE job_id ='IT_PROG')AND job_id <>'IT_PROG';-- 二、select后面#查询每个部门的员工个数SELECT d.`department_id`, d.`department_name`,(SELECTCOUNT(*)FROM employees e
WHERE e.`department_id`= d.`department_id`) 个数
FROM departments d;#查询员工号=102的部门名SELECT employee_id,(SELECT department_name
FROM departments d
WHERE d.department_id = e.`department_id`) 部门名
FROM employees e
WHERE e.`employee_id`=102;-- 三、from后面#将子查询的结果集当做一张表#查询每个部门的平均工资等级SELECT ag_dep.*, j.grade_level
FROM(SELECTAVG(salary) 平均工资, department_id FROM employees GROUPBY department_id
) ag_dep INNERJOIN job_grades j
ON 平均工资 BETWEEN j.`lowest_sal`AND j.`highest_sal`;-- 四、exists后面(相关子查询)#指子查询有没有结果,有没有值,返回1/0.SELECTEXISTS(SELECT employee_id FROM employees);#1SELECTEXISTS(SELECT salary FROM employees WHERE salary >30000);#0/*
语法:
exists(子查询)
最终结果只有两种情况:1 / 0
*/#查询有员工的部门名SELECTDISTINCT department_name
FROM departments d
LEFTJOIN employees e
ON d.`department_id`= e.`department_id`WHERE e.`employee_id`ISNOTNULL;
或者
SELECT department_name
FROM departments d
WHEREEXISTS(SELECT*FROM employees e
WHERE e.`department_id`= d.`department_id`);
或者
SELECT department_name
FROM departments d
WHERE d.department_id IN(SELECT department_id
FROM employees
);-- 五、子查询的练习#1、查询和Zlotkey相同部门的员工姓名和工资SELECT last_name, salary, department_id
FROM employees
WHERE department_id =(SELECT department_id
FROM employees
WHERE last_name ='Zlotkey');#2、查询工资比公司平均工资高的员工的员工号、姓名和工资SELECT employee_id, last_name, salary
FROM employees
WHERE salary >(SELECTAVG(salary)FROM employees
);#3、查询各部门中比本部门平均工资高的员工的员工号、姓名和工资SELECT employee_id, last_name, salary, e.`department_id`FROM(SELECTAVG(salary) ag, department_id
FROM employees
GROUPBY department_id
)tempJOIN employees e
ONtemp.department_id = e.`department_id`WHERE e.`salary`>temp.ag;#4、查询和姓名中包含字母u的员工在同一部门的员工号和姓名SELECT employee_id, last_name
FROM employees
WHERE department_id IN(SELECTDISTINCT department_id
FROM employees
WHERE last_name LIKE'%u%');#5、查询在部门的location_id=1700的部门工作的员工的员工号(IN或者=ANY)SELECT employee_id
FROM employees
WHERE department_id IN(SELECTDISTINCT department_id
FROM departments
WHERE location_id =1700);#6、查询管理者是King的员工姓名和工资(姓名为k_ing的管理者可能有多个,当你不确定是=还是in,那么就用in)SELECT last_name, salary
FROM employees
WHERE manager_id IN(SELECTDISTINCT employee_id
FROM employees
WHERE last_name ='K_ing');#7、查询工资最高的员工的姓名,要求first_name,last_name显示为一列,列名为姓名SELECT CONCAT(first_name, last_name)AS 姓名
FROM employees
WHERE salary =(SELECTMAX(salary)FROM employees
);