MySQL 入门实践项目（3/4）

MySQL 实战

学习内容

数据导入导出

将之前创建的任意一张MySQL表导出,且是CSV格式

再将CSV表导入数据库

作业

项目七: 查询各部门工资最高的员工（难度：中等）

创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id

CREATE TABLE Employee (
Id 			 INT NOT NULL PRIMARY KEY,
Name		 VARCHAR(20) NOT NULL,
Salary 		 INT NOT NULL ,
DepartmentId INT
);

INSERT INTO Employee(Id,Name,Salary,DepartmentId) 
VALUES
(1,'Joe',70000,1),
(2,'Henry',80000,2),
(3,'Sam',60000,2),
(4,'Max',90000,1);

创建Department 表，包含公司所有部门的信息

CREATE TABLE Department (
Id   INT NOT NULL PRIMARY KEY,
Name VARCHAR(20) NOT NULL
);

INSERT INTO Department(Id,Name) 
VALUES
(1,'IT'),
(2,'Sales');

思路：先查询出每个部门最高的工资，然后再通过JOIN匹配部门信息

SELECT d.Name,e.Name as Employee,e.Salary
	FROM Department d
	JOIN Employee e
	  ON e.DepartmentId = d.Id
	 AND e.Salary=(SELECT MAX(Salary) 
	 				 FROM Employee 
	 			    WHERE Employee.DepartmentId=Department.Id);

项目八: 改变相邻俩学生的座位（如果学生人数是奇数，则不需要改变最后一个同学的座位）

解法1：从最后结果来看就是 ：
①id为偶数的需要往前挪
②id为奇数的需要往后挪
③再考虑最后一位是奇数还是偶数，奇数不变（发现偶数的情况已经包含在前面了）
通过 id%2=1 来判断是奇数。
这里需要注意的地方是，最后一位，我们在判断奇数往后挪的时候，是不包含最后一位的。所以在②的时候要限定 id 小于总个数。
SELECT *
	FROM(
		//遇到偶数就往上移一个位置
		SELECT id-1 AS id,student
			FROM seat WHERE id%2 = 0
			
		UNION
		//遇到奇数就往下移一个位置，不包含最后一个位置
		SELECT id+1 AS id,student
			FROM seat 
		   WHERE id%2 =1
			 AND (id+1) <= (SELECT COUNT(*) FROM seat)
				
		UNION
		// 处理最后一个位置，这里只考虑奇数情况，保持不变（偶数已经在第一步里处理了）
		SELECT id AS id,student
			FROM seat 
		   WHERE id%2 =1
			 AND (id+1) > (SELECT COUNT(*) FROM seat) AS ranks
ORDER BY id ASC;
	
解法2：将SELECT 语句括号中内容的作用是为了更新original id 并将以id作为更新后的别名

mysql> SELECT(
    -> CASE
    -> WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) --id为奇数且为最后一个值时
    -> THEN id --其值不变，为本身
    -> WHEN MOD(id,2) = 1
    -> THEN id+1
    -> ELSE id-1
    -> END)
    -> as id, student
    -> FROM seat

项目九: 分数排名（难度：中等）

实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

这里的排名是连续排名。将分数去重后的清单排名就是最终排名。然后将这个排名JOIN原始数据。
问题在于，怎么在去重的分数清单增加一列ID，这个ID会随着数据自动增加。这个可以通过参数化查询搞定，但是我们还没学，所以pass。

然后就有个取巧的办法。我们还是需要获取去重的分数清单，但是不需要排名ID了：
①  原始表里的最大值。 分数清单只取最大值，然后COUNT(*)，这样就只有1
②  原始表里的第二大值。 分数清单取最大值和第二大值，然后COUNT(*), 这样就是2
以此内推。
在code里就是 分数清单里的score要大于等于原始表的某个具体的值。

这个写法的缺点是，每一个原始表socre列的值，都需要重新扫描去重后的分数清单，在数据表大的情况下，性能会很差。

SELECT   score
		   ,SELECT COUNT(DISTINCT score)
		     FROM scores
		    WHERE score >= s.score
             ) AS Ranks
    FROM  scores s
ORDER BY  score DESC;
+-------+-------+
| score | Ranks |
+-------+-------+
|  4.00 |     1 |
|  4.00 |     1 |
|  3.85 |     2 |
|  3.65 |     3 |
|  3.65 |     3 |
|  3.50 |     4 |
+-------+-------+