【B站老杜】习题详解

最新推荐文章于 2024-10-07 21:00:41 发布
最新推荐文章于 2024-10-07 21:00:41 发布
阅读量489 收藏 2
点赞数
分类专栏： # MySQL 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_39236499/article/details/121787906
版权

文章目录

【1】此博客整理自B站老杜34道作业题；视频链接：https://www.bilibili.com/video/BV1Vy4y1z7EX?p=132
【2】关于B站老杜的MySQL配套文档获取链接：https://pan.baidu.com/s/1mH5nMMv1YWLtd4xromvnsA
【3】关于本篇博客习题详解的电子文档获取链接：https://pan.baidu.com/s/1EXNV5In7mkuoSvpeMiSvpg
【4】关于百度网盘的提取码：请关注VX公宗浩zdb呀后台回复关键字提取码获取
【5】整理不易，如链接失效，请联系本人


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

步骤一：先查询每个部门的最高工资

mysql> select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
3 rows in set (0.00 sec)

步骤二：把上面查询结果当成一张表，查询符合上表情况的人

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 |
+-------+--------+---------+
4 rows in set (0.00 sec)

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

步骤一：查询每个部门的平均薪资

mysql> select deptno,avg(sal) as avg_sal from emp group by deptno;
+--------+-------------+
| deptno | avg_sal     |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

步骤二：获取大于平均薪资的人

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

+-------+---------+
| ename | sal     |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

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

步骤一：获取每个人的薪资等级

mysql> select e.deptno,e.ename, s.grade
    -> from emp e
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal;
+--------+--------+-------+
| deptno | ename  | grade |
+--------+--------+-------+
|     20 | SMITH  |     1 |
|     30 | ALLEN  |     3 |
|     30 | WARD   |     2 |
|     20 | JONES  |     4 |
|     30 | MARTIN |     2 |
|     30 | BLAKE  |     4 |
|     10 | CLARK  |     4 |
|     20 | SCOTT  |     4 |
|     10 | KING   |     5 |
|     30 | TURNER |     3 |
|     20 | ADAMS  |     1 |
|     30 | JAMES  |     1 |
|     20 | FORD   |     4 |
|     10 | MILLER |     2 |
+--------+--------+-------+
14 rows in set (0.01 sec)

步骤二：基于上述结果分组

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 |
+--------+--------------+
3 rows in set (0.00 sec)

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

方法一：desc降序排序，limit显示第一条数据

mysql> select sal from emp order by sal desc limit 1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)

方法二：表的自连接

mysql> select distinct a.sal from emp a join emp b where a.sal<b.sal;
+---------+
| sal     |
+---------+
|  800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+
11 rows in set (0.00 sec)

mysql> select sal from emp
    -> where sal not in (select distinct a.sal from emp a join emp b where a.sal<b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)

5、取得平均薪水最高的部门的部门编号

方法一

步骤一：获取每个部门的平均薪资

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

步骤二：排序

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

+--------+
| deptno |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

方法二：max

select t.deptno,max(t.avg_sal)
from (select deptno,avg(sal) as avg_sal from emp group by deptno) t;

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

步骤一：获取平均薪资最高的部门

mysql> select deptno,avg(sal) avg_sal from emp group by deptno order by avg_sal desc limit 1;
+--------+-------------+
| deptno | avg_sal     |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

步骤二：内连接两张表

mysql> select d.dname
    -> from dept d
    -> join (select deptno,avg(sal) avg_sal from emp group by deptno order by avg_sal desc limit 1) t
    -> on d.deptno=t.deptno;
+------------+
| dname      |
+------------+
| ACCOUNTING |
+------------+
1 row in set (0.00 sec)

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

步骤一：先求部门的平均薪资

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

步骤二：再求部门的平均薪资对应的等级

mysql> select e.deptno, s.grade
    -> from (select deptno,avg(sal) avg_sal from emp group by deptno) e
    -> join salgrade s
    -> on e.avg_sal between s.losal and s.hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
|     30 |     3 |
|     10 |     4 |
|     20 |     4 |
+--------+-------+
3 rows in set (0.00 sec)

步骤三：内连接部门名称表

select e.deptno, s.grade, d.dname
from (select deptno,avg(sal) avg_sal from emp group by deptno) e
join salgrade s
on e.avg_sal between s.losal and s.hisal
join dept d
on d.deptno = e.deptno;

+--------+-------+------------+
| deptno | grade | dname      |
+--------+-------+------------+
|     30 |     3 | SALES      |
|     10 |     4 | ACCOUNTING |
|     20 |     4 | RESEARCH   |
+--------+-------+------------+
3 rows in set (0.00 sec)

步骤四：排序

select d.dname
from (select deptno,avg(sal) avg_sal from emp group by deptno) e
join salgrade s
on e.avg_sal between s.losal and s.hisal
join dept d
on d.deptno = e.deptno
order by s.grade limit 1;

+-------+
| dname |
+-------+
| SALES |
+-------+
1 row in set (0.00 sec)

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

步骤一：先找到所有的领导员工

mysql> select distinct mgr from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+
7 rows in set (0.00 sec)

注意：必须排除null

mysql> select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
6 rows in set (0.00 sec)

步骤二：找出不同员工在最高薪资

mysql> select max(sal)
    -> from emp
    -> where empno not in (select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+
1 row in set (0.00 sec)

步骤三：找出高于此薪资的

注意：比普通员工薪资还要高的一定是领导

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

+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+
6 rows in set (0.00 sec)

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

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 |
+-------+---------+
5 rows in set (0.00 sec)

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

mysql> select ename,sal from emp order by sal desc limit 5,5;   //第一个参数表示开始的索引，默认从0开始  //第二个参数表示长度  
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)

11、取得最后入职的 5 名员工

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 |
+--------+------------+
5 rows in set (0.00 sec)

12、取得每个薪水等级有多少员工

步骤一：获取每个员工薪资的等级

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

+---------+-------+
| sal     | grade |
+---------+-------+
|  800.00 |     1 |
| 1600.00 |     3 |
| 1250.00 |     2 |
| 2975.00 |     4 |
| 1250.00 |     2 |
| 2850.00 |     4 |
| 2450.00 |     4 |
| 3000.00 |     4 |
| 5000.00 |     5 |
| 1500.00 |     3 |
| 1100.00 |     1 |
|  950.00 |     1 |
| 3000.00 |     4 |
| 1300.00 |     2 |
+---------+-------+
14 rows in set (0.00 sec)

步骤二：计数

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 |
+-------+----------+
5 rows in set (0.00 sec)

13、面试题

14、列出所有员工及领导的姓名

自关联，外连接

select e.ename '员工', e2.ename '领导'
from emp e
left join emp e2
on e.mgr = e2.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 |
+--------+-------+
14 rows in set (0.00 sec)

15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

步骤一：找到每个员工的编号，姓名，部门名称

mysql> select e.empno, e.ename, d.dname
    -> from emp e
    -> join dept d
    -> on e.deptno = d.deptno;
+-------+--------+------------+
| empno | ename  | dname      |
+-------+--------+------------+
|  7782 | CLARK  | ACCOUNTING |
|  7839 | KING   | ACCOUNTING |
|  7934 | MILLER | ACCOUNTING |
|  7369 | SMITH  | RESEARCH   |
|  7566 | JONES  | RESEARCH   |
|  7788 | SCOTT  | RESEARCH   |
|  7876 | ADAMS  | RESEARCH   |
|  7902 | FORD   | RESEARCH   |
|  7499 | ALLEN  | SALES      |
|  7521 | WARD   | SALES      |
|  7654 | MARTIN | SALES      |
|  7698 | BLAKE  | SALES      |
|  7844 | TURNER | SALES      |
|  7900 | JAMES  | SALES      |
+-------+--------+------------+
14 rows in set (0.00 sec)

步骤二：筛选

select e.empno, e.ename, d.dname
from emp e
join dept d
on e.deptno = d.deptno
join emp e2
on e.mgr = e2.empno
where e.hiredate<e2.hiredate;   //where得放后面

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

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

+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING |  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 |
| RESEARCH   |  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 |
| SALES      |  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 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)

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

步骤一：统计每个部门的人数

mysql> select d.dname, count(*)
    -> from emp e
    -> join dept d
    -> on e.deptno = d.deptno
    -> group by e.deptno;
+------------+----------+
| dname      | count(*) |
+------------+----------+
| ACCOUNTING |        3 |
| RESEARCH   |        5 |
| SALES      |        6 |
+------------+----------+
3 rows in set (0.00 sec)

步骤二：筛选

select d.dname, count(*)
from emp e
join dept d
on e.deptno = d.deptno
group by e.deptno
having count(*)>=5;    //多这句

+----------+----------+
| dname    | count(*) |
+----------+----------+
| RESEARCH |        5 |
| SALES    |        6 |
+----------+----------+
2 rows in set (0.00 sec)

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

步骤一：找到SMITH的薪资

mysql> select sal from emp where ename='SMITH';
+--------+
| sal    |
+--------+
| 800.00 |
+--------+
1 row in set (0.00 sec)

步骤二：筛选

select e.* 
from emp e
join (select sal from emp where ename='SMITH') t
where e.sal>t.sal;

或者

select * 
from emp 
where sal>(select sal from emp where ename='SMITH');

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

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

步骤一：找到所有办事员的姓名

mysql> select deptno, ename from emp where job='CLERK';
+--------+--------+
| deptno | ename  |
+--------+--------+
|     20 | SMITH  |
|     20 | ADAMS  |
|     30 | JAMES  |
|     10 | MILLER |
+--------+--------+
4 rows in set (0.00 sec)

步骤二：找到每个部门的人数

mysql> select d.deptno, d.dname, count(*) as cc
    -> from emp e
    -> join dept d
    -> where e.deptno = d.deptno
    -> group by e.deptno;
+--------+------------+----+
| deptno | dname      | cc |
+--------+------------+----+
|     10 | ACCOUNTING |  3 |
|     20 | RESEARCH   |  5 |
|     30 | SALES      |  6 |
+--------+------------+----+
3 rows in set (0.00 sec)

步骤三：连接两张表

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

+--------+------------+----+
| ename  | dname      | cc |
+--------+------------+----+
| SMITH  | RESEARCH   |  5 |
| ADAMS  | RESEARCH   |  5 |
| JAMES  | SALES      |  6 |
| MILLER | ACCOUNTING |  3 |
+--------+------------+----+
4 rows in set (0.00 sec)

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

步骤一：查询每种工作的最低薪资

mysql> select job, min(sal) min_sal from emp group by job;
+-----------+---------+
| job       | min_sal |
+-----------+---------+
| ANALYST   | 3000.00 |
| CLERK     |  800.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1250.00 |
+-----------+---------+
5 rows in set (0.00 sec)

步骤二：查询最低薪资大于1500的工作

mysql> select job, min(sal) min_sal from emp group by job having min(sal)>1500;
+-----------+---------+
| job       | min_sal |
+-----------+---------+
| ANALYST   | 3000.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+
3 rows in set (0.00 sec)

步骤三：统计各种工作人数

select job,count(*) from emp group by job;

+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| CLERK     |        4 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
| SALESMAN  |        4 |
+-----------+----------+
5 rows in set (0.00 sec)

步骤四：合起来

select t1.job,t2.num
from (select job, min(sal) min_sal from emp group by job having min(sal)>1500) t1
join (select job,count(*) num from emp group by job) t2
on t1.job=t2.job;

+-----------+-----+
| job       | num |
+-----------+-----+
| ANALYST   |   2 |
| MANAGER   |   3 |
| PRESIDENT |   1 |
+-----------+-----+
3 rows in set (0.00 sec)

步骤三和步骤四可以合起来用以下代替：

mysql> select job, count(*) from emp group by job having min(sal)>1500;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+
3 rows in set (0.00 sec)

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

步骤一：先查找到编号

mysql> select deptno from dept where dname='SALES';
+--------+
| deptno |
+--------+
|     30 |
+--------+
1 row in set (0.00 sec)

步骤二：

mysql> select ename from emp where deptno=(select deptno from dept where dname='SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)

22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.

步骤一：求出平均薪资

select avg(sal) avg_sal from emp;

+-------------+
| avg_sal     |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

步骤二：找出薪资高于平均薪资的人

select ename, deptno, mgr, sal from emp where sal>(select avg(sal) avg_sal from emp);

+-------+--------+------+---------+
| ename | deptno | mgr  | sal     |
+-------+--------+------+---------+
| JONES |     20 | 7839 | 2975.00 |
| BLAKE |     30 | 7839 | 2850.00 |
| CLARK |     10 | 7839 | 2450.00 |
| SCOTT |     20 | 7566 | 3000.00 |
| KING  |     10 | NULL | 5000.00 |
| FORD  |     20 | 7566 | 3000.00 |
+-------+--------+------+---------+
6 rows in set (0.00 sec)

步骤三：加入部门名称

select t1.ename '姓名', d.dname '部门'
from (select ename, deptno, mgr, sal from emp where sal>(select avg(sal) avg_sal from emp)) t1
join dept d
on t1.deptno = d.deptno;

+-------+------------+
| 姓名      | 部门           |
+-------+------------+
| CLARK | ACCOUNTING |
| KING  | ACCOUNTING |
| JONES | RESEARCH   |
| SCOTT | RESEARCH   |
| FORD  | RESEARCH   |
| BLAKE | SALES      |
+-------+------------+
6 rows in set (0.00 sec)

步骤四：加入上级领导

select t1.ename '姓名', d.dname '部门', e.ename '上级领导'
from (select ename, deptno, mgr, sal from emp where sal>(select avg(sal) avg_sal from emp)) t1
join dept d
on t1.deptno = d.deptno
left join emp e
on t1.mgr = e.empno;

步骤五：加入薪资等级

select t1.ename '姓名', d.dname '部门', e.ename '上级领导', s.grade '工资等级'
from (select ename, deptno, mgr, sal from emp where sal>(select avg(sal) avg_sal from emp)) t1
join dept d
on t1.deptno = d.deptno
left join emp e
on t1.mgr = e.empno
join salgrade s
on t1.sal between s.losal and s.hisal;

+-------+------------+----------+----------+
| 姓名      | 部门           | 上级领导      | 工资等级       |
+-------+------------+----------+----------+
| JONES | RESEARCH   | KING     |        4 |
| BLAKE | SALES      | KING     |        4 |
| CLARK | ACCOUNTING | KING     |        4 |
| SCOTT | RESEARCH   | JONES    |        4 |
| KING  | ACCOUNTING | NULL     |        5 |
| FORD  | RESEARCH   | JONES    |        4 |
+-------+------------+----------+----------+
6 rows in set (0.00 sec)

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

步骤一：找到此人从事的工作

mysql> select job from emp where ename='SCOTT';
+---------+
| job     |
+---------+
| ANALYST |
+---------+
1 row in set (0.00 sec)

步骤二：找到相同工作的人

mysql> select * from emp where job=(select job from emp where ename='SCOTT') and ename!='SCOTT';
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

步骤三：找到部门

select t.ename, d.dname 
from (select * from emp where job=(select job from emp where ename='SCOTT') and ename!='SCOTT') t
join dept d
on d.deptno = t.deptno;

+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.

步骤一：找到部门30的所有薪资

mysql> select sal from emp where deptno=30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
6 rows in set (0.00 sec)

步骤二：找到所有员工薪资等同于部门30的薪资的员工

select ename, sal, deptno from emp where (sal in (select sal from emp where deptno=30));

+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| TURNER | 1500.00 |     30 |
| JAMES  |  950.00 |     30 |
+--------+---------+--------+
6 rows in set (0.00 sec)

步骤三：过滤掉30部门的员工

select ename, sal, deptno from emp where (sal in (select sal from emp where deptno=30) and deptno!=30);

符合条件的为0

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金.部门名称.

步骤一：找到部门30的最高薪资

mysql> select max(sal) from emp where deptno=30;
+----------+
| max(sal) |
+----------+
|  2850.00 |
+----------+
1 row in set (0.00 sec)

步骤二：找到所有高于部门30最高薪资的人

select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno=30);

+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.00 sec)

步骤三：部门加进去

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

+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| KING  | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+
4 rows in set (0.00 sec)

答案二：

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);

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

步骤一：找到每个人的部门

select d.dname
from emp e
join dept d
on d.deptno=e.deptno;

步骤二：统计每个部门的人数

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

+------------+----------------+
| dname      | count(e.ename) |
+------------+----------------+
| OPERATIONS |              0 |
| ACCOUNTING |              3 |
| RESEARCH   |              5 |
| SALES      |              6 |
+------------+----------------+
4 rows in set (0.00 sec)

步骤三：加入平均薪资

select d.dname, count(e.ename), ifnull(round(avg(e.sal), 2), 0)
from emp e
right join dept d
on d.deptno=e.deptno
group by e.deptno;

+------------+----------------+---------------------------------+
| dname      | count(e.ename) | ifnull(round(avg(e.sal), 2), 0) |
+------------+----------------+---------------------------------+
| OPERATIONS |              0 |                            0.00 |
| ACCOUNTING |              3 |                         2916.67 |
| RESEARCH   |              5 |                         2175.00 |
| SALES      |              6 |                         1566.67 |
+------------+----------------+---------------------------------+
4 rows in set (0.00 sec)

步骤四：加入平均服务期限

select d.dname, count(e.ename), 
	ifnull(round(avg(e.sal), 2), 0) avg_sal, ifnull(avg(timestampdiff(YEAR, e.hiredate, now())),0) avg_year
from emp e
right join dept d
on d.deptno=e.deptno
group by e.deptno;

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 |
+--------+------------+---------+
14 rows in set (0.00 sec)

28、列出所有部门的详细信息和人数

步骤一：统计每个部门的人数

mysql> select deptno, count(*) num from emp group by deptno;
+--------+-----+
| deptno | num |
+--------+-----+
|     10 |   3 |
|     20 |   5 |
|     30 |   6 |
+--------+-----+
3 rows in set (0.00 sec)

步骤二：

select d.*, ifnull(t.num,0) 
from dept d
left join (select deptno, count(*) num from emp group by deptno) t
on d.deptno=t.deptno;

+--------+------------+----------+-----------------+
| DEPTNO | DNAME      | LOC      | ifnull(t.num,0) |
+--------+------------+----------+-----------------+
|     10 | ACCOUNTING | NEW YORK |               3 |
|     20 | RESEARCH   | DALLAS   |               5 |
|     30 | SALES      | CHICAGO  |               6 |
|     40 | OPERATIONS | BOSTON   |               0 |
+--------+------------+----------+-----------------+
4 rows in set (0.00 sec)

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

步骤一：找出各种工作的最低薪资

select job,min(sal) from emp group by job;

+-----------+----------+
| job       | min(sal) |
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |   800.00 |
| MANAGER   |  2450.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1250.00 |
+-----------+----------+
5 rows in set (0.00 sec)

步骤二

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

+--------+-----------+---------+
| ename  | job       | min_sal |
+--------+-----------+---------+
| 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 |
+--------+-----------+---------+
7 rows in set (0.00 sec)

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

步骤一：找出工作为MANAGER的所有人

mysql> select deptno,ename,sal from emp where job='MANAGER';
+--------+-------+---------+
| deptno | ename | sal     |
+--------+-------+---------+
|     20 | JONES | 2975.00 |
|     30 | BLAKE | 2850.00 |
|     10 | CLARK | 2450.00 |
+--------+-------+---------+
3 rows in set (0.00 sec)

步骤二

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

+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  2450.00 |
|     20 |  2975.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

31、列出所有员工的年工资,按年薪从低到高排序

select ename, (sal + ifnull(comm,0))*12 as income from emp order by income;

+--------+----------+
| ename  | income   |
+--------+----------+
| 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 |
+--------+----------+
14 rows in set (0.00 sec)

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

步骤一：找出员工领导对应表

mysql> select a.ename, b.ename, b.sal
    -> from emp a
    -> join emp b
    -> on a.mgr = b.empno;
+--------+-------+---------+
| ename  | ename | sal     |
+--------+-------+---------+
| SMITH  | FORD  | 3000.00 |
| ALLEN  | BLAKE | 2850.00 |
| WARD   | BLAKE | 2850.00 |
| JONES  | KING  | 5000.00 |
| MARTIN | BLAKE | 2850.00 |
| BLAKE  | KING  | 5000.00 |
| CLARK  | KING  | 5000.00 |
| SCOTT  | JONES | 2975.00 |
| TURNER | BLAKE | 2850.00 |
| ADAMS  | SCOTT | 3000.00 |
| JAMES  | BLAKE | 2850.00 |
| FORD   | JONES | 2975.00 |
| MILLER | CLARK | 2450.00 |
+--------+-------+---------+
13 rows in set (0.00 sec)

步骤二：筛选>3000

select a.ename, b.ename, b.sal 
from emp a
join emp b
on a.mgr = b.empno
where b.sal>3000;   //加筛选

+-------+-------+---------+
| ename | ename | sal     |
+-------+-------+---------+
| JONES | KING  | 5000.00 |
| BLAKE | KING  | 5000.00 |
| CLARK | KING  | 5000.00 |
+-------+-------+---------+
3 rows in set (0.00 sec)

33、求出部门名称中,带’S’字符的部门员工的工资合计、部门人数

步骤一：找到员工名和部门对照表

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

+------------+--------+
| dname      | ename  |
+------------+--------+
| ACCOUNTING | CLARK  |
| ACCOUNTING | KING   |
| ACCOUNTING | MILLER |
| RESEARCH   | SMITH  |
| RESEARCH   | JONES  |
| RESEARCH   | SCOTT  |
| RESEARCH   | ADAMS  |
| RESEARCH   | FORD   |
| SALES      | ALLEN  |
| SALES      | WARD   |
| SALES      | MARTIN |
| SALES      | BLAKE  |
| SALES      | TURNER |
| SALES      | JAMES  |
+------------+--------+
14 rows in set (0.00 sec)

步骤二：按照部门分组，统计每组人数

select d.dname, count(e.ename)
from emp e
join dept d
on e.deptno=d.deptno
group by d.deptno;

+------------+----------------+
| dname      | count(e.ename) |
+------------+----------------+
| ACCOUNTING |              3 |
| RESEARCH   |              5 |
| SALES      |              6 |
+------------+----------------+
3 rows in set (0.00 sec)

步骤三：加入名字条件

select d.dname, count(e.ename)
from emp e
right join dept d
on e.deptno=d.deptno
where d.dname like '%S%'
group by d.deptno;

+------------+----------------+
| dname      | count(e.ename) |
+------------+----------------+
| RESEARCH   |              5 |
| SALES      |              6 |
| OPERATIONS |              0 |
+------------+----------------+
3 rows in set (0.00 sec)

步骤四：加入平均薪资

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

+------------+----------+----------------+
| dname      | sumsal   | count(e.ename) |
+------------+----------+----------------+
| RESEARCH   | 10875.00 |              5 |
| SALES      |  9400.00 |              6 |
| OPERATIONS |     0.00 |              0 |
+------------+----------+----------------+
3 rows in set (0.00 sec)

34、给任职日期超过 30 年的员工加薪 10%

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

Query OK, 14 rows affected (0.00 sec)
Rows matched: 14  Changed: 14  Warnings: 0
zdb呀
关注
专栏目录
老杜Servlet笔记.zip
06-09
哔哩哔哩上老杜的Servlet笔记。 2020-5-05 Servlet学习 Tomcat 服务器配置 2020-5-08 UML 2020-5-08 Servlet学习 2020-5-09 Servlet学习 2020-5-10 Servlet学习 2020-5-11 Servlet学习---Cookie 2020-5-11 Servlet中...
数据库练习
weixin_56680764的博客
08-13 1627
数据库练习
MySQL：练习题
璃白的博客
04-27 7826
题目 答案 1、取得每个部门最高薪水的人员名称 // 思路：先查询部门最高工资，再通过最高工资匹配对应人员 SELECT ename,sal,t.deptno FROM emp e JOIN (SELECT deptno,MAX(sal) AS maxsal FROM emp GROUP BY deptno) t ON e.deptno=t.deptno AND e.sal=t.maxsal 2、哪些人的薪水在部门的平均薪水之上 // 思路：先查出部门平均薪水，再查薪水大于平均薪水的员工 SELE
MySQL 第十二章 练习题
日天家的猫
08-20 813
文章目录题目及答案传送门 题目及答案 1.取得每个部门最高薪水的人员名称 select depno,max(sal) as maxsal from emp group by deptno; select e.ename,t.* from emp e join (select depno,max(sal) as maxsal from emp group by deptno) t on t.deptno = e.deptno and t.maxsal = e.sal; 2.哪些人的薪水在部门的平均薪水之上
动力节点老杜Spring6，配套笔记PDF文件
02-08
动力节点老杜Spring6配套笔记主要讲解了Spring框架的一些核心概念和最佳实践，特别是针对代码设计中的开闭原则（Open-Closed Principle, OCP）进行了深入探讨。在给出的代码示例中，我们可以看到一个简单的用户登录...
动力节点老杜javaweb笔记
10-03
动力节点老杜javaweb笔记
基于动力节点老杜MyBatis视频的Java学习资料源码
09-30
该项目为动力节点老杜MyBatis视频教程配套的Java学习源码，总计205个文件，涵盖81个XML配置、63个Java源代码、19个编译后的class文件、11个属性配置文件、9个日志文件、8个HTML页面、7个JAR库文件、2个元数据文件、1...
老杜jsp学习笔记.zip
06-09
老杜JSP 教程学习笔记 JSP（全称JavaServer Pages）是由Sun Microsystems公司主导创建的一种动态网页技术标准。JSP部署于网络服务器上，可以响应客户端发送的请求，并根据请求内容动态地生成HTML、XML或其他格式文档...
实用SQL小总结
UntifA的博客
09-29 1509
SQL记录
PostgreSQL的WAL日志优化及验证
Java_fenxiang的博客
10-05 642
在应用负载不变的情况下可以通过如下方法进行WAL优化登录后复制 1) 延长checkpoint时间间隔 FPI产生于checkpoint之后第一次变脏的page，在下次checkpoint到来之前，已经输出过FPI的page是不在需要再次输出FPI。因此checkpoint时间间隔越长，FPI产生的频度会越低。增大ch...
MySQL之复合查询与内外连接
zdlynj的博客
09-30 1322
前面我们讲解的mysql表的查询都是对一张表进行查询，即数据的查询都是在某一时刻对一个表进行操作的。而在实际开发中，我们往往还需要对多个表同时进行查询。我们这里使用的测试表，为雇员信息表(来自Oracle 9i的经典测试表)：EMP员工表，DEPT部门表，SALGRADE工资等级表。
World of Warcraft [CLASSIC][80][Grandel] Call to Arms: Victory in Wintergrasp
qq 117791303
10-02 330
Wintergrasp 冬拥湖 120 VS 120
Redis事务
zhzjn的博客
09-30 409
Redis事务可以通过MULTI、EXEC、WATCH和DISCARD指令来实现。1. MULTI指令：表示事务的开始，之后的所有指令都会被逐一记录，但不会立即执行。2. EXEC指令：表示事务的执行，会按照记录的指令逐一执行。3. WATCH指令：可以监视一个或多个键的变化，如果在事务执行期间被其他客户端修改，事务会被回滚。4. DISCARD指令：表示事务的撤销，可以放弃之前记录的指令。
【RocketMQ】RocketMQ应用难点
记录一名在读研究生的学习笔记、感悟、开发产物
09-30 925
本文探讨了RocketMQ中消息重复消费的问题及其解决方案，尤其是在CLUSTERING模式下的扩容影响。文章分析了重复消费的原因，如广播模式、负载均衡模式下的多consumerGroup消费、消费者组内的动态变化及网络延迟等，并提出了利用唯一标识进行去重的方法。此外，还讨论了如何选择合适的存储机制以高效处理大量消息标识，如HashMap、MySQL、Redis以及推荐的布隆过滤器等方案。对于防止消息堆积与确保消息不丢失，也给出了相应的策略和技术措施。
2024Java最新面试题总结（针对于一些小厂、中厂）
weixin_62375676的博客
10-07 691
2024最新Java面试题，针对一些小厂，中厂
wsl环境下安装Ubuntu，并下载MySQL5.7
最新发布
weixin_51621120的博客
10-07 170
1-通过 sudo su - ，切换到root用户。2-在每一个命令前加上sudo，临时提升权限。2、配置仓库，使用dpkg命令安装仓库。1、检查仓库是否有MySQL5.7。按以下图示进行安装即可。2、下载MySQL5.7。1、下载apt仓库文件。安装包是.deb的文件。
MQTT--EMQX入门+MQTTX使用
CJPSR的博客
09-30 1269
EMQ X基于 Erlang/OTP 平台开发的MQTT 消息服务器，是开源社区中最流行的 MQTT 消息服务器。EMQ X 是开源百万级分布式。
思迅商云开启货号修改语句
xushugang007的博客
10-03 230
insert into t_sys_system (sys_var_id,sys_var_name,sys_var_value,is_changed,sys_ver_flag) values('itemnoisedit','货号、供应商、客户编码修改','1','否','1')use hbposv10 --分店数据库名改成： hbposv10_branch。--开启 货号、供应商、客户编码修改。
一个真实可用的登录界面！
Q_w7742的博客
10-05 3239
至此，所有的代码运行之后应该可以实现输入账号密码，与数据库比对，重定向到欢迎界面这样的过程，可以将这个代码放在任何一个需要登录的网页中（自夸一下），当然还有很多很多的内容需要补充，留给你们自己发挥~这里如果php.ini设置不好可能会出现问题，找不到mysqli这个拓展，需要手动处理一下，当然这里不赘述。并且加密的过程被我注释掉了，防止新手搞不明白密码。首先需要一个静态网页，在前面已经讲了很多有关的代码，这里也是常规的，但是这里我们需要post到一个php页面，请注意。连接成功后，输入下面的代码，
老杜MySQL笔记：数据库管理系统与SQL详解
“老杜MySQL笔记总结.md（全）” 这篇笔记涵盖了关于MySQL和SQL的基础知识，包括数据库、数据库管理系统（DBMS）的概念以及SQL的作用。首先，数据库（DataBase，DB）是一个按照特定格式存储数据的文件集合，用作...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

zdb呀

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值