MySQL函数 · 程序员讲师课件

## 一、字符串函数 **函数**|**描述**|**示例** ---|---|--- `ASCII(s)`|返回字符串 s 的第一个字符的 ASCII 码|`SELECT ASCII(字段) AS 新命名 FROM 表名;` `CHAR_LENGTH(s)`|返回字符串 s 的字符数|`SELECT CHAR_LENGTH(字段) AS 新命名 FROM 表名;` `CHARACTER_LENGTH(s)`|返回字符串 s 的字符数|`SELECT CHARACTER_LENGTH(字段) AS 新命名 FROM 表名;` `CONCAT_WS(x,s1,s2...sn)`|同 CONCAT(s1,s2,...) 函数一样，但是每个字符串之间要加上 x，x 可以是分隔符|`SELECT CONCAT_WS('-',字段,字段) AS 新命名 FROM 表名;` `FIELD(s,s1,s2...)`|返回第一个字符串 s 在字符串列表(s1,s2...)中的位置|`SELECT FIELD("c", "a", "b", "c", "d", "e");` `FIND_IN_SET(s1,s2)`|返回在字符串s2中与s1匹配的字符串的位置|`SELECT FIND_IN_SET("c", "a,b,c,d,e");` `FORMAT(x,n)`|函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入|`SELECT FORMAT(250500.5634, 2);` `INSERT(s1,x,len,s2)`|字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串|`SELECT INSERT("ouyangke", 1, 6, "huangrong");` `LOCATE(s1,s)`|从字符串 s 中获取 s1 的开始位置|`SELECT LOCATE('yan','ouyangke');` `LCASE(s)`|将字符串 s 的所有字母变成小写字母|`SELECT LCASE('OUYANGKE')` `LEFT(s,n)`|返回字符串 s 的前 n 个字符|`SELECT LEFT('ouyangke',2)` `LOWER(s)`|将字符串 s 的所有字母变成小写字母|`SELECT LOWER('OUYANGKE')` `LTRIM(s)`|去掉字符串 s 开始处的空格|`SELECT LTRIM(" ouyangke") AS new_name;` `MID(s,n,length)`|从字符串 s 的 start 位置截取长度为 length 的子字符串，同 SUBSTRING(s,n,length)|`SELECT MID("ouyangke", 2, 3) AS new_name;` `POSITION(s1 IN s)`|从字符串 s 中获取 s1 的开始位置|`SELECT POSITION('y' in 'ouyangke');` `REPEAT(s,n)`|将字符串 s 重复 n 次|`SELECT REPEAT('ouyangke',3);` `REPLACE(s,s1,s2)`|将字符串 s2 替代字符串 s 中的字符串 s1|`SELECT REPLACE('ouyangke','ke','feng');` `REVERSE(s)`|将字符串s的顺序反过来|`SELECT REVERSE('ouyangke');` `RIGHT(s,n)`|返回字符串 s 的后 n 个字符|`SELECT RIGHT('ouyangke',2);` `RPAD(s1,len,s2)`|在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len|`SELECT RPAD('ouyangke',10,'huangrong');` `RTRIM(s)`|去掉字符串 s 结尾处的空格|`SELECT RTRIM("ouyangke ") AS new_name;` `SPACE(n)`|返回 n 个空格|`SELECT SPACE(10)` `STRCMP(s1,s2)`|比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1 < s2 返回 -1|`SELECT STRCMP("ouyangke", "huangrong");` `SUBSTR(s, start, length)`|从字符串 s 的 start 位置截取长度为 length 的子字符串|`SELECT SUBSTR("ouyangke", 2, 3) AS new_name;` `SUBSTRING(s, start, length)`|从字符串 s 的 start 位置截取长度为 length 的子字符串|`SELECT SUBSTRING("ouyangke", 2, 3) AS new_name;` `SUBSTRING_INDEX(s, delimiter, number)`|返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数，返回第 number 个字符左边的字符串。如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。|`SELECT SUBSTRING_INDEX('a*b','*',1);` `TRIM(s)`|去掉字符串 s 开始和结尾处的空格|`SELECT TRIM(' ouyangke ') AS new_name;` `UCASE(s)`|将字符串转换为大写|`SELECT UCASE("ouyangke");` `UPPER(s)`|将字符串转换为大写|`SELECT UPPER("ouyangke");` ## 二、数字函数 **函数**|**描述**|**示例** ---|---|--- `ABS(x)`|返回 x 的绝对值|`SELECT ABS(-1);` `ACOS(x)`|求 x 的反余弦值(参数是弧度)|`SELECT ACOS(0.25);` `ASIN(x)`|求反正弦值(参数是弧度)|`SELECT ASIN(0.25);` `ATAN(x)`|求反正切值(参数是弧度)|`SELECT ATAN(2.5);` `ATAN2(n, m)`|求反正切值(参数是弧度)|`SELECT ATAN2(-0.8, 2);` `AVG(expression)`|返回一个表达式的平均值，expression 是一个字段|`SELECT AVG(Price) AS AveragePrice FROM Products;` `CEIL(x)`|返回大于或等于 x 的最小整数|`SELECT CEIL(1.5);` `CEILING(x)`|返回大于或等于 x 的最小整数|`SELECT CEILING(1.5);` `COS(x)`|求余弦值(参数是弧度)|`SELECT COS(2);` `COT(x)`|求余切值(参数是弧度)|`SELECT COT(2);` `COUNT(expression)`|返回查询的记录总数，expression 参数是一个字段或者 * 号|`SELECT COUNT(name) AS new_name FROM user;` `DEGREES(x)`|将弧度转换为角度|`SELECT DEGREES(3.1415926535898);` `n DIV m`|整除，n 为被除数，m 为除数|`SELECT 10 DIV 5;` `EXP(x)`|返回 e 的 x 次方|`SELECT EXP(3);` `FLOOR(x)`|返回小于或等于 x 的最大整数|`SELECT FLOOR(1.5);` `GREATEST(expr1, expr2, expr3, ...)`|返回列表中的最大值|`ELECT GREATEST(3, 12, 34, 8, 25);` `LEAST(expr1, expr2, expr3, ...)`|返回列表中的最小值|`SELECT LEAST(3, 12, 34, 8, 25);` `LN`|返回数字的自然对数|`SELECT LN(2);` `LOG(x)`|返回自然对数(以 e 为底的对数)|`SELECT LOG(20.085536923188);` `LN`|返回数字的自然对数|`SELECT LN(2);` `LOG10(x)`|返回以 10 为底的对数|`SELECT LOG10(100);` `LOG2(x)`|返回以 2 为底的对数|`SELECT LOG2(6);` `MAX(expression)`|返回字段 expression 中的最大值|`SELECT MAX(age) AS new_name FROM user;` `MIN(expression)`|返回字段 expression 中的最大值|`SELECT MIN(age) AS new_name FROM user;` `MOD(x,y)`|返回 x 除以 y 以后的余数|`SELECT MOD(5,2);` `PI()`|返回圆周率(3.141593)|`SELECT PI();` `POW(x,y)`|返回圆周率(3.141593)|`SELECT POW(2,3);` `POWER(x,y)`|返回 x 的 y 次方|`SELECT POWER(2,3);` `RADIANS(x)`|将角度转换为弧度|`SELECT RADIANS(180);` `RAND()`|返回 0 到 1 的随机数|`SELECT RAND();` `ROUND(x)`|返回离 x 最近的整数|`SELECT ROUND(1.23456);` `SIGN(x)`|返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1|`SELECT SIGN(-10);` `SIN(x)`|求正弦值(参数是弧度)|`SELECT SIN(RADIANS(30));` `SQRT(x)`|返回x的平方根|`SELECT SQRT(25);` `SUM(expression)`|返回指定字段的总和|`SELECT SUM(age) AS new_name FROM user;` `TAN(x)`|求正切值(参数是弧度)|`SELECT TAN(1.75);` `TRUNCATE(x,y)`|返回数值 x 保留到小数点后 y 位的值(与 ROUND 最大的区别是不会进行四舍五入)|`SELECT TRUNCATE(1.23456,3);` ## 三、日期函数 **函数**|**描述**|**示例** ---|---|--- `ADDDATE(d,n)`|计算起始日期 d 加上 n 天的日期|`SELECT ADDDATE("2019-10-10", INTERVAL 10 DAY);` `ADDTIME(t,n)`|时间 t 加上 n 秒的时间|`SELECT ADDTIME('2019-11-11 11:11:11', 5);` `CURDATE()`|返回当前日期|`SELECT CURDATE();` `CURRENT_DATE()`|返回当前日期|`SELECT CURRENT_DATE();` `CURRENT_TIME()`|返回当前日期|`SELECT CURRENT_TIME();` `CURRENT_TIMESTAMP()`|返回当前日期|`SELECT CURRENT_TIMESTAMP();` `CURTIME()`|返回当前日期|`SELECT CURTIME();` `DATE()`|返回当前日期|`SELECT DATE("2019-10-10 12:12:12");` `DATEDIFF(d1,d2)`|计算日期 d1->d2 之间相隔的天数|`SELECT DATEDIFF('2019-01-01','2019-10-10');` `DATE_ADD(d，INTERVAL expr type)`|计算起始日期 d 加上一个时间段后的日期|`SELECT ADDDATE('2019-11-11 11:11:11',1);` `DATE_FORMAT(d,f)`|按表达式 f的要求显示日期 d|`SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r');` `DATE_SUB(date,INTERVAL expr type)`|函数从日期减去指定的时间间隔|`SELECT DATE_SUB(age,INTERVAL 2 DAY) AS new_name FROM user;` `DAY(d)`|返回日期值 d 的日期部分|`SELECT DAY("2019-10-15");` `DAYNAME(d)`|返回日期 d 是星期几|`SELECT DAYNAME("2019-10-15 12:12:12");` `DAYOFMONTH(d)`|计算日期 d 是本月的第几天|`SELECT DAYOFMONTH("2019-10-15 12:12:12");` `DAYOFWEEK(d)`|日期 d 今天是星期几|`SELECT DAYOFWEEK("2019-10-15 12:12:12");` `DAYOFYEAR(d)`|计算日期 d 是本年的第几天|`SELECT DAYOFYEAR("2019-10-15 12:12:12");` `EXTRACT(type FROM d)`|从日期 d 中获取指定的值，type 指定返回的值|`SELECT EXTRACT(MINUTE FROM '2019-10-15 12:12:12');` `FROM_DAYS(n)`|计算从 0000 年 1 月 1 日开始 n 天后的日期|`SELECT FROM_DAYS(1111);` `HOUR(t)`|返回 t 中的小时值|`SELECT HOUR('11:12:13');` `LAST_DAY(d)`|返回给给定日期的那一月份的最后一天|`SELECT LAST_DAY("2017-06-20");` `LOCALTIME()`|返回当前日期和时间|`SELECT LOCALTIME();` `LOCALTIMESTAMP()`|返回当前日期和时间|`SELECT LOCALTIMESTAMP();` `MAKEDATE(year, day-of-year)`|返回当前日期和时间|`SELECT LOCALTIMESTAMP();` `MAKETIME(hour, minute, second)`|组合时间，参数分别为小时、分钟、秒|`SELECT MAKETIME(11, 35, 4);` `MICROSECOND(date)`|返回日期参数所对应的微秒数|`SELECT MICROSECOND("2019-10-10 12:12:12.000023");` `MINUTE(t)`|返回 t 中的分钟值|`SELECT MINUTE('11:12:13');` `MONTHNAME(d)`|返回日期当中的月份名称|`SELECT MONTHNAME('2019-11-11 11:11:11');` `MONTH(d)`|返回日期d中的月份值，1 到 12|`SELECT MONTH('2019-11-11 11:11:11');` `NOW()`|返回当前日期和时间|`SELECT NOW();` `PERIOD_ADD(period, number)`|为年-月组合日期添加一个时段|`SELECT PERIOD_ADD(201903, 5);` `PERIOD_DIFF(period1, period2)`|返回两个时段之间的月份差值|`SELECT PERIOD_DIFF(201910, 201903);` `QUARTER(d)`|返回日期d是第几季节，返回 1 到 4|`SELECT QUARTER('2019-11-11 11:11:11');` `SECOND(t)`|返回 t 中的秒钟值|`SELECT SECOND('11:12:13');` `SEC_TO_TIME(s)`|将以秒为单位的时间 s 转换为时分秒的格式|`SELECT SEC_TO_TIME(4320);` `STR_TO_DATE(string, format_mask)`|将字符串转变为日期|`SELECT STR_TO_DATE("August 10 2019", "%M %d %Y");` `SUBDATE(d,n)`|日期 d 减去 n 天后的日期|`SELECT SUBDATE('2019-11-11 11:11:11', 1);` `SUBTIME(t,n)`|时间 t 减去 n 秒的时间|`SELECT SUBTIME('2019-11-11 11:11:11', 5);` `SYSDATE()`|返回当前日期和时间|`SELECT SYSDATE();` `TIME(expression)`|提取传入表达式的时间部分|`SELECT TIME("19:30:10");` `TIME_FORMAT(t,f)`|按表达式 f 的要求显示时间 t|`SELECT TIME_FORMAT('11:11:11','%r');` `TIME_TO_SEC(t)`|将时间 t 转换为秒|`SELECT TIME_TO_SEC('1:12:00');` `TIMEDIFF(time1, time2)`|计算时间差值|`SELECT TIMEDIFF("13:10:11", "13:10:10");` `TIMESTAMP(expression, interval)`|单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和|`SELECT TIMESTAMP("2019-10-10", "11:11:11");` `TO_DAYS(d)`|计算日期 d 距离 0000 年 1 月 1 日的天数|`SELECT TO_DAYS('0001-01-01 01:01:01');` `WEEK(d)`|计算日期 d 是本年的第几个星期，范围是 0 到 53|`SELECT WEEK('2019-11-11 11:11:11');` `WEEKDAY(d)`|日期 d 是星期几，0 表示星期一，1 表示星期二|`SELECT WEEKDAY("2019-06-15");` `WEEKOFYEAR(d)`|计算日期 d 是本年的第几个星期，范围是 0 到 53|`SELECT WEEKOFYEAR('2019-11-11 11:11:11');` `YEAR(d)`|返回年份|`SELECT YEAR("2019-06-15");` `YEARWEEK(date, mode)`|返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推|`SELECT YEARWEEK("2019-10-10");` ## 四、高级函数 **函数**|**描述**|**示例** ---|---|--- `BIN(x)`|返回 x 的二进制编码|`SELECT BIN(15);` `BINARY(s)`|将字符串 s 转换为二进制字符串|`SELECT BINARY "ouyangke";` `CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END`|CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了|`SELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END` `CAST(x AS type)`|转换数据类型|`SELECT CAST("2019-10-10" AS DATE);` `COALESCE(expr1, expr2, ...., expr_n)`|返回参数中的第一个非空表达式（从左向右）|`SELECT COALESCE(NULL, NULL, NULL, 'ouyangke', NULL, 'huangrong');` `CONNECTION_ID()`|返回服务器的连接数|`SELECT CONNECTION_ID();` `CONV(x,f1,f2)`|返回 f1 进制数变成 f2 进制数|`SELECT CONV(15,10,2);` `CONVERT(s USING cs)`|函数将字符串 s 的字符集变成 cs|`SELECT CHARSET(CONVERT('ABC' USING gbk));` `CURRENT_USER()`|返回当前用户|`SELECT CURRENT_USER();` `DATABASE()`|返回当前数据库名|`SELECT DATABASE();` `IF(expr,v1,v2)`|如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2|`SELECT IF(1 > 0,'正确','错误');` `IFNULL(v1,v2)`|如果 v1 的值不为 NULL，则返回 v1，否则返回 v2|`SELECT IFNULL(null,'Hello Word');` `ISNULL(expression)`|判断表达式是否为 NULL|`SELECT ISNULL(NULL);` `LAST_INSERT_ID()`|返回最近生成的 AUTO_INCREMENT 值|`SELECT LAST_INSERT_ID();` `NULLIF(expr1, expr2)`|比较两个字符串，如果字符串 expr1 与 expr2 相等返回 NULL，否则返回 expr1|`SELECT NULLIF(25, 25);` `SESSION_USER()`|返回当前用户|`SELECT SESSION_USER();` `SYSTEM_USER()`|返回当前用户|`SELECT SYSTEM_USER();` `USER()`|返回当前用户|`SELECT USER();` `VERSION()`|返回数据库的版本号|`SELECT VERSION();`