2020-08-10

#子查询
/概念： 嵌套在另一个查询中的查询语句称为子查询，外部的查询称为主查询，类似于java中的内部类语法 select 字段1，字段2 from 表1 where Id in( select 字段1 from 表2)特点或者说注意事项： 1、子查询放在小括号内 2、放在条件右侧 3、子查询优先于主查询执行分类： 单行子查询：子查询的结果只有一个值，使用单行操作符（> < >= <= = <>） 多行子查询:使用多行操作符（any、all、in、not in）/#1.单行子查询#案例1：谁的工资比Abel高 #①查询Abel的工资 SELECT salary FROM employees WHERE last_name = ‘Abel’ #②查询员工的信息满足工资>①的结果 SELECT * FROM employees WHERE salary>( SELECT salary FROM employees WHERE last_name = ‘Abel’ );#案例2：题目：返回job_id与141号员工相同，salary比143号员工多的员工 的姓名，job_id 和工资 #①查询141的job_id SELECT job_id FROM employees WHERE employee_id=141 #②查询143的salary SELECT salary FROM employees WHERE employee_id=143 #③查询 姓名，job_id 和工资，满足job_id=①并且salary>② 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 ); #案例3：返回公司工资最少的员工的last_name,job_id和salary #①查询最低工资 SELECT MIN(salary) FROM employees; #②查询员工的last_name,job_id和salary满足 salary=① SELECT last_name,job_id,salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees ) #案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资 #①查询50号部门的最低工资 SELECT MIN(salary) FROM employees WHERE department_id=50 #②查询每个部门的最低工资 SELECT department_id,MIN(salary) FROM employees GROUP BY department_id #③筛选最低工资>① SELECT department_id,MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary)>( SELECT MIN(salary) FROM employees WHERE department_id=50 );