Mysql中常用函式的使用示例

場景

基礎知識回顧：mysql中常用函式的使用示例。

注：

部落格：
https://blog.csdn.net/badao_liumang_qizhi

實現

數學函式

-- ABS(x)返回x的絕對值
SELECT ABS(-1),ABS(2);
-- PI()返回圓周率
SELECT PI();
-- SQRT(x)返回非負數x的二次方根
SELECT SQRT(4);
-- MOD(x,y)返回x被y除之後的餘數
SELECT MOD(10,3);
-- CEIL(X) CEILING(X)返回不小於x的最小整數值，返回值轉化為一個BIGINT
SELECT CEIL(3.5),CEILING(2.22);
-- FLOOR(X)返回不大於x的最大整數值，返回值轉化為一個BIGINT
SELECT FLOOR(3.5);
-- RAND()返回一個隨機浮點數v,0<=v<=1
SELECT RAND();
-- RAND(X)返回一個隨機浮點值v,0<=v<=1。引數x為整數，被用作種子值，用來產生重複序列
SELECT RAND(1);
-- ROUND(X)返回最接近於引數x的整數，對x值進行四捨五入
SELECT ROUND(3.4);
-- ROUND(X,Y)返回最接近於引數x的值，此值保留到小數點的後面的y位
SELECT ROUND(3.564,2);
-- TRUNCATE(X,Y)返回截去小數點後y位的數值x
SELECT TRUNCATE(3.2565,3);
-- SIGN(X)返回引數x的符號
SELECT SIGN(10),SIGN(-10);
-- POW(X,Y) POWER(X,Y)返回x的y次方
SELECT POW(2,3),POWER(3,2);
-- EXP(X)返回e的x次方
SELECT EXP(2);
-- LOG(X)返回x的自然對數，x相對於基數e的對數
SELECT LOG(8);
-- LOG10(X)返回x的基數為10的對數
SELECT LOG10(10);
-- RADIANS(X)返回引數x由角度轉化為弧度的值
SELECT RADIANS(90);
-- DEGREES(X)返回引數x由弧度轉化為角度的值
SELECT DEGREES(180);
-- SIN(X)返回x的正弦值
SELECT SIN(3);
-- ASIN(X)返回x的反正弦，即正弦為x的值
SELECT ASIN(0.1411);
-- COS(X)返回x的餘弦值
SELECT COS(5);
-- ACOS(X)返回引數x的反餘弦，即餘弦為x的值
SELECT ACOS(0.14);
-- TAN(X)返回x的正切值
SELECT TAN(10);
-- COT(X)返回x的餘切值
SELECT COT(10);

字串函式

-- CHAR_LENGTH(str)計算字串字元數函式，返回str中包含的字元個數
SELECT CHAR_LENGTH('BADAO')
-- LENGTH(str)計算字串長度函式，返回字串的位元組長度
SELECT LENGTH('BADAO')
-- CONCAT(str1,str2,...)合併字串函式，引數可以是一個或多個
SELECT CONCAT('BADAO','DE','CHENGXVYUAN');
-- CONCAT_WS(separator,str1,str2,...)此函式代表CONCAT With Separator,是CONCAT()的特殊形式
SELECT CONCAT_WS('-','BADAO','DE');
-- INSERT(str,pos,len,newstr)替換字串函式，返回字串str，在位置pos起始的len個字元長的子串由字串newstr代替
SELECT INSERT('BADAODECHENGXVYUAN',3,3,'BA');
-- LOWER(str) LCASE(str)將字串中的字母轉換為小寫
SELECT LOWER('BADAO'),LCASE('DE');
-- UPPER(str) UCASE(str)將字串中的字母轉換成大寫
SELECT UPPER('badao'),UCASE('de');
-- LEFT(str,len)擷取左側字串函式，返回str的最左邊len個字元
SELECT LEFT('BADAODE',5);
-- RIGHT(str,len)擷取右側字串函式，返回str的最右邊len個字元
SELECT RIGHT('BADAODE',2);
-- LPAD(str,len,padstr)填充左側字串函式，返回字串str的左邊由字串padstr填補到滿足len個字元長度
SELECT LPAD('BADAO',7,'-');
-- RPAD(str,len,padstr)填充右側字串函式，返回字串str的右邊由字串padstr填補到滿足len個字元長度
SELECT RPAD('BADAO',7,'-');
-- LTRIM(str)刪除字串左側空格函式
SELECT LTRIM(' BADAO ');
-- RTRIM(str)刪除字串右側空格函式
SELECT RTRIM(' BADAO ');
-- TRIM(str)刪除字串左右兩側空格函式
SELECT TRIM(' BADAO ');
-- TRIM(s1 from str)刪除指定字串函式，用於刪除字串str中兩端包含的子字串s1
SELECT TRIM('A' FROM 'ABADAOAA');
-- REPEAT(str,count)重複生成字串函式，返回一個由重複的字串str組成的字串，該字串中str的重複次數是count
SELECT REPEAT('BA',2);
-- SPACE(N)空格函式，返回一個由n個空格組成的字串
SELECT SPACE(2);
-- REPLACE(str,from_str,to_str)替換函式，使用字串to_str替換字串str中所有的子字串from_str
SELECT REPLACE('BADAODECHENGXVYUANBABA','BA','BB');
-- STRCMP(expr1,expr2)比較字串大小函式
SELECT STRCMP('12','123'),STRCMP('123','12');
-- SUBSTRING(str,pos,len) MID(str,pos,len)都是獲取子字串的函式，從字串str中獲取一個長度為len的子字串，起始位置是pos,此函式的len可以省略
SELECT SUBSTR('BADAODE',3,3);
-- LOCATE(substr,str)匹配子字串中開始位置的函式，返回子字串substr在字串str中第一次出現的位置
SELECT LOCATE('DAO','BADAO');
-- POSITION(substr IN str)匹配子字串開始位置的函式，功能同LOCATE函式，返回子字串substr在str中的開始位置
SELECT POSITION('DAO' IN 'BADAO');
-- INSTR(str,substr)匹配子字串開始位置的函式，功能同上面兩個函式
SELECT INSTR('BADAO','DAO');
-- REVERSE(str)字串逆序函式
SELECT REVERSE('BADAO');
-- ELT(N,str1,str2,str3,...)返回指定位置的字串函式，根據n的取值返回指定的字串sn
SELECT ELT(2,'BADAO','DEE','CHENGXV');
-- FIELD(str,str1,str2,str3,...)返回指定字串位置的函式，用於返回字串str在列表str1、str2等中第一次出現的位置，如果找不到則返回0
SELECT FIELD('A','BDO','DEA','CHENGXVYUAN','A');
-- FIND_IN_SET(str,strlist)返回子字串位置的函式，用於返回字串str在字串列表strlist中出現的位置
SELECT FIND_IN_SET('A','A,B,C');
-- MAKE_SET(bits,str1,str2,...)選取字串的函式，用於返回一個設定值(一個包含被逗號分開的子字串的字串)，由在bits組中具有相應位的字串組成
SELECT MAKE_SET(2,'A','B','C');

日期和時間函式

-- CURDATE() CURRENT_DATE()返回當前系統的日期值
SELECT CURDATE(),CURRENT_DATE();
-- CURTIME() CURRENT_TIME()返回當前系統的時間值
SELECT CURTIME(),CURRENT_TIME();
-- CURRENT_TIMESTAMP() LOCALTIME() NOW() SYSDATE()這四個函式作用相同，都是返回當前系統的日期和時間值
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
-- UNIX_TIMESTAMP(date)UNIX時間戳函式，返回一個以UNIX時間戳為基礎的無符號整數
SELECT UNIX_TIMESTAMP();
-- FROM_UNIXTIME(unix_timestamp)把UNIX時間戳轉換為時間格式的函式，與UNIX_TIMESTAMP互為反函式
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
-- UTC_DATE()返回UTC日期函式，用於返回當前UTC(世界標準時間)的日期值。由於時差關係，UTC不一定是當前計算機系統顯示的日期值。
SELECT UTC_DATE();
-- UTC_TIME()返回UTC時間函式，用於返回當前UTC(世界標準時間)的時間值。由於時差關係，UTC不一定是當前計算機系統顯示的時間值。
SELECT UTC_TIME();
-- MONTH(date) MONTHNAME(date)獲取日期引數date中的月份的函式，MONTH(date)返回數值型別，MONTHNAME(date返回字串型別
SELECT MONTH(CURRENT_DATE()),MONTHNAME(CURRENT_DATE());
-- DAYNAME(date)獲取星期的函式，返回日期date對應的星期幾的英文名稱
SELECT DAYNAME(CURRENT_DATE());
-- DAYOFWEEK(date)獲取星期的函式，返回日期date對應的一週的索引位置值
SELECT DAYOFWEEK(CURRENT_DATE());
-- WEEK(date)獲取星期的函式，返回日期date對應的工作日索引
SELECT WEEK(CURRENT_DATE());
-- WEEK(date,mode)獲取星期數的函式，返回日期引數date在一年中位於第幾周。該函式允許指定星期是否起始於週日或者週一，以及返回值的範圍是否為0-53或1到53，如果mode引數別省略，
-- 則使用系統自變數default_week_format,預設情況下，default_week_format的預設值為0
SELECT WEEK(CURRENT_DATE(),1),WEEK(CURRENT_DATE(),2);
-- WEEKOFYEAR(date)計算日期引數date是一年中的第幾個星期，範圍是1-53，相當於WEEK(date,53)
SELECT WEEKOFYEAR(CURRENT_DATE());
-- DAYOFYEAR(date)獲取天數的函式，返回日期引數date是一年中的第幾天，範圍是1-366
SELECT DAYOFYEAR(CURRENT_DATE());
-- DAYOFMONTH(date)獲取天數的函式，返回日期引數date是一個月中的第幾天，範圍是1-31
SELECT DAYOFMONTH(CURRENT_DATE());
-- YEAR(date)獲取年份的函式，返回日期引數date對應的年份，範圍是1970-2069
SELECT YEAR(CURRENT_DATE());
-- QUARTER(date)返回日期引數對應一年中的季度值，範圍1-4
SELECT QUARTER(CURRENT_DATE());
-- MINUTE(time)返回時間引數對應的分鐘數，範圍0-59
SELECT MINUTE(CURRENT_TIME());
-- SECOND(time)返回時間引數對應的秒數，範圍0-59
SELECT SECOND(CURRENT_TIME);
-- EXTRACT(unit FROM date)獲取日期時間引數對應的指定型別的函式
SELECT EXTRACT(YEAR FROM CURRENT_DATE),EXTRACT(MONTH FROM CURRENT_DATE);
-- TIME_TO_SEC(time)時間和秒數轉換的函式，返回將時間引數time轉換為秒數的時間值
SELECT TIME_TO_SEC(CURRENT_TIME);
-- SEC_TO_TIME(seconds)秒數和時間轉換的函式，返回將seconds轉換為小時、分鐘和秒數的時間值
SELECT SEC_TO_TIME(6400);
-- DATE_ADD(date,INTERVAL expr unit) ADDDATE(date,INTERVAL expr unit)加法計算日期函式，返回一個以引數date為起始日期加上時間間隔值之後的日期值，
-- 其中expr是一個字串，可以是以負號開頭的負值時間間隔，type指出了expr被解釋的方式
SELECT DATE_ADD(CURRENT_DATE,INTERVAL 7 DAY);
-- DATE_SUB(date,INTERVAL expr unit) SUBDATE(date,INTERVAL expr unit)減法計算日期函式，返回一個以引數date為起始日期減去時間間隔值
-- 之後的日期值
SELECT DATE_SUB(CURRENT_DATE,INTERVAL 7 DAY);
-- ADDTIME(time,expr)加法計算時間值函式，返回將expr值加上原始時間time之後的值
SELECT ADDTIME(CURRENT_TIME,'00:30:00');
-- SUBTIME(time,expr2)減法計算時間值函式，返回將原始時間time減去expr值之後的值
SELECT SUBTIME(CURRENT_TIME,'00:30:00');
-- DATEDIFF(expr1,expr2)計算兩個日期時間間隔的函式，返回引數expr1減去expr2之後的值
SELECT DATEDIFF(DATE_ADD(CURRENT_DATE,INTERVAL 7 DAY),CURRENT_DATE);
-- DATE_FORMAT(date,format)將日期和時間格式化的函式，返回根據引數format指定的格式顯示的date值
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
-- TIME_FORMAT(time,format)將時間格式化的函式
SELECT TIME_FORMAT(CURRENT_TIME,'%H:%i:%s')
-- GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})
-- 獲得國家地區時間格式函式，返回值是一個格式字串,傳參選項從上面選擇
SELECT GET_FORMAT(DATE,'USA');
SELECT GET_FORMAT(DATETIME,'JIS');

條件判斷函式

-- IF(expr1,expr2,expr3)返回表示式expr1得到不用運算結果時對應的值。若expr是TRUE,則返回expr2,否則返回expr3
SELECT IF(1>=0,1,0);
-- IFNULL(expr1,expr2)返回引數expr1或expr2的值，如果expr1不為NULL,則返回expr1,否則返回expr2
SELECT IFNULL(NULL,'AAA'),IFNULL('BBB','CCC');
-- CASE 函式，根據expr的取值返回相應值
SELECT
CASE
WEEKDAY(
NOW())
WHEN 0 THEN
'星期一'
WHEN 1 THEN
'星期二'
WHEN 2 THEN
'星期三'
WHEN 3 THEN
'星期四'
WHEN 4 THEN
'星期五'
WHEN 5 THEN
'星期六' ELSE '星期天'
END AS column1;

系統資訊函式

-- VERSION()返回當前Mysql版本號的字串
SELECT VERSION();
-- CONNECTION_ID()返回mysql伺服器當前使用者的連線次數
SELECT CONNECTION_ID();
-- PROCESSLIST 使用"SHOW PROCESSLIST"顯示正在執行的執行緒，不僅可以檢視當前所有的連線數，還可以檢視當前的連線狀態，幫助使用者
-- 識別出有問題的查詢語句等。如果是root賬戶，能看到所有使用者的當前連線，如果是普通賬號，只能看到自己佔用的連線
SHOW PROCESSLIST;
-- DATABASE() SCHEMA()顯示目前正在使用的資料庫名稱
SELECT DATABASE(),SCHEMA();
-- USER() CURRENT_USER() SYSTEM_USER() SESSION_USER()都是獲取當前登入使用者名稱的函式。
SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();
-- CHARSET(str)獲取字串的字符集函式，返回引數字串str使用的字符集
SELECT CHARSET('AAA');
-- COLLATION(str)返回引數字串str的排列方式
SELECT COLLATION('ABCD');
-- LAST_INSERT_ID()獲取最後一個自動生成的ID值的函式，將自動返回最後一個INSERT或UPDATE為AUTO_INCREMENT列設定的第一個發生的值
SELECT LAST_INSERT_ID();

資料加密與解密函式

-- PASSWORD(str)加密函式，該函式計算原明文密碼str，並返回加密後的密碼字串
-- PASSWORD()在mysql 8.0版本已棄用
SELECT PASSWORD('AAA');
-- MD5(str)加密函式，該函式為引數字串str計算出一個MD5128位校驗和，該值以32位十六進位制數字的二進位制字串形式返回
SELECT MD5('BADAO');
-- ENCODE(str,pass_str)加密函式，該函式使用引數pass_str作為金鑰，加密引數str
-- 在mysql 8.0版本已棄用
SELECT ENCODE('AAA','BADAO');
-- 在mysql 8.0版本已棄用
-- DECODE(crypt_str,pass_str)解密函式，該函式使用引數pass_str作為金鑰，解密引數加密字串crypt_str

其它函式

-- FORMAT(X,D)格式化函式，該函式將數值引數x格式化，並以四捨五入的方式保留小數點後d位，結果以字串形式返回
SELECT FORMAT('2.123456',2);
-- CONV(N,from_base,to_base)不用進位制的數字進行轉換的函式，該函式將數字n從form_base轉換到to_base,並以字串形式返回。
-- 其中，引數n被解釋為一個整數，但是也可以被指定為一個字串。其最小基為2，最大為36
SELECT CONV(10,10,2);
-- INET_ATON(expr)IP地址與數字相互轉換的函式，該函式將引數expr(作為字串的網路地址的點地址)轉換成一個代表該地址數值的整數
-- 數字網路地址可以是4位或8位
SELECT INET_ATON('192.168.1.1');
-- INET_NTOA(expr)數字網路地址轉換成字串網路地址函式，該函式將引數expr(數字網路地址，4位或8位)轉換成字串型別的該地址的點地址表示
SELECT INET_NTOA('3232235777');
-- GET_LOCK(str,timeout)加鎖函式，該函式使用引數字串str給定的名字得到一個鎖，超時時間位timeout秒。若成功得到鎖，返回1；若超時操作，返回0；若發生錯誤，返回NULL
SELECT GET_LOCK('BADAO',3);
-- RELEASE_LOCK(str)該函式解開被GET_LOCK()獲取的用字串str命名的鎖。若鎖被解開，返回1；若該執行緒尚未建立鎖，返回0（此時鎖沒有被解開）
-- 若命名的鎖不存在，返回NULL
SELECT RELEASE_LOCK('BADAO');
-- IS_FREE_LOCK(str)該函式檢查名為str的鎖是否可以使用(也就是說沒有被鎖)。若鎖可以用，返回1；若鎖正在被使用，返回0；如出現引數錯誤，返回NULL
SELECT IS_FREE_LOCK('BADAO');
-- IS_USED_LOCK(str)該函式檢查名為str的鎖是否正在被使用(也就是說被鎖)。若鎖正在被鎖，返回使用該鎖的客戶端的連線識別符號，否則返回NULL
SELECT IS_USED_LOCK('BADAO');
-- BENCHMARK(count,expr)重複執行指定操作的函式，該函式重複count次執行表示式expr。該函式可以用於計算Mysql處理表示式的速度，
-- 結果值通常為0（0只是表示處理過程很快，並不是沒有花費時間）。該函式的另一個作用是在Mysql客戶端內部報告語句執行的時間。
SELECT BENCHMARK(1000000,1+1);
-- CONVERT(expr USING transcoding_name)改變字符集函式，該函式可以改變字串預設的字符集
SELECT CHARSET('BADAO');
SELECT CHARSET(CONVERT('BADAO' USING GBK));
-- CAST(expr AS type) CONVERT(expr,type)改變資料型別的函式，將引數expr由一個型別轉換為另外一個型別
SELECT CAST(3.14 AS SIGNED);