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