文章目录

子查询where或having后面的子查询select后面的子查询from后面的子查询exists后面的子查询

子查询

定义:出现在其他语句中内部的select语句，称为子查询或内查询

外面的语句可以是insert、update、delete、select等；一般select作为外面语句较多。

外面的语句如果是select语句，则此语句称为外查询或主查询

分类:

按照子查询出现的位置：

select后面：仅仅支持标量子查询

from后面：支持表子查询

where或having后面：标量子查询、列子查询、行子查询、表子查询

exists后面：标量子查询、列子查询、行子查询、表子查询

按照结果集行列数分类：

标量子查询（单行子查询）：结果集只有一行一列

列子查询（多行子查询）：结果集只有一列多行

行子查询：结果一行多列或者多行多列

表子查询（嵌套子查询）：一般为多行多列（一行一列、一列多行、一行多列都可以）

where或having后面的子查询

支持查询：标量子查询、列子查询、行子查询

特点：①子查询放在小括号内

②子查询一般放在条件的右侧

③标量子查询一般搭配着单行操作符使用（单行操作符：>、<、>=、<=、<>）

④列子查询一般搭配着多行操作符使用(多行操作符：in、any、some、all)

⑤子查询的执行都是优先于主查询的执行

1.标量子查询:一行一列

标量子查询一般搭配着单行操作符使用（单行操作符：>、<、>=、<=、<>）

/**案例1：查询谁的工资比Abel工资高 分析：①查询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_id;②查询143号员工的salary；③在①和②的基础上查询姓名、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 ); /**返回公司工资最少的员工的last_name,job_id和salary 分析：①查找公司工资最少的salary；②在①的基础上查询last_name,job_id和salary **/ SELECT last_name,job_id,salary FROM employees WHERE salary = ( SELECT MIN(salary)FROM employees ); /**查询最低工资大于50号部门的最低工资的部门id和其最低工资 分析：①查询50号部门的最低工资；②查询所有部门的最低工资；③在②的基础上筛选出salary>①的部门id和其最低工资**/ SELECT department_id, MIN(salary) FROM emplooyees GROUP BY department_id HAVING MIN(salary) > ( SELECT salary FROM employees WHERE department_id = 50 );

2.列子查询（多行子查询）：一列多行

列子查询一般搭配着多行操作符使用(多行操作符：in、any、some、all)

操作符含义in、not in（不）等于列表中的任意一个any、some和子查询返回的某一个值比较all和子查询返回的所有值比较 /**案例1：返回location_id是1400或1700的部门中的所有员工的姓名 分析：①：查询location_id是1400或1700的部门编号；②：有①的部门编号查出所有员工的姓名 **/ SELECT last_name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location_id IN(1400,1700) ); /**案例2：方法一：返回其他工种中比job_id为'IT_PROG'部门任意工资低的员工的：工号、姓名、job_id以及salary 分析：①查询job_id为'IT_PROG'的部门中的任意工资 **/ SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ANY( SELECT salary FROM employees WHERE job_id = 'IT_PROG' )AND job_id <> 'IT_PROG'; /**案例2的方法二：**/ SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ( SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG' )AND job_id <> 'IT_PROG'; /**案例3：方法一：返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的：工号、姓名、job_id以及salary 分析：①查询job_id为'IT_PROG'的部门中的任意工资 **/ SELECT 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';

3.行子查询：一行多列或多行多列

/**案例1：方法一：查询员工编号最小并且工资最高的员工信息**/ SELECT * FROM employees WHERE employee_ie = ( SELECT MIN(employee_id) FROM employees )AND salary = ( SELECT MAX(salary) FROM employees ); /**案例1：方法二：**/ SELECT * FROM employees WHERE （employee_id,salary) = ( SELECT MIN(employee_id),MAX(salary) FROM employees );

select后面的子查询

仅仅支持标量子查询(单行单列)

/**案例1：查询每个部门的员工个数 分析：涉及到表departments和表employees:count(*) --> department_id **/ SELECT d.*, ( SELECT COUNT(*) FROM employees e WHERE d.'department_id' = e.'department_id' )员工个数 FROM departments d; /**查询员工号=102的部门名 分析：涉及到表departments和表employees:employee_id --> department_id --> department_name **/ SELECT department_name FROM departments d WHERE d.department_id = ( SELECT e.department_id FROM employees e WHERE employee_id = 102 );

from后面的子查询

支持表子查询

/**案例1：查询每个部门的平均工资的工资等级 分析：①查询每个部门的平均工资（查询结果为一个结果集）； ②将①的结果集起别名作为一张新表（avg），将表avg和表grade_level进行连接查询 **/ SELECT avg.*,grade_level FROM ( SELECT AVG(salary) ag FROM employees GROUP BY department_id ) avg INNER JOIN job_level ON avg.ag BETWEEN lowest_sal AND highest_sal;

exists后面的子查询

exists后面的子查询又称为相关子查询

语法：

exists(完整的查询语句);

结果为：0或1

/**案例1：查询有员工的部门名**/ SELECT department_name FROM departments d WHERE EXISTS ( SELECT * FROM employees e WHERE d.department_id = e.department_id ); /**方法二：**/ SELECT department_name FROM departments d WHERE d.department_id IN ( SELECT e.department_id FROM employees e ) /**案例2：查询没有女朋友的男神信息**/ SELECT * FROM boys b WHERE b.id NOT IN( SELECT boyfriend_id FROM beauty ); /**案例2方法二：查询没有女朋友的男神信息**/ SELECT * FROM boys b WHERE NOT EXISTS( SELECT * FROM beauty bea WHERE b.id = bea.boyfriend_id );