vlambda博客
学习文章列表

#进阶7：子查询/*含义：出现在其他语句中的select语句，称为子查询或内查询外部的查询语句，称为主查询或外查询
分类：按子查询出现的位置： select 后面： 仅仅标量子查询 from后面 支持表子查询 where或having后面：※ 支持标量子查询√ 列子查询√  行子查询（用的较少）
 exists后面（相关子查询） 表子查询按结果集的行列数不同： 标量子查询（结果集只有一行一列） 即查出的结果作为标准， 列子查询（结果集只有一列多行） 行子查询（结果集有一（多）行多列） 表子查询（结果集一般为多行多列） */
#一.where 或 having 后面/*1.标量子查询（单行子查询）2.列子查询（多行子查询）3.行子查询（多列多行）
特点：子查询都放在小括号内子查询一般放在条件右侧标量子查询，一般搭配单行操作符使用> < >= <= = <>列子查询，一般搭配着多行操作符使用IN、any/some、all子查询的执行优先于主查询的条件，*/#1.标量子查询#案例1：谁的工资比abel高？SELECT last_name,salaryFROM employeesWHERE salary>( SELECT salary FROM employees WHERE last_name = 'abel');
#案例2：返回job_id与141员工相同，salary比143员工多的员工姓名，job_id和工资SELECT last_name,job_id,salaryFROM employeesWHERE job_id = ( SELECT job_id FROM employees WHERE employee_id = 141)AND salary > ( SELECT salary FROM employees WHERE employee_id = 143)ORDER BY salary DESC;
#案例3：返回公司工资最少的员工的last_name,job_id和salarySELECT last_name,job_id,salaryFROM employeesWHERE salary = ( SELECT MIN(salary) FROM employees);#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资SELECT department_id,MIN(salary)FROM employeesGROUP BY department_idHAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 50);
#非法使用标量子查询SELECT department_id,MIN(salary)FROM employeesGROUP BY department_idHAVING MIN(salary) > ( SELECT salary FROM employees WHERE department_id = 50); 
#2.列子查询（多行子查询）#使用多行比较操作符/*IN/NOT IN 等于列表中的任意一个ANY|SOME 和子查询返回的某一个值比较，跟其中一个比就行ALL 和子查询返回的所有值比较，跟所有比*/#案例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)); SELECT `last_name`FROM `employees`WHERE `department_id` = ANY ( SELECT `department_id` FROM `departments` WHERE `location_id` IN (1400,1700));#使用内连接查询SELECT last_nameFROM employees eINNER JOIN departments dWHERE e.`department_id` = d.`department_id`AND d.`location_id` IN (1400,1700);
#案例2：返回其他工种中比job_id为‘IT_PROG’部门任一工资低的员工的：工号、姓名、job_id以及salarySELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary < ( SELECT MAX(salary) FROM employees WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG' ;

SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary < ANY ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG' ;
#案例3：返回其他工种中比job_id为‘IT_PROG’部门所有工资都低的员工的：工号、姓名、job_id以及salarySELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary < ALL ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG' ;
SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary < ( SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG' ;
#3、行子查询（结果一行多列、多行多列（较少））#案例：查询员工编号最小并且工资最高的员工信息#用法很像临时组成新的字段SELECT *FROM employeesWHERE (employee_id,salary)=( SELECT MIN(employee_id),MAX(salary) FROM employees);

SELECT *FROM employeesWHERE employee_id = ( SELECT MIN(employee_id) FROM employees)AND salary = ( SELECT 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 INNER JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = 102) 部门名;
#三、from后面#将子查询充当一张表，要求必须起别名#案例：查询每个部门的平均工资的工资等级
SELECT ag_dep.*,g.`grade_level`FROM ( SELECT AVG(salary) ag,department_id FROM employees  GROUP BY department_id) ag_depINNER JOIN `job_grades` gON ag_dep.ag BETWEEN g.lowest_sal AND g.`highest_sal`;