MySQL常见基本函数介绍

1.字符串函数

• 字符串拼接

mysql> select concat('a','-','b');
+---------------------+
| concat('a','-','b') |
+---------------------+
| a-b                 |
+---------------------+
1 row in set (0.00 sec)

mysql> select c_name,c_madein,concat(c_name,c_madein) from commodity limit 5;
+-----------------+----------+-------------------------+
| c_name          | c_madein | concat(c_name,c_madein) |
+-----------------+----------+-------------------------+
| 变形金刚-擎天柱 | 中国     | 变形金刚-擎天柱中国     |
| 变形金刚-霸天虎 | 中国     | 变形金刚-霸天虎中国     |
| 变形金刚-威震天 | 美国     | 变形金刚-威震天美国     |
| 魔仙玩偶1       | 中国     | 魔仙玩偶1中国           |
| 超人玩偶        | 中国     | 超人玩偶中国            |
+-----------------+----------+-------------------------+
5 rows in set (0.00 sec)

• 将所有字母变为大写或小写

mysql> # 所有字母大写
mysql> select upper('Jack');
+---------------+
| upper('Jack') |
+---------------+
| JACK          |
+---------------+
1 row in set (0.00 sec)

mysql> # 所有字母小写
mysql> select lower('Jack');
+---------------+
| lower('Jack') |
+---------------+
| jack          |
+---------------+
1 row in set (0.00 sec)

• 返回/截取字符串中的字符

mysql> # 所有字母大写
mysql> select upper('Jack');
+---------------+
| upper('Jack') |
+---------------+
| JACK          |
+---------------+
1 row in set (0.00 sec)

mysql> # 所有字母小写
mysql> select lower('Jack');
+---------------+
| lower('Jack') |
+---------------+
| jack          |
+---------------+
1 row in set (0.00 sec)

mysql> # 从左边开始截取字符串
mysql> select left('hello',2);
+-----------------+
| left('hello',2) |
+-----------------+
| he              |
+-----------------+
1 row in set (0.00 sec)

mysql> # 从右边开始截取字符串
mysql> select right('hello',2);
+------------------+
| right('hello',2) |
+------------------+
| lo               |
+------------------+
1 row in set (0.00 sec)

mysql> # 替换指定字符
mysql> select replace('hello','l','k');
+--------------------------+
| replace('hello','l','k') |
+--------------------------+
| hekko                    |
+--------------------------+
1 row in set (0.00 sec)

mysql> # 去除前后空格
mysql> select trim('   hello     ');
+-----------------------+
| trim('   hello     ') |
+-----------------------+
| hello                 |
+-----------------------+
1 row in set (0.00 sec)

mysql> # 计算字符串长度
mysql> select length(trim('   hello     '));
+-------------------------------+
| length(trim('   hello     ')) |
+-------------------------------+
|                             5 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> # 截取指定位置字符串
mysql> select substring('hello',1,3);
+------------------------+
| substring('hello',1,3) |
+------------------------+
| hel                    |
+------------------------+
1 row in set (0.00 sec)

2.数值函数

mysql> # 求绝对值
mysql> select abs(-99);
+----------+
| abs(-99) |
+----------+
|       99 |
+----------+
1 row in set (0.00 sec)

mysql> # 向上取整
mysql> select ceil(3.14);
+------------+
| ceil(3.14) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> # 向下取整
mysql> select floor(3.14);
+-------------+
| floor(3.14) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> # 求余数
mysql> select mod(5,3);
+----------+
| mod(5,3) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> # 随机数
mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.29647299581810177 |
+---------------------+
1 row in set (0.00 sec)

3.日期函数

mysql> # 返回当前系统时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-07-16 16:00:09 |
+---------------------+
1 row in set (0.00 sec)

mysql> # 返回时间戳
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1594886443 |
+------------------+
1 row in set (0.00 sec)

mysql> # 返回当前时间的年份
mysql> select year();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2020 |
+-------------+
1 row in set (0.00 sec)

4.流程函数

mysql> # IFNULL(value1,value2) 如果value1不为空 则返回value1否则返回value2
mysql> select ifnull(c_outprice,0) from commodity limit 10;
+----------------------+
| ifnull(c_outprice,0) |
+----------------------+
|                   50 |
|                   45 |
|                  245 |
|                   12 |
|                   99 |
|                   99 |
|                 3000 |
|                  600 |
|                   60 |
|                    0 |
+----------------------+
10 rows in set (0.00 sec)

mysql> # case when 如果c_type为1则输出'玩具'，为2输出'文具'，其他输出'书籍'
mysql> select case c_type when 1 then '玩具' when 2 then '文具' else '书籍' end case_when from commodity limit 10;
+-----------+
| case_when |
+-----------+
| 玩具      |
| 玩具      |
| 玩具      |
| 玩具      |
| 玩具      |
| 玩具      |
| 玩具      |
| 玩具      |
| 玩具      |
| 玩具      |
+-----------+
10 rows in set (0.00 sec)

5.其他系统函数

mysql> # 想知道当前的MySQL版本号
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.30-log |
+------------+
1 row in set (0.00 sec)

mysql> # 想知道当前的用户
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> # 当前操作数据库
mysql> select database();
+------------+
| database() |
+------------+
| ishop      |
+------------+
1 row in set (0.00 sec)

mysql> # MySQL5.7才有的md5加密
mysql> select md5('hello');
+----------------------------------+
| md5('hello')                     |
+----------------------------------+
| 5d41402abc4b2a76b9719d911017c592 |
+----------------------------------+
1 row in set (0.00 sec)