• select查询题目练习

    文章目录

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

    +-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
思路：获取每个部门的最高薪资和部门编号，作为临时表t；再与emp表联合查询。
select
	e.ename,t.*
from
	emp e
join
	(select max(sal) as sal,deptno from emp group by deptno) t
on
	e.deptno=t.deptno and e.sal=t.sal;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

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

    +-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| ALLEN | 1600.00 |     30 |
| JONES | 2975.00 |     20 |
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
思路：获取每个部门的平均薪水，部门编号作为临时表t；再与emp表联合查询。
select
	e.ename,e.sal,e.deptno
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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

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

    +--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
思路：先获取每个人的薪水等级，然后分组进行avg求每个部门的平均薪水等级
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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    4.取得最高薪水（给出三种解决方案）

    +---------+
| sal     |
+---------+
| 5000.00 |
+---------+
第一种·：max()函数
select max(sal) from emp;

第二种：降序，limit
select
	sal
from
	emp
order by
	sal desc
limit 
	1;

第三种：表的自连接
思路：子查询获得一张不包含最大值的表，然后进行not in操作
第一步：子查询获得一张不包含最大值的表t
select
	distinct a.sal
from
	emp a
join
	emp b
on
	a.sal
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    5.取得平均薪水最高的部门的部门编号（至少给出两种解决方案）

    +--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
第一种：降序第一个。
思路：先分组求出每组的薪资平均值，然后对表降序排序，limit 1 获得。
select
	deptno,avg(sal) as avgsal
from
	emp
group by
	deptno
order by
	avgsal desc
limit
	1;

第二种：max()函数
思路：先找出每个部门的平均薪水，再该表找出avgsal最大的值，再与该表进行having筛选操作

//这里是找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;

//这里是找出avgsal最大的值
select
	max(t.avgsal)
from
	(select deptno,avg(sal) as avgsal from emp group by deptno) t;

//having筛选
select
	deptno,avg(sal) as avgsal
from
	emp
group by
	deptno
having
	avgsal=(select max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) t);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

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

    +------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

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

    +-------+-------------+-------+
| dname | avgsal      | grade |
+-------+-------------+-------+
| SALES | 1566.666667 |     3 |
+-------+-------------+-------+
select
	d.dname,avg(e.sal) as avgsal,s.grade
from
	emp e
join
	dept d
on
	d.deptno=e.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal	
group by
	d.dname
order by
	avgsal asc
limit
	1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

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

    +-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
第一步：找出领导人列表
select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+

第二步：找出普通员工最高的薪资
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+

第三步：从emp中找出高于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 |
+-------+---------+	

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    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 |
| WARD   | 1250.00 |
+--------+---------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    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 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
+--------+------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

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

    思路：先找出每个员工的薪水等级，然后分组，求各组数量。
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 |
|     3 |        2 |
|     2 |        3 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    13.列出所有员工及领导的姓名

    select
	e.ename,ifnull(l.ename,'没有上级')
from
	emp e
left join
	emp l
on
	e.mgr=l.empno;
+--------+----------------------------+
| ename  | ifnull(l.ename,'没有上级') |
+--------+----------------------------+
| SMITH  | FORD                       |
| ALLEN  | BLAKE                      |
| WARD   | BLAKE                      |
| JONES  | KING                       |
| MARTIN | BLAKE                      |
| BLAKE  | KING                       |
| CLARK  | KING                       |
| SCOTT  | JONES                      |
| KING   | 没有上级                   |
| TURNER | BLAKE                      |
| ADAMS  | SCOTT                      |
| JAMES  | BLAKE                      |
| FORD   | JONES                      |
| MILLER | CLARK                      |
+--------+----------------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

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

    select
	distinct e.empno,e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno=d.deptno
join
	emp l
on
	e.mgr=l.empno
where
	e.hiredate
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

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

    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 |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
| ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
| RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
| SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    16.列出至少有 5 个员工的所有部门

    select
	d.dname,count(*)
from
	dept d
join
	emp e
on
	d.deptno=e.deptno
group by 
	d.dname
having
	count(*)>=5;
+----------+----------+
| dname    | count(*) |
+----------+----------+
| RESEARCH |        5 |
| SALES    |        6 |
+----------+----------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    17.列出薪金比"SMITH"多的所有员工信息

    第一步：找出SMITH的薪资
select sal from emp where ename='SMITH';
第二步：找出比SMITH薪资高的员工，运用子查询
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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

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

    第一步：找出所有'CLERK'的信息
select
	e.ename,d.deptno,d.dname
from
	emp e
join
	dept d
on
	e.deptno=d.deptno
where
	e.job='CLERK';
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+ t1表

第二步：统计各部门的人数
select
	d.deptno,count(*) as cc
from
	emp e
join
	dept d
on
	e.deptno=d.deptno
group by
	d.deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     20 |        5 |
|     30 |        6 |
|     10 |        3 |
+--------+----------+ t2表

第三步：联合两个表
select
	t1.ename,t1.dname,t2.cc
from
	(select e.ename,d.deptno,d.dname from emp e join dept d on e.deptno=d.deptno where e.job='CLERK') t1
join
	(select d.deptno,count(*) as cc from emp e join dept d on e.deptno=d.deptno group by d.deptno) t2
on
	t1.deptno=t2.deptno;
+--------+------------+----+
| ename  | dname      | cc |
+--------+------------+----+
| SMITH  | RESEARCH   |  5 |
| ADAMS  | RESEARCH   |  5 |
| JAMES  | SALES      |  6 |
| MILLER | ACCOUNTING |  3 |
+--------+------------+----+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56

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

    第一步：找出最低薪资大于1500的工作
select
	job,min(sal) as minsal	
from
	emp
group by
	job
having
	minsal>1500;
+-----------+---------+
| job       | minsal  |
+-----------+---------+
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+ t1表

第二步：找出每种工作的人数
select
	job,count(*)
from
	emp 
group by
	job;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| CLERK     |        4 |
| SALESMAN  |        4 |
| MANAGER   |        3 |
| ANALYST   |        2 |
| PRESIDENT |        1 |
+-----------+----------+ t2表

第三步：联合两张表
select
	t1.job,t2.count
from
	(select
		job,min(sal) as minsal	
	from
		emp
	group by
		job
	having
		minsal>1500) t1
join
	(select
		job,count(*) as count
	from
		emp 
	group by
		job) t2
on
	t1.job=t2.job;
+-----------+-------+
| job       | count |
+-----------+-------+
| MANAGER   |     3 |
| ANALYST   |     2 |
| PRESIDENT |     1 |
+-----------+-------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62

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

    第一种：表联合
select
	e.ename
from
	emp e
join
	dept d
on
	e.deptno=d.deptno
where
	d.dname='SALES';
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

第二种：子查询
select
	ename	
from
	emp
where
	deptno=(select deptno from dept where dname='SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

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

    select
	e.ename,d.dname,ifnull(l.ename,'无'),s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join
	emp l
on
	e.mgr=l.empno
join
	dept d
on
	e.deptno=d.deptno
where
	e.sal>(select avg(sal) as avgsal from emp);
+-------+------------+----------------------+-------+
| ename | dname      | ifnull(l.ename,'无') | grade |
+-------+------------+----------------------+-------+
| FORD  | RESEARCH   | JONES                |     4 |
| SCOTT | RESEARCH   | JONES                |     4 |
| CLARK | ACCOUNTING | KING                 |     4 |
| BLAKE | SALES      | KING                 |     4 |
| JONES | RESEARCH   | KING                 |     4 |
| KING  | ACCOUNTING | 无                   |     5 |
+-------+------------+----------------------+-------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

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

    select
	e.ename,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 | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

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

    第一步：查找部门30中员工的薪金
select distinct sal from emp where deptno=30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+ t表

第二步：从emp表中查找薪资等于t表中薪资并且部门不为30的员工
select
	ename,sal
from
	emp
where
	sal in(select distinct sal from emp where deptno=30) and deptno!=30;
Empty set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

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

    第一步：找出部门30最高薪金
select max(sal) from emp where deptno=30;
+---------+
| max(sal)  |
+---------+
| 2850.00 |
+---------+

第二步：从emp表中找薪金大于部门30最高薪金的员工
select
	e.ename,e.sal,d.dname	
from
	emp e
join
	dept d
on
	e.deptno=d.deptno
where
	sal>(select max(sal) from emp where deptno=30);
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

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

    计算两个日期之间的差值：
	用法：timestampdiff(间隔类型, 前一个日期, 后一个日期)
	间隔类型：
		SECOND   秒，
		MINUTE   分钟，
		HOUR   小时，
		DAY   天，
		WEEK   星期
		MONTH   月，
		QUARTER   季度，
		YEAR   年

//没有员工的部门，部门人数是0
select
	d.deptno,count(e.ename) as ecount,avg(ifnull(e.sal,0)) as avgsal,avg(ifnull((timestampdiff(YEAR,e.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 |        40.3333 |
|     20 |      5 | 2175.000000 |        38.4000 |
|     30 |      6 | 1566.666667 |        40.5000 |
|     40 |      0 |    0.000000 |         0.0000 |
+--------+--------+-------------+----------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    26.列出所有员工的姓名、部门名称和工资

    select
	e.ename,d.dname,e.sal
from
	emp e
join
	dept d
on
	e.deptno=d.deptno;
+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| SMITH  | RESEARCH   |  800.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| JONES  | RESEARCH   | 2975.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| CLARK  | ACCOUNTING | 2450.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| KING   | ACCOUNTING | 5000.00 |
| TURNER | SALES      | 1500.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| JAMES  | SALES      |  950.00 |
| FORD   | RESEARCH   | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
+--------+------------+---------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    27.列出所有部门的详细信息和人数

    select
	d.*,ifnull(count(e.ename),0) as '人数'
from
	dept d
left join
	emp e
on
	d.deptno=e.deptno
group by
	d.deptno;
+--------+------------+----------+------+
| DEPTNO | DNAME      | LOC      | 人数 |
+--------+------------+----------+------+
|     10 | ACCOUNTING | NEW YORK |    3 |
|     20 | RESEARCH   | DALLAS   |    5 |
|     30 | SALES      | CHICAGO  |    6 |
|     40 | OPERATIONS | BOSTON   |    0 |
+--------+------------+----------+------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

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

    第一步：找出各种工作的最低工资
select
	job,min(sal) as minsal
from
	emp
group by
	job;
+-----------+---------+
| job       | minsal  |
+-----------+---------+
| CLERK     |  800.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+
	
第二步：从emp表中筛选最低工资和工作岗位相对的员工
select
	e.*
from
	emp e
join
	(select
		job,min(sal) as minsal
	from 
		emp
	group by
		job) t
on
	e.sal=t.minsal and e.job=t.job;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     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 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42

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

    select
	deptno,min(sal)
from
	emp 
where
	job='MANAGER'
group by
	deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     20 |  2975.00 |
|     30 |  2850.00 |
|     10 |  2450.00 |
+--------+----------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

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

    年工资=sal*12+comm*12

select
	ename,(sal*12+ifnull(comm,0)*12) as incomm
from
	emp
order by
	incomm asc;
+--------+----------+
| ename  | incomm   |
+--------+----------+
| 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 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    31.求出员工领导的薪水超过 3000 的员工名称与领导名称

    select
	e.ename,l.ename
from
	emp e
join
	emp l
on
	e.mgr=l.empno
where	
	l.sal>3000;
+-------+-------+
| ename | ename |
+-------+-------+
| JONES | KING  |
| BLAKE | KING  |
| CLARK | KING  |
+-------+-------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

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

    select
	d.dname,ifnull(sum(e.sal),0),ifnull(count(e.ename),0)
from
	dept d
left join
	emp e
on
	d.deptno=e.deptno
where
	d.dname like '%S%'
group by
	d.dname;
+------------+----------------------+--------------------------+
| dname      | ifnull(sum(e.sal),0) | ifnull(count(e.ename),0) |
+------------+----------------------+--------------------------+
| RESEARCH   |             10875.00 |                        5 |
| SALES      |              9400.00 |                        6 |
| OPERATIONS |                 0.00 |                        0 |
+------------+----------------------+--------------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

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

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

    • 1

    34.总结select查询：

    select查询：
	select
		...
	from
		...
	(join
		...)
	where
		...
	group by
		..
	having
		...
	order by
		...
	limit
		...
执行顺序？
	1.from
	2.where
	3.group by
	4.having
	5.select
	6.order by
	7.limit

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
  • 相关阅读:
    python隶属关系图模型:基于模型的网络中密集重叠社区检测方法
    ios开发App，在App Store上架的app更新提交过程
    【问题解决】发现Web应用程序源代码泄露模式
    性能小课堂：Jmeter录制手机app脚本！
    Postman设置全局变量和传参
    操作系统 - 看完这篇还读不懂《银行家算法》那我也没办法了
    HTTP介绍：一文了解什么是HTTP
    axios和ajax的区别是什么
    MySQL：关于group by的一个小坑，以及sql_mode=only_full_group_by问题
    新零售模式这么应用，太牛了
  • 原文地址：https://blog.csdn.net/m0_66689823/article/details/125992528