MySQL講義第 38 講——select 查詢之函式（1）：字串函式
使用函式可在查詢時構造更加靈活的查詢條件，MySQL 提供了處理字串的函式，下面對字串函式進行詳細的介紹并舉例說明其用法，在當前資料庫中創建一個 student 表，表結構及表中的資料如下：
mysql> desc student;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| s_id | char(5) | NO | PRI | NULL | |
| s_name | char(20) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| addr | varchar(100) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.03 sec)
mysql> select * from student;
+-------+--------------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+--------------+---------------------+-------------+-----------+
| s1801 | 劉蓓 | 2000-03-08 00:00:00 | 15903735566 | Kaifeng |
| s1802 | 趙蕓 | 2000-04-27 00:00:00 | 15903735533 | Zheng |
| s1803 | 張毅得 | 1999-11-20 00:00:00 | 15903735544 | Xinxiang |
| s1804 | 曹夢德 | 1999-08-07 00:00:00 | 15903735577 | Zheng |
| s1805 | 孫泉 | 2000-10-25 00:00:00 | 15903735587 | Xinyang |
| s1806 | 司馬意 | 2000-10-24 00:00:00 | 15903735581 | Xinxiang |
| s1807 | 司馬招 | 2000-08-08 00:00:00 | 15903734444 | Anyang |
| s1808 | 趙紫龍 | 2001-06-26 00:00:00 | 15903736666 | Zhumadian |
| s1809 | 呂步 | 2001-04-30 00:00:00 | 15903737777 | Nanyang |
| s1810 | 劉珊 | 2002-10-26 00:00:00 | 15903732222 | Zhumadian |
| s1811 | 諸葛亮 | 2001-11-24 00:00:00 | 15903731111 | Zhumadian |
| s1901 | 諸葛英 | 2001-08-09 00:00:00 | 15903731234 | Xinxiang |
| s1902 | 司馬相如 | 2001-12-08 00:00:00 | 15903731144 | Nanyang |
| s1903 | 王召軍 | 2002-10-23 00:00:00 | 15903731199 | Kaifeng |
| s1904 | 康西 | 2001-10-26 00:00:00 | 15903731177 | Kaifeng |
| s1905 | 乾龍 | 2000-10-27 00:00:00 | 15903732211 | Kaifeng |
| s1906 | 朱元張 | 2000-08-07 00:00:00 | 15903732233 | Kaifeng |
| s1907 | 李氏敏 | 2001-10-23 00:00:00 | 15903732244 | Zhumadian |
| s1908 | 趙匡銀 | 2001-10-24 00:00:00 | 15903733322 | Xinyang |
| s1909 | 趙夠 | 2001-10-25 00:00:00 | 15903733366 | Anyang |
| s2011 | 王保墻 | 2001-10-22 00:00:00 | 15903733355 | Xinxiang |
| s2012 | 李曉露 | 2001-11-27 00:00:00 | 15903731441 | Zhumadian |
| s2013 | 賈大空 | 2002-03-08 00:00:00 | 15903734422 | Xinxiang |
| s2014 | 孫悟空 | 2002-10-25 00:00:00 | 15903734466 | Anyang |
| s2015 | 豬八戒 | 2001-10-26 00:00:00 | 15903734477 | Xinxiang |
| s2016 | 沙和尚 | 2002-10-24 00:00:00 | 15903736611 | Anyang |
| s2017 | 唐三藏 | 2001-09-08 00:00:00 | 15903736655 | Nanyang |
| s2018 | 白龍馬 | 2001-12-05 00:00:00 | 15903736633 | Nanyang |
| s2019 | 如來 | 2002-01-27 00:00:00 | 15903736699 | Nanyang |
| s2020 | 觀音 | 2002-02-23 00:00:00 | 15903737744 | Zhumadian |
+-------+--------------+---------------------+-------------+-----------+
30 rows in set (0.23 sec)
1、ASCII() 函式
ASCII() 函式回傳一個字符的 ASCII 碼，如果引數為字串，則回傳第一個字符的 ASCII 碼，語法如下：
ASCII(str);
--說明：
（1）函式的引數為字串，
（2）回傳字串中第一個字符的 ASCII 碼，
例如：
mysql> SELECT
-> ASCII('abc');
+--------------+
| ASCII('abc') |
+--------------+
| 97 |
+--------------+
1 row in set (0.02 sec)
mysql> SELECT
-> ASCII(NULL);
+-------------+
| ASCII(NULL) |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT
-> ASCII('');
+-----------+
| ASCII('') |
+-----------+
| 0 |
+-----------+
1 row in set (0.01 sec)
2、BIN() 函式
BIN() 函式把一個整數轉化為二進制數，回傳結果的型別為字串，語法如下：
BIN(n);
--說明：
（1）引數為一個整數，如果引數為小數，則取整后再進行處理，
（2）把數值 n 轉化為二進制形式，回傳值是一個由 0 和 1 組成的字串，
例如：
mysql> SELECT
-> BIN(12.85);
+------------+
| BIN(12.85) |
+------------+
| 1100 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT
-> BIN(1023);
+------------+
| BIN(1023) |
+------------+
| 1111111111 |
+------------+
1 row in set (0.00 sec)
3、CHAR() 函式
CHAR() 函式把多個 ASCII 碼轉換為對應的字符，并連接成一個字串，語法如下：
CHAR(n1, n2, ...);
說明：
（1）引數為若干個表示 ASCII 碼的整數，
（2）回傳由 n1，n2，… 的 ASCII 碼對應的字符組成的字串，
例如：
mysql> SELECT
-> CHAR(97,98,99,65,66,67);
+-------------------------+
| CHAR(97,98,99,65,66,67) |
+-------------------------+
| abcABC |
+-------------------------+
1 row in set (0.00 sec)
4、CHAR_LENGTH() 函式
CHAR_LENGTH() 函式回傳一個字串的字符個數，語法如下：
CHAR_LENGTH(str);
--說明：
（1）函式的引數為一個字串，
（2）回傳字串中包含字符的個數，包括半角字符和全角字符，
例如：
mysql> SELECT
-> CHAR_LENGTH('ABC0123');
+------------------------+
| CHAR_LENGTH('ABC0123') |
+------------------------+
| 7 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT
-> CHAR_LENGTH('化工路258號');
+--------------------------------+
| CHAR_LENGTH('化工路258號') |
+--------------------------------+
| 7 |
+--------------------------------+
1 row in set (0.00 sec)
5、LENGTH() 函式
LENGTH() 函式回傳一個字串的長度，用位元組表示，語法如下：
LENGTH(str);
--說明：
（1）函式的引數為一個字串，
（2）回傳字串的長度（單位為位元組），
（3）多位元組字符的長度取決于所用的字符集，比如 utf8 字符集一個漢字為 3 個位元組，而 latin1 字符集一個漢字為 2 位元組，
例如：
mysql> SHOW VARIABLES LIKE '%CHARACT%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.09 sec)
mysql> SELECT
-> LENGTH('ABC0123');
+-------------------+
| LENGTH('ABC0123') |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT
-> LENGTH('河南省');
+---------------------+
| LENGTH('河南省') |
+---------------------+
| 9 |
+---------------------+
1 row in set (0.00 sec)
6、CONCAT() 函式
CONCAT() 函式用于字串的連接，語法如下：
CONCAT(str1,str2,...);
--說明：
（1）函式的引數為若干個字串，
（2）把引數中包含的字串連接成一個字串，然后回傳，
例如：
mysql> SELECT
-> s_id,s_name,concat(addr,', ',phone) AS contact
-> FROM
-> student
-> WHERE
-> s_id LIKE 's18%';
+-------+-----------+------------------------+
| s_id | s_name | contact |
+-------+-----------+------------------------+
| s1801 | 劉蓓 | Kaifeng, 15903735566 |
| s1802 | 趙蕓 | Zheng, 15903735533 |
| s1803 | 張毅得 | Xinxiang, 15903735544 |
| s1804 | 曹夢德 | Zheng, 15903735577 |
| s1805 | 孫泉 | Xinyang, 15903735587 |
| s1806 | 司馬意 | Xinxiang, 15903735581 |
| s1807 | 司馬招 | Anyang, 15903734444 |
| s1808 | 趙紫龍 | Zhumadian, 15903736666 |
| s1809 | 呂步 | Nanyang, 15903737777 |
| s1810 | 劉珊 | Zhumadian, 15903732222 |
| s1811 | 諸葛亮 | Zhumadian, 15903731111 |
+-------+-----------+------------------------+
11 rows in set (0.00 sec)
7、CONCAT_WS() 函式
CONCAT_WS() 函式用于多個字串的連接，可以指定連接字串時的分隔符，語法如下：
CONCAT_WS(separator,st1,st2,...);
--說明：
（1）第一個引數為連接字串時各字串之間的分隔符
（2）把引數中包含的字串連接成一個字串，并且用一個分隔符隔開，
例如：
mysql> SELECT
-> s_id,s_name,CONCAT_WS(',',addr,phone) AS contact
-> FROM
-> student
-> WHERE
-> s_id LIKE 's18%';
+-------+-----------+-----------------------+
| s_id | s_name | contact |
+-------+-----------+-----------------------+
| s1801 | 劉蓓 | Kaifeng,15903735566 |
| s1802 | 趙蕓 | Zheng,15903735533 |
| s1803 | 張毅得 | Xinxiang,15903735544 |
| s1804 | 曹夢德 | Zheng,15903735577 |
| s1805 | 孫泉 | Xinyang,15903735587 |
| s1806 | 司馬意 | Xinxiang,15903735581 |
| s1807 | 司馬招 | Anyang,15903734444 |
| s1808 | 趙紫龍 | Zhumadian,15903736666 |
| s1809 | 呂步 | Nanyang,15903737777 |
| s1810 | 劉珊 | Zhumadian,15903732222 |
| s1811 | 諸葛亮 | Zhumadian,15903731111 |
+-------+-----------+-----------------------+
11 rows in set (0.00 sec)
8、GROUP_CONCAT() 函式
GROUP_CONCAT() 函式用于把一個欄位中的所有資料進行連接，可以在分組查詢時使用，語法如下：
GROUP_CONCAT([distinct] fieldname [order by 排序欄位 asc|desc] separator '分隔符');
--說明：
（1）該函式對表中的一個欄位進行處理，把該欄位的所有資料連接起來，
（2）該函式一般用于分組查詢，其用法類似于聚合函式，
（3）把欄位的所有資料使用 separator 指定的分隔符連接起來，如果省略 separator，則默認的分隔符為逗號，
（4）distinct：取出重復的資料，
（5）order by：按表中的某個欄位排序之后再進行連接，
例如：
（1）對 student 表按 addr 進行分組，使用 GROUP_CONCAT 連接 s_name
mysql> SELECT
-> addr,GROUP_CONCAT(s_name)
-> FROM
-> student
-> GROUP BY
-> addr;
+-----------+-------------------------------------------------------------+
| addr | GROUP_CONCAT(s_name) |
+-----------+-------------------------------------------------------------+
| Anyang | 孫悟空,趙夠,司馬招,沙和尚 |
| Kaifeng | 劉蓓,朱元張,乾龍,康西,王召軍 |
| Nanyang | 唐三藏,呂步,司馬相如,白龍馬,如來 |
| Xinxiang | 豬八戒,王保墻,賈大空,諸葛英,司馬意,張毅得 |
| Xinyang | 趙匡銀,孫泉 |
| Zheng | 趙蕓,曹夢德 |
| Zhumadian | 李曉露,李氏敏,諸葛亮,劉珊,趙紫龍,觀音 |
+-----------+-------------------------------------------------------------+
7 rows in set (0.00 sec)
（2）按出生日期排序再連接 s_name
mysql> SELECT
-> addr,GROUP_CONCAT(s_name ORDER BY birth)
-> FROM
-> student
-> GROUP BY
-> addr;
+-----------+-------------------------------------------------------------+
| addr | GROUP_CONCAT(s_name ORDER BY birth) |
+-----------+-------------------------------------------------------------+
| Anyang | 司馬招,趙夠,沙和尚,孫悟空 |
| Kaifeng | 劉蓓,朱元張,乾龍,康西,王召軍 |
| Nanyang | 呂步,唐三藏,白龍馬,司馬相如,如來 |
| Xinxiang | 張毅得,司馬意,諸葛英,王保墻,豬八戒,賈大空 |
| Xinyang | 孫泉,趙匡銀 |
| Zheng | 曹夢德,趙蕓 |
| Zhumadian | 趙紫龍,李氏敏,諸葛亮,李曉露,觀音,劉珊 |
+-----------+-------------------------------------------------------------+
7 rows in set (0.00 sec)
9、FORMAT() 函式
FORMAT() 函式用于把某個數值進行四舍五入后回傳，回傳結果的型別為字串，語法如下：
FORMAT(x,d);
--說明：
（1）對數值 x 進行四舍五入運算，保留 d 為小數，回傳值為一個字串，
（2）該函式的功能和 ROUND() 函式類似，回傳值的型別不同，
例如：
mysql> SELECT
-> FORMAT(125.487,1);
+-------------------+
| FORMAT(125.487,1) |
+-------------------+
| 125.5 |
+-------------------+
1 row in set (0.00 sec)
10、INSERT() 和 REPLACE() 函式
INSERT() 和 REPLACE() 函式用于把一個字串中的某些字符替換為另外的字符，語法如下：
INSERT(str,pos,len,instr);
REPLACE(str1,str2,str3);
--說明：
（1）INSERT() 函式：把字串 str 從 pos 位置開始的 len 個字符替換為 instr 字串，并回傳替換之后的字串，
（2）如果 pos 超過字串長度，則不進行任何替換，直接回傳原字串，
（3）REPLACE() 函式：把 str1 中的 str2 子串替換為 str3 子串，
（4）如果要替換的字串的位置確定，內容不知道，使用 INSERT() 函式，
（5）如果要替換的字串的內容確定，位置不確定，使用 REPLACE() 函式，
例如：
mysql> SELECT
-> s_id,s_name,INSERT(phone,4,4,'****')
-> FROM
-> student
-> WHERE
-> s_id LIKE 's18%';
+-------+-----------+--------------------------+
| s_id | s_name | INSERT(phone,4,4,'****') |
+-------+-----------+--------------------------+
| s1801 | 劉蓓 | 159****5566 |
| s1802 | 趙蕓 | 159****5533 |
| s1803 | 張毅得 | 159****5544 |
| s1804 | 曹夢德 | 159****5577 |
| s1805 | 孫泉 | 159****5587 |
| s1806 | 司馬意 | 159****5581 |
| s1807 | 司馬招 | 159****4444 |
| s1808 | 趙紫龍 | 159****6666 |
| s1809 | 呂步 | 159****7777 |
| s1810 | 劉珊 | 159****2222 |
| s1811 | 諸葛亮 | 159****1111 |
+-------+-----------+--------------------------+
11 rows in set (0.00 sec)
mysql> SELECT
-> s_id,s_name,REPLACE(phone,'159','136')
-> FROM
-> student
-> WHERE
-> s_id LIKE 's18%';
+-------+-----------+----------------------------+
| s_id | s_name | REPLACE(phone,'159','136') |
+-------+-----------+----------------------------+
| s1801 | 劉蓓 | 13603735566 |
| s1802 | 趙蕓 | 13603735533 |
| s1803 | 張毅得 | 13603735544 |
| s1804 | 曹夢德 | 13603735577 |
| s1805 | 孫泉 | 13603735587 |
| s1806 | 司馬意 | 13603735581 |
| s1807 | 司馬招 | 13603734444 |
| s1808 | 趙紫龍 | 13603736666 |
| s1809 | 呂步 | 13603737777 |
| s1810 | 劉珊 | 13603732222 |
| s1811 | 諸葛亮 | 13603731111 |
+-------+-----------+----------------------------+
11 rows in set (0.00 sec)
11、大小寫轉換函式
MySQL提供了四個大小寫轉換函式，語法如下：
LCASE(str);
UCASE(str);
LOWER(str);
UPPER(str);
--說明：
（1）UCASE 和 UPPER 可以把字串中的字母轉換為大寫字母，
（2）LCASE 和 LOWER 可以把字串中的字母轉換為小寫字母，
（3）非字母不轉換，
例如：
mysql> set @str = 'Paypal is a great site and is used by many to send and receive money.';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT
-> LCASE(@str);
+-----------------------------------------------------------------------+
| LCASE(@str) |
+-----------------------------------------------------------------------+
| paypal is a great site and is used by many to send and receive money. |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT
-> UCASE(@str);
+-----------------------------------------------------------------------+
| UCASE(@str) |
+-----------------------------------------------------------------------+
| PAYPAL IS A GREAT SITE AND IS USED BY MANY TO SEND AND RECEIVE MONEY. |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT
-> LOWER(@str);
+-----------------------------------------------------------------------+
| LOWER(@str) |
+-----------------------------------------------------------------------+
| paypal is a great site and is used by many to send and receive money. |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT
-> UPPER(@str);
+-----------------------------------------------------------------------+
| UPPER(@str) |
+-----------------------------------------------------------------------+
| PAYPAL IS A GREAT SITE AND IS USED BY MANY TO SEND AND RECEIVE MONEY. |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
12、截取子字串函式
該類函式可以從一個字串中截取一個字串，一共有三個，語法如下：
LEFT(str,n);
RIGHT(str,n);
SUBSTR(str,m,n);
SUBSTRING(str,m,n);
--說明：
（1）LEFT() 函式是從字串最前端開始截取 n 個字符并回傳，
（2）RIGHT() 函式是從字串最右端截取 n 個字符并回傳，
（3）SUBSTR() 和 SUBSTRING() 兩個函式的功能相同，從第 m 個字符開始，截取 n 個字符并回傳，
例如：
mysql> SELECT
-> company_id,
-> registration_address,
-> LEFT(registration_address,6)
-> FROM
-> company_information
-> WHERE
-> company_id < '000010';
+------------+-------------------------------------------------------------+----------------+|
company_id | registration_address | LEFT(registration_address,6)
|+------------+------------------------------------------------------------+----------------+|
000001 | 廣東省深圳市羅湖區深南東路5047號 | 廣東省深圳市 |
000002 | 廣東省深圳市鹽田區大梅沙環梅路33號萬科中心 | 廣東省深圳市 |
000004 | 廣東省深圳市南山區中心路（深圳灣段）3333號中鐵南方總部大廈503室 | 廣東省深圳市 |
000005 | 廣東省深圳市人民南路發展中心大廈13層 | 廣東省深圳市 |
000006 | 廣東省深圳市羅湖區寶安南路2014號振業大廈B座11-17層 | 廣東省深圳市 |
000007 | 廣東省深圳市華強北路1058號現代之窗大廈A座25層 | 廣東省深圳市 |
000008 | 北京市海淀區高梁橋斜街59號院1號樓16層1606 | 北京市海淀區 |
000009 | 廣東省深圳市筍崗東路1002號寶安廣場A座28-29層 | 廣東省深圳市 |
+------------+-----------------------------------------------------------+--------------------+
8 rows in set (0.00 sec)
mysql> SELECT
-> company_id,
-> web_address,
-> RIGHT(web_address,CHAR_LENGTH(web_address)-4) AS web_address
-> FROM
-> company_information
-> WHERE
-> company_id < '000010';
+------------+-------------------------+---------------------+
| company_id | web_address | web_address |
+------------+-------------------------+---------------------+
| 000001 | www.bank.pingan.com | bank.pingan.com |
| 000002 | www.vanke.com | vanke.com |
| 000004 | www.sz000004.cn | sz000004.cn |
| 000005 | www.fountain.com.cn | fountain.com.cn |
| 000006 | www.zhenye.com | zhenye.com |
| 000007 | None | |
| 000008 | www.shenzhou-gaotie.com | shenzhou-gaotie.com |
| 000009 | www.chinabaoan.com | chinabaoan.com |
+------------+-------------------------+---------------------+
8 rows in set (0.01 sec)
mysql> SELECT
-> company_id,
-> registration_address,
-> SUBSTRING(registration_address,7) AS registration_address
-> FROM
-> company_information
-> WHERE
-> company_id < '000010';
+------------+------------------------------------------------+-------------------------
+| company_id | registration_address | registration_address
|+------------+------------------------------------------------+------------------------
| 000001 | 廣東省深圳市羅湖區深南東路5047號 | 羅湖區深南東路5047號 |
| 000002 | 廣東省深圳市鹽田區大梅沙環梅路33號萬科中心 | 鹽田區大梅沙環梅路33號萬科中心 |
| 000004 | 廣東省深圳市南山區中心路（深圳灣段）3333號中鐵南方總部大廈503室 | 南山區中心路（深圳灣段）3333號中鐵南方總部大廈503室 |
| 000005 | 廣東省深圳市人民南路發展中心大廈13層 | 人民南路發展中心大廈13層 |
| 000006 | 廣東省深圳市羅湖區寶安南路2014號振業大廈B座11-17層| 羅湖區寶安南路2014號振業大廈B座11-17層|
| 000007 | 廣東省深圳市華強北路1058號現代之窗大廈A座25層 | 華強北路1058號現代之窗大廈A座25層 |
| 000008 | 北京市海淀區高梁橋斜街59號院1號樓16層1606 | 高梁橋斜街59號院1號樓16層1606 |
| 000009 | 廣東省深圳市筍崗東路1002號寶安廣場A座28-29層 | 筍崗東路1002號寶安廣場A座28-29層 |
+------------+----------------------------------------------+-----------------------------
8 rows in set (0.00 sec)
mysql> SELECT
-> company_id,
-> registration_address,
-> SUBSTRING(registration_address,4,3) AS registration_address
-> FROM
-> company_information
-> WHERE
-> company_id < '000010';
+------------+--------------------------------------------------------+----------------------+| company_id | registration_address | registration_address |
+------------+-------------------------------------------------------------------+------------+
| 000001 | 廣東省深圳市羅湖區深南東路5047號 | 深圳市 |
| 000002 | 廣東省深圳市鹽田區大梅沙環梅路33號萬科中心 | 深圳市 |
| 000004 | 廣東省深圳市南山區中心路（深圳灣段）3333號中鐵南方總部大廈503室 | 深圳市 |
| 000005 | 廣東省深圳市人民南路發展中心大廈13層 | 深圳市 |
| 000006 | 廣東省深圳市羅湖區寶安南路2014號振業大廈B座11-17層 | 深圳市 |
| 000007 | 廣東省深圳市華強北路1058號現代之窗大廈A座25層 | 深圳市 |
| 000008 | 北京市海淀區高梁橋斜街59號院1號樓16層1606 | 海淀區 |
| 000009 | 廣東省深圳市筍崗東路1002號寶安廣場A座28-29層 | 深圳市 |
+------------+---------------------------------------------------+--------------------------+|
8 rows in set (0.03 sec)
13、查找字串函式
查找字串函式用于查找某個字串在另一個字串中出現的位置，一共有兩個，語法如下：
LOCATE(str1,str2);
POSITION(str1 in str2);
--說明：
（1）如果 str1 是 str2 的子字串，則回傳子字串第一次出現的位置，否則回傳0，
（2）LOCATE 函式和 POSTION 函式用法相同，不同點是 LOCATE 函式的兩個引數用逗號隔開，POSITION 函式的兩個引數用 in 隔開，
舉例：
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> registration_address
-> FROM
-> company_information
-> WHERE
-> LOCATE('深圳市羅湖區',registration_address) > 0;
+------------+----------------------+-----------------------------------------------------+
| company_id | company_abbreviation | registration_address |
+------------+----------------------+----------------------------------------------------+
| 000001 | 平安銀行 | 廣東省深圳市羅湖區深南東路5047號 |
| 000006 | 深振業Ａ | 廣東省深圳市羅湖區寶安南路2014號振業大廈B座11-17層 |
| 000040 | 東旭藍天 | 廣東省深圳市羅湖區東門中路1011號鴻基大廈25-27樓 |
| 000048 | 康達爾 | 廣東省深圳市羅湖區深南東路1086號集浩大廈二、三樓 |
| 000061 | 農產品 | 廣東省深圳市羅湖區布吉路1021號天樂大廈22樓 |
| 000099 | 中信海直 | 廣東省深圳市羅湖區解放西路188號 |
| 002285 | 世聯行 | 廣東省深圳市羅湖區深南東路2028號羅湖商務中心12樓 |
| 002325 | 洪濤股份 | 廣東省深圳市羅湖區泥崗西洪濤路17號 |
| 002482 | 廣田集團 | 廣東省深圳市羅湖區深南東路2098號 |
| 002620 | 瑞和股份 | 廣東省深圳市羅湖區深南東路3027號瑞和大廈 |
| 002736 | 國信證券 | 廣東省深圳市羅湖區紅嶺中路1012號國信證券大廈十六層至二十六層 |
| 002740 | 愛迪爾 | 廣東省深圳市羅湖區東曉路1005號北樓二、三樓 |
| 002822 | 中裝建設 | 廣東省深圳市羅湖區深南東路4002號鴻隆世紀廣場四-五層（僅限辦公） |
| 002830 | 名雕股份 | 廣東省深圳市羅湖區寶安北筍崗倉831、830號陸層615、616房 |
| 300532 | 今天國際 | 廣東省深圳市羅湖區筍崗東路1002寶安廣場A座10樓F、G、H |
| 600892 | 大晟文化 | 廣東省深圳市羅湖區筍崗東路3012號中民時代廣場B座2103室 |
+------------+---------------------+---------------------------------------------------------+
16 rows in set (0.02 sec)
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> registration_address,
-> LOCATE('路',registration_address) AS locate
-> FROM
-> company_information
-> WHERE
-> LOCATE('深圳市羅湖區',registration_address) > 0;
+------------+----------------------+------------------------------------------------+--------+
| company_id | company_abbreviation | registration_address | locate |
+------------+----------------------+----------------------------------------------+--------+
| 000001 | 平安銀行 | 廣東省深圳市羅湖區深南東路5047號 | 13 |
| 000006 | 深振業Ａ | 廣東省深圳市羅湖區寶安南路2014號振業大廈B座11-17層 | 13 |
| 000040 | 東旭藍天 | 廣東省深圳市羅湖區東門中路1011號鴻基大廈25-27樓 | 13 |
| 000048 | 康達爾 | 廣東省深圳市羅湖區深南東路1086號集浩大廈二、三樓 | 13 |
| 000061 | 農產品 | 廣東省深圳市羅湖區布吉路1021號天樂大廈22樓 | 12 |
| 000099 | 中信海直 | 廣東省深圳市羅湖區解放西路188號 | 13 |
| 002285 | 世聯行 | 廣東省深圳市羅湖區深南東路2028號羅湖商務中心12樓 | 13 |
| 002325 | 洪濤股份 | 廣東省深圳市羅湖區泥崗西洪濤路17號 | 15 |
| 002482 | 廣田集團 | 廣東省深圳市羅湖區深南東路2098號 | 13 |
| 002620 | 瑞和股份 | 廣東省深圳市羅湖區深南東路3027號瑞和大廈 | 13 |
| 002736 | 國信證券 | 廣東省深圳市羅湖區紅嶺中路1012號國信證券大廈十六層至二十六層 | 13 |
| 002740 | 愛迪爾 | 廣東省深圳市羅湖區東曉路1005號北樓二、三樓 | 12 |
| 002822 | 中裝建設 | 廣東省深圳市羅湖區深南東路4002號鴻隆世紀廣場四-五層（僅限辦公）| 13 |
| 002830 | 名雕股份 | 廣東省深圳市羅湖區寶安北筍崗倉831、830號陸層615、616房 | 0 |
| 300532 | 今天國際 | 廣東省深圳市羅湖區筍崗東路1002寶安廣場A座10樓F、G、H | 13 |
| 600892 | 大晟文化 | 廣東省深圳市羅湖區筍崗東路3012號中民時代廣場B座2103室 | 13 |
+------------+----------------------+--------------------------------------------------+--------+
16 rows in set (0.01 sec)
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> web_address,
-> POSITION('e' in web_address) AS POSITION
-> FROM
-> company_information
-> WHERE
-> LOCATE('深圳市羅湖區',registration_address) > 0;
+------------+----------------------+-----------------------+----------+
| company_id | company_abbreviation | web_address | POSITION |
+------------+----------------------+-----------------------+----------+
| 000001 | 平安銀行 | www.bank.pingan.com | 0 |
| 000006 | 深振業Ａ | www.zhenye.com | 7 |
| 000040 | 東旭藍天 | www.bahjdc.com | 0 |
| 000048 | 康達爾 | www.kondarl.com | 0 |
| 000061 | 農產品 | www.szap.com | 0 |
| 000099 | 中信海直 | www.cohc.citic | 0 |
| 002285 | 世聯行 | www.worldunion.com.cn | 0 |
| 002325 | 洪濤股份 | www.szhongtao.cn | 0 |
| 002482 | 廣田集團 | www.szgt.com | 0 |
| 002620 | 瑞和股份 | www.sz-ruihe.com | 12 |
| 002736 | 國信證券 | www.guosen.com.cn | 9 |
| 002740 | 愛迪爾 | www.idr.com.cn | 0 |
| 002822 | 中裝建設 | www.zhongzhuang.com | 0 |
| 002830 | 名雕股份 | www.mingdiao.com.cn | 0 |
| 300532 | 今天國際 | www.nti56.com | 0 |
| 600892 | 大晟文化 | www.baochengshare.com | 10 |
+------------+----------------------+-----------------------+----------+
16 rows in set (0.00 sec)
14、REPEAT() 函式
REPEAT() 函式生成一個由某字串重復 n 次組成的字串，語法格式如下：
REPEAT(str,n);
--說明：生成一個由 n 個 str 連接而成的字串，
舉例：
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> CONCAT(company_abbreviation,REPEAT('=',3),web_address) AS repeat_string
-> FROM
-> company_information
-> WHERE
-> company_id < '000010';
+------------+----------------------+----------------------------------------+
| company_id | company_abbreviation | repeat_string |
+------------+----------------------+----------------------------------------+
| 000001 | 平安銀行 | 平安銀行===www.bank.pingan.com |
| 000002 | 萬科Ａ | 萬科Ａ===www.vanke.com |
| 000004 | 國農科技 | 國農科技===www.sz000004.cn |
| 000005 | 世紀星源 | 世紀星源===www.fountain.com.cn |
| 000006 | 深振業Ａ | 深振業Ａ===www.zhenye.com |
| 000007 | 全新好 | 全新好===None |
| 000008 | 神州高鐵 | 神州高鐵===www.shenzhou-gaotie.com |
| 000009 | 中國寶安 | 中國寶安===www.chinabaoan.com |
+------------+----------------------+----------------------------------------+
8 rows in set (0.00 sec)
15、LPAD() 與 RPAD() 函式
LPAD() 與 RPAD() 函式分別在一個字串的前端或后端填充若干個字符，使字串達到指定的長度，語法格式如下：
LPAD(str,len,padstr);
RPAD(str,len,padstr);
--說明：
（1）把字串 str 用 padstr 字串填充到長度為 len，并回傳長度為 len 的字串，
（2）LPAD 函式 str 字串右對齊，padstr 從左邊填充，
（3）RPAD 函式 str 字串左對齊，padstr 從右端填充，
舉例：
mysql> SELECT
-> company_id,
-> web_address,
-> CONCAT(RPAD(company_abbreviation,6,'='),web_address) AS rpad
-> FROM
-> company_information
-> WHERE
-> company_id < '000010';
+------------+-------------------------+---------------------------------------+
| company_id | web_address | rpad |
+------------+-------------------------+---------------------------------------+
| 000001 | www.bank.pingan.com | 平安銀行==www.bank.pingan.com |
| 000002 | www.vanke.com | 萬科Ａ===www.vanke.com |
| 000004 | www.sz000004.cn | 國農科技==www.sz000004.cn |
| 000005 | www.fountain.com.cn | 世紀星源==www.fountain.com.cn |
| 000006 | www.zhenye.com | 深振業Ａ==www.zhenye.com |
| 000007 | None | 全新好===None |
| 000008 | www.shenzhou-gaotie.com | 神州高鐵==www.shenzhou-gaotie.com |
| 000009 | www.chinabaoan.com | 中國寶安==www.chinabaoan.com |
+------------+-------------------------+---------------------------------------+
8 rows in set (0.00 sec)
mysql> SELECT
-> company_id,
-> web_address,
-> CONCAT(RPAD(web_address,30,'*'),company_abbreviation) AS rpad
-> FROM
-> company_information
-> WHERE
-> company_id < '000010';
+------------+-------------------------+--------------------------------------------+
| company_id | web_address | rpad |
+------------+-------------------------+--------------------------------------------+
| 000001 | www.bank.pingan.com | www.bank.pingan.com***********平安銀行 |
| 000002 | www.vanke.com | www.vanke.com*****************萬科Ａ |
| 000004 | www.sz000004.cn | www.sz000004.cn***************國農科技 |
| 000005 | www.fountain.com.cn | www.fountain.com.cn***********世紀星源 |
| 000006 | www.zhenye.com | www.zhenye.com****************深振業Ａ |
| 000007 | None | None**************************全新好 |
| 000008 | www.shenzhou-gaotie.com | www.shenzhou-gaotie.com*******神州高鐵 |
| 000009 | www.chinabaoan.com | www.chinabaoan.com************中國寶安 |
+------------+-------------------------+--------------------------------------------+
8 rows in set (0.00 sec)
16、SPACE() 函式
SPACE() 函式生成由若干個空格構成的字串，語法格式如下：
SPACE(n);
--說明：
回傳由 n 個空格構成的字串，
17、SUBSTRING_INDEX() 函式
SUBSTRING_INDEX() 函式的語法格式如下：
SUBSTRING_INDEX(str,delimiter,n);
--說明：
（1）如果 n 大于 0，回傳從左邊數第 n 個 delimiter 所表示字符的左邊的所有內容，
（2）如果 n 小于 0，回傳從右邊數第 n 個 delimiter 所表示字符的右邊的所有內容，
舉例：
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> web_address,
-> SUBSTRING_INDEX(web_address,'.',-1) AS SUBSTRING_INDEX
-> FROM
-> company_information
-> WHERE
-> company_id < '000010';
+------------+----------------------+-------------------------+-----------------+
| company_id | company_abbreviation | web_address | SUBSTRING_INDEX |
+------------+----------------------+-------------------------+-----------------+
| 000001 | 平安銀行 | www.bank.pingan.com | com |
| 000002 | 萬科Ａ | www.vanke.com | com |
| 000004 | 國農科技 | www.sz000004.cn | cn |
| 000005 | 世紀星源 | www.fountain.com.cn | cn |
| 000006 | 深振業Ａ | www.zhenye.com | com |
| 000007 | 全新好 | None | None |
| 000008 | 神州高鐵 | www.shenzhou-gaotie.com | com |
| 000009 | 中國寶安 | www.chinabaoan.com | com |
+------------+----------------------+-------------------------+-----------------+
8 rows in set (0.03 sec)
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> web_address,
-> SUBSTRING_INDEX(web_address,'.',1) AS SUBSTRING_INDEX
-> FROM
-> company_information
-> WHERE
-> company_id < '000010';
+------------+----------------------+-------------------------+-----------------+
| company_id | company_abbreviation | web_address | SUBSTRING_INDEX |
+------------+----------------------+-------------------------+-----------------+
| 000001 | 平安銀行 | www.bank.pingan.com | www |
| 000002 | 萬科Ａ | www.vanke.com | www |
| 000004 | 國農科技 | www.sz000004.cn | www |
| 000005 | 世紀星源 | www.fountain.com.cn | www |
| 000006 | 深振業Ａ | www.zhenye.com | www |
| 000007 | 全新好 | None | None |
| 000008 | 神州高鐵 | www.shenzhou-gaotie.com | www |
| 000009 | 中國寶安 | www.chinabaoan.com | www |
+------------+----------------------+-------------------------+-----------------+
8 rows in set (0.01 sec)
18、REVERSE() 函式
REVERSE() 函式對一個字串中的內容進行翻轉，語法格式如下：
REVERSE(str);
--說明：
回傳str的翻轉字串，
舉例：
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> web_address,
-> REVERSE(web_address) AS REVERSE
-> FROM
-> company_information
-> WHERE
-> company_id < '000010';
+------------+----------------------+-------------------------+-------------------------+
| company_id | company_abbreviation | web_address | REVERSE |
+------------+----------------------+-------------------------+-------------------------+
| 000001 | 平安銀行 | www.bank.pingan.com | moc.nagnip.knab.www |
| 000002 | 萬科Ａ | www.vanke.com | moc.eknav.www |
| 000004 | 國農科技 | www.sz000004.cn | nc.400000zs.www |
| 000005 | 世紀星源 | www.fountain.com.cn | nc.moc.niatnuof.www |
| 000006 | 深振業Ａ | www.zhenye.com | moc.eynehz.www |
| 000007 | 全新好 | None | enoN |
| 000008 | 神州高鐵 | www.shenzhou-gaotie.com | moc.eitoag-uohznehs.www |
| 000009 | 中國寶安 | www.chinabaoan.com | moc.naoabanihc.www |
+------------+----------------------+-------------------------+-------------------------+
8 rows in set (0.00 sec)
CREATE INDEX idx_web_address
ALTER TABLE company_information
ADD INDEX idx_web_address (REVERSE(web_address));
SELECT
company_id,
company_abbreviation,
web_address,
REVERSE(web_address) AS REVERSE
FROM
company_information
WHERE
company_id < '000010';
為 company_information 表創建一個計算列，該列的內容為 web_address 欄位內容的翻轉，然后為虛擬列創建索引，
-- 創建計算列
mysql> ALTER TABLE company_information
-> ADD reverse_web varchar(50) AS (REVERSE(web_address));
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 根據計算列創建索引
mysql> CREATE INDEX idx_reverse_web
-> ON company_information(reverse_web);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE company_information\G
*************************** 1. row ***************************
Table: company_information
Create Table: CREATE TABLE `company_information` (
`company_id` char(6) NOT NULL,
`company_abbreviation` char(20) DEFAULT NULL,
`company_fullname` char(100) DEFAULT NULL,
`English_name` char(200) DEFAULT NULL,
`registration_address` char(200) DEFAULT NULL,
`web_address` char(200) DEFAULT NULL,
`reverse_web` varchar(50) GENERATED ALWAYS AS (reverse(`web_address`)) VIRTUAL,
PRIMARY KEY (`company_id`),
KEY `idx_reverse_web` (`reverse_web`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> explain
-> SELECT
-> *
-> FROM
-> company_information
-> WHERE reverse_web = REVERSE('www.wanke.com')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company_information
partitions: NULL
type: ref
possible_keys: idx_reverse_web
key: idx_reverse_web
key_len: 153
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
19、STRCMP() 函式
STRCMP() 函式用于比較兩個字串的大小，語法如下：
STRCMP(str1,str2);
說明：
（1）根據比較規則，如果字串 str1 大于 str2，回傳 1，
（2）如果字串 str1 小于 str2，回傳 -1，
（3）如果字串 str1 和 str2 完全相同，則回傳 0，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/200208.html
標籤：其他