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。
