MySQL 实战

1  数据导入导出

Navicat导入，导出向导

利用Navicat的导入向导和导出向导，可以方便地执行数据的导入导出：

1.1 导入向导

参考：教你一步一步将excel文件导入mysql数据库（个人尝试过直接导入.xlsx文件，屡次没有成功，还是.csv靠谱）

导入向导分为八个步骤：

1. 选择数据导入格式，如上图所示；
2. 选择文件作为数据源，并指定文件编码；
3. 指定分隔符，常见的如分号，逗号，固定宽度和空格；
4. 附加选项，设置栏位名行和起止数据行，日期，时间和数字的符号；
5. 选择目标表，包括现有表和新建表；
6. 设定栏位对应，可以对应源数据表修改导入后的栏位名和设置主键；
7. 设置导入模式，如添加，复制，删除，更新，添加或更新；
8. 点击开始导入，并输出导入过程中的日志信息，方便查询进度和错误。

1.2 导出向导 

导出格式没有限制

导出向导分为5个步骤：

1. 选择导出格式；
2. 选择要导出的表，导出到的文件名和路径，文件编码和添加时间戳；
3. 选择要导出哪些表中的哪些列；
4. 附加选项，如是否包含列的标题，分隔符，时期，时间和数字格式，导出遇到错误是否继续；
5. 点击开始导出，并输出导入过程中的日志信息，方便查询进度和错误。

注意
导入Excel文件时，需要打开微软OFFICE软件，且和Navicat选择同样的64位或32位版本，或转为CSV文件导入。
 

2  实战 

2.1 各部门工资最高的员工

创建Employee 表与 Department 表如下所示，编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

解析：因为 e.Name 没有进行聚合，如果直接通过 GROUP BY 语句写的话，返回的 e.Name 将是第一个值，即 IT 部门最高的是 Joe 不是我们想要的结果。所以需要通过别的方法实现：

方案一：判断 e.Salary 是最大值

#建立数据表
CREATE TABLE IF NOT EXISTS Employee(
Id INT NOT NULL PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Salary INT NOT NULL,
DepartmentId INT NOT NULL
);
INSERT INTO Employee
VALUES
(1,"Joe",70000,1),
(2,"Henry",80000,2),
(3,"Sam",60000,2),
(4,"Max",90000,1);
CREATE TABLE Department(
Id INT NOT NULL PRIMARY KEY,
Name VARCHAR(20) NOT NULL
);
INSERT Into Department
VALUES
(1,"IT"),(2,"Sales");
--------------------- 
-- 查询每个部门工资最高的员工
SELECT
	d. NAME Department,
	e. NAME Employee,
	e.Salary
FROM
	Employee e
INNER JOIN Department d ON e.DepartmentId = d.ID
WHERE
	e.Salary = (
		SELECT
			MAX(Salary)
		FROM
			Employee e
		WHERE
			e.DepartmentID = d.ID
	);

方案二：对每个员工，先统计同部门工资大于他的有几个，这个方法可以筛选出每个部门工资前N高的人

-- 方案二
SET @limit_n = 1;  -- 定义变量，筛选各部门前N个工资最高的人
SELECT
	d. NAME Department,
	e. NAME Employee,
	e.Salary
FROM
	Employee e
INNER JOIN Department d ON e.DepartmentId = d.ID
WHERE
	(
		SELECT
			COUNT(1)
		FROM
			Employee e2
		WHERE
			e2.DepartmentID = d.ID
		AND e2.Salary > e.Salary
	) < @limit_n;

2.2 换座位

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。 其中纵列的 id 是连续递增的 小美想改变相邻俩学生的座位。 你能不能帮她写一个 SQL query 来输出小美想要的结果呢？ (如果学生人数是奇数，则不需要改变最后一个同学的座位。)

解析：先用一个变量记录座位总数，然后再对 id 进行修改排序即可

--创建表
 CREATE TABLE seat (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     student VARCHAR(20));

--插入数据
INSERT INTO seat (id, student) VALUES
    (1, "Abbot"),
    (2, "Doris"),
    (3, "Emerson"),
    (4, "Green"),
    (5, "Jeames");

SELECT @cnt:=count(1) FROM seat;
SELECT 
	IF(id = @cnt, 
		 id, 
		 IF(MOD(id, 2)=1 , id+1, id-1)
  ) id,
	student
FROM seat 
ORDER BY id;

2.3 分数排名

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

解析：计算 Rank 时只需要统计有多少个不重复的分数 小于等于 当前分数 即可

#建立数据表
CREATE TABLE IF NOT EXISTS score(
Id INT NOT NULL PRIMARY KEY,
Score FLOAT NOT NULL
);
INSERT INTO score
VALUES
(1,3.50),
(2,3.65),
(3,4.00),
(4,3.85),
(5,4.00),
(6,3.65);
--------------------- 
SELECT
  s1.Score,
  (
    SELECT
      COUNT(DISTINCT Score)
    FROM
      Scores s2
    WHERE
      s2.Score >= s1.Score
  ) Rank
FROM
  Scores s1
ORDER BY
  s1.Score DESC;