mysql函数知识点

1、数学函数

        1）、绝对值函数：ABS(x);

        2）、求圆周率的函数：PI();

        3）、平方根函数：SQRT(x),返回非负数x的二次方根;

        4）、求余函数：MOD(x,y);

        5）、获取整数的函数：

                CEIL(x)和CEILING(x)，指返回不小于x的最小整数值，返回值转换为BIGINT类型。

                FLOOR(x)，指返回不大于x的最大整数值，返回值转换为BIGINT类型。

        6）、获取随机函数：

                RAND()和RAND(x),返回一个随机浮点数v,范围在0到1之间。

                若已指定一个整数参数x，则它被用作种子值，用来产生重复序列。

        7）、四舍五入函数：

                ROUND(x)，指返回最接近于x的整数，对x值进行四舍五入。

                ROUND(x,y)，指返回最接近于x的数，其值保留小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

                TRUNCATE(x,y)，指返回被舍去小数点后y位的数字x。

                若y为0，则结果不带有小数点或不带有小数部分。

                若y为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。

        8）、符号函数：

                SIGN(x)，指返回参数的符号，x的值为负、零或正时返回结果依次为-1、0或1。

        9）、幂运算函数：

                POW(x,y)和POWER(x,y)函数返回x的y次乘方的结果值。

                EXP(x)函数返回e的x乘方后的值。

        10）、对数运算函数：

                LOG(x)返回x的自然对数，x相对于基数e的对数。对数定义域不能为负数否则返回NULL。

                LOG10(x)返回x的基数为10的对数。

        11）、角度与弧度相互转换的函数：

                RADIANS(x)将参数x由角度转换为弧度。

                DEGREES(x)将参数x有弧度转换为角度。

        12）、正弦函数：

               SIN(x)返回x的正弦，其中x为弧度值。

        13）、反正弦函数：

                ASIN(x)返回x的反正弦，即正弦为x的值。若x不在-1到1的范围之内，则返回null。

        14）、余弦函数：

                COS(x)返回x的余弦，其中x为弧度值。

        15）、反余弦函数：

                ACOS(x)返回x的反余弦，即余弦为x的值。若x不在-1到1的范围之内，则返回null。

        16）、正切函数：

                TAN(x)返回x的正切，其中x为弧度值。

        17）、反正切函数：

                ATAN(x)返回x的反正切，即正切为x的值。

        18）、余切函数：

                COT(x)返回x的余切，其中x为弧度值。

2、字符串函数

        1）、计算字符串字符数的函数：

                CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算一个单字符。

        2）、计算字符串长度的函数：

                LENGTH(str)返回值为字符串str的字节长度。一个汉字两个字节。

        3）、合并字符串函数：

                CONCAT(s1,s2,.....)返回结果为连接参数产生的字符串，或许有一个或多个参数。

                如有任何一个参数为null，则返回值为null。

                如果所有参数均为非二进制字符串，则结果为非二进制字符串。

                如果参数中有一个二进制字符串，则结果为二进制字符串。

                CONCAT_WS(x,s1,s2,.....)第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。

                分隔符可以是一个字符串，也可以是其他参数。

                如果分隔符为null，则结果为null。

                函数会忽略任何分隔符参数后的null值。

        4）、替换字符串的函数：

                INSERT(s1,x,len,s2)返回字符串s1，其子字符串起始于x位置和被字符串s2取代len字符。

如果x超过字符串长度，则返回原始字符串。

假如len的长度大于其他字符串长度，则从位置x开始替换，若任何一个参数为null,则返回值为null。

        5）、字母大小写转换函数：

                LOWER(str)或者LCASE(str),转换成小写。

                UPPER(str)或者UCASE(str),转换成大写。

        6）、获取指定长度的字符串的函数：

                LEFT(s,n)返回字符串s开始的最左边n个字符。

                RIGHT(s,n)返回字符串s最右边的n个字符。

        7）、填充字符串的函数：

                LPAD(s1,len,s2)返回字符串是s1,其左边由字符串s2填补到llen字符长度。假如s1的字符长度大于len，则返回值被缩短至len字符。

                RPAD(s1,len,s2)返回字符串是s1,其右边由字符串s2填补到llen字符长度。假如s1的字符长度大于len，则返回值被缩短至len字符。

        8）、删除空格的函数：

                LTRIM(s)返回字符串s，删除左侧空格

                RTRIM(s)返回字符串s，删除右侧空格

                TRIM(s)返回字符串s，删除两侧空格

        9）、重复生成字符串的函数：

                REPEAT(s,n)返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。

                若n<=0,则返回一个空字符串。若s或n为NULL,则返回NULL。

        10)、空格函数：

                SPACE(n)返回一个由n个空格组成的字符串。

        11）、替换函数：

                REPLACE(s,s1,s2)使用字符串s2替换字符串s中所有的字符串s1。

        12）、比较字符串大小的函数：

                STRCMP(s1,s2)若所有的字符串均相同，则返回0；

                若根据当前分类次序，第一个参数小于第二个参数，则返回-1，其他情况返回1。

        13）、获取子串的函数：

                SUBSTRING(s,n,len)和MID(s,n,len)带有len参数的格式，从字符串s返回一个长度同len相同的子字符串，起始于位置n。也可能对n使用一个负值，假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串开头位置。

        14）、匹配子串开始位置的函数：

                LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str1,str)这三个函数作用相同，返回子字符串str1在字符串str中的开始位置。

        15）、字符串逆序的函数：

                REVERSE(s)将z字符串s反转，返回字符串的顺序和s字符串顺序相反。

        16）、返回指定位置的字符串函数：

                ELT(N,字符串1,字符串2,字符串3,.......,字符串N)若N=1,则返回字符串1；

                若N=2，则返回字符串2，以此类推。若N小于1或者大于参数的数目，则返回值为NULL。

        17）、返回指定字符串位置的函数：

                FIELD(s,s1,s2,...)返回字符串s在列表s1,s2,....中第一次出现的位置，在找不到s的情况下，返回值为0。若s为null,则返回0；

        18）、返回子串位置的函数：

                FIND_IN_SET(s1,s2)返回子串s1在字符串列表s2中出现的位置，字符串列表是一个由多个“,”分开的字符串组成的列表。如果s1不在s2或s2为空，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。这个函数的第一个参数包含一个“,”时将无法正常运行。

        19）、选取字符串的函数：

                MAKE_SET(x,s1,s2,......)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特01以此类推。s1,s2.....中的NULL值不会b被添加到结果中。

3、日期和时间函数

        1）、获取当前日期的函数：

                CURDATE()和CURRENT_DATE()函数作用相同，将当前日期按照'YYYY-MM-DD'或'YYYYMMDD'格式的值返回。

        2）、获取当前时间的函数：

                CURTIME()和CURRENT_TIME()函数作用相同，将当前日期按照'HH:MM:SS'或'HHMMSS'格式的值返回。

        3）、获取当前日期和时间的函数：

                CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE()函数作用相同，均返回当前日期和时间值，格式为'YYYY-MM-DD HH:MM:SS'或'YYYYMMDDHHMMSS'。

        4）、UNIX时间戳函数：

                UNIX_TIMESTAMP(date)，返回一个UNIX时间戳。

                FROM_UNIXTIME(date)把UNIX时间戳转换为普通格式时间。

        5）、获取UTC日期的函数：

                UTC_DATE()将当前UTC(世界标准时间)日期按照'YYYY-MM-DD'或'YYYYMMDD'格式的值返回。

        6）、获取UTC时间的函数：

                UTC_TIME()将当前UTC(世界标准时间)时间按照'HH:MM:SS'或'HHMMSS'格式的值返回。

        7）、获取月份函数：

                MONTH(date)函数返回date对应的月份，范围值从1-12。

                MONTHNAME(date)函数返回date对应的月份的英文全称。

        8）、获取星期的函数：

                DAYNAME(date)函数返回date对应的工作日的英文名称，例如：Sunday,Monday等。

                DAYOFWEEK(date)函数返回date对应的一周中的索引(位置),1表示周日,2表示周一,.....,7表示周六

                WEEKDAY(date)函数返回date对应的工作日的索引(位置),0表示周一,1表示周二,.....,6表示周日

        9）、获取星期数的函数：

                WEEK(date)计算日期date是一年中的第几周。

                WEEKOFYEAR(date)计算某天位于一年中的第几周，范围从1~53，类似于WEEK(date,3)。

        10）、获取天数的函数：

                DAYOFYEAR(date)函数返回date是一年中的第几天，范围是从1~366。

                DAYOFMONTH(date)函数返回date是一月中的第几天，范围是从1~31。

        11）、获取年份的函数：

                YEAR(date)返回date对应的年份，范围是1970~2069。

        12）、获取季度的函数：

                QUARTER(date)返回date对应的一年中的季度值，范围是从1~4。

        13）、获取小时的函数：

                HOUR(time)返回time对应的时钟数，范围是从0~24。

        14）、获取分钟的函数：

                MINUTE(time)返回time对应的分钟数，范围是从0~59。

        15）、获取秒数的函数：

                SECOND(time)返回time对应的秒数，范围是从0~59。

        16）、获取日期指定值的函数：

                EXTRACT(type FROM date)。

        17）、时间和秒数转换的函数：

                TIME_TO_SEC(time)返回已转换为秒数的time参数。转换公式为：小时*3600+分钟*60+秒

                SEC_TO_TIME(seconds)返回已转换为小时、分钟、秒数的seconds参数。

                其格式为HH:MM:SS或HHMMSS。

        18）、计算日期和时间的函数：

                DATE_ADD(date,expr)、ADDDATE(date,expr)日期加函数

                DATE_SUB(date,expr)、SUBDATE(date,expr)日期减函数

                ADDTIME(date,expr)时间加函数

                SUBTIME(date,expr)时间减函数

                DATE_DIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数。

                date1和date2为日期或date-and-time表达式。计算中只用到这些值的日期部分。

        19）、将日期和时间格式化的函数：

                DATE_FORMAT(date,format)日期格式化。

                TIME_FORMAT(time,format)时间格式化。

                GET_FORMAT(val_type,format_type)返回日期时间字符串的显示格式。

4、条件判断函数

        1）、IF(expr,v1,v2)：

                如果表达式expr是true,则IF()返回v1,否则返回v2。

                IF()的返回值为数字值或字符串值。具体情况视所在语境而定。

        2）、IFNULL(v1,v2)：

                假如v1不为NULL，则IFNULL()的返回值为v1，否则返回值为v2。

                IFNULL()的返回值为数字值或字符串值。具体情况视所在语境而定。

        3）、CASE函数：

                表示如果expr值等于某个值，则返回对应位置THEN后面的结果。

                如果与所有值都不相等，则返回ELSE后面的m

CASE expr WHEN v1 THEN r1

[WHEN v2 THEN r2]

[ELSE rn]

END

5、系统信息函数

        1）、获取mysql版本号的函数：

                VERSION();

        2）、获取连接数的函数：

                CONNECTION_ID();

        3）、查看那些线程在运行：

                SHOW PROCESSLIST,只列出前100条；

                SHOW FULL PROCESSLIST列出全部。

        4）、获取数据库名：

                DATABASE(),SCHEMA();

        5）、获取用户名的函数：

                USER()、CURRENT_USER、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()

        6）、获取字符串的字符集：

                CHARSET(str)

        7）、获取字符串的字符排列方式：

                COLLATION(str)

        8）、获取最后一个自动生成的ID值得函数：

                LAST_INSERT_ID();

6、加密解密函数

        1）、加密函数：

                PASSWORD(str)从原文密码str计算并返回加密后的密码字符串，当参数为NULL时返回NULL。此加密是单向的(不可逆)。

                MD5(str)为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL时返回NULL。

                ENCODE(str,pswd_str)使用pswd_str作为密码，加密str。

        2）、解密函数：

                DENCODE(crypt_str,pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str。crypt_str是由ENCODE()返回的字符串

7、其他函数

        1）、格式化函数：

                FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0则结果函数不含小数部分。

        2）、不同进制的数字进行函数转换：

                CONV(N,from_base,to_base)返回值为数值N的字符串表示，由from_base进制转化为to_base进制，如果任意参数为null,则返回值为NULL。自变量N被理解为一个整数，但是可以被指定为整数或字符串。最小基数为2，最大基数为36。

        3）、IP地址与数字相互转换的函数：

                INET_ATON(expr)给出y一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以s是4或8比特地址。

                INET_NTOA(expr)给定一个数字网络地址(4或8比特)，返回作为字符串的该地址的店地址表示。

        4）、加锁函数和解锁函数：

                GET_LOCK(str,timeout)设法使用字符串str给定的名字得到一个锁。

                RELEASE_LOCK(str)解锁。

                IS_FREE_LOCK(str)检查名为str的锁是否可以使用(换言之没有被封锁)。

                IS_USED_LOCK(str)检查名为str的锁是否正在被使用(换言之被封锁)。

        5）、重复执行指定操作的函数：

                BENCHMARK(count,expr)函数重复count次执行表达式expr。可以用于计算MYSQL处理表达式的速度。结果通常为0(0只是表示处理过程很快，并不是没有花费时间)。另一个作用是他可以在mysql客户端内部报告语句执行时间。

        6）、改变字符集的函数：

                CONVERT(... USING ...)用来在不同的字符集之间转换数据。

        7）、改变数据类型的函数：

                CAST(x,As type)和CONVERT(x,type)函数将一个类型的值转换为另一个类型的值，可转换的type有：BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED。

8. 聚合函数

        用于从多行数据中计算汇总值，例如：
                COUNT(column)：计算指定列中非NULL值的数量。
                SUM(column)：计算指定列的总和。
                AVG(column)：计算指定列的平均值。
                MAX(column)：找出指定列的最大值。
                MIN(column)：找出指定列的最小值。