?作者簡介：大家好我是@每天都要敲代碼，希望一起努力，一起進步！
??個人主頁：@每天都要敲代碼的個人主頁
??系列專欄：MySQL專欄
目錄
1. 取得每個部門最高薪水的人員名稱
2. 哪些人的薪水在部門的平均薪水之上
3. 取得部門中（所有人的）平均的薪水等級
4. 用不同的方法，求最高薪水
5.?取得平均薪水最高的部門的部門編號
6.?取得平均薪水最高的部門的部門名稱
7. 求平均薪水的等級最低的部門的部門名稱
8. 取得比普通員工的最高薪水還要高的領(lǐng)導(dǎo)人姓名
9. 取得薪水最高的前五名員工?
10.?取得薪水最高的第六到第十名員工
11. 得最后入職的 5 名員工
12.?取得每個薪水等級有多少員工?
13. 面試題：
14.?列出所有員工及領(lǐng)導(dǎo)的姓名
15.?列出受雇日期早于其直接上級的所有員工的編號,姓名,部門名稱
16.?列出部門名稱和這些部門的員工信息, 同時也要列出那些沒有員工的部門
17.?列出至少有 5 個員工的所有部門
18.?列出薪金比"SMITH" 多的所有員工信息?
19.?列出所有"CLERK"( 辦事員) 的姓名及其部門名稱, 部門的人數(shù)
20.?列出最低薪金大于 1500 的各種工作及從事此工作的全部雇員人數(shù)
21.?列出在部門"SALES"< 銷售部> 工作的員工的姓名, 假定不知道銷售部的部門編號?
22.?列出薪金高于公司平均薪金的所有員工, 所在部門, 上級領(lǐng)導(dǎo), 雇員的工資等級
23.?列出與"SCOTT" 從事相同工作的所有員工及部門名稱
24.?列出薪金等于部門30中員工的薪金的其他員工的姓名和薪金
25.?列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金,部門名稱
26.?列出在每個部門工作的員工數(shù)量, 平均工資和平均服務(wù)期限
27.?列出所有員工的姓名、部門名稱和工資
28.?列出所有部門的詳細信息和人數(shù)
29.?列出各種工作的最低工資及從事此工作的雇員姓名
30.?列出各個部門的 MANAGER( 領(lǐng)導(dǎo)) 的最低薪金
31.?列出所有員工的 年工資, 按 年薪從低到高排序
32.?求出員工領(lǐng)導(dǎo)的薪水超過3000的員工名稱與領(lǐng)導(dǎo)?
33.?求出部門名稱中, 帶'S'字符的部門員工的工資合計、部門人數(shù)
34.??給任職日期超過 30 年的員工加薪 10%
1. 取得每個部門最高薪水的人員名稱
第一步：先按照部門分組，找到每個部門的最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;
-- 養(yǎng)成好習(xí)慣，給max(sal)定義一個別名，便于操作；不定義下面直接t.max(sal)會報錯?
第二步：將以上的查詢結(jié)果當(dāng)做一張臨時表t，t和emp表連接，條件：t.deptno = e.deptno and t.maxsal = e.sal
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? e.sal = t.maxsal;
2. 哪些人的薪水在部門的平均薪水之上
第一步：按照部門分組，求出每個部門的平均薪水
select deptno,avg(sal) as avgsal
from emp
group by deptno;
第二步：將以上查詢結(jié)果當(dāng)做t表，t和emp表連接；條件：部門編號相同，并且emp的sal大于t表的avgsal?
select e.ename,e.sal,t.*
from (select deptno,avg(sal) as avgsal from emp group by deptno) t
join emp e
on e.deptno = t.deptno and sal > t.avgsal;
3. 取得部門中（所有人的）平均的薪水等級
第一步：先獲得每個人的薪水等級
select e.ename,e.sal,e.deptno,s.grade
from emp e
join salgrade s
on e.sal between s.losal and hisal;
第二步：根據(jù)以上結(jié)果進行分組，然后求平均值
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組函數(shù)
select max(sal) maxsal from emp;
第二種方法：先降序排，然后使用limit取第一個
select sal from emp order by sal desc limit 1;
第三種方法：使用自連接
第一步：先使用自連接求出一個范圍數(shù)據(jù)，這堆數(shù)據(jù)里不包括除最大值，其它都包括
select distinct a.sal
from emp a
join emp b
on a.sal < b.sal;
-- 最大值5000不小于任何值，不會被列出來；其它數(shù)據(jù)都會被列出來?
第二步：使用子查詢
select sal
from emp
where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal );
第四種方法：常識思維
select sal from emp where mgr is null;
-- 工資最高，肯定是老板，沒有上級領(lǐng)導(dǎo)
5.?取得平均薪水最高的部門的部門編號
第一種方法：降序排，然后limit截取第一個
?第一步：先求出每個部門的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
第二步： 降序選第一個
select deptno,avg(sal) avgsal
from emp
group by deptno
order by avgsal desc
limit 1;
第二種方法： 使用max
??第一步：先求出每個部門的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
第二步：找出以上結(jié)果中avgsal最大的值
select max(t.avgsal) maxavgsal
from? (select deptno,avg(sal) avgsal from emp group by deptno) t
?第三步：聯(lián)合使用，第一步和第二步結(jié)合，顯示平均薪水中最大的值
select deptno,avg(sal) avgsal
from emp
group by deptno
having avgsal = (select max(t.avgsal) maxavgsal from? (select deptno,avg(sal) avgsal from emp group by deptno) t);
6.?取得平均薪水最高的部門的部門名稱
?第一步：先求出每個部門的平均薪水，用部門名稱dname分組
select d.dname,avg(e.sal) avgsal?
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname?; --根據(jù)部門名稱分類
第二步： 降序選第一個?
select d.dname,avg(e.sal) avgsal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname -- 根據(jù)姓名進行排序
order by avgsal desc
limit 1;
7. 求平均薪水的等級最低的部門的部門名稱
第一步：找出每個部門的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
?第二步：找出部門的平均薪水等級?
select t.*,s.grade?
from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join salgrade s
on t.avgsal between s.losal and s.hisal;
第三步：選出最低的薪水
select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
第四步：根據(jù)最低薪水得出部門名稱；有可能不止一個數(shù)據(jù)，所以不能直接先升序排然后limit?
select t.*,s.grade?
from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join salgrade s
on t.avgsal between s.losal and s.hisal
where e.sal = (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1);8. 取得比普通員工的最高薪水還要高的領(lǐng)導(dǎo)人姓名
第一步：找出普通員工的最高薪資
-- 編號沒有出現(xiàn)在mgr中的一定就是普通員工
select max(sal)
from emp
where empno not in(select distinct mgr from emp where mgr is not null);
-- not in 后面一定要手動排除null，不要最后結(jié)果是null
第二步：找出高于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));
9. 取得薪水最高的前五名員工?
select ename,sal from emp order by sal desc limit 5;
10.?取得薪水最高的第六到第十名員工
select ename,sal from emp order by sal desc limit 5,5;
11. 得最后入職的 5 名員工
select ename,hiredate from emp order by hiredate desc limit 5;
-- 日期也可以降序，升序
12.?取得每個薪水等級有多少員工?
第一步：找出每個員工的薪水等級
select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and hisal;
?第二步：分組count
select s.grade,count(*)
from emp e
join salgrade s
on e.sal between s.losal and hisal
group by s.grade;
13. 面試題：
有 3 個表 S(學(xué)生表)，C（課程表），SC（學(xué)生選課表）
S（SNO，SNAME）代表（學(xué)號，姓名）
C（CNO，CNAME，CTEACHER）代表（課號，課名，教師）
SC（SNO，CNO，SCGRADE）代表（學(xué)號，課號，成績）
問題：
（1）找出沒選過“黎明”老師的所有學(xué)生姓名。
（2）列出 2 門以上（含2 門）不及格學(xué)生姓名及平均成績。
（3）即學(xué)過 1 號課程又學(xué)過 2 號課所有學(xué)生的姓名。
（1）找出沒選過“黎明”老師的所有學(xué)生姓名
第一步：先根據(jù)姓名找出“黎明”老師的課號cno
select cno from c where cteacher=“黎明”;第二步：根據(jù)課號cno，找出選的學(xué)生的學(xué)號sno
select sno from sc where cno = (select cno from c where cteacher=“黎明”);第三步：根據(jù)學(xué)號sno找出學(xué)生姓名
select sname from s where sno not in (select sno from sc where cno = (select cno from c where cteacher=“黎明”));（2）列出 2 門以上（含2 門）不及格學(xué)生姓名及平均成績
select s.sname,avg(sc.scgrade) avggrade
from s
right join sc
on s.sno = sc.sno
where sc.scgrade < 60
group by s.sname
having count(s.sname) >= 2;（3）即學(xué)過 1 號課程又學(xué)過 2 號課所有學(xué)生的姓名
第一步：選出學(xué)過1號課程的學(xué)生
select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 1;第一步：選出學(xué)過2號課程的學(xué)生
select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 2;第三步：選出即學(xué)過 1 號課程又學(xué)過 2 號課所有學(xué)生的姓名
select t1.sname from (select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 1) t1
join (select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 2) t2 on t1.sname = t2.sname;14.?列出所有員工及領(lǐng)導(dǎo)的姓名
-- 使用自連接
select a.ename '員工', b.ename '領(lǐng)導(dǎo)'
from emp a
left join emp b
on a.mgr = b.empno; 
15.?列出受雇日期早于其直接上級的所有員工的編號,姓名,部門名稱
select a.empno,a.ename '員工',a.deptno,a.hiredate,b.empno,b.ename '領(lǐng)導(dǎo)',b.deptno,b.hiredate,d.dname
from emp a
join emp b
on a.mgr = b.empno --直接上級
join dept d
on a.deptno = d.deptno --根據(jù)條件輸出對應(yīng)的d.name
where a.hiredate < b.hiredate;
16.?列出部門名稱和這些部門的員工信息, 同時也要列出那些沒有員工的部門
select e.*,d.dname from emp e right join dept d on e.deptno = d.deptno;??
17.?列出至少有 5 個員工的所有部門
select deptno from emp group by deptno having count(*)>=5;
18.?列出薪金比"SMITH" 多的所有員工信息?
第一步：先查出smith的薪資
select e.sal from emp e where e.ename = 'smith';
第二步：找出薪資大于800的
select ename,sal ?from emp where sal > (select e.sal from emp e where e.ename = 'smith');
19.?列出所有"CLERK"( 辦事員) 的姓名及其部門名稱, 部門的人數(shù)
第一步：找出clerk(辦事員)的姓名和部門名稱
select e.ename,d.dname,d.deptno
from emp e
join dept d
on e.deptno = d.deptno
where? job = 'CLERK';
?
第二步：分組，統(tǒng)計每個部門的人數(shù)
select deptno,count(*) as deptcount
from emp e
group by deptno; 
?第三步：兩張表進行鏈接
select t1.*,t2.deptcount
from (select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno
where? job = 'CLERK') t1
join (select deptno,count(*) as deptcount from emp e group by deptno) t2
on t1.deptno = t2.deptno;
20.?列出最低薪金大于 1500 的各種工作及從事此工作的全部雇員人數(shù)
select job,count(*) from emp group by job having min(sal) > 1500;
21.?列出在部門"SALES"< 銷售部> 工作的員工的姓名, 假定不知道銷售部的部門編號?
第一步：利用部門sales得到部門編號
select deptno from dept where dname = 'sales';
第二步：在通過部門編號得到員工姓名
select ename from emp where deptno = (select deptno from dept where dname = 'sales');
22.?列出薪金高于公司平均薪金的所有員工, 所在部門, 上級領(lǐng)導(dǎo), 雇員的工資等級
第一步：找出公司平均薪金
select avg(sal) from emp;
第二步：找出所有工資>2073.214286的員工?
select e1.ename '員工',d.dname,e2.ename '領(lǐng)導(dǎo)',s.grade
from emp e1
join dept d
on e1.deptno = d.deptno
left join emp e2
on e1.mgr = e2.deptno
join salgrade s
on e1.sal between s.losal and hisal
where e1.sal > (select avg(sal) from emp);
23.?列出與"SCOTT" 從事相同工作的所有員工及部門名稱
第一步：找出scott從事的工作
select job from emp where ename = 'SCOTT';
第二步： 找出相同工作的所有員工及部門名稱
select e.ename,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where job = (select job from emp where ename = 'SCOTT')
and ename <> 'SCOTT'; --排除本身自己
24.?列出薪金等于部門30中員工的薪金的其他員工的姓名和薪金
?第一步：找出部門30的薪資集合
select distinct sal from emp where deptno = 30;
第二步：找出薪資屬于上述集合，但部門不是30的
select ename,sal
from emp
where sal in (select distinct sal from emp where deptno = 30)
and deptno <> 30;?
25.?列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金,部門名稱
第一步：找出30部門的最高薪資
select max(sal) from emp where deptno = 30;
?第二步：找出薪資>2850.00的員工
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.?列出在每個部門工作的員工數(shù)量, 平均工資和平均服務(wù)期限
注：沒有員工的部門，使用ifnull函數(shù)，部門人數(shù)是0
第一步：找出員工數(shù)量和平均工資
select d.dname,count(e.ename),ifnull(avg(e.sal),0)
from emp?e
right join dept d
on e.deptno = d.deptno
group by d.dname;
第二步：加上計算平均服務(wù)期限
?在mysql當(dāng)中怎么計算兩個日期的“年差”，差了多少年？
?? ?TimeStampDiff(間隔類型, 前一個日期, 后一個日期)?? ?間隔類型：
?? ??? ?SECOND ? 秒，
?? ??? ?MINUTE ? 分鐘，
?? ??? ?HOUR ? 小時，
?? ??? ?DAY ? 天，
?? ??? ?WEEK ? 星期
?? ??? ?MONTH ? 月，
?? ??? ?QUARTER ? 季度，
?? ??? ?YEAR ? 年
select d.dname,count(e.ename),ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
from emp?e
right join dept d
on e.deptno = d.deptno
group by d.dname;
27.?列出所有員工的姓名、部門名稱和工資
select e.ename,d.dname,e.sal
from emp e
join dept d
on e.deptno = d.deptno;??
28.?列出所有部門的詳細信息和人數(shù)
select d.* ,count(e.ename)
from?emp e
right join dept d
on?e.deptno = d.deptno
group by d.deptno,d.dname,d.loc;
29.?列出各種工作的最低工資及從事此工作的雇員姓名
第一步：找出各種工作的最低工資
select job,min(sal) as minsal?
from emp
group? by job;
第二步：把上述結(jié)果當(dāng)成t表，進行表連接
select e.ename,t.*
from emp e
join (select job,min(sal) as minsal?from emp group??by job) t
on e.job = t.job and e.sal = t.minsal;
30.?列出各個部門的 MANAGER( 領(lǐng)導(dǎo)) 的最低薪金
select deptno,min(sal)
from emp
where job = 'MANAGER'
group by deptno;
31.?列出所有員工的 年工資, 按 年薪從低到高排序
select ename,(sal+ifnull(comm,0)) * 12 as yearsal?
from emp?
order by yearsal asc;
32.?求出員工領(lǐng)導(dǎo)的薪水超過3000的員工名稱與領(lǐng)導(dǎo)?
select a.ename?as '員工', b.ename as '領(lǐng)導(dǎo)'
from emp a
join emp b
on a.mgr = b.empno
where b.sal > 3000;
33.?求出部門名稱中, 帶'S'字符的部門員工的工資合計、部門人數(shù)
select d.deptno,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,d.dname;
文章來源：http://www.zghlxwxcb.cn/news/detail-789654.html
34.??給任職日期超過 30 年的員工加薪 10%
update emp set sal = sal*1.1 where (timestampdiff(YEAR,hiredate,now()) >30 );
select * from emp;
文章來源地址http://www.zghlxwxcb.cn/news/detail-789654.html
到了這里，關(guān)于MySQL基礎(chǔ)篇 | 經(jīng)典三十四道練習(xí)題的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
