【MySQL】函数

1. 函数简介

• SQL 语言中，包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的。
• MySQL提供的内置函数实现的功能角度可以分为 数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。
• 内置函数再分为两类单行函数、聚合函数（或分组函数）

2.单行函数

• 操作数据对象接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套使用

2.1 数值函数

2.1.1 基本函数

• ABS(x)：返回x的绝对值                  
• SIGN(x)：返回X的符号。正数返回1，负数返回-1，0返回0          
• PI()：返回圆周率的值                                 
• CEIL(x)，CEILING(x)：返回大于或等于某个值的最小整数                
• FLOOR(x)：返回小于或等于某个值的最大整数            
• LEAST(e1,e2,e3…)：返回列表中的最小值                            
• GREATEST(e1,e2,e3…) ：返回列表中的最大值                             
• MOD(x,y)：返回X除以Y后的余数                                   
• RAND()：返回0~1的随机值                                        
• RAND(x) ：返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
• ROUND(x)：返回一个对x的值进行四舍五入后，最接近于X的整数      
• ROUND(x,y)：返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
• TRUNCATE(x,y)：返回数字x截断为y位小数的结果                     
• SQRT(x)：返回x的平方根。当X的值为负数时，返回NULL                

2.1.2 角度与弧度互换函数

• RADIANS(x)：将角度转化为弧度，其中，参数x为角度值
• DEGREES(x)：将弧度转化为角度，其中，参数x为弧度值

2.1.3 三角函数

• SIN(x)：返回x的正弦值，其中，参数x为弧度值                  
• ASIN(x)：返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
• COS(x)：返回x的余弦值，其中，参数x为弧度值                    
• ACOS(x)：返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
• TAN(x)：返回x的正切值，其中，参数x为弧度值                
• ATAN(x)：返回x的反正切值，即返回正切值为x的值                  
• ATAN2(m,n)：返回两个参数的反正切值                               
• COT(x)：返回x的余切值，其中，X为弧度值                     

 

2.1.4 指数与对数

• POW(x,y)，POWER(X,Y)：返回x的y次方                                    
• EXP(X)：返回e的X次方，其中e是一个常数，2.718281828459045    
• LN(X)，LOG(X)：返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL 
• LOG10(X)：返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
• LOG2(X)：返回以2为底的X的对数，当X <= 0 时，返回NULL      

2.1.5 进制转换

• BIN(x)：返回x的二进制编码  
• HEX(x)：返回x的十六进制编码  
• OCT(x)：返回x的八进制编码    
• CONV(x,f1,f2)：返回f1进制数变成f2进制数

2.2 字符串函数

• ASCII(S)：返回字符串S中的第一个字符的ASCII码值       
• CHAR_LENGTH(s)：返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同      
• LENGTH(s)：返回字符串s的字节数，和字符集有关                      
• CONCAT(s1,s2,......,sn)：连接s1,s2,......,sn为一个字符串                      
• CONCAT_WS(x,s1,s2,......,sn)： 同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x   
• INSERT(str, idx, len,replacestr)：将字符串str从第idx位置开始，len个字符长的子串替换为字符replacestr
• REPLACE(str, a, b)：用字符串b替换字符串str中所有出现的字符串a             
• UPPER(s) 或 UCASE(s)：将字符串s的所有字母转成大写字母                      
• LOWER(s) 或LCASE(s)：将字符串s的所有字母转成小写字母                
• LEFT(str,n)：返回字符串str最左边的n个字符                    
• RIGHT(str,n)：返回字符串str最右边的n个字符                         
• LPAD(str, len, pad)：用字符串pad对str最左边进行填充，直到str的长度为len个字符  
• RPAD(str ,len, pad)：用字符串pad对str最右边进行填充，直到str的长度为len个字符   
• LTRIM(s)：去掉字符串s左侧的空格                                
• RTRIM(s)：去掉字符串s右侧的空格                             
• TRIM(s)：去掉字符串s开始与结尾的空格                  
• TRIM(s1 FROM s)：去掉字符串s开始与结尾的s1                         
• TRIM(LEADING s1 FROM s)：去掉字符串s开始处的s1                               
• TRIM(TRAILING s1 FROM s)： 去掉字符串s结尾处的s1                                
• REPEAT(str, n) ：返回str重复n次的结果                                 
• SPACE(n)：返回n个空格                                         
• STRCMP(s1,s2)：比较字符串s1,s2的ASCII码值的大小                   
• SUBSTR(s,index,len)：返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、
• MID(s,n,len)相同
• LOCATE(substr,str)：返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
• ELT(m,s1,s2,…,sn)：返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
• FIELD(s,s1,s2,…,sn)：返回字符串s在字符串列表中第一次出现的位置                   
• FIND_IN_SET(s1,s2)：返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
• REVERSE(s)：返回s反转后的字符串                              
• NULLIF(value1,value2)：比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

2.3日期和时间函数

2.3.1 获取日期、时间

• CURDATE() ，CURRENT_DATE()：返回当前日期，只包含年、月、日
• CURTIME() ， CURRENT_TIME() ：返回当前时间，只包含时、分、秒
• NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP()：返回当前系统日期和时间        
• UTC_DATE()：返回UTC（世界标准时间）日期   
• UTC_TIME()：返回UTC（世界标准时间）时间   

 

2.3.2 日期与时间戳的转换

• UNIX_TIMESTAMP()：以UNIX时间戳的形式返回当前时间。
• UNIX_TIMESTAMP(date)：将时间date以UNIX时间戳的形式返回。                
• FROM_UNIXTIME(timestamp)：将UNIX时间戳的时间转换为普通格式的时间              

 

2.3.3 获取月份、星期、星期数、天数等函数

• YEAR(date) / MONTH(date) / DAY(date)：返回具体的日期值                               
• HOUR(time) / MINUTE(time) /SECOND(time)：返回具体的时间值                               
• MONTHNAME(date)：返回月份：January，...                         
• DAYNAME(date)：返回星期几：MONDAY，TUESDAY.....SUNDAY         
• WEEKDAY(date)：返回周几，注意，周1是0，周2是1
• QUARTER(date) ：返回日期对应的季度，范围为1～4                 
• WEEK(date) ， WEEKOFYEAR(date)：返回一年中的第几周                             
• DAYOFYEAR(date)：返回日期是一年中的第几天                       
• DAYOFMONTH(date) ：返回日期位于所在月份的第几天                   
• DAYOFWEEK(date)：返回周几，注意：周日是1，周一是2

2.3.4 计算日期和时间的函数

• ADDTIME(time1,time2)：返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
• SUBTIME(time1,time2)：返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
• DATEDIFF(date1,date2)：返回date1 - date2的日期间隔天数                             
• TIMEDIFF(time1,time2)：返回time1 - time2的时间间隔                                 
• FROM_DAYS(N)：返回从0000年1月1日起，N天以后的日期                         
• TO_DAYS(date)：返回日期date距离0000年1月1日的天数                          
• LAST_DAY(date)：返回date所在月份的最后一天的日期                            
• MAKEDATE(year,n)：针对给定年份与所在年份中的天数返回一个日期                  
• MAKETIME(hour,minute,second)：将给定的小时、分钟和秒组合成时间并返回                      
• PERIOD_ADD(time,n)：将给定的小时、分钟和秒组合成时间并返回                      

 

2.3.5 日期的格式化与解析

• DATE_FORMAT(date,fmt)：按照字符串fmt格式化日期date值       
• TIME_FORMAT(time,fmt)：按照字符串fmt格式化时间time值             
• GET_FORMAT(date_type,format_type)：返回日期字符串的显示格式                  
• STR_TO_DATE(str, fmt)：按照字符串fmt对str进行解析，解析为一个日期

 

3.聚合函数

• 聚合函数作用于一组数据，并对一组数据返回一个值。
• 不能嵌套使用

3.1 常用于数据类型函数

• AVG 和 SUM 函数：求平均求和
• MIN和MAX函数：求最大最小
• COUNT函数：返回表中记录总数