Mysql——案例题

1.第一步：找出每个部门的最高薪水

mysql> select deptno,max(sal) as maxsal from emp group by deptno;

2.将以上查询结果当做临时表t，t表和emp e表连接，条件：t.deptno=emp.deptno and e.sal = t.maxsal;

mysql> select e.ename,t.*
    -> from
    -> emp e
    -> join
    -> (select deptno,max(sal) as maxsal from emp group by deptno) t
    -> on
    -> t.deptno=e.deptno and e.sal = t.maxsal;

二、哪些人的薪水在部门的平均薪水之上
1.第一步：找出部门的平均薪水

mysql> select deptno,avg(sal) as avgsal from emp group by deptno

2.第二步：将以上查询结果当做t表，t和emp表连接
条件：部门编号相同，并且emp的sal大于t表的avgsal

mysql> select
    -> t.*,e.ename,e.sal
    -> from
    -> emp e
    -> join
    -> (select deptno,avg(sal) as avgsal from emp group by deptno) t
    -> on
    -> e.deptno=t.deptno and e.sal>t.avgsal;

三、取得部门中（所有人的）平均的薪水等级
第一步：先找出每个薪水的等级

mysql> select
    -> e.ename,e.deptno,e.sal,s.grade
    -> from
    -> emp e
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal;

第二步：

mysql> select
    -> e.deptno,avg(s.grade)
    -> from
    -> emp e
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal
    -> group by
    -> e.deptno;

四、不准用组函数，取得最高薪水
1.max()函数

mysql> select max(sal) from emp;

2.order by 排序

mysql> select ename,sal from emp order by sal desc limit 1;

3.表的自连接

mysql> select distinct a.sal from emp a join emp b on a.sal > b.sal
#最大值不包含在上述语句
mysql> select sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal > b.sal);

五、取得平均薪水最高的部门的部门编号
第一种方案：
1.第一步：找出每个部门的平均薪水

mysql> select deptno,avg(sal) from emp group by deptno;

2.第二步：对结果降序排列取第一个

mysql> select deptno,avg(sal) from emp group by deptno order by avg(sal) desc limit 1;

第二种方案：
1.第一步：找出每个部门的平均薪水

mysql> select deptno,avg(sal) from emp group by deptno;

2.第二步：找出以上结果中avgsal最大的值。

mysql> select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t;

3.第三步：找出平均薪资是avgsal的部门

mysql> select
    -> deptno,avg(sal) as avgsal
    -> from
    -> emp
    -> group by
    -> deptno
    -> having
    -> avgsal = (select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t);

六、取得平均薪水最高的部门的部门名称

mysql> select 
    -> d.dname,avg(e.sal) as avgsal
    -> from 
    -> emp e 
    -> join 
    -> dept d 
    -> on 
    -> e.deptno=d.deptno
    -> group by
    -> d.dname
    -> order by
    -> avgsal desc
    -> limit
    -> 1;

七、求平均薪水的等级最低的部门的部门名称

最低的薪水对应的薪水等级是最低的，但是最低的薪水等级对应的薪水不一定是最低的。
1.找出最低的平均薪水对应的等级，得到最低的薪水等级

#最低的部门平均薪水
mysql> select avg(sal) as avgsal from emp group by deptno asc order by avgsal limit 1;
#最低的平均薪水对应的薪水等级
mysql> select grade from salgrade s where (select avg(sal) as avgsal from emp group by deptno asc order by avgsal limit 1) between losal and hisal;

2.找出部门的平均薪水，表连接找出薪水的等级

mysql> select
    -> t.*,s.grade
    -> from
    -> (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
    -> join
    -> salgrade s
    -> on
    -> t.avgsal between s.losal and s.hisal;

3.找出平均薪水的等级最低的部门的部门名称

mysql> select
    -> t.*,s.grade
    -> from
    -> (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
    -> join
    -> salgrade s
    -> on
    -> t.avgsal between s.losal and s.hisal
    -> where
    -> s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);

八、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名

1.找出普通员工的最高薪资

#找出员工代码没有出现在mgr字段上的领导的员工号
mysql> select distinct mgr from emp where mgr is not null;
#找出普通员工的最高薪资
mysql> select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
##not in在使用的时候，后面小括号中记得排除NULL！！

2.找出薪资高于1600的领导姓名

mysql> select ename,sal from emp where sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));

九、取得薪水最高的前五名员工

mysql> select ename,sal from emp order by sal desc limit 5;

十、取得薪水最高的第六到第十名员工

mysql> select ename,sal from emp order by sal desc limit 5,5;

十一、取得最后入职的 5 名员工
日期也可以升序、降序排列

mysql> select ename,hiredate from emp order by hiredate desc limit 5;

十二、取得每个薪水等级有多少员工
1.第一步：第一步：找出每个员工的薪水等级

mysql> select e.ename,e.sal,s.grade
    -> from
    -> emp e
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal

2.第二步：继续按照grade分组统计数量

mysql> select s.grade,count(*)
    -> from
    -> emp e
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal
    -> group by
    -> s.grade;

十三、面试题
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。

十四、列出所有员工及领导的姓名
自连接，左外连接

mysql> select 
    -> a.ename '员工', b.ename '领导'
    -> from
    -> emp a
    -> left join 
    -> emp b
    -> on
    -> a.mgr = b.empno;

十五、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
emp表做自连接，然后和dept表做连接。

mysql> select
    -> a.ename '员工',b.ename '领导',a.hiredate,b.hiredate,d.dname
    -> from
    -> emp a
    -> join
    -> emp b
    -> on
    -> a.mgr = b.empno
    -> join
    -> dept d
    -> on
    -> a.deptno = d.deptno
    -> where
    -> a.hiredate < b.hiredate;

十六、列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门

dept表做主表做外连接。

mysql> select e.*,d.dname
    -> from
    -> emp e
    -> right join
    -> dept d
    -> on
    -> d.deptno = e.deptno;

十七、列出至少有 5 个员工的所有部门

mysql> select
    -> deptno
    -> from
    -> emp
    -> group by
    -> deptno
    -> having
    -> count(*) >= 5;

十八、列出薪金比"SMITH" 多的所有员工信息

mysql> select
    -> ename,sal
    -> from
    -> emp
    -> where
    -> sal > (select sal from emp where ename='smith');

十九、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

分别得到两张表，然后做表连接，将部门办事员信息和部门人数信息连接。

mysql> select 
    -> t1.*,t2.deptcount 
    -> from 
    -> (select e.ename,e.job,d.dname,d.deptno from emp e join dept d on d.deptno=e.deptno where e.job='clerk') t1 
    -> join 
    -> (select deptno,count(*) as deptcount from emp group by deptno) t2 
    -> on t1.deptno = t2.deptno;

二十、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数。

按照工作岗位分组求最小值。

mysql> select job,count(*) from emp group by job having min(sal) > 1500;

二十一、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号

mysql> select ename from emp where deptno = (select deptno from dept where dname='sales');

二十二、列出薪金高于公司平均薪金的所有员工， 所在部门，上级领导， 雇员的工资等级。

三张表做连接查询。

mysql> select
    -> e.ename'员工',d.dname,I.ename '领导',s.grade
    -> from
    -> emp e
    -> join
    -> dept d
    -> on
    -> e.deptno = d.deptno
    -> left join
    -> emp I
    -> on
    -> e.mgr = I.empno
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal
    -> where
    -> e.sal > (select avg(sal) from emp);

二十三、列出与"SCOTT" 从事相同工作的所有员工及部门名称

mysql> select
    -> e.ename,e.job,d.dname
    -> from
    -> emp e
    -> join
    -> dept d
    -> on
    -> d.deptno = e.deptno
    -> where
    -> e.job = (select job from emp where ename = 'scott')
    -> and
    -> e.ename <> 'scott';  #名字不是scott的其他人

二十四、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金

mysql> select
    -> ename,sal
    -> from
    -> emp
    -> where
    -> sal in (select distinct sal from emp where deptno=30)
    -> and
    -> deptno <> 30;

二十五、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金，部门名称

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

二十六、列出在每个部门工作的员工数量, 平均工资和平均服务期限

注意有一个没有员工的部门。
计算两个日期的时间差
TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
timestampdiff(YEAR, hiredate, now())
间隔类型：SECOND 秒，MINUTE 分钟，HOUR 小时，DAY 天，WEEK 星期，MONTH 月，QUARTER 季度，YEAR 年

mysql> select
    -> d.deptno,count(e.ename) ecount,ifnull(avg(e.sal),0) avgsal,ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) avgservicetime
    -> from
    -> emp e
    -> left join
    -> dept d
    -> on
    -> e.deptno = d.deptno
    -> group by
    -> d.deptno;

二十七、列出所有员工的姓名、部门名称和工资。

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

二十八、列出所有部门的详细信息和人数

mysql> select 
    -> d.*,count(e.ename) 
    -> from 
    -> emp e 
    -> right join 
    -> dept d 
    -> on 
    -> d.deptno = e.deptno 
    -> group by 
    -> d.deptno,d.dname,d.loc;

二十九、列出各种工作的最低工资及从事此工作的雇员姓名

mysql> select e.ename,t.*
    -> from
    -> emp e
    -> join
    -> (select job,min(sal) minsal from emp group by job) t
    -> on
    -> e.job = t.job and sal = t.minsal;

三十、列出各个部门的 MANAGER( 领导) 的最低薪金

注意 where 和group by 的顺序。

mysql> select min(sal),deptno from emp where job = 'manager' group by deptno;

三十一、列出所有员工的 年工资, 按 年薪从低到高排序
年薪= （工资+津贴）*12

mysql> select
    -> ename,(sal + ifnull(comm,0))*12 as yearsal 
    -> from 
    -> emp 
    -> order by 
    -> yearsal asc;

三十二、求出员工领导的薪水超过3000的员工名称与领导

mysql> select
    -> a.ename '员工',b.ename '领导'
    -> from
    -> emp a 
    -> left join 
    -> emp b 
    -> on 
    -> a.mgr=b.empno 
    -> where 
    -> b.sal >3000;

三十三、求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数（*）

mysql> select
    -> d.dname,count(e.ename),ifnull(sum(e.sal),0) sumsal
    -> from
    -> emp e
    -> right join
    -> dept d
    -> on
    -> e.deptno=d.deptno
    -> where 
    -> d.dname like '%s%'
    -> group by
    -> d.dname;

三十四、给任职日期超过 30 年的员工加薪 10%.

mysql> update emp set sal = sal*1.1 where timestampdiff(year,hiredate,now())>30;