MySQL數據庫中提供了很豐富的函數。MySQL函數包括數學函數、字符串函數、日期和時間函數、條件判斷函數、系統信息函數、加密函數、格式化函數等。通過這些函數,可以簡化用戶的操作。SELECT語句及其條件表達式都可以使用這些函數。同時,INSERT、UPDATE、DELECT語句及其條件表達式也可以使用這些函數。
數學函數是M有SQL中常用的一類函數。主要用於處理數字,包括整型、浮點數等。數學函數包括絕對值函數、正弦函數、余弦函數、獲取隨機數的函數等。
字符串函數主要用於處理表中的字符串。字符串函數包括求字符串長度、合並字符串、在字符串中插入子串、大小寫字母之間切換等函數。
CHAR_LENGTH(s)函數計算字符串s的字符數;
LENGTH(s)函數計算字符串s的長度。
CONCAT(s1,s2,、、、)函數和CONCAT_WS(x,s1.s2,、、、)函數都可以將s1、s2等多個字符串合並成一個字符串。但CONCAT_WS(x,s1,s3,、、、)可以將各字符串直接用參數x隔開。
SELECT CONCAT('bei','ji','ng'),CONCAT_WS('-','bei','ji','ng');
運行結果:
INSERT(s1,x,len,s2)函數將字符串s1中x位置開始長度為Len的字符串用s2替換。
SELECT s,INSERT(s,4,4,'fang') FROM t2;
運行結果:
UPPER(s)函數和UCASE(s)函數將字符串s的所遇字母變成大寫字母;LOWER(s)函數和LCASE(s)函數將字符串s的所有字母變成小寫字母。
SELECT UPPER('mysql'),UCASE('mysql'),LOWER('MYSQL'),LCASE('MYSQL');
運行結果:
LEFT(s,n)函數返回字符串s的前n個字符;RIGHT(s,n)函數返回字符串s的後n個字符。
SELECT s, LEFT(s,3),RIGHT(s,4) FROM t2;
運行結果:
LPAD(s1,len,s2)函數將字符串s2填充到s1的開始處,使字符串長度達到len;RPAD(s1,len,s2)函數將字符串s2填充到s1的結尾處,使字符串達到len.
SELECT s,LPAD(s,10,'+-'),RPAD(s,10,'+-') FROM t2;
運行結果:
LTTIM(s)函數將去點字符串s開始處的空格;
RTRIM(s)函數將去點字符串s結尾處的空格;
TRIM(s)函數將去掉字符串s開始處和結尾處的空格。
SELECT CONCAT('+',' me ','+'),CONCAT('+',LTRIM(' me '),'+');
運行結果:
TRIM(s1 FROM s)函數將去掉字符串s中開始處和結尾處的字符串s1。
SELECT TRIM('ab' FROM 'ababddddabddab');
運行結果:
REPEAT(s,n)函數將字符串s重復n次。
SELECT REPEAT('MYSQL-',5);
運行結果:
SPACE(n)函數返回n個空格;REPLACE(s,s1,s2)函數將字符串s2替代字符串s中的字符串s1。
SELECT CONCAT('+',SPACE(4),'+'),REPLACE('mysql','sql','book');
運行結果:
STRCMP(s1,s2)函數用來比較字符串s1和s2。如果s1大於s2,結果返回1;如果s1等於s2,結果返回0;如果s1小於s2,結果返回-1.
SELECT STRCMP('abc','abb'),STRCMP('abc','abc'),STRCMP('abc','abd');
運行結果:
SUBSTUING(s,n,len)函數和MID(s,n,len)函數從字符串s的第n個位置開始獲取長度為len的字符串。下面將演示SUBSTRING(s,n,len)函數和MID(s,n,len)函數的使用。
SELECT s ,SUBSTRING(s,4,3),MID(s,4,3) FROM t2;
運行結果:
LOCATE(s1,s)、POSITION(s1 IN s)和INSTR(s,s1)這三個函數從字符串s中獲取s1的開始位置。
SELECT s,LOCATE('jin',s),POSITION('jin' IN s),INSTR(s,'jin') FROM t2;
運行結果:
REVERSE(s)函數將字符串s的順序反過來。
SELECT s,REVERSE(s) FROM t2;
運行結果:
BLT(n,s1,s2,、、、)函數返回第n個字符串。
FIFLD(s,s1,s2,、、、)函數返回第一個與字符串s匹配的字符串的位置。
FIND_IN_SET(s1,s2)函數返回在字符串s2中與s1匹配的字符串的位置。其中,字符串s2中包含了若干個用逗號隔開的字符串。
SELECT FIND_IN_SET('like','i,like,bei,jing');
運行結果:
MAKE_SET(x,s1,s2,、、、)函數按x的二進制數從s1,s2,、、、sn中選取字符串。例如12的二進制是1100.這個二進制數從右到左的第三位和第四位是1,所以選取s3和s4。
日期和時間函數主要用於處理表中的日期和時間數據。日期和時間函數包括獲取當前日期的函數、獲取當前時間的函數、計算日期的函數、計算時間的函數等。
CURDATE()和CURRENT_DATE()函數獲取當前日期;
CURTIME()和CURRENT_TIME()函數獲取當前時間。
SELECT CURDATE(),CURTIME(),CURRENT_DATE(),CURRENT_TIME();
運行結果:
NOW()、CURRENT_TIMESTAMP()、LOCALTIME()和SYSDATE()這四個函數都用來獲取當前的日期和時間。
UNIX_TIMESTAMP()函數以UNIX時間戳的形式返回當前時間;
UNIX_TIMESTAMP(d)函數將時間d以UNIX時間戳的形式返回;
FROM_UNIXTIME(d)函數把UNIX時間戳的時間轉換為普通格式的時間。
UNIX_TIMESTAMP(d)函數和FROM_UNIXTIME(d)互為反函數。
SELECT NOW(), UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW());
MONTH(d)函數返回日期d中的月份值,其取值范圍是1~12;MONTHNAME(d)函數返回日期d中的月份的英文名稱,其中參數d可以是日期和時間,也可以是日期。
SELECT NOW(),MONTH(NOW()),MONTHNAME(NOW());
運行結果:
DAYNAME(d)函數返回日期d是星期幾,顯示其英文名;
DAYOFWEEK(d)函數也返回日期d是星期幾,1表示星期日,2表示星期一,
WEEKDAY(d)函數也返回日期d是星期幾,0表示星期一,1表示星期二,
SELECT NOW(),DAYNAME(NOW()),DAYOFWEEK(NOW()),WEEKDAY(NOW());
運行結果:
WEEK(d)函數和WEEKOFYEAR(d)函數都是計算日期d是本年的第幾個星期。
SELECT NOW(),WEEK(NOW()),WEEKOFYEAR(NOW());
運行結果:
DAYOFYEAR(d)函數日期d是本年的第幾天;
DAYOFMONTH(d)函數返回計算日期d是本月的第幾天。
SELECT NOW(),DAYOFYEAR(NOW()),DAYOFMONTH(NOW());
運行結果:
YEAR(d)函數返回日期d中的年分值;
QUARTER(d)函數返回季度值;
HOUR(t)函數返回時間t的小時值;
MINUTE(t)函數返回時間t中的分鐘值;
SECOND(t)函數返回時間t中的秒鐘值。
SELECT NOW(),YEAR(NOW()),QUARTER(NOW());
運行結果:
SELECT CURTIME(),HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME());
運行結果:
EXTRACT(type FROM d)函數從日期d中獲取指定的值。這個值是什麼有type決定。Type可以取YEAR、MONTH、DAY、HOUR、MINUTE、SECOND如果type的值是YEAR,結果返回年分值。
SELECT NOW(),EXTRACT(MONTH FROM NOW());
運行結果:
TIME_TO_SEC(t)函數將時間t轉換為以秒為單位的時間;
SEC_TO_TIME(s)函數將以秒為單位的時間s轉換為時分秒的格式。
SELECT CURTIME(),TIME_TO_SEC(CURTIME()),SEC_TO_TIME(76084);
運行結果:
1、TO_DAYS(d)、FROM_DAYS(n)和DATEDIFF(d1,d2)函數
2、ADDDATE(d,n)在日期d增加n天、SUBDATE(d,n) 在日期d減去n天、ADDTIME(t,n)在時間t上增加n秒 和SUBTIME(t,n) 在時間t上減少n秒函數
3、ADDDATE(d,INTERVAL expr type)和DATE_ADD(d,INTERVAL expr type)函數
1:
SELECT
CURDATE(),TO_DAYS(CURDATE()),FROM_DAYS(76084),
DATEDIFF(CURDATE(),'2016-04-12');
運行結果:
| CURDATE() | TO_DAYS(CURDATE()) | FROM_DAYS(76084) | DATEDIFF(CURDATE(),’2016-
04-12’) |
DATE_FORMAT(d,f)函數
TIME_FORMATE(t,f)函數
GET_FORMAT(type,s)函數
SELECT CURDATE(),DATE_FORMAT(CURDATE(),'%b %D %Y');
運行結果:
條件判斷函數用來在SQL語句中進行條件判斷。根據是否滿足判斷條件,SQL語句執行不同的分支。例如,從員工表中查詢員工的業績。如果業績高於指定值n,則輸出“good”。否則,輸出“bad”。
IF(expr,V1,V2)函數中,如果表達式expr成立,返回結果V1;否則,返回結果V2。
SELECT num,score,IF(score>=90, 'PASS', 'FAIL') FROM grade LIMIT 4;
運行結果:
IFNULL(V1,V2)函數中,如果V1的不為空,就顯示V1的值;否則就顯示V2的值。
1、CASE WHEN expr1 THEN v1 [WHEN expr2 THEN v2、、、] [ELSE vn] END
2、CASE expr WHEN e1 THEN v1 [WHEN e2 THEN v2、、、] [ELSE vn] END
SELECT id,grade,
CASE WHEN grade>60 THEN 'GOOD' WHEN grade=60 THEN 'PASS' ELSE 'FAIL' END
LEVEL FROM t6;
系統信息函數用來查詢MySQL數據庫的系統信息。例如,查詢數據庫的版本,查詢數據庫的當前用戶。
VERSION()函數返回數據庫的版本號;
CONNECTION_ID()函數返回服務器的連接數,也就是到現在為止MySQL服務的連接次數;
DATABASE()和SCHEMA()返回當前數據庫名
USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和CURRENT_USER這幾個函數可以返回當前用戶的名稱。
CHARSET(str)函數返回字符串str的字符集,一般情況這個字符集就是系統的默認字符集;COLLATION(str)函數返回字符串str的字符排列方式。
LAST_INSERT_ID()函數返回最後生成的AUTO_INCREMENT值。
加密函數是MySQL中用來對數據進行加密的函數。因為數據庫中有些很敏感的信息不希望被其他人看到,就應該通過加密方式來使這些數據變成看似亂碼的數據。
PASSWORD(str)函數可以對字符串str進行加密,一般情況下,PASSWORD(str)函數主要是用來給用戶的密碼加密的。
SELECT PASSWORD('abcd');
運行結果:
MD5(str)函數可以對字符串str進行加密。MD5(str)函數主要對普通的數據進行加密。
SELECT MD5('abcd');
運行結果:
ENCODE(str,pswd_str)函數可以使用字符串pswd_str來加密字符串str。加密的結果是一個二進制數,必須使用BLOB類型的字段來保存它。
DECODE(crypt_str,pawd_str)函數可以使用字符串pswd_str來為crypt_str解密。Crypt_str是通過ENCODE(str,pswd_str)加密後的二進制數據。字符串pswd_str應該與加密時的字符串pswd_str是相同的。
SELECT DECODE(ENCODE('abcd','aa'),'aa');
運行結果:
MySQL中除了上訴函數以外,還包含了很多函數。例如FORMAT(x,n)函數用來格式化數字x,INET_ATON()函數可以將IP轉換為數字。
FORMAT(x,n)函數可以將數字x進行格式化,將x保留到小數點後n位。
ASCII(s)返回字符串s的第一個字符的ASCII碼;BIN(x)返回x的二進制編碼;HEX(x)返回x的十六進制編碼;OCT(x)返回x的八進制編碼;CONV(x,f1,f2)將x從f1進制數變成f2進制數。
INET_ATON(IP)函數可以將IP地址轉換為數字表示;
INET_NTOA(n)函數可以將數字n轉換成IP的形式。
其中INET_ATON(IP)函數中IP值需要加上引號。
GET_LOCT(name,time)函數定義一個名稱為name、持續時間長度為time秒的鎖。如果鎖定成功,返回1;如果嘗試超時,返回0,如果遇到錯誤,返回NULL.
RELEASE_LOCK(name)函數解除名稱為name的鎖。如果解鎖成功,返回1;如果嘗試超時,返回0;如果解鎖失敗,返回NULLL;
IS_FREE_LOCK(name)函數判斷是否使用名為name的鎖。如果使用,返回0;否則,返回1.
BENCHMARK(count,expr)函數將表達式expr重復執行count次,然後返回執行時間,該函數可以用來判斷MySQL處理表達式的速度。