MySQL基础入门【#】习题篇1-17

---

MySQL下载请查看：MySQL基础入门【1】下载与使用

Part1笔记：MySQL基础入门【2】

Part2笔记：MySQL基础入门【3】

---

动力节点入门视频链接：MySQL基础入门-mysql教程-数据库实战（MySQL基础+MySQL高级+MySQL优化+MySQL34道作业题）

数据及笔记下载链接：百度云链接
提取码：0r3s

---

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

第一题就不会做我好牛。

要求得到的结果如下图所示：

第一个想法很简单

select ename,sal,max(sal) from emp group by deptno;
# ERROR 1055 (42000): Expression #1 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

select 之后只能跟分组函数或者group by的对象。回顾11分组函数。

attempt 2

select ename, sal, deptno from emp where sal = (select max(sal) from emp group by deptno);
 select ename, sal deptno from emp group by deptno having sal = max(sal);
 select e.ename, (select d.max(sal), d.deptno from emp d group by d.deptno where e.ename = d.ename) from emp e;
 #全错，大概想到要用子查询和连接了，但是没写对

正确答案

# 第一步先查出每个部门的最高薪水，再将它当作临时表t
# 第一步将emp表和t表以sal和deptno两个键进行内连接。
select 
e.ename,t.*
from 
emp e
join 
(select max(sal) as maxsal, deptno from emp group by deptno) t
on
e.sal = t.maxsal and e.deptno = t.deptno;
+-------+---------+--------+
| ename | maxsal  | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

02 那些人的薪水在部门的平均薪水之上

感觉这道题的思路和上一题比较像，也是先得到平均薪水，然后进行一个join

select e.ename, e.sal
    -> from 
    -> (select avg(sal) as avgsal, deptno from emp group by deptno) t
    -> join 
    -> emp e
    -> on 
    -> e.sal > t.avgsal and e.deptno = t.deptno;
    ->order by e.sal;
+-------+---------+
| ename | sal     |
+-------+---------+
| ALLEN | 1600.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
+-------+---------+
6 rows in set (0.00 sec)

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

这题已经练习过了～不需要建立临时表

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
    -> order by 
    -> e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+

04 不准用组函数，取得最高薪水

select sal from emp order by sal desc limit 1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

表的自连接，神奇的方法，将emp表进行自连，找出小的值，因为5000不比其他数小，所以没有被选择出来。

select 
sal
from 
emp
where 
sal 
not in 
(select distinct a.sal from emp a 
join emp b on a.sal < b.sal);

05 取得平均薪水最高的部门编号（两种方法）

第一种方法使用limit

select t.deptno from  (select avg(sal) as avgsal, deptno from emp group by deptno order by avgsal desc) t limit 1;

+--------+
| deptno |
+--------+
|     10 |
+--------+

第二种方法使用max分组函数

select max(t.avgsal), t.deptno from (