程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL學習筆記10(MySQL函數)

MySQL學習筆記10(MySQL函數)

編輯:MySQL綜合教程

MySQL學習筆記10(MySQL函數)


MySQL學習筆記10

MySQL函數

MySQL數據庫中提供了很豐富的函數。MySQL函數包括數學函數、字符串函數、日期和時間函數、條件判斷函數、系統信息函數、加密函數、格式化函數等。通過這些函數,可以簡化用戶的操作。SELECT語句及其條件表達式都可以使用這些函數。同時,INSERT、UPDATE、DELECT語句及其條件表達式也可以使用這些函數。

1:數學函數

數學函數是M有SQL中常用的一類函數。主要用於處理數字,包括整型、浮點數等。數學函數包括絕對值函數、正弦函數、余弦函數、獲取隨機數的函數等。

2:字符串函數

字符串函數主要用於處理表中的字符串。字符串函數包括求字符串長度、合並字符串、在字符串中插入子串、大小寫字母之間切換等函數。

2.1 計算字符串字符數的函數和字符串長度的函數

CHAR_LENGTH(s)函數計算字符串s的字符數;
LENGTH(s)函數計算字符串s的長度。

2.2 合並字符串的函數

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');

運行結果:

CONCAT(‘bei’,’ji’,’ng’) CONCAT_WS(‘-‘,’bei’,’ji’,’ng’) beijing bei-ji-ng

2.3 替換字符串的函數

INSERT(s1,x,len,s2)函數將字符串s1中x位置開始長度為Len的字符串用s2替換。

SELECT s,INSERT(s,4,4,'fang') FROM t2;

運行結果:

s INSERT(s,4,4,’fang’) beijing beifang

2.4 字母大小寫轉換函數

UPPER(s)函數和UCASE(s)函數將字符串s的所遇字母變成大寫字母;LOWER(s)函數和LCASE(s)函數將字符串s的所有字母變成小寫字母。

SELECT UPPER('mysql'),UCASE('mysql'),LOWER('MYSQL'),LCASE('MYSQL');

運行結果:

UPPER(‘mysql’) UCASE(‘mysql’) LOWER(‘MYSQL’) LCASE(‘MYSQL’) MYSQL MYSQL mysql mysql

2.5 獲取指定長度的字符串的函數

LEFT(s,n)函數返回字符串s的前n個字符;RIGHT(s,n)函數返回字符串s的後n個字符。

SELECT s, LEFT(s,3),RIGHT(s,4) FROM t2;

運行結果:

s LEFT(s,3) RIGHT(s,4) beijing bei jing

2.6 填充字符串的函數

LPAD(s1,len,s2)函數將字符串s2填充到s1的開始處,使字符串長度達到len;RPAD(s1,len,s2)函數將字符串s2填充到s1的結尾處,使字符串達到len.

SELECT s,LPAD(s,10,'+-'),RPAD(s,10,'+-') FROM t2;

運行結果:

s LPAD(s,10,’+-‘) RPAD(s,10,’+-‘) beijing +-+beijing beijing+-+

2.7 刪除空格函數

LTTIM(s)函數將去點字符串s開始處的空格;
RTRIM(s)函數將去點字符串s結尾處的空格;
TRIM(s)函數將去掉字符串s開始處和結尾處的空格。

SELECT CONCAT('+',' me ','+'),CONCAT('+',LTRIM(' me '),'+');

運行結果:

CONCAT(‘+’,’ me ‘,’+’) CONCAT(‘+’,LTRIM(’ me ‘),’+’) me + +me +

2.8 刪除指定字符串的函數

TRIM(s1 FROM s)函數將去掉字符串s中開始處和結尾處的字符串s1。

SELECT TRIM('ab' FROM 'ababddddabddab');

運行結果:

TRIM(‘ab’ FROM ‘ababddddabddab’) ddddabdd

2.9 重復生成字符串的函數

REPEAT(s,n)函數將字符串s重復n次。

SELECT REPEAT('MYSQL-',5);

運行結果:

REPEAT(‘MYSQL-‘,5) MYSQL-MYSQL-MYSQL-MYSQL-MYSQL-

2.10 空格函數和替換函數

SPACE(n)函數返回n個空格;REPLACE(s,s1,s2)函數將字符串s2替代字符串s中的字符串s1。

SELECT CONCAT('+',SPACE(4),'+'),REPLACE('mysql','sql','book');

運行結果:

CONCAT(‘+’,SPACE(4),’+’) REPLACE(‘mysql’,’sql’,’book’) + mybook

2.11 比較字符串大小的函數

STRCMP(s1,s2)函數用來比較字符串s1和s2。如果s1大於s2,結果返回1;如果s1等於s2,結果返回0;如果s1小於s2,結果返回-1.

SELECT STRCMP('abc','abb'),STRCMP('abc','abc'),STRCMP('abc','abd');

運行結果:

STRCMP(‘abc’,’abb’) STRCMP(‘abc’,’abc’) STRCMP(‘abc’,’abd’) 1 0 -1

2.12 獲取子串的函數

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;

運行結果:

s SUBSTRING(s,4,3) MID(s,4,3) beijing jin jin

2.13 匹配字符串開始位置的函數

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;

運行結果:

s LOCATE(‘jin’,s) POSITION(‘jin’ IN s) INSTR(s,’jin’) beijing 4 4 4

2.14 字符串逆序的函數

REVERSE(s)函數將字符串s的順序反過來。

SELECT s,REVERSE(s) FROM t2;

運行結果:

s REVERSE(s) beijing gnijieb

2.15 返回指定位置的字符串函數

BLT(n,s1,s2,、、、)函數返回第n個字符串。

2.16 返回指定字符串位置的函數

FIFLD(s,s1,s2,、、、)函數返回第一個與字符串s匹配的字符串的位置。

2.17 返回子串位置的函數

FIND_IN_SET(s1,s2)函數返回在字符串s2中與s1匹配的字符串的位置。其中,字符串s2中包含了若干個用逗號隔開的字符串。

SELECT FIND_IN_SET('like','i,like,bei,jing');

運行結果:

FIND_IN_SET(‘like’,’i,like,bei,jing’) 2

2.18 選取字符串的函數

MAKE_SET(x,s1,s2,、、、)函數按x的二進制數從s1,s2,、、、sn中選取字符串。例如12的二進制是1100.這個二進制數從右到左的第三位和第四位是1,所以選取s3和s4。

3:日期和時間函數

日期和時間函數主要用於處理表中的日期和時間數據。日期和時間函數包括獲取當前日期的函數、獲取當前時間的函數、計算日期的函數、計算時間的函數等。

3.1 獲取當前日期的函數和獲取當前時間的函數

CURDATE()和CURRENT_DATE()函數獲取當前日期;
CURTIME()和CURRENT_TIME()函數獲取當前時間。

SELECT CURDATE(),CURTIME(),CURRENT_DATE(),CURRENT_TIME();

運行結果:

CURDATE() CURTIME() CURRENT_DATE() CURRENT_TIME() 2016-04-09 20:21:13 2016-04-09 20:21:13

3.2 獲取當前日期和時間的函數

NOW()、CURRENT_TIMESTAMP()、LOCALTIME()和SYSDATE()這四個函數都用來獲取當前的日期和時間。

3.3 UNIX時間戳函數

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());
NOW() UNIX_TIMESTAMP() UNIX_TIMESTAMP(NOW()) 2016-04-09 20:30:05 1460205005 1460205005

3.4 獲取月份的函數

MONTH(d)函數返回日期d中的月份值,其取值范圍是1~12;MONTHNAME(d)函數返回日期d中的月份的英文名稱,其中參數d可以是日期和時間,也可以是日期。

SELECT NOW(),MONTH(NOW()),MONTHNAME(NOW());

運行結果:

NOW() MONTH(NOW()) MONTHNAME(NOW()) 2016-04-09 20:38:05 4 April

3.5 獲取星期的函數

DAYNAME(d)函數返回日期d是星期幾,顯示其英文名;
DAYOFWEEK(d)函數也返回日期d是星期幾,1表示星期日,2表示星期一,
WEEKDAY(d)函數也返回日期d是星期幾,0表示星期一,1表示星期二,

SELECT NOW(),DAYNAME(NOW()),DAYOFWEEK(NOW()),WEEKDAY(NOW());

運行結果:

NOW() DAYNAME(NOW()) DAYOFWEEK(NOW()) WEEKDAY(NOW()) 2016-04-09 20:42:55 Saturday 7 5

3.6 獲取星期數的函數

WEEK(d)函數和WEEKOFYEAR(d)函數都是計算日期d是本年的第幾個星期。

SELECT NOW(),WEEK(NOW()),WEEKOFYEAR(NOW());

運行結果:

NOW() WEEK(NOW()) WEEKOFYEAR(NOW()) 2016-04-09 20:46:15 14 14

3.7 獲取天數的函數

DAYOFYEAR(d)函數日期d是本年的第幾天;
DAYOFMONTH(d)函數返回計算日期d是本月的第幾天。

SELECT NOW(),DAYOFYEAR(NOW()),DAYOFMONTH(NOW());

運行結果:

NOW() DAYOFYEAR(NOW()) DAYOFMONTH(NOW()) 2016-04-09 20:49:01 100 9

3.8 獲取年份、季度、小時、分鐘、秒鐘的函數

YEAR(d)函數返回日期d中的年分值;
QUARTER(d)函數返回季度值;
HOUR(t)函數返回時間t的小時值;
MINUTE(t)函數返回時間t中的分鐘值;
SECOND(t)函數返回時間t中的秒鐘值。

SELECT NOW(),YEAR(NOW()),QUARTER(NOW());

運行結果:

NOW() YEAR(NOW()) QUARTER(NOW()) 2016-04-09 20:55:24 2016 2
SELECT CURTIME(),HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME());

運行結果:

CURTIME() HOUR(CURTIME()) MINUTE(CURTIME()) SECOND(CURTIME()) 20:57:08 20 57 8

3.9 獲取日期的指定值的函數

EXTRACT(type FROM d)函數從日期d中獲取指定的值。這個值是什麼有type決定。Type可以取YEAR、MONTH、DAY、HOUR、MINUTE、SECOND如果type的值是YEAR,結果返回年分值。

SELECT NOW(),EXTRACT(MONTH FROM NOW());

運行結果:

NOW() EXTRACT(MONTH FROM NOW()) 2016-04-09 21:03:06 4

3.10 時間和秒鐘轉換的函數

TIME_TO_SEC(t)函數將時間t轉換為以秒為單位的時間;
SEC_TO_TIME(s)函數將以秒為單位的時間s轉換為時分秒的格式。

SELECT CURTIME(),TIME_TO_SEC(CURTIME()),SEC_TO_TIME(76084);

運行結果:

CURTIME() TIME_TO_SEC(CURTIME()) SEC_TO_TIME(76084) 21:08:30 76110 21:08:04

3.11 計算日期和時間的函數

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’) |

2016-04-09 736428 0208-04-24 -3

3.12 將日期和時間格式化的函數

DATE_FORMAT(d,f)函數
TIME_FORMATE(t,f)函數
GET_FORMAT(type,s)函數

SELECT CURDATE(),DATE_FORMAT(CURDATE(),'%b %D %Y');

運行結果:

CURDATE() DATE_FORMAT(CURDATE(),’%b %D %Y’) 2016-04-09 Apr 9th 2016

4:條件判斷函數

條件判斷函數用來在SQL語句中進行條件判斷。根據是否滿足判斷條件,SQL語句執行不同的分支。例如,從員工表中查詢員工的業績。如果業績高於指定值n,則輸出“good”。否則,輸出“bad”。

4.1 IF(expr,V1,V2)函數

IF(expr,V1,V2)函數中,如果表達式expr成立,返回結果V1;否則,返回結果V2。

SELECT num,score,IF(score>=90, 'PASS', 'FAIL') FROM grade LIMIT 4;

運行結果:

num score IF(score>=90, ‘PASS’, ‘FAIL’) 1001 80 FAIL 1001 90 PASS 1001 85 FAIL 1001 95 PASS

4.2 IFNULL(V1,V2)函數

IFNULL(V1,V2)函數中,如果V1的不為空,就顯示V1的值;否則就顯示V2的值。

4.3 CASE函數

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;

5:系統信息函數

系統信息函數用來查詢MySQL數據庫的系統信息。例如,查詢數據庫的版本,查詢數據庫的當前用戶。

5.1 獲取MySQL版本號、連接數、數據庫名的函數

VERSION()函數返回數據庫的版本號;
CONNECTION_ID()函數返回服務器的連接數,也就是到現在為止MySQL服務的連接次數;
DATABASE()和SCHEMA()返回當前數據庫名

5.2 獲取用戶名的函數

USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和CURRENT_USER這幾個函數可以返回當前用戶的名稱。

5.3 獲取字符串的字符集和排序方式的函數

CHARSET(str)函數返回字符串str的字符集,一般情況這個字符集就是系統的默認字符集;COLLATION(str)函數返回字符串str的字符排列方式。

5.4 獲取最後一個自動生成的ID值的函數

LAST_INSERT_ID()函數返回最後生成的AUTO_INCREMENT值。

6:加密函數

加密函數是MySQL中用來對數據進行加密的函數。因為數據庫中有些很敏感的信息不希望被其他人看到,就應該通過加密方式來使這些數據變成看似亂碼的數據。

6.1 加密函數PASSWORD(str)

PASSWORD(str)函數可以對字符串str進行加密,一般情況下,PASSWORD(str)函數主要是用來給用戶的密碼加密的。

SELECT PASSWORD('abcd');

運行結果:

PASSWORD(‘abcd’) *A154C52565E9E7F94BFC08A1FE702624ED8EFFDA

6.2 加密函數MD5(str)

MD5(str)函數可以對字符串str進行加密。MD5(str)函數主要對普通的數據進行加密。

SELECT MD5('abcd');

運行結果:

MD5(‘abcd’) e2fc714c4727ee9395f324cd2e7f331f

6.3 加密函數ENCODE(str,pswd_str)

ENCODE(str,pswd_str)函數可以使用字符串pswd_str來加密字符串str。加密的結果是一個二進制數,必須使用BLOB類型的字段來保存它。

6.4解密函數DECODE(crypt_str,pswd_str)

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');

運行結果:

DECODE(ENCODE(‘abcd’,’aa’),’aa’) abcd

7:其他函數

MySQL中除了上訴函數以外,還包含了很多函數。例如FORMAT(x,n)函數用來格式化數字x,INET_ATON()函數可以將IP轉換為數字。

7.1 格式化函數

FORMAT(x,n)函數可以將數字x進行格式化,將x保留到小數點後n位。

7.2 不同進制的數字進行轉換的函數

ASCII(s)返回字符串s的第一個字符的ASCII碼;BIN(x)返回x的二進制編碼;HEX(x)返回x的十六進制編碼;OCT(x)返回x的八進制編碼;CONV(x,f1,f2)將x從f1進制數變成f2進制數。

7.3 IP地址與數字相互轉換的函數

INET_ATON(IP)函數可以將IP地址轉換為數字表示;
INET_NTOA(n)函數可以將數字n轉換成IP的形式。
其中INET_ATON(IP)函數中IP值需要加上引號。

7.4 加鎖函數和解鎖函數

GET_LOCT(name,time)函數定義一個名稱為name、持續時間長度為time秒的鎖。如果鎖定成功,返回1;如果嘗試超時,返回0,如果遇到錯誤,返回NULL.
RELEASE_LOCK(name)函數解除名稱為name的鎖。如果解鎖成功,返回1;如果嘗試超時,返回0;如果解鎖失敗,返回NULLL;
IS_FREE_LOCK(name)函數判斷是否使用名為name的鎖。如果使用,返回0;否則,返回1.

7.5 重復執行指定操作的函數

BENCHMARK(count,expr)函數將表達式expr重復執行count次,然後返回執行時間,該函數可以用來判斷MySQL處理表達式的速度。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved