MySQL运算符和函数_+-+-+-+--CSDN博客

本文链接：https://blog.csdn.net/m0_37327931/article/details/84587373

一、字符函数：

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

mysql> select left('nihfdsa',2);
+-------------------+
| left('nihfdsa',2) |
+-------------------+
| ni                |
+-------------------+
1 row in set (0.00 sec)

mysql> select format(12332.879,2);
+---------------------+
| format(12332.879,2) |
+---------------------+
| 12,332.88           |
+---------------------+
1 row in set (0.00 sec)

mysql>

mysql> select concat_ws('|',id，name,price) as product from goods ;
ERROR 1054 (42S22): Unknown column 'id，name' in 'field list'
mysql> select concat_ws('|',id,name,price) as product from goods ;
+-----------------------+
| product               |
+-----------------------+
| 1|L1|4999.000         |
| 2|L2|3999.000         |
| 3|L3|3399.000         |
| 4|摄像头|399.000      |
| 5|键盘|699.000        |
| 6|鼠标|199.000        |
| 7|D1|5399.000         |
| 8|D2|5599.000         |
| 9|D3|4599.000         |
| 10|D4|3599.000        |
| 11|D5|6599.000        |
| 12|MackBook|13999.000 |
| 13|MackAir|6999.000   |
| 14|MackMINI|5999.000  |
| 15|pad|2999.000       |
| 16|pad-pro|3999.000   |
| 17|pad-mini|1999.000  |
+-----------------------+
17 rows in set (0.00 sec)

mysql>

删除前导（leading）和后面（trailing）及两侧（both）的字符串；

mysql> select trim(leading '!' from '!!!!mySql!!!!!');
+-----------------------------------------+
| trim(leading '!' from '!!!!mySql!!!!!') |
+-----------------------------------------+
| mySql!!!!!                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select trim(trailing '!' from '!!!!mySql!!!!!');
+------------------------------------------+
| trim(trailing '!' from '!!!!mySql!!!!!') |
+------------------------------------------+
| !!!!mySql                                |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select trim(both '!' from '!!!!mySql!!!!!');
+--------------------------------------+
| trim(both '!' from '!!!!mySql!!!!!') |
+--------------------------------------+
| mySql                                |
+--------------------------------------+
1 row in set (0.00 sec)

mysql>

通配符‘%’，如果不需要通配符解析，需要在前加1（'%1%%' ESCAPE '1'）则匹配的就是含有‘%’的字符串；

‘%’代表任意字符，'_'代表任意一个字符；

mysql> select * from goods where name like '%book%';
+----+----------+---------+----------+-----------+---------+------------+
| id | name     | cate_id | brand_id | price     | is_show | is_saleoff |
+----+----------+---------+----------+-----------+---------+------------+
| 12 | MackBook |       1 |        3 | 13999.000 |       1 |          0 |
+----+----------+---------+----------+-----------+---------+------------+
1 row in set (0.00 sec)

mysql>

二、数值运算符与函数；

mysql> select ceil(3.02);
+------------+
| ceil(3.02) |
+------------+
|          4 |
+------------+
1 row in set (0.01 sec)

mysql> select floor(3.99);
+-------------+
| floor(3.99) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> select 3/4;
+--------+
| 3/4    |
+--------+
| 0.7500 |
+--------+
1 row in set (0.00 sec)

mysql> select 3 div 4;
+---------+
| 3 div 4 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> select 8 mod 3;
+---------+
| 8 mod 3 |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

mysql> select power(4,3);
+------------+
| power(4,3) |
+------------+
|         64 |
+------------+
1 row in set (0.00 sec)

mysql> select round(3.486,2);
+----------------+
| round(3.486,2) |
+----------------+
|           3.49 |
+----------------+
1 row in set (0.00 sec)

mysql> select truncate(12.578,1);
+--------------------+
| truncate(12.578,1) |
+--------------------+
|               12.5 |
+--------------------+
1 row in set (0.00 sec)

mysql>

三、比较运算符和函数：

mysql> select 44 between 10 and 80;
+----------------------+
| 44 between 10 and 80 |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> select 44 not between 10 and 80;
+--------------------------+
| 44 not between 10 and 80 |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select 10 in(3,4,10,20);
+------------------+
| 10 in(3,4,10,20) |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> select null is null;
+--------------+
| null is null |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> select 12 is not null;
+----------------+
| 12 is not null |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from goods where price between 10000 and 20000;
+----+----------+---------+----------+-----------+---------+------------+
| id | name     | cate_id | brand_id | price     | is_show | is_saleoff |
+----+----------+---------+----------+-----------+---------+------------+
| 12 | MackBook |       1 |        3 | 13999.000 |       1 |          0 |
+----+----------+---------+----------+-----------+---------+------------+
1 row in set (0.00 sec)

mysql>

四、日期时间函数：

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-11-29 10:48:52 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2018-11-29 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 10:49:32  |
+-----------+
1 row in set (0.00 sec)

mysql> select date_add('2011-3-11',interval 365 day);
+----------------------------------------+
| date_add('2011-3-11',interval 365 day) |
+----------------------------------------+
| 2012-03-10                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2011-3-11',interval -365 day);
+-----------------------------------------+
| date_add('2011-3-11',interval -365 day) |
+-----------------------------------------+
| 2010-03-11                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2011-3-11',interval 1 year);
+---------------------------------------+
| date_add('2011-3-11',interval 1 year) |
+---------------------------------------+
| 2012-03-11                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2011-3-11',interval 1 week);
+---------------------------------------+
| date_add('2011-3-11',interval 1 week) |
+---------------------------------------+
| 2011-03-18                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2011-3-11','2011-4-11');
+-----------------------------------+
| datediff('2011-3-11','2011-4-11') |
+-----------------------------------+
|                               -31 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select date_format('2011-2-2','%m/%d/%y');
+------------------------------------+
| date_format('2011-2-2','%m/%d/%y') |
+------------------------------------+
| 02/02/11                           |
+------------------------------------+
1 row in set (0.00 sec)

mysql>

五、信息函数：