MYSQL完全手册学习笔记（第六章）

运算符

算数运算符
mysql> select 10+5,100+658;
+------+---------+
| 10+5 | 100+658 |
+------+---------+
|   15 |     758 |
+------+---------+
1 row in set (0.02 sec)

mysql> select 3.24+0.0000695,0.0000000000000000000000004+0.000000000000000000000
09;
+----------------+-------------------------------------------------------+
| 3.24+0.0000695 | 0.0000000000000000000000004+0.00000000000000000000009 |
+----------------+-------------------------------------------------------+
|      3.2400695 |                           0.0000000000000000000000904 |
+----------------+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 5-4 , 4-0.3,0.065-1;
+-----+-------+---------+
| 5-4 | 4-0.3 | 0.065-1 |
+-----+-------+---------+
|   1 |   3.7 |  -0.935 |
+-----+-------+---------+
1 row in set (0.02 sec)

乘法和除法类似，下面介绍的是%
mysql> select 25%7, -33%7;
+------+-------+
| 25%7 | -33%7 |
+------+-------+
|    4 |    -5 |
+------+-------+
1 row in set (0.00 sec)


mysql> use test
Database changed
mysql> select * from toys;
+----+-----------------+-------+-------+-------+--------
| id | item            | ctm   | ctr   | ctc   | quantit
+----+-----------------+-------+-------+-------+--------
|  1 | huggy bear      |  5.00 |  7.40 |  9.90 |      30
|  2 | coloring book   |  3.00 |  5.00 |  7.99 |     200
|  3 | board game      | 12.00 | 20.00 | 39.90 |     143
|  4 | activity cd-rom |  2.00 |  6.00 | 17.49 |     378
+----+-----------------+-------+-------+-------+--------
4 rows in set (0.01 sec)

mysql> select item,ctr-ctm from toys;
+-----------------+---------+
| item            | ctr-ctm |
+-----------------+---------+
| huggy bear      |    2.40 |
| coloring book   |    2.00 |
| board game      |    8.00 |
| activity cd-rom |    4.00 |
+-----------------+---------+
4 rows in set (0.00 sec)

mysql> select item,ctr-ctm as bb from toys;
+-----------------+------+
| item            | bb   |
+-----------------+------+
| huggy bear      | 2.40 |
| coloring book   | 2.00 |
| board game      | 8.00 |
| activity cd-rom | 4.00 |
+-----------------+------+
4 rows in set (0.00 sec)

察看每个商品的利润率

mysql> select item,ctc-ctr,(ctc-ctr)/ctm%100 from toys;
+-----------------+---------+-------------------+
| item            | ctc-ctr | (ctc-ctr)/ctm%100 |
+-----------------+---------+-------------------+
| huggy bear      |    2.50 |          0.500000 |
| coloring book   |    2.99 |          0.996667 |
| board game      |   19.90 |          1.658333 |
| activity cd-rom |   11.49 |          5.745000 |
+-----------------+---------+-------------------+
4 rows in set (0.02 sec)

由厂商提供给零售商的每种产品的折扣


比较运算符

mysql> select 6='6gfh' ,'6'='6gfh';
+----------+------------+
| 6='6gfh' | '6'='6gfh' |
+----------+------------+
|        1 |          0 |
+----------+------------+
1 row in set, 1 warning (0.00 sec)

处理数字和字符串


mysql> select 7 in(1,2,3,4,5,6,7);
+---------------------+
| 7 in(1,2,3,4,5,6,7) |
+---------------------+
|                   1 |


校验一个值（或者一个求值表达式）是否包含在一个指定的值集合中


mysql> select 'red' regexp 'red|green|blue','hammer' regexp 'ham$';
+-------------------------------+------------------------+
| 'red' regexp 'red|green|blue' | 'hammer' regexp 'ham$' |
+-------------------------------+------------------------+
|                             1 |                      0 |
+-------------------------------+------------------------+
1 row in set (0.00 sec)

Regexp 运算符允许我们执行更复杂的字符串比较运算。在这里使用unix 正则表达式。如果找到匹配结果，返回真值。


P115