MYSQL基础上机练习题(七)选取特殊值综合应用

3/7/2017来源:SQL技巧人气:740

一、实验目的:

各种选取特殊值的综合应用

二、实验内容:

选取一些特殊值如最大值、最小值、连续值、缺失值、重复值等

三、题目:

1.选取各部门工作年限最高的人的姓名以及其工作年限

2.选取各部门薪水最低的人的姓名以及其薪酬水平

3.选取各个部门中每个部门的员工比部门经理薪水高的人的姓名及其薪酬水平

4.选取企业中连续迟到6天的人

5.选取还未录入薪酬水平的员工名称

6.选取电话号码重复的员工姓名及其电话号码

四、内容:

1.选取各部门工作年限最高的人的姓名以及其工作年限

一般来说,选取工作年限最高的人,在MySQL中可以简单地用MAX()函数求出,但当要同时输出该人的姓名,则容易出错,Mysql会直接选取各个部门中第一行数据的人的姓名,而非对应工作年限最高的人,如下:

SELECT Name, MAX(WorkYear), DepartmentName FROM Employees LEFT JOIN Departments USING(DepartmentID) GROUP BY DepartmentID

得出的结果是: MYSQL最大值查询 然而回顾一下Employees表,名字为“张扬”的员工,工作年龄只是2年而非6年 MYSQL最大值查询

因此为了防止这类型错误,需要使用嵌套查询

SELECT Name, DepartmentName, Employees.WorkYear FROM Employees, Departments, (SELECT MAX(WorkYear) AS WorkYear, DepartmentId FROM Employees GROUP BY DepartmentId)T WHERE Employees.WorkYear = T.WorkYear AND Departments.DepartmentID = T.DepartmentID AND Departments.DepartmentID = Employees.DepartmentID ORDER BY DepartmentName

这里的子查询用于确定每个部门最高的工作年限:

SELECT MAX(WorkYear) AS WorkYear, DepartmentId FROM Employees GROUP BY DepartmentId

确定了薪酬水平后,再从Employees表中找出该薪酬水平对应的人的名称以及部门ID,再通过部门ID在部门表中找到部门名称:

SELECT Name, DepartmentName, Employees.WorkYear FROM Employees, Departments, (#子查询)T WHERE Employees.WorkYear = T.WorkYear AND Departments.DepartmentID = T.DepartmentID AND Departments.DepartmentID = Employees.DepartmentID ORDER BY DepartmentName

MYSQL选取最大值

2.选取各部门薪水最低的人的姓名以及其薪酬水平

同理,选取各个部门的薪水最低的人的姓名及其薪酬,使用的也是自嵌套查询而不能直接查询,而由于这里涉及三个表,则需要连接三个表进行嵌套查询

SELECT S1.InCome, Name, DepartmentName FROM Employees E1 LEFT JOIN Salary S1 USING(EmployeeID) LEFT JOIN Departments D USING(DepartmentID), (SELECT MIN(InCome) AS InCome, DepartmentID FROM Salary S2 LEFT JOIN Employees E2 USING(EmployeeID) GROUP BY E2.DepartmentID)T WHERE T.DepartmentID = E1.DepartmentID AND T.InCome = S1.InCome

原理与第一题相同,在子查询部分先查询到各个部门中薪水最低的薪水水平是多少

SELECT MIN(InCome) AS InCome, DepartmentID FROM Salary S2 LEFT JOIN Employees E2 USING(EmployeeID) GROUP BY E2.DepartmentID

然后把结果输出到外层,进行比较

SELECT S1.InCome, Name, DepartmentName FROM Employees E1 LEFT JOIN Salary S1 USING(EmployeeID) LEFT JOIN Departments D USING(DepartmentID), (#子查询)T WHERE T.DepartmentID = E1.DepartmentID AND T.InCome = S1.InCome

得出结果: MYSQL选取最小值

3.选取各个部门中每个部门的员工比部门经理薪水高的人的姓名及其薪酬水平

由于部门经理也是员工之一,因此需要连接部门经理以及员工表,以作对比

SELECT E.Name AS Employee, E.InCome, M.Name AS Manager, M.InCome FROM (SELECT DepartmentID, Name, InCome FROM Employees E1 LEFT JOIN Salary S1 USING(EmployeeID))E LEFT JOIN (SELECT M2.DepartmentID, M2.EmployeeID, E2.Name, InCome FROM Managers M2 LEFT JOIN Salary S2 USING(EmployeeID) LEFT JOIN Employees E2 USING(EmployeeID))M USING(DepartmentID) WHERE E.InCome > M.InCome ORDER BY M.DepartmentID

①首先,需要连接Employees和Salary两个表,成为E表,代表全体员工的薪酬水平

(SELECT DepartmentID, Name, InCome FROM Employees E1 LEFT JOIN Salary S1 USING(EmployeeID))E

得出以下的表: MYSQL对比 ②然后需要连接Managers和Salary表,成为M表,代表各个部门经理的薪酬水平

(SELECT M2.DepartmentID, M2.EmployeeID, E2.Name, InCome FROM Managers M2 LEFT JOIN Salary S2 USING(EmployeeID) LEFT JOIN Employees E2 USING(EmployeeID))M

得出以下的表: MYSQL对比应用

然后把E表和M表通过相同的部门编号进行连接

SELECT E.Name AS Employee, E.InCome, M.Name AS Manager, M.InCome FROM (#全体员工的薪酬水平)E LEFT JOIN (#各个部门经理的薪酬水平)M USING(DepartmentID) WHERE E.InCome > M.InCome ORDER BY M.DepartmentID

通过对比,可以得出结果: MYSQL对比应用

4.选取企业中连续迟到6天的人

5.选取还未录入薪酬的员工名称

使用ISNULL( )函数可以直接找出还未录入薪酬的员工名称

SELECT Name FROM Employees LEFT JOIN Salary USING(EmployeeID) WHERE ISNULL(InCome)

MYSQL缺失值

6.选取电话号码重复的员工姓名及其电话号码

选取重复的电话号码时,可以选择通过电话号码来连接两个Employees表,且连接后,员工的ID是不一样的,则可以选择不同员工的重复的电话号码

SELECT DISTINCT E1.Name, E1.PhoneNumber FROM Employees E1 LEFT JOIN Employees E2 USING(PhoneNumber) WHERE E1.EmployeeID <> E2.EmployeeID

MYSQL选取重复值