0405任务四MySQL实战

最新推荐文章于 2024-04-25 12:02:52 发布

Guiabbey

最新推荐文章于 2024-04-25 12:02:52 发布

阅读量226

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/guiabbey/article/details/89041727

版权

任务四MySQL实战

一、数据导入导出
- 1、导入
- 2、导出
二、作业
纠错
项目七代码更改为：

一、数据导入导出

https://www.shiyanlou.com/courses/9/labs/76/document

1、导入

（1）数据文件导入：

LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;

（2）用SQL语句导入：

source *.sql

两者之间的不同是：数据文件导入方式只包含数据，导入规则由数据库系统完成；SQL 文件导入相当于执行该文件中包含的 SQL 语句，可以实现多种操作，包括删除，更新，新增，甚至对数据库的重建。

2、导出

导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中。导出语句基本格式为：

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;

注意：语句中“文件路径”之下不能已经有同名文件。

二、作业

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

--创建Employee表并插入数据
CREATE TABLE Employee(
	Id int(10) primary key,
	Name VARCHAR(10),
	Salary int(10),
	DepartmentId int(4)
);
INSERT INTO Employee(Id,Name,Salary,DepartmentId)
VALUES(1,"Joe",70000,1);
INSERT INTO Employee(Id,Name,Salary,DepartmentId)
VALUES(2,"Henry",80000,2);
INSERT INTO Employee(Id,Name,Salary,DepartmentId)
VALUES(3,"Sam",60000,2);
INSERT INTO Employee(Id,Name,Salary,DepartmentId)
VALUES(4,"Max",90000,1);
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
|  1 | Joe   |  70000 |            1 |
|  2 | Henry |  80000 |            2 |
|  3 | Sam   |  60000 |            2 |
|  4 | Max   |  90000 |            1 |
+----+-------+--------+--------------+
4 rows in set (0.00 sec)

CREATE TABLE Department(
	Id int(10) primary key,
	Name VARCHAR(10)
);
--创建Department表并插入数据
INSERT INTO Department(Id,Name)
VALUES(1,"IT");
INSERT INTO Department(Id,Name)
VALUES(2,"Sales");
+----+-------+
| Id | Name  |
+----+-------+
|  1 | IT    |
|  2 | Sales |
+----+-------+
2 rows in set (0.00 sec)

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
思路：1、先进行表连接，条件是e.DepartmentId=d.Id，作为临时表t
2、从临时表t中按照Department分组，找出最高工资。

SELECT Department,Employee,MAX(Salary) FROM
(SELECT d.Name as Department,e.Name as Employee,e.Salary FROM Employee e join Department d
on e.DepartmentId=d.Id) t
GROUP BY Department;

--结果如下：
+------------+----------+-------------+
| Department | Employee | MAX(Salary) |
+------------+----------+-------------+
| IT         | Joe      |       90000 |
| Sales      | Henry    |       80000 |
+------------+----------+-------------+
2 rows in set (0.01 sec)

项目八：换座位（难度：中等）

小美有一张 seat 座位表，用来储存学生名字和与相对应的座位 id。其中纵列的 id 是连续递增的。小美想改变相邻俩学生的座位。
注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。

--创建seat表并插入数据
CREATE TABLE seat(
	id int(10) primary key,
	student VARCHAR(10)
);
INSERT INTO seat(id,student)
VALUES(1,"Abbot");
INSERT INTO seat(id,student)
VALUES(2,"Doris");
INSERT INTO seat(id,student)
VALUES(3,"Emerson");
INSERT INTO seat(id,student)
VALUES(4,"Green");
INSERT INTO seat(id,student)
VALUES(5,"Jeames");

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

思路：利用CASE WHEN把id是奇数且不等于5的id+1,id是奇数的且等于5的id不变，id是偶数的id-1,然后将id按照升序排列。

SELECT (CASE
	WHEN id%2<>0 AND id<>(SELECT COUNT(*) FROM seat)  THEN id+1
	WHEN id%2<>0 AND id=(SELECT COUNT(*) FROM seat) THEN id
	ELSE id-1
	END) 
	AS id,student FROM seat ORDER BY id ASC;
-- 结果如下：
+----+---------+
| id | student |
+----+---------+
|  1 | Doris   |
|  2 | Abbot   |
|  3 | Green   |
|  4 | Emerson |
|  5 | Jeames  |
+----+---------+
5 rows in set (0.00 sec)

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

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

-- 创建score表并插入数据
CREATE TABLE score(
	Id int(10) primary key,
	Score FLOAT(4)
);
INSERT INTO score(Id,Score)
VALUES(1,3.50);
INSERT INTO score(Id,Score)
VALUES(2,3.65);
INSERT INTO score(Id,Score)
VALUES(3,4.00);
INSERT INTO score(Id,Score)
VALUES(4,3.85);
INSERT INTO score(Id,Score)
VALUES(5,4.00);
INSERT INTO score(Id,Score)
VALUES(6,3.65);
+----+-------+
| Id | Score |
+----+-------+
|  1 |   3.5 |
|  2 |  3.65 |
|  3 |     4 |
|  4 |  3.85 |
|  5 |     4 |
|  6 |  3.65 |
+----+-------+
6 rows in set (0.00 sec)

思路1：对于每一个分数，从表中找出有多少个大于或等于该分数的不重复分数（达到了分数相同排名相同的效果），然后降序排列。

select Score,
(select count(distinct Score) from Score as s2 where s2.Score >= s1.Score) Rank 
from Score as s1
order by Score DESC;
-- 结果如下：
+-------+------+
| Score | Rank |
+-------+------+
|     4 |    1 |
|     4 |    1 |
|  3.85 |    2 |
|  3.65 |    3 |
|  3.65 |    3 |
|   3.5 |    4 |
+-------+------+
6 rows in set (0.00 sec)

思路2：使用联结 join，条件是左表的分数小于等于右表的分数时，对右表的分数进行计数（即计算有几个不重复的分数大于自己，计算结果就是rank），然后根据id分组后，再根据分数降序排列。

SELECT s1.Score, count(distinct s2.Score) Rank
FROM Score as s1 JOIN Score as s2
on s1.Score <= s2.Score
GROUP BY s1.Id
ORDER BY s1.Score DESC;

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

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

Create table If Not Exists Trips (Id int,Client_Id int, Driver_Id int, City_Id int, Status ENUM('completed','cancelled_by_driver', 'cancelled_by_client'), Request_at varchar(50));
Create table If Not Exists Users (Users_Id int,Banned varchar(50), Role ENUM('client', 'driver', 'partner'));
Truncate table Trips;
insert into Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) values ('1', '1', '10', '1', 'completed','2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) values ('2', '2', '11', '1','cancelled_by_driver', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) values ('3', '3', '12', '6', 'completed','2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) values ('4', '4', '13', '6','cancelled_by_client', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) values ('5', '1', '10', '1', 'completed','2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) values ('6', '2', '11', '6', 'completed','2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) values ('7', '3', '12', '6', 'completed','2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) values ('8', '2', '12', '12', 'completed','2013-10-03');
insert into Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) values ('9', '3', '10', '12', 'completed','2013-10-03');
insert into Trips (Id, Client_Id, Driver_Id,City_Id, Status, Request_at) values ('10', '4', '13', '12','cancelled_by_driver', '2013-10-03');
Truncate table Users;
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');
--Trips表
+------+-----------+-----------+---------+---------------------+------------+
| 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   |
+----------+--------+--------+
|        1 | No     | client |
|        2 | Yes    | client |
|        3 | No     | client |
|        4 | No     | client |
|       10 | No     | driver |
|       11 | No     | driver |
|       12 | No     | driver |
|       13 | No     | driver |
+----------+--------+--------+
8 rows in set (0.00 sec)

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

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

思路：1、找出在日期区间内且是非禁止用户的记录作为临时表t
2、对于这些记录根据日期(request_at列)进行分组,找出每组符合status!='completed’条件的元组的个数作为分子，分组内记录个数总数作为分母，分子除以分母得出结果。

SELECT request_at,
round(sum(case when status="completed" then 0 else 1 end)/count(*),2) as "Cancellation Rate" FROM
(select * from Trips
where client_id not in (select users_id from Users where banned = "yes" and role = "client") and request_at >= "2013-10-01" and request_at <= "2013-10-03") t
GROUP BY request_at ORDER BY request_at ASC;
-- 结果如下：
+------------+-------------------+
| Request_at | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |              0.33 |
| 2013-10-02 |              0.00 |
| 2013-10-03 |              0.50 |
+------------+-------------------+

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

将项目7中的employee表清空，重新插入以下数据（其实是多插入5,6两行）

INSERT INTO Employee(Id,Name,Salary,DepartmentId)
VALUES(5,"Janet",69000,1);
INSERT INTO Employee(Id,Name,Salary,DepartmentId)
VALUES(6,"Randy",85000,1);
--Employee表
+----+-------+--------+--------------+
| 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 |
+----+-------+--------+--------------+
--Department表
+----+-------+
| Id | Name  |
+----+-------+
|  1 | IT    |
|  2 | Sales |
+----+-------+

编写一个 SQL 查询，找出每个部门工资前三高的员工。

思路：1、表连接条件是e1.DepartmentId = Department.Id
2、每个部门工资前三高。Employee表自连接，将其与1中的表作对比，令e2表中的salary大于e1表中的salary。限制条件：e2表中salary大于e1表中salary的个数少于3。这等价于取e1表中最大的三个值。
（经常进行自连接比较表中某一字段大小）

SELECT Department.Name AS Department, e1.Name AS Employee, e1.Salary AS Salary
FROM Employee e1
JOIN Department ON e1.DepartmentId = Department.Id
WHERE 3 >   (
            SELECT COUNT(DISTINCT e2.Salary) 
            FROM Employee e2
            WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
            )
ORDER BY Department.Name, e1.Salary DESC;
-- 结果如下：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      |  90000 |
| IT         | Randy    |  85000 |
| IT         | Joe      |  70000 |
| Sales      | Henry    |  80000 |
| Sales      | Sam      |  60000 |
+------------+----------+--------+

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

依然是昨天的分数表，实现排名功能，但是排名是非连续的。

--Score表
+----+-------+
| Id | Score |
+----+-------+
|  1 |   3.5 |
|  2 |  3.65 |
|  3 |     4 |
|  4 |  3.85 |
|  5 |     4 |
|  6 |  3.65 |
+----+-------+

思路：不连续的，所以count(*)取表中总记录数即可，之后统计多少个人比这个分数高，对结果+1后即为排名。几个人分数比你高，你的排名就在这几个人后面需要+1.

SELECT Score,
(SELECT count(*) FROM Score AS s2 WHERE s2.Score > s1.Score)+1 AS Rank 
FROM Score AS s1
ORDER BY Score DESC;

纠错

项目七代码更改为：

select e.name,t.maxsal,t.departmentid from employee e join
(select departmentid,max(salary) as maxsal from employee group by departmentid) t
on e.departmentid=t.departmentid and e.salary=t.maxsal;

Guiabbey

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
0405任务四MySQL实战

任务四MySQL实战二、作业项目七：各部门工资最高的员工（难度：中等）项目八：换座位（难度：中等）项目九：分数排名（难度：中等）项目十：行程和用户（难度：困难）项目十一：各部门前3高工资的员工（难度：中等）项目十二：分数排名 - （难度：中等）二、作业项目七：各部门工资最高的员工（难度：中等）--创建Employee表并插入数据CREATE TABLE Employee( Id int(...
复制链接

扫一扫