MySQL 实战

Wesley13
• 阅读 533

项目七: 各部门工资最高的员工(难度:中等)

创建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 |

+----+-------+--------+--------------+

MySQL 实战

创建Department 表,包含公司所有部门的信息。

+----+----------+

| Id | Name |

+----+----------+

| 1 | IT |

| 2 | Sales |

+----+----------+

MySQL 实战

编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。

+------------+----------+--------+

| Department | Employee | Salary |

+------------+----------+--------+

| IT | Max | 90000 |

| Sales | Henry | 80000 |

+------------+----------+--------+

 MySQL 实战

项目八: 换座位(难度:中等)

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

+---------+---------+

注意:如果学生人数是奇数,则不需要改变最后一个同学的座位。

MySQL 实战

MySQL 实战

查询SQL代码:

 1 SELECT * from (
 2 SELECT id+1 as id,student from seat
 3 where id % 2=1 and id !=(SELECT COUNT(*) from seat)
 4 UNION
 5 SELECT id-1 as id,student from seat
 6 where id % 2=0
 7 UNION
 8 SELECT id,student from seat
 9 where id = (SELECT COUNT(*) from seat)) as s
10 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 |

+-------+------+

 1 -- 创建score表
 2 CREATE TABLE score(
 3 id int not null PRIMARY key,
 4 score FLOAT(5,2) NULL);
 5 
 6 -- 插入数据 
 7 INSERT INTO score VALUES
 8 (1,3.50),
 9 (2,3.65),
10 (3,4.00),
11 (4,3.85),
12 (5,4.00),
13 (6,3.65);

MySQL 实战

1 -- 查询分数排名。如果两个分数相同,则两个分数排名(Rank)相同
2 SELECT score,(SELECT COUNT(DISTINCT score) FROM score WHERE score >= s.score) as rank
3 from score as s
4 ORDER BY rank;

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|

+----+-----------+-----------+---------+--------------------+----------+

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 |

+------------+-------------------+

 1 -- 创建trips表
 2 CREATE TABLE Trips(
 3 id int not null PRIMARY key,
 4 client_id int not null,
 5 driver_id int not null,
 6 city_id int not null,
 7 status enum('completed','cancelled_by_driver','cancelled_by_client') not null,
 8 request_at DATE not null
 9 );
10 
11 -- 插入数据
12 INSERT INTO Trips VALUES
13 (2,2,11,1,'cancelled_by_driver','2013-10-1'),
14 (3,3,12,6,'completed','2013-10-01'),
15 (4,4,13,6,'cancelled_by_client','2013-10-01'),
16 (5,1,10,1,'completed','2013-10-02'),
17 (6,2,11,6,'completed','2013-10-02'),
18 (7,3,12,6,'completed','2013-10-02'),
19 (8,2,12,12,'completed','2013-10-03'),
20 (9,3,10,12,'completed','2013-10-03'),
21 (10,4,13,12,'cancelled_by_driver','2013-10-03');

 1 -- 创建users表
 2 CREATE TABLE Users(
 3 users_id int not null primary key,
 4 banned varchar(50) not null,
 5 role enum('client','driver','partner') not NULL
 6 );
 7 
 8 -- 插入数据
 9 INSERT INTO Users VALUES
10 ('1','No','client'),
11 ('2','Yes','client'),
12 ('3','No','client'),
13 ('4','No','client'),
14 ('10','No','driver'),
15 ('11','No','driver'),
16 ('12','No','driver'),
17 ('13','No','driver');

待补充

项目十一:各部门前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高工资的员工功能。

项目十二 分数排名 - (难度:中等)

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

+-------+------+

| Score | Rank |

+-------+------+

| 4.00 | 1 |

| 4.00 | 1 |

| 3.85 | 3 |

| 3.65 | 4 |

| 3.65 | 4 |

| 3.50 | 6 |

+-------+------

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Wesley13 Wesley13
2年前
Oracle——分页查询
查询员工表中,工资排名在1020之间的员工信息。select  from(    selectrownumrn,employee_id,salary      from(        selectemployee_id,salary,last_name        fromem
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
2年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
2年前
mysql——GROUP BY和HAVING
GROUPBY语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。select子句中的列名必须为分组列或列函数,列函数对于groupby子句定义的每个组返回一个结果。某个员工信息表结构和数据如下:  id  name  dept  salary  edlevel     hiredate   1  张
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究