MySQL练习

USE dept_emp; 

单表查询
-- 1.选择部门30中的所有员工

select * from emp where deptno=30;

-- 2.列出所有办事员的姓名，编号和部门编号
select empno,ename,deptno from emp where job="办事员";

-- 3.找出佣金高于薪金的员工
select * from emp where comm > sal;

-- 4.找出没有佣金的员工
select * from emp where comm is null;

-- 5.找出佣金高于薪金的60%的员工
select * from emp where comm/sal > 0.6;

-- 6.找出部门10中所有人事部长和部门20中所有办事员的详细资料
select * from emp where (deptno = 10 and job = "人事部长") or (deptno = 20 and job = "办事员");

-- 7.找出收取佣金的员工
select * from emp where comm !=0;

-- 8.找出不收取佣金或收取的佣金低于100的员工
select * from emp where comm = 0 or comm<100;

-- 9.找出姓张的员工的信息
select * from emp where ename like "张%";

-- 10.显示员工的姓名和受雇日期,新的员工排在最前面
select ename, hiredate from emp order by hiredate desc ;

-- 11.按工作的降序排序,若工作相同则按薪金排序
select * from emp order by job desc, sal;

-- 12.查出不姓刘的员工的信息
select * from emp where ename  not like "刘%";

-- 13.取出姓李的员工
select * from emp where ename like "李%";

-- 多表查询
-- 1. 查询出每一位雇员的编号、姓名、职位、部门名称、位置
select e.empno, e.ename, e.job, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno;

-- 2.要求查询出每一位雇员的姓名、职位、领导的姓名
select e.ename,e.job,m.ename as mgrname from emp e,emp m where e.mgr=m.empno;

-- 3.查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。
select e.empno,e.ename,e.sal,e.job,m.ename,d.dname,d.loc from emp e,emp m,dept d where e.mgr=m.empno and e.deptno=d.deptno;"

-- 4.列出在部门"销售部"工作的员工的姓名，假定不知道销售部的部门编号。
select emp.ename from emp ,dept where emp.deptno = dept.deptno and dept.dname = "销售部"; 

-- 5.列出与"李兴"从事相同工作的所有员工。
select * from emp where job =(select job from emp where ename = "李兴");

-- 6.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal > (select max(sal) from emp where deptno = 30); 

-- 7.列出受雇日期早于其直接上级的所有员工。
select ename from (select e.ename,e.hiredate,m.hiredate as mgrhiredate,m.ename as mgrname from emp e,emp m where e.mgr=m.empno) as r where hiredate > mgrhiredate;

-- 分组函数查询
-- 1.按照部门编号分组，求出每个部的人数，平均工资
select deptno,count(*),avg(sal) from emp group by deptno;

-- 2.按照职位分组，求出每个职位的最高和最低工资
select job ,max(sal), min(sal) from emp group by job;

-- 3.计算出每个各职位的平均工资
select job,avg(sal) from emp group by job;

-- 4.查询出每个部门的名称、部门的人数、平均工资
select dname,count(ename),avg(sal) from emp, dept where emp.deptno=dept.deptno group by dname;

-- left outer join on

-- 5.要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于2000；
 select * from
(select emp.deptno,dname,loc,avg(sal) as avgsal from emp,dept where emp.deptno=dept.deptno group by deptno) t
left join emp
on t.deptno = emp.deptno
where avgsal > 2000;

-- 6.要求查询出工资比华仔还要高的全部雇员信息
select * from emp left join dept on emp.deptno=dept.deptno where sal > (select sal from emp where ename="华仔");

-- 7.要求查询出高于公司平均工资的全部雇员信息
select * from emp left join dept on emp.deptno=dept.deptno where sal > (select avg(sal) from emp);

-- 8.查询出每个部门的编号、名称、位置、部门人数、平均工资
select e.deptno, d.dname, d.loc,count(*), avg(sal) from emp e, dept d where e.deptno= d.deptno group by deptno;

-- 9.列出至少有一个员工的所有部门。
select dname from (select e.deptno, d.dname, count(*) as totalnum from emp e, dept d where e.deptno= d.deptno group by deptno) as r where totalnum >= 1; 

-- 等级查询
-- 1.查询每个工资等级各有多少员工
select s.grade,count(*) as Number from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by s.grade

-- 2.查询部门中（所有人）的平均工资等级
select t.dname,s.grade,avgsal
from (select dname,avg(sal) as avgsal from emp,dept where emp.deptno=dept.deptno group by dname) t
inner join salgrade s
on t.avgsal BETWEEN s.losal and s.hisal
ORDER BY t.dname;