MYSQL基礎上機練習題（七）選取特殊值綜合應用

2024-07-24 13:00:30

字體：大中小

來源：轉(zhuǎn)載

供稿：網(wǎng)友

一、實驗目的：

各種選取特殊值的綜合應用

二、實驗內(nèi)容：

選取一些特殊值如最大值、最小值、連續(xù)值、缺失值、重復值等

三、題目：

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

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

3.選取各個部門中每個部門的員工比部門經(jīng)理薪水高的人的姓名及其薪酬水平

4.選取企業(yè)中連續(xù)遲到6天的人

5.選取還未錄入薪酬水平的員工名稱

6.選取電話號碼重復的員工姓名及其電話號碼

四、內(nèi)容：

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

一般來說，選取工作年限最高的人，在MySQL中可以簡單地用MAX()函數(shù)求出，但當要同時輸出該人的姓名，則容易出錯，Mysql會直接選取各個部門中第一行數(shù)據(jù)的人的姓名，而非對應工作年限最高的人，如下：

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

得出的結(jié)果是： 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

然后把結(jié)果輸出到外層，進行比較

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

得出結(jié)果： MYSQL選取最小值

3.選取各個部門中每個部門的員工比部門經(jīng)理薪水高的人的姓名及其薪酬水平

由于部門經(jīng)理也是員工之一，因此需要連接部門經(jīng)理以及員工表，以作對比

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表，代表各個部門經(jīng)理的薪酬水平

(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(#各個部門經(jīng)理的薪酬水平)MUSING(DepartmentID)WHERE E.InCome > M.InComeORDER BY M.DepartmentID

通過對比，可以得出結(jié)果： MYSQL對比應用