这篇“mysql子查询如何应用”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“mysql子查询如何应用”文章吧。

简介

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

分类

单行子查询：查询的结果只有1行多行子查询：查询的结果有多行相关(或关联)子查询：子查询中的参数需要用到主查询中的值不相关(或非关联)子查询

案例1

#1.查询工资比Abel高用户#方式1：需要2步SELECTsalaryFROMemployeesWHERElast_name='Abel';SELECTlast_name,salaryFROMemployeesWHEREsalary>11000;#方式2：自连接SELECTe2.last_name,e2.salaryFROMemployeese1,employeese2WHEREe2.`salary`>e1.`salary`#多表的连接条件ANDe1.last_name='Abel';#方式3：子查询SELECTlast_name,salaryFROMemployeesWHEREsalary>(SELECTsalaryFROMemployeesWHERElast_name='Abel');

称谓的规范

外查询（或主查询）内查询（或子查询）

单行比较操作符

代码案例

#查询工资大于149号员工工资的员工的信息SELECTemployee_id,last_name,salaryFROMemployeesWHEREsalary>(SELECTsalaryFROMemployeesWHEREemployee_id=149);#返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资SELECTlast_name,job_id,salaryFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=141)ANDsalary>(SELECTsalaryFROMemployeesWHEREemployee_id=143);#返回公司工资最少的员工的last_name,job_id和salarySELECTlast_name,job_id,salaryFROMemployeesWHEREsalary=(SELECTMIN(salary)FROMemployees);#查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id#方式1：SELECTemployee_id,manager_id,department_idFROMemployeesWHEREmanager_id=(SELECTmanager_idFROMemployeesWHEREemployee_id=141)ANDdepartment_id=(SELECTdepartment_idFROMemployeesWHEREemployee_id=141)ANDemployee_id<>141;#方式2：SELECTemployee_id,manager_id,department_idFROMemployeesWHERE(manager_id,department_id)=(SELECTmanager_id,department_idFROMemployeesWHEREemployee_id=141)ANDemployee_id<>141;#查询最低工资大于110号部门最低工资的部门id和其最低工资SELECTdepartment_id,MIN(salary)FROMemployeesWHEREdepartment_idISNOTNULLGROUPBYdepartment_idHAVINGMIN(salary)>(SELECTMIN(salary)FROMemployeesWHEREdepartment_id=110);#显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’SELECTemployee_id,last_name,CASEdepartment_idWHEN(SELECTdepartment_idFROMdepartmentsWHERElocation_id=1800)THEN'Canada'ELSE'USA'END"location"FROMemployees;#子查询结果为null，则最后结果为nullSELECTlast_name,job_idFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHERElast_name='Haas');

多行子查询

也称为集合比较子查询内查询返回多行使用多行比较操作符

多行比较操作符

代码案例

#IN:SELECTemployee_id,last_nameFROMemployeesWHEREsalaryIN(SELECTMIN(salary)FROMemployeesGROUPBYdepartment_id);#ANY或ALL:#返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salarySELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREjob_id<>'IT_PROG'ANDsalary<ANY(SELECTsalaryFROMemployeesWHEREjob_id='IT_PROG');#返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id以及salarySELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREjob_id<>'IT_PROG'ANDsalary<ALL(SELECTsalaryFROMemployeesWHEREjob_id='IT_PROG');#查询平均工资最低的部门id#MySQL中聚合函数是不能嵌套使用的。#方式1：SELECTdepartment_idFROMemployeesGROUPBYdepartment_idHAVINGAVG(salary)=(SELECTMIN(avg_sal)FROM(SELECTAVG(salary)avg_salFROMemployeesGROUPBYdepartment_id)t_dept_avg_sal);#方式2：SELECTdepartment_idFROMemployeesGROUPBYdepartment_idHAVINGAVG(salary)<=ALL(SELECTAVG(salary)avg_salFROMemployeesGROUPBYdepartment_id)#子查询结果为null，则最后结果为nullSELECTlast_nameFROMemployeesWHEREemployee_idNOTIN(SELECTmanager_idFROMemployees);

相关子查询

子查询中使用主查询中的列

代码案例

#查询员工中工资大于公司平均工资的员工的last_name,salary和其department_idSELECTlast_name,salary,department_idFROMemployeesWHEREsalary>(SELECTAVG(salary)FROMemployees);#查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id#方式1：使用相关子查询SELECTlast_name,salary,department_idFROMemployeese1WHEREsalary>(SELECTAVG(salary)FROMemployeese2WHEREdepartment_id=e1.`department_id`);#方式2：在FROM中声明子查询SELECTe.last_name,e.salary,e.department_idFROMemployeese,(SELECTdepartment_id,AVG(salary)avg_salFROMemployeesGROUPBYdepartment_id)t_dept_avg_sal#取别名WHEREe.department_id=t_dept_avg_sal.department_idANDe.salary>t_dept_avg_sal.avg_sal#查询员工的id,salary,按照department_name排序SELECTemployee_id,salaryFROMemployeeseORDERBY(SELECTdepartment_nameFROMdepartmentsdWHEREe.`department_id`=d.`department_id`)ASC;

在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询

SELECT....,....,....(存在聚合函数)FROM...(LEFT/RIGHT)JOIN....ON多表的连接条件(LEFT/RIGHT)JOIN...ON....WHERE不包含聚合函数的过滤条件GROUPBY...,....HAVING包含聚合函数的过滤条件ORDERBY....,...(ASC/DESC)LIMIT...,....

EXISTS 与 NOT EXISTS关键字

1、关联子查询通常也会和EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行2、如果在子查询中不存在满足条件的行：条件返回FALSE继续在子查询中查找3、如果在子查询中存在满足条件的行：不在子查询中继续查找条件返回TRUE4、NOTEXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE

代码案例

#若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_idSELECTemployee_id,last_name,job_idFROMemployeeseWHERE2<=(SELECTCOUNT(*)FROMjob_historyjWHEREe.`employee_id`=j.`employee_id`)#EXISTS与NOTEXISTS关键字#查询公司管理者的employee_id，last_name，job_id，department_id信息#方式1：自连接SELECTDISTINCTmgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_idFROMemployeesempJOINemployeesmgrONemp.manager_id=mgr.employee_id;#方式2：子查询SELECTemployee_id,last_name,job_id,department_idFROMemployeesWHEREemployee_idIN(SELECTDISTINCTmanager_idFROMemployees);#方式3：使用EXISTSSELECTemployee_id,last_name,job_id,department_idFROMemployeese1WHEREEXISTS(SELECT*FROMemployeese2WHEREe1.`employee_id`=e2.`manager_id`);#查询departments表中，不存在于employees表中的部门的department_id和department_name#方式1：SELECTd.department_id,d.department_nameFROMemployeeseRIGHTJOINdepartmentsdONe.`department_id`=d.`department_id`WHEREe.`department_id`ISNULL;#方式2：SELECTdepartment_id,department_nameFROMdepartmentsdWHERENOTEXISTS(SELECT*FROMemployeeseWHEREd.`department_id`=e.`department_id`);

以上就是关于“mysql子查询如何应用”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注亿速云行业资讯频道。