4.1 MySQL 實戰(zhàn)
感謝?https://blog.csdn.net/u011490595/article/details/82050325
讓我終于連接上了navicat，終于不用看性冷淡的命令行了。
#學(xué)習(xí)內(nèi)容#
數(shù)據(jù)導(dǎo)入導(dǎo)出
將之前創(chuàng)建的任意一張MySQL表導(dǎo)出,且是CSV格式
再將CSV表導(dǎo)入數(shù)據(jù)庫
項目七: 各部門工資最高的員工（難度：中等）
創(chuàng)建 Employee?表米罚，包含所有員工信息，每個員工有其對應(yīng)的?Id, salary 和 department Id.
創(chuàng)建 Department?表澄干，包含公司所有部門的信息誉察。
編寫一個 SQL 查詢，找出每個部門工資最高的員工赘被。例如悴侵，根據(jù)上述給定的表格瞧剖，Max 在 IT 部門有最高工資，Henry 在 Sales 部門有最高工資畜挨。（也可以使用RANK函數(shù)）
SELECT d. NAME AS Department, e. NAME AS Employee, e.Salary FROM Employee e
JOIN (SELECT Max(Salary) AS Salary FROM Employee GROUP BY DepartmentId)
?AS g? ON? e.Salary = g.Salary LEFT JOIN Department d ON? e.DepartmentId = d.Id;
項目八: 換座位（難度：中等）
小美是一所中學(xué)的信息科技老師筒繁，她有一張?seat?座位表，平時用來儲存學(xué)生名字和與他們相對應(yīng)的座位 id巴元。
其中縱列的?id?是連續(xù)遞增的
小美想改變相鄰倆學(xué)生的座位毡咏。如果學(xué)生人數(shù)是奇數(shù)，則不需要改變最后一個同學(xué)的座位逮刨。
SELECT (CASE WHEN MOD(id,2)=1 AND id != (SELECT COUNT(*)FROM Seat) THEN id+1
WHEN MOD(id,2)=0 THEN id-1 ELSE id END) AS id, student From Seat
ORDER BY id ASC;
項目九: 分數(shù)排名（難度：中等）
編寫一個 SQL 查詢來實現(xiàn)分數(shù)排名呕缭。如果兩個分數(shù)相同，則兩個分數(shù)排名（Rank）相同修己。請注意恢总，平分后的下一個名次應(yīng)該是下一個連續(xù)的整數(shù)值。換句話說睬愤，名次之間不應(yīng)該有“間隔”片仿。
SELECT Score,(SELECT COUNT(*) FROM (SELECT DISTINCT Score S FROM score) AS s2
WHERE? S >= Score) AS 'Rank' FROM score ORDER BY score DESC;
項目十：行程和用戶（難度：困難）
Trips?表中存所有出租車的行程信息。每段行程有唯一鍵 Id尤辱，Client_Id 和?Driver_Id 是?Users?表中 Users_Id 的外鍵砂豌。Status 是枚舉類型，枚舉成員為 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)光督。 enum可以用來約束輸入的值阳距。
CREATE TABLE Trips (Id INT auto_increment PRIMARY KEY, Client_Id INT NOT NULL, Driver_Id INT NOT NULL, City_Id INT NOT NULL, Status ENUM ('completed','cancelled_by_driver','cancelled_by_client'),Request_all DATE NOT NULL);
INSERT INTO Trips VALUES (NULL,1,10,1,'completed','2013-10-1');
INSERT INTO Trips VALUES (NULL,2,11,1,'cancelled_by_driver','2013-10-01'),
(NULL,3,12,6,'completed','2013-10-01'),(NULL,4,13,6,'cancelled_by_client','2013-10-01'),(NULL,1,10,1,'completed','2013-10-02'),
(NULL,2,11,6,'completed','2013-10-02'),(NULL,3,12,6,'completed','2013-10-02'),
(NULL,2,12,12,'completed','2013-10-03'),(NULL,3,10,12,'completed','2013-10-03'),(NULL,4,13,12,'cancelled_by_driver','2013-10-03');
Users?表存所有用戶。每個用戶有唯一鍵 Users_Id结借。Banned 表示這個用戶是否被禁止筐摘，Role 則是一個表示（‘client’, ‘driver’, ‘partner’）的枚舉類型。
CREATE TABLE Users (Users_Id INT NOT NULL PRIMARY KEY, Banned enum('Yes','No'), Role enum('client','driver','partner'));
INSERT INTO Users VALUES (1,'No','client'),(2,'Yes','client'),(3,'No','client'),(4,'No','client'), (10,'No','driver'),(11,'No','driver'),(12,'No','driver'),(13,'No','driver');
SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM
(SELECT Name,Salary, DepartmentId,
RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS 'rank'
FROM Employee) AS e
LEFT JOIN Department d ON e.DepartmentId=d.Id WHERE e.rank<=3;
SELECT t.Request_all AS Day, ROUND(SUM(CASE WHEN Status ='completed' THEN 0 ELSE 1 END)/COUNT(*),2) AS 'Cancellation Rate'
FROM Trips t LEFT JOIN Users U ON t.Client_Id=U.Users_Id LEFT JOIN Users U2 on t.Driver_Id=U2.Users_Id
where U.Banned='No' AND U2.Banned='No' AND t.Request_all BETWEEN '2013-10-01'and'2013-10-03' GROUP BY t.Request_all;
項目十一：各部門前3高工資的員工（難度：中等）
將項目7中的 employee 表清空，重新插入以下數(shù)據(jù)（其實是多插入5,6兩行）：
+----+-------+--------+--------------+
| Id | Name? | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1? | Joe? | 70000? | 1? ? ? ? ? ? |
| 2? | Henry | 80000? | 2? ? ? ? ? ? |
| 3? | Sam? | 60000? | 2? ? ? ? ? ? |
| 4? | Max? | 90000? | 1? ? ? ? ? ? |
| 5? | Janet | 69000? | 1? ? ? ? ? ? |
| 6? | Randy | 85000? | 1? ? ? ? ? ? |
+----+-------+--------+--------------+
編寫一個?SQL 查詢咖熟，找出每個部門工資前三高的員工圃酵。
SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM (SELECT Name,Salary, DepartmentId,? RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS 'rank'? FROM Employee) AS e LEFT JOIN Department d ON e.DepartmentId=d.Id WHERE e.rank<=3
項目十二 分數(shù)排名 - （難度：中等）
依然是昨天的分數(shù)表，實現(xiàn)排名功能球恤，但是排名是非連續(xù)的
+-------+------+
| Score | Rank |
+-------+------+
| 4.00? | 1? ? |
| 4.00? | 1? ? |
| 3.85? | 3? ? |
|?3.65? | 4? ? |
| 3.65? | 4? ? |
| 3.50? | 6? ? |
+-------+------
在這里可以用Rank函數(shù)辜昵，也可以用上面的方式荸镊。
SELECT Score, RANK() OVER (ORDER BY Score DESC) AS 'Rank' FROM score
