MySQL学习Day07——函数

一、MySQL函数：

在MySQL中，为了提高代码的重用性和隐藏实现细节，MySQL提供了很多函数，函数可以理解为别人封装好的模板代码。

不同的DBMS函数的差异:DBMS之间函数的差异很大，远大于同一个语言不同版本之间的差异，只有很少的函数是被多个DBMS同时支持的，意味着采用SQL函数的代码的可移植性是很差的，因此在使用函数时需要特别注意。

单行函数:

1.操作数据对象

2.接受参数返回一个结果

3.只对一行进行变换

4.每行返回一个结果

5.可以嵌套

6.参数可以是一列或者是一个值

二、MySQL中函数的分类:

1.聚合函数:聚合函数作用于一组数据，并对一组数据返回一个值

1.1.聚合函数的类型:

(1).AVG(),SUM():只适用于数值类型的字段(或者变量)，其他类型的字段返回结果为0，不考虑NULL值，对AVG来说，遇到NULL时分子分母都跳过，对SUM来说，遇到NULL值当作0来处理

(2).MAX(),MIN():适用于数值类型、字符串类型、日期时间类型的字段(或者变量).

(3).COUNT()：

a.计算指定字段在查询结果中出现的个数

b.计算表中有多少条记录:COUNT(*)|COUNT(1)|COUNT(表中具体的字段名)

c.COUNT的参数为指定字段时不会计算值为NULL的情况，也就是会去除字段结果为NULL的个数,COUNT(*)和COUNT(1)会包括值为NULL的个数

d.如果使用的是MyISAM存储引擎，则COUNT(*)，COUNT(1)，COUNT(表中具体的字段名)三者效率相同，都是O(1),如果使用的是InnoDB存储引擎，则效率在一般情况下COUNT(*)=COUNT(1)>COUNT(表中具体的字段名)

1.3.聚合函数的语法格式:在MySQL中，聚合函数主要由count,sum,min,max,avg这些聚合函数，另外还有一个聚合函数为group_concat()，可以实现行的合并，group_concat()函数首先根据group by指定的列进行分组，并用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。其格式如下:

GROUP_CONCAT([DISTINCT] 字段名 [ORDER BY 排序字段 ASC|DESC] [SEPARATOR '分隔符'])
a.使用distinct可以去除重复值
b.如果需要对结果中的值进行排序，可以使用order by子句
c.separator是一个字符串值，默认为逗号

2.数学函数:

(1).ABS(x):返回x的绝对值

(2).SIGN(x):返回x的符号，正数返回1，负数返回-1,0返回0

(3).SQRT(x):返回x的平方根，当x的值为负数时，返回NULL

(4).CEIL(x),CEILING(x):返回大于等于x的最小整数

(5).FLOOR(x):返回小于等于x的最大整数

(6).GREATEST(expr1,expr2,...,exprn):返回列表中的最大值，列表可以为数字或者字符串

(7).LEAST(expr1,expr2,...,exprn):返回列表中的最小值，列表可以为数字或者字符串

(8).MAX(expression):返回字段expression中的最大值

(9).MIN(expression):返回字段expression中的最小值

(10).MOD(x,y):返回x除以y的余数

(11).PI():返回圆周率

(12).POW(x,y)/POWER(x,y)/EXP(x):返回x的y次幂/返回x的y次幂/返回e的x次幂

(13).LN(x):返回lnx的值

(14).RAND():返回0到1之间的随机数

(15).ROUND(x):返回离x最近的整数(四舍五入)

(16).ROUND(x,y):返回指定y位数的小数(四舍五入),保留到小数点后第y位

(17).TRUNCATE(x,y):返回数值x保留到小数点后y位的值，不会进行四舍五入的操作

(18).SIN(x):返回x的正弦值，其中参数x为弧度值

(19).ASIN(x):返回x的反正弦值(弧度值)，即获取正弦为x的值，如果x的值不在-1到1之间，则返回NULL

(20).COS(x):返回x的余弦值，其中参数x为弧度值

(21).ACOS(x):返回x的反余弦值(弧度值)，即获取余弦为x的值，如果x的值不在-1到1之间，则返回NULL

(22).TAN(x):返回x的正切值，其中参数x为弧度值

(23).ATAN(x):返回x的反正切值(弧度值)，即返回正切值为x的值

(24).ATAN2(m,n):返回两个参数的反正切值(弧度值)

(25).COT(x):返回x的余切值，其中参数x为弧度值

(26).RADIANS(x):将角度转化为弧度，其中参数x为角度值

(27).DEGREES(x):将弧度转化为角度，其中参数x为弧度值

(28).BIN(x)/HEX(x)/OCT(x)/CONV(x,f1,f2):不同进制之间数据的转换

3.字符串函数:

(1).ASCII(s):返回字符串s中第一个字母的ASCII码

(2).CHAR_LENGTH(s):返回字符串s的字符数,汉字或英文都返回的是字符的个数

(3).CHARACTER_LENGTH(s):返回字符串s的字符数

(4).CONCAT(s1,s2,...sn):将字符串s1,s2等多个字符串合并为一个字符串,s1,s2,...,sn可以为字段或者是字符串常量值

(5).CONCAT_WS(x,s1,s2...sn):同CONCAT函数，但是每个字符串之间要加上x，x可以为分隔符

(6).INSERT(str,index,len,replacestr):将字符串str从第index位（index从1开始）开始的len长度的子串替换为replacestr

(7).FIELD(s,s1,s2...):返回第一个字符串s在字符串列表s1,s2...中的位置

(8).LTRIM(s)/RTRIM(s)/TRIM(s):去掉字符串s开始/结束/开始和结束处的空格

(9).LOACTE(substr,str):返回字符串substr在字符串str中首次出现的位置，未找到则返回0

(10).ELT(m,s1,s2,....,sn):返回字符串列表s1,s2,...sn中指定位置m的字符串

(11).MID(s,n,len):从字符串s的n位置截取长度为len的子字符串，同SUBSTRING(s,n,len)

(12).POSITION(s1 IN s):从字符串s中获取s1的开始位置

(13).REPLACE(s,s1,s2):将字符串s2代替字符串s中的字符串s1

(14).REVISE(s):将字符串s的顺序反过来

(15).LEFT(s,n)/RIGHT(s,n):返回字符串s的前/后n个字符

(16).LPAD(str,n,padstr)/RPAD(str,n,padstr):实现左对齐/右对齐，用字符串padstr对str的最左/右边进行填充，直到str的长度为len个字符为止

(17).STRCMP(s1,s2):比较字符串s1和s2，如果s1和s2相等则返回0，如果s1>s2则返回1，否则返回-1

(18).SUBSTR(s,start,length):从字符串s的start位置截取长度为length的子字符串

(19).SUBSTRING(s,start,length):从字符串s的start位置截取长度为length的子字符串

(20).UCASE(s)/ UPPER(s):将字符串全部转换为大写

(21).LCASE(s)/LOWER(s):将字符串全部转换为小写

(22).FIND_IN_SET(s1,s2):返回字符串s1在字符串s2中出现的位置，其中字符串s2是一个以逗号分隔的字符串

(23).REVERSE(s):返回s反转后的字符串

(24).NULLIF(value1,value2):比较两个字符串，如果value1和value2相等，则返回NULL，否则返回value1

4.日期函数:

4.1.获取日期、时间:

(1).CURDATE()/CURRENT_DATE():返回当前日期,只包含年月日

(2).CURRENT_TIME()/CURTIME():返回当前时间，只包含时分秒

(3).NOW()/CURRENT_TIMESTAMP()/SYSDATE()/LOCALTIME()/LOCALTIMESTAMPE():返回当前日期和时间

(4).UTC_DATE()/UTC_TIME():返回UTC日期/时间

4.2.日期与时间戳的转换

(1).UNIX_TIMESTAMP():以UNIX时间戳的形式返回当前时间，返回从1970-01-01 00:00:00到当前的毫秒值

(2).UNIX_TIMESTAMP(DATE_STRING):将指定的日期转换为毫秒的时间戳

(3).FROM_UNIXTIME(BIGINT UNIXTIME[,STRING FORMAT]):将毫秒值时间戳转换为指定格式日期

4.3.获取月份、星期、星期数、天数等函数

(1).YEAR(date)/MONTH(date)/DAY(date):返回具体的日期值

(2).HOUR(time)/MINUTE(time)/SECOND(time):返回具体的时间值

(3).MONTHNAME(d):返回日期当中的月份名称

(4).DAYNAME(d):返回日期d是星期几

(5).WEEKDAY(d):日期d是星期几，0表示星期一，1表示星期二

(6).QUARTER(date):返回日期对应的季度，范围1~4

(7).DATOFWEEK(d):计算日期d是本周的第几天，其中1代表星期日，2代表星期一，其余类推

(8).DAYOFYEAR(d):计算日期d是本年的第几天

(9).WEEK(d)/WEEKOFYEAR(d):计算日期d是本年的第几个星期，范围为0到53

4.4.日期的操作函数

(1).EXTRACT(type FROM d):从日期d中获取指定的值type指定返回的值，可为MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,HOUR_MINUTE,........

4.5.时间和秒钟的转换函数

(1).TIME_TO_SEC(time):将time转化为秒并返回结果值，转化公式为:小时*3600+分钟*60+秒

(2).SEC_TO_TIME(seconds):将seconds转化为包含小时、分钟和秒的时间

4.6.计算日期和时间的函数: 

(1).TIMESTAMPDIFF(type,timestamp1,timestamp2):计算两个时间日期之间相差的年、月、日、时、分、秒，type同下

(2).DATE_SUB/SUBDATE(date,INTERVAL expr type):函数从日期减去指定的时间间隔

(3).ADDDATE/DATE_ADD(datetime,INTERVAL expr type):计算起始日期d加上一个时间段后的日期，EXPR为具体的值，INTERVAL为关键字,type:MICROECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,DAY_MINUTE,DAY_HOUR,YEAR_MONTH

(4).ADDTIME(time1,time2):返回time1加上time2的时间，当time2为一个数字时，代表的是秒，可以为负数

(5).SUBTIME(time1,time2):返回time1减去time2的时间，当time2为一个数字时，代表的是秒，可以为负数

(6).DATEDIFF(d1,d2):计算日期d1到d2之间相隔的天数

(7).TIMEDIFF(time1,time2):计算时间差值，单位为毫秒

(8).FROM_DAYS(N):返回从0000年1月1日起，N天以后的日期

(9).TO_DAYS(date):返回日期date距离0000年1月1日的天数

(10).LAST_DAY(d):返回给定日期那一个月的最后一天

(11).MAKEDATE(year,day-of-year):基于给定参数年份year和所在年中的天数序号返回一个日期

(12).MAKETIME(hour,minute,second):将给定的小时，分钟和秒组合成时间并返回

(13).PERIOD_ADD(time,n):返回time加上n后的时间

4.7.日期的格式化与解析:

格式化:日期转变为字符串

解析:字符串转变为日期

(1).DATE_FORMAT(date,fmt):按照字符串fmt格式化日期date值

(2).TIME_FORMAT(time,fmt):按照字符串fmt格式化时间time值

(3).GET_FORMAT(date_type,format_type):返回日期字符串的显示格式

日期类型date_type	格式化类型format_type	返回格式化的字符串
DATE	USA	%m.%d.%Y
DATE	JIS	%Y-%m-%d
DATE	ISO	%Y-%m-%d
DATE	EUR	%d.%m.%Y
DATE	INTERVAL	%Y%m%d
TIME	USA	%h:%i:%s%p
TIME	JIS	%H:%i:%s
TIME	ISO	%H:%i:%s
TIME	EUR	%H.%i.%s
TIME	INTERVAL	%H%i%s
DATETIME	USA	%Y-%m-%d %H.%i.%s
DATETIME	JIS	%Y-%m-%d %H:%i:%s
DATETIME	ISO	%Y-%m-%d %H:%i:%s
DATETIME	EUR	%Y-%m-%d %H.%i.%s
DATETIME	INTERVAL	%Y%m%d%H%i%s

(4).STR_TO_DATE(str,fmt):按照字符串fmt对str进行解析，解析为一个日期

fmt格式说明:

fmt格式	含义
%Y	4位数字表示年份
%y	2位数字表示年份
%M	月名表示月份(January...)
%m	两位数字表示月份
%b	缩写的月名(Jan.,....)
%c	数字表示月份(1,2,3...)
%D	英文后缀标识月中的天数(1st,2nd,...)
%d	两位数字表示月中的天数(01,02,...)
%e	数字形式表示月中的天数(1,2,3...)
%H	两位数字表示小时，24小时制(01,02,...)
%h和%l	两位数字表示小时，12小时制(01,02,...)
%k	数字形式的小时，24小时制(1,2,3,...)
%i	两位数字表示分钟(00,01,02,...)
%S和%s	两位数字表示秒(01,02,...)
%W	一周中的星期名称(Sunday,...)
%a	一周中的星期缩写(Sun.,Mon.,...)
%w	以数字表示周中的天数(0=Sunday,1=Monday,....)
%j	以3位数字表示年中的天数(001,002,....)
%U	以数字表示年中的第几周(1,2,3...)
%T	24小时制
%r	12小时制
%p	AM或PM
%%	表示%

4.8控制流函数:

(1).IF(expr,v1,v2):如果表达式expr成立，则返回结果v1，否则返回结果v2

(2).IFNULL(v1,v2):如果v1的值不为NULL，则返回v1，否则返回v2

(3).ISNULL(expression):判断表达式是否为NULL

(4).NULLIF(expr1,expr2):比较两个表达式，如果表达式expr1和expr2相等，返回NULL，否则返回expr1

(5).CASE WHEN:

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    WHEN conditionN THEN resultN
    ELSE result
END

CASE表示函数开始，END表示函数结束，如果条件1成立，则返回result1,如果条件2成立，则返回result2,当全不成立返回result，而当其中的一个条件成立后其他就不会执行了.case后面如果带有值或者字段，则类似于switch—case语句，如果没有带任何值而和when相连，则condition1~conditionN均为各种条件表达式，用于判断是否成立

4.9加密与解密函数:

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取，这些函数在保证数据库安全时十分有用。

(1).PASSWORD(str):返回字符串str的加密版本，41位长的字符串，加密结果不可逆，常用于用户的密码加密，mysql8.0弃用

(2).MD5(str):返回字符串str的md5加密后的值，也是一种加密方式，如果参数为NULL，则会返回NULL

(3).SHA(str):从原明文密码str计算并返回加密后的字符串，当参数为NULL时，返回NULL，比MD5更加安全

(4).ENCODE(value,password_seed):返回使用password_seed作为加密密码加密value，mysql8.0弃用

(5).DECODE(value,password_seed):返回使用password_seed作为加密密码解密value，mysql8.0弃用

4.10信息函数:

函数名称	函数作用
VERSION()	返回当前MySQL版本号
CONNECTION_ID()	返回当前MySQL服务器连接数
DATABASE(),SCHEMA()	返回MySQL命令行所在的数据库
USER(),CURRENT_USER()	返回当前连接MySQL的用户名
CHARSET(value)	返回字符串value自变量的字符集
COLLATION(value)	返回字符串的比较规则

4.11其他函数:

(1).FORMAT(value,n):返回对数字value格式化后的结果数据，n表示四舍五入后保留到小数点后n位

(2).CONV(value,from,to):将value的值进行不同进制的转换

(3).INET_ATON(ipvalue):将以点分隔的IP地址转化为一个数字

(4).INET_NTOA(value):将数字形式的IP地址转化为以点分隔的IP地址

(5).BENCHMARK(n,expr):将表达式expr重复执行n次，用于测试MySQL处理表达式expr所需耗费的时间

(6).CONVERT(value USING char_code):将value所使用的字符编码修改为char_code

4.12窗口函数:

MySQL8.0新增窗口函数，窗口函数又称开窗函数。非聚合窗口函数是相对聚合函数来说的，聚合函数是对一组数据计算后返回单个值(分组)，非聚合函数一次只会处理一行数据，窗口聚合函数在行记录上计算某个字段的结果时可以将窗口范围内的数据输入到聚合函数中，并不会改变行数。窗口函数的作用类似于在查询中对数据进行分组，不同的是分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。窗口函数可分为静态窗口函数和动态窗口函数,静态窗口函数的窗口函数大小是固定的，不会因为记录的不同而不同; 动态窗口函数的窗口大小会随着记录的不同而变化。

语法格式:

函数() OVER ([PARTITION BY 字段名 ORDER BY 字段名 ASC/DESC}])
OVER:指定函数窗口的范围
    如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算
    如果OVER关键字后面的括号不为空，则可以使用如下的语法设置窗口
窗口名:为窗口制定一个别名，用来标识窗口
PARTITION BY子句:指定窗口函数按照哪些字段进行分组，分组后窗口函数可以在每个分组中分别执行
ORDER BY子句:指定窗口函数按照哪些字段进行排序，执行排序操作使得窗口函数按照排序后的数据记录的顺序进行编号

(1).序号函数:能够对数据中的序号进行顺序显示，进行排序,没有partition by表示全局排序

a.ROW_NUMBER():1,2,3

b.RANK():并列排序，跳过重复的序号,1,1,3

c.DENSE_RANK():并列排序，不会跳过重复的序号1,1,2

row_number()|rank()|dense_rank() OVER(
    PARTITION BY...
    ORDER BY...
)

(2).分布函数:

a.PERCENT_RANK():等级百分比函数，每行按照(rank-1)/(rows-1)计算,其中rank为RANK()函数产生的序号，rows为当前窗口的记录总行数

b.CUME_DIST():用于查询小于或等于某个值的比例，分组内≤当前rank值的行数/总行数，根据order by的列进行比较和计算

(3).前后函数:

a.LAG(expr,n):返回当前行的前面第n行的expr的值

b.LEAD(expr,n):返回当前行的后面第n行的expr的值,可以带有默认参数，如果没有默认参数的话则对应行为null,expr可以为数据表的字段名

(4).头尾函数:

a.FIRST_VALUE(expr):返回到目前为止本行窗口中第一个expr的值

b.LAST_VALUE(expr):返回到目前为止本行窗口中最后一个expr的值,expr可以为数据表的字段名称

(5).其他函数:

a.NTH_VALUE(expr,n):返回窗口第n个expr的值，expr可以为表达式也可以是数据表的列名

b.NTILE(n):将有序数据分为n个桶，记录等级数

(6).开窗聚合函数:max,min,avg,sum

在窗口中每条记录动态的使用聚合函数可以计算在指定窗口内的各种聚合函数值

语法结构:

window_function (expr) OVER(
    PARTITION BY...
    ORDER BY...
    frame_clause
)

其中window_function是窗口函数的名称，expr是参数，有些窗口函数不需要参数，OVER子句包含三个选项:

分区(PARTITION BY):用于将数据行拆分成多个分区，类似于GROUP BY分组，如果省略了PARTITION BY,所有的数据作为一个组进行计算

排序(ORDER BY):用于指定分区内的排序方式，与ORDER BY子句的作用类似

窗口大小(frame_clause):在当前分区内指定一个计算窗口，与一个当前行相关的数据子集。