Datawhale-MySQL-Task3
4.1 MySQL 实战
1. 学习内容
数据导入导出
将之前创建的任意一张MySQL表导出,且是CSV格式
再将CSV表导入数据库
简答:
导出命令: select * from table into outfile “d:/tmp/data/1.csv”;
导入命令: load data infile “d:/tmp/data/1/csv” into table address ;
出现问题:
- The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
查看secure_file_priv的变量的地址,使用命令 select variables like '%secure%'; 注意变量名为secure-file-priv, 不是下划线
- 1 (HY000): Can’t create/write to file ‘c:\1.csv’ (OS errno 13 - Permission denied)
修改对应的权限,允许用户可以创建文件 是否对C盘权限只开放了administrators组的权限,如是,请打开C:\windows\TEMP赋予该目录Everyone所有的权限.重新测试 或者 设置其他目录 d:/
2.OVER(PARTITION BY)函数介绍
摘抄至https://www.cnblogs.com/lcngu/p/5335170.html文章内容。
1、over函数的写法:
over(partition by class order by sroce) 按照sroce排序进行累计,order by是个默认的开窗函数,按照class分区。
2、开窗的窗口范围:
over(order by sroce range between 5 preceding and 5 following):窗口范围为当前行数据幅度减5加5后的范围内的。
over(order by sroce rows between 5 preceding and 5 following):窗口范围为当前行前后各移动5行。
**ORDER BY子句的语法**
ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2
RANGE表示按照值的范围进行范围的定义,而ROWS表示按照行的范围进行范围的定义;边界规则的可取值见下表:
“RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2”部分用来定位聚合计算范围,这个子句又被称为定位框架。
3、与over()函数结合的函数的介绍
SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;
(1)求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果。
SELECT * FROM (select t.name,t.class,t.sroce,row_number() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;
(2)rank()和dense_rank()可以将所有的都查找出来,rank可以将并列第一名的都查找出来;rank()和dense_rank()区别:rank()是跳跃排序,有两个第二名时接下来就是第四名。
rank(), dense_rank()是用于排名, 处理并列排序的方式不同,dence_rank在并列关系是,相关等级不会跳过。rank则跳过。
rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)
dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。
select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
select t.name,t.class,t.sroce,dense_rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
## dense_rank()l是连续排序,有两个第二名时仍然跟着第三名
(3)sum()over()的使用
##根据班级进行分数求和
select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
(4) first_value() over()和last_value() over()的使用
##分别求出第一个和最后一个成绩。
select t.name,t.class,t.sroce,first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
select t.name,t.class,t.sroce,last_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
(5)sum() over()的使用
select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
-------------------
count() over(partition by ... order by ...):求分组后的总数。
max() over(partition by ... order by ...):求分组后的最大值。
min() over(partition by ... order by ...):求分组后的最小值。
avg() over(partition by ... order by ...):求分组后的平均值。
lag() over(partition by ... order by ...):取出前n行数据。
lead() over(partition by ... order by ...):取出后n行数据。
ratio_to_report() over(partition by ... order by ...):Ratio_to_report() 括号中就是分子,over() 括号中就是分母。
percent_rank() over(partition by ... order by ...):
(6)rank(),dense_rank()语法:
RANK()
dense_rank()
【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
【参数】dense_rank与rank()用法相当,
【区别】dence_rank在并列关系是,相关等级不会跳过。rank则跳过
rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)
dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。
(7)row_number() 函数语法:
ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根据COL1分组,在分组内部根据 COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的)
row_number() 返回的主要是“行”的信息,并没有排名
【参数】
【说明】Oracle分析函数
主要功能:用于取前几名,或者最后几名等
3. over partition by与group by的区别:
group by是对检索结果的保留行进行单纯分组,一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。partition by虽然也具有分组功能,但同时也具有其他的高级功能。
作业
项目七: 各部门工资最高的员工(难度:中等)
创建 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 |
简单
项目八: 换座位(难度:中等)
小美是一所中学的信息科技老师,她有一张 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 |
注意:
如果学生人数是奇数,则不需要改变最后一个同学的座位。
简答
项目九: 分数排名(难度:中等)
编写一个 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 |
简答
4.2 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 |
简答
项目十一:各部门前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 |
简答
引用:
- https://blog.csdn.net/sinat_38640606/article/details/83072570
- https://blog.csdn.net/wenqiwenqi123/article/details/82466598
- https://blog.csdn.net/sphinx1122/article/details/84189855
- https://blog.csdn.net/caoxiaohong1005/article/details/69228592
- https://www.cnblogs.com/lcngu/p/5335170.html