mysql-练习

(day05)

关联查询练习

1.查询出每个员工的名字和所对应的地址

select e.ename,d.loc from emp e join dept d on e.deptno=d.deptno;

2.查询出所有商品的名字和对应的分类码名

select i.title,c.name from t_item i left join t_item_category c on i.category_id=c.id;

3.查询拿最低工资的员工姓名和他的部门名称

select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno where sal=(select min(sal) from emp);

**having要和group by结合使用,不要单独使用***

作业：

  1. 每个部门的人数,根据人数排序

select deptno,count(*) c from emp where deptno is not null group by deptno order by c;

  2. 每个部门中，每个主管的手下人数**

select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;

  3. 每种工作的平均工资

select job,avg(sal) from emp group by job;

  4. 每年的入职人数 

select count(*),extract(year from hiredate) y from emp group by y;

  5. 少于等于3个人的部门

select deptno,count(*) c from emp group by deptno having c<=3; 

  6. 拿最低工资的员工信息
  

select * from emp where sal=(select min(sal) from emp);

  7. 只有一个下属的主管信息
  

select mgr from emp group by mgr having count(*)=1;

通过主管id查主管详情

select * from emp where empno in

(select mgr from emp group by mgr having count(*)=1);

select mgr,count(*) c from emp e join 

(select mgr from emp group by mgr having count(*)=1;)

 on e.deptno=d.deptno group by mgr having count(*)=1;

  8. 平均工资最高的部门编号
 

select deptno from emp group by deptno order by avg(sal) limit 2,1;

     **有拓展**

---考虑平均工资最高的会有多个部门

1.得到最高的平均工资

2.通过最大平均工资查询对应的部门编号

3. 如果需要查看部门详情

  9. 下属人数最多的人，查询其个人信息

select 

  11. 最后入职的员工信息

select * from emp where hiredate=(select max(hiredate) from emp);

  12. 工资多于平均工资的员工信息

select 

  13. 查询员工信息，部门名称
   

select 

    
  14. 员工信息，部门名称，所在城市
   

select 

  
  15. DALLAS 市所有的员工信息
 

select 

                          
  16. 按城市分组，计算每个城市的员工数量
   

select d.loc,count(*) from dept d join emp e on e.deptno=d.deptno group by d.loc; 

 
  17. 查询员工信息和他的主管姓名-----自关联(把一张表拆分成两张表)
   

select e.*,m.ename from emp e join emp m on e.mgr=m.empno;

   
  18. 员工信息，员工主管名字，部门名-------三张表

select e.*,m.ename 主管姓名,d.dname 

from emp e join emp m 

on e.mgr=m.empno 

join dept d on d.deptno=e.deptno;

    20. 员工和他所在部门名

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

  22. 查询emp表中所有员工的编号，姓名，职位，工资以及该员工上级领导的编号，姓名，职位，工资

select e.empno,e.ename,e.job,e.sal,m.empno 上级编号,m.ename 上级姓名,m.job 上级职位,m.sal 上级工资
from emp e left join emp m on e.mgr=m.empno;

  23.查询emp表中名字中没有字母'K'的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址

select e.empno,e.ename,e.job,d.* from emp e left join dept d on e.deptno=d.deptno where e.ename not like '%k%';

  24. 查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资

select d.*,e.empno,e.ename,e.job from emp e right join dept d on e.deptno=d.deptno;