MySQL常用函数介绍

MySQL常用函数介绍

ASCII

ASCII函数用于显示字符串第一个字符的ASCII码

# 假设sex用m代表男，用f代表女，修改数据表，将所有的m改成f，将所有的f改成m
update tableName set sex = char(ascii('m') + ascii('f') - sex)

CASE WHEN

case when 类似于java中的 if else

# 使用方法：
CASE WHEN condition THEN result
[WHEN...THEN...]
ELSE result
END
# condition是一个返回布尔类型的表达式，如果表达式返回true，则整个函数返回相应result的值，如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。

# 例如：
CASE SCORE WHEN 'A' THEN
CASE SCORE WHEN 'B' THEN
CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END
# 等价于
CASE SCORE WHEN 'A' THEN '优'
     	   WHEN 'B' THEN '良'
           WHEN 'C' THEN '中' ELSE '不及格' END
#  THEN后边的值与ELSE后边的值类型应一致，否则会报错。

IF 和 IFNULL

# condition是一个布尔表达式，为真时返回expr1，否则返回expr2
if(condition,expr1,expr2)  # 类似于三目运算符

# 当expr1不为NULL时返回expr1，否则返回expr2
IFNULL(expr1 , expr2)

STRCMP

strcmp函数用于比较两个字符串，如果这两个字符串相等返回0，如果第一个参数是根据当前的排序小于第二个参数顺序返回-1，否则返回1。

# 举例
SELECT STRCMP('MOHD', 'MOHD'); # 返回 0 
SELECT STRCMP('AMOHD', 'MOHD'); # 返回-1
SELECT STRCMP('MOHD', 'AMOHD'); # 返回1

MOD

mod(a,b)即对 a/b 取余

mod(id,2) = 1;	# id为奇数
mod(id,2) = 0;	# id为偶数

LEFT

# 返回从字符串表达式的开始(最左位置)开始的指定数量的字符。
LEFT(str,num)

RIGHT函数正好相反！

concat

用于将两个或多个字符串连接起来，形成一个单一的字符串。

# eg:
SELECT CONCAT(id, name, work_date) FROM employee_tbl;

concat_ws

使用方法：

​ concat_ws(separator,str1,str2,…)

• 第一个参数是分隔符，分隔符的位置要放在两个字符串连接的位置中间，分隔符可以是一个字符串，也可以是其他参数。
• 如果分隔符是NULL，那么结果也为NULL

group_concat

使用语法如下：

​ group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

upper和lower

upper： 以大写形式显示结构化查询语言中的所有字符串字符。

lower： 以小写形式显示结构化查询语言中的所有字符串字符。

substring

作用：截取字符串中的一部分字符。有两种用法

# 第一种：
substring(str,position,length)
# 第一个参数是指需要截取的字符串。
# 第二个参数是指从哪个位置开始截取字符串（从1开始计数），若为负数则表示从右向左开始数位置。
# 第三个参数是指需要截取的字符串的长度，如果不写，则默认截取到最后一位。

# 第二种：
substring(str from position for length)
# 参数函数通上所述
# for length可以省去，默认截取到最后

DATEDIFF

作用：返回两个日期之间的天数

用法：datediff(date1,date2)

# eg:
SELECT DATEDIFF("2020-11-20", "2020-11-1");		# 输出结果为19
SELECT DATEDIFF("2020-11-12", "2020-11-19");	# 输出结果为-7
SELECT DATEDIFF("2020-11-20 09:34:21", "2020-11-17 09:34:21");  	# 输出结果为3
SELECT DATEDIFF("2020-11-20 09:34:21", "2020-11-20 08:11:23");  	# 输出结果为0

limit

作用：提取出前几行或中间某几行数据

用法：select * from table limit m,n;

• m表示：记录开始的位置，从0开始，表示第一条数据；
• n表示：从第m+1条开始，取n条数据
• 与offset合用时，limit n offset m 表示跳过前m行，取接下来的n行

# eg:找出工资排名在第4到第9名的员工。
SELECT ename, sal 
from emp 
order by sal desc 
limit 3, 6;
# 取前5条数据
limit 0,5 等同于 limit 5
# 取第二行数据
limit 1,1 等价于 limit 1 offset 1

窗口函数

1.窗口函数的定义

​ MySQL从8.0开始支持窗口函数，这个功能在大多数据库中早已支持，有的也叫分析函数。窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数对于每条记录都要在此窗口内执行函数。简单的说窗口函数就是对于查询的每一行，都使用与该行相关的行进行计算。

​ 窗口函数也叫OLAP函数(Online Analytical Processing,联机分析处理)，可以对数据进行实时分析处理。窗口函数和group by有类似之处，其区别在于窗口会对每个分组之后的数据进行分别操作，而group by一般对分组之后的函数使用聚合函数汇总。

2.窗口函数与普通聚合函数的区别

①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。

②聚合函数也可以用于窗口函数。

3.常见的窗口函数

名称	参数	描述
ROW_NUMBER()	否	当前行在其分组内的序号.不管其排序结果中是否出现重复值.其排序结果都为:1,2.3.4.5.
DENSE_RANK()	否	不间断的组内排序.使用这个函数时,可以出现1.1.2.2这种形式的分组。
RAN()	否	间断的组内排序。其排序结果可能出现如下结果:1.1.3.4.4.6
PERCENT_RANK()	否	累计百分比。该函数的计算结果为:小于该条记录值的所有记录的行数/该分组的总行数-1.所以该记录的返回值为[0,1]。
CUME_DIST()	否	累计分布值。即分组值小于等于当前值的行数与分组总行数的比值。取值范围为(0.1]。
LAG()	lag(expr,[N,[detaut]])	从当前行开始往前取第N行，如果N缺失，默认为1。如果不存在前一行.则默认返回default。default默认值为NULL。
LEAD()	lead(expr,[N,[defaut]])	从当前行开始往后取第N行。函数功能与lag()相反.其余与lag()相同。
FIRST_VALUE()	frst_value(expr)	返回分组内截止当前行的第一个值。
LAST_VALUE()	last_value(expr)	返回分组内截止当前行的最后一个值。
NTH_VALUE()	nth_value(expr.N)	返回分组内截止当前行的第N行。与first_value\last_value\nth_ value函数功能相似，只是返回分组内截止当前行的不同行号的数据。
NTILE()	ntile(N)	返回当前行在分组内的分桶号。在计算时要先将该分组内的所有数据划分成N个桶。之后返回每个记录所在的分桶号。返回范围从1到N。

4.窗口函数分类

• 序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
• 分布函数：PERCENT_RANK()、CUME_DIST()
• 前后函数：LAG()、LEAD()
• 头尾函数：FIRST_VALUE()、LAST_VALUE()
• 其它函数：NTH_VALUE()、NTILE()

序号函数：

ROW_NUMBER()：顺序排序——1、2、3，

RANK()：并列排序，跳过重复序号——1、1、3，
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2。

分布函数：

每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

前后函数：

LAG和LEAD分析函数可以在同一次查询中取出同一字段的前N行的数据(LAG)和后N行的数据(LEAD)作为独立的列。
NTH_VALUE(expr, n)：

其中NTH_VALUE(expr, n)中的第二个参数是指这个函数取排名第几的记录，返回窗口中第n个expr的值。

NTILE(n)函数：

将分区中的有序数据分为n个等级，记录等级数。

5.窗口函数的用法

window_function(expr)
OVER( partition by ……
order by ……
frame_clause ……
)

如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：
partition by子句：按照指定字段进行分区，两个分区由边界分隔，窗口函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
order by子句：按照指定字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。

round

作用：用于数据的四舍五入。

用法：

1. round(x,d)：x指要处理的数，d指要保留d位小数。
2. round(x)：其实就是round(x,0)。默认d为0.

# eg:
select round(3.142592653,2)		# 结果为3.14

exist

1. 建表时使用：

# 如果表存在就删除
drop table tablename if exists temp;

2. 查找时使用：

# 放在where后面，格式如下：
select * from tablename where exists(子查询)
# 如果子查询有任意数据返回，exists就返回true，子查询外的查询语句执行
# 如果子查询没有数据返回，exists就返回false，子查询外的查询语句就不执行

# eg:如果存在叫小王的妹子的话，找出他的男朋友
select bname from boy b where exists (select g.boyfriend_id from girl g 
                                      where g.boyfriend id = b.id and g.name ='小王')