mysql-子查询的学习
子查询 由一个具体的需求,引入子查询 谁的工资比Abel 的高 SELECT * from employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' ) -- 自连接 SELECT e2.* FROM employees e1, employees e2 WHERE e2.salary > e1.salary AND e1.last_name = "abel" /* - 子查询(内查询)在主查询之前一次执行完成。 - 子查询的结果被主查询(外查询)使用 。 - 注意事项 - 子查询要包含在括号内 - 将子查询放在比较条件的右侧 - 单行操作符对应单行子查询,多行操作符对应多行子查询 */ 1 单行操作符: = != > >= < <= #题目:查询工资大于149号员工工资的员工的信息 SELECT * FROM employees WHERE salary > ( SELECT salary FROM employees WHERE employee_id=149 ) #题目:返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资 SELECT 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) #题目:返回公司工资最少的员工的last_name,job_id和salary SELECT last_name,job_id,salary FROM employees WHERE salary = (SELECT min(salary) FROM employees) #题目:查询与141号员工的manager_id和department_id相同的其他员工 SELECT * FROM employees WHERE manager_id = (SELECT manager_id FROM employees WHERE employee_id=141) and department_id = (SELECT department_id FROM employees where employee_id=141) AND employee_id <> 141; #题目:查询最低工资大于110号部门最低工资的部门id和其最低工资 SELECT department_id , min(salary) FROM employees GROUP BY department_id HAVING min(salary)>( SELECT min(salary) FROM employees GROUP BY department_id HAVING department_id = 110) #题目:显式员工的employee_id,last_name和location。 #其中,若员工department_id与location_id为1800的department_id相同, #则location为’Canada’,其余则为’USA’。 SELECT employee_id,last_name, case department_id WHEN (SELECT department_id FROM departments WHERE location_id=1800) THEN 'canada' else 'USA' end 'location' FROM employees 2. 多行子查询 多行子查询的操作符;IN ANY ALL SOME(同any) 也称为集合比较子查询 内查询返回多行 使用多行比较操作符 in : 等于列表中的任意一个 ANY : 需要和单行比较操作符一起使用,和子查询返回的某一个值比较 ALL : 需要和单行比较操作符一起使用,和子查询返回的所有值比较 SOME: 实际是any的别名,作用相同,一般常使用any #题目:查询平均工资最低的部门id SELECT department_id FROM employees GROUP BY department_id HAVING avg(salary) = (SELECT min(avg_sal) FROM (SELECT avg(salary) avg_sal FROM employees GROUP BY department_id) de_min_sal ) SELECT department_id FROM employees GROUP BY department_id HAVING avg(salary)<= ALL( SELECT avg(salary) FROM employees GROUP BY department_id) #6. 相关子查询 #回顾:查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id SELECT last_name, salary, department_id FROM employees WHERE salary > (SELECT avg(salary) FROM employees ) #题目:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id SELECT e.last_name, e.salary,e.department_id from employees e, (SELECT department_id,avg(salary) dep_avg_sal FROM employees GROUP BY department_id) d WHERE e.department_id=d.department_id and salary > d.dep_avg_sal ##查询员工的ID,salary ,按照department_name 排序 SELECT employee_id,salary FROM employees ORDER BY ( SELECT department_name FROM departments WHERE employees.department_id=departments.department_id ) EXISTS 和not EXISTS 关键字 SELECT distinct e2.employee_id,e2.last_name,e2.job_id ,e2.department_id FROM employees e1,employees e2 WHERE e1.manager_id=e2.employee_id #1.查询和Zlotkey相同部门的员工姓名和工资 SELECT last_name, salary 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 > (SELECT avg(salary) FROM employees); #3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary SELECT last_name, job_id,salary FROM employees WHERE salary > ALL (SELECT salary from employees where job_id = 'SA_MAN' ); #4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 #5.查询在部门的location_id为1700的部门工作的员工的员工号 SELECT employee_id FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location_id=1700) #6.查询管理者是King的员工姓名和工资 SELECT e2.last_name, e2.salary FROM employees e1,employees e2 where e1.employee_id=e2.manager_id AND e1.last_name="King" #7.查询工资最低的员工信息: last_name, salary SELECT last_name, salary FROM employees WHERE salary = (SELECT min(salary) FROM employees) #8.查询平均工资最低的部门信息 SELECT * FROM departments WHERE department_id = ( SELECT department_id from (SELECT avg(salary) avg_sal,department_id FROM employees GROUP BY department_id order by avg_sal ASC limit 1) dep_min_sal) #9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询) SELECT d.*,avg_sal from departments d, (SELECT avg(salary) avg_sal,department_id FROM employees GROUP BY department_id ORDER BY avg_sal ASC limit 1) dep_avg_sal WHERE d.`department_id` = dep_avg_sal.`department_id` #10.查询平均工资最高的 job 信息 #11.查询平均工资高于公司平均工资的部门有哪些? SELECT avg(salary) avg_sal,department_id FROM employees GROUP BY department_id HAVING avg_sal > ( SELECT avg(salary) from employees) AND department_id is not NULL #12.查询出公司中所有 manager 的详细信息 SELECT DISTINCT e1.* FROM employees e1,employees e2 WHERE e1.employee_id=e2.manager_id #13.各个部门中 最高工资中最低的那个部门的 最低工资是多少? SELECT MAX(salary),department_id FROM employees GROUP BY department_id. ???? #14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary SELECT DISTINCT e1.last_name,e1.department_id,e1.email,e1.salary FROM employees e1, employees e2 WHERE e1.employee_id=e2.manager_id and e2.department_id = ( SELECT department_id from (SELECT avg(salary) avg_sal, department_id FROM employees GROUP BY department_id ORDER BY avg_sal DESC limit 1) dep_max_sal) #15. 查询部门的部门号,其中不包括job_id是"ST_CLERK"的部门号 #16. 选择所有没有管理者的员工的last_name #17.查询员工号、姓名、雇用时间、工资,其中员工的管理者为 'De Haan' SELECT last_name, employee_id,hire_date,salary FROM employees WHERE manager_id = ( SELECT employee_id FROM employees WHERE last_name = 'De Haan') #18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(相关子查询) ( SELECT avg(salary),department_id FROM employees GROUP BY department_id ) #19.查询每个部门下的部门人数大于 5 的部门名称(相关子查询) SELECT department_name FROM departments WHERE department_id = ANY( SELECT department_id FROM ( SELECT department_id,count(*) num FROM employees GROUP BY department_id HAVING num > 5) dep_5 ) #20.查询每个国家下的部门个数大于 2 的国家编号(相关子查询)
每天进步一点点~~