MySQL实战

最新推荐文章于 2022-12-18 02:24:47 发布
置顶 最新推荐文章于 2022-12-18 02:24:47 发布
阅读量176 收藏 2
点赞数 2
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40819816/article/details/89039990
版权

项目实战

项目七

  • 项目七: 各部门工资最高的员工（难度：中等）
    创建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 |
    ±-----------±---------±-------+
-- 建表
CREATE TABLE Employee (
	`Id` INT ( 4 ) NOT NULL AUTO_INCREMENT,
	`Name` VARCHAR ( 20 ) NOT NULL,
	`Salary` INT ( 10 ) NOT NULL,
	`DepartmentId` INT ( 4 ) NOT NULL,
	PRIMARY KEY ( `Id` ) 
);

CREATE TABLE Department ( 
	`Id` INT ( 4 ) AUTO_INCREMENT, 
	`Name` VARCHAR ( 20 ), 
	PRIMARY KEY ( `Id` ) 
);

-- 添加数据
INSERT INTO employee ( name, salary, departmentid )
VALUES
	( 'Joe', 70000, 1 ),
	( 'Henry', 80000, 2 ),
	( 'Sam', 60000, 2 ),
	( 'Max', 90000, 1 )

INSERT INTO Department ( NAME )
VALUES
	( 'IT' ),
	( 'Sales' );

-- 查询
SELECT
	d.`name` `Department`,
	e.`name` `Employee`,
	e.salary 
FROM
	( SELECT DepartmentId, MAX( Salary ) s FROM employee GROUP BY DepartmentId ) tmp
	JOIN employee e ON e.Salary = tmp.s
	JOIN department d ON d.id = tmp.DepartmentId 
ORDER BY
	Department

项目八

  • 项目八: 换座位（难度：中等）
    小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
    其中纵列的 id 是连续递增的
    小美想改变相邻俩学生的座位。
    你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
    请创建如下所示seat表：
    示例：
    ±--------±--------+
    | id | student |
    ±--------±--------+
    | 1 | Abbot |
    | 2 | Doris |
    | 3 | Emerson |
    | 4 | Green |
    | 5 | Jeames |
    ±--------±--------+
    假如数据输入的是上表，则输出结果如下：
    ±--------±--------+
    | id | student |
    ±--------±--------+8
    | 1 | Doris |
    | 2 | Abbot |
    | 3 | Green |
    | 4 | Emerson |
    | 5 | Jeames |
    ±--------±--------+
    注意：
    如果学生人数是奇数，则不需要改变最后一个同学的座位。
-- 建表
CREATE TABLE `seat`  (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `student` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
);

-- 添加数据
INSERT INTO `seat` ( student )
VALUES
	( 'Abbot' ),
	( 'Doris' ),
	( 'Emerson' ),
	( 'Green' ),
	( 'Jeames' )

-- 查询
SELECT
	(
CASE
	
	WHEN MOD ( id, 2 ) = 1 
	AND id != maxid THEN
		id + 1 
		WHEN MOD ( id, 2 ) = 1 
		AND id = maxid THEN
			id ELSE id - 1 
		END 
		) AS id,
		student 
	FROM
		seat,
		( SELECT max( id ) AS maxid FROM seat ) AS TEMP 
ORDER BY
id;

项目九

  • 项目九: 分数排名（难度：中等）
    编写一个 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 |
    ±------±-----+
-- 查询
SELECT
	score,
	( SELECT COUNT( DISTINCT s1.score ) FROM score s1 WHERE s1.score >= s.score ) AS `rank` 
FROM
	Score s 
ORDER BY
	`rank`

项目十

  • 项目十：行程和用户（难度：困难）
    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-01 | 0.33 |
    | 2013-10-02 | 0.00 |
    | 2013-10-03 | 0.50 |
    ±-----------±------------------+
-- 建表
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
	total.request_at `Day`,
	FORMAT( CASE WHEN cancelled.num / total.num IS NULL THEN 0 ELSE cancelled.num / total.num END, 2 ) `Cancellation Rate` 
FROM
	(
SELECT
	t.Request_at,
	count( * ) 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%' 
	AND t.Request_at BETWEEN '2013-10-01' 
	AND '2013-10-03' 
GROUP BY
	t.Request_at 
	) cancelled
	RIGHT JOIN (
SELECT
	t.Request_at,
	count( * ) num 
FROM
	trips t
	JOIN users u ON u.user_id = t.Client_Id 
	AND u.Role = 'client' 
	AND u.Banned = 'No' 
	AND t.request_at BETWEEN '2013-10-01' 
	AND '2013-10-03' 
GROUP BY
	t.Request_at 
	) total ON cancelled.request_at = total.request_at

项目十一

  • 项目十一：各部门前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 |
    ±—±------±-------±-------------+
    编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：
    ±-----------±---------±-------+
    | Department | Employee | Salary |
    ±-----------±---------±-------+
    | IT | Max | 90000 |
    | IT | Randy | 85000 |
    | IT | Joe | 70000 |
    | Sales | Henry | 80000 |
    | Sales | Sam | 60000 |
    ±-----------±---------±-------+

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

SELECT
	d.`Name` Department,
	e2.`Name` 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`;

若需要实现前N高工资的员工功能，则把3改成对应的N即可。

项目十二

  • 项目十二 分数排名 - （难度：中等）
    项目九的分数表，实现排名功能，但是排名是非连续的，如下：
    ±------±-----+
    | Score | Rank |
    ±------±-----+
    | 4.00 | 1 |
    | 4.00 | 1 |
    | 3.85 | 3 |
    | 3.65 | 4 |
    | 3.65 | 4 |
    | 3.50 | 6 |
    ±------±-----
SELECT
	score,
	( SELECT COUNT( s.score ) FROM score s WHERE s.Score > sc.Score ) + 1 AS `Rank` 
FROM
	score sc 
ORDER BY
	`Rank`
qq_40819816
关注
MySQL 实战（一）
strmat的博客
03-03 1200
MySQL实战（一）1.数据导入导出1.1 Navicat导入，导出向导1.2 MySQL导入导出语句1.3 mysqlimport2.实战2.1 Excel数据导入导出2.2 各部门工资最高的员工2.3 换座位2.4 分数排名 1.数据导入导出 1.1 Navicat导入，导出向导 利用Navicat的导入向导和导出向导，可以方便地执行数据的导入导出： 导入向导 导入向导分为八个步骤： 选择...
【任务四】——by 003-Vamein
梦醒山中客
04-04 372
【任务三】 #任务时间# 请于4月6日22:00前完成，在[打卡表格]处打卡。逾期尚未打卡的会被清退。 3.1 MySQL 实战 #学习内容# 数据导入导出 将之前创建的任意一张MySQL表导出,且是CSV格式 再将CSV表导入数据库 步骤： 导出 选择“全部记录” 选择“csv文件”，点击“>>” 点击开始， 点击“关闭”，查看桌面， 点开查看，与原表格相同。 #作业# ...
【MySQL】4.实战项目
susanhc的博客
04-10 4391
文章目录mysql项目1. 找出每个部门工资最高的员工2. 换座位3. 分数排名5. 各部门前3高工资的员工解题思路6.分数排名 mysql项目 1. 找出每个部门工资最高的员工 1.创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。 创建Department 表，包含公司所有部门的信息。 编写一个 SQL 查询，找出每个部门...
数据库mysql实战
kakaka666的博客
08-21 934
数据库mysql实战
MYSQL实战
最新发布
hj的博客
12-18 561
0. mysql5.7 安装 1 .增删改查sql语句写一下 ： 写出Mysql创建HRM数据库的语句，如果HRM数据库存在，删除它重建；并在HRM数据库创建一张user表，表包含自增量id，以及姓名name(25个字符串) 2.公司单机数据库延伸主从架构 实施 3.主主模式实战演示 4.数据库MHA 高可用实施 5. 数据库架构延伸 实施读写分离 一个主库负责写 一个从库负责读取 6.给开发建立一个用户kf 密码Hello88 授权只能select读取权限 7. 数据库备份命令：
从 0 开始带你成为MySQL实战优化高手
02-08
【MySQL实战优化高手之路】 MySQL，作为世界上最受欢迎的关系型数据库管理系统之一，对于任何涉及数据库管理、开发或运维的IT专业人士来说，都是一个必备的技能。本资源集合旨在帮助你从零开始，逐步深入理解MySQL...
从 0 开始带你成为MySQL实战优化高手.txt
02-16
《从0开始带你成为MySQL实战优化高手》这一教程旨在帮助读者从零基础开始学习并掌握MySQL数据库优化的核心技能。MySQL作为世界上最受欢迎的关系型数据库管理系统之一，在互联网应用、企业级服务等多个领域发挥着至关...
从零开始带你成为MySQL实战优化高手PDF.txt
12-06
从零开始带你成为MySQL实战优化高手
从零开始带你成为MySQL实战优化高手【完整版】
03-29
失效私聊补上，132张
MySQL 实战
L19942013的博客
03-03 267
day5   偷个懒，这里就不写excel和mysql导入导出了，使用navicat右键表选择导入/导出向导，按步骤操作就好了。 1.找出每个部门工资最高的员工。 2.换座位 3.分数排名 ...
MySql实战
fhp123的博客
05-06 926
第1讲 基础架构：一条SQL查询语句是如何执行的 Server层，它主要做的是MySQL功能层面的事情；引擎层，负责存储相关的具体事宜。 连接器负责跟客户端建立连接、获取权限、维持和管理连接。 MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。不建议使用 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句.
mysql 数据库 实战
安全界的彭于晏的博客
06-28 168
mysql 数据库 数据库最高的权限用户是root密码保存在mysql数据中的user表中, 密码采用mysql5 特有的加密,通过cmd网站进行解密或通过cain等这列专业可以对 mysqlhash破解所以对数据库做安全的时候无论如何不能给网站root权限一定要给 一个普通用户权限. 实战过程 第一步先检查注入点 ’ and 1=1 and1=2 第二步 ’ order by 1,2–+ 查询主查询字段数 如果是在URL进行POST型字符型注入–后会加个+ 第三步 查询数据库用户名 版本和库名 第四
实战mysql_MySQL实战
weixin_33510623的博客
02-05 188
第1章 初识MySQL启动MySQL服务net start mysql(服务名)关闭MySQL服务net stop mysql(服务名)MySQL登录mysql -u root -p -P3306 -h127.0.0.1MySQL退出mysql > exit;mysql > quit;mysql > \q;修改MySQL提示符(1)连接客户端时通过参数指定shell>mys...
实战mysql_MYSQL实战
weixin_33812391的博客
01-19 342
1、mysql可分为server层和存储引擎1.1 server层：连接器、查询缓存、分析器、优化器 、执行器、包含所有内置函数(日期，时间，数学、加密函数)，所有跨存储引擎的功能都在此层，比如存储过程、触发器、视图等1.1.1 连接器：负责跟客户端建立连接、获取权限、维持和管理连接mysql -h$host -P$port -u$user -p$password备注：a、sleep状态是连接完成...
MySQL数据库实战（1）
摸鱼中......
08-02 752
MySQL数据库实战（1）
MySQL实战：日志与索引疑问解析
在"MySQL实战第15讲日志索引相关问题"中，作者林晓斌针对MySQL数据库中的关键概念进行了深入探讨。该章节主要聚焦于日志系统，特别是binlog（归档日志）和redolog（重做日志）在事务处理中的重要角色以及它们在...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值