MySQL实战练习

项目一：数据导入导出

内容：将之前创建的任意一张MySQL表导出，且是CSV格式，再将CSV表导入数据库。

操作如下（以下为Navicat操作步骤截图）：
1.选一个表格，右击选择“导出向导”，如下图：
在这里插入图片描述
2.选择“CSV文件”，如下图：
在这里插入图片描述
3.按照提示选择即可：
在这里插入图片描述
4.找到导出的文件，右击Navicat中表格，选择“导入向导”，如下图：
在这里插入图片描述
5.按导入向导顺序导入即可
项目二: 各部门工资最高的员工

创建 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 部门有最高工资。
创建表：

-- 创建Employee表并插入数据
create table Employee(
id char(10),
Name char(20),
Salary int,
Departmentid char(10));

insert into Employee values('1','Joe',70000,'1');
insert into Employee values('2','Henry',80000,'2');
insert into Employee values('3','Sam',60000,'2');
insert into Employee values('4','Max',90000,'1');

-- 创建Department表并插入数据
create table Department(
id char(10),
Name char(20));

insert into Department values('1','IT');
insert into Department values('2','Sales');

 

查询&运行结果：

SELECT (department.Name)as Department,(employee.Name)as Employee,max(employee.Salary)as Salary
FROM department,employee
WHERE department.id=employee.Departmentid
GROUP BY Department

在这里插入图片描述
项目二：换座位

小美是一所中学的信息科技老师，她有一张 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

注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。
创建表：

-- 创建表并插入数据：
create table seat(
id int,
student char(20));
insert into seat values (1,'Abbot');
insert into seat values(2,'Doris');
insert into seat values(3,'Emerson');
insert into seat values(4,'Green');
insert into seat values(5,'Jeames');

查询&运行结果：
在这里插入图片描述
项目三: 分数排名

编写一个 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

创建表：

CREATE TABLE scores (
    Id INT NOT NULL PRIMARY KEY,
    Score float(5,2) NOT NULL
);

insert into scores(Id,Score) values(1,3.50);
insert into scores(Id,Score) values(2,3.65);
insert into scores(Id,Score) values(3,4.00);
insert into scores(Id,Score) values(4,3.85);
insert into scores(Id,Score) values(5,4.00);
insert into scores(Id,Score) values(6,3.65);

 

查询&运行结果：

SELECT
    Score,
    (SELECT
            COUNT(DISTINCT Score)
        FROM
            scores
        WHERE
            Score >= s.Score) as ScoreRank
FROM
    Scores s
ORDER BY Score DESC;

在这里插入图片描述
项目四：行程和用户

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
Id     Client_Id     Driver_Id     City_Id     Status     Request_at
1     1     10     1     completed     2013-10-01
2     2     11     1     cancelled_by_driver     2013-10-01
3     3     12     6     completed     2013-10-01
4     4     13     6     cancelled_by_client     2013-10-01
5     1     10     1     completed     2013-10-02
6     2     11     6     completed     2013-10-02
7     3     12     6     completed     2013-10-02
8     2     12     12     completed     2013-10-03
9     3     10     12     completed     2013-10-03
10     4     13     12     cancelled_by_driver     2013-10-03

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
Users_Id     Banned     Role
1     No     client
2     Yes     client
3     No     client
4     No     client
10     No     driver
11     No     driver
12     No     driver
13     No     driver

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
Day     Cancellation Rate
2013-10-02     0.00
2013-10-03     0.50

创建表：

CREATE TABLE Users (
    Users_Id INT NOT NULL PRIMARY KEY,
    Banned varchar(3) NOT NULL,
    Role ENUM('client', 'driver','partner')
);

insert into Users(Users_Id,Banned,Role) values(1,'No','client');
insert into Users(Users_Id,Banned,Role) values(2,'Yes','client');
insert into Users(Users_Id,Banned,Role) values(3,'No','client');
insert into Users(Users_Id,Banned,Role) values(4,'No','client');
insert into Users(Users_Id,Banned,Role) values(10,'No','driver');
insert into Users(Users_Id,Banned,Role) values(11,'No','driver');
insert into Users(Users_Id,Banned,Role) values(12,'No','driver');
insert into Users(Users_Id,Banned,Role) values(13,'No','driver');

CREATE TABLE Trips (
    Id INT NOT NULL 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_at date,
    foreign key(Client_Id) references Users(Users_Id),
    foreign key(Driver_Id) references Users(Users_Id)
);

insert into Trips values(1,1,10,1,'completed','2013-10-01');
insert into Trips values(2,2,11,1,'cancelled_by_driver','2013-10-01');
insert into Trips values(3,3,12,6,'completed','2013-10-01');
insert into Trips values(4,4,13,6,'cancelled_by_client','2013-10-01');
insert into Trips values(5,1,10,1,'completed','2013-10-02');
insert into Trips values(6,2,11,6,'completed','2013-10-02');
insert into Trips values(7,3,12,6,'completed','2013-10-02');
insert into Trips values(8,2,12,12,'completed','2013-10-03');
insert into Trips values(9,3,10,12,'completed','2013-10-03');
insert into Trips values(10,4,13,12,'cancelled_by_driver','2013-10-03');

查询：

SELECT T2.DAY,IFNULL(ROUND((T1.num/T2.num),2),0) AS 'Cancellation Rate'
FROM
(SELECT Request_at as Day,count(*) as num
    FROM Trips t
    LEFT JOIN Users u
    ON t.Client_Id = u.Users_Id
  WHERE u.Banned != 'Yes'
  AND t.status != 'completed'
  AND Request_at >='2013-10-01' AND Request_at <= '2013-10-03'
    GROUP BY Day) AS T1
RIGHT JOIN
 (SELECT Request_at as Day,count(*) as num
    FROM Trips t
    LEFT JOIN Users u
    ON t.Client_Id = u.Users_Id
  WHERE u.Banned != 'Yes'
  AND Request_at >='2013-10-01' AND Request_at <= '2013-10-03'
    GROUP BY Day) AS T2
  ON T1.DAY = T2.DAY;

项目五：各部门前3高工资的员工

将项目二中的 employee 表清空，重新插入以下数据（其实是多插入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 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

| Department | Employee | Salary |
|------------|---- ------|--------|
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |

此外，请考虑实现各部门前N高工资的员工功能。
查询：

SELECT
    d.name AS Department, e.Name AS Employee, Salary
FROM
    Employee e
        JOIN
    Department d ON e.DepartmentId = d.Id
WHERE
    (SELECT
            COUNT(DISTINCT em.Salary)
        FROM
            Employee em
        WHERE
            em.Salary >= e.Salary
                AND em.DepartmentId = e.DepartmentId) <= 3
GROUP BY Department , Salary DESC;

项目六：分数排名

依然是项目三的分数表，实现排名功能，但是排名是非连续的，如下：
Score     Rank
4.00     1
4.00     1
3.85     3
3.65     4
3.65     4
3.50     6

查询：

SELECT
    s.Score,
    (SELECT
            COUNT(*) + 1
        FROM
            Scores AS s1
        WHERE
            s1.Score > s.Score) AS Rank
FROM
    scores s
ORDER BY Score DESC;