函数的用法"/>
数据库系统原理与应用教程（053）—— MySQL 查询（十五）：字符型函数的用法
数据库系统原理与应用教程（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)
更多推荐
数据库系统原理与应用教程（053）—— MySQL 查询（十五）：字符型函数的用法
发布评论