測試環境為 CentOS 8 x86_64 虛擬機.
- 字串函數 ( String Functions ) 與 型別轉換 – 這邊介紹.
- 數字函數 (Numeric Functions) – https://benjr.tw/101970
- 日期與時間函數 (Date & Time Functions) – https://benjr.tw/102964
- 資訊函數 (Information Functions) – https://benjr.tw/102961
- 搭配 GROUP BY 函數 (Aggregate Functions) – https://benjr.tw/102967
- 流程控制函數 (Control Flow Functions) – https://benjr.tw/102747
先建立一個測試用資料庫 (testdb) , 與 tables (employee) ,下面範例會使用到 . 格式為 K1 int(11) – auto_increment & PRIMARY KEY , Name char(20), Dept char(20), jobTitle char(20) ,email char(30) 以及 Salary int(11).
SELECT 查詢需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – https://benjr.tw/102156 .
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 10.3.11-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> USE testdb; Database changed MariaDB [testdb]> CREATE TABLE employee (K1 int(11) NOT NULL auto_increment, Name char(20) NOT NULL, Dept char(20) NOT NULL, JobTitle char(20) NOT NULL, Salary int(11) NOT NULL , email char(30) NOT NULL, PRIMARY KEY (K1)) ; Query OK, 0 rows affected (0.010 sec) MariaDB [testdb]> DESCRIBE employee; +----------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+----------------+ | K1 | int(11) | NO | PRI | NULL | auto_increment | | Name | char(20) | NO | | NULL | | | Dept | char(20) | NO | | NULL | | | JobTitle | char(20) | NO | | NULL | | | Salary | int(11) | NO | | NULL | | | email | char(30) | NO | | NULL | | +----------+----------+------+-----+---------+----------------+ 6 rows in set (0.004 sec)
透過 INSERT 新增加了五筆資料.
MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary , email) VALUES ('Ben' , 'Testing' , 'Engineer' , '45000' , 'ben@benjr.tw') , ('Afa' , 'Power' , 'Engineer' , '48000' , 'Afa@benjr.tw') , ('Boss' ,'Testing' , 'Manager' , '75000' , 'Boss@benjr.tw') , ('JoJo' , 'Testing' , 'Engineer' , '45000' , 'jojo@benjr.tw') , ('Ian' , 'PM' , 'Engineer' , '85000' , 'ian@benjr.tw');
Query OK, 5 rows affected (0.002 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [testdb]> SELECT * FROM employee;
+----+------+---------+----------+--------+---------------+
| K1 | Name | Dept | JobTitle | Salary | email |
+----+------+---------+----------+--------+---------------+
| 1 | Ben | Testing | Engineer | 45000 | ben@benjr.tw |
| 2 | Afa | Power | Engineer | 48000 | Afa@benjr.tw |
| 3 | Boss | Testing | Manager | 75000 | Boss@benjr.tw |
| 4 | JoJo | Testing | Engineer | 45000 | jojo@benjr.tw |
| 5 | Ian | PM | Engineer | 85000 | ian@benjr.tw |
+----+------+---------+----------+--------+---------------+
5 rows in set (0.001 sec)
- RIGHT & LEFT
RIGHT (從右邊) 與 LEFT (從左邊) 擷取指定字元數量.MariaDB [(none)]> SELECT RIGHT('MariaDB', 5); +---------------------+ | RIGHT('MariaDB', 5) | +---------------------+ | riaDB | +---------------------+ 1 row in set (0.000 sec)MariaDB [(none)]> SELECT LEFT('MariaDB', 5); +--------------------+ | LEFT('MariaDB', 5) | +--------------------+ | Maria | +--------------------+ 1 row in set (0.000 sec) - POSITION & INSTR
POSITION 與 INSTR 函數可以查看子字串在字串的位置.MariaDB [(none)]> SELECT POSITION("DB" IN "MariaDB"); +-----------------------------+ | POSITION("DB" IN "MariaDB") | +-----------------------------+ | 6 | +-----------------------------+ 1 row in set (0.000 sec)MariaDB [(none)]> SELECT INSTR('MariaDB', 'DB'); +------------------------+ | INSTR('MariaDB', 'DB') | +------------------------+ | 6 | +------------------------+ 1 row in set (0.000 sec) - CONCAT
CONTACT 函數可以將兩個或更多個字串連接在一起.CONCAT(expression1, expression2, expression3,...)
MariaDB [(none)]> SELECT CONCAT("APPLE", ", Banana", ", Cherry") AS Fruit; +-----------------------+ | Fruit | +-----------------------+ | APPLE, Banana, Cherry | +-----------------------+ 1 row in set (0.00 sec)要注意使用 CONTACT 函數中有 NULL 時跟任何字串連接在一起還是 NULL.
MariaDB [(none)]> SELECT CONCAT(NULL, ", Banana", ", Cherry") AS Fruit; +-------+ | Fruit | +-------+ | NULL | +-------+ 1 row in set (0.001 sec)
- CONCAT_WS
相較於 CONTACT 函數 CONCAT_WS 可以加上固定的分隔字元.CONCAT_WS(separator, expression1, expression2, expression3,...)
MariaDB [(none)]> SELECT CONCAT_WS(", ", "APPLE", "Banana", "Cherry") AS Fruit; +-----------------------+ | Fruit | +-----------------------+ | APPLE, Banana, Cherry | +-----------------------+ 1 row in set (0.01 sec) - GROUP_CONCAT
通常搭配 GROUP BY 使用,透過 GROUP_CONCAT 函數,可以把同 Group 的欄位文字整合在一起.GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept; +---------+---------------+ | Dept | Member Name | +---------+---------------+ | PM | Ian | | Power | Afa | | Testing | Ben,Boss,JoJo | +---------+---------------+ 3 rows in set (0.001 sec)
如果 GROUPBY 的資料要按照順序,可以加 ORDER BY DESC 或是 ASC
MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name DESC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept; +---------+---------------+ | Dept | Member Name | +---------+---------------+ | PM | Ian | | Power | Afa | | Testing | JoJo,Boss,Ben | +---------+---------------+ 3 rows in set (0.001 sec) MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name ASC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept; +---------+---------------+ | Dept | Member Name | +---------+---------------+ | PM | Ian | | Power | Afa | | Testing | Ben,Boss,JoJo | +---------+---------------+ 3 rows in set (0.001 sec)
- LOCATE
檢視字串裡面是否有要搜尋的字,substr (搜尋關鍵字), str (字串), pos(從字串的哪一個字元開始搜尋).LOCATE(substr,str), LOCATE(substr,str,pos)
MariaDB [testdb]> SELECT LOCATE('bar', 'foobarbar') AS 'Search Result'; +---------------+ | Search Result | +---------------+ | 4 | +---------------+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT LOCATE('xbar', 'foobar') AS 'Search Result'; +---------------+ | Search Result | +---------------+ | 0 | +---------------+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT LOCATE('bar', 'foobarbar', 5) AS 'Search Result'; +---------------+ | Search Result | +---------------+ | 7 | +---------------+ 1 row in set (0.001 sec) - TRIM
移除特定字串.TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)可以用 BOTH (全部), LEADING (字頭) , TRAILING (字尾) 來決定取代哪邊的字串.
MariaDB [testdb]> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx') AS 'TRIM Result'; +-------------+ | TRIM Result | +-------------+ | barxxx | +-------------+ 1 row in set (0.001 sec) MariaDB [testdb]> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx') AS 'TRIM Result'; +-------------+ | TRIM Result | +-------------+ | bar | +-------------+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT TRIM(TRAILING 'x' FROM 'xxxbarxxx') AS 'TRIM Result'; +-------------+ | TRIM Result | +-------------+ | xxxbar | +-------------+ 1 row in set (0.000 sec)
- REPLACE
用 REPLACE 可以取代字串裡的特定字串.REPLACE(str, find_string, replace_with)
\n 為 Linux 的換行字元.
MariaDB [testdb]> SELECT '123\n456' AS ' TEXT'; +---------+ | TEXT | +---------+ | 123 456 | +---------+ 1 row in set, 1 warning (0.001 sec) MariaDB [testdb]> SELECT REPLACE('123\n456' ,'\n' ,'') AS ' Replace Result'; +----------------+ | Replace Result | +----------------+ | 123456 | +----------------+ 1 row in set, 1 warning (0.000 sec) - REGEXP_REPLACE
可以搭配 REGEXP (正規表示式) 使用的 REPLACE (取代字串裡的特定字串),更多關於 REGEXP_REPLACE 請參考 – https://benjr.tw/102758 .REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
下面範例用來剔除 HTML 語法的標籤(TAG).
MariaDB [(none)]> SELECT REGEXP_REPLACE("<h2 title=I'm a header>The title Attribute</h2>","<.+?>",''); +------------------------------------------------------------------------------+ | REGEXP_REPLACE("<h2 title=I'm a header>The title Attribute</h2>","<.+?>",'') | +------------------------------------------------------------------------------+ | The title Attribute | +------------------------------------------------------------------------------+ 1 row in set (0.004 sec) - FIND_IN_SET
回傳值為 string 位於 string_list (須以 , 分隔)的哪一個位置(不存在時,回傳 0 ).FIND_IN_SET(string, string_list)
MariaDB [testdb]> SELECT FIND_IN_SET('b' , 'a,b,c') AS 'FIND_IN_SET Result'; +--------------------+ | FIND_IN_SET Result | +--------------------+ | 2 | +--------------------+ 1 row in set (0.001 sec) MariaDB [testdb]> SELECT FIND_IN_SET('f' , 'a,b,c') AS 'FIND_IN_SET Result'; +--------------------+ | FIND_IN_SET Result | +--------------------+ | 0 | +--------------------+ 1 row in set (0.001 sec)雖然 FIND_IN_SET 函數可以解決單一欄位儲存多個資料 (以欄位中的 “, comma separated ” 為分隔) 所造成的搜尋問題,但不建議儲存成這樣的欄位內容.使用資料庫正規化 (Database normalization) – https://benjr.tw/101850 重新定義 Table 與其相關性或是使用 JSON 格式來儲存 https://benjr.tw/103200 .
- SUBSTR , SUBSTRING
從一字串取出一段選定的字串,SUBSTR , SUBSTRING 的使用方式一樣.SUBSTR(string, start, length)
OR:
SUBSTR(string FROM start FOR length)
從字串中取出第一個字元到第四個字元.
MariaDB [(none)]> SELECT SUBSTR("This is a Book", 1, 4) AS ExtractString; +---------------+ | ExtractString | +---------------+ | This | +---------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT SUBSTRING("This is a Book", 1, 4) AS ExtractString; +---------------+ | ExtractString | +---------------+ | This | +---------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT SUBSTR("This is a Book" FROM 1 FOR 4) AS ExtractString; +---------------+ | ExtractString | +---------------+ | This | +---------------+ 1 row in set (0.000 sec)字元起始位置也可以從後面算起,-4 代俵從後面算起來第 4 個字元,並取出 4 個字元.
MariaDB [(none)]> SELECT SUBSTR("This is a Book",-4, 4) AS ExtractString; +---------------+ | ExtractString | +---------------+ | Book | +---------------+ 1 row in set (0.001 sec) - SUBSTRING_INDEX
依據自定 delimiter (分隔符號) 來從一字串中取出一段選定的字串.SUBSTRING_INDEX(string, delimiter, number)
以空格作區分,從字串中取第兩個空格字串前的字串.
MariaDB [(none)]> SELECT SUBSTRING_INDEX("This is a Book"," ", 2) AS ExtractString; +---------------+ | ExtractString | +---------------+ | This is | +---------------+ 1 row in set (0.000 sec)– (負數)代表從後面算起,-2 代表字串從後面算起取第兩個空格字串後的字串.
MariaDB [(none)]> SELECT SUBSTRING_INDEX("This is a Book"," ", -2) AS ExtractString; +---------------+ | ExtractString | +---------------+ | a Book | +---------------+ 1 row in set (0.001 sec) - STR_TO_DATE()
mysql 標準 timestamp 格式為 yyyy-mm-dd hh:mm:ss (24 小時制),如果從程式讀取的格式不同就可以透過這個函數作轉換.STR_TO_DATE(string, format)
MariaDB [testdb]> SELECT STR_TO_DATE("2/25/2020 11:13:03 PM", "%m/%d/%Y %h:%i:%s %p") AS TimeStamp; +---------------------+ | TimeStamp | +---------------------+ | 2020-02-25 23:13:03 | +---------------------+ 1 row in set (0.000 sec)更多參數請參考 – https://mariadb.com/kb/en/str_to_date/
- %m : Month with 2 digits.
- %d : Day with 2 digits.
- %Y : Year with 4 digits.
- %h : Hour with 2 digits between 01-12.
- %i : Minute with 2 digits.
- %s : Seconds with 2 digits.
- %p : AM/PM according to current locale.
- CHAR_LENGTH , LENGTH
計算字元有幾個.MariaDB [(none)]> SELECT CHAR_LENGTH('ABC'); +--------------------+ | CHAR_LENGTH('ABC') | +--------------------+ | 3 | +--------------------+ 1 row in set (0.000 sec)MariaDB [(none)]> SELECT LENGTH('ABC'); +---------------+ | LENGTH('ABC') | +---------------+ | 3 | +---------------+ 1 row in set (0.000 sec) - QUOTE 函數
QUOTE 函數有以下功能.- 帶有單引號 ‘ , 反斜線 \ ,ASCII NUL 以及 Control-Z 帶有反斜線.
- 如果值是 NULL,則傳回為字串 “NULL”
在 SQL 的字串不能使用 ” (Double Quote) 或是 ‘ (Single Quote) 來表示,但可以使用 \ 反斜線 (Backslash Character) 這個跳脫字元來表示 (” 需用 \” , ‘ 需用 \’) – https://benjr.tw/102928 ,使用 QUOTE 函數可以還原原本資料.
MariaDB [testdb]> SELECT 'I\'m'; +-----+ | I'm | +-----+ | I'm | +-----+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT QUOTE('I\'m'); +---------------+ | QUOTE('I\'m') | +---------------+ | 'I\'m' | +---------------+ 1 row in set (0.000 sec)傳回為字串 “NULL” .
MariaDB [testdb]> SELECT QUOTE(NULL); +-------------+ | QUOTE(NULL) | +-------------+ | NULL | +-------------+ 1 row in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容