MySQL实战（四）

最新推荐文章于 2024-01-18 18:57:13 发布

前端开发小强

最新推荐文章于 2024-01-18 18:57:13 发布

阅读量169

点赞数 1

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/weixin_42098909/article/details/89061165

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

数据导入导出

我本地MySQL客户端是用的是Navicat for MySQL。就使用Navicat for MySQL导入/导出CSV格式数据。
在这里插入图片描述

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

项目七: 各部门工资最高的员工（难度：中等）
创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

Employee表
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| Id           | int(10) unsigned | NO   | PRI | <null>  | auto_increment |
| Name         | varchar(100)     | YES  |     | <null>  |                |
| Salary       | int(11)          | YES  |     | <null>  |                |
| DepartmentId | int(11)          | YES  |     | <null>  |                |
+--------------+------------------+------+-----+---------+----------------+

Department表
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| Id    | int(10) unsigned | NO   | PRI | <null>  | auto_increment |
| Name  | varchar(100)     | NO   |     | <null>  |                |
+-------+------------------+------+-----+---------+----------------+

数据表、数据准备

-- 创建Employee表, 插入相关数据
CREATE TABLE IF NOT EXISTS Employee (
	Id int UNSIGNED AUTO_INCREMENT,
	Name varchar(100),
	Salary int,
	DepartmentId int,
	PRIMARY KEY (`Id`)
);
INSERT INTO `Employee` (Name, Salary, `DepartmentId`) VALUES ('Joe', 70000, 1);
INSERT INTO `Employee` (Name, Salary, `DepartmentId`) VALUES ('Henry', 80000, 2);
INSERT INTO `Employee` (Name, Salary, `DepartmentId`) VALUES ('Sam', 60000, 2);
INSERT INTO `Employee` (Name, Salary, `DepartmentId`) VALUES ('Max', 90000, 1);

/* 表数据
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+ */

-- 创建Department表
CREATE TABLE IF NOT EXISTS `Department` (
	`Id` INT UNSIGNED AUTO_INCREMENT,
	`Name` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`Id`)
);
INSERT INTO `Department` (`Name`) VALUES ('IT');
INSERT INTO `Department` (`Name`) VALUES ('Sales');

/* 表数据
+----+-------+
| Id | Name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+ */

查询练习

-- Employee和Department根据DepartmentId联合查询所有数据
SELECT e.Id AS EmployeeId, e.Name AS Employee, e.Salary AS Salary, d.Name AS DepartmentName, d.Id AS DepartmentId
FROM `Employee` e
	INNER JOIN `Department` d ON e.`DepartmentId` = d.`Id`;
/* 查询结果
+------------+----------+--------+----------------+--------------+
| EmployeeId | Employee | Salary | DepartmentName | DepartmentId |
+------------+----------+--------+----------------+--------------+
| 1          | Joe      | 70000  | IT             | 1            |
| 2          | Henry    | 80000  | Sales          | 2            |
| 3          | Sam      | 60000  | Sales          | 2            |
| 4          | Max      | 90000  | IT             | 1            |
+------------+----------+--------+----------------+--------------+ */

-- 查找出部门最高薪资
SELECT DepartmentId, Max(Salary) AS Salary FROM Employee GROUP BY DepartmentId;
/* 查询结果
+--------------+--------+
| DepartmentId | Salary |
+--------------+--------+
| 1            | 90000  |
| 2            | 80000  |
+--------------+--------+ */

-- 查询每个部分最高工资，输出对应的部门、员工姓名、薪资
SELECT d.Name AS Department, e.Name AS Employee, e.Salary AS Salary
FROM Employee e
	INNER JOIN Department d ON e.`DepartmentId` = d.`Id`
WHERE Salary IN (
	SELECT MAX(Salary)
	FROM Employee
	GROUP BY DepartmentId
)
ORDER BY Salary DESC;
/* 查询结果
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+ */

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

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

表seat数据：

+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+

-- 数据准备
CREATE TABLE IF NOT EXISTS `seat`(
	`id` INT UNSIGNED AUTO_INCREMENT,
	`student` VARCHAR(120) NOT NULL,
	PRIMARY KEY(`id`)
);
INSERT INTO seat(student) VALUES('Doris');
INSERT INTO seat(student) VALUES('Abbot');
INSERT INTO seat(student) VALUES('Green');
INSERT INTO seat(student) VALUES('Emerson');
INSERT INTO seat(student) VALUES('Jeames');

-- 查询思路：
-- 1. 先查询如果是奇数行的话，查询最后一条数据；
SELECT id, student FROM seat WHERE id%2 = 1 AND id = (SELECT count(id) FROM seat);
/* 查询结果
+----+---------+
| id | student |
+----+---------+
| 5  | Jeames  |
+----+---------+ */

-- 2. 查询奇数行
SELECT id + 1, student FROM seat WHERE id%2 = 1 AND id != (SELECT count(id) FROM seat)
/* 查询结果
+--------+---------+
| id + 1 | student |
+--------+---------+
| 2      | Doris   |
| 4      | Green   |
+--------+---------+ */

-- 3. 查询偶数行
SELECT id-1,student FROM seat WHERE id%2 = 0
/* 查询结果
+------+---------+
| id-1 | student |
+------+---------+
| 1    | Abbot   |
| 3    | Emerson |
+------+---------+ */

-- 4. 将上面是哪个查询结果拼起来
SELECT * FROM (
	SELECT id, student FROM seat WHERE id%2 = 1 AND id = (SELECT count(id) FROM seat)
	UNION
	SELECT id + 1, student FROM seat WHERE id%2 = 1 AND id != (SELECT count(id) FROM seat)
	UNION
	SELECT id-1,student FROM seat WHERE id%2 = 0) s
ORDER BY id;
/* 查询结果
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+ */

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

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

score表结构

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| Id    | int(10) unsigned | NO   | PRI | <null>  | auto_increment |
| Score | double           | NO   |     | <null>  |                |
+-------+------------------+------+-----+---------+----------------+

-- 数据准备
CREATE TABLE IF NOT EXISTS `score`(
	`Id` INT UNSIGNED AUTO_INCREMENT,
	`Score` DOUBLE NOT NULL,
	PRIMARY KEY(`Id`)
);

INSERT INTO score(Score) VALUES(3.50);
INSERT INTO score(Score) VALUES(3.65);
INSERT INTO score(Score) VALUES(4.00);
INSERT INTO score(Score) VALUES(3.85);
INSERT INTO score(Score) VALUES(4.00);
INSERT INTO score(Score) VALUES(3.65);

-- 排名查询
SELECT 
	score,
	(SELECT COUNT(DISTINCT s1.score)
		FROM score s1
		WHERE s1.score >= s.score
	) AS `rank`
FROM Score s
ORDER BY `rank`;

/* 查询结果
+-------+------+
| score | rank |
+-------+------+
| 4.0   | 1    |
| 4.0   | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.5   | 4    |
+-------+------+ */

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

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

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

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

-- 数据表、数据准备
CREATE TABLE Users (
	`User_Id` INT(4) NOT NULL AUTO_INCREMENT,
	`Banned` VARCHAR(20) NOT NULL,
	`Role` enum('client', 'driver', 'partner'),
	PRIMARY KEY (`User_Id`)
);

CREATE TABLE Trips (
	`Id` INT(4) NOT NULL AUTO_INCREMENT,
	`Client_Id` INT(4) NOT NULL,
	`Driver_Id` INT(4) NOT NULL,
	`City_Id` INT(4) NOT NULL,
	`Status` enum('completed', 'cancelled_by_driver', 'cancelled_by_client'),
	`Request_at` date NOT NULL,
	PRIMARY KEY (`Id`),
	FOREIGN KEY (`Client_Id`) REFERENCES Users (`User_Id`),
	FOREIGN KEY (`Driver_Id`) REFERENCES Users (`User_Id`)
);

INSERT INTO Users (`User_Id`, `Banned`, `Role`)
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');

INSERT INTO Trips (`Client_Id`, `Driver_Id`, `City_Id`, `Status`, `Request_at`)
VALUES (1, 10, 1, 'completed', '2013-10-01'),
	(2, 11, 1, 'cancelled_by_driver', '2013-10-01'),
	(3, 12, 6, 'completed', '2013-10-01'),
	(4, 13, 6, 'cancelled_by_client', '2013-10-01'),
	(1, 10, 1, 'completed', '2013-10-02'),
	(2, 11, 6, 'completed', '2013-10-02'),
	(3, 12, 6, 'completed', '2013-10-02'),
	(2, 12, 12, 'completed', '2013-10-03'),
	(3, 10, 12, 'completed', '2013-10-03'),
	(4, 13, 12, 'cancelled_by_driver', '2013-10-03');

-- 查询
SELECT DISTINCT total.Request_at AS `Day`, FORMAT(CASE 
		WHEN cancelled.num / total.num IS NULL THEN 0
		ELSE cancelled.num / total.num
	END, 2) AS "`Cancellation Rate`"
FROM (
	SELECT t.Request_at, COUNT(*) AS num
	FROM trips t
		JOIN users u
		ON u.user_id = t.Client_Id
			AND u.Role = 'client'
			AND u.Banned = 'No'
			AND t.`Status` LIKE 'cancelled%'
	GROUP BY t.Request_at
) cancelled
	RIGHT JOIN (
		SELECT t.Request_at, COUNT(*) AS num
		FROM trips t
			JOIN users u
			ON u.user_id = t.Client_Id
				AND u.Role = 'client'
				AND u.Banned = 'No'
		GROUP BY t.Request_at
	) total
	ON cancelled.Request_at = total.Request_at

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

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

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果。此外，请考虑实现各部门前N高工资的员工功能。

-- 清空原数据、插入新数据
DELETE FROM `Employee`;
INSERT INTO `Employee` (Id, Name, Salary, `DepartmentId`)
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);
/* 准备数据
+----+-------+--------+--------------+
| 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            |
+----+-------+--------+--------------+ */

-- 查询
SELECT d.`Name` AS Department, e2.`Name` AS Employee, e2.Salary
FROM (
	SELECT `Name`, DepartmentId, Salary
		, (
			SELECT COUNT(DISTINCT e1.salary)
			FROM employee e1
			WHERE e.Salary <= e1.Salary
				AND e.DepartmentId = e1.DepartmentId
		) AS `Rank`
	FROM employee e
) e2
	JOIN department d ON d.Id = e2.DepartmentId
WHERE e2.`Rank` <= 3
ORDER BY DepartmentId, `Rank`;

/* 查询结果
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+ */

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

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

SELECT
	score,
	(SELECT COUNT(s.score) FROM score s WHERE s.Score > s2.Score ) + 1 AS `Rank` 
FROM
	score s2
ORDER BY
	`Rank`;

/* 查询结果
+-------+------+
| score | Rank |
+-------+------+
| 4.0   | 1    |
| 4.0   | 1    |
| 3.85  | 3    |
| 3.65  | 4    |
| 3.65  | 4    |
| 3.5   | 6    |
+-------+------+ */

前端开发小强

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL实战（四）

文章目录数据导入导出项目七：各部门工资最高的员工（难度：中等）项目八：换座位（难度：中等）数据导入导出我本地MySQL客户端是用的是Navicat for MySQL。就使用Navicat for MySQL导入/导出CSV格式数据。项目七：各部门工资最高的员工（难度：中等）项目七: 各部门工资最高的员工（难度：中等）创建Employee 表，包含所有员工信息，每个员工...
复制链接

扫一扫