MySQL常用函数

阿牛9513
402 阅读10分钟

数学函数

  • ABS(x)：返回x的绝对值

  • BIN(x)：返回x的二进制

  • PI()：返回圆周率π，默认显示6位小数

  • SQRT(x)：返回非负数的x的二次方根

  • MOD(x,y)：返回x被y除后的余数

  • CEIL(x)、CEILING(x)：返回不小于x的最小整数

  • FLOOR(x)：返回不大于x的最大整数

  • GREATEST(x1,x2,...,xn)：返回集合中最大的值

  • LEAST(x1,x2,...,xn)：返回集合中最小的值

  • ROUND(x)、ROUND(x,y)：前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位

  • SIGN(x)：返回参数x的符号，-1表示负数，0表示0，1表示正数

  • POW(x,y)、POWER(x,y)：返回x的y次乘方的值

  • EXP(x)：返回e的x乘方后的值

  • LN(x)：返回x的自然对数

  • LOG(x,y)：返回x的以y为底的对数

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

  • RADIANS(x)：返回x由角度转化为弧度的值

  • DEGREES(x)：返回x由弧度转化为角度的值

  • SIN(x)、ASIN(x)：前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦

  • COS(x)、ACOS(x)：前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦

  • TAN(x)、ATAN(x)：前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切

  • COT(x)：返回给定弧度值x的余切

  • RAND()：返回 0 到 1 内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值

  • TRUNCATE(x,y)：返回数字x截短为y位小数的结果

  • CONV(N,from_base,to_base)：不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制

聚合函数

  • AVG(col)：返回指定列的平均值

  • COUNT(col)：返回指定列中非NULL值的个数

  • MIN(col)：返回指定列的最小值

  • MAX(col)：返回指定列的最大值

  • SUM(col)：返回指定列的所有值之和

  • GROUP_CONCAT(col)：返回由属于一组的列值连接组合而成的结果

字符串函数

  • STR_TO_DATE(str，fmt)：字符串转时间，fmt 日期格式见下方

  • LENGTH(str)：计算字符串字符个数

  • CONCAT(s1,s2，...)：返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL

  • CONCAT_WS(x,s1,s2,...)：返回多个字符串拼接之后的字符串，每个字符串之间有一个x

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

  • LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)：前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写

  • LEFT(s,n)、RIGHT(s,n)：前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符

  • LPAD(s1,len,s2)、RPAD(s1,len,s2)：前者返回s1，其左边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符；前者返回s1，其右边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符

  • LTRIM(s)、RTRIM(s)：前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除

  • TRIM(s)：返回字符串s删除了两边空格之后的字符串

  • TRIM(s1 FROM s)：删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格

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

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

  • REPLACE(s,s1,s2)：返回一个字符串，用字符串s2替代字符串s中所有的字符串s1

  • STRCMP(s1,s2)：若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1

  • SUBSTRING(s,n,len)、MID(s,n,len)：两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串

  • LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)：三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）

  • REVERSE(s)：将字符串s反转

  • ELT(N,str1,str2,str3,str4,...)：返回第N个字符串

  • CONVERT(str USING charset)：使用字符集charset表示字符串str

日期时间函数

  • CURDATE()、CURRENT_DATE()：将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定

  • CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()：这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定

  • UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)：前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数，后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数

  • FROM_UNIXTIME(date)：和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间

  • UTC_DATE()、UTC_TIME()：前者返回当前UTC（世界标准时间）日期值，其格式为"YYYY-MM-DD"或"YYYYMMDD"，后者返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中

  • MONTH(date)、MONTHNAME(date)：前者返回指定日期中的月份，后者返回指定日期中的月份的名称

  • DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)：DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二

  • WEEK(d)、WEEKOFYEAD(d)：前者计算日期d是一年中的第几周，后者计算某一天位于一年中的第几周

  • DAYOFYEAR(d)、DAYOFMONTH(d)：前者返回d是一年中的第几天，后者返回d是一月中的第几天

  • YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)：YEAR(date)返回指定日期对应的年份，范围是19702069；QUARTER(date)返回date对应一年中的季度，范围是14；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值

  • EXTRACE(type FROM date)：从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

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

  • SEC_TO_TIME()：和TIME_TO_SEC(time)互为反函数，将秒值转换为时间格式

  • DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)：返回将起始时间加上expr type之后的时间，比如DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)表示的就是把第一个时间加1秒

  • DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)：返回将起始时间减去expr type之后的时间

  • ADDTIME(date,expr)、SUBTIME(date,expr)：前者进行date的时间加操作，后者进行date的时间减操作

格式化函数

  • DATE_FORMAT(date,fmt)：依照字符串fmt格式化日期date值

  • FORMAT(x,y)：将数字 x 格式化，并以四舍五入的方式保留小数点后 y 位，结果以字符串形式返回

  • INET_ATON(ip)：返回IP地址的数字表示

  • INET_NTOA(num)：返回数字所代表的IP地址

  • TIME_FORMAT(time,fmt)：依照字符串fmt格式化时间time值 

    示例
    SELECT FORMAT(34234.34323432,3);
    SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');
    SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
    SELECT DATE_FORMAT(19990330,'%Y-%m-%d');
    SELECT DATE_FORMAT(NOW(),'%h:%i %p');
    SELECT INET_ATON('10.122.89.47');
    SELECT INET_NTOA(175790383);

    日期格式
    %M 月名字(January……December)
    %W 星期名字(Sunday……Saturday)
    %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
    %Y 年, 数字, 4 位
    %y 年, 数字, 2 位
    %a 缩写的星期名字(Sun……Sat)
    %d 月份中的天数, 数字(00……31)
    %e 月份中的天数, 数字(0……31)
    %m 月, 数字(01……12)
    %c 月, 数字(1……12)
    %b 缩写的月份名字(Jan……Dec)
    %j 一年中的天数(001……366)
    %H 小时(00……23)
    %k 小时(0……23)
    %h 小时(01……12)
    %I 小时(01……12)
    %l 小时(1……12)
    %i 分钟, 数字(00……59)
    %r 时间,12 小时(hh:mm:ss [AP]M)
    %T 时间,24 小时(hh:mm:ss)
    %S 秒(00……59)
    %s 秒(00……59)
    %p AM或PM
    %w 一个星期中的天数(0=Sunday ……6=Saturday)
    %U 星期(0……52), 这里星期天是星期的第一天
    %u 星期(0……52), 这里星期一是星期的第一

流程控制函数

  • IF(expr,v1,v2)：如果expr是TRUE则返回v1，否则返回v2

  • IFNULL(v1,v2)：如果v1不为NULL，则返回v1，否则返回v2

  • NULLIF(v1,v2)：如果v1=v2返回NULL，否则返回v1

  • CASE WHEN[test1] THEN [result1]...ELSE [default] END：如果testN是真，则返回resultN，否则返回default

  • CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END：如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn

排序函数

  • RANK()：并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名

  • DENSE_RANK 并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名

  • ROW_NUMBER 连续排名，即使相同的值，依旧按照连续数字进行排名

    示例
    select rank() OVER(order by e.salary desc) as row_num, e.salary from employee e;
    select dense_rank() OVER(order by e.salary desc) as row_num, e.salary from employee e;
    select row_number() OVER(order by e.salary desc) as row_num, e.salary from employee e;

加密函数

  • PASSWORD(str)：从原明文密码str计算并返回加密后的字符串密码，注意这个函数的加密是单向的（不可逆），因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用

  • MD5(str)：为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回

  • SHA(str)：计算字符串str的安全散列算法(SHA)校验和

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

  • DECODE(crypt_str,pswd_str)：使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串

  • ENCRYPT(str,salt)：使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str

系统信息函数

  • VERSION()：查看MySQL版本号

  • CONNECTION_ID()：查看当前用户的连接数

  • USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()：查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的

  • CHARSET(str)：查看字符串str使用的字符集

  • COLLATION()：查看字符串排列方式

  • DATABASE()：返回当前数据库名

  • BENCHMARK(count,expr)：将表达式expr重复运行count次，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。另一个作用是用它在MySQL客户端内部报告语句执行的时间

其他函数

  • INET_ATON(expr)：给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特
  • INET_NTOA(expr)：给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示
avatar
文章
阅读
粉丝