mysql子查询写法

1、子查询概述

子查询简介

子查询指一个查询语句嵌套在另一个查询语句内部的查询，内部的查询是外部查询的条件，这个特性从MySQL4.1开始引入。

SQL中子查询的使用大大增强了ｓｅｌｅｃｔ查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

子查询基本使用

语法：

ｓｅｌｅｃｔ -- 主查询select_listFROMtableWHEREexpr operator > (ｓｅｌｅｃｔ -- 子查询select_listFROMtable);复制代码

子查询(内查询)在主查询之前执行完成。

子查询的结果被主查询(外查询)使用。

注意事项：

子查询要包含在括号内将子查询放在比较条件的右侧单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

按子查询的结果返回一条还是多条记录，将子查询分为单行子查询和多行子查询。

按子查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。

2、子查询的使用2.1、单行子查询

单行比较操作符

=等于，>大于，>=大于等于， (ｓｅｌｅｃｔ salary from employees where employee_id = 149)-- 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job _id和工资ｓｅｌｅｃｔ last_name,job_id,salaryfrom employeeswhere job_id = (ｓｅｌｅｃｔ job_id from employees where employee_id = 141)and salary > (ｓｅｌｅｃｔ salary from employees where employee_id = 143);-- 返回公司工资最少的员工的last_name,job_id和salaryselect last_name,job_id,salaryfrom employeeswhere salary = (ｓｅｌｅｃｔ min(salary) from employees);-- 查询与147号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_idselect employee_id,manager_id,department_idfrom employeeswhere manager_id = (ｓｅｌｅｃｔ manager_id from employees where employee_id = 147)and department_id = (ｓｅｌｅｃｔ department_id from employees where employee_id = 147)and employee_id 147;-- 查询最低工资大于50号部门最低工资的部门id和其最低工资ｓｅｌｅｃｔ department_id,min(salary) min_salaryfrom employeeswhere department_id is not nullgroup by department_idhaving min_salary > (ｓｅｌｅｃｔ min(salary) from employees where department_id = 50);复制代码2.2、多行子查询

多行子查询比较操作符

使用实例

-- 查找属于位置ID为1700的所有员工SELECTemployee_id, first_name, last_nameFROMemployeesWHEREdepartment_id IN (SELECTdepartment_idFROMdepartmentsWHERElocation_id = 1700)ORDER BY first_name , last_name;-- 返回其它job id中比job id为‘IT_PROG’部门任一工资低的员工的员工号姓名、job id以及salaryselect last_name,job_id,salaryfrom employeeswhere salary < any (ｓｅｌｅｃｔ salary from employees where job_id = ‘IT_PROG‘)and job_id ‘IT_PROG‘;-- 返回其它job id中比job id为‘IT_PROG’部门所有工资低的员工的员工号姓名、job id以及salaryselect last_name,job_id,salaryfrom employeeswhere salary < all (ｓｅｌｅｃｔ salary from employees where job_id = ‘IT_PROG‘)and job_id ‘IT_PROG‘;-- 查询平均工资最低的部门ID-- 方式一：ｓｅｌｅｃｔ department_id,avg(salary) from employees group by department_id order by avg(salary) limit 1;-- 方式二：ｓｅｌｅｃｔ department_idfrom employeesgroup by department_idhaving avg(salary) = (ｓｅｌｅｃｔ min(avg_sal)from ( ｓｅｌｅｃｔ avg(salary) avg_sal from employees group by department_id ) t_dept_avg_sal)-- 方式三：ｓｅｌｅｃｔ department_idfrom employeesgroup by department_idhaving avg(salary) (ｓｅｌｅｃｔ avg(salary) from employees e2 where e1.department_id = e2.department_id);-- 方式二ｓｅｌｅｃｔ last_name,salary,e.department_idfrom employees e ,(ｓｅｌｅｃｔ department_id,avg(salary) avg_sal from employees group by department_id) t_dept_avg_salwhere e.department_id = t_dept_avg_sal.department_idand e.salary > t_dept_avg_sal.avg_sal;-- 查询员工的id,salary,按照department_name排序ｓｅｌｅｃｔ employee_id,salaryfrom employees eorder by (ｓｅｌｅｃｔ department_name from departments d where e.department_id = d.department_id ); 复制代码

EXISTS与NOT EXISTS

EXISTS运算符用于指定子查询中是否存在满足条件的行，

如果子查询包含任何行，则EXISTS运算符返回true。 否则它返回false。

EXISTS运算符在找到行后立即终止查询处理，因此，可以利用EXISTS运算符的此功能来提高查询性能

NOT EXISTS 表示如果不存在某种条件，则返回true，否则返回true。

使用实例：

-- 查找管理者的id，姓名，工作和部门idselect employee_id,last_name,job_id,department_idfrom employees e1where exists(ｓｅｌｅｃｔ * from employees e2 where e1.employee_id = e2.manager_id)-- 查询departments表中，不存在于employees表中的部门的department_id和department_name-- 方式一ｓｅｌｅｃｔ department_id,department_namefrom departments dwhere not exists(ｓｅｌｅｃｔ * from employees e where d.department_id = e.department_id);-- 方式二ｓｅｌｅｃｔ d.department_id,department_namefrom departments d left join employees e on d.department_id = e.department_idwhere e.department_id is null;复制代码子查询练习题#1.查询和Zlotkey相同部门的员工姓名和工资ｓｅｌｅｃｔ last_name,salaryfrom employeeswhere department_id in (ｓｅｌｅｃｔ department_id from employees where last_name = ‘Zlotkey‘)and last_name ‘Zlotkey‘;#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。ｓｅｌｅｃｔ employee_id,last_name,salaryfrom employeeswhere salary > (ｓｅｌｅｃｔ avg(salary) from employees);#3.选择工资大于所有JOB_ID = ‘SA_MAN‘的员工的工资的员工的last_name，job_id, salaryselect last_name,job_id,salaryfrom employeeswhere salary > (ｓｅｌｅｃｔ max(salary) from employees where job_id = ‘SA_MAN‘);#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名ｓｅｌｅｃｔ employee_id,last_namefrom employeeswhere department_id in (ｓｅｌｅｃｔ department_id from employees where last_name like ‘%u%‘);#5.查询在部门的location_id为1700的部门工作的员工的员工号ｓｅｌｅｃｔ employee_idfrom employees ewhere department_id in (ｓｅｌｅｃｔ department_id from departments where location_id = 1700);#6.查询管理者是King的员工姓名和工资ｓｅｌｅｃｔ last_name,salaryfrom employeeswheremanager_id in (ｓｅｌｅｃｔ employee_id from employees where last_name = ‘K_ing‘);#7.查询工资最低的员工信息: last_name,salaryselect last_name,salaryfrom employeesorder by salarylimit 1;ｓｅｌｅｃｔ last_name,salaryfrom employeeswhere salary = (ｓｅｌｅｃｔ min(salary) from employees);#8.查询平均工资最低的部门信息-- 方式一：ｓｅｌｅｃｔ * from departmentswhere department_id = (ｓｅｌｅｃｔ department_idfrom employeesgroup by department_idorder by avg(salary)limit 1);-- 方式二：ｓｅｌｅｃｔ * from departmentswhere department_id = (ｓｅｌｅｃｔ department_idfrom employeesgroup by department_idhaving avg(salary) = (ｓｅｌｅｃｔ min(avg_sal) from (ｓｅｌｅｃｔ avg(salary) avg_sal from employees group by department_id) t_sal));-- 方式三：ｓｅｌｅｃｔ * from departmentswhere department_id = (ｓｅｌｅｃｔ department_idfrom employeesgroup by department_idhaving avg(salary) (ｓｅｌｅｃｔ avg(salary) from employees);#12.查询出公司中所有manager 的详细信息-- 方式一ｓｅｌｅｃｔ employee_id,last_name,salary,department_idfrom employees e1where exists(ｓｅｌｅｃｔ * from employees e2 where e1.employee_id = e2.manager_id);-- 方式二ｓｅｌｅｃｔ distinct man.employee_id,man.last_name,man.salary,man.department_idfrom employees empjoin employees manon emp.manager_id = man.employee_id;-- 方式三 子查询ｓｅｌｅｃｔ employee_id,last_name,salary,department_idfrom employeeswhere employee_id in (ｓｅｌｅｃｔ distinct manager_id from employees);#13.查各个部门最高的工资中 最低的那个部门的最低工资多少-- 方式一：ｓｅｌｅｃｔ department_id,min(salary) from employeeswhere department_id = (ｓｅｌｅｃｔ department_id from employeesgroup by department_idhaving max(salary) = (ｓｅｌｅｃｔ min(max_sal)from employees e, ( ｓｅｌｅｃｔ max(salary) max_salfrom employeeswhere department_id is not nullgroup by department_id ) t_min_sal));-- 方式二：ｓｅｌｅｃｔ department_id,min(salary) from employeeswhere department_id = (ｓｅｌｅｃｔ department_id from employeesgroup by department_idhaving max(salary) = all (ｓｅｌｅｃｔ avg(salary)from employeeswhere department_id is not nullgroup by department_id)) );-- 方式三：ｓｅｌｅｃｔ last_name,department_id,email,salary from employeeswhere employee_id = (ｓｅｌｅｃｔ manager_id from departmentswhere department_id = (ｓｅｌｅｃｔ department_idfrom employeesgroup by department_idhaving avg(salary) = (ｓｅｌｅｃｔ max(avg_sal) from (ｓｅｌｅｃｔ avg(salary) avg_salfrom employeeswhere department_id is not nullgroup by department_id) t_avg_sql)) );#15．查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号-- 方式一：ｓｅｌｅｃｔ department_idfrom departmentswhere department_id not in (ｓｅｌｅｃｔ distinct department_idfrom employees e1where job_id = ‘ST_CLERK‘);-- 方式二：ｓｅｌｅｃｔ department_idfrom departments dwherenot exists (ｓｅｌｅｃｔ *from employees e1where d.department_id = e1.department_id and job_id = ‘ST_CLERK‘);#16．选择所有没有管理者的员工的last_nameselect last_namefrom employees e1where not exists(ｓｅｌｅｃｔ * from employees e2 where e1.manager_id = e2.employee_id);#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 ‘De Haan ‘-- 方式一：自连接ｓｅｌｅｃｔ e1.employee_id,e1.last_name,e1.hiredate,e1.salaryfrom employees e1,employees e2where e1.manager_id = e2.employee_id and e2.last_name = ‘De Haan‘;-- 方式二：子查询ｓｅｌｅｃｔ employee_id,last_name,hiredate,salaryfrom employeeswhere manager_id in (ｓｅｌｅｃｔ employee_id from employees where last_name = ‘De Haan‘);#18.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资（相关子查询)-- 方式一：ｓｅｌｅｃｔ employee_id,salary,last_namefrom employees e1where salary > (ｓｅｌｅｃｔ avg(salary) from employees e2 where e1.department_id = e2.department_id);-- 方式二：ｓｅｌｅｃｔ employee_id,salary,last_namefrom employees e,(ｓｅｌｅｃｔ department_id,avg(salary) avg_sal from employees group by department_id) t_avg_salwhere e.department_id = t_avg_sal.department_id and e.salary > t_avg_sal.avg_sal;#19.查询每个部门下的部门人数大于5的部门名称（相关子查询)Iselect department_namefrom departments dwhere 5 < (ｓｅｌｅｃｔ count(*) from employees e where e.department_id = d.department_id);#20.查询每个国家下的部门个数大于2的国家编号(相关子查询)ｓｅｌｅｃｔ country_idfrom locations lwhere 2 < (ｓｅｌｅｃｔ count(*) from departments d where l.location_id = d.location_id);