MySql_34道经典Sql试题（一）

如果能流畅地把这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、注意：每个部门拿最高薪水的有可能是多个人

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、再获取薪水在部门平均薪水之上的人

/*方法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、再获取各部门的平均薪水的等级

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;

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

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

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

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、注意：这种方式获取到的确实是平均薪水最高的部门，但是如果有多个部门的平均薪水并列最高时，这种方式就不适用了

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

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、最后获取平均薪水最高的部门的部门名称

/*方法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、最后求出各部门中平均薪水等级=最低平均薪水等级的部门名称

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、在求出比普通员工的最高薪水还要高的经理姓名

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

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

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、limit 5,5，第一个5表示从下标=5的数据开始取（第6条），第二个5表示取5条数据

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

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

1、可以根据时间来排序

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

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

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;

十三、有三个表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);

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;

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、表可以自连接

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

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

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、左外连接以左边表中的数据为主

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

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

1、group by和having联合使用

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