mysql面试sql语句 mysql面试题之sql语句

MySQLSQL语句练习题

• 0 创建测试数据库
• 1 取得每个部门最高薪水的人员名称
• 2 哪些人的薪水在部门的平均薪水之上
• 3 取得部门中（所有人的）平均的薪水等级
• 4 不准用组函数（Max），求得最高薪水
• 5 取平均薪水最高的部门的部门编号
• 6 取得平均薪水最高的部门的部门名称
• 7 求平均薪水的等级最低的部门的部门名称
• 8 取得比普通员工（员工代码没有在mgr字段上出现）的最高薪水还要高的领导人的姓名
• 9 取得薪水最高的前五名员工
• 10 取得薪水最高的第六到第十名员工
• 12 取得最后入职的5名员工
• 12 取得每个薪水等级有多少员工
• 13 面试题
• 14 列出所有员工及领导的姓名
• 15 列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
• 16 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
• 17 列出至少有5个员工的部门
• 18 列出薪金比'SMITH'多的员工信息
• 19 列出所有'CLERK'（办事员）的姓名及其部门名称，部门的人数
• 20 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
• 21 列出在部门'SALES'《销售部》工作的员工的姓名，假定不知道销售部的部门编号
• 22 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级
• 23 列出于'SCOTT'从事相同工作的所有员工及部门名称
• 24 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
• 25 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金 部门名称
• 26 列出在每个部门工作的员工数量，平均工资和平均服务期限
• 27 列出所有员工的姓名、部门名称和工资
• 28 列出所有部门的详细信息和人数
• 29 列出各种工作的最低工资及从事此工作的雇员姓名
• 30 列出各个部门的 MANAGER( 领导) 的最低薪金
• 31 列出所有员工的年工资, 按年薪从低到高排序
• 32 求出员工领导的薪水超过3000的员工名称与领导
• 33 求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
• 34 给任职日期超过 30 年的员工加薪 10%

0 创建测试数据库

创建数据库，执行bjpowernode.sql

create database bjpowernode;
use bjpowernode;
source /home/project/02 MySQL/Day10/02动力节点/bjpowernode.sql

DEPT

EMP

SALGRADE

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

① 分组 & MAX 函数 取得每个部门最高薪水（按照部门编号分组，找出每一组最大值）

SELECT DEPTNO,MAX(SAL) AS MAXSAL FROM EMP GROUP BY DEPTNO;

② 多表查询操作 将上述查询结果当做一张临时表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;

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

① 分组 & AVG函数
找出每个部门的平均薪水

SELECT DEPTNO,AVG(SAL) AVGSQL FROM EMP GROUP BY DEPTNO;

② 连接

将上述查询结果当做一张临时表T

T表和EMP表连接

条件：T.DEPTNO = E.DEPTNO AND T.AVGSAL < E.SAL

SELECT  E.ENAME,E.SAL,T.* FROM EMP E JOIN (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) T ON T.DEPTNO = E.DEPTNO AND T.AVGSAL < E.SAL;

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

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

① 连接 & 排序
找出每个人的薪水等级
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;

SELECT E.ENAME,E.SAL,E.DEPTNO,S.GRADE FROM EMP E JOIN SALGRA E.SAL BETWEEN S.LOSAL AND S.HISAL ORDER BY DEPTNO;

② 分组 & AVG函数 & 连接
基于上述结果继续按照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;

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

① SAL降序 LIMIT 1

SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 1;

② 表自连接

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

方案一：降序取第一
① 分组 & AVG & 排序
找出每个部门的平均薪水

SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

② 降序 & LIMIT 1
降序取第一个

SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO ORDER BY AVGSAL DESC LIMIT 1;

方案二：Max
① 每个部门的平均薪资当成临时表 求出最大值

SELECT MAX(T.AVGSAL) FROM (SELECT DEPTNO,AVG(SAL) 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) AVGSAL FROM EMP GROUP BY DEPTNO) T);

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

①

SELECT   D.DNAME,AVG(E.SAL) AVGSAL FROM  EMP E JOIN  DEPT D ON  E.DEPTNO = D.DEPTNO GROUP BY  D.DNAME  ORDER BY  AVGSAL DESC LIMIT   1;

方案二：

① 用户变量

SELECT @max_deptno := DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO HAVING AVGSAL = (SELECT MAX(T.AVGSAL) FROM (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) T);

SELECT DNAME FROM DEPT WHERE DEPTNO = @max_deptno;

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

① 分组 & AVG函数
按照部门名称分组，找出每个部门的平均薪水

SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO;

② 连接 & BETWEEN AND
找出每个部门的平均薪水的等级
上述结果作为临时表T 和 SALGRADE表连接
条件：T.AVGSAL BETWEEN S.LOSAL AND S.HISAL

SELECT T.*,S.GRADE FROM (SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO) T  JOIN  SALGRADE S ON  T.AVGSAL BETWEEN S.LOSAL AND S.HISAL;

③ ORDER BY & LIMIT

SELECT  T.*,S.GRADE FROM  (SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO ORDER BY AVGSAL LIMIT 1) T JOIN  SALGRADE S ON  T.AVGSAL BETWEEN S.LOSAL AND S.HISAL;

== 区间 ==

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

SELECT DISTINCT MGR FROM EMP;
员工编号没有在上述结果范围内的都是普通员工
① not in & MAX
找出普通员工的最高薪水

SELECT MAX(SAL) FROM EMP WHERE EMPNO NOT IN (SELECT DISTINCT MGR FROM EMP);

not in 在使用中记得排除NULL -> WHERE MGR IS NOT NULL

SELECT MAX(SAL) FROM EMP WHERE EMPNO NOT IN (SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL);

② >
找出高于普通员工最高薪水的

SELECT @max_sal := MAX(SAL) FROM EMP WHERE EMPNO NOT IN (SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL);

SELECT ENAME,SAL FROM EMP WHERE SAL > @max_sal;

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

降序取前五

SELECT EMPNO,ENAME,SAL  FROM EMP ORDER BY SAL DESC LIMIT 5;

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

降序 & LIMIT 参数 SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 5,5;

12 取得最后入职的5名员工

日期也可以排序哦！

SELECT EMPNO,ENAME,HIREDATE  FROM EMP ORDER BY HIREDATE DESC LIMIT 5;

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

① 连接 & BETWEEN AND
每个员工的薪水等级

SELECT E.ENAME,E.SAL,S.GRADE FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

② 连接 & 分组 & BETWEEN AND

继续按照GRADE分组统计数量

SELECT S.GRADE,COUNT(*) FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL GROUP BY S.GRADE;

13 面试题

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

左外连接

SELECT A.ENAME '员工',B.ENAME '领导'  FROM EMP A LEFT JOIN EMP B ON A.MGR = B.EMPNO;

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

EMP A 员工表
EMP B 领导表
A.MGR = B.EMPNO AND A.HIREDATA < B.HIREDATA

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;

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

右连接

SELECT E.*,D.DNAME  FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

左连接

SELECT E.*,D.DNAME  FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

SELECT D.DNAME,E.*  FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

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

HAVING & COUNT & 分组

SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) >= 5;

18 列出薪金比’SMITH’多的员工信息

① 'SMITH’员工的薪金

SELECT SAL FROM EMP WHERE ENAME = 'SMITH';

② 大于'SMITH'员工的薪金的员工信息

SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');

19 列出所有’CLERK’（办事员）的姓名及其部门名称，部门的人数

① 所有’CLERK’（办事员）的姓名及其部门名称 T1

mysql> SELECT E.ENAME,E.JOB,D.DNAME,D.DEPTNO
    -> FROM EMP E
    -> JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO 
    -> WHERE E.JOB = 'CLERK';

② 部门的人数 T2

SELECT DEPTNO,COUNT(*) AS DEPTCOUNT FROM EMP GROUP BY DEPTNO;

③ 连接T1 T2

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;

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

按照工作岗位分组求最小值

mysql> SELECT JOB,COUNT(*)
    -> FROM EMP
    -> GROUP BY JOB
    -> HAVING MIN(SAL) > 1500;

21 列出在部门’SALES’《销售部》工作的员工的姓名，假定不知道销售部的部门编号

① 部门编号

mysql> SELECT DEPTNO
    -> FROM DEPT
    -> WHERE DNAME = 'SALES';

②

mysql> SELECT ENAME 
    -> FROM EMP
    -> WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');

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

① 平均工资

mysql> SELECT AVG(SAL) 
    -> FROM EMP;

② 多表连接查询

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

23 列出于’SCOTT’从事相同工作的所有员工及部门名称

①

SELECT JOB FROM EMP WHERE ENAME = 'SCOTT';

②

mysql> 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';

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

①

SELECT DISTINCT SAL FROM EMP WHERE DEPTNO = 30;

②

mysql> SELECT ENAME,SAL
    -> FROM EMP
    -> WHERE SAL IN (
    ->  SELECT DISTINCT SAL FROM EMP WHERE DEPTNO = 30);

③

SELECT ENAME,SAL FROM EMP WHERE SAL IN (  SELECT DISTINCT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO <> 30;

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

① 部门30员工的最高薪金

mysql> SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30;

② 连接 子查询

mysql> 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 列出在每个部门工作的员工数量，平均工资和平均服务期限

① COUNT AVG GROUP BY 右连接

mysql> SELECT D.DEPTNO,COUNT(E.ENAME),AVG(E.SAL)
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

② IFNULL

mysql> SELECT D.DEPTNO,COUNT(E.ENAME),IFNULL(AVG(E.SAL),0)
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

③ 计算日期年差 函数Mysql中计算两个日期之间的时间间隔的方法

SELECT TimeStampDiff(间隔类型，前一个日期，后一个日期);

间隔类型：

• SECOND 秒
• MINUTE 分钟
• HOUR 小时
• DAY 天
• WEEK 星期
• MONTH 月
• QUARTER 季度
• YEAR 年

当前日期： now()

SELECT TIMESTAMPDIFF(YEAR,HIREDATE,NOW()) FROM EMP;

④ 整合

mysql> SELECT D.DEPTNO,COUNT(E.ENAME) ECOUNT,IFNULL(AVG(E.SAL),0) AVGSAL,
    ->  IFNULL(AVG(TIMESTAMPDIFF(YEAR,HIREDATE,NOW())),0) AVGSERVICETIME
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

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

mysql> SELECT E.ENAME,D.DNAME,E.SAL

-> FROM EMP E

-> JOIN DEPT D

-> ON E.DEPTNO = D.DEPTNO;

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

mysql> SELECT D.*,COUNT(E.ENAME)
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

mysql> SELECT D.*,COUNT(E.EMPNO)
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

mysql> SELECT D.*,COUNT(*)
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

mysql> SELECT D.*,COUNT(E.ENAME)
    -> FROM EMP E
    -> JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

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

① 各种工作的最低工资

mysql> SELECT JOB,MIN(SAL) MINSAL
    -> FROM EMP
    -> GROUP BY JOB;

② 自连接

mysql> SELECT E.ENAME,T.*
    -> FROM EMP E
    -> JOIN (SELECT JOB,MIN(SAL) MINSAL FROM EMP GROUP BY JOB) T
    -> ON E.JOB = T.JOB AND E.SAL = T.MINSAL;

SELECT E.ENAME,T.* FROM EMP E JOIN (SELECT JOB,MIN(SAL) MINSAL FROM EMP GROUP BY JOB) T ON E.SAL = T.MINSAL;

SELECT E.ENAME,T.* FROM EMP E JOIN (SELECT JOB,MIN(SAL) MINSAL FROM EMP GROUP BY JOB) T ON E.JOB = T.JOB;

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

mysql> SELECT DEPTNO,MIN(SAL) MINSAL
    -> FROM EMP
    -> WHERE JOB = 'MANAGER'
    -> GROUP BY DEPTNO;

mysql> SELECT DISTINCT JOB FROM EMP;

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

mysql> SELECT ENAME,SAL*12 YEARSAL
    -> FROM EMP
    -> ORDER BY YEARSAL ASC;

== 津贴==

mysql> SELECT ENAME,(SAL + IFNULL(COMM,0)) * 12 YEARSAL
    -> FROM EMP
    -> ORDER BY YEARSAL;

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

mysql> SELECT A.ENAME '员工',B.ENAME '领导'
    -> FROM EMP A
    -> JOIN EMP B
    -> ON A.MGR = B.EMPNO
    -> WHERE B.SAL > 3000;

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

LIKE

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

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

mysql> UPDATE EMP SET SAL = SAL * 1.1  WHERE TIMESTAMPDIFF(YEAR,HIREDATE,NOW()) > 30;