Mysql刷题经验

最新推荐文章于 2024-04-25 17:04:54 发布

程序员Realeo

最新推荐文章于 2024-04-25 17:04:54 发布

阅读量330

点赞数

分类专栏：数据库大数据文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/perfect_zdq/article/details/129121693

版权

数据库同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

大数据

5 篇文章 0 订阅

订阅专栏

Mysql刷题经验

SQL运行顺序

(1)from 　　(2) join 　　(3) on 　　(4) where 　　(5)group by 　　(6) avg,sum… （组函数）　　(7)having 　　(8) select 　　(9) distinct 　　(10) order by

常用函数

日期函数

TIMESTAMPDIFF(unit,begin,end)
- 函数返回end-begin的结果，其中begin和end是DATE或DATETIME表达式
- 函数允许其参数具有混合类型，例如，begin是DATE值，end可以是DATETIME值。如果使用DATE值，则TIMESTAMPDIFF函数将其视为时间部分为“00:00:00”的DATETIME值。
- unit参数是确定(end-begin)的结果的单位，表示为整数。以下是有效单位
- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
datediff(date1,date2)=int
- date1: 比较日期1
- date2: 比较日期2
- DATEDIFF函数返回date1 - date2的计算结果，date1和date2两个参数需是有效的日期或日期时间值;如果参数传递的是日期时间值，DATEDIFF函数仅将日期部分用于计算，并忽略时间部分(只有值的日期部分参与计算)
  
  DATEDIFF函数与TIMESTAMPDIFF函数的区别
  
  1.DATEDIFF函数仅用于返回两个日期的天数，TIMESTAMPDIFF函数用于返回计算两个日期指定单位的时间差(指定单位可以是年，季度，月，星期，天数，小时，分钟，秒等等)
  
  2.对日期差值的计算方式相反
  
  DATEDIFF函数的语法格式: DATEDIFF(start,end)
  
  DATEDIFF函数返回start - end的计算结果
  
  TIMESTAMPDIFF函数的语法格式: TIMESTAMPDIFF(DAY,start,end)
  
  TIMESTAMPDIFF函数返回end - start的计算结果
  
  – -2
  SELECT DATEDIFF(‘2022-04-28’, ‘2022-04-30’);
  – 2
  SELECT TIMESTAMPDIFF(DAY,‘2022-04-28’, ‘2022-04-30’);
  3.DATEDIFF则是直接截取日期的部分相减。那么此时使用两个函数计算出来的天数是不一样的
  
  – 2
  SELECT DATEDIFF(‘2022-04-30 00:00:00’,‘2022-04-28 23:59:59’);
  
  如果时间是YYYY-MM-DD HH:MM:SS格式，在计算天数差时，TIMESTAMPDIFF使用的是24小时制，即使是23:59:59，也不算做是一天
  
  – 1
  SELECT TIMESTAMPDIFF(DAY,‘2022-04-28 23:59:59’, ‘2022-04-30 00:00:00’);
  
  – 0
  SELECT TIMESTAMPDIFF(DAY,‘2022-04-29 14:00:00’, ‘2022-04-30 13:59:59’);
  
  时间格式为YYYY-MM-DD HH:MM:SS时，使用两个函数的结果是不同的，需要根据具体的业务场景进行选择
year(date)
month(date)
day(date)
date_format(date, “%Y-%m”):参考
DATE_SUB(date,INTERVAL expr type):参考

分组聚合

avg()
max()
min()
count()：字段为null不用判断，相当于没有

数据类

distinct()
round(x,d)
- 用于数据的四舍五入
- x指要处理的数，d是指保留几位小数
- d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0
- round(x) ,其实就是round(x,0),也就是默认d为0；
下面是几个实例

1、查询: select round(1123.26723,2);

结果：1123.27

2、查询: select round(1123.26723,1);

结果: 1123.3

3、查询: select round(1123.26723,0);

结果：1123

4、查询: select round(1123.26723,-1);

结果: 1120

5、查询: select round(1123.26723,-2);

结果：1100

5、查询: select round(1123.26723);

结果：1123

条件函数

ifnull(判断对象,赋值)
- 数据库字段为null时，用此函数处理为0或预期值
if表达式：IF(expr1,expr2,expr3);
- 如果expr1为TRUE，则IF()返回值为expr2，否则返回值为expr3

case when p_id is null then "Root"
         when id not in (select p_id from tree) then "Leaf"
         else "Inner"
    end as Type

文本函数

查找定位

LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；
POSITION(substr IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；
LEFT(str, length)：从左边开始截取str，length是截取的长度；
RIGHT(str, length)：从右边开始截取str，length是截取的长度；
SUBSTRING_INDEX(str ,substr ,n)：返回字符substr在str中第n次出现位置之前的字符串;
SUBSTRING(str ,n ,m)：返回字符串str从第n个字符截取到第m个字符；
REPLACE(str, n, m)：将字符串str中的n字符替换成m字符；
LENGTH(str)：计算字符串str的长度

文本处理

TRIM(remstr FROM str)：要删除的字符串，默认是删除空格’ ’
concat(str1, str2,…)：返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null
concat_ws(separator, str1, str2, …)：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null
group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
- 功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果
- 通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

窗口函数

背景：

MySQL从8.0开始支持开窗函数，这个功能在大多商业数据库中早已支持，也叫分析函数。
窗口函数分为静态窗口和滑动窗口，静态窗口的大小是固定的，滑动窗口的大小可以根据设置进行变化，在当前窗口下生成子窗口。
窗口函数针对over后对窗口范围内的每条记录都执行这个窗口函数，举个例子：
```
SELECT gender, day, SUM(score_points) OVER (PARTITION BY gender ORDER BY day) AS total FROM Scores;
```
- SUM(score_points) OVER (PARTITION BY gender ORDER BY day) ,表示针对gender分组，对day排序的窗口每条记录都执行sum(score_points）累计分数的操作

语法：

函数名([参数])  
over(
partition by [分组字段] 
order by [排序字段] asc/desc 
rows/range between 起始位置 and 结束位置
)

函数名称，开窗函数的数量较少，只有11个窗口函数(rank, dense_rank, row_number)+聚合函数(sum. avg, count, max, min)
over语句，over()是必须要写的，里面有三个参数，都是非必须参数，根据需求选写
- 第一个参数是 partition by +分组字段，将数据根据此字段分成多份，如果不加partition by参数，那会把整个数据当做一个窗口。
- 第二个参数是 order by +排序字段，每个窗口的数据要不要进行排序。
- 第三个参数 rows/range between start and end，这个参数仅针对滑动窗口函数有用，是在当前窗口下分出更小的子窗口。
  - rows 6 preceding #向前六行，加上本行总共七行
  - rows between 2 preceding and current row # 取当前行和前面两行
  - rows between unbounded preceding and current row # 包括本行和之前所有的行
  - rows between current row and unbounded following # 包括本行和之后所有的行
  - rows between 3 preceding and current row # 包括本行和前面三行
  - rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行
  - 当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded preceding and current row.
  - 当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following

在这里插入图片描述

其他

having
- 当聚合条件作为筛选结果时，不可以使用where函数，要使用having函数
- where 不能跟聚合函数限制做筛选，having可以实现
- having的执行语句顺序：selcet——from——where——group by——having
union:而union等价于or
union all:不去重

组合查询

判断条件成立次数总和
- sum(if(result = ‘right’,1,0))
name分组的所有组的id和score
- select name,group_concat(concat_ws(‘-’,id,score) order by id) from table group by name

Tips

on和where条件的却别
- 在内连接的时候，也就是使用inner join …on的时候，on和where作用是相同的。
- 在外连接的时候，on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。而where条件是在临时表生成好后，再对临时表进行过滤的条件。
- 在外连接（left/right）的时候，会先把左边/右边的所有记录先记录下来，然后再根据on找到另一张表的所有记录，若不满足所有的on，那么直接赋值为NULL。而where是在两张表链接后进行筛选，若不匹配直接过滤掉。
ORDER BY对后所有排序字段有效，升序和降序要给每个字段单独设定
- 升序排序asc可以省略，降序排列不能省略；
- SELECT device_id,gpa,age FROM user_profile ORDER BY gpa,age;
- SELECT device_id,gpa,age FROM user_profile ORDER BY gpa DESC,age DESC;