MySQL中内置的函数不仅可以在SELECT 中使用, 还可以在INSERT , UPDATE 和 DELETE 等语句中使用.
数学类函数:
CEIL(X), CEILIN(X)-----------进一取整
FLOOR(X)-------------舍掉小数部分
ROUND(x, y)----------四舍五入(保留y位小数)
MOD(x,y)---------x除以y以后的余数
ABS(x)-----------取x的绝对值
POWER(x, y), POW(x, y)---------幂运算(x的y次幂)
PI()------------圆周率
RAND()或者RAND(X)------------0~1之间的随机数, x相同时返回的结果相同.
SIGN(X)-------------得到数字符号
EXP(X)-------------计算e的x次方;
PI()----------返回圆周率, 默认7位
TRUNCATE(X, Y)---------返回x,保留小数点后y位
SQRT(x)----------返回非负数x的二次方根
LOG(x)----------返回x的基数为2的对数
LOG10(x)-----------返回x的基数为10的对数
RADIANS(x)----------将角度转换为弧度
DEGREES(x)-----------将弧度转化为角度
SIN(x)---------------返回x的正弦, 其中x在弧度中被给定
ASIN(x)----------------返回x的反正弦, 若x不在-1到1之间的值, 则返回NULL
(COS, ACOS, TAN, ATAN, COT等类似)
2. 字符串函数
CHAR_LENGTH(s)-----------返回字符串s的字符数
LENGTH(s)------------------返回s的长度, 单位为字节, 一个多字节字符算作多个字节
CONCAT(s1, s2, .....)----------------返回结果为连接参数(s1, s2, ....)产生的字符串, 若有一个参数为NULL,
则返回NULL.如果所有参数均为非二进制, 则返回结果为非二进制,
若有一个参数为二进制, 则返回结果为二进制字符串.一个数字参数转化为
与之相等的二进制字符串格式, 若要避免这种情况, 可以使用显式类型CAST,
如: SELECT CONCAT(CAST(int_col AS CHAR), char_col);
CONCAT_WS(x, s1, s2, .....)------------同CONCAT()函数, 但是每个字符串要直接加上x
INSERT(s1, x, len, s2)---------------将s2替换s1的x位置开始, 长度为len的字符串
UPPER(s), UCASE(s)------------将s转换为大写
LOWER(s), LCASE(s)-------------将s转换为小写
LEFT(s, n)------------返回字符串s从左边开始的n个字符
RIGHT(s, n)----------返回字符串s从右边开始的n个字符
LPAD(s1, len, s2)------------返回字符串s1, 其左边用s2填补到len字符长度,
假如s1的长度大于len, 则返回s1被缩短至len的长度
RPAD(s1, len, s2)-------------返回字符串s1, 其右边被字符串s2填补至len字符长度,
假如s1的长度大于len, 则返回值被缩短到与len字符相等的长度
LTRIM(s)----------返回字符串s, 开头空格字符被删除
RTRIM(s)----------返回字符串s, 结尾空格字符被删除
TRIM(s)----------去掉s开头和结尾的空格
TRIM(s1 FROM s)----------去掉s中开头和结尾处的字符串s1
REPEAT(s, n)-----------将字符串s重复n次
SPACE(n)------------返回个空格
REPLACE(s, s1, s2)---------用s2代替s中的s1
STRCMP(s1, s2)----比较s1和s2
SUBSTRING(s, n, len)---------获取字符串s中的第n个位置开始长度为len的字符串
MID(s, n, len)--------------同SUBSTRING(s, n, len)
LOCATE(s1, s), POSITION(s1 IN s)---------------从字符串s中获取s1的开始位置
INSTR(s, s1)-------------查找字符串s1在s中的位置, 返回首次出现的位置的索引值
REVERSE(s)----------将字符串s反序
ELT(n, s1, s2, .....)----------返回第n个字符串
EXPORT_SET(bits, on, off[, separator [, number_of_bits ] ] )---------
返回一个字符串, 生产规则如下: 针对bits的二进制格式, 如果其位为1, 则返回一个on值,
为0返回off值, 每个字符用separator进行分隔, 默认为" , " . number_of_bits
指定可用的位数, 默认为64位.
例如: 生成数字182的二进制(10110110)替换格式, 以" @ "作为分隔符, 设置有效位为6位,
其语句如下:
SELECT EXPORT_SET(182, 'Y', 'N', '@', 6) 其返回结果为: N@Y@Y@N@Y@Y
FIFLD(s, s1, s2, ............)-----------返回第一个与字符串s匹配的字符串的位置
FIND_IN_SET(s1, s2)----------返回在字符串s2中与s1相匹配的字符串的位置
MAKE_SET(x, s1, s2, ........)-------------按x的二进制数从s1, s2, .............sn中选取字符串
3. 日期和时间函数
CURDATE(), CURRENT_DATE()----------返回当前日期
CURTIME(), CURRENT_DATE()-----------返回当前时间
NOW(), CURRENT_TIMESTAMP(), LOCALTIME(), SYSDATE(), LOCALTIESTAMP()--------返回当前日期和时间
UNIX_TIMESTAMP()----------以UNIX时间戳的形式返回当前时间,
UNIX_TIMESTAMP(d)---------将时间d以UNIX时间戳的形式返回
FROM_UNIXTIME(d)----------把UNIX时间戳转化为普通时间格式返回
UTC_DATE()-----------返回UTC(国际协调时间)日期
UTC_TIME()------------返回UTC时间
MONTH(d)------------返回日期d中的月份值, 范围为1-12
MONTHNAME(d)--------返回日期d中的月份名称, 如: January, February.....
DAYNAME(d)-----------返回日期d是星期几, 如Monday, Tuesday.....
DAYOFWEEK(d)-----------返回日期d是星期几, 1表示星期日, 2表示星期一......
WEEKDAY(d)-------------返回日期d是星期几, 0表示星期一, 1表示星期二......
WEEK(d)----------计算日期d是本年的第几个星期, 范围是0~53
WEEKOFYEAR(d)-----------计算日期 d 是本年的第几个星期，范围是 1~53
DAYOFYEAR(d)-----------计算日期 d 是本年的第几天
DAYOFMONTH(d)-----------计算日期 d 是本月的第几天
YEAR(d)--------返回年份
QUARTER(d)----------返回日期d是第几季节，返回 1 到 4
HOUR(t)-----------返回 t 中的小时值
MINUTE(t)----------返回 t 中的分钟值
SECOND(t)------------返回 t 中的秒钟值
EXTRACT(type FROM d)------------
从日期 d 中获取指定的值，type 指定返回的值。
type可取值为： MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK,
MONTH, QUARTER, YEAR, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.....
TIME_TO_SEC(t)-----------将时间 t 转换为秒
SEC_TO_TIME(s)--------将以秒为单位的时间 s 转换为时分秒的格式
TO_DAYS(d)---------计算日期 d 距离 0000 年 1 月 1 日的天数
FROM_DAYS(n)--------计算从 0000 年 1 月 1 日开始 n 天后的日期
DATEDIFF(d1,d2)-------计算日期 d1->d2 之间相隔的天数
ADDDATE(d,n)--------计算起始日期 d 加上 n 天的日期
ADDDATE(d, INTERVAL expr type)-------计算起始日期d加上一段时间(expr)之后的日期, type可以为: day, month.....
例如:
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
->2017-06-25DATE_ADD(d，INTERVAL expr type)----------计算起始日期 d 加上一个时间段后的日期
例如:
SELECT ADDDATE('2011-11-11 11:11:11',1)
-> 2011-11-12 11:11:11 (默认是天)
SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE)
-> 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似)SUBDATE(d,n)---------日期 d 减去 n 天后的日期
SELECT SUBDATE('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (默认是天)SUBTIME(t,n)-------时间 t 减去 n 秒的时间
DATE_FORMAT(d,f)-----按表达式 f的要求显示日期 d
例如:
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')
-> 2011-11-11 11:11:11 AMTIME_FORMAT(t,f)----------按表达式 f 的要求显示时间 t
例如:
SELECT TIME_FORMAT('11:11:11','%r')
11:11:11 AMGET_FORMAT(type, s)---------根据字符串s获取type类型数据的显示格式
4. 条件判断函数
IF(expr, v1, v2)----------expr成立, 执行v1, 否则v2.
IFNULL(v1, v2)---------如果v1不为空, 则显示v1的值, 否则显示v2的值
CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE result
ENDCASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1,
如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。
例如:
SELECT CASE
WHEN 1 > 0
THEN '1 > 0'
WHEN 2 > 0
THEN '2 > 0'
ELSE '3 > 0'
END
->1 > 0
5. 系统信息函数
VERSION()--------返回数据库的版本号
CONNECTION_ID()--------返回服务器的连接数
DATABASE(), SCHEMA()----------返回当前数据库名
USER(), SYSTEM_USER(), SESSION_USER()--------返回当前用户
CURRENT_USER(), CURRENT_USER---------返回当前用户
CHARSET(s)-------获取字符串s的字符集
COLLATION(s)---------获取字符串s的字符的排列方式
LAST_INSERT_ID()--------返回最近生成的 AUTO_INCREMENT 值
6. 加密函数
PASSWORD(str)------对字符串str进行加密, 经此加密后的数据是不可逆的. 其经常用于对用户注册的密码进行加密处理
MD5(str)----------对str进行加密, 经常用于对普通数据进行加密
ENCODE(str, pswd_str)---------使用字符串pwsd_str来加密字符串str, 加密的结果是一个二进制数,
必须使用BLOB类型的字段来保护它
DECODE(crypt_str, pswd_str)-------使用字符串pswd_str来为crypt_str解密, crypt_str 是通过
ENCODE(str, pswd_str)加密后的二进制数据字符串pswd_str应该与加密时的相同.
7. 其他函数
FORMAT(x, n)-------函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。
ASCII(s)------返回字符串 s 的第一个字符的 ASCII 码。
BIN(x)-------返回 x 的二进制编码
HEX(x)------返回x的十六进制编码
OTC(x)------返回x的八进制编码
CONV(x,f1,f2)----------将x从f1进制数变成f2进制数
INET_ATON(IP)----------将IP地址转化为数字表示
INET_NTOA(N)-----------将数字N转化为IP的形式
GET_LOCT(name, time)-------定义一个名为name, 持续时长为time秒的锁, 锁定成功返回1, 如果尝试超时返回0,
遇到错误返回NULL
RELEASE_LOCK(name)-------解除名为name的锁, 成功1, 超时0, 失败NULL
IS_FREE_LOCK(name)--------判断是否使用name的锁, 使用0, 否则返回1
BENCHMARK(count, expr)-------将表达式expr重复执行count次, 然后返回执行的时间, 该表达式可以用来测试
MySQL处理表达式的速度
CONVERT(s USING cs)---------函数将字符串 s 的字符集变成 cs
例如:
SELECT CHARSET('ABC')
->utf-8
SELECT CHARSET(CONVERT('ABC' USING gbk))
->gbkCAST(x AS type)------------转换数据类型
例如:
字符串日期转换为日期：
SELECT CAST("2017-08-29" AS DATE);
-> 2017-08-29
