Mysql(7)子查询

提示文章写完后目录可以自动生成如何生成可参考右边的帮助文档文章目录子查询select中嵌套子查询select中嵌套子查询where或having中嵌套子查询exists型子查询from中嵌套子查询update中嵌套子查询delete中嵌套子查询使用子查询复制表结构和数据通用表达式一、整体总结1. 子查询Subquery2. CTECommon Table Expressions 通用表达式3. 递归 CTERECURSIVE CTE二、各类子查询核心用法总结1. SELECT 中嵌套子查询2. WHERE / HAVING 中嵌套子查询3. FROM 中嵌套子查询派生表4. UPDATE / DELETE 中嵌套子查询三、高频易错点汇总重点子查询select中嵌套子查询子查询嵌套在另一个SQL语句中的查询。select语句可以嵌套在另一个select、update、delete、insert、create等语句中。select中嵌套子查询--在t_employee表中查询每个人薪资和公司平均薪资的差值--并显示员工薪资和公司平均薪资相差5000元以上的记录 select enameas姓名,salaryas薪资,round((select avg(salary)fromt_employee),2)as全公司平均薪资,round(salary-(select avg(salary)fromt_employee),2)as差值fromt_employee whereabs(round(salary-(select avg(salary)fromt_employee),2))5000;--在t_employee表中查询每个部门平均薪资和公司平均薪资的差值 select did,avg(salary),avg(salary)-(select avg(salary)fromt_employee)fromt_employee group by did;where或having中嵌套子查询当子查询结果作为外层另一个SQL的过滤条件通常把子查询嵌入到where或having中。根据子查询结果的情况分为如下三种情况当子查询的结果是单列单个值那么可以直接使用比较运算符如“”、“”、“”、“”、“”、“!”等与子查询结果进行比较当子查询的结果是单列多个值那么可以使用比较运算符in或not in进行比较当子查询的结果是单列多个值还可以使用比较运算符, 如“”、“”、“”、“”、“”、“!”等搭配any、all等关键字与查询结果进行比较子查询结果一列一行--在t_employee表中查询比全公司平均薪资高的男员工姓名和薪资 select ename,salaryfromt_employee where salary(select avg(salary)fromt_employee)andgender男;子查询一列多行--在t_employee表中查询和 白露谢吉娜 同一部门的员工姓名和电话 select ename,tel,didfromt_employee where didin(select didfromt_employee where ename白露orename谢吉娜);--在t_employee表中查询薪资比 白露李诗雨黄冰茹 三个人的薪资都要高的员工姓名和薪资 select ename,salaryfromt_employee where salaryall(select salaryfromt_employee where enamein(白露,李诗雨,黄冰茹))--查询t_employee和t_department表按部门统计平均工资--显示部门平均工资比全公司的总平均工资高的部门编号、部门名称、部门平均薪资--并按照部门平均薪资升序排列 select t_department.did,dname,avg(salary)fromt_employee right join t_department on t_employee.didt_department.did group by t_department.did having avg(salary)(select avg(salary)fromt_employee)order by avg(salary);exists型子查询比如下面第一个案例也就是相当于对t_department的每一行进行判断其的did是否为null,并且exists中的select是什么事无所谓的比如看结果--查询t_employee表中是否存在部门编号为null的员工--如果存在查询t_department表的部门编号、部门名称 select*fromt_department where exists(select*fromt_employee where didisnull);同样的道理也是看t_department的每一行是否满足这个t_employee.didt_department.did条件满足的返回--查询t_department表是否存在与t_employee表相同部门编号的记录--如果存在查询这些部门的编号和名称 select*fromt_department where exists(select*fromt_employee where t_employee.didt_department.did);结果如下筛选去掉了一个测试部因为测试部门的id在员工表中不存在等价于--查询结果等价于下面的SQL select distinct t_department.*fromt_department inner join t_employee on t_department.didt_employee.did;from中嵌套子查询当子查询结果是多列的结果时通常将子查询放到from后面然后采用给子查询结果取别名的方式把子查询结果当成一张“动态生成的临时表”使用。若是使用这种方式select ename,did,salary,dense_rank()over(partition by did order by salary desc)aspaimingfromt_employee where dense_rank()over(partition by did order by salary desc)2;窗口函数是最后一步进行计算的所以报错sql中的执行顺序FROM→ WHERE→ GROUP BY→ HAVING→ SELECT ✅窗口函数在这里算# 在t_employee表中查询每个部门中薪资排名前2的员工姓名、部门编号和薪资select*from(select ename,did,salary,dense_rank()over(partition by did order by salary desc)aspaimingfromt_employee)temp where temp.paiming2;update中嵌套子查询--修改t_employee表中部门编号和测试部部门编号相同的员工薪资为原来薪资的1.5倍 update t_employeesetsalarysalary*1.5where did(select didfromt_department where dname测试部);--修改t_employee表中did为null的员工信息--将他们的did值修改为测试部的部门编号--这种子查询必须是单个值否则无法赋值 update t_employeesetdid(select didfromt_department where dname测试部)where didisnull;– 当update的表和子查询的表是同一个表时需要将子查询的结果用临时表的方式表示– 即再套一层子查询使得update和最外层的子查询不是同一张表update t_employeesetdid(select didfromt_department where dname测试部)where didisnull;所以使用下面的办法嵌套作为一个临时表--修改t_employee表中李冰冰的薪资值等于孙红梅的薪资值 update t_employeesetsalary(select salaryfrom(select salaryfromt_employee where ename孙红梅)temp)where ename李冰冰;– 修改t_employee表李冰冰的薪资与她所在部门的平均薪资一样注意一定要给临时表起别名否则报错update t_employee tsetsalary(select pingfrom(SELECT avg(salary)pingfromt_employee t2 where t.didt2.did)tmp)where ename李冰冰;delete中嵌套子查询--从t_employee表中删除测试部的员工记录 deletefromt_employee where did(select didfromt_department where dname测试部);--从t_employee表中删除和李冰冰同一个部门的员工记录 deletefromt_employee where did(select didfromt_employee where ename李冰冰);--报错因为删除和子查询是同一张表和上面同样的处理方式设置一个临时表delete from t_employeewhere did (select did from(SELECT did from t_employee where ename‘李冰冰’)temp)使用子查询复制表结构和数据1复制表结构– 仅复制表结构可以用create语句create table department like t_department;2复制一条或多条记录– 使用insert语句子查询复制数据此时insert不用写valuesinsert into department (select * from t_department where did3);3同时复制表结构和记录– 同时复制表结构数据create table d_department as (select * from t_department);– 如果select后面是部分字段复制的新表就只有这一部分字段通用表达式通用表达式简称为CTECommon Table Expressions。CTE是命名的临时结果集作用范围是当前语句。CTE可以理解为一个可以复用的子查询但是和子查询又有区别一个CTE可以引用其他CTECTE还可以是自引用(递归CTE)也可以在同一查询中多次引用但子查询不可以。with[recursive]cte_name[(字段名1,字段名2)]as(子查询),cte_name[(字段名1,字段名2)]as(子查询)– 在t_employee表中查询每个人薪资和公司平均薪资的的差值SELECT*FROM(SELECT ename AS员工姓名,salary AS薪资,AVG(salary)OVER()AS pingjun,ROUND(salary-AVG(salary)OVER(),2)AS差值FROM t_employee)t WHERE ROUND(t.薪资-t.pingjun,2)5000;可以这样写– 查询薪资低于9000的员工编号员工姓名员工薪资领导编号领导姓名领导薪资但是也可以使用CTE来替代子查询WITH empas(SELECT*fromt_employee where salary9000)select emp.salaryas员工薪资,emp.enameas员工姓名,emp.midas领导编号,mgr.enameas领导姓名,mgr.salaryas领导薪资fromemp join t_employee mgr on emp.midmgr.eid;--查询eid为21的员工和他所有领导直到最高领导--建表设置多层领导 create table empas(select eid,ename,salary,tel,midfromt_employee where salary10000);update empsetmid19where eid21;update empsetmid17where eid19;update empsetmid16where eid17;update empsetmid15where eid16;update empsetmid4where eid15;update empsetmidnull where eid4;select*fromemp;withrecursive cteas(select eid,ename,midfromemp where eid21unionallselect emp.eid,emp.ename,emp.midfromemp join cte on emp.eidcte.mid where emp.eidisnotnull)select*fromcte;这里CET循环解释一下循环有三个条件起始递归终止首先是起始select eid,ename,midfrom empwhere eid 21循环部分,相当于不断地在结果上进行递加union allselect emp.id emp.ename,emp.midfrom emp join cteon cte.midemp.eid终止条件where emp eid is not null总结一、整体总结1. 子查询Subquery子查询是指嵌套在其他 SQL 语句中的查询可以出现在SELECT、FROM、WHERE、HAVING、UPDATE、DELETE等位置。核心特点子查询先执行其结果作为外层 SQL 的条件或数据源。根据返回结果不同可分为单行单列、单列多行、多列多行。执行顺序子查询总是优先于主查询执行。2. CTECommon Table Expressions 通用表达式CTE 是MySQL 8.0引入的特性可以理解为可以命名的临时结果集只在当前语句有效。与子查询的区别对比项子查询CTE通用表达式是否需要命名不需要from中必须必须命名可重复使用不可以可以多次引用是否可相互引用不可以可以CTE 可以引用其他 CTE是否支持递归不支持支持加RECURSIVE可读性差嵌套多层很乱优秀结构清晰语法结构WITH[RECURSIVE]cte_name1AS(子查询1),cte_name2AS(子查询2)SELECT...FROMcte_name1...3. 递归 CTERECURSIVE CTE递归 CTE 是 CTE 的进阶用法用于处理层级关系、树形结构如查找所有上级、所有下属、组织架构等。核心组成种子查询提供起始点第一层数据递归查询通过UNION ALLJOIN自己调用自己终止条件当递归查询不再返回结果时自动停止执行本质像“滚雪球”一样一行一行不断累积结果而不是“在表格后面添加列”。二、各类子查询核心用法总结1. SELECT 中嵌套子查询常用于返回单值如公司平均薪资每次外层查询一行子查询就会执行一次性能较差2. WHERE / HAVING 中嵌套子查询单行单列直接用、、等比较单列多行用IN、ANY、ALLHAVING中可使用聚合函数WHERE中不能3. FROM 中嵌套子查询派生表子查询结果当作临时表使用必须给别名常用于窗口函数后筛选因为窗口函数在SELECT中最后计算4. UPDATE / DELETE 中嵌套子查询更新同一张表时容易出现You cant specify target table错误解决办法把子查询再包一层成为临时表三、高频易错点汇总重点以下是这篇笔记中最容易出错的地方按严重程度排序序号易错点具体表现正确做法1窗口函数写在 WHERE 中WHERE dense_rank() OVER(...) 2必须包一层子查询在外层用WHERE筛选2UPDATE/DELETE 与子查询操作同一张表报You cant specify target table子查询外面再包一层临时表3HAVING 中使用 SELECT 的别名HAVING 差值 5000外层用子查询或者重复写表达式4递归 CTE 方向写反ON emp.eid cte.mid想找上级却写成向下向上找领导用ON e.eid cte.mid向下找下属用ON e.mid cte.eid5递归 CTE 没有终止条件导致Recursive query aborted after 1001 iterations加上level字段 WHERE cte.level 20限制6子查询返回多行却用WHERE did (SELECT did ...)返回多行时报错改用IN7FROM 子查询不写别名FROM (SELECT ...)没给别名必须写别名如FROM (...) t8EXISTS 子查询写法混淆把EXISTS当成返回具体值使用EXISTS只关心“有没有结果”不关心内容9CTE 中忘记写 RECURSIVE递归查询无法生效递归必须写WITH RECURSIVE10多表查询不加表别名导致 ambiguousColumn eid is ambiguous所有字段都加上表别名如emp.eid、mgr.eid