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

2024-07-24 13:00:30

字体：大中小

来源：转载

供稿：网友

一、实验目的：

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

二、实验内容：

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

三、题目：

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

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

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

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

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

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

四、内容：

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

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

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

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

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

SELECT Name, DepartmentName, Employees.WorkYearFROMEmployees, Departments,(SELECT MAX(WorkYear) AS WorkYear, DepartmentId FROM Employees GROUP BY DepartmentId)TWHERE Employees.WorkYear = T.WorkYearAND Departments.DepartmentID = T.DepartmentIDAND Departments.DepartmentID = Employees.DepartmentIDORDER BY DepartmentName

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

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

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

SELECT Name, DepartmentName, Employees.WorkYearFROMEmployees, Departments,(#子查询)TWHERE Employees.WorkYear = T.WorkYearAND Departments.DepartmentID = T.DepartmentIDAND Departments.DepartmentID = Employees.DepartmentIDORDER BY DepartmentName

MYSQL选取最大值

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

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

SELECT S1.InCome, Name, DepartmentNameFROM Employees E1LEFT JOIN Salary S1 USING(EmployeeID)LEFT JOIN Departments D USING(DepartmentID),(SELECT MIN(InCome) AS InCome, DepartmentIDFROM Salary S2LEFT JOIN Employees E2 USING(EmployeeID)GROUP BY E2.DepartmentID)TWHERE T.DepartmentID = E1.DepartmentIDAND T.InCome = S1.InCome

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

SELECT MIN(InCome) AS InCome, DepartmentIDFROM Salary S2LEFT JOIN Employees E2 USING(EmployeeID)GROUP BY E2.DepartmentID

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

SELECT S1.InCome, Name, DepartmentNameFROM Employees E1LEFT JOIN Salary S1 USING(EmployeeID)LEFT JOIN Departments D USING(DepartmentID),(#子查询)TWHERE T.DepartmentID = E1.DepartmentIDAND T.InCome = S1.InCome

得出结果： MYSQL选取最小值

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

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

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

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

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

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

(SELECT M2.DepartmentID, M2.EmployeeID, E2.Name, InCome FROM Managers M2LEFT 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(#全体员工的薪酬水平)ELEFT JOIN(#各个部门经理的薪酬水平)MUSING(DepartmentID)WHERE E.InCome > M.InComeORDER BY M.DepartmentID

通过对比，可以得出结果： MYSQL对比应用