Mysql笔记（4）-- 题目练习

注：如果我的方法旁边写了同，表明和答案的方法是一样的，

1、取得每个部门最高薪水的人员名称

@@@@@@@@

我的思考：
法一同
1.找出每个部门的最高薪水：
mysql> select deptno, max(sal) from emp  group by  deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     30 |  2850.00 |
|     10 |  5000.00 |
+--------+----------+
3 rows in set (0.01 sec)
2.由上面的信息得到人员名字：
mysql>  select deptno, max(sal),ename from emp  group by  deptno;
ERROR 1055 (42000): Expression #3 of SELECt list is not in GROUP BY clause and contains nonaggregated column 'bjpowernode.emp.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
//当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段，所以不能有ename。

因为想找ename要用到1中的表同时还要用到别的表（emp表），所以可以将1中的表当作临时表t，用子查询。
mysql> select t.*,e.ename from (select deptno, max(sal) maxsal from emp  group by  deptno) t join emp e on t.maxsal=e.sal;
+--------+---------+-------+
| deptno | maxsal  | ename |
+--------+---------+-------+
|     30 | 2850.00 | BLAKE |
|     20 | 3000.00 | SCOTT |
|     10 | 5000.00 | KING  |
|     20 | 3000.00 | FORD  |
+--------+---------+-------+
4 rows in set (0.00 sec)

我觉得这样还是有点不严谨，因为只用maxsal和sal对比的话，如：2850是部门30的maxsal，但万一也是部门20中的小张的薪资，则可能会把小张也查出来，但是小张并不是其部门的最高薪资。
所以我觉得要再核对一下部门编号。修改如下：
mysql> select t.*,e.ename from (select deptno, max(sal) maxsal from emp  group by  deptno) t join emp e on t.maxsal=e.sal where e.deptno=t.deptno;
+--------+---------+-------+
| deptno | maxsal  | ename |
+--------+---------+-------+
|     30 | 2850.00 | BLAKE |
|     20 | 3000.00 | SCOTT |
|     10 | 5000.00 | KING  |
|     20 | 3000.00 | FORD  |
+--------+---------+-------+
4 rows in set (0.00 sec)

（我的考虑是对的，但是我用了on和where两个筛选来分别放薪资和部门的条件。答案是直接用and将两个条件相连放在on中，没用where）

@@@@@@@@

第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组最大值)
mysql> select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
第二步：将以上的查询结果当做一张临时表t，
t和emp表连接，条件：t.deptno = e.deptno and t.maxsal = e.sal
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 t.maxsal = e.sal;

+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

2、哪些人的薪水在部门的平均薪水之上

@@@@@@@@

我的思考：
法一同
1找出部门平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

2找出每个部门里高于该部门平均薪水的人
select e.ename,e.sal,t.* 
from emp e 
join (select deptno,avg(sal) avgsal from emp group by deptno) t 
on e.deptno=t.deptno and e.sal>t.avgsal; 
+-------+---------+--------+-------------+
| ename | sal     | deptno | avgsal      |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 |     30 | 1566.666667 |
| JONES | 2975.00 |     20 | 2175.000000 |
| BLAKE | 2850.00 |     30 | 1566.666667 |
| SCOTT | 3000.00 |     20 | 2175.000000 |
| KING  | 5000.00 |     10 | 2916.666667 |
| FORD  | 3000.00 |     20 | 2175.000000 |
+-------+---------+--------+-------------+
6 rows in set (0.00 sec)
成功，和第一题一样的思路

@@@@@@@@

第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
第二步：将以上查询结果当做t表，t和emp表连接
条件：部门编号相同，并且emp的sal大于t表的avgsal
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;
+--------+-------------+-------+---------+
| deptno | avgsal      | ename | sal     |
+--------+-------------+-------+---------+
|     30 | 1566.666667 | ALLEN | 1600.00 |
|     20 | 2175.000000 | JONES | 2975.00 |
|     30 | 1566.666667 | BLAKE | 2850.00 |
|     20 | 2175.000000 | SCOTT | 3000.00 |
|     10 | 2916.666667 | KING  | 5000.00 |
|     20 | 2175.000000 | FORD  | 3000.00 |
+--------+-------------+-------+---------+

3、取得部门中（所有人的）平均的薪水等级

@@@@@@@@

我的思考：
题目意思是：求每个部门的员工的薪水等级的平均值
法一同
1求每个人的薪水等级
select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+--------+-------+
| ename  | deptno | grade |
+--------+--------+-------+
| SMITH  |     20 |     1 |
| ALLEN  |     30 |     3 |
| WARD   |     30 |     2 |
| JONES  |     20 |     4 |
| MARTIN |     30 |     2 |
| BLAKE  |     30 |     4 |
| CLARK  |     10 |     4 |
| SCOTT  |     20 |     4 |
| KING   |     10 |     5 |
| TURNER |     30 |     3 |
| ADAMS  |     20 |     1 |
| JAMES  |     30 |     1 |
| FORD   |     20 |     4 |
| MILLER |     10 |     2 |
+--------+--------+-------+
14 rows in set (0.00 sec)


2 按照部门分组，找出等级的平均值，这里不需要用临时表，因为我们要的信息就在上面那张表上，和其他表无关了，只用当作普通的查询即可。对上表分组求均值即可
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;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)
成功

@@@@@@@@

平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。

平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

第一步：找出每个人的薪水等级
emp e和salgrade s表连接。
连接条件：e.sal between s.losal and s.hisal

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

+--------+---------+--------+-------+
| ename  | sal     | deptno | grade |
+--------+---------+--------+-------+
| CLARK  | 2450.00 |     10 |     4 |
| KING   | 5000.00 |     10 |     5 |
| MILLER | 1300.00 |     10 |     2 |

| SMITH  |  800.00 |     20 |     1 |
| ADAMS  | 1100.00 |     20 |     1 |
| SCOTT  | 3000.00 |     20 |     4 |
| FORD   | 3000.00 |     20 |     4 |
| JONES  | 2975.00 |     20 |     4 |

| MARTIN | 1250.00 |     30 |     2 |
| TURNER | 1500.00 |     30 |     3 |
| BLAKE  | 2850.00 |     30 |     4 |
| ALLEN  | 1600.00 |     30 |     3 |
| JAMES  |  950.00 |     30 |     1 |
| WARD   | 1250.00 |     30 |     2 |
+--------+---------+--------+-------+

第二步：基于以上的结果继续按照deptno分组，求grade的平均值。
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;

+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+

4、不准用组函数（Max ），取得最高薪水;

@@@@@@@@

我的思考：
法一同
将所有人的sal按照降序排列，第一行的就是最高sal
select * from emp order by sal desc limit 0,1;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)
成功（不过用这种方法有个缺点：如果有好几个都是5000，那你就不能这样只取第一行的数据了）
我再改进下：
select * from emp where sal=(select sal from emp order by sal desc limit 0,1);
这样就解决了上面的问题。成功。




但除了排序我没想到别的办法，然后我看答案还可以用表的自连接，我先试试：
select a.* 
from emp a 
join emp b 
on a.sal 
@@@@@@@@ 
第一种：sal降序，limit 1 
（（不过用这种方法有个缺点：如果有好几个都是5000，那你就不能这样只取第一行的数据了）我的方法再次基础上改进了一下）
select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
 
第二种方案：select max(sal) from emp; 
第三种方案：表的自连接 
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);
（我没想起distinct和not in）

+---------+
| sal     |
+---------+
| 5000.00 |
+---------+



a表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+

b表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+
 
5、取得平均薪水最高的部门的部门编号; 
@@@@@@@@ 
我的思考：
法一：同
1.按照部门分组得到各部门平均薪水

select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
2.按照avgsal降序排，取第一行的：
select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc limit 0,1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)
成功，当然也可以想上一题我的思考中那样再完善下“有多个最高”的情况，在此我不写了。

法二：max：
select deptno,max(avg(sal)) from emp group by deptno:
ERROR 1111 (HY000): Invalid use of group function
注意：出错了，分组函数1(分组函数2())，mysql中不可以这样使用，而Oracle中可以这样使用

mysql> select max(avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) ;
ERROR 1248 (42000): Every derived table must have its own alias
注意：仍然有问题！必须要像下面写的这样重命名一下才对！！！别忘了！！
select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
+---------------+
| max(t.avgsal) |
+---------------+
|   2916.666667 |
+---------------+
1 row in set (0.00 sec)
还要加上对应的部门名字，于是我多加一个字段t.deptno：
select t.deptno,max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECt list contains nonaggregated column 't.deptno'; this is incompatible with sql_mode=only_full_group_by
“取最大平均薪水，这个时候没有用字段进行分组，所以只能使用分组函数，而取不出来deptno”
但此时竟然报错了，进行下面实验：
mysql> select t.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
+-------------+
| avgsal      |
+-------------+
| 2175.000000 |
| 1566.666667 |
| 2916.666667 |
+-------------+
3 rows in set (0.00 sec)
mysql> select  t.deptno from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
|     10 |
+--------+
mysql>select t.deptno,max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECt list contains nonaggregated column 't.deptno'; this is incompatible with sql_mode=only_full_group_by
没有group by的情况下，查单独字段没问题，查单独分组函数也没问题，两个放在一起以后就出问题了，
错误 1140 （42000）：在没有 GROUP BY 的聚合查询中，SELECT 列表的表达式 #1 包含非聚合列“t.deptno”;这与 sql_mode=only_full_group_by 不兼容
那我试试如果是正常的表，不是临时表是否可以：
select e.ename,max(e.sal) from emp e;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECt list contains nonaggregated column 'bjpowernode.e.ENAME'; this is incompatible with sql_mode=only_full_group_by
哦，同样是不可以的！那我怎么印象中之前学的时候是写过类似上面这行命令的？我找一找，确实没找到想这里一样写的，只找到唯一一个类似的也是写了group by的，3.3、from后面嵌套子查询中的（select avg(t.grade),deptno from (select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal)  t group by t.deptno;），不过这个句子是我自己写的，其实当时那里根本不用“临时表”。
加入group by试试
select t.deptno,max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t group by t.deptno;
+--------+---------------+
| deptno | max(t.avgsal) |
+--------+---------------+
|     20 |   2175.000000 |
|     30 |   1566.666667 |
|     10 |   2916.666667 |
+--------+---------------+
3 rows in set (0.01 sec)
奇怪，应该只显示一条记录啊，它这个和临时表t的结果一模一样
我想了想，这样确实不对，t.deptno有3个数据，而max(t.avgsal)只有一个数据，如果没有筛选条件让t.deptno只留下一个的话是无法形成表格的

我再试验一下，对正常的表添加一个筛选语句试试：
mysql> select e.ename,max(e.sal) from emp e where ename='smith';
+-------+------------+
| ename | max(e.sal) |
+-------+------------+
| SMITH |     800.00 |
+-------+------------+
1 row in set (0.00 sec)
没有报错，但是数据并不正确，输出的是Smith的sal，max函数没有起作用。因为是先执行where再分组再分组函数的，执行了where以后其实只有一条smith相关的数据在表格中了，此时smith的sal800就是表格中的最大数据了。所以我们看到了上面的输出结果。试试having：
select t.deptno,t.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t having t.avgsal=max(t.avgsal);
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECt list contains nonaggregated column 't.deptno'; this is incompatible with sql_mode=only_full_group_by
也不行。

于是我明白了，要遵循这样的规则：
1.没有group by的情况下，可以单独查字段，也可以单独查分组函数，但是不能同时查字段和分组函数。
2.如果同时查字段和分组函数，则要求必须group by要查的字段。（也就是之前说如果有group by则select里面只能写用来分组的字段和分组函数）

我重新写一下：
之前已经得到这个结果了；
select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
+---------------+
| max(t.avgsal) |
+---------------+
|   2916.666667 |
+---------------+
1 row in set (0.00 sec)
select t.deptno,t.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t where t.avgsal=(select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t);
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------
结果是对的，但是不好，太复杂了，看答案

@@@@@@@@
 
第一种方案：降序取第一个。 
第一步：找出每个部门的平均薪水
	select deptno,avg(sal) as avgsal from emp group by deptno;
	+--------+-------------+
	| deptno | avgsal      |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	|     30 | 1566.666667 |
	+--------+-------------+
第二步：降序选第一个。
	select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
	+--------+-------------+
	| deptno | avgsal      |
	+--------+-------------+
	|     10 | 2916.666667 |
	+--------+-------------+
 
第二种方案：max 
第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：找出以上结果中avgsal最大的值。
select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
+---------------+
| max(t.avgsal) |
+---------------+
|   2916.666667 |
+---------------+

第三步：
select 
	deptno,avg(sal) as avgsal 
from 
	emp 
group by 
	deptno
having
	avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
 
6、取得平均薪水最高的部门的部门名称; 
@@@@@@@@ 
我的思考：
同
select d.dname,avg(e.sal) avgsal 
from emp e 
join dept d 
on e.deptno =d.deptno 
group by dname 
order by avgsal desc  
limit 0,1; 
+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
1 row in set (0.01 sec)
 
@@@@@@@@ 
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;

+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
 
7、求平均薪水的等级最低的部门的部门名称; 
@@@@@@@@ 
我的思考：
法一：只适用于只有一个最低值
1.按照部门分组，求每个部门的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
2.求出平均薪水的“等级“
select t.*,s.grade 
from (select deptno,avg(sal) avgsal from emp group by deptno) t 
join salgrade s 
on t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

3.limit排序取出等级最低的（只适用于只有一个最低值）
select t.*,s.grade 
from (select deptno,avg(sal) avgsal from emp group by deptno) t 
join salgrade s 
on t.avgsal between s.losal and s.hisal 
order by s.grade  
limit 0,1;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+
1 row in set (0.00 sec)
4.join dept表显示部门名称
select t.*,s.grade,d.dname  
from (select deptno,avg(sal) avgsal from emp group by deptno) t  
join salgrade s  
on t.avgsal between s.losal and s.hisal  
join dept d  
on t.deptno=d.deptno 
order by s.grade   
limit 0,1  ;

+--------+-------------+-------+-------+
| deptno | avgsal      | grade | dname |
+--------+-------------+-------+-------+
|     30 | 1566.666667 |     3 | SALES |
+--------+-------------+-------+-------+
1 row in set (0.01 sec)
成功，但是用limit这样做只针对只有一条最低记录的情况，如果最低记录有多条就不能这样了。

法二：要求考虑最低记录有多条的情况
1.2.还是同法一一样
1.按照部门分组，求每个部门的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
2.求出平均薪水的“等级“
select t.*,s.grade 
from (select deptno,avg(sal) avgsal from emp group by deptno) t 
join salgrade s 
on t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)
 
3.加入显示部门名字(就是法一的4，但没加limit和order by )
select t.*,s.grade,d.dname 
from (select deptno,avg(sal) avgsal from emp group by deptno) t  
join salgrade s  
on t.avgsal between s.losal and s.hisal 
join dept d  
on t.deptno=d.deptno  ;

+--------+-------------+-------+------------+
| deptno | avgsal      | grade | dname      |
+--------+-------------+-------+------------+
|     30 | 1566.666667 |     3 | SALES      |
|     20 | 2175.000000 |     4 | RESEARCH   |
|     10 | 2916.666667 |     4 | ACCOUNTING |
+--------+-------------+-------+------------+
3 rows in set (0.00 sec)
4.考虑怎么把所有的最低记录找出来
select t.*,s.grade,d.dname 
from (select deptno,avg(sal) avgsal from emp group by deptno) t  
join salgrade s  
on t.avgsal between s.losal and s.hisal 
join dept d  
on t.deptno=d.deptno  
where s.grade = (select min(grade) from 这张表)	我觉得这样很麻烦，就没写了。看答案
 
@@@@@@@@ 
平均薪水是800
平均薪水是900
那么他俩都是1级别。

第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：找出每个部门的平均薪水的等级
以上t表和salgrade表连接，条件：t.avgsal between s.losal and s.hisal

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;

+------------+-------------+-------+
| dname      | avgsal      | grade |
+------------+-------------+-------+
| SALES      | 1566.666667 |     3 |
| ACCOUNTING | 2916.666667 |     4 |
| RESEARCH   | 2175.000000 |     4 |
+------------+-------------+-------+
3.找出最低的薪资等级，然后将所有等于这个“最低薪资等级“的都筛出来
平均薪水最低的对应的等级一定是最低的.
select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
+-------------+
| avgsal      |
+-------------+
| 1566.666667 |
+-------------+

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;
+-------+
| grade |
+-------+
|     3 |
+-------+

根据3来筛
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);

+-------+-------------+-------+
| dname | avgsal      | grade |
+-------+-------------+-------+
| SALES | 1566.666667 |     3 |
+-------+-------------+-------+
 
8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名； 
@@@@@@@@ 
我的思考
先把所有普通员工都找出来，然后找到普通员工的最高薪水maxsal，找出所有不在普通元表中的并且工资大于maxsal的人即可
mysql> select ename,sal,empno from emp where empno not in mgr;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mgr' at line 1
不可以这样写，not in（特别注意not in结果集中不能有null，如果not in结果集中有Null，则查询结果直接为Null）不能直接跟这种字段呀，它和>,=,<这些一样，跟的是一个值，所以我们可以用select将mgr都选出来，而不是直接写这个字段：

select ename,sal,empno from emp where empno not in (select mgr from emp);
Empty set (0.00 sec)//得到的是一个空集合，为什么呢？因为mgr中有NULL，而对于not in来说，只要有NULL，查询结果直接为NULL
所以我要换个思路，不查普通员工了，查领导，得到所有领导的名字：
select ename,sal,empno from emp where empno  in (select mgr from emp);
+-------+---------+-------+
| ename | sal     | empno |
+-------+---------+-------+
| FORD  | 3000.00 |  7902 |
| BLAKE | 2850.00 |  7698 |
| KING  | 5000.00 |  7839 |
| JONES | 2975.00 |  7566 |
| SCOTT | 3000.00 |  7788 |
| CLARK | 2450.00 |  7782 |
+-------+---------+-------+
6 rows in set (0.00 sec)

现在要找出普通员工的最高薪水：
select max(e.sal) from emp e join (select ename,sal,empno from emp where empno  in (select mgr from emp)) t on e.ename not in (select ename from t);
ERROR 1146 (42S02): Table 'bjpowernode.t' doesn't exist
行不通耶。select ename from t的t不被认可，如果再写一个的话就太麻烦了。
那我试试找普通员工表，用ifnull把null除去试试看？但也不行。


select max(e.sal) from emp e join (select ename,sal,empno from emp where empno  in (select mgr from emp)) t on e.ename !=t.ename;(不行，这个条件还是会得到所有人的名字)



换思路：	不用not in 用自连接试试：
distinct只能出现在所有字段的最前面。表示distinct后面的所有字段联合起来去除重复的记录。
1.找普通员工的表并按照sal降序排序：
select distinct a.ename,a.sal 
from emp a 
join emp b 
on a.empno != b.mgr 
order by a.sal desc;
（不对，因为这样实际上是得到了所有员工的名字）
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+
14 rows in set (0.00 sec)

还是要反向找领导的名字：
select distinct a.ename,a.sal 
from emp a 
join emp b 
on a.empno = b.mgr 
order by a.sal desc;
（这是对的）
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
+-------+---------+
6 rows in set (0.00 sec)
 
2.找出普通员工的最高薪资：
这步又回到了之前有问题的地方了，看答案，其实我想到了NULL的问题，但我想偏了，应该用is not  null而不是ifnull，并且这部分也不需要像我那样用临时表
 
@@@@@@@@ 
比“普通员工的最高薪水”还要高的一定是领导！
	没毛病！！！！

mysql> select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
员工编号没有在以上范围内的都是普通员工。（这就是领导表，我自己想的太复杂了。实际上MGR就是上级领导呀，只要用distinct选出来就好。。）

第一步：找出普通员工的最高薪水！
not in在使用的时候，后面小括号中记得排除NULL。
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+

第二步：找出高于1600的
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));
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
 
9、取得薪水最高的前五名员工 
select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
 
10、取得薪水最高的第六到第十名员工 
select ename,sal from emp order by sal desc limit 5, 5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+
 
11、取得最后入职的 5 名员工 
日期也可以降序，升序。 
select ename,hiredate from emp order by hiredate desc limit 5;

+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| FORD   | 1981-12-03 |
| JAMES  | 1981-12-03 |
+--------+------------+
 
12、取得每个薪水等级有多少员工; 
分组count 
第一步：找出每个员工的薪水等级
 select
 e.ename,e.sal,s.grade
 from
 emp e
 join
 salgrade s
 on
 e.sal between s.losal and s.hisal;
 ±-------±--------±------+
 | ename | sal | grade |
 ±-------±--------±------+
 | SMITH | 800.00 | 1 |
 | ALLEN | 1600.00 | 3 |
 | WARD | 1250.00 | 2 |
 | JONES | 2975.00 | 4 |
 | MARTIN | 1250.00 | 2 |
 | BLAKE | 2850.00 | 4 |
 | CLARK | 2450.00 | 4 |
 | SCOTT | 3000.00 | 4 |
 | KING | 5000.00 | 5 |
 | TURNER | 1500.00 | 3 |
 | ADAMS | 1100.00 | 1 |
 | JAMES | 950.00 | 1 |
 | FORD | 3000.00 | 4 |
 | MILLER | 1300.00 | 2 |
 ±-------±--------±------+ 
第二步：继续按照grade分组统计数量(这里的count用法学一下)
 select
 s.grade ,count(*)
 from
 emp e
 join
 salgrade s
 on
 e.sal between s.losal and s.hisal
 group by
 s.grade; 
±------±---------+
 | grade | count(*) |
 ±------±---------+
 | 1 | 3 |
 | 2 | 3 |
 | 3 | 2 |
 | 4 | 5 |
 | 5 | 1 |
 ±------±---------+ 
13、面试题; 
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
 S（SNO，SNAME）代表（学号，姓名）
 C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
 SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
 问题：
 1，找出没选过“黎明”老师的所有学生姓名。
 2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
 3，即学过 1 号课程又学过 2 号课所有学生的姓名。
 请用标准sql语句写出答案，方言也行（请说明是使用什么方言） 
14、列出所有员工及领导的姓名; 
@@@@@@@@ 
我的思考
select a.ename  'YG ',b.ename 'LD' from emp a left join emp b on a.mgr=b.empno;
+--------+-------+
| YG     | LD    |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)
 
@@@@@@@@ 
select 
	a.ename '员工', b.ename '领导' 
from 
	emp a 
left join 
	emp b 
on 
	a.mgr = b.empno; 

+--------+-------+
| 员工   | 领导    |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
 
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称 
emp a 员工表
emp b 领导表
a.mgr = b.empno and a.hiredate < b.hiredate

select 
	a.ename '员工', a.hiredate, b.ename '领导', 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;

+-------+------------+-------+------------+------------+
| 员工     | hiredate   | 领导    | hiredate   | dname      |
+-------+------------+-------+------------+------------+
| CLARK | 1981-06-09 | KING  | 1981-11-17 | ACCOUNTING |
| SMITH | 1980-12-17 | FORD  | 1981-12-03 | RESEARCH   |
| JONES | 1981-04-02 | KING  | 1981-11-17 | RESEARCH   |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES      |
| WARD  | 1981-02-22 | BLAKE | 1981-05-01 | SALES      |
| BLAKE | 1981-05-01 | KING  | 1981-11-17 | SALES      |
+-------+------------+-------+------------+------------+
 
16、 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门 
select 
	e.*,d.dname
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno;

+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | RESEARCH   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | SALES      |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | SALES      |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
 
17、列出至少有 5 个员工的所有部门 
按照部门编号分组，计数，筛选出 >= 5

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

+--------+
| deptno |
+--------+
|     20 |
|     30 |
+--------+

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

select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
 
19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数 
select ename,job from emp where job = 'CLERK';
+--------+-------+
| ename  | job   |
+--------+-------+
| SMITH  | CLERK |
| ADAMS  | CLERK |
| JAMES  | CLERK |
| MILLER | CLERK |
+--------+-------+

select 
	e.ename,e.job,d.dname
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
where 
	e.job = 'CLERK';

+--------+-------+------------+
| ename  | job   | dname      |
+--------+-------+------------+
| MILLER | CLERK | ACCOUNTING |
| SMITH  | CLERK | RESEARCH   |
| ADAMS  | CLERK | RESEARCH   |
| JAMES  | CLERK | SALES      |
+--------+-------+------------+

select 
	e.ename,e.job,d.dname,d.deptno
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
where 
	e.job = 'CLERK';

+--------+-------+------------+--------+
| ename  | job   | dname      | deptno |
+--------+-------+------------+--------+
| MILLER | CLERK | ACCOUNTING |     10 |
| SMITH  | CLERK | RESEARCH   |     20 |
| ADAMS  | CLERK | RESEARCH   |     20 |
| JAMES  | CLERK | SALES      |     30 |
+--------+-------+------------+--------+


//每个部门的人数？
select deptno, count(*) as deptcount from emp group by deptno;
+--------+-----------+
| deptno | deptcount |
+--------+-----------+
|     10 |         3 |
|     20 |         5 |
|     30 |         6 |
+--------+-----------+

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

+--------+-------+------------+--------+-----------+
| ename  | job   | dname      | deptno | deptcount |
+--------+-------+------------+--------+-----------+
| MILLER | CLERK | ACCOUNTING |     10 |         3 |
| SMITH  | CLERK | RESEARCH   |     20 |         5 |
| ADAMS  | CLERK | RESEARCH   |     20 |         5 |
| JAMES  | CLERK | SALES      |     30 |         6 |
+--------+-------+------------+--------+-----------+
 
20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数 
按照工作岗位分组求最小值。
select job,count(*) from emp group by job having min(sal) > 1500;

+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+
 
21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号. 
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');

+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
 
±-------+ 
22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级. 
select 
	e.ename '员工',d.dname,l.ename '领导',s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
left join
	emp l
on
	e.mgr = l.empno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
where
	e.sal > (select avg(sal) from emp);


+-------+------------+-------+-------+
| 员工     | dname      | 领导    | grade |
+-------+------------+-------+-------+
| JONES | RESEARCH   | KING  |     4 |
| BLAKE | SALES      | KING  |     4 |
| CLARK | ACCOUNTING | KING  |     4 |
| SCOTT | RESEARCH   | JONES |     4 |
| KING  | ACCOUNTING | NULL  |     5 |
| FORD  | RESEARCH   | JONES |     4 |
+-------+------------+-------+-------+
 
23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称 
select job from emp where ename = 'SCOTT';
+---------+
| job     |
+---------+
| ANALYST |
+---------+

select 
	e.ename,e.job,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
where
	e.job = (select job from emp where ename = 'SCOTT')
and
	e.ename <> 'SCOTT';

+-------+---------+----------+
| ename | job     | dname    |
+-------+---------+----------+
| FORD  | ANALYST | RESEARCH |
+-------+---------+----------+
 
24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金. 
select distinct sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+

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

Empty set (0.00 sec)
 
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称 
select max(sal) from emp where deptno = 30;
+----------+
| max(sal) |
+----------+
|  2850.00 |
+----------+

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

+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| KING  | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+
 
26、列出在每个部门工作的员工数量, 平均工资和平均服务期限 
没有员工的部门，部门人数是0

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

+--------+--------+-------------+----------------+
| deptno | ecount | avgsal      | avgservicetime |
+--------+--------+-------------+----------------+
|     10 |      3 | 2916.666667 |        38.0000 |
|     20 |      5 | 2175.000000 |        35.8000 |
|     30 |      6 | 1566.666667 |        38.3333 |
|     40 |      0 |    0.000000 |         0.0000 |
+--------+--------+-------------+----------------+

在mysql当中怎么计算两个日期的“年差”，差了多少年？
	TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
	
	timestampdiff(YEAR, hiredate, now())

	间隔类型：
		SECOND   秒，
		MINUTE   分钟，
		HOUR   小时，
		DAY   天，
		WEEK   星期
		MONTH   月，
		QUARTER   季度，
		YEAR   年
 
27、 列出所有员工的姓名、部门名称和工资。 
select 
	e.ename,d.dname,e.sal
from
	emp e
join 
	dept d
on
	e.deptno = d.deptno;

+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| CLARK  | ACCOUNTING | 2450.00 |
| KING   | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH  | RESEARCH   |  800.00 |
| JONES  | RESEARCH   | 2975.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| FORD   | RESEARCH   | 3000.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| TURNER | SALES      | 1500.00 |
| JAMES  | SALES      |  950.00 |
+--------+------------+---------+
 
28、列出所有部门的详细信息和人数 
select 
	d.deptno,d.dname,d.loc,count(e.ename)
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno,d.dname,d.loc;

+--------+------------+----------+----------------+
| deptno | dname      | loc      | count(e.ename) |
+--------+------------+----------+----------------+
|     10 | ACCOUNTING | NEW YORK |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
+--------+------------+----------+----------------+
 
29、列出各种工作的最低工资及从事此工作的雇员姓名 
select 
	job,min(sal) as minsal
from
	emp
group by
	job;

+-----------+----------+
| job       | minsal		|
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |   800.00 |
| MANAGER   |  2450.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1250.00 |
+-----------+----------+

emp e和以上t连接

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

+--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+
 
30、列出各个部门的 MANAGER( 领导) 的最低薪金 
select 
	deptno, min(sal)
from
	emp
where
	job = 'MANAGER'
group by
	deptno;

+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  2450.00 |
|     20 |  2975.00 |
|     30 |  2850.00 |
+--------+----------+
 
31、列出所有员工的 年工资, 按 年薪从低到高排序 
select 
	ename,(sal + ifnull(comm,0)) * 12 as yearsal
from
	emp
order by
	yearsal asc;

+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| FORD   | 36000.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
+--------+----------+
 
32、求出员工领导的薪水超过3000的员工名称与领导 
select 
	a.ename '员工',b.ename '领导'
from
	emp a
join
	emp b
on
	a.mgr = b.empno
where
	b.sal > 3000;

+-------+------+
| 员工  | 领导   |
+-------+------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+------+
 
33、求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数 
select 
	d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	d.dname like '%S%'
group by
	d.deptno,d.dname,d.loc;

+--------+------------+---------+----------------+----------+
| deptno | dname      | loc     | count(e.ename) | sumsal   |
+--------+------------+---------+----------------+----------+
|     20 | RESEARCH   | DALLAS  |              5 | 10875.00 |
|     30 | SALES      | CHICAGO |              6 |  9400.00 |
|     40 | OPERATIONS | BOSTON  |              0 |     0.00 |
+--------+------------+---------+----------------+----------+
 
34、给任职日期超过 30 年的员工加薪 10%. 
update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;

Mysql笔记（4）-- 题目练习

Java相关栏目本月热门文章