MySql_34道经典Sql试题

MySql_34道经典Sql试题

 

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/xiaouncle/article/details/79939040

如果能流畅地把这34道题解答出来的话，那对于大多数开发者来说就不会再为写Sql语句而烦恼，写复杂的Sql语句时要分步骤完成，逐步击破最终就能得到你想要的东西。看完这两篇文章不代表你已经学会了，这只是个小小的开端而已。

准备工作

-- Employee中有Bonus=null记录
-- in(nul1,200)只能查询到Bonus=200的记录
SELECT * FROM test.employee where Bonus in(null,200);

-- Employee中有Bonus=null记录
-- not in(200,40)查询结果会自动把Bonus=null的记录过滤掉
-- not in(200,40,null)查询结果为空
SELECT * FROM test.employee where Bonus not in(200,40);

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

一、取得每个部门拿最高薪水的人员名称

1、先取得各部门的最高薪水 
2、再获取各部门拿最高薪水的人员名称 
3、注意：每个部门拿最高薪水的有可能是多个人

select Employee.EmployeeName,Employee.DeptNo,Employee.Salary from Employee
inner join (select DeptNo,max(Salary)as maxSalary from Employee group by DeptNo) as b
on Employee.DeptNo=b.DeptNo and Employee.Salary=b.maxSalary;

• 1
• 2
• 3

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

1、先取得各部门的平均薪水 
2、再获取薪水在部门平均薪水之上的人

/*方法1*/
select Employee.EmployeeName,Employee.DeptNo,Employee.Salary from Employee
inner join (select DeptNo,avg(Salary)as avgSalary from Employee group by DeptNo) as b
on Employee.DeptNo=b.DeptNo and Employee.Salary>b.avgSalary;

/*方法2*/
select Employee.EmployeeName,Employee.DeptNo,Employee.Salary from Employee
inner join (select DeptNo,avg(Salary)as avgSalary from Employee group by DeptNo) as b
on Employee.DeptNo=b.DeptNo
where Employee.Salary>b.avgSalary

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

三、取得部门中所有人的平均薪水等级

1、先取得各部门的平均薪水 
2、再获取各部门的平均薪水的等级

select a.DeptNo,a.avgSalary,b.Grade
from (select DeptNo,avg(Salary)as avgSalary from Employee group by DeptNo) as a
left join SalaryGrade as b
on a.Salary between b.Lowest and b.Highest;

• 1
• 2
• 3
• 4

四、不用max()取得最高薪水

1、按照薪水将序排列，取第一项

select Id,EmployeeName,Salary from Employee order by Salary desc
limit 0,1;

• 1
• 2

1、获取非最高薪水的员工Id（自己的薪水<其他人的薪水） 
2、查询Id不在非最高薪水员工Id列表的数据

select Id,EmployeeName,Salary from Employee
where Id not in(
    select distinct a.Id from Employee as a
    inner join Employee as b
    on a.Salary<b.Salary
);

• 1
• 2
• 3
• 4
• 5
• 6

五、取得平均薪水最高的部门的部门编号

1、按照部门平均薪水将序排列，取第一项 
2、注意：这种方式获取到的确实是平均薪水最高的部门，但是如果有多个部门的平均薪水并列最高时，这种方式就不适用了

select DeptNo,avg(Salary)as avgSalary from Employee group by DeptNo order by avgSalary desc
limit 0,1;

• 1
• 2

1、先获取各部门中最高的平均薪水 
2、再获取部门平均薪水等于部门最高平均薪水的部门信息

select DeptNo,avg(Salary)as avgSalary from Employee group by DeptNo
having avgSalary=(
    select avg(Salary)as maxAvgSalary from Employee group by DeptNo order by maxAvgSalary desc
    limit 0,1
);

• 1
• 2
• 3
• 4
• 5

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

1、先获取各部门中最高的平均薪水 
2、再获取部门平均薪水等于部门最高平均薪水的部门编号 
3、最后获取平均薪水最高的部门的部门名称

/*方法1*/
select a.DeptNo,b.DName,a.avgSalary from (select DeptNo,avg(Salary)as avgSalary from Employee group by DeptNo
having avgSalary=(
    select avg(Salary)as maxAvgSalary from Employee group by DeptNo order by maxAvgSalary desc
    limit 0,1
))as a
inner join Dept as b
on a.DeptNo=b.DeptNo;

/*方法2*/
select Employee.DeptNo,b.DName,avg(Salary)as avgSalary from Employee
inner join dept as b on Employee.DeptNo=b.DeptNo
group by Employee.DeptNo,b.DName
having avgSalary=(select avg(Salary)as maxAvgSalary from Employee group by DeptNo order by maxAvgSalary desc
                  limit 0,1);

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

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

1、先求出各部门中最低平均薪水的等级 
2、再求出各部门中平均薪水等级=最低平均薪水等级的部门编号 
3、最后求出各部门中平均薪水等级=最低平均薪水等级的部门名称

select a.DeptNo,c.DName,a.avgSalary,b.Grade from(select DeptNo,avg(Salary)as avgSalary from Employee group by DeptNo)as a
inner join salarygrade as b on a.avgSalary between b.Lowest and b.Higest
inner join Dept as c on a.DeptNo=c.DeptNo
group by DeptNo,b.Grade
having b.Grade=(select b.Grade from(select DeptNo,avg(Salary)as avgSalary from Employee group by DeptNo order by avgSalary asc limit 0,1)as a
                inner join salarygrade as b on a.avgSalary between b.Lowest and b.Higest)
order by avgSalary asc;

• 1
• 2
• 3
• 4
• 5
• 6
• 7

八、求比普通员工的最高薪水还要高的经理姓名

1、先求出普通员工的最高薪水 
2、在求出比普通员工的最高薪水还要高的经理姓名

select EmployeeName,Salary from Employee
where Job='Manager' and Salary>(select max(Salary)as maxWorkerSalary from Employee where Job='Worker');

• 1
• 2

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

1、limit 0,5，0表示从下标=0的数据开始取（第1条），5表示取5条数据

select EmployeeName,Salary from Employee
where Job='Worker'
order by Salary desc
limit 0,5;

• 1
• 2
• 3
• 4

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

1、limit 5,5，第一个5表示从下标=5的数据开始取（第6条），第二个5表示取5条数据

select EmployeeName,Salary from Employee
where Job='Worker'
order by Salary desc
limit 5,5;

• 1
• 2
• 3
• 4

十一、取得最后入职的五名员工

1、可以根据时间来排序

select EmployeeName,Salary,CreateDate from Employee
order by CreateDate desc
limit 0,5;

• 1
• 2
• 3

十二、取得每个薪水等级有多少名员工

1、先求每个员工的薪水等级 
2、再求每个薪水等级有多少名员工

select Grade,count(*) from (select EmployeeName,Salary,b.Grade from Employee
                            inner join SalaryGrade as b
                            on Employee.Salary between b.Lowest and b.Highest)as a
group by Grade
order by Grade asc;

• 1
• 2
• 3
• 4
• 5

十三、有三个表S(学生表)、C(课程表)、SC(学生选课表)

S（SNo, SName）代表（学号, 姓名） 
C（CNo, CName, CTeacher）代表（课号, 课名, 老师） 
SC（SNo, CNo, Score）代表（学号, 课号, 成绩） 
1、找出没选过”张国荣”老师的所有学生姓名 
1.1、找出选过”张国荣”老师的学生学号 
1.2、找出没选过”张国荣”老师的学生姓名

select SNo,SName from S
where SNo not in (select distinct SNo from SC
                  inner join C on SC.CNo=C.CNo and C.CTeacher='张国荣'
                  where SNo is not null);

• 1
• 2
• 3
• 4

2、列出2门以上（含2门）不及格学生姓名及平均分 
2.1、先找出不及格课程数>1的所有学生编号 
2.2、再找出不及格课程数>1的所有学生姓名及平均分

select SC.SNo,S.SName,avg(Score) from SC
inner join S on SC.SNo=S.SNo
where SNo in(select SNo from SC where Score<60
             group by SNo
             having count(*)>1)
group by SC.SNo,S.SName;

• 1
• 2
• 3
• 4
• 5
• 6

3、既学过1号课程也学过2号课程的学生 
3.1、先找出学过2号课程的学生编号集Set1 
3.2、再找出学过1号课程的学生编号，这些学生编号必须也存在于Set1中

select SC.SNo,S.SName from SC
inner join S on SC.SNo=S.SNo
where CNo=1 and SC.SNo in(select SNo from SC where CNo=2);

• 1
• 2
• 3

十四、列出所有员工及其领导的名字

1、表可以自连接

select  Employee.EmployeeName,b.EmployeeName as LeaderName from Employee
left join Employee as b
on Employee.LeaderId=b.Id;

• 1
• 2
• 3

十五、列出受雇日期早于其直接上级领导的所有员工编号、姓名

1、表可以自连接

select  Employee.EmployeeName,Employee.CreateDate,b.EmployeeName as LeaderName,b.CreateDate as LeaderCreateDate from Employee
left join Employee as b
on Employee.LeaderId=b.Id and Employee.CreateDate<b.CreateDate;

• 1
• 2
• 3

十六、列出部门名称和这些部门的员工信息，同时列出没有员工的部门名称

1、左外连接以左边表中的数据为主

select Dept.DeptNo,Dept.DName,b.EmployeeName from Dept
left join Employee as b on Dept.DeptNo=b.DeptNo;

• 1
• 2

十七、列出至少有三个员工的所有部门名称

1、group by和having联合使用

select DeptNo,count(*)as employeeCount from Employee
group by DeptNo
having employeeCount>2;

十八、列出薪水比”贾跃亭”高的所有员工信息
1、先求出‘贾跃亭’的工资

select EmployeeName,EmployeeCode,Salary from employee
where Salary>(select Salary from Employee where EmployeeName='贾跃亭');
1
2
十九、列出所有Worker的姓名及其部门名称、部门人数
1、先找出所有部门的名称及其部门人数

select EmployeeName,b.DeptNo,b.DName,b.employeeCount from Employee
left join(select Employee.Id,Employee.DeptNo,b.DName,count(*)as employeeCount from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo
group by Employee.Id,Employee.DeptNo,b.DName)as b
on Employee.Id=b.Id
where Employee.Job='Worker';
1
2
3
4
5
6
二十、列出最低薪水>1500的各种工作及从事此工作的全部雇员人数
1、找出各种工作的全部雇员人数
2、找出最低薪水>1500的各种工作

select Employee.Job,min(Salary)as minSalary,b.employeeCount from Employee
inner join(select Job,count(*)as employeeCount from Employee group by Job)as b
on Employee.Job=b.Job
group by Employee.Job
having minSalary>1500;
1
2
3
4
5
二十一、列出在财务部工作的员工姓名，假定不知道财务部的编号
1、先找出财务部的编号

select * from Employee
where DeptNo=(select DeptNo from Dept where DName='财务部');
1
2
二十二、列出薪水高于公司平均薪水的所有员工，所在部门、薪水等级
1、先求出公司平均薪水
2、求出所有员工的所在部门、薪水等级

select Employee.EmployeeName,Employee.DeptNo,b.DName,Employee.Salary,c.Grade from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo
inner join SalaryGrade as c on employee.Salary between c.Lowest and c.Highest
where Employee.Salary>(select avg(Salary) from Employee);
1
2
3
4
二十三、列出与”安禄山”从事相同工作的所有员工及部门名称
1、先找出‘安禄山’的工作岗位

select Employee.EmployeeName,Employee.Job,Employee.DeptNo,b.DName from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo
where Job=(select Job from Employee where EmployeeName='安禄山');
1
2
3
二十四、列出薪水等于财务部中员工薪水的其他员工的姓名和薪水
1、先找出财务部中所有员工的薪水

select EmployeeName,Salary from Employee
where Salary in(select Salary from Employee where DeptNo='101') and DeptNo != '101';
1
2
二十五、列出薪水大于财务部所有员工薪水的员工的姓名、薪水、部门名称
1、找出财务部中的最高薪水

select Employee.EmployeeName,Employee.Salary,Employee.DeptNo,b.DName from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo
where Salary >(select max(Salary) from Employee where DeptNo='101');
1
2
3
二十六、列出每个部门的员工数量、平均薪水和平均服务期限
1、获取系统当前时间now()
2、日期转换为天数to_days()

select DeptNo,count(*),avg(Salary),avg(to_days(now())-to_days(CreateDate)) from Employee
group by DeptNo;
1
2
二十七、列出所有员工的姓名、部门名称和工资
1、常见的内连接

select Employee.EmployeeName,Employee.DeptNo,b.DName,Employee.Salary from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo;
1
2
二十八、列出所有部门的详细信息和人数
1、先求出所有部门的人数
2、IFNULL(expr1,expr2) 如果expr1是NULL，IFNULL()返回expr2，否则返回expr1

select Dept.DeptNo,DName,Location,ifnull(b.employeeCount,0) from Dept
left join (select DeptNo,count(*)as employeeCount from Employee group by DeptNo)as b
on Dept.DeptNo=b.DeptNo;
1
2
3
二十九、列出各种工作的最低工资及从事此工作的员工姓名
1、找出各工作的最低工资

select Employee.EmployeeName,Employee.Job,Employee.Salary from Employee
inner join (select Job,min(Salary)as minSalary from Employee group by Job)as b
on Employee.Job=b.Job and Employee.Salary=b.minSalary;
1
2
3
三十、列出各部门Manager的最低薪水
1、按照部门分类
2、工作岗位=’Manager’

select DeptNo,min(Salary) from Employee
where Job='Manager'
group by DeptNo;
1
2
3
三十一、列出所有员工的年薪，按年薪升序排列
1、字段可以进行运算

select EmployeeName,(Salary+ifnull(Bonus,0))*12 as YearSalary from Employee
order by YearSalary asc;
1
2
三十二、求出员工领导的薪水超过7000的员工名称和领导名称
1、先把员工、员工领导、员工领导的薪水关联起来

select Employee.EmployeeName,b.EmployeeName as LeaderName from Employee
inner join Empoloyee as b on Employee.LeaderId=b.Id
where b.Salary >7000;
1
2
3
三十三、求出部门名称中带”务”字符的工资合计和部门人数
1、求出所有部门的部门名称、工资合计、部门人数

select Employee.DeptNo,b.DName,sum(Salary),count(*) from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo
where b.DName like '%务%'
group by Employee.DeptNo,b.DName;
1
2
3
4
三十四、给任职超过1年的员工加薪10%
1、修改表中的信息

update Employee set Salary=Salary*1.1
where (to_days(now())-to_days(CreateDate))>365