MySQL中的内置函数

MySQL中的内置函数

mysql内置函数列表可以从mysql官方文档查询，简单介绍一些可能会用到的函数。
1 数学函数
abs(x)：求绝对值函数
pi()：求π的函数
mod(x,y)：求x除以y后的余数
注意：
正负号不同的两个数使用mod函数所得值得正负问题
mod(负 , 正)=正
mod(正 , 负)=负
sqrt(x)：求平方根的函数
ceil(x)或者ceiling(x)：该函数返回的最小整数值，但不能小于X。
SELECT CEILING(3.46);#4
SELECT CEIL(-6.43);#-6
rand(),rand(N):返回0-1间的浮点数，使用不同的seed N可以获得不同的随机数
round(x, D)：四舍五入保留D位小数，D默认为0， 可以为负数， 如round(19, -1)返回20
truncate(x, D):截断至保留D位小数，D可以为负数， 如trancate(19,-1)返回10
sign(x): 返回x的符号，正负零分别返回1， -1， 0
pow(x,y)或者power(x,y)
exp(x)：e^x
log(x)：自然对数
log10(x)：以10为底的对数
radians(x):角度换弧度
degrees(x):弧度换角度
sin(x)和asin(x):正弦与反正弦
cos(x)和acos(x):余弦与反余弦
tan(x)和atan(x):正切与反正切
cot(x):余切
2. 字符串函数
char_length(str):返回str所包含的字符数，一个多字节字符算一个字符
length(str): 返回字符串的字节长度，如utf8中，一个汉字3字节，数字和字母算一个字节
concat(s1, s1, …): 返回连接参数产生的字符串
concat_ws(x, s1, s2, …): 使用连接符x连接其他参数产生的字符串
INSERT(str,pos,len,newstr):返回str,其起始于pos，长度为len的子串被newstr取代。

1. 若pos不在str范围内，则返回原字符串str
2. 若str中从pos开始的子串不足len,则将从pos开始的剩余字符用newstr取代
3. 计算pos时从1开始，若pos=3,则从第3个字符开始替换
   lower（str)或者lcase(str):改为根据当前字符集映射为小写字母。
   upper(str)或者ucase(str):改为根据当前字符集映射为大写字母。
   left(s,n):返回字符串s最左边n个字符
   right(s,n): 返回字符串最右边n个字符
   lpad(s1, len, s2): 用s2在s1左边填充至长度为len, 若s1的长度大于len,则截断字符串s1至长度len返回
   rpad(s1, len, s2):
   ltrim(s):删除s左侧空格字符
   rtrim(s):删除s右侧空格字符
   TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)或TRIM([remstr FROM] str)：从str中删除remstr, remstr默认为空白字符
   REPEAT(str,count)：返回str重复count次得到的新字符串
   REPLACE(str,from_str,to_str)： 将str中的from_str全部替换成to_str
   SPACE(N):返回长度为N的空白字符串
   STRCMP(str1,str2):若str1和str2相同，返回0， 若str1小于str2, 返回-1， 否则返回1.
   SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len),MID(str,pos,len): 获取特定位置，特定长度的子字符串
   LOCATE(substr,str), LOCATE(substr,str,pos),INSTR(str,substr),POSITION(substr IN str): 返回字符串中特定子串的位置，注意这里INSTR与其他函数的参数位置是相反的
   REVERSE(str)
   ELT(N,str1,str2,str3,…)：返回参数strN, 若N大于str参数个数，则返回NULL
   FIELD(str,str1,str2,str3,…): 返回str在后面的str列表中第一次出现的位置，若找不到str或者str为NULL, 则返回0
   FIND_IN_SET(str,strlist)：strlist是由’,‘分隔的字符串，若str不在strlist或者strlist为空字符串，则返回0；若任意一个参数为NULL则返回ＮＵＬＬ
   MAKE_SET(bits,str1,str2,…): 由bits的作为位图来选取strN参数，选中的参数用’,‘连接后返回
   3 日期和时间函数
   CURDATE(), CURRENT_DATE, CURRENT_DATE():用于获取当前日期，格式为’YYYY-MM-DD’; 若+0则返回YYYYMMDD
   UTC_DATE, UTC_DATE():返回当前世界标准时间
   CURTIME([fsp]), CURRENT_TIME, CURRENT_TIME([fsp]): 用于获取当前时间， 格式为’HH:MM:SS’ 若+0则返回 HHMMSS
   UTC_TIME, UTC_TIME([fsp])
   CURRENT_TIMESTAMP, CURRENT_TIMESTAMP([fsp]), LOCALTIME, LOCALTIME([fsp]), SYSDATE([fsp]), NOW([fsp]): 用于获取当前的时间日期，格式为’YYYY-MM-DD HH:MM:SS’，若+0则返回YYYYMMDDHHMMSS
   UTC_TIMESTAMP, UTC_TIMESTAMP([fsp])
   UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)：返回一个unix时间戳（‘1970-01-01 00:00:00’ UTC至今或者date的秒数），这实际上是从字符串到整数的一个转化过程
   FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)：从时间戳返回’YYYY-MM-DD HH:MM:SS’ 或者YYYYMMDDHHMMSS，加入format后根据所需的format显示。
   MONTH(date)
   MONTHNAME(date)
   DAYNAME(date)
   DAY(date)，DAYOFMONTH(date)：1-31或者0
   DAYOFWEEK(date)：1-7==>星期天-星期六
   DAYOFYEAR(date)： 1-365（366）
   WEEK(date[,mode])：判断是一年的第几周，如果1-1所在周在新的一年多于4天，则将其定为第一周；否则将其定为上一年的最后一周。mode是用来人为定义一周从星期几开始。
   WEEKOFYEAR(date)：类似week(date,3)，从周一开始计算一周。
   QUARTER(date)：返回1-4
   HOUR(time)：返回时间中的小时数，可以大于24
   MINUTE(time)：
   SECOND(time)：
   EXTRACT(unit FROM date)：提取日期时间中的要素
   SELECT EXTRACT(YEAR FROM ‘2009-07-02’); ##2009
   SELECT EXTRACT(YEAR_MONTH FROM ‘2009-07-02 01:02:03’);##200907
   SELECT EXTRACT(DAY_MINUTE FROM ‘2009-07-02 01:02:03’);##20102
   SELECT EXTRACT(MICROSECOND FROM ‘2003-01-02 10:30:00.000123’);##123
   TIME_TO_SEC(time)
   SEC_TO_TIME(seconds)
   TO_DAYS(date): 从第0年开始的天数
   TO_SECNDS(expr)：从第0年开始的秒数
   ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days),DATE_ADD(date,INTERVAL expr unit)
   DATE_SUB(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
   ADDTIME(expr1,expr2)
   SUBTIME(expr1,expr2)
   SELECT ADDTIME(‘2007-12-31 23:59:59.999999’, ‘1 1:1:1.000002’);##‘2008-01-02 01:01:01.000001’
   SELECT ADDTIME(‘01:00:00.999999’, ‘02:00:00.999998’);##‘03:00:01.999997’
   注意：时间日期的加减也可以直接用+/-来进行
   date + INTERVAL expr unit
   date - INTERVAL expr unit
   如：
   SELECT ‘2008-12-31 23:59:59’ + INTERVAL 1 SECOND;##‘2009-01-01 00:00:00’
   SELECT INTERVAL 1 DAY + ‘2008-12-31’;##‘2009-01-01’
   SELECT ‘2005-01-01’ - INTERVAL 1 SECOND;##‘2004-12-31 23:59:59’
   DATE_FORMAT(date,format):
   DATEDIFF(expr1,expr2):返回相差的天数
   TIMEDIFF(expr1,expr2)：返回相隔的时间
   4 条件判断函数
   IF(expr1,expr2,expr3)：如果expr1不为0或者NULL,则返回expr2的值，否则返回expr3的值
   IFNULL(expr1,expr2)：如果expr1不为NULL,返回expr1,否则返回expr2
   NULLIF(expr1,expr2): 如果expr1=expr2则返回NULL, 否则返回expr2
   CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSE result] END
   当compare_value=value时返回result
   CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END
   当condition为TRUE时返回result
   SELECT CASE 1 WHEN 1 THEN ‘one’
   WHEN 2 THEN ‘two’ ELSE ‘more’ END;##‘one’
   SELECT CASE WHEN 1>0 THEN ‘true’ ELSE ‘false’ END;##‘true’
   SELECT CASE BINARY ‘B’
   WHEN ‘a’ THEN 1 WHEN ‘b’ THEN 2 END;##NULL
   5 系统信息函数
   VERSION():返回mysql服务器的版本，是utf8编码的字符串
   CONNECTION_ID()：显示连接号（连接的线程号）
   DATABASE()，SCHEMA()：显示当前使用的数据库
   SESSION_USER(), SYSTEM_USER(), USER(), CURRENT_USER, CURRENT_USER():返回当前的用户名@主机，utf8编码字符串
   CHARSET(str)
   COLLATION(str)
   LAST_INSERT_ID()：自动返回最后一个insert或者update查询， 为auto_increment列设置的第一个发生的值
   6 加密和压缩函数
   PASSWORD(str):这个函数的输出与变量old_password有关。old_password 在mysql5.6中默认为0。 不同取值的效果如下表
   old_password=1时， password(str)的效果与old_password(str)相同，由于其不够安全已经弃用（5.6.5以后）。
   old_password=2时，在生成哈希密码时会随机加盐。
   MD5(str):计算MD5 128位校验和，返回32位16进制数构成的字符串，当str为NULL时返回NULL。可以用作哈希密码
   SHA1(str), SHA(str)：计算160位校验和，返回40位16进制数构成的字符串，当str为NULL时返回NULL。
   SHA2(str, hash_length)：计算SHA-2系列的哈希方法(SHA-224, SHA-256, SHA-384, and SHA-512). 第一个参数为待校验字符串，第二个参数为结果的位数（224， 256， 384， 512）
   ENCRYPT(str[,salt]): 用unix crypt()来加密str. salt至少要有两位字符，否则会返回NULL。若未指定salt参数，则会随机添加salt。
   ECODE(crypt_str,pass_str):解密crypt_str, pass_str用作密码
   ENCODE(str,pass_str)：用pass_str作为密码加密str
   DES_ENCRYPT(str[,{key_num|key_str}])：用Triple-DES算法编码str， 这个函数只有在mysql配置成支持ssl时才可用。
   DES_DECRYPT(crypt_str[,key_str])
   AES_ENCRYPT(str,key_str[,init_vector])
   AES_DECRYPT(crypt_str,key_str[,init_vector])
   COMPRESS(string_to_compress)：返回二进制码
   UNCOMPRESS(string_to_uncompress)
   7 聚合函数
   若在没使用group by时使用聚合函数，相当于把所有的行都归于一组来进行处理。除非特殊说明，一般聚合函数会忽略掉NULL.
   AVG([DISTINCT] expr): 返回expr的平均值，distinct选项用于忽略重复值
   COUNT([DISTINCT] expr)：返回select中expr的非0值个数，返回值为bigint类型
   group_concat:连接组内的非空值，若无非空值，则返回NULL
   GROUP_CONCAT([DISTINCT] expr [,expr …]
   [ORDER BY {unsigned_integer | col_name | expr}
   [ASC | DESC] [,col_name …]]
   [SEPARATOR str_val])
   MAX([DISTINCT] expr)
   MIN([DISTINCT] expr)
   SUM([DISTINCT] expr)
   VAR_POP(expr)
   VARIANCE(expr)：同VAR_POP(expr)，但是这是标准sql的一个扩展函数
   VAR_SAMP(expr)
   STD(expr): 这是标准sql的一个扩展函数
   STDDEV(expr)：这个函数是为了跟oracle兼容而设置的
   STDDEV_POP(expr)：这个是sql标准函数
   STDDEV_SAMP(expr)：样本标准差
   8 格式或类型转化函数
   FORMAT(X,D[,locale])：将数字X转化成’#,###,###.##‘格式，D为保留的小数位数
   CONV(N,from_base,to_base)：改变数字N的进制，返回值为该进制下的数字构成的字符串
   INET_ATON(expr)：ip字符串转数字
   INET_NTOA(expr)：数字转ip字符串
   CAST(expr AS type)：转换数据类型
   CONVERT(expr,type), CONVERT(expr USING transcoding_name)： type可以为BINARY[(N)]，CHAR[(N)]，DATE，DATETIME， DECIMAL[(M[,D])]，DECIMAL[(M[,D])]，TIME，UNSIGNED [INTEGER]等等。transcoding_name如utf8等等
   常用的内置函数如下：
   #数学函数
   select PI()* 2 2; #pi
   select CEIL(-12.3); #向上取整
   select FLOOR(12.3); #向下取整
   select ROUND(8.45,-1); #四舍五入
   select MOD(5,2); #取模
   select RAND(); #随机数 [0,1)
   select POW(2,3); #幂运算
   #随机从emp中获取两条记录。
   select * from emp order by RAND() limit 2;
   #字符函数
   select LENGTH(‘this is a dog’); #获取长度
   select length(ename) from emp;
   select LOWER(‘THIS’);
   select UPPER(‘this’);
   select SUBSTR(‘this is zs’,1,6); #下标从1开始
   #select REPLACE(str,from_str,to_str);
   select trim(’ this is ‘); #去两端空格
   select LPAD(‘aa’,10,’’); #左填充
   select RPAD(‘aa’,10,’’); #右填充
   #日期函数
   select NOW(); #当前时间
   select SYSDATE(); #获取系统时间
   select CURRENT_DATE();
   select CURDATE();
   select CURRENT_TIME();
   select CURTIME();
   select YEAR(‘1998-09-09’);
   select YEAR(NOW());
   select MONTH(date);
   select DAY(date);
   #获取当前月最后一天
   select LAST_DAY(‘2018-02-02’);
   #日期计算
   select DATE_ADD(NOW(),interval 2 MONTH);
   # 聚合函数
   #min() max() avg() count() sum()
   select max(sal) from emp;
   select min(sal) from emp;
   select avg(sal) from emp;
   select count() from emp; #记录数
   select count(1) from emp; #记录数
   select count(comm) from emp; #字段非空总数
   select sum(sal) from emp;
   #分组函数
   #分组 group by 分组条件 having:分组之后进行检索
   select deptno,avg(sal) from emp group by deptno;
   #查询平均工资大于2000的部门的编号和平均工资。
   # 1.where在group by之后
   # 2.where中不能使用聚合函数
   select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
   #加密函数
   select MD5(‘root’);
   select SHA(‘root’);
   select password(‘root’);