好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

MYSQL 常用函数

MYSQL 常用函数

祖仙教小凡仙 海鲨数据库架构师

字符串函数

合并字符串

 
### v  方式1：CONCATE()函数
§  CONCAT(s1, s2, …, sn)
§  将字符串合并起来，如果其中一个为NULL,  返回值为NULL
§  可以接受非字符串参数
§  例子：SELECT  CONCAT(‘Hello’,‘ World’, 1234);
§  例子：SELECT  CONCAT(‘Hello’,’ World’, NULL); 
 
mysql> select concat('hello','wold',1234);
+-----------------------------+
| concat('hello','wold',1234) |
+-----------------------------+
| hellowold1234               |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select concat('hello','wold',NULL);
+-----------------------------+
| concat('hello','wold',NULL) |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set (0.00 sec)

v 方式2：CONCATE_WS()函数

§ CONCAT_WS(sep, s1, s2, …, sn) § 将字符串合并起来，并用sep作为分隔符 § 例子：SELECT CONCAT_WS(‘%’,‘Hi’,‘World’);

 
mysql> select concat_ws('%','Hi','World');
+-----------------------------+
| concat_ws('%','Hi','World') |
+-----------------------------+
| Hi%World                    |
+-----------------------------+
1 row in set (0.00 sec)

比较字符串

v STRCMP(str1, str2) 函数 § 如果str1大于str2，返回1 § 如果str1等于str2，返回0 § 如果str1小于str2，返回-1

例子：SELECT STRCMP(‘abc’,’abd’), STRCMP(‘abc’,’abc’), STRCMP(‘abc’,’abb’);

mysql> select strcmp('abc','abd'),strcmp('abc','abc'),strcmp('abc','abb'); +---------------------+---------------------+---------------------+ | strcmp('abc','abd') | strcmp('abc','abc') | strcmp('abc','abb') | +---------------------+---------------------+---------------------+ | -1 | 0 | 1 | +---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)

 
### 长度函数

v  获取字符串长度函数 LENGTH()
§  LENGTH(str) :  字节长度
§  例子：SELECT    LENGTH(‘MySQL’) ，
                                    LENGTH(‘中国’) ；

mysql> select LENGTH('MySql'),LENGTH('中国');\G +-----------------+------------------+ | LENGTH('MySql') | LENGTH('中国') | +-----------------+------------------+ | 5 | 6 | +-----------------+------------------+ 1 row in set (0.01 sec)

 
v  获取字符函数 CHAR_LENGTH()
§  CHAR_LENGTH(str) :  字符长度
§  例子：SELECT   CHAR_LENGTH(‘MySQL’) ，
                                  CHAR_LENGTH(‘中国’) ；

mysql> select char_length('MySql'),char_length('中国'); +----------------------+-----------------------+ | char_length('MySql') | char_length('中国') | +----------------------+-----------------------+ | 5 | 2 | +----------------------+-----------------------+ 1 row in set (0.00 sec)

 
### 替换字符串
v  INSERT(str, pos, len, newstr)：

   §  将字符串str中从pos位置开始长度为len的字符串用newstr替换
   §  例子：SELECT   ‘这是MySQL数据库管理系统’ 原字符串,
     INSERT(‘这是MySQL数据库管理系统’ , 3, 5,‘ORACLE’, 2) 替换后;

mysql> SELECT '这是MySQL数据库管理系统' as 原字符串, INSERT('这是MySQL数据库管理系统',3,5,'ORACLE') as 替换后; +----------------------------------+-----------------------------------+ | 原字符串 | 替换后 | +----------------------------------+-----------------------------------+ | 这是MySQL数据库管理系统 | 这是ORACLE数据库管理系统 | +----------------------------------+-----------------------------------+ 1 row in set (0.00 sec)

 
v  REPLACE(str, substr, newstr)：
§  将str中substr替换为newstr
§  例子：SELECT   ‘这是SQL数据库管理系统MySQL’ 原字符串,
     REPLACE(‘这是SQL数据库管理系统MySQL’ , ’SQL’, ‘ORACLE’) 替换后;

mysql> SELECT '这是MySQL数据库管理系统' as 原字符串, -> REPLACE('这是MySQL数据库管理系统','SQL','ORACLE') as 替换后;

+----------------------------------+-------------------------------------+ | 原字符串 | 替换后 | +----------------------------------+-------------------------------------+ | 这是MySQL数据库管理系统 | 这是MyORACLE数据库管理系统 | +----------------------------------+-------------------------------------+ 1 row in set (0.00 sec)

 
字母大小写转换
v  字母转大写：UPPER(s),  UCASE(s)
§  例子：SELECT    UPPER(‘mysql’) ，
                                   UCASE(‘mysql’) ；

mysql> SELECT UPPER('mysql'),ucase('mysql_small'); +----------------+----------------------+ | UPPER('mysql') | ucase('mysql_small') | +----------------+----------------------+ | MYSQL | MYSQL_SMALL | +----------------+----------------------+ 1 row in set (0.00 sec)

 
v  字母转大写：LOWER(s),  LCASE(s)
§  例子：SELECT    LOWER(‘MySQL’) ，
                                   LCASE(‘MySQL’) ；

mysql> SELECT LOWER('MySQL'), LCASE('MySQL');

+----------------+----------------+ | LOWER('MySQL') | LCASE('MySQL') | +----------------+----------------+ | mysql | mysql | +----------------+----------------+ 1 row in set (0.00 sec)

 
### 查找字符串
v  返回字符串位置：FIND_IN_SET(str1, str2)
§  返回在str2中与str1相匹配的字符串的位置，str2包含若干个用逗号隔开的字符串
§  例子：SELECT  FIND_IN_SET(‘MySQL’,’ab,test,MySQL’)；

mysql> SELECT FIND_IN_SET('MySQL','ab,test,MySQL'); +--------------------------------------+ | FIND_IN_SET('MySQL','ab,test,MySQL') | +--------------------------------------+ | 3 | +--------------------------------------+ 1 row in set (0.00 sec)

 
v  返回指定字符串位置：FIELD(str, str1, str2)
§  返回第一个与字符串str匹配的字符串的位置
§  例子：SELECT   FIELD(‘ab’,’a’,’ab’,’ac’,’ab’)；

v  返回字符串相匹配的开始位置：
§  LOCATE(str1, str): 返回str中str1的开始位置
§  POSITION(str1  IN  str)
§  INSTR(str, str1)
§  例子：SELECT  LOCATE(‘SQL’,’MySQL’),
                        POSITION(‘SQL’ IN ’MySQL’) , 
                        INSTR(‘MySQL’,’SQL’) ；

v  截取字符串：
§  LEFT(str, num) : 返回字符串str中左边num个字母的字符串
§  RIGHT(str, num)：返回字符串str中右边num个字母的字符串
§  例子：SELECT   LEFT(‘MySQL’, 2) 前两个字符串,
                             RIGHT(‘MySQL’, 2) 后两个字符串;

v  截取指定位置和长度字符串：
§  SUBSTRING(str, num, len) :
§  MID(str, num, len)
§  返回字符串str中第num个位置开始长度为len的字符串
§  例子：SELECT   SUBSTRING(‘MySQL’, 3，3) 截取的字符串,
                                MID(‘MySQL’, 3，3) 截取的字符串;

mysql> select field('ab','a','ab','ac','ab'); +--------------------------------+ | field('ab','a','ab','ac','ab') | +--------------------------------+ | 2 | +--------------------------------+ 1 row in set (0.00 sec)

mysql> select locate('SQL','MySQL'), position('SQL' in 'MySQL'), instr('MySQL','SQL'); +-----------------------+----------------------------+----------------------+ | locate('SQL','MySQL') | position('SQL' in 'MySQL') | instr('MySQL','SQL') | +-----------------------+----------------------------+----------------------+ | 3 | 3 | 3 | +-----------------------+----------------------------+----------------------+ 1 row in set (0.00 sec)

mysql> select left('MYSQL',2),RIGHT('MYSQL',2); +-----------------+------------------+ | left('MYSQL',2) | RIGHT('MYSQL',2) | +-----------------+------------------+ | MY | QL | +-----------------+------------------+ 1 row in set (0.00 sec)

mysql> select substring('MYSQL',3,3),mid('MYSQL',3,3); +------------------------+------------------+ | substring('MYSQL',3,3) | mid('MYSQL',3,3) | +------------------------+------------------+ | SQL | SQL | +------------------------+------------------+ 1 row in set (0.00 sec)

 
### 数值函数

![](https://s4.51cto.com/images/blog/202103/06/9ebc7cc423d494e3f61a6ee622dbffcf.png)

mysql> select rand(),rand(1),rand(2),rand(3); +--------------------+---------------------+--------------------+--------------------+ | rand() | rand(1) | rand(2) | rand(3) | +--------------------+---------------------+--------------------+--------------------+ | 0.1878656774646283 | 0.40540353712197724 | 0.6555866465490187 | 0.9057697559760601 | +--------------------+---------------------+--------------------+--------------------+ 1 row in set (0.00 sec)

mysql> select ceil(2.1),ceil(-2.1),floor(2.1),floor(-2.1); +-----------+------------+------------+-------------+ | ceil(2.1) | ceil(-2.1) | floor(2.1) | floor(-2.1) | +-----------+------------+------------+-------------+ | 3 | -2 | 2 | -3 | +-----------+------------+------------+-------------+ 1 row in set (0.00 sec)

mysql> select round(2.3456),round(2.34567,2); +---------------+------------------+ | round(2.3456) | round(2.34567,2) | +---------------+------------------+ | 2 | 2.35 | +---------------+------------------+ 1 row in set (0.00 sec)

mysql> select truncate(122.23456,2),truncate(1223.3456,-1); +-----------------------+------------------------+ | truncate(122.23456,2) | truncate(1223.3456,-1) | +-----------------------+------------------------+ | 122.23 | 1220 | +-----------------------+------------------------+ 1 row in set (0.00 sec) mysql> select mod(10,3); +-----------+ | mod(10,3) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)

 
### 日期和时间函数

  ![](https://s4.51cto.com/images/blog/202103/06/cd601ac60fd633df890b834d0f6e1bd1.png)                      

v 获取当前日期和时间
§  NOW(), CURRENT_TIMESTAMP(),
§  LOCALTIME(), SYSDATE()
v 获取当前日期
§ CURDATE(),  CURRENT_DATE()
v 获取当前时间
§ CURTIME(),  CURRENT_TIME()
v 获取当前时间的年月季度周天时分秒
§YEAR(),QUARTER(),MONTH() ,WEEK(),DAYOFMONTH(),HOUR(),MINUTE(),SECOND()

获取当前日期和时间

mysql> SELECT NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(); +---------------------+---------------------+---------------------+---------------------+ | NOW() | CURRENT_TIMESTAMP() | LOCALTIME() | SYSDATE() | +---------------------+---------------------+---------------------+---------------------+ | 2021-02-27 12:03:10 | 2021-02-27 12:03:10 | 2021-02-27 12:03:10 | 2021-02-27 12:03:10 | +---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.02 sec)

#获取当前日期 mysql> SELECT CURDATE(),CURRENT_DATE(); +------------+----------------+ | CURDATE() | CURRENT_DATE() | +------------+----------------+ | 2021-02-27 | 2021-02-27 | +------------+----------------+ 1 row in set (0.00 sec)

#获取当前时间 mysql> SELECT CURTIME(),CURRENT_TIME(); +-----------+----------------+ | CURTIME() | CURRENT_TIME() | +-----------+----------------+ | 12:03:52 | 12:03:52 | +-----------+----------------+ 1 row in set (0.00 sec)

#获取当前时间的年月季度周 WEEK 是一年中第几周 mysql> SELECT NOW(),YEAR(NOW()),QUARTER(NOW()),MONTH(NOW()),WEEK(NOW()); +---------------------+-------------+----------------+--------------+-------------+ | NOW() | YEAR(NOW()) | QUARTER(NOW()) | MONTH(NOW()) | WEEK(NOW()) | +---------------------+-------------+----------------+--------------+-------------+ | 2021-02-27 12:05:13 | 2021 | 1 | 2 | 8 | +---------------------+-------------+----------------+--------------+-------------+ 1 row in set (0.00 sec)

#天时分秒 mysql> SELECT DAYOFMONTH(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()); +-------------------+-------------+---------------+---------------+ | DAYOFMONTH(NOW()) | HOUR(NOW()) | MINUTE(NOW()) | SECOND(NOW()) | +-------------------+-------------+---------------+---------------+ | 27 | 12 | 5 | 53 | +-------------------+-------------+---------------+---------------+ 1 row in set (0.00 sec)

xxxNAME 表示时间名称 英语或中文

mysql> SELECT NOW(),MONTHNAME(NOW()),WEEKOFYEAR(NOW()) AS '年中第几周', dayname(now()),dayofweek(now()); +---------------------+------------------+-----------------+----------------+------------------+ | NOW() | MONTHNAME(NOW()) | 年中第几周 | dayname(now()) | dayofweek(now()) | +---------------------+------------------+-----------------+----------------+------------------+ | 2021-02-27 12:08:16 | February | 8 | Saturday | 7 | +---------------------+------------------+-----------------+----------------+------------------+ 1 row in set (0.00 sec)

mysql> select now(),dayofyear(now()) 年中第几天 ,dayofmonth(now()) 月中第几天; +---------------------+-----------------+-----------------+ | now() | 年中第几天 | 月中第几天 | +---------------------+-----------------+-----------------+ | 2021-02-27 12:11:18 | 58 | 27 | +---------------------+-----------------+-----------------+ 1 row in set (0.00 sec)

mysql> select now(),week(now()),weekofyear(now()),dayofweek(now()), dayofyear(now()),dayofmonth(now()); +---------------------+-------------+-------------------+------------------+------------------+-------------------+ | now() | week(now()) | weekofyear(now()) | dayofweek(now()) | dayofyear(now()) | dayofmonth(now()) | +---------------------+-------------+-------------------+------------------+------------------+-------------------+ | 2021-02-27 12:17:28 | 8 | 8 | 7 | 58 | 27 | +---------------------+-------------+-------------------+------------------+------------------+-------------------+ 1 row in set (0.00 sec)

 
v  计算日期和时间的函数
§  TO_DAYS(date)：计算date与默认日期（0000年1月1日）之间的天数
§  FROM_DAYS(number)：计算从默认日期开始number天后的日期
§  DATE(date1, date2)：date1 与 date2 之间的相隔天数
§  ADDDATE(date, n)：日期date加上n天后的日期
§  SUBDATE(date, n)：日期date减去n天后的日期

mysql> select now() 当前时间,to_days(now()) 相隔天数, from_days(to_days(now())) 现在日期; +---------------------+--------------+--------------+ | 当前时间 | 相隔天数 | 现在日期 | +---------------------+--------------+--------------+ | 2021-02-27 12:22:41 | 738213 | 2021-02-27 | +---------------------+--------------+--------------+ 1 row in set (0.00 sec)

mysql> select adddate(curdate(),5) , subdate(curdate(),5); +----------------------+----------------------+ | adddate(curdate(),5) | subdate(curdate(),5) | +----------------------+----------------------+ | 2021-03-04 | 2021-02-22 | +----------------------+----------------------+ 1 row in set (0.00 sec)

查看更多关于MYSQL 常用函数的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did162089
  阅读：26次

上一篇： 笔记 | Python之匿名函数

下一篇：上线 Python 应用仅需一条命令的开源框架：Zappa（详细教程）

相关资讯