Mysql

| | | |

Mysql经典入门练习题

<一>分数排名

1.分数排名

题目：

编写一个 SQL 查询student表的分数排名。如果两个分数相同，则两个分数排名（Rank）相同。

例如：

1
2
3
4
5
6
7
8
9
10
±—±------+
| Id | Score |
±—±------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
±—±------+

1根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

1
2
3
4
5
6
7
8
9
10
±------±-----+
| Score | Rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
±------±-----+

解题思路：

需要用到的函数：

四大排名函数之一：dense_rank:分组并列连续排名

DENSE_RANK()函数也是排名函数，和RANK()功能相似，也是对字段进行排名,DENSE_RANK()密集的排名他和RANK()区别在于，排名的连续性，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，所以一般情况下用的排名函数就是RANK()。

使用语法：

1
2
select dense_rank() over(order by 字段),字段,...
from 表名;

解答：

1
2
select score,dense_rank() over(order by score) as id
from student;

2.实现排名功能，但是排名是非连续的

题目：

排名如下：

1
2
3
4
5
6
7
8
9
10
±------±-----+
| Score | Rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 3 |
| 3.65 | 4 |
| 3.65 | 4 |
| 3.50 | 6 |
±------±-----

解题思路：

需要用到的函数：

四大排名函数之一：rank:分组并列跳跃排名

RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，这里为什么和ROW_NUMBER()不一样那，ROW_NUMBER()是排序，当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()则不一样出现相同的，他们的排名是一样的。

使用语法：

1
2
select rank() over(order by 字段),字段,...
from 表名;

解答：

1
2
select score,rank() over(order by score) as id
from student;

扩展：

Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）
row_number:分组连续排名

row_number的用途的非常广泛，一般可以用来实现web程序的分页，他会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

使用语法：

1
2
select row_number() over(order by 字段),字段,...
from 表名;

NTILE()函数函数的使用

NTILE()函数是将有序分区中的行分发到指定数目的组中，各个组有编号，编号从1开始，就像我们说的’分区’一样 ，分为几个区，一个区会有多少个 。

使用语法

1
2
select ntile(*) over(order by 字段),字段,...
from 表名;

可观看178.分数排名这篇文章。密码178。里面有详细对这四大排名函数的使用。

<二>换座位

题目：

力扣626. 换座位

有一张student座位表，id为学生座位号，其中id 是连续递增的。

现在想改变相邻俩学生的座位，你能不能写一个 SQL来输出想要的结果呢？

1
2
3
4
5
6
7
8
9
10
示例：
±--------±--------+
| id | student |
±--------±--------+
| 1 | Abbot  |
| 2 | Doris  |
| 3 | Emerson |
| 4 | Green  |
| 5 | Jeames |
±--------±--------+

假如数据输入的是上表，则输出结果如下：

1
2
3
4
5
6
7
8
9
±--------±--------+
| id | student |
±--------±--------+
| 1 | Doris  |
| 2 | Abbot  |
| 3 | Green  |
| 4 | Emerson |
| 5 | Jeames |
±--------±--------+

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

解题思路：

这里要用到一个函数：

if函数

if函数相当于Java/C++的三元运算符。

IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。

使用语法：

1
2
select if(i>0,i+1,i-1) as i
from 表名;

这一题的思路是：

我们使用if函数，如果id为偶数，那就让id-1；否则如果id是奇数让id+1，否者不变(但是我们发现不需要改变最后一个同学的座位，那么，我们还需要一个判断)使用if函数，如果id是奇数并且id不是表中最大(如何知道是最大的呢？当然要用查询max(id))的，那就让id+1,否则就不改变id值。这样即可实现换座位了。换位座位不要忘了排序哦~

那这个代码怎样写呢？

1
if(id%2=0,id-1,if(id%2=1 and id!=(select max(id) from student),id+1,id)

解答：

1
2
3
4
select 
if(id%2=0,id-1,if(id%2=1 and id!=(select max(id) from student),id+1,id) as id,student
from student
order by id asc;

扩展：

IFNULL()函数的使用
IFNULL(expr1,expr2)，如果expr1的值为null，则返回expr2的值，如果expr1的值不为null，则返回expr1的值。
NULLIF()函数的使用
NULLIF(expr1,expr2)，如果expr1=expr2成立，那么返回值为null，否则返回值为expr1的值。
ISNULL()函数的使用
ISNULL(expr)，如果expr的值为null，则返回1，如果expr1的值不为null，则返回0。

<三>关于Date的函数

题目：

现有一份某个网站一天用户登录日志表，如附件。你需要导入Mysql数据库，这里需要的数据是用户ID（id），和时间日志（time）。请写一段SQL语句得到登录人数最高的三个小时人数的平均值。

链接：https://pan.baidu.com/s/1jqiLDMVjhkAWlXMPkeXEhw
提取码：qqnv

解题思路：

分析：

<1>.首先要导入数据表，如果你有navicat直接就可以导入就成。

image-20200724231041896

导入之后你会发现，time是varchar类型的。并不是Date类型的呀！

image-20200724231114209

所以要用到str_to_date （）函数

str_to_date （）函数

1.含义：是将时间格式的字符串（str），按照规定的显示格式（format）转换为DATETIME类型

2.语法： str_to_date(str,format)

3.使用：

1
SELECT STR_TO_DATE('2020725','%Y%m%d') AS TIME；
image-20200724231618811

<2>.需要得到登录人数最高的三个小时人数

首先取最高的登录三小时的人数：要用到limit函数

image-20200724231717266

求人数的平均值，那就需要用到count函数咯！并且要使用order by进行倒序排序选出最大的三个。

image-20200724232138889

<3>.把上面的表作为一个临时表来进行取平均值即可

解答：

1
2
3
4
5
6
7
select avg(num)
from(select str_to_date(time,'%H') as ti,count(first_name) as num
     from data1
     group by ti
     order by num desc
     limit 3
) as a;

image-20200724232937552

扩展：

1.DATE_FORMAT() 函数:用于以不同的格式显示日期/时间数据。
语法
1
DATE_FORMAT(date,format)

date 参数是合法的日期。format 规定日期/时间的输出格式。

可以使用的格式有：(摘自：https://www.w3school.com.cn/sql/func_date_format.asp)

格式 描述
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位
2.Limit函数
1.LIMIT子句简介

（原文链接：https://www.yiibai.com/mysql/limit.html ）

在SELECT语句中使用LIMIT子句来约束结果集中的行数。LIMIT子句接受一个或两个参数。两个参数的值必须为零或正整数。

下面说明了两个参数的LIMIT子句语法：

1
2
3
4
5
6
SELECT 
    column1,column2,...
FROM
    table
LIMIT offset , count;
SQL

我们来查看LIMIT子句参数：

  • offset参数指定要返回的第一行的偏移量。第一行的偏移量为0，而不是1。
  • count指定要返回的最大行数。
2. 使用MySQL LIMIT获取前N行

可以使用LIMIT子句来选择表中的前N行记录，如下所示：

1
2
3
4
5
6
SELECT 
    column1,column2,...
FROM
    table
LIMIT N;
SQL

例如，要查询employees表中前5个客户，请使用以下查询：

1
SELECT customernumber, customername, creditlimit FROM customers LIMIT 5;
3. 使用MySQL LIMIT获得最高和最低的值

LIMIT子句经常与ORDER BY子句一起使用。首先，使用ORDER BY子句根据特定条件对结果集进行排序，然后使用LIMIT子句来查找最小或最大值。

注意：ORDER BY子句按指定字段排序的使用。

4. 使用MySQL LIMIT获得第n个最高值

MySQL中最棘手的问题之一是：如何获得结果集中的第n个最高值，例如查询第二(或第n)贵的产品是哪个，显然不能使用MAX或MIN这样的函数来查询获得。 但是，我们可以使用MySQL LIMIT来解决这样的问题。

通用查询如下：

1
2
3
4
5
6
SELECT 
    column1, column2,...
FROM
    table
ORDER BY column1 DESC
LIMIT nth-1, count;
相关文章
点击查看
-------------------本文结束 感谢您的阅读-------------------