8.14 mysql之子查询
定义：一个查询嵌套另一个查询例：求出IT部门的姓名和员工编号
it部门 dept表 中的dept_name
name，sid emp表中
内连接（先合表再根据条件查询）
select name,sidfromdeptinner join emp on dept.dept1=emp.dept2where dept_name='IT技术' ;
select dept1 from dept where dept_name ='IT技术'；
子查询：select name，sidfrom emp where dept2=(select dept1 from dept where dept_name ='IT技术');
========================================================================
子查询的分类：
1、标量子查询
2、列量子查询
3、行子查询
4、表子查询
========================================================================
1、标量子查询（返回一个值）
标量子查询 ：把一个sql执行返回的一个值，作为另一个sql执行的一个条件，返回结果是一行一列，一般出现在where之后。
例：select dept1 from dept where dept_name ='IT技术'；
select name，sidfrom emp where dept2=(select dept1 from dept where dept_name ='IT技术');
data:,
注意点：允许使用运算付号：=，！=，>,<,>=,<=,<>
========================================================================
2、列子查询（返回一个列）
定义：返回的是一列值
注意：通常在where条件后使用， in或not in 不能使用=，<,>等运算符
例：select dept1 from dept where dept_name ='IT技术'or dept_name ='财务' （1列多行）
select name，sidfrom emp where dept2 in(select dept1 from dept where dept_name ='IT技术' or dept_name ='财务' ）；
select name，sidfrom emp where dept2 =(select dept1 from dept where dept_name ='IT技术'or dept_name ='财务' ）；（报错）
select name，sidfrom emp where dept2 = any (select dept1 from dept where dept_name ='IT技术'or dept_name ='财务' ）；（对的）
anysomeall（了解）
data:,
=========================================================================
3、行子查询（返回一行多列）
指子查询返回的结果是一行多列，一般出现在where后
造一个1880 刘十 64 1971/10同牛9
1881 马12 同刘十
例：selectage, incoming from emp where name='马十二'；
select* from empwhere (age,incoming)=any (selectage, incoming from emp where name='马十二');
=======================================================================
4、表子查询（重点）（返回一个表）
select *from dept,emp where dept1=dept2
selectname,sid from (select *from dept,emp where dept1=dept2) s where age=64;
一整个表
data:,
临时表：给表取一个别名，聚合函数 最好取别名
=====================================================================
案例
2.财务部门的收入总和；
selectsum(incoming) fromempwhere dept2=(
selectdept1fromdept where dept_name="财务") ;
=====================================================================
1674 李四 32 1983/4/1 3500 101
1776 王五 24 1990/7/1 2000 101
1568 赵六 57 1970/10/11 7500 102
1564 荣七 64 1963/10/11 8500 102
1879 牛八 55 1971/10/20 7300 103
1.列出每个部门的平均收入及部门名称;
emp incoming
deptdept_name
avg
group by
例1：selectavg(incoming ),dept_name from (select * from dept2 inner joinemp2ondept2.dept1=emp2.dept2)c group by dept_name;
left的更好
selectavg(incoming) ,dept_name from (dept leftjoin emp on dept.dept1=emp.dept2)cGROUP BY dept_name;（有行政）
2.财务部门的收入总和；
dept "财务"
emp incoming
sum
例1：selectsum(incoming ),dept_name from dept inner joinempondept.dept1=emp.dept2where dept_name="财务" ;
例2：select sum(incoming) from (select *fromdept2,emp2 where dept2.dept1=emp2.dept2)c where dept_name='财务';
子查询：select sum(incoming) fromemp where dept2=(selectdept1fromdeptwheredept_name="财务");
3.It技术部入职员工的员工号
emp sid
dept it技术部门
name ，sid
例1：select name,sid from (select * from dept2,emp2 where dept2.dept1=emp2.dept2)c where dept_name='IT技术';
例2：select emp.sid from dept inner join emp on dept.dept1=emp.dept2 where dept_name="技术 ";
例3：selectsidfromempwhere dept2=(selectdept1fromdept where dept_name="IT技术") ;
4.财务部门收入超过2000元的员工姓名
emp incoming>2000
dept财务
例1：select name from (select * from dept2,emp2 where dept2.dept1=emp2.dept2)c where incoming>2000 and dept_name= '财务';
例2：SELECT emp2.name fromemp2 where dept2=
(select dept1 from dept2 where dept_name='财务') and incoming>2000;
例3：SELECT name,incoming FROM Dept INNER JOIN Emp on Dept1=DEPT2 WHERE incoming>2000 AND Dept_name="财务";-- 4
5.找出销售部收入最低的员工的入职时间；
emp
dept
max
select name,worktime_start,dept_name,min(incoming) from (select * from dept2 inner join emp2 on dept2.dept1=emp2.dept2)cwheredept_name='销售';（错误：默认）
例1：select name,worktime_start from (select *,min(incoming) from (dept inner join emp on dept1=dept2)group by dept_name)s where dept_name='销售'
例2：
select name,woektime_start from (select *,min(incoming) from dept inner join emp on dept1=dept2 group by dept_name)s where dept_name='销售'
select woektime_start FROM emp INNER JOIN dept on dept.dept1=emp.dept2 whereincoming =(select min(incoming) from emp INNER JOIN and dept on emp.dept2=dept.dept1 where dept.dept_name="销售")and dept_name="销售";
select ename "姓名",etime "入职时间",deptname from dept1,emp1 where dept1.deptbm=emp1.deptbm1 and
salary=(select min(salary) from dept1,emp1 where dept1.deptbm=emp1.deptbm1 and deptname="销售" ) and deptname="销售" ;
① select a.dept_name,b.id,
b.name,b.worktime_startfrom dept a emp b wherea.dept1 =b.dept2 anda.dept_name = "销售" and b.incoming = (select min(b.incoming) from dept a emp b where a.dept1 = b.dept2 anda.dept_name = "销售" );
② selecta.dept_name,b.id,
b.name,b.worktime_startfrom dept ajoin emp b ona.dept1 =b.dept2 where a.dept_name = "销售"ORDER BY b.incoming asc LIMIT 1;（可以，但是有重复的数据只显示一条）
6.找出年龄小于平均年龄的员工的姓名，ID和部门名称
sid dept_name
select name, sid,dept_name from (select * from dept2,emp2 where dept2.dept1=emp2.dept2)c where age< (select avg(age) from emp2);
7.列出每个部门收入总和高于9000的部门名称
select dept_name,sum(incoming) as sFROM(select * from dept2,emp2 where dept2.dept1=emp2.dept2)c GROUP BY dept_name havings>9000;（默认）
少一层？
例1：1select s.dept_name from (select a.dept_name,sum(b.incoming) as k from dept a join emp b on a.dept1 = b.dept2GROUP BYa.dept_name HAVING k >9000)s;
例2：
SELECT dept_name FROM ( SELECT sum( incoming ) a, dept_name FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 GROUP BY dept_name ) b WHERE a > 9000;
例3：
SELECT dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2
GROUP BY dept_name having sum(incoming)>9000;
8.查出财务部门工资少于3800元的员工姓名
财务dept
incomingemp
name
<
例1：
select name, incoming from(select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
where dept_name='财务' andincoming<3800;
例2：
select name from emp2 where incoming<3800 and
deptno=(SELECT deptno from dept2 WHERE dept_name='财务');
9.求财务部门最低工资的员工姓名；
min
deptdept_name
empincomingmin
select name,min(incoming) from(select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
where dept_name='财务'
select name from emp where incoming=(select min(incoming)from emp inner join dept on dept1= dept2 where dept_name='财务');
财务里找出2000 而其他部门可能也有两千。所以还要加部门=xxx
例1：
select name from 合表 whereincoming =(select min(incoming) from emp left join dept on emp.dept2=dept.dept1 where dept.dept_name="财务") and dept_name="财务";
例2：
10.找出销售部门中年纪最大的员工的姓名
select name,max(age)
from(select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
where dept_name='销售';
例1：
select name from emp inner join dept on dept1=dept2where age=(select max(age)from emp inner join dept on dept1=dept2 where dept_name='销售') and dept_name='销售';
例2：
SELECT name age from emp2 INNER JOIN dept2 on emp2.dept2=dept2.dept1
WHERE dept_name='销售'ORDER BY age DESC LIMIT 0,1;
11.求收入最低的员工姓名及所属部门名称：
select name,dept_name,min(incoming) from(select * from dept2,emp2 where dept2.dept1=emp2.dept2)c;
select name,dept_name from emp inner join dept on dept1=dept2where incoming=(select min(incoming)from emp inner join dept on dept1=dept2)
12.求李四的收入及部门名称
select name,dept_name,incoming from(select * from dept2,emp2 where dept2.dept1=emp2.dept2)c where name='李四';
13.求员工收入小于4000元的员工部门编号及其部门名称
select name,dept1,dept_name
from(select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
where incoming<4000;
14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
select name,dept_name,incoming
from(select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
groupby dept_name
orderby incoming desc;
15.求出财务部门收益最高的俩位员工的姓名，工号，收益
select name,sid,incoming from dept2 inner join emp2 on dept2.dept1=emp2.dept2 where dept_name='财务' order by incoming desc limit 0,2;
16.查询财务部低于平均收入的员工号与员工姓名：
select sid,name
from (select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
where incoming<(select avg(incoming) from emp2) and dept_name='财务';
17.列出部门员工数大于1个的部门名称；
select dept_name,count(sid) from dept2 inner joinemp2 on dept2.dept1=emp2.dept1
group by dept_name having count(sid)>1;
18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
select name,age,dept2
from (select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
where 7500>=incoming and incoming>3000;
19.求入职于20世纪70年代的员工所属部门名称；
select name,dept_name
from (select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
where worktime_startbetween "1970-1-1" and "1979-12-31";
20.查找张三所在的部门名称；
select dept_name
from (select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
where name='张三';
21.列出每一个部门中年纪最大的员工姓名，部门名称；
select name,dept_name,max(age) from (select * from dept2,emp2 where dept2.dept1=emp2.dept2)c group by dept_name;
22.列出每一个部门的员工总收入及部门名称；
select dept_name,sum(incoming)
from (select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
group by dept_name;
23.列出部门员工收入大于7000的员工号，部门名称；
select sid,dept_name
from (select * from dept2,emp2 where dept2.dept1=emp2.dept2)c
where incoming>7000;
24.找出哪个部门还没有员工入职；
select dept_name from dept2 left join emp2 on dept2.dept1=emp2.dept2
where name is NULL;
25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
select * from dept2 left join emp2 on dept2.dept1=emp2.dept2 order by dept2 asc,worktime_start asc;
26.求出财务部门工资最高员工的姓名和员工号
select name,sid from emp2 inner join dept2 on dept1=dept2 where incoming=(select max(incoming) from emp2 inner join dept2 on dept1=dept2 where dept_name='财务' ) and dept_name='财务';
27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
select name,dept_name from dept2 left join emp2 on dept2.dept1=emp2.dept2 where age=(select max(age)from emp2 where incoming between 7500 and 8500) ;
页:
[1]