Mysql之多表查询&子查询练习1

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

列: d.deptno、d.dname、d.loc、部门人数
表: dept d,emp e
条件: 多表查询:使用关联列去除笛卡尔积: e.deptno = d.deptno

部门人数: select deptno,count(*) from emp group by deptno

SELECT d.deptno, d.dname,d.loc,z1.cnt
FROM (select deptno,count(*) cnt from emp group by deptno) z1 INNER JOIN dept d
on z1.deptno = d.deptno;

思想： 单独两部分都会剩下的采用链接！



2. 列出薪金比关羽高的所有员工。
列：*
表: emp
条件: 工资比关羽高--> 那关羽的工资是多高

分析：那关羽的工资是多高: select sal from emp where ename = '关羽'; 子查询的结果集是单行单列一般放在where后面作为条件！

select *
from emp 
where sal > (select sal from emp where ename = '关羽')




3. 列出所有员工的姓名及其直接上级的姓名。
列：e.ename,e2.ename
表  e,z1
条件: e.mgr = z1.empno

-- 
select e.ename '员工',z1.ename '上级' 
from emp e inner join (select empno,ename from emp) z1
on e.mgr = z1.empno

-- 上面还是有问题，是列出所有员工！
select e.ename '员工', m.ename '上级'
from emp e left outer join emp m
on e.mgr = m.empno;

思想: 自身多表连接

select * from emp;


4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
列：e.empno,e.ename,d.dname
表：emp e, emp m, dept d
条件：是上级并且受雇日期早于上级：e.mgr = m.empno and e.hiredate < m.hiredate，注意：

思路：先不查部门名称
select e.empno, e.ename,e.deptno
from emp e inner join emp m
on e.mgr = m.empno and e.hiredate < m.hiredate
注意：关联表时都需要一个列作为条件，不一定是从表的外键指向主表的主键，其他的也行,就行这里的e.mgr = m.empno 也行,为了去除笛卡尔积！

然后只剩部门名称了
select e.empno, e.ename,d.dname
from emp e inner join emp m inner join dept d
on e.mgr = m.empno and e.hiredate < m.hiredate and e.deptno = d.deptno;

-- 下面使用子查询的方式也可以完成，但sql过于复杂！
select z1.empno,z1.ename,d.dname
from (select e.empno empno, e.ename ename,e.deptno deptno
from emp e inner join emp m
on e.mgr = m.empno and e.hiredate < m.hiredate) z1 inner join dept d
on z1.deptno = d.deptno



5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
列：d.dname, e.*
表：emp e, dept d
条件: e.deptno = d.deptno 

select e.*, d.dname
from emp e right outer join dept d
on e.deptno = d.deptno


6. 列出所有文员的姓名及其部门名称，部门的人数。
列：e.ename,d.dname,部门人数
表: emp e, dept d
条件 e.deptno = d.deptno

注：部门人数: select deptno,count(*)  from emp group by deptno

思路：先简化一下,不要部门的人数
select e.ename,d.dname
from emp e inner join dept d
on e.deptno = d.deptno and e.job = '文员'; //使用标准内连接，有条件一般用and连上 ！而不是where!

最后：
select e.ename,d.dname,z1.cnt
from emp e inner join dept d inner join (select deptno,count(*) cnt  from emp group by deptno) z1
on e.deptno = d.deptno and e.job = '文员' and d.deptno = z1.deptno;


7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
列：e.job,员工人数
表：emp e
条件：最低薪金大于15000: min(sal) > 15000 : 只是这样表示而已!

思路：先简化一下,不要从事此工作的员工人数。
先根据部门编号分组,查看一下每个组中最低的工资是多少！
select deptno,min(sal)
from emp group by deptno

最后！
select e.ename,e.job, z2.cnt
from emp e inner join (select min(sal) minSal from emp group by deptno) z1 inner join (select job,count(*) cnt from emp group by job) z2
on e.deptno = z1.deptno and z1.minSal > 10000 and e.job = z2.job

判断：条件用在分组前还是分组后,看有没有聚合函数比如这里的min(sal) > 15000, 如果有就是在分组后
我这个分析有问题！

答案是这样的
select job,count(*)
from emp
group by job
having min(sal) > 15000


8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
列：e.ename
表：emp e, dept d
条件：e.deptno = 销售部的编号 = (select deptno from emp where dname = '销售部')

列出在销售部工作的员工的姓名
方式一：子查询
select e.ename
from emp e
where e.deptno = (select deptno from dept where dname = '销售部')


方式二：连接查询
select e.ename
from emp e inner join dept d
on e.deptno = d.deptno and d.dname = '销售部'




9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
列：e.*, d.dname, 上级领导，工资等级
表：emp e, emp m,dept d
条件: 高于公司平均薪金 sal>avg(sal)

select e.ename,e.sal,d.dname, m.ename '上级'
from emp e inner join dept d inner join emp m
on e.deptno = d.deptno and e.mgr = m.empno and e.sal>(select avg(sal) from emp)

我上面写法不是很好，应该连接一张表就给出条件，应该如下:
select e.*,d.dname,m.ename '上级',s.grade
from 
		emp e left outer join dept d on e.deptno = d.deptno
          left outer join emp m  on e.mgr = m.empno
          left outer join salgrade s on e.sal between s.losal and s.hisal
where e.sal > (select avg(sal) from emp)          


使用方言写一遍：
select e.*,d.dname,m.ename '上级',s.grade
from emp e,dept d,emp m,salgrade s
where e.deptno = d.deptno and e.mgr = m.empno and e.sal between s.losal and s.hisal and e.sal > (select avg(sal) from emp)

总结：标准是一个连接一个条件，方言是先全部连接，后全部条件
      标准是一个饺子粘点酱吃，方言是先把饺子吃完,再把酱喝完！


10.列出与庞统从事相同工作的所有员工及部门名称。
列：e.ename,d.dname
表：emp e, dept d
条件：庞统从事相同工作--> 庞统是什么工作--> select job from emp where ename = '庞统'

select e.ename,e.job,d.dname
from emp e inner join dept d
on e.deptno = d.deptno and job = (select job from emp where ename = '庞统') 

11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
列：e.ename,e.sal,d.dname
表：emp e,dept d
条件：sal > (30部门最高工资) = select max(sal) from emp where deptno = 30;

select e.ename,e.sal,d.dname
from emp e inner join dept d on e.deptno = d.deptno
where e.sal > (select max(sal) from emp where deptno = 30)

//这样也行
select e.ename,e.sal,d.dname
from emp e inner join dept d on e.deptno = d.deptno
where e.sal > all (select sal from emp where deptno = 30)


12.列出每个部门的员工数量、平均工资。
select * from emp
列：count(*),avg
表：emp e
条件：

select deptno,count(*),avg(sal)
from emp
group by deptno

注: 一旦group by，那么group by后面列名，会出现在select后，其他列名出现会有错误,除非聚合函数包着!

13.查出年份、利润、年度增长比
今年的增长比 = 今年的增长-去年的增长/去年的增长
所以要找到一行记录，有今年的增长，去年的增长

create table tb_year(
	year int primary key auto_increment,
  zz int
);

insert into tb_year values(2010,100);
insert into tb_year values(null,150);
insert into tb_year values(null,250);
insert into tb_year values(null,800);
insert into tb_year values(null,1000);

select * from tb_year


select t1.*, ifnull(concat((t1.zz - t2.zz)/t2.zz*100,'%'),'0%') '年度增长比'
from tb_year t1 left outer join tb_year t2
on t1.year = t2.year+1

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数

dept表: 部门编号、部门名称、部门位置
emp表: 部门人数

-- 方式一: 先表之间连接，再过滤内容
SELECT
	d.dname, d.loc, d.deptno,count(e.deptno) as deptCount
from 
	dept d 
inner join 
	emp e
on
	d.deptno = e.deptno
group by
	e.deptno
having 
	count(*) > 1


-- 方式二：先得的每个表的数据，表与表之间再连接
SELECT
	d.deptno, d.dname, d.loc, temp.deptCount
FROM
	dept d
inner join 
(
SELECT
	count(deptno) as deptCount, deptno
FROM
	emp
GROUP BY
	deptno
having 
	count(deptno) > 1
) temp
on
	d.deptno = temp.deptno

-- 方式二的简洁写法: 在子查询多且复杂的时候能体现出来优势
with temp as(
	select deptno, count(*) as deptCount from emp group by deptno having count(*) > 1
)
select 
	d.dname, d.loc, d.deptno, temp.deptCount
from 
	dept d inner join temp on d.deptno = temp.deptno


=========================================================================================4.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
select 
	e.ename, 
	e.hiredate as '员工受雇日期', 
	t.ename as '上级领导',
	t.hiredate as '上级领导的受雇日期'
from 
	emp e
inner join emp t on e.mgr = t.empno where e.hiredate < t.hiredate
inner join dept d  on e.deptno = d.deptno
注：这种写法是错误的例子


-- 方式一: 先表之间连接，再过滤内容,一次性查出来不需要子查询
-- 这种多表连的话, on只能有一个,表与表之间去除笛卡尔积的条件用and连起来, 过滤的用where
select 
	e.ename, 
	e.hiredate as '员工受雇日期', 
	t.ename as '上级领导',
	t.hiredate as '上级领导的受雇日期'
from 
	emp e 
inner join 
	emp t 
inner join 
	dept d 
on 
	e.mgr = t.empno and e.deptno = d.deptno
where 
	e.hiredate < t.hiredate
	
-- 方式二：分两步走，分别把数据查出来，再连接表
-- 1.列出受雇日期早于直接上级的所有员工的编号、姓名
select 
	e.empno, e.ename, e.deptno
from 
	emp e
inner join 
	emp m
on 
	e.mgr = m.empno
where
	e.hiredate < m.hiredate

-- 2.部门表
select
	d.* 
from 
	dept d 


select 
	d.*, temp.empno, temp.ename
from 
	dept d
inner join
(
select 
	e.empno, e.ename, e.deptno
from 
	emp e
inner join 
	emp m
on 
	e.mgr = m.empno
where
	e.hiredate < m.hiredate
) temp 
on temp.deptno = d.deptno

-- 稍微简洁点,看的很清楚
with e as(
	select 
		e.empno, e.ename, e.deptno
	from 
		emp e
	inner join 
		emp m
	on
		e.mgr = m.empno
	where
		e.hiredate < m.hiredate
)
select 
	*
from 
	dept d inner join e
on 
	d.deptno = e.deptno