MySQL常用函数

数据库 waitig 476℃ 百度已收录 0评论

摘要:

这是一篇关于MySQL的文章,主要介绍 MySQL 常用函数


1. 字符串函数

  • 合并字符串函数: CONCAT()
mysql> select concat('love', ' cs');
+------------------------+
| concat('love', ' cs') |
+------------------------+
| love cs               |
+------------------------+
1 row in set (0.01 sec)

-- 遇到空值,整个结果为NULL
mysql> select concat('love', ' cs', NULL);
+------------------------------+
| concat('love', ' cs', NULL) |
+------------------------------+
| NULL                         |
+------------------------------+
1 row in set (0.00 sec)
  • 合并字符串函数: CONCAT_WS(),可以指定分隔符
-- 第一个参数为分隔符

mysql> select concat_WS('-', 'love', 'cs');
+-------------------------------+
| concat_WS('-', 'love', 'cs') |
+-------------------------------+
| love-cs                      |
+-------------------------------+
1 row in set (0.00 sec)
  • 字符串比较函数: STRCMP()
mysql> select STRCMP('ab', 'aa'),        STRCMP('ab', 'ab'),        STRCMP('ab', 'ac');
+--------------------+--------------------+--------------------+
| STRCMP('ab', 'aa') | STRCMP('ab', 'ab') | STRCMP('ab', 'ac') |
+--------------------+--------------------+--------------------+
|                  1 |                  0 |                 -1 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)
  • 获取字符串长度: LENGTH()
mysql>  select length('xff');
+---------------+
| length('xff') |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)
  • 大小写转换: UPPER(), LOWER()
mysql> select UPPER('xff'), LOWER('XFF');
+--------------+--------------+
| UPPER('xff') | LOWER('XFF') |
+--------------+--------------+
| XFF          | xff          |
+--------------+--------------+
1 row in set (0.00 sec)
  • 字符串位置查找: FIND_IN_SET()
mysql> select FIND_IN_SET('BEIJING', 'HONGKANG,BEIJING');
+--------------------------------------------+
| FIND_IN_SET('BEIJING', 'HONGKANG,BEIJING') |
+--------------------------------------------+
|                                          2 |
+--------------------------------------------+
1 row in set (0.00 sec)
  • 截取字符串函数: LEFT(), RIGHT(), SUBSTRING(), MID()
mysql> select left('beijing', 3), right('beijing', 4), mid('beijing welcome you', 9, 7);
+--------------------+---------------------+----------------------------------+
| left('beijing', 3) | right('beijing', 4) | mid('beijing welcome you', 9, 7) |
+--------------------+---------------------+----------------------------------+
| bei                | jing                | welcome                          |
+--------------------+---------------------+----------------------------------+
1 row in set (0.00 sec)

-- SUBSTRING函数和MID函数相同
  • 字符串去除空格函数: LTRIM, RTRIM, TRIM
-- 三个函数分别为去除左边空格,右边空格和两边空格
mysql> select concat('|', LTRIM('     xff        '), '|'),
    ->        concat('|', RTRIM('     xff        '), '|'),
    ->        concat('|', TRIM('     xff        '), '|')\G
*************************** 1. row ***************************
concat('|', LTRIM('     xff        '), '|'): |xff        |
concat('|', RTRIM('     xff        '), '|'): |     xff|
 concat('|', TRIM('     xff        '), '|'): |xff|
1 row in set (0.00 sec)
  • 字符串替换函数: REPLACE
mysql> select replace('Beijing welcome you', 'Beijing', 'MySQL');
+----------------------------------------------------+
| replace('Beijing welcome you', 'Beijing', 'MySQL') |
+----------------------------------------------------+
| MySQL welcome you                                  |
+----------------------------------------------------+
1 row in set (0.00 sec)

2. 数值函数

  • 获取随机数: RAND()
-- 返回0 ~ 1之间的随机数
mysql> select RAND();
+---------------------+
| RAND()              |
+---------------------+
| 0.25700983522181386 |
+---------------------+
1 row in set (0.00 sec)
  • 四舍五入函数(圆整): ROUND()
-- 第二个参数为精度,如果没有指定精度则返回的是整数
mysql> select ROUND(3.14159), ROUND(3.145927, 3);
+----------------+--------------------+
| ROUND(3.14159) | ROUND(3.145927, 3) |
+----------------+--------------------+
|              3 |              3.146 |
+----------------+--------------------+
1 row in set (0.00 sec)

3. 时间和日期函数

  • 当前时间和日期
-- 分别为获取当前日期,时间,日期和时间
mysql> select CURDATE(), CURTIME(), NOW();
+------------+-----------+---------------------+
| CURDATE()  | CURTIME() | NOW()               |
+------------+-----------+---------------------+
| 2016-02-26 | 08:14:05  | 2016-02-26 08:14:05 |
+------------+-----------+---------------------+
1 row in set (0.00 sec)
  • 获取时间和日期各部分
-- 顾名思义
mysql> select  now(),
               year(now()),
               quarter(now()), -- 季度
               month(now()),
               week(now()),    -- 第几周
               dayofmonth(now()),
               hour(now()),
               minute(now()),
               second(now())\G
*************************** 1. row ***************************
            now(): 2016-02-26 08:17:02
      year(now()): 2016
   quarter(now()): 1
     month(now()): 2
      week(now()): 8
dayofmonth(now()): 26
      hour(now()): 8
    minute(now()): 17
    second(now()): 2
1 row in set (0.00 sec)

4. 系统信息函数

mysql> select version(),
    ->        database(),
    ->        user(),
    ->        last_insert_id(); --最近一次auto_increment的数值
+-------------------------+------------+----------------+------------------+
| version()               | database() | user()         | last_insert_id() |
+-------------------------+------------+----------------+------------------+
| 5.6.28-0ubuntu0.14.04.1 | NULL       | root@localhost |                0 |
+-------------------------+------------+----------------+------------------+
1 row in set (0.00 sec)

本文由【waitig】发表在等英博客
本文固定链接:MySQL常用函数
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)