数据库系统原理与应用教程（053）—— MySQL 查询（十五）：字符型函数的用法

数据库系统原理与应用教程（053）—— MySQL 查询（十五）：字符型函数的用法
- 一、创建数据表
- 二、字符处理函数
- - - 1、ASCII() 函数
    - 2、CHAR() 函数
- 三、计算字符串长度函数
- - - 1、CHAR_LENGTH() 函数
    - 2、LENGTH() 函数
- 四、连接字符串函数
- - - 1、CONCAT() 函数
    - 2、CONCAT_WS() 函数
    - 3、GROUP_CONCAT() 函数
- 五、字符串替换函数
- - - 1、INSERT() 函数
    - 2、REPLACE() 函数
- 六、大小写转换函数
- 七、截取子字符串函数
- 八、查找子字符串函数
- - - 1、LOCATE() 函数
    - 2、POSITION() 函数
- 九、其他函数
- - - 1、BIN() 函数
    - 2、FORMAT() 函数
    - 3、REPEAT() 函数
    - 4、SPACE() 函数
    - 5、REVERSE() 函数
    - 6、STRCMP() 函数
    - 7、SUBSTRING_INDEX() 函数
    - 8、LPAD() 与 RPAD() 函数

查询时使用函数可以构造更加灵活的查询条件，MySQL 提供了处理字符串的函数。

一、创建数据表

查询使用的数据表如下：

-- 表结构
mysql> desc student;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| s_id   | char(5)      | NO   | PRI | NULL    |       |
| s_name | char(20)     | YES  |     | NULL    |       |
| gender | char(1)      | YES  |     | NULL    |       |
| birth  | datetime     | YES  |     | NULL    |       |
| phone  | char(20)     | YES  |     | NULL    |       |
| addr   | varchar(100) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
6 rows in set (0.10 sec)-- 表中的数据
mysql> select * from student;
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2011 | 张晓刚    | 男     | 1999-12-03 00:00:00 | 13163735775 | 信阳市    |
| S2012 | 刘小青    | 女     | 1999-10-11 00:00:00 | 13603732255 | 新乡市    |
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2014 | 刘艳      | 女     | 1998-06-24 00:00:00 | 13623735335 | 郑州市    |
| S2015 | 刘艳      | 女     | 1999-07-06 00:00:00 | 13813735225 | 信阳市    |
| S2016 | 刘若非    | 女     | 2000-08-31 00:00:00 | 13683735533 | 开封市    |
| S2021 | 董雯花    | 女     | 2000-07-30 00:00:00 | 13533735564 | 开封市    |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2023 | 特朗普    | 男     | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |
| S2024 | 奥巴马    | 男     | 2000-10-17 00:00:00 | 13843735885 | 信阳市    |
| S2025 | 周健华    | 男     | 2000-08-22 00:00:00 | 13788736655 | 开封市    |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
| S2031 | 李明博    | 女     | 1999-10-26 00:00:00 | 13643732222 | 郑州市    |
| S2032 | 达芬奇    | 男     | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
14 rows in set (0.00 sec)

二、字符处理函数

1、ASCII() 函数

返回一个字符的 ASCII 码，如果参数为字符串，则返回第一个字符的 ASCII 码。

语法格式如下：

-- 函数的参数为字符
-- 如果参数为字符串，则返回字符串中第一个字符的 ASCII 码
ASCII(str)

2、CHAR() 函数

把多个 ASCII 码转换为对应的字符，并连接成一个字符串。

语法格式如下：

-- 参数为若干个表示 ASCII 码的整数
-- 返回由 n1，n2，… 的 ASCII 码对应的字符组成的字符串
CHAR(n1, n2, ...)

例如：

mysql> select ascii('a') lower,char(ascii('a')-32) upper;
+-------+-------+
| lower | upper |
+-------+-------+
|    97 | A     |
+-------+-------+
1 row in set (0.00 sec)mysql> select ascii('b') lower,char(ascii('b')-32) upper;
+-------+-------+
| lower | upper |
+-------+-------+
|    98 | B     |
+-------+-------+
1 row in set (0.00 sec)

三、计算字符串长度函数

1、CHAR_LENGTH() 函数

返回字符串包含的字符个数（中文字符和西文字符都计算为一个字符）。

语法格式如下：

-- 函数的参数为一个字符串
-- 返回字符串中包含字符的个数，包括半角字符和全角字符
CHAR_LENGTH(str)

例如：

/*
select s_id, char_length(s_id), s_name, char_length(s_name) 
from student where addr='郑州市';
*/
mysql> select s_id, char_length(s_id), s_name, char_length(s_name) -> from student where addr='郑州市';
+-------+-------------------+-----------+---------------------+
| s_id  | char_length(s_id) | s_name    | char_length(s_name) |
+-------+-------------------+-----------+---------------------+
| S2013 |                 5 | 曹梦德    |                   3 |
| S2014 |                 5 | 刘艳      |                   2 |
| S2022 |                 5 | 周华建    |                   3 |
| S2026 |                 5 | 张学有    |                   3 |
| S2031 |                 5 | 李明博    |                   3 |
| S2032 |                 5 | 达芬奇    |                   3 |
+-------+-------------------+-----------+---------------------+
6 rows in set (0.12 sec)

2、LENGTH() 函数

返回一个字符串的长度，用字节表示。

语法格式如下：

-- 函数的参数为一个字符串
-- 返回字符串的长度（单位为字节）
-- 多字节字符的长度取决于所用的字符集。比如 utf8mb4 字符集一个汉字为 4 个字节，utf8 字符集一个汉字为 3 个字节，而 latin1 字符集一个汉字为 2 字节。
LENGTH(str)

例如：

/*
select s_id, length(s_id), s_name, length(s_name) 
from student where addr='郑州市';
*/
mysql> select s_id, length(s_id), s_name, length(s_name) -> from student where addr='郑州市';
+-------+--------------+-----------+----------------+
| s_id  | length(s_id) | s_name    | length(s_name) |
+-------+--------------+-----------+----------------+
| S2013 |            5 | 曹梦德    |              9 |
| S2014 |            5 | 刘艳      |              6 |
| S2022 |            5 | 周华建    |              9 |
| S2026 |            5 | 张学有    |              9 |
| S2031 |            5 | 李明博    |              9 |
| S2032 |            5 | 达芬奇    |              9 |
+-------+--------------+-----------+----------------+
6 rows in set (0.02 sec)-- student 表使用的字符集为 utf8，一个汉字为 3 个字节
mysql> show create table student\G
*************************** 1. row ***************************Table: student
Create Table: CREATE TABLE `student` (`s_id` char(5) NOT NULL,`s_name` char(20) DEFAULT NULL,`gender` char(1) DEFAULT NULL,`birth` datetime DEFAULT NULL,`phone` char(20) DEFAULT NULL,`addr` varchar(100) DEFAULT NULL,PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.04 sec)

四、连接字符串函数

1、CONCAT() 函数

把两个或多个字符串连接为一个字符串。

语法格式如下：

-- 函数的参数为若干个字符串
-- 把参数中包含的字符串连接成一个字符串，然后返回
CONCAT(str1,str2,...)

例如：

/*
select concat(s_name, ':', phone, ':', addr) name_phone_addr 
from student where addr='郑州市';
*/
mysql> select concat(s_name, ':', phone, ':', addr) name_phone_addr -> from student where addr='郑州市';
+---------------------------------+
| name_phone_addr                 |
+---------------------------------+
| 曹梦德:13853735522:郑州市       |
| 刘艳:13623735335:郑州市         |
| 周华建:13243735578:郑州市       |
| 张学有:13743735566:郑州市       |
| 李明博:13643732222:郑州市       |
| 达芬奇:13043731234:郑州市       |
+---------------------------------+
6 rows in set (0.00 sec)

2、CONCAT_WS() 函数

用于多个字符串的连接，和 CONCAT() 函数相比较，该函数可以指定多个字符串之间的分隔符。

语法格式如下：

-- 第一个参数为连接字符串时各字符串之间的分隔符
-- 把参数中包含的字符串连接成一个字符串，并且用指定的分隔符隔开
CONCAT_WS(separator,st1,st2,...)

例如：

/*
select concat_ws(':', s_name, phone, addr) name_phone_addr 
from student where addr='郑州市';
*/
mysql> select concat_ws(':', s_name, phone, addr) name_phone_addr -> from student where addr='郑州市';
+---------------------------------+
| name_phone_addr                 |
+---------------------------------+
| 曹梦德:13853735522:郑州市       |
| 刘艳:13623735335:郑州市         |
| 周华建:13243735578:郑州市       |
| 张学有:13743735566:郑州市       |
| 李明博:13643732222:郑州市       |
| 达芬奇:13043731234:郑州市       |
+---------------------------------+
6 rows in set (0.00 sec)

3、GROUP_CONCAT() 函数

把一个列中包含的所有数据进行连接，可以指定各个数据之间的分隔符（默认为逗号），经常在分组查询时使用。

语法格式如下：

-- 该函数对表中的某一个列进行处理，把该列包含的所有数据连接起来
-- 该函数一般用于分组查询，其用法类似于聚合函数
-- 把某一列包含的所有数据连接起来，由 separator 参数指定分隔符，如果省略 separator，则默认的分隔符为逗号
-- distinct 参数用于去除重复的数据
-- order by 参数指定按表中的某个字段排序之后再进行连接
GROUP_CONCAT([distinct] fieldname [order by 排序字段 asc|desc] separator '分隔符')

例如：

（1）不指定分隔符，默认为逗号

mysql> select addr, group_concat(s_name) name from student group by addr;
+-----------+----------------------------------------------------------+
| addr      | name                                                     |
+-----------+----------------------------------------------------------+
| 信阳市    | 张晓刚,刘艳,奥巴马                                       |
| 开封市    | 刘若非,董雯花,周健华                                     |
| 新乡市    | 刘小青,特朗普                                            |
| 郑州市    | 曹梦德,刘艳,周华建,张学有,李明博,达芬奇                  |
+-----------+----------------------------------------------------------+
4 rows in set (0.08 sec)

（2）指定分隔符

mysql> select addr, group_concat(s_name separator '||') name from student group by addr;
+-----------+---------------------------------------------------------------+
| addr      | name                                                          |
+-----------+---------------------------------------------------------------+
| 信阳市    | 张晓刚||刘艳||奥巴马                                          |
| 开封市    | 刘若非||董雯花||周健华                                        |
| 新乡市    | 刘小青||特朗普                                                |
| 郑州市    | 曹梦德||刘艳||周华建||张学有||李明博||达芬奇                  |
+-----------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

（3）连接时去除重复数据

mysql> select group_concat(addr) addr from student;
+------------------------------------------------------------------------------------+
| addr 
+------------------------------------------------------------------------------------+
| 信阳市,新乡市,郑州市,郑州市,信阳市,开封市,开封市,郑州市,新乡市,信阳市,开封市,郑州市,郑州市,郑州市
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> select group_concat(distinct addr) addr from student;
+-----------------------------------------+
| addr                                    |
+-----------------------------------------+
| 信阳市,开封市,新乡市,郑州市             |
+-----------------------------------------+
1 row in set (0.02 sec)

（4）按某个列排序之后再连接

mysql> select s_id, s_name, addr from student where addr='郑州市' order by s_id desc;
+-------+-----------+-----------+
| s_id  | s_name    | addr      |
+-------+-----------+-----------+
| S2032 | 达芬奇    | 郑州市    |
| S2031 | 李明博    | 郑州市    |
| S2026 | 张学有    | 郑州市    |
| S2022 | 周华建    | 郑州市    |
| S2014 | 刘艳      | 郑州市    |
| S2013 | 曹梦德    | 郑州市    |
+-------+-----------+-----------+
6 rows in set (0.01 sec)mysql> select addr, group_concat(s_name) name from student where addr='郑州市';
+-----------+----------------------------------------------------------+
| addr      | name                                                     |
+-----------+----------------------------------------------------------+
| 郑州市    | 曹梦德,刘艳,周华建,张学有,李明博,达芬奇                  |
+-----------+----------------------------------------------------------+
1 row in set (0.00 sec)mysql> select addr, group_concat(s_name order by s_id desc) name from student where addr='郑州市';
+-----------+----------------------------------------------------------+
| addr      | name                                                     |
+-----------+----------------------------------------------------------+
| 郑州市    | 达芬奇,李明博,张学有,周华建,刘艳,曹梦德                  |
+-----------+----------------------------------------------------------+
1 row in set (0.00 sec)

五、字符串替换函数

1、INSERT() 函数

把一个字符串中的某些字符替换为另外的字符。

语法格式如下：

-- 把字符串 str 从 pos 位置开始的 len 个字符替换为 instr 字符串，并返回替换之后的字符串
-- 如果 pos 超过字符串长度，则不进行任何替换，直接返回原字符串。
INSERT(str,pos,len,instr)

例如：把 phone 中的 4-7 个字符替换为【*】

mysql> select s_id,s_name,phone,insert(phone,4,4,'****') phone11 from student;
+-------+-----------+-------------+-------------+
| s_id  | s_name    | phone       | phone11     |
+-------+-----------+-------------+-------------+
| S2011 | 张晓刚    | 13163735775 | 131****5775 |
| S2012 | 刘小青    | 13603732255 | 136****2255 |
| S2013 | 曹梦德    | 13853735522 | 138****5522 |
| S2014 | 刘艳      | 13623735335 | 136****5335 |
| S2015 | 刘艳      | 13813735225 | 138****5225 |
| S2016 | 刘若非    | 13683735533 | 136****5533 |
| S2021 | 董雯花    | 13533735564 | 135****5564 |
| S2022 | 周华建    | 13243735578 | 132****5578 |
| S2023 | 特朗普    | 13343735588 | 133****5588 |
| S2024 | 奥巴马    | 13843735885 | 138****5885 |
| S2025 | 周健华    | 13788736655 | 137****6655 |
| S2026 | 张学有    | 13743735566 | 137****5566 |
| S2031 | 李明博    | 13643732222 | 136****2222 |
| S2032 | 达芬奇    | 13043731234 | 130****1234 |
+-------+-----------+-------------+-------------+
14 rows in set (0.01 sec)

2、REPLACE() 函数

把一个字符串中的某些字符替换为另外的字符。

语法格式如下：

-- 把 str1 中的 str2 子串替换为 str3 子串
-- 如果要替换的字符串的位置确定，内容不知道，使用 INSERT() 函数
-- 如果要替换的字符串的内容确定，位置不确定，使用 REPLACE() 函数
REPLACE(str1,str2,str3)

例如：把 s_name 中的【华】字替换为拼音【HUA】

mysql> select s_id, s_name, replace(s_name, '华', 'HUA') name2 from student;
+-------+-----------+-----------+
| s_id  | s_name    | name2     |
+-------+-----------+-----------+
| S2011 | 张晓刚    | 张晓刚    |
| S2012 | 刘小青    | 刘小青    |
| S2013 | 曹梦德    | 曹梦德    |
| S2014 | 刘艳      | 刘艳      |
| S2015 | 刘艳      | 刘艳      |
| S2016 | 刘若非    | 刘若非    |
| S2021 | 董雯花    | 董雯花    |
| S2022 | 周华建    | 周HUA建   |
| S2023 | 特朗普    | 特朗普    |
| S2024 | 奥巴马    | 奥巴马    |
| S2025 | 周健华    | 周健HUA   |
| S2026 | 张学有    | 张学有    |
| S2031 | 李明博    | 李明博    |
| S2032 | 达芬奇    | 达芬奇    |
+-------+-----------+-----------+
14 rows in set (0.03 sec)

六、大小写转换函数

MySQL提供了四个大小写转换函数。

语法格式如下：

-- UCASE 和 UPPER 可以把字符串中的字母转换为大写字母
-- LCASE 和 LOWER 可以把字符串中的字母转换为小写字母。
-- 非字母不转换
LCASE(str)
UCASE(str)
LOWER(str)
UPPER(str)

例如：

mysql> select @str := 'I am a Student123.';
+------------------------------+
| @str := 'I am a Student123.' |
+------------------------------+
| I am a Student123.           |
+------------------------------+
1 row in set (0.00 sec)mysql> select UCASE(@str), UPPER(@str) from dual;
+--------------------+--------------------+
| UCASE(@str)        | UPPER(@str)        |
+--------------------+--------------------+
| I AM A STUDENT123. | I AM A STUDENT123. |
+--------------------+--------------------+
1 row in set (0.00 sec)mysql> select LCASE(@str), LOWER(@str) from dual;
+--------------------+--------------------+
| LCASE(@str)        | LOWER(@str)        |
+--------------------+--------------------+
| i am a student123. | i am a student123. |
+--------------------+--------------------+
1 row in set (0.00 sec)

七、截取子字符串函数

该类函数可以从一个字符串中截取一个子串。

语法格式如下：

LEFT(str,n)          -- 从字符串最前端开始截取 n 个字符并返回
RIGHT(str,n)         -- 从字符串最右端截取 n 个字符并返回
SUBSTR(str,m,n)      -- 从第 m 个字符开始，截取 n 个字符并返回
SUBSTRING(str,m,n)   -- 从第 m 个字符开始，截取 n 个字符并返回

例如：

（1）显示 phone 的后四位

mysql> select s_id,s_name,phone,right(phone,4) phone22 from student;
+-------+-----------+-------------+---------+
| s_id  | s_name    | phone       | phone22 |
+-------+-----------+-------------+---------+
| S2011 | 张晓刚    | 13163735775 | 5775    |
| S2012 | 刘小青    | 13603732255 | 2255    |
| S2013 | 曹梦德    | 13853735522 | 5522    |
| S2014 | 刘艳      | 13623735335 | 5335    |
| S2015 | 刘艳      | 13813735225 | 5225    |
| S2016 | 刘若非    | 13683735533 | 5533    |
| S2021 | 董雯花    | 13533735564 | 5564    |
| S2022 | 周华建    | 13243735578 | 5578    |
| S2023 | 特朗普    | 13343735588 | 5588    |
| S2024 | 奥巴马    | 13843735885 | 5885    |
| S2025 | 周健华    | 13788736655 | 6655    |
| S2026 | 张学有    | 13743735566 | 5566    |
| S2031 | 李明博    | 13643732222 | 2222    |
| S2032 | 达芬奇    | 13043731234 | 1234    |
+-------+-----------+-------------+---------+
14 rows in set (0.00 sec)

（2）查询学生【姓什么】

mysql> select s_id,s_name,left(s_name,1) name22 from student;
+-------+-----------+--------+
| s_id  | s_name    | name22 |
+-------+-----------+--------+
| S2011 | 张晓刚    | 张     |
| S2012 | 刘小青    | 刘     |
| S2013 | 曹梦德    | 曹     |
| S2014 | 刘艳      | 刘     |
| S2015 | 刘艳      | 刘     |
| S2016 | 刘若非    | 刘     |
| S2021 | 董雯花    | 董     |
| S2022 | 周华建    | 周     |
| S2023 | 特朗普    | 特     |
| S2024 | 奥巴马    | 奥     |
| S2025 | 周健华    | 周     |
| S2026 | 张学有    | 张     |
| S2031 | 李明博    | 李     |
| S2032 | 达芬奇    | 达     |
+-------+-----------+--------+
14 rows in set (0.00 sec)

（3）显示 phone 的第 4 到第 7 位

mysql> select s_id,s_name,phone,substr(phone,4,4) phone22 from student;
+-------+-----------+-------------+---------+
| s_id  | s_name    | phone       | phone22 |
+-------+-----------+-------------+---------+
| S2011 | 张晓刚    | 13163735775 | 6373    |
| S2012 | 刘小青    | 13603732255 | 0373    |
| S2013 | 曹梦德    | 13853735522 | 5373    |
| S2014 | 刘艳      | 13623735335 | 2373    |
| S2015 | 刘艳      | 13813735225 | 1373    |
| S2016 | 刘若非    | 13683735533 | 8373    |
| S2021 | 董雯花    | 13533735564 | 3373    |
| S2022 | 周华建    | 13243735578 | 4373    |
| S2023 | 特朗普    | 13343735588 | 4373    |
| S2024 | 奥巴马    | 13843735885 | 4373    |
| S2025 | 周健华    | 13788736655 | 8873    |
| S2026 | 张学有    | 13743735566 | 4373    |
| S2031 | 李明博    | 13643732222 | 4373    |
| S2032 | 达芬奇    | 13043731234 | 4373    |
+-------+-----------+-------------+---------+
14 rows in set (0.00 sec)

八、查找子字符串函数

1、LOCATE() 函数

查找某个字符串在另一个字符串中出现的位置。

语法格式如下：

-- 如果 str1 是 str2 的子字符串，则返回子字符串第一次出现的位置，否则返回0
LOCATE(str1, str2)

例如：查找手机号包含 5566 的学生信息

-- 使用通配符
mysql> select * from student where phone like '%5566%';
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
1 row in set (0.00 sec)-- 使用 locate() 函数
mysql> select *,locate('5566',phone) position from student where locate('5566',phone)>0;
+-------+-----------+--------+---------------------+-------------+-----------+----------+
| s_id  | s_name    | gender | birth               | phone       | addr      | position |
+-------+-----------+--------+---------------------+-------------+-----------+----------+
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |        8 |
+-------+-----------+--------+---------------------+-------------+-----------+----------+
1 row in set (0.02 sec)

2、POSITION() 函数

查找某个字符串在另一个字符串中出现的位置。

语法格式如下：

-- 如果 str1 是 str2 的子字符串，则返回子字符串第一次出现的位置，否则返回0
-- LOCATE 函数和 POSTION 函数用法相同，不同点是 LOCATE 函数的两个参数用逗号隔开，POSITION 函数的两个参数用 in 隔开
POSITION(str1 in str2)

例如：查找手机号包含 55 的学生信息

-- 使用通配符
mysql> select * from student where phone like '%55%';
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2012 | 刘小青    | 女     | 1999-10-11 00:00:00 | 13603732255 | 新乡市    |
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2016 | 刘若非    | 女     | 2000-08-31 00:00:00 | 13683735533 | 开封市    |
| S2021 | 董雯花    | 女     | 2000-07-30 00:00:00 | 13533735564 | 开封市    |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2023 | 特朗普    | 男     | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |
| S2025 | 周健华    | 男     | 2000-08-22 00:00:00 | 13788736655 | 开封市    |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
8 rows in set (0.00 sec)-- 使用 position() 函数
mysql> select *,position('55' in phone) position from student where position('55' in phone)>0;
+-------+-----------+--------+---------------------+-------------+-----------+----------+
| s_id  | s_name    | gender | birth               | phone       | addr      | position |
+-------+-----------+--------+---------------------+-------------+-----------+----------+
| S2012 | 刘小青    | 女     | 1999-10-11 00:00:00 | 13603732255 | 新乡市    |       10 |
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |        8 |
| S2016 | 刘若非    | 女     | 2000-08-31 00:00:00 | 13683735533 | 开封市    |        8 |
| S2021 | 董雯花    | 女     | 2000-07-30 00:00:00 | 13533735564 | 开封市    |        8 |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |        8 |
| S2023 | 特朗普    | 男     | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |        8 |
| S2025 | 周健华    | 男     | 2000-08-22 00:00:00 | 13788736655 | 开封市    |       10 |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |        8 |
+-------+-----------+--------+---------------------+-------------+-----------+----------+
8 rows in set (0.00 sec)

九、其他函数

1、BIN() 函数

把一个整数转化为二进制数，返回结果的类型为字符串。

语法格式如下：

-- 参数为一个整数，如果参数为小数，则取整后再进行处理
-- 把数值 n 转化为二进制形式，返回值是一个由 0 和 1 组成的字符串
BIN(n)

例如：

mysql> select bin(101),bin(2.89) from dual;
+----------+-----------+
| bin(101) | bin(2.89) |
+----------+-----------+
| 1100101  | 10        |
+----------+-----------+
1 row in set (0.03 sec)

2、FORMAT() 函数

把某个数值进行四舍五入后返回，返回结果的类型为字符串。

语法格式如下：

-- 对数值 x 进行四舍五入运算，保留 d 为小数，返回值为一个字符串
-- 该函数的功能和 ROUND() 函数类似，只是返回值的类型不同
ORMAT(x,d)

例如：

mysql> select format(25.1258,2),format(1254.2,-1) from dual;
+-------------------+-------------------+
| format(25.1258,2) | format(1254.2,-1) |
+-------------------+-------------------+
| 25.13             | 1,254             |
+-------------------+-------------------+
1 row in set (0.02 sec)

3、REPEAT() 函数

生成一个由某字符串重复 n 次组成的字符串。

语法格式如下：

-- 生成一个由 n 个 str 连接而成的字符串
REPEAT(str,n)

例如：

mysql> select repeat('abc',4),repeat('王鹏',3) from dual;
+-----------------+--------------------+
| repeat('abc',4) | repeat('王鹏',3)   |
+-----------------+--------------------+
| abcabcabcabc    | 王鹏王鹏王鹏       |
+-----------------+--------------------+
1 row in set (0.00 sec)mysql> select concat(s_id,repeat('=',4),'>',s_name) from student;
+---------------------------------------+
| concat(s_id,repeat('=',4),'>',s_name) |
+---------------------------------------+
| S2011====>张晓刚                      |
| S2012====>刘小青                      |
| S2013====>曹梦德                      |
| S2014====>刘艳                        |
| S2015====>刘艳                        |
| S2016====>刘若非                      |
| S2021====>董雯花                      |
| S2022====>周华建                      |
| S2023====>特朗普                      |
| S2024====>奥巴马                      |
| S2025====>周健华                      |
| S2026====>张学有                      |
| S2031====>李明博                      |
| S2032====>达芬奇                      |
+---------------------------------------+
14 rows in set (0.00 sec)

4、SPACE() 函数

生成由若干个空格构成的字符串。

语法格式如下：

-- 返回由 n 个空格构成的字符串
SPACE(n)

例如：

mysql> select concat(s_id,space(6),s_name) from student;
+------------------------------+
| concat(s_id,space(6),s_name) |
+------------------------------+
| S2011      张晓刚            |
| S2012      刘小青            |
| S2013      曹梦德            |
| S2014      刘艳              |
| S2015      刘艳              |
| S2016      刘若非            |
| S2021      董雯花            |
| S2022      周华建            |
| S2023      特朗普            |
| S2024      奥巴马            |
| S2025      周健华            |
| S2026      张学有            |
| S2031      李明博            |
| S2032      达芬奇            |
+------------------------------+
14 rows in set (0.00 sec)

5、REVERSE() 函数

对一个字符串中的内容进行翻转。

语法格式如下：

-- 返回str的翻转字符串
REVERSE(str)

例如：

mysql> select s_id, s_name, reverse(s_name) from student;
+-------+-----------+-----------------+
| s_id  | s_name    | reverse(s_name) |
+-------+-----------+-----------------+
| S2011 | 张晓刚    | 刚晓张          |
| S2012 | 刘小青    | 青小刘          |
| S2013 | 曹梦德    | 德梦曹          |
| S2014 | 刘艳      | 艳刘            |
| S2015 | 刘艳      | 艳刘            |
| S2016 | 刘若非    | 非若刘          |
| S2021 | 董雯花    | 花雯董          |
| S2022 | 周华建    | 建华周          |
| S2023 | 特朗普    | 普朗特          |
| S2024 | 奥巴马    | 马巴奥          |
| S2025 | 周健华    | 华健周          |
| S2026 | 张学有    | 有学张          |
| S2031 | 李明博    | 博明李          |
| S2032 | 达芬奇    | 奇芬达          |
+-------+-----------+-----------------+
14 rows in set (0.00 sec)

6、STRCMP() 函数

比较两个字符串的大小。

语法格式如下：

-- 根据比较规则，如果字符串 str1 大于 str2，返回 1
-- 如果字符串 str1 小于 str2，返回 -1
-- 如果字符串 str1 和 str2 完全相同，则返回 0
STRCMP(str1,str2)

例如：

mysql> select strcmp('abc','abc'),strcmp('abc','bcd'),strcmp('abc','123') from dual;
+---------------------+---------------------+---------------------+
| strcmp('abc','abc') | strcmp('abc','bcd') | strcmp('abc','123') |
+---------------------+---------------------+---------------------+
|                   0 |                  -1 |                   1 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

7、SUBSTRING_INDEX() 函数

语法格式如下：

-- 如果 n 大于 0，返回从左边数第 n 个 delimiter 所表示字符的左边的所有内容
-- 如果 n 小于 0，返回从右边数第 n 个 delimiter 所表示字符的右边的所有内容
SUBSTRING_INDEX(str,delimiter,n)

例如：有如下的表 pic。

/*
create table pic(id int primary key,name char(20),file varchar(200));
insert into pic values(1,'flower','c:\\mydoc\\picture\\flower.jpg');
insert into pic values(2,'sun','d:\\camera\\picture\\sun.png');
insert into pic values(3,'spring','d:\\camera\\pic\\spring.psd');
insert into pic values(4,'moon','e:\\history\\2011-3-22\\moon.jpg');
insert into pic values(5,'school','e:\\history\\2020-12-3\\school.jpg');
*/
mysql> select * from pic;
+----+--------+---------------------------------+
| id | name   | file                            |
+----+--------+---------------------------------+
|  1 | flower | c:\mydoc\picture\flower.jpg     |
|  2 | sun    | d:\camera\picture\sun.png       |
|  3 | spring | d:\camera\pic\spring.psd        |
|  4 | moon   | e:\history\2011-3-22\moon.jpg   |
|  5 | school | e:\history\2020-12-3\school.jpg |
+----+--------+---------------------------------+
5 rows in set (0.00 sec)

（1）查询表中文件的扩展名

mysql> select *,substring_index(file,'.',-1) from pic;
+----+--------+---------------------------------+------------------------------+
| id | name   | file                            | substring_index(file,'.',-1) |
+----+--------+---------------------------------+------------------------------+
|  1 | flower | c:\mydoc\picture\flower.jpg     | jpg                          |
|  2 | sun    | d:\camera\picture\sun.png       | png                          |
|  3 | spring | d:\camera\pic\spring.psd        | psd                          |
|  4 | moon   | e:\history\2011-3-22\moon.jpg   | jpg                          |
|  5 | school | e:\history\2020-12-3\school.jpg | jpg                          |
+----+--------+---------------------------------+------------------------------+
5 rows in set (0.00 sec)

（2）查询表中的文件名（不带路径）

mysql> select *,substring_index(file,'\\',-1) from pic;
+----+--------+---------------------------------+-------------------------------+
| id | name   | file                            | substring_index(file,'\\',-1) |
+----+--------+---------------------------------+-------------------------------+
|  1 | flower | c:\mydoc\picture\flower.jpg     | flower.jpg                    |
|  2 | sun    | d:\camera\picture\sun.png       | sun.png                       |
|  3 | spring | d:\camera\pic\spring.psd        | spring.psd                    |
|  4 | moon   | e:\history\2011-3-22\moon.jpg   | moon.jpg                      |
|  5 | school | e:\history\2020-12-3\school.jpg | school.jpg                    |
+----+--------+---------------------------------+-------------------------------+
5 rows in set (0.00 sec)

（3）查询表中的文件路径

/*
select *,
left(file,char_length(file)-char_length(substring_index(file,'\\',-1))) path 
from pic;
*/
mysql> select *,-> left(file,char_length(file)-char_length(substring_index(file,'\\',-1))) path -> from pic;
+----+--------+---------------------------------+-----------------------+
| id | name   | file                            | path                  |
+----+--------+---------------------------------+-----------------------+
|  1 | flower | c:\mydoc\picture\flower.jpg     | c:\mydoc\picture\     |
|  2 | sun    | d:\camera\picture\sun.png       | d:\camera\picture\    |
|  3 | spring | d:\camera\pic\spring.psd        | d:\camera\pic\        |
|  4 | moon   | e:\history\2011-3-22\moon.jpg   | e:\history\2011-3-22\ |
|  5 | school | e:\history\2020-12-3\school.jpg | e:\history\2020-12-3\ |
+----+--------+---------------------------------+-----------------------+
5 rows in set (0.00 sec)

（4）查询表中的文件所在的盘符

mysql> select *,substring_index(file,'\\',1) from pic;
+----+--------+---------------------------------+------------------------------+
| id | name   | file                            | substring_index(file,'\\',1) |
+----+--------+---------------------------------+------------------------------+
|  1 | flower | c:\mydoc\picture\flower.jpg     | c:                           |
|  2 | sun    | d:\camera\picture\sun.png       | d:                           |
|  3 | spring | d:\camera\pic\spring.psd        | d:                           |
|  4 | moon   | e:\history\2011-3-22\moon.jpg   | e:                           |
|  5 | school | e:\history\2020-12-3\school.jpg | e:                           |
+----+--------+---------------------------------+------------------------------+
5 rows in set (0.00 sec)

8、LPAD() 与 RPAD() 函数

LPAD() 与 RPAD() 函数分别在一个字符串的前端或后端填充若干个字符，使字符串达到指定的长度。

语法格式如下：

-- 把字符串 str 用 padstr 字符串填充到长度为 len，并返回长度为 len 的字符串
-- LPAD 函数 str 字符串右对齐，padstr 从左边填充
-- RPAD 函数 str 字符串左对齐，padstr 从右端填充
LPAD(str, len, padstr)
RPAD(str, len, padstr)

例如：

mysql> select id,concat(name,'||',file) from pic;
+----+-----------------------------------------+
| id | concat(name,'||',file)                  |
+----+-----------------------------------------+
|  1 | flower||c:\mydoc\picture\flower.jpg     |
|  2 | sun||d:\camera\picture\sun.png          |
|  3 | spring||d:\camera\pic\spring.psd        |
|  4 | moon||e:\history\2011-3-22\moon.jpg     |
|  5 | school||e:\history\2020-12-3\school.jpg |
+----+-----------------------------------------+
5 rows in set (0.00 sec)-- rpad() 函数：左对齐，右侧填充空格
mysql> select id,concat(rpad(name,8,' '),'||',file) from pic;
+----+-------------------------------------------+
| id | concat(rpad(name,8,' '),'||',file)        |
+----+-------------------------------------------+
|  1 | flower  ||c:\mydoc\picture\flower.jpg     |
|  2 | sun     ||d:\camera\picture\sun.png       |
|  3 | spring  ||d:\camera\pic\spring.psd        |
|  4 | moon    ||e:\history\2011-3-22\moon.jpg   |
|  5 | school  ||e:\history\2020-12-3\school.jpg |
+----+-------------------------------------------+
5 rows in set (0.00 sec)-- lpad() 函数：右对齐，左侧填充空格
mysql> select id,concat(rpad(name,8,' '),'||',lpad(file,35,' ')) from pic;
+----+-------------------------------------------------+
| id | concat(rpad(name,8,' '),'||',lpad(file,35,' ')) |
+----+-------------------------------------------------+
|  1 | flower  ||        c:\mydoc\picture\flower.jpg   |
|  2 | sun     ||          d:\camera\picture\sun.png   |
|  3 | spring  ||           d:\camera\pic\spring.psd   |
|  4 | moon    ||      e:\history\2011-3-22\moon.jpg   |
|  5 | school  ||    e:\history\2020-12-3\school.jpg   |
+----+-------------------------------------------------+
5 rows in set (0.00 sec)