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();`