【MySQL基础】05常用函数

sql的函数是有很多的，我就写一下关键的几个函数。当时在上课的时候，老师有给一个课件，里面包含了比较全的常见的函数，有需要的筒子们可以下载：网盘地址：链接:https://pan.baidu.com/s/1HcCuxIU5hZiLrlZntabuaw  密码:g3lr

---

目录

一、字符串函数

二、数学函数

三、时期时间函数

四、分组合并函数

五、逻辑函数

六、开窗函数

七、序号函数

---

一、字符串函数

1. concat(str1,str2,…)：合并字符串

【注意】合并时，其中有个字段是null那么返回结果也是null，null不代表空格，需要空格，可以写‘ ’

二、数学函数

1、floor(n)：向下取整

【例】select floor(1.23);  -- 1

Select floor(-1.23);  -- -2

2、Ceiling(n)：向上取整

【例】select ceiling(1.23);  -- 2

Select ceiling(-1.23);  -- -1

3、Rand([n])：返回范围0到1内的随机数，n作为初始值，也叫随机种子。n是固定随机数多少。

【例】select rand(2);  -- 0.65558664465490 #固定了n，在其他任何时候运算得到的值是一样的，避免模型在不同时候运算因随机数而导致结果不同。

三、时期时间函数

1. date_format(date,format)：转换时间格式

四、分组合并函数

1、group_concat([distinct] str [order by str asc/desc] [separator])

将group by 产生的同一个分组的值合并成一个字符串。

【参数】

distinct：去重

orderby：排序

separato：指定分割符号

【例子】

Select deptno, group_concat(distinct ename order by sal desc separator '/') 
from emp 
group by deptno;

【注意】

• 需要和group by 一起使用；
• Group_concat()函数会忽略空值。

五、逻辑函数

1、ifnull(expression,alt_value)

判断第一个参数是否为空值NULL，如果为NULL则返回第二个参数值，如果不为NULL则返回自己。常用于代替空值为0场景。

2、If(expr1,expr2,expr3)

判断如果expr1为true，则返回expr2，如果为false，则返回expr3。

3、Case when expr1 then expr2 [when expr3 then expr4 … else expr5 ] end 

判断如果expr1为true，则返回expr2，如果为expr1为false且expr3为true，则返回expr4…

六、开窗函数

1、开窗函数名([<字段名>]) over( [partition by <分组字段>] [order by <排序字段> [desc] ] [<滑动窗口>])

【注意】

• over和groupby分组的意义是一样的，都是分组统计；
• 区别在于group by 是针对分组后的组统计，返回的结果是一个组一个结果，而over是对每条记录执行统计，即每行记录后都有结果，结果一样也是分组后统计的。也可以理解为：group by的select只能显示分组字段和聚合函数，而分窗over可以显示所有字段。
• OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号中的选项为空，则代表不做任何分组。 
• 当over中指定分区和排序，但没有指定滑动窗口（rows），默认计算的是分区内第一行到当前行的值。

【例】

2、滑动窗口的7种写法

1. Rows between 1 preceding and current row: 前一行和当前行共两行记录 
2. Rows between current row and 1 following: 当前行和后一行共两行记录 
3. Rows between 1 preceding and 1 following:前一行、当前行、后一行共三行记录 
4. Rows unbounded preceding:分区中第一行到当前行 
5. Rows between unbounded preceding and current row:分区中第一行到当前行 
6. Rows between current row and unbounded following:分期中当前行到最后一行
7. Rows between unbounded preceding and unbounded following:分区中所有行记录，等同于不写

【例子】

select *,avg(sal) over(partition by deptno order by hiredate rows between 1 preceding and 1 following) 部门平均工资 from emp;

七、序号函数

1. row_number()：显示分区中不重复不间断的序号
2. dense_rank()：显示分区中重复不间断的序号
3. Rank()：显示分区中重复间断的序号

【注意】

row_number():数值相等，排名不会相等，会根据数值插入时间排名

dense_rank():数值相等，排名会相等，下个排名不会间隔 

rank():数值相等，排名会相等，下个排名会间隔