MySQL运算符和函数
一、字符函数：
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>