MySQL测试

最新推荐文章于 2024-09-07 15:29:09 发布

CoolEr3022

最新推荐文章于 2024-09-07 15:29:09 发布

阅读量504

点赞数

分类专栏：笔记文章标签： mysql 数据库 dba

本文链接：https://blog.csdn.net/weixin_56341892/article/details/122540173

版权

这篇博客主要探讨了MySQL在笔试和面试中的常见问题，包括查询最高薪水、平均薪水、部门信息、员工关系、日期运算等复杂SQL操作。在面试部分，通过解决涉及学生、课程和选课表的实际问题，展示了如何使用SQL进行多表联查和条件筛选。

摘要由CSDN通过智能技术生成

MySQL测试

一、笔试

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

mysql> select e.ename,t.*
    ->
    -> from (select deptno,max(sal) as maxsal from emp group by deptno) t
    ->
    -> join emp e
    ->
    -> on e.deptno = t.deptno and t.maxsal = e.sal;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| KING  |     10 | 5000.00 |
| SCOTT |     20 | 3000.00 |
| FORD  |     20 | 3000.00 |
| BLAKE |     30 | 2850.00 |
+-------+--------+---------+

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

mysql> select e.ename,e.sal,t.*
    -> 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;
+-------+---------+--------+-------------+
| ename | sal     | deptno | avgsal      |
+-------+---------+--------+-------------+
| KING  | 5000.00 |     10 | 2916.666667 |
| JONES | 2975.00 |     20 | 2175.000000 |
| SCOTT | 3000.00 |     20 | 2175.000000 |
| FORD  | 3000.00 |     20 | 2175.000000 |
| ALLEN | 1600.00 |     30 | 1566.666667 |
| BLAKE | 2850.00 |     30 | 1566.666667 |
+-------+---------+--------+-------------+

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

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

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

第一种：limit的使用
```
mysql> select sal from emp order by sal desc limit 0,1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
```
第二种：表的自连接

a表和b表数据一样，a表中最高工资无论如何都不会小于b表中的任何一个数据，因此最高工资一定会被排除，此时用上not in正好将筛出去的最高工资选出来。
```
mysql> select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
```

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

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

求平均薪水最低的部门的部门名称

 select d.dname from (select deptno,avg(sal) as avgsal
    ->
    -> from emp group by deptno
    ->
    -> having avgsal = (select min(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t)) a
    ->
    -> join dept d
    ->
    -> on d.deptno = a.deptno;
+-------+
| dname |
+-------+
| SALES |
+-------+

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

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));
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

取薪水最高的前五名员工

mysql> 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 |
+-------+---------+

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

mysql>  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 |
+--------+---------+

取最后入职的五名员工

mysql> 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 |
+--------+------------+