MySQL子查询

子查询重点：where和having后面

术语：

标量子查询（一行一列，也就是一个值）列子查询（一列多行）行子查询（一行多列）

特点：

子查询一般放在小括号内子查询一般放在条件的右侧标量子查询一般搭配着单行操作符使用 单行操作符：> < >= <= = <>列子查询，一般搭配着多行操作符使用 多行操作符：in,any/some,all

标量子查询 案例1：谁的工资比Abel高

SELECT * FROM employees WHERE salary>( SELECT salary FROM employees e WHERE e.last_name='Abel' );

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

SELECT last_name,job_id,salary FROM employees WHERE salary>( SELECT salary FROM employees WHERE employee_id=143 ) AND job_id=( SELECT job_id FROM employees WHERE employee_id=141 );

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

SELECT last_name,job_id,salary from employees WHERE salary=( SELECT MIN(salary) FROM employees );

案例4：查询最低工资大于50号部门最低工资的部门和其最低工资

SELECT department_id,MIN(salary) 最低工资 from employees GROUP BY department_id HAVING 最低工资>( SELECT MIN(salary) FROM employees WHERE department_id=50 );

列子查询 案例1：返回location_id是1400或者1700的部门中的所有员工姓名

SELECT last_name FROM employees WHERE department_id IN ( SELECT DISTINCT department_id from departments WHERE location_id IN (1400,1700) );

IN 可以替换为：=ANY

SELECT last_name FROM employees WHERE department_id =ANY ( SELECT DISTINCT department_id from departments WHERE location_id IN (1400,1700) );

ＮOT IN可以替换成<>ALL

案例2：返回其他工种中比job_id为IT_PROG工种任一工资低的员工的员工号、姓名、job_id以及salary

SELECT employee_id,job_id,salary FROM employees WHERE salary<ANY( SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG' ) AND job_id<>'IT_PROG';

比任一工资低，可以替换为比最高的工资低

SELECT employee_id,job_id,salary FROM employees WHERE salary<( SELECT max(salary) FROM employees WHERE job_id='IT_PROG' ) AND job_id<>'IT_PROG';

案例3：返回其他工种中比job_id为IT_PROG工种所有工资都低的员工的员工号、姓名、job_id以及salary

SELECT employee_id,job_id,salary FROM employees WHERE salary<ALL( SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG' ) AND job_id<>'IT_PROG';

同样，比所有的工资都低，可以替换成比最低工资的都低

SELECT employee_id,job_id,salary FROM employees WHERE salary<( SELECT min(salary) FROM employees WHERE job_id='IT_PROG' ) AND job_id<>'IT_PROG';

行子查询（用的较少） 案例：查询员工编号最小并且工资最高的员工信息 一般的思想：

SELECT * FROM employees WHERE employee_id=( SELECT MIN(employee_id) from employees ) AND salary=( SELECT MAX(salary) FROM employees );

行子查询思想：

SELECT * FROM employees WHERE (employee_id,salary)=( SELECT MIN(employee_id),MAX(salary) from employees );

位于select后面的子查询（仅仅支持标量子查询）

案例1：查询每个部门的员工个个数

SELECT d.*,( SELECT COUNT(*) FROM employees e WHERE e.department_id=d.department_id ) from departments d;

案例2：查询员工号为102的部门名

SELECT( SELECT department_name FROM departments d LEFT JOIN employees e ON d.department_id=e.department_id WHERE e.employee_id=102 ) 部门名;

位于from后面的子查询

相当于把子查询的结果集充当于一个表来使用,要求必须取别名

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

SELECT ag_dep.*,g.grade_level FROM ( SELECT department_id,AVG(salary) ag FROM employees GROUP BY department_id ) ag_dep INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

位于exists后面的子查询(相关子查询) exists(完整的查询语句) 结果为1表示存在；0表示不存在 类似于true和false

案例1：查询有员工的部门名

SELECT department_name FROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE e.department_id=d.department_id );

用IN来代替

SELECT department_name FROM departments d WHERE d.department_id IN( SELECT department_id FROM employees e );

案例2：查询没有女朋友的男神信息

SELECT bo.* FROM boys bo WHERE bo.id NOT IN( SELECT boyfriend_id from beauty );

用exists来代替

SELECT bo.* FROM boys bo WHERE NOT EXISTS( SELECT * FROM beauty b WHERE b.boyfriend_id=bo.id );