MySQL資料庫學習筆記（五）----MySQL字串函數、日期時間函數

標籤：style   blog   http   color   os   ar   使用   for   strong   

一、常見字串函數：

• 1、CHAR_LENGTH  擷取長度（字元為單位）
• 2、FORMAT  格式化
• 3、INSERT  替換的方式插入
• 4、INSTR  擷取位置
• 5、LEFT/RIGHT  取左、取右
• 6、LENGTH   擷取長度（位元組為單位）
• 7、LTRIM/RTRIM/TRIM 去空格（左/右/自訂）
• 8、STRCMP  字串比較
• 9、CONCAT  字串拼接
• 10、SUBSTRING  字串截取

1、CHAR_LENGTH：擷取長度（字元為單位）

CHAR_LENGTH(str)

 

解釋：傳回值為字串str的長度，計算的單位為字元（一個中文也算一個字元）

舉例：

如果是查詢已知表格裡的欄位長度，可以這樣寫：

2、FORMAT：

FORMAT(X,D)

 

解釋：將數字X的格式寫為‘#,###,###.##‘,以四捨五入的方式保留小數點後D位，並將結果以字串的形式返回。若D為0, 則返回結果不帶有小數點，或不含小數部分。

舉例：

3、INSERT：替換的方式插入

INSERT (str,pos,len,newstr)

 

解釋：返回字串str,從原str的pos位置開始，用字串newstr來替換，替換的長度為len。

如果pos超過字串長度，則傳回值為原始字串。假如len的長度超過後面字串的長度，則從位置pos開始替換到字串的結尾即可。

四個參數中若任何一個參數為的值為null，則整個函數的傳回值為NULL。

舉例：將smyh001替換為smyh002

4、INSTR：尋找子字串的位置

INSTR(str,substr)

 

解釋：返回字串str中首次出現子字串substr的位置。

舉例：

5、LEFT/RIGHT：字串截取

• 左截取：

LEFT(str,len)

 

解釋：返回字串str中，從左邊開始算起的，前len個字元。

• 右截取：

RIGHT(str,len)

 

解釋：返回字串str中，從右邊開始算起的，前len個字元。

6、LENGTH：求字串長度（以位元組為單位）

LENGTH(str)

 

解釋：傳回值為字串str的長度，單位為位元組。一個多位元組字元算作多位元組。這意味著對於一個包含五個2位元組字元的字串，LENGTH()的傳回值為10,而CHAR_LENGTH()的傳回值則為5。

註：在UTF-8編碼中，一個英文字元是兩個位元組，一個中文字元是三個位元組。

7、LTRIM/RTRIM/TRIM 去空格（左/右/自訂）：

• 去掉左邊的空格：

LTRIM(str)

 

解釋：返回字串str，其引導空白字元被刪除。

• 去掉中間的空格：

RTRIM(str)

 

解釋：返回字串str，其結尾空白字元被刪除。

• 自訂去空格：

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) 

 

註：大括弧和中括弧裡面的參數為可選項，全部都加上的話，預設為參數both。

解釋：返回字串str，去掉空格。只去開頭（leading）的空格，只去結尾(trailng)的空格，或者同時去掉開頭和結尾的空格都行。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的,則假設為BOTH。

TRIM([remstr FROM] str)

解釋：remstr關鍵字的作用是去掉指定字元。 即所有包含remstr的首碼和/或尾碼都將被刪除。未指定情況下，即刪除空格。

官方文檔的舉例：

8、STRCMP：

STRCMP(expr1,expr2)

解釋：判斷兩個字串是否相等。若所有的字串均相同，則返回0；若根據當前分類次序，第一個參數小於第二個，則返回-1；其它情況返回1。和比較子類似。

註：字串的大小是按照ANSI編碼來比較的。

舉例：

9、CONCAT：字串拼接

CONCAT(str1,str2,...)

返回結果為連將str1、str2···拼接之後產生的字串。如有任何一個參數為NULL ，則傳回值為 NULL。可能有一個或多個參數。 

如果所有參數均為非二進位字串，則結果為非二進位字串。如果自變數中含有任意一個二進位字串，則結果為一個二進位字串。一個數字參數將自動轉化為與之相等的二進位字串格式。

若要避免這種情況二進位的情況出現，可使用顯式類型 cast(str),將二進位的字串轉換為非二進位字串。例如：SELECT CONCAT(CAST(int_col AS CHAR), char_col)

舉例：（navicat編輯器會自動將二進位字串轉換為非二進位字串）

10、SUBSTRING：字串截取

SUBSTRING(str,pos)

SUBSTRING(str FROM pos)

SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len) 

不帶有len 參數的格式從字串str返回一個子字串，起始於位置 pos。帶有len參數的格式從字串str返回一個長度同len字元相同的子字串，起始於位置 pos。

使用FROM的格式為標準SQL文法。

也可能對pos使用一個負值。假若這樣，則子字串的位置起始於字串結尾的pos字元（也就是說，pos為正時，從左邊算起；pos為負時，從右邊算起）。 

官方文檔舉例如下：

二、常見日期時間函數：

• 1、DAYOFWEEK、 DAYOFYEAR、DAYOFMONTH
• 2、WEEKDAY
• 3、YEAR、QUARTER、MONTH、
• 4、HOUR、MINUTE、SECOND
• 5、TO DAYS、FROM DAYS    
• 6、CURDATE、CURTIME、NOW   擷取目前時間

1、DAYOFWEEK、 DAYOFYEAR、DAYOFMONTH

• 求星期幾：（一周中的第幾天）

DAYOFWEEK(date)

解釋：返回date(1=周日,2=周一, ...,7=周六)對應的工作日索引。即：date對應的範圍是1至7。

• 求一年中的某天是第幾天：（一年中的第幾天）

DAYOFYEAR(date)

返回date對應的一年中的天數，範圍是從1到366。

• 求一個月中的某天是第幾天：（一個月的第幾天）

DAYOFMONTH(date)

返回date對應的該月日期，範圍是從1到31。註：DAY(date) 和DAYOFMONTH(date)的含義相同。

舉例：

2、WEEKDAY：

• 求星期幾：（一周的第幾天）

WEEKDAY(date)

返回date(0 = 周一, 1 = 周二, ... 6 = 周日)對應的工作日索引。即：date對應的範圍是0至6。

註：DAYOFWEEK(date)和WEEKDAY(date)的功能相同，唯一的區別在於date對應的取值範圍。

3、 YEAR、QUARTER、MONTH

• 求年份：

YEAR(date)

返回date對應的年份,範圍是從1000到9999。

• 求季度：

QUARTER(date)

返回date對應的一年中的季度值，範圍是從1到4。

• 求月份：（該月是一年當中的第幾個月）

MONTH(date)

返回date 對應的月份，範圍時從1到12。

舉例：

4、HOUR、MINUTE、SECOND

求小時：

HOUR(time)

返回time 對應的小時數。對於日時值的傳回值範圍是從0到23 。

求分鐘：

MINUTE(time)

返回time對應的分鐘數,範圍是從0到59。

求秒數：

SECOND(time)

返回time對應的秒數,範圍是從0到59。

舉例：

5、TO_DAYS、FROM_DAYS

• 求某日期，一共經曆了多少天：（曆史長河中）

TO_DAYS(date)

給定一個日期date, 返回一個天數 (從年份0開始的天數 )。

• 求曆史長河中的第某天，的日期：

FROM_DAYS(N)

給定一個天數 N, 返回一個DATE值。

使用FROM_DAYS()處理古老日期時，務必謹慎，他不用於處理陽曆出現前的天數(1582)。

6、CURDATE、CURTIME、NOW

• 求當前的年月日：

CURDATE()

解釋：將當前日期按照‘YYYY-MM-DD‘或YYYYMMDD格式的值返回，具體格式根據函數用在字串或是數字語境中而定。

舉例：

• 求當前的時分秒：

CURTIME()

將目前時間以‘HH:MM:SS‘或HHMMSS的格式返回，具體格式根據函數用在字串或是數字語境中而定。

舉例：

求當前的年月日、時分秒：

NOW()

舉例：

註：如果now是放在語句當中，要看具體欄位的格式要求。比如說，如果是放在date欄位中，則只顯示年月日；如果是放在datetime欄位中，則同時顯示年月日和時分秒。

MySQL資料庫學習筆記（五）----MySQL字串函數、日期時間函數