「MySQL基础」Day5 MySQL 实战

2019-03-03

1. 任务背景

Datawhale组队学习中MySQL一周学习任务，通过一周组队自主学习，快速熟悉MySQL相关基础知识，并能掌握基本的数据查询操作方式

1.1 任务内容

数据导入导出 (见附件)
- 将Excel文件导入MySQL表
- MySQL导出表到Excel文件

2. 数据导入

根据给定excel内容，先建立表格

CREATE TABLE IF NOT EXISTS `employees` (
    employeeNumber INT NOT NULL DEFAULT 0,
    lastName VARCHAR(15) NOT NULL DEFAULT '',
    firstName VARCHAR(15) NOT NULL DEFAULT '',
    extension VARCHAR(8) NOT NULL DEFAULT '',
    email VARCHAR(30) NOT NULL DEFAULT '',
    officeCode TINYINT(3) NOT NULL DEFAULT 0,
    reportsTo INT NOT NULL DEFAULT 0,
    jobTitle VARCHAR(30) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COMMENT 'Day5任务employees表';

一般MySQL导入只支持CSV格式，因此先将xlsx格式转换为CSV格式

由于提供的表格中有多个sheet，直接进行格式转换会报错，因此删去了空的sheet1

此时即可看见数据已经全部导入成功

通过观察表，在某一行reportsTo字段存在缺失值，暂时没找到好方法，于是去掉了这个字段的NOT NULL限制

2. 作业

2.1 作业一

2.1.1 作业内容

项目七: 各部门工资最高的员工（难度：中等）
创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
创建Department 表，包含公司所有部门的信息。
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

2.1.2 建表和数据导入

CREATE TABLE IF NOT EXISTS `employee` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10) NOT NULL DEFAULT '',
    salary INT NOT NULL DEFAULT 0,
    department_id INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COMMENT 'Day5作业一employee表';

INSERT INTO employee(`name`, `salary`, department_id) VALUE ('Joe', 70000, 1);
INSERT INTO employee(`name`, `salary`, department_id) VALUE ('Henry', 80000, 2);
INSERT INTO employee(`name`, `salary`, department_id) VALUE ('Sam', 60000, 2);
INSERT INTO employee(`name`, `salary`, department_id) VALUE ('Max', 90000, 1);

CREATE TABLE IF NOT EXISTS `department` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COMMENT 'Day5作业一department表';

INSERT INTO department(`name`) VALUE ('IT');
INSERT INTO department(`name`) VALUE ('Sales');

2.1.3 作业思路

首先先根据employee表，找出其中各部门工资最高的那个，然后根据department_id联结department表，找出department的name

2.1.4 SQL实现

SELECT
  b.name as Department, a.name, a.salary
FROM 
  employee a
INNER JOIN 
  department b 
ON 
  b.id = a.department_id
WHERE 
  a.salary >= (
    SELECT 
           MAX(salary) 
    FROM employee c 
    WHERE 
          a.department_id = c.department_id
  );

得到结果

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| Sales      | Henry    |  80000 |
| IT         | Max      |  90000 |
+------------+----------+--------+
2 rows in set (0.01 sec)

2.2 作业二换座位（难度：中等）

2.2.1 作业内容

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
 请创建如下所示seat表：
示例：
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。

2.2.2 建表和数据导入

CREATE TABLE IF NOT EXISTS `seat` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student VARCHAR(15) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COMMENT 'Day5作业二seat表';

INSERT INTO `seat`(`student`) VALUE ('Doris');
INSERT INTO `seat`(`student`) VALUE ('Abbot');
INSERT INTO `seat`(`student`) VALUE ('Green');
INSERT INTO `seat`(`student`) VALUE ('Emerson');
INSERT INTO `seat`(`student`) VALUE ('Jeames');

2.2.3 作业思路

这个需要多次查询结果，可以用UNION来进行联合，但是需要注意的是，当id为奇数时，如果其已经是最后一个了，那它此时显示的id是不应该变化的，因此需要进行相应调整

2.2.4 SQL实现

SELECT
    *
FROM (
    SELECT
        id - 1 as id, student
    FROM
        seat
    WHERE
        id % 2 = 0
    UNION ALL
    SELECT
        id + 1 as id, student
    FROM
        seat
    WHERE
        id % 2 = 1
        AND id != (
            SELECT MAX(id) FROM seat
        )
    UNION ALL
    SELECT
        id as id, student
    FROM
        seat
    WHERE
        id % 2 = 1
        AND id = (
            SELECT MAX(id) FROM seat
        )
) AS a ORDER BY id;

其中这个As a如果不加，会报错，具体原因还需要研究

1	Every derived table must have its own alias

得到结果

+----+---------+
| id | student |
+----+---------+
|  1 | Abbot   |
|  2 | Doris   |
|  3 | Emerson |
|  4 | Green   |
|  5 | Jeames  |
+----+---------+
5 rows in set (0.01 sec)

2.3 作业三分数排名（难度：中等）

2.3.1 作业内容

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
创建以下score表：
+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

2.3.2 建表和数据导入

CREATE TABLE IF NOT EXISTS `scores` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    score DOUBLE(4, 2) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COMMENT 'Day5作业三scores表';

INSERT INTO `scores`(`score`) VALUE (3.50);
INSERT INTO `scores`(`score`) VALUE (3.65);
INSERT INTO `scores`(`score`) VALUE (4.00);
INSERT INTO `scores`(`score`) VALUE (3.85);
INSERT INTO `scores`(`score`) VALUE (4.00);
INSERT INTO `scores`(`score`) VALUE (3.65);

2.3.3 作业思路

其中一种常见方法是设置变量来做，但因为目前内容没有涉及，所以结合题意用了另一种方式，即看大于等于这个成绩的有多少个，即为其并列排名

2.3.4 SQL实现

SELECT
    score,
    (
        SELECT
            COUNT(DISTINCT score)
        FROM
            scores
        WHERE
            score >= s.score
    ) AS Rank
FROM
    scores s
ORDER BY
    score DESC;

得到结果

+-------+------+
| score | Rank |
+-------+------+
|  4.00 |    1 |
|  4.00 |    1 |
|  3.85 |    2 |
|  3.65 |    3 |
|  3.65 |    3 |
|  3.50 |    4 |
+-------+------+
6 rows in set (0.01 sec)

1. 任务背景

1.1 任务内容

2. 数据导入

2. 作业

2.1 作业一

2.1.1 作业内容

2.1.2 建表和数据导入

2.1.3 作业思路

2.1.4 SQL实现

2.2 作业二 换座位（难度：中等）

2.2.1 作业内容

2.2.2 建表和数据导入

2.2.3 作业思路

2.2.4 SQL实现

2.3 作业三 分数排名（难度：中等）

2.3.1 作业内容

2.3.2 建表和数据导入

2.3.3 作业思路

2.3.4 SQL实现

2.2 作业二换座位（难度：中等）

2.3 作业三分数排名（难度：中等）