MySql学习系列（四）

分手后的思念是犯贱 2022-01-29 08:25 242阅读 0赞

### MySql学习系列（四） ###

*  任务四
 *   *  4.1 MySQL 实战 - 复杂项目
     *   *  \#作业\#
         *   *  项目十：行程和用户（难度：困难）
             *  项目十一：各部门前3高工资的员工（难度：中等）
             *  项目十二 分数排名 - （难度：中等）

# 任务四 #

## 4.1 MySQL 实战 - 复杂项目 ##

### \#作业\# ###

#### 项目十：行程和用户（难度：困难） ####

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

建表：

create table Trips(
    	Id int not null primary key,
    	Client_Id int, 
    	Driver_Id int,
    	City_Id int,
    	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"),
    (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’）的枚举类型。

![在这里插入图片描述][20190521153055844.png]

建表：

create table Users(
    	Users_Id int primary key,
    	Banned enum("yes", "no"),
    	Role enum("client", "driver", "Role")
    );

插入数据：

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");

写一段 SQL 语句查出 \*\*2013年10月1日 \*\*至 \*\*2013年10月3日 \*\*期间非禁止用户的取消率。取消率（Cancellation Rate）保留两位小数。

select Request_at as Day, ROUND(count(Status != 'completed' OR NULL)/count(*),2) as "Cancellation Rate"
    from Trips, users
    where Trips.Client_Id = users.Users_Id
    and Banned="no" and Request_at BETWEEN "2013-10-1" and "2013-10-3"
    group by Request_at;

输出:

![在这里插入图片描述][20190521153156873.png]

#### 项目十一：各部门前3高工资的员工（难度：中等） ####

将项目7中的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 |

建表：

create table Employee2(
    	Id int not null,
    	name char(10) not null,
    	salary long,
    	departmentId int
    );

插入数据：

insert into Employee2 values
    (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 Department, e.Name Employee2, e.Salary Salary 
    from Employee2 e inner join Department d 
    on e.DepartmentId = d.Id
    where (
    	select count(distinct c.Salary) 
    	from Employee2 c
    	where e.Salary < c.Salary and e.DepartmentId = c.DepartmentId
    ) < 3
    order by e.DepartmentId, e.Salary desc;

输出:

![在这里插入图片描述][20190521153252555.png]

#### 项目十二 分数排名 - （难度：中等） ####

依然是昨天的分数表，实现排名功能，但是排名是非连续的，如下： ±------±-----+ | Score | Rank | ±------±-----+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 3 | | 3.65 | 4 | | 3.65 | 4 | | 3.50 | 6 | ±------±-----

select score as Score,(
    	select count(score)+1 
    	from scores s1 
    	where s1.score>s2.score
    )	as "Rank"
    from scores s2
    order by score desc;

输出:

![在这里插入图片描述][20190521153318777.png]

[20190521153055844.png]: /images/20220129/355d63f13bb64e0aaa43f06b682b2af4.png
[20190521153156873.png]: /images/20220129/eaf88c6cf04e4408a2f5d930a6416200.png
[20190521153252555.png]: /images/20220129/7dcc88cd1793449cb9099b0ebf0a7710.png
[20190521153318777.png]: /images/20220129/271e53fc72be4f58bb393b3d95961d55.png