mysql=====mysql数据运算（2）
1. 案例目的创建数据表，并对表中的数据进行运算操作，掌握各种运算符的使用方法。
创建表tmp15，其中包含VARCHAR类型的字段note和INT类型的字段price，使用运算符对表tmp15中不同的字段进行运算；使用逻辑操作符对数据进行逻辑操作；使用位操作符对数据进行位操作。
本案例使用数据表tmp15，首先创建该表，SQL语句如下：
CREATE TABLE tmp15 (note VARCHAR(100), price INT);
向表中插入一条记录，note值为“Thisisgood”，price值为50，SQL语句如下：
INSERT INTO tmp15 VALUES(“Thisisgood”, 50);
2. 案例操作过程
对表tmp15中的整型数值字段price进行算术运算，执行过程如下：
SELECT price, price + 10, price -10, price * 2, price /2, price%3 FROM tmp15 ;
+--------+------------+-----------+-----------+-----------+---------+
| price| price + 10 | price -10 | price * 2 | price /2 | price%3 |
+--------+------------+-----------+-----------+-----------+---------+
| 50 | 60 | 40 | 100 |25.0000 | 2 |
+--------+------------+-----------+-----------+-----------+---------+
对表tmp15中的整型数值字段price进行比较运算，执行过程如下：
SELECT price, price> 10, price 10 | price SELECT price, price BETWEEN 30 AND 80, GREATEST(price, 70,30), price IN (10, 20, 50,35) FROM tmp15 ;
+--------+------------------------------------+--------------------------------+----------------------------+
| price| price BETWEEN 30 AND 80 | GREATEST(price, 70,30) | price IN (10, 20, 50,35) |
+--------+------------------------------------+--------------------------------+----------------------------+
| 50 | 1 | 70 | 1 |
+--------+------------------------------------+--------------------------------+----------------------------+
对tmp15中的字符串数值字段note进行比较运算，判断表tmp15中note字段是否为空；使用LIKE判断是否以字母’t’开头；使用REGEXP判断是否以字母’y’结尾；判断是否包含字母’g’或者’m’，执行过程如下：
mysql> SELECT note, note IS NULL, note LIKE 't%', note REGEXP '$y' ,note REGEXP '' FROM tmp15 ;
+--------------+----------------+-------------------+-----------------------+--------------------------+
| note | note IS NULL | note LIKE 't%' | note REGEXP '$y' | note REGEXP '' |
+--------------+----------------+------------------+------------------------+--------------------------+
| Thisisgood | 0 | 1 | 0 | 1 |
+--------------+-----------------+------------------+----------------------+---------------------------+
将price字段值与NULL，0进行逻辑运算，执行过程如下：
mysql> SELECT price, price && 1, price && NULL, price||0, price AND 0, 0 AND NULL, price OR NULL FROM tmp15 ;
+--------+--------------+--------------------+----------+---------------+------------------+--------------------+
| price| price && 1 | price && NULL | price||0 | price AND 0 | 0 AND NULL | price OR NULL |
+--------+--------------+--------------------+----------+---------------+------------------+--------------------+
| 50 | 1 | NULL | 1 | 0 | 0 | 1 |
+--------+--------------+-------------------+-----------+---------------+------------------+--------------------+
1 row in set (0.00 sec)
mysql>SELECT price,!price,NOT NULL,price XOR 3, 0 XOR NULL, price XOR 0 FROM tmp15 ;
+--------+--------+--------------+----------------+-----------------+-----------------+
| price| !price | NOT NULL | price XOR 3 | 0 XOR NULL | price XOR 0 |
+--------+--------+--------------+----------------+-----------------+-----------------+
| 50 | 0 | NULL | 0 | NULL | 1 |
+--------+--------+--------------+----------------+-----------------+-----------------+
1 row in set (0.00 sec)
将price字段值与2、4进行按位与、按位或操作，并对price进行按位操作，执行过程如下：
mysql> SELECT price, price&2 , price|4, ~price FROM tmp15 ;
+--------+----------+---------+-----------------------------+
| price| price&2 | price|4 | ~price |
+--------+----------+---------+-----------------------------+
| 50 | 2 | 54 | 18446744073709551565 |
+--------+----------+---------+------------------------------+
将price字段值分别左移和右移两位，执行过程如下：
mysql> SELECT price, price2FROM tmp15 ;
+--------+------------+-----------+
| price| price 2 |
+--------+------------+-----------+
| 50 | 200 | 12 |
+--------+------------+-----------+
页:
[1]