【数据库】数据库的分组查询、排序查询、连接查询 及 相关子查询

分组统计

• -- 查询各部门员工的平均年收入

select deptno , avg((sal + COALESCE(comm,0)) * 12) 平均年收入 from emp group by deptno;

 

 -- 查询不同职务的员工的总年收入

select job , sum((sal + COALESCE(comm,0)) * 12) 平均年收入 from emp group by job;

 

 -- 查询各部门下各职务员工的平均年收入

select deptno,job,avg((sal + COALESCE(comm,0)) * 12) 平均年收入 from emp group by deptno,job;

-- 查询10，20部门下各职务员工的平均年收入

select deptno,job,avg((sal + COALESCE(comm,0)) * 12) 平均年收入 from emp group by deptno,job having deptno in 
(10,20) ;
-- 不推荐

select deptno,job,avg((sal + COALESCE(comm,0)) * 12) 平均年收入 from emp where deptno in (10,20) group by deptno,job ;
-- 推荐

 

 

 

 

• 排序

 --按工资由多到少排列

select * from emp order by sal +  COALESCE(comm,0) desc;

 

 

--按工资由少到多排列

select * from emp order by sal +  COALESCE(comm,0),hiredate ;
​

 

 

 

多表查询（笛卡尔机原理）

https://www.cnblogs.com/leibao/p/8471630.html 

 

连接查询

 

自然连接

select * from emp,dept;

 

 

 

等值连接 86标准

查询员工基本信息及其所在部门的信息

--查询员工基本信息及其所在部门的信息
select * from emp,dept where emp.deptno = dept.deptno;


select empno,ename,job,sal,comm,hiredate,deptno,dname,loc from emp,dept where emp.deptno = dept.deptno; 
-- 这个查询是错误的，因为连接后投影列必须加上表名做前缀，表示列属于哪个表


select emp.empno,emp.ename,emp.job,emp.sal,emp.comm,emp.hiredate,emp.deptno,dept.dname,dept.loc from emp,dept where emp.deptno = dept.deptno;  
-- 这个是正确的


select e.empno,e.ename,e.job,e.sal,e.comm,e.hiredate,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno;
-- 一般表名使用别名

 

等值连接 | 92标准 | 报表查询

select e.ename,e.job,e.sal,e.comm,d.dname,d.loc from emp e join dept d on e.deptno = d.deptno;

 

内连接

查询员工工资等级

select e.ename,e.sal,s.grade,s.losal,s.hisal from emp e join salgrade s;

​
非等值接连

 查询员工姓名，收入信息及其工资的等级

select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

 

 查询员工姓名，收入信息及其工资的等级

select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

查询员工姓名，收入信息及其工资的等级及员工的经理的姓名

select e.ename,e.sal,e1.ename,s.grade from emp e join salgrade s join emp e1 on (e.sal between s.losal and s.hisal) and e.mgr = e1.empno 
-- 了解


select e.ename,e.sal,e1.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal join emp e1 on e.mgr = e1.empno;
-- 推荐

 

 

右外连接:无限制左表

 查询员工姓名，收入信息及其工资的所有等级（即便工资等级没有员工也要列出）

select e.ename,e.sal,s.grade from emp e right outer join salgrade s on e.sal between s.losal and s.hisal;

 

左外连接：无限制右表

select e.ename,e.sal,s.grade from salgrade s left join emp e on e.sal between s.losal and s.hisal;

 

全外连接
select e.ename,e.sal,s.grade from salgrade s full outer join emp e on e.sal between s.losal and s.hisal;

-- 这个会报错，因为mysql不支持全外连接

 

自连接

查询员工的姓名及1其经理的姓名

select e.ename,e.empno 员工编号,e1.ename,e.mgr 经理编号 from emp e join emp e1 on e.mgr = e1.empno;

 

 

【先分等级再连接】

查询员工姓名、收入信息及其工资的等级及员工的经理的姓名

select e.ename,e.sal,e1.ename,s.grade from emp e join salgrade s join emp e1 on (e.sal between s.losal and s.hisal) and e.mgr = e1.empno;

【先连接再分等级】

select e.ename,e.sal,e1.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal join emp e1 on e.mgr = e1.empno;

 

 

​相关子查询

 查找至少有一个员工的经理 相关子查询

select * from emp e where exists(select * from emp e1 where e1.mgr = e.empno);

查找至少有三个员工的经理 相关子查询

select * from emp e where 3 <= (select count(*) from emp e1 where e1.mgr = e.empno);

查找至少有三个员工的经理的员工 相关子查询

select t.empno 员工编号,t.ename 员工姓名,e3.ename 经理姓名 from emp e3 join
(select * 
from emp e2 
where e2.mgr in (
   select e.empno 
   from emp e 
   where 3 <= (
       select count(*) 
       from emp e1 
       where e1.mgr = e.empno)
    ))t 
on t.mgr = e3.empno;