mysql的常用语句练习使用

最新推荐文章于 2023-06-12 08:58:04 发布

萌茶๑

最新推荐文章于 2023-06-12 08:58:04 发布

阅读量166

点赞数

分类专栏： mysql练习文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/xj15291519671/article/details/120429873

版权

mysql练习专栏收录该内容

1 篇文章

订阅专栏

本文通过34道练习详细介绍了如何使用MySQL进行复杂查询操作，包括找出每个部门最高薪水的人员、筛选薪水高于部门平均值的员工、计算部门平均薪水等级、查询平均薪水最高的部门等。每道题目提供了思路和解决方案，涵盖了多表联接、子查询、分组聚合等多种技巧，是学习和提升MySQL查询能力的好材料。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用的数据库

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

34道练习
`1、取得每个部门最高薪水的人员名称
思路：第一步.取得最高每个部门的最高薪水
select
deptno,max(sal) as maxsal
from
emp
group by
deptno;
第二步、取得最高薪水的名称
select
e.deptno, e.ename,t.maxsal
from
(select deptno,max(sal) as maxsal from emp group by deptno) t
join
emp e
where
e.deptno = t.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
e.deptno = t.deptno and t.maxsal = e.sal;
总结：一个表全部显示的时候可以选择使用*，还有，join 和on联用
2、哪些人的薪水在部门平均薪水之上
思路：第一步：找出部门的平均薪水
select
deptno,avg(sal) as avgsal
from
emp
group by
deptno;
第二步：找出薪水在部门平均薪水以上的人
select
e.ename,e.sal,t.*
from
emp e
join
(
select
deptno,avg(sal) as avgsal
from
emp
group by
deptno) t
on
e.sal > t.avgsal and e.deptno = t. deptno;
和老师一致(-_-)
3、取得部门中所有人的平均薪水等级
第一步：取得所有人的薪水等级
select
e.deptno,d.grade
from
emp e
join
salgrade d
on
e.sal between d.losal and hisal;
第二步：取得平均等级
select
e.deptno,avg(grade)
from
emp e
join
salgrade d
on
e.sal between d.losal and hisal
group by
e.deptno;
基本一致

4、不准用组函数（max） ,取得最高薪水
方法一：deac降序，用limit
select ename,sal as maxsal from emp order by sal desc limit 1;
方法二：自连接
select
sal
from
emp
where
sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);
5、取得平均薪水最高的部门的部门编号（两种解决方案）
方案一：取得所有部门的平均薪水，降序排列取得第一个
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1 ;
方案二:
select q.deptno from (select deptno,avg(sal) as avgsal from emp group by deptno) q where
q.avgsal = (select max(t.avgsal) as maxsal from (select deptno,avg(sal) as avgsal from emp group by deptno) t);
老师的方法二：select deptno,avg(sal) as avgsal from emp group by deptno having
avg(sal) =(select max(t.avgsal) as maxsal from (select deptno,avg(sal) as avgsal from emp group by deptno) t);
// 平均值：select deptno,avg(sal) as avgsal from emp group by deptno;
// 最大值：select max(t.avgsal) as maxsal from (select deptno,avg(sal) as avgsal from emp group by deptno) t;
6、取得平均薪水最高的部门的部门名称
第一步：取得平均薪水最高的部门编号
select d.dname,w.avgsal from dept d join
(select deptno, avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1) w
on d.deptno = w.deptno;
// 取得最高薪水的部门编号select deptno, avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
select deptno, avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
老师的：
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;

7、求平均薪水的等级最低的部门的部门名称
平均薪水以及部门： select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal asc;
结果： select a.deptno,d.dname,s.grade as avgsalgrade from
( select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal asc) a
join salgrade as s on a.avgsal between s.losal and s.hisal join dept d on d.deptno = a.deptno
order by avgsalgrade limit 1; (有个小问题，不能保证在最小的等级有2个的时候都查出来，有可能会丢失数据)
完整版
开始/好麻烦/ select distinct z.dname,z.avgsalgrade from ( select a.deptno,d.dname,s.grade as avgsalgrade from
( select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal asc) a
join salgrade as s on a.avgsal between s.losal and s.hisal join dept d on d.deptno = a.deptno
order by avgsalgrade) z join ( select a.deptno,d.dname,s.grade as avgsalgrade from
( select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal asc) a
join salgrade as s on a.avgsal between s.losal and s.hisal join dept d on d.deptno = a.deptno
order by avgsalgrade) x on z.avgsalgrade = ( select min(c.avgsalgrade) from ( select a.deptno,d.dname,s.grade as avgsalgrade from
( select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal asc) a
join salgrade as s on a.avgsal between s.losal and s.hisal join dept d on d.deptno = a.deptno
order by avgsalgrade) c); 结束
8、取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人的名字

第一步：找出普通员工
  领导阶级：  select a.empno,a.ename,a.sal from emp  a where a.empno  in (select mgr from emp)；

   平民阶级：     select s.ename from emp s where  s.empno not in

(select v.empno from ( select a.empno,a.ename from emp a where a.empno in (select mgr from emp)) v);
第二步：平民阶级最高薪水：
select max(e.sal) from (select s.ename ,s.sal from emp s where s.empno not in
(select v.empno from ( select a.empno,a.ename from emp a where a.empno in (select mgr from emp)) v)) e;
修改版
select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);

第三步： select ff.* from ( select a.empno,a.ename,a.sal from emp a where a.empno in (select mgr from emp)) ff
where ff.sal > ( select max(e.sal) from (select s.ename ,s.sal from emp s where s.empno not in
(select v.empno from ( select a.empno,a.ename from emp a where a.empno in (select mgr from emp)) v)) e);

9、取得薪水最高的前五名
select ename,sal from emp order by sal desc limit 5;

10、取得薪水最高的地6到第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 s.hisal;

结果：
select t.grade,count(t.grade) from(
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
group by t.grade;

13、面试题：有三张表 s(学生表)，c（课程表），sc(学生选课)
s(sno,sname) 代表（学号，姓名）
c(sno,cno,scgeade) 代表（学号，课程，成绩）
问题：
1，找出没有选过‘黎明’老师的所有学生的名字。
2、列出两门及以上（包括两门）不及格学生姓名以及平均成绩。
3、既学过1号课程又学过2号课程的所有学生的名字。
14、列出所有员工及领导的名字

 king 没有处理：select a.ename,b.ename as '领导' from emp a join emp b on a.mgr = b.empno;

select a.ename,ifnull(b.ename,‘没有领导’) as ‘领导’ from emp a join emp b on a.mgr = b.empno;

处理版：select a.ename,b.ename as ‘领导’ from emp a join emp b on a.mgr = b.empno;
select a.ename,ifnull(b.ename,‘没有领导’) as ‘领导’ from emp a left /* 加一个leftji==即可 */ join emp b on a.mgr = b.empno;

15列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
第一步：找出所有的上级领导
表格完整版：
select a.empno,a.ename,d.dname,a.hiredate,a.deptno,b.empno as ‘领导编号’,b.hiredate as’领导入职时间’ from emp a join emp b
on a.mgr = b.empno and a.hiredate < b.hiredate join dept d on d.deptno = a.deptno;

精简版：
select a.empno as ‘员工编号’,a.ename as ‘员工姓名’,d.dname as ‘部门名称’ from emp a join emp b
on a.mgr = b.empno and a.hiredate < b.hiredate join dept d on d.deptno = a.deptno;
16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select d.dname,e.* from dept d left join emp e on d.deptno = e.deptno order by d.deptno ;

17、列出至少有5个员工的部门
select deptno,count(ename) as num from emp e group by deptno;
解决：
select d.dname from dept d join ( select deptno,count(ename) as num from emp e group by deptno) t
on t.num >= 5 and d.deptno = t.deptno;

18、列出薪资比smith多的所有员工信息
select e.* from emp e where e.sal > (select sal from emp where ename = ‘smith’);
19、列出所有“”clerk“(办事员）的姓名以及部门名称，部门人数
部门人数：
select deptno,count(ename) as countnum from emp group by deptno;
姓名以及部门名称：
select e.ename,e.deptno,d.dname from emp e join dept d on e.job = ‘clerk’ and e.deptno =d.deptno;
结果：
select w.*,r.countnum from ( select e.ename,e.deptno,d.dname from emp e join dept d on
e.job = ‘clerk’ and e.deptno =d.deptno) w join (
select deptno,count(ename) as countnum from emp group by deptno) r on r.deptno = w.deptno;
20、列出最低薪资大于1500的各种工作以及从事工作的全部雇员人数，
按照工作岗位求最小值
第一步：找出同种职业中最低工资大于1500的工作
select job,min(sal) from emp group by job having min(sal) > 1500;
结果：
select e.ename,e.empno,e.job,e.sal from emp e join (
select job,min(sal) from emp group by job having min(sal) > 1500) t on t.job = e.job;
21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
select e.ename,d.dname from dept d join emp e on d.dname = ‘sales’ and e.deptno = d.deptno;
老师版：select ename from emp where deptno = (select deptno from dept where dname = ‘sales’);
22、列出薪资高于公司平均水平的所有员工，所在部门，上级领导，雇员的工资等级
select
e1.ename as ‘员工’,e1.sal,d.dname as ‘部门名称’,e2.ename as ‘领导’,s.grade as ‘工资等级’
from
emp e1
join
dept d
on
d.deptno = e1.deptno
left join
emp e2
on
e1.mgr = e2.empno
join
salgrade s
on
e1.sal between s.losal and s.hisal
where
e1.sal > (select avg(sal) from emp)
;
23、列出与“”scott“”从事相同工作的所有员工及部门名称
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = (select w.job from emp w where w.ename = ‘scott’);
老师版：
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 e.deptno,e.sal from emp e where e.deptno <> 30 and e.sal in(select d.sal from emp d where d.deptno = 30);
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
d.deptno = e.deptno
where
e.sal > (select max(sal) from emp group by deptno having deptno = 30);
老师版：

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);
此处不需要group by

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

select
e.deptno,count(e.ename),ifnull(avg(e.sal),0) as ‘平均工资’,ifnull(avg(timeStampDiff(month,hiredate,now())),0)
from
emp e
group by
e.deptno;
27、列出所有员工的姓名、部门名称和工资。
select
e.ename,d.dname,e.sal
from
emp e
join
dept d
on
d.deptno = e.deptno;
28、列出所有部门的详细信息和人数

select
d.*,t.num
from
dept d
left join
( select
e.deptno,count(e.ename) as num
from
emp e
group by
e.deptno) t
on
t.deptno = d.deptno;
29、列出各种工作的最低工资及从事此工作的雇员姓名

select
e.ename,e.job,e.sal
from
emp e
join
(select
job,min(sal) as sal
from
emp
group by
job) t

on
e.job = t.job and e.sal = t.sal ;

30、列出各个部门的 MANAGER( 领导) 的最低薪金
select
deptno,min(sal)
from
emp
where
job = ‘manager’
group by
deptno;
31、列出所有员工的年工资, 按年薪从低到高排序
select
ename,sal *12 as yearsal
from
emp
order by
yearsal;
32、求出员工领导的薪水超过3000的员工名称与领导
select ename from emp where sal > 3000;

33、求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数
select d.deptno,d.dname from dept d where d.dname like ‘%s%’;
select e.deptno,ifnull(sum(e.sal),0),count(e.ename) from emp e join (select d.deptno,d.dname from dept d where d.dname like ‘%s%’) t on e.deptno = t.deptno group by e.deptno;
34、给任职日期超过 30 年的员工加薪 10%.
update emp set sal*1.1 where(timestampdiff(year,hiredate,now()) > 30);
`