mysql经典查询34题（附带答案和文件）

`db`SELECT sal FROM emp ORDER BY sal;--选出薪水进行升序排列

SELECT DISTINCT sal FROM emp ORDER BY sal;
--去重关键字diatinct 将sal挑出并且按照默认升序排序
SELECT DISTINCT job,deptno,sal FROM emp;
--挑选不同工作，代号

--统计工作岗位的数量
SELECT COUNT(DISTINCT job)FROM emp;

SELECT ename,deptno FROM emp;
SELECT * FROM dept;
--两张表连接没有任何条件限
SELECT ename,dname FROM emp, dept;

--选择deptno一样的进行表格的拼接操作
SELECT ename,dname FROM emp,dept
WHERE emp.`DEPTNO` = dept.`DEPTNO`;
--起别名的方法
SELECT
e.ename,d.dname
FROM
emp e, dept d
WHERE
e.deptno = d.deptno;
--SQL99起别名的方法
SELECT E.ENAME ,D.DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
--INNER 可以省略但是加了可读性更好
SELECT E.ENAME ,D.DNAME
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;--条件是等量关系所以被称为等值连接
--找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？
SELECT e.ename ，e.sal ,s.GRADE
FROM salgrade s,emp e
WHERE e.sal BETWEEN s.losal AND s.hisal;--自己写的垃圾代码
SELECT e.ename ,e.sal ,s.GRADE
FROM emp e
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;

SELECT e.ename , d.dname
FROM emp e
JOIN dept d
ON e.`DEPTNO` = d.`DEPTNO`;--sql 99语法规则

--案例：查询员
--工的上级领导，要求显示员工名和对应的领导名？
SELECT e.ename , e.leader
FROM emp e
ON e.empno = e.mgr ;
SELECT empno,ename,mgr FROM emp;--bu成功示范

--查询案例的进行，需要将一张表看成是两张表进行
e表的工号等于上级编号时，使用另一张表（其实也是emp）员工的领导编号
SELECT e.empno ,e.ename AS '员工' ,m.empno,m.ename AS '领导'
FROM
emp e
JOIN
emp m
ON e.mgr = m.empno;

-内连接
SELECT
e.ename,d.dname
FROM
emp e
JOIN
dept d
ON
e.deptno = d.deptno;

外连接（右外连接）：
SELECT
e.ename,d.dname
FROM
emp e
RIGHT JOIN
dept d
ON
e.deptno = d.deptno;
--拼接三个表格的数据，并且设定了条件分开
SELECT
e.ename,d.deptno,s.grade
FROM EMP e
JOIN dept d
ON e.deptno = d.deptno
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;

1、、取得每个部门最高薪水的人员名称
SELECT
ename,sal,deptno
FROM
emp t
WHERE
sal=(SELECT MAX(sal) FROM emp)
GROUP BY
t.deptno;

--第二题
SELECT sal,ename,AVG(sal)
FROM emp
GROUP BY sal
HAVING sal>(SELECT AVG(sal)FROM emp);

3、、取得部门中（所有人的）平均的薪水等级，如下:从emp中获取平均值和部门编号，通过部门编号进行分组
SELECT
AVG(sal),deptno
FROM
emp
GROUP BY
deptno;

例子：
SELECT job, SUM(sal) FROM emp GROUP BY job;--获取每个岗位的平均工资

4、不准用组函数（Max ），取得最高薪水（给出两种解决方案）
-- 方法一采用了自连接的方法，先定义出除了最大值的那些数字，再将整个集合中不属于这些数的那个数字拿出来就是MAX值
SELECT
sal
FROM
emp
WHERE
sal NOT IN(SELECT DISTINCT a.sal FROM emp a JOIN emp b ON a.sal<b.sal);
-- 方法2 将sal按照顺序排序取最大值即可,加入限制条件1 得到降序排列的第一个就是最大值
SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 1;

例子：select DISTINCT a.sal FROM emp a JOIN emp b ON a.sal<b.sal；--挑选唯一的薪水从表A加入到表B条件是A的薪水小于B的薪水

5、、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
-- 方法一
SELECT deptno,AVG(sal)-- 是否会有疑？deptno只是刚好在这个位置
FROM
emp
GROUP BY
deptno
ORDER BY AVG(sal)DESC
LIMIT 1;
-- 方法二
SELECT MAX(t.avgsal),deptno
FROM
(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) t;
-- 将团队的平均值看作一个新的列表，从这个列表中取得最大值就是平均值里面的最大值-- 是否会有疑？deptno只是刚好在这个位置

6 、取得平均薪水最高的部门的部门名称
SELECT MAX(t.avgsal),t.deptno , d.dname
FROM
(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) t -- 精髓之处仍然是吧部门的平均数看成是一个表格，
JOIN dept d
ON d.deptno=t.deptno; -- 还是觉得有bug在仍然需要改进,找到原因，只有deptno相等条件不足，需要再加上一个限定条件如下:

SELECT d.dname , MIN(t.avgsal)
FROM dept d, (SELECT AVG(e.sal) avgsal,e.deptno FROM emp e GROUP BY e.deptno)t
WHERE d.deptno = t.deptno AND t.avgsal = (SELECT AVG(sal)
FROM
emp
GROUP BY
deptno
ORDER BY AVG(sal) DESC
LIMIT 1);
---------------------------------------------------------------------------------------
SELECT d.dname, t.avgsal
FROM dept d
JOIN
( SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) t
ON
d.deptno=t.deptno AND t.avgsal=(SELECT AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1);
————————————————
版权声明：本文为CSDN博主「茶花女--」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_42764468/article/details/97813060

7、求平均薪水的等级最低的部门的部门名称
SELECT MIN(t.avgsal),t.deptno , d.dname
FROM
(SELECT e.deptno,d.dname,AVG(e.sal) avgsal FROM emp e ON dept d GROUP BY deptno HAVING d.deptno = e.deptno) t -- 精髓之处仍然是吧部门的平均数看成是一个表格，
JOIN dept d
ON d.deptno=t.deptno;//自己写的垃圾代码

SELECT d.dname, t.avgsal
FROM dept d
JOIN
( SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) t
ON
d.deptno=t.deptno AND t.avgsal=(SELECT AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal LIMIT 1);
-- 这是建立在别人的代码成果之上,以后还需要多去练习

-- 8、、取得比普通员工( 员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导
-- 个人分析首先是采用自连接把员工以及其领导列举出来。再采用限制条件-- 采用b表用来作为领导的姓名排
-- 把这个表看成是表T，给表格T采取限制条件进行取值(参考价值的案例)
SELECT DISTINCT a.empno,a.ename,a.sal,a.job FROM emp a JOIN emp b WHERE a.empno=b.mgr;-- 这是所有的领导，借助not in得到所有的员工
SELECT empno ,job ,sal FROM emp WHERE ename NOT IN(SELECT DISTINCT a.ename FROM emp a JOIN emp b WHERE a.empno=b.mgr);

SELECT
a.empno ,a.ename worker,a.job,b.ename leader,b.ename leaderjob,a.mgr
FROM emp a
JOIN emp b
ON a.mgr = b.empno;
-- 取出员工最高薪水w 和领导薪水L比较
SELECT e.sal,e.`ENAME`,e.`DEPTNO`
FROM emp e
WHERE e.sal > (SELECT MAX(sal) FROM emp WHERE ename NOT IN(SELECT DISTINCT a.ename FROM emp a JOIN emp b WHERE a.empno=b.mgr));

-- 9、取得薪水最高的前五名员工
SELECT saL ，ename ,job
FROM
emp
ORDER BY
sal DESC
LIMIT 5;

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

SELECT saL ，ename ,job
FROM
emp
ORDER BY
sal DESC
LIMIT 5,5;

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

SELECT hiredate ,sal ,job
FROM
emp
ORDER BY
HIREDATE DESC
LIMIT 5;

-- 12、、取得每个薪水等级有多少员工
SELECT
COUNT(e.sal),e.`DEPTNO`
FROM
emp e
GROUP BY
e.deptno; -- 错解这么做只是把每个部门的人数统计出来正解如下

SELECT COUNT(s.`GRADE`) AS '等级数量',s.`GRADE`
FROM emp e,salgrade s
WHERE
sal BETWEEN s.`LOSAL` AND s.`HISAL`
GROUP BY s.grade;

-- 14、、列出所有员工及领导的姓名（采用自连接方式）
SELECT a.ename ,b.ename
FROM
emp a
JOIN
emp b
ON
a.`mgr` = b.`EMPNO`;

-- 15、、列出受雇日期早于其直接上级的所有员工的编号, 姓名,部门
SELECT a.empno ,a.ename ,b.ename,a.deptno
FROM
emp a
JOIN
emp b
ON
a.`MGR`=b.`EMPNO` AND a.`HIREDATE`< b.`HIREDATE`;

-- 16、、列出部门名称和这些部门的员工信息,同时列出没有员工的部门
-- (左右连接- 以一边的表格为准，就是说这边的表格无论什么情况，都需要显示在表格当中另一边的话只有条件符合才会显示在结果中)
SELECT d.dname ,e.*
FROM
dept d
LEFT JOIN
emp e
ON
e.`DEPTNO` = d.`DEPTNO` ;
SELECT d.dname,e.empno,e.ename,e.job,e.hiredate,e.sal FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

-- 17、、有列出至少有 5 个员工的所有部门
SELECT COUNT(DEPTNO ),DEPTNO
FROM
EMP
GROUP BY
DEPTNO
HAVING
COUNT(DEPTNO)>=5;

-- 18、、列出薪金比"SMITH"的员工信息

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

-- 19、、列出所有"CLERK"( 办事员) 的姓名及其部门名称,
SELECT E.ENAME ,D.DNAME
FROM
EMP E
JOIN
DEPT D
ON
E.`JOB` = 'CLERK' AND D.`DEPTNO` = E.`DEPTNO`;

-- 20、、于列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数.
SELECT MIN(sal),COUNT(sal)
FROM
emp
GROUP BY
job
HAVING
MIN(sal)>1500;

-- 21、、列出在部门"SALES"< 销售部> 工作的员工的姓名,
SELECT d.`DNAME`,e.ename
FROM
emp e
JOIN
dept d
ON
d.`DEPTNO` = e.`DEPTNO` AND d.`DNAME` = 'sales';
-- 22、、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级
-- 对于多个表的加入还不够熟练
SELECT
e.ename '员工', d.dname '部门',a.ename '领导',s.`GRADE` '薪资等级'
FROM
emp e
JOIN
dept d
ON
d.`DEPTNO` = e.`DEPTNO` -- 一一对应部门编号得出员工所在部门的名称
LEFT JOIN
emp a
ON
e.`MGR` = a.`empno` -- 采用左连接使得首席执行官能够留下来
JOIN
salgrade s
ON
e.sal BETWEEN s.`LOSAL` AND s.`HISAL` -- 得出薪资等级
WHERE
e.sal > (SELECT AVG(sal) FROM emp); -- 将薪资大于平均水平的筛选出来

SELECT
e.ename , d.dname ,a.ename ,s.`GRADE`
FROM
emp e,dept d ,emp a,salgrade s
WHERE
e.sal > (SELECT AVG(sal) FROM emp)
AND e.`MGR` = a.`empno`
AND e.`DEPTNO` = d.`DEPTNO`
AND e.sal BETWEEN s.`LOSAL` AND s.`HISAL`; -- 自己做得结果还是不对，缺少一个条件导致多了一个

SELECT e1.empno, e1.ename, d.dname, e1.sal, sg.grade
FROM emp e1, emp e2, dept d, salgrade sg
WHERE e1.sal > (SELECT AVG(sal) FROM emp)
AND e1.mgr = e2.empno
AND e1.deptno = d.deptno
AND e1.sal BETWEEN losal AND hisal; -- 错误案例缺少首席执行官

-- 23、、列出与"SCOTT" 从事相同工作的所有员工及部门名称.
SELECT e.ename ,d.dname
FROM emp e
JOIN dept d
ON e.job = (SELECT job FROM emp WHERE ename = 'scott')
AND e.`DEPTNO` = d.`DEPTNO`;

-- 24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.--我打断先把30部门的人员球出来
SELECT a.ename ,a.sal
FROM emp a
WHERE a.deptno = 30 ;-- 表示30部门中所有人员正解如下

SELECT a.ename ,a.sal
FROM emp a
RIGHT JOIN (SELECT a.sal
FROM emp a
WHERE a.deptno = 30) b
ON a.sal = b.sal ;

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

SELECT a.ename ,a.sal ,d.`DNAME`
FROM emp a
JOIN dept d
ON
d.`DEPTNO` = a.`DEPTNO`
AND a.`SAL`>(SELECT MAX(a.sal)
FROM emp a
WHERE a.deptno = 30);

-- 26、列出在每个部门工作的员工数量, 平均工资和平均服务期限.
SELECT COUNT(ename) ,
AVG(sal) ,
AVG(YEAR(SYSDATE())-YEAR(hiredate))
FROM emp
GROUP BY emp.deptno; -- (网上获取的答案)

-- 26、列出在每个部门工作的员工数量, 平均工资和平均服务期限.
SELECT COUNT(ename),AVG(sal),AVG(YEAR (SYSDATE()) - YEAR(hiredate)) FROM emp GROUP BY emp.`DEPTNO`;

-- 27、、列出所有员工的姓名、部门名称和工资。
SELECT e.ename , d.dname,sal FROM emp e JOIN dept d;

-- 28、列出所有部门的详细信息和人数
SELECT e.* , d.dname,sal FROM emp e JOIN dept d ON d.`DEPTNO` = e.`DEPTNO` ORDER BY deptno;

-- 29、、列出各种工作的最低工资及从事此工作的雇员
SELECT ename ,MIN(sal) FROM emp GROUP BY job;

-- 30、的列出各个部门的 MANAGER( 领导) 的最低薪金的是
SELECT ename ,job ,MIN(sal),deptno FROM emp WHERE job = 'manager';

-- 31、、列出所有员工的年工资,按按年薪从低到高排序
SELECT ename ,sal*12 FROM emp ;

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

SELECT d.dname,SUM(e.sal),COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno AND d.dname LIKE '%S%'
GROUP BY d.dname;

-- 34、、过给任职日期超过 30 年的员工加薪 10%.
-- 先对表格备份一下
CREATE TABLE emp_bak AS SELECT * FROM emp;
UPDATE emp_bak SET sal = sal *1.1 WHERE (TO_DAYS(NOW())-TO_DAYS(hiredate))/365>30;

发表于 2020-03-21 21:46 Office2019 阅读(649) 评论(0) 编辑收藏举报

mysql经典查询34题（附带答案和文件 ）