这篇文章将为大家详细讲解有关mysql中运算符的使用示例,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。
案例:创建数据表tmp15,其中包含varchar类型的字段note和int类型的字段price。
使用运算符对表tmp15中不同的字段进行运算。
使用逻辑操作符对数据进行逻辑操作。
使用位操作符对数据进行位操作。
首先创建tmp15表,插入一条记录,note值为"Thisisgood",price值为50,SQL语句如下:
mysql> create table tmp15 -> (
-> note varchar(100),
-> price int
-> );Query OK, 0 rows affected (0.13 sec)mysql> into tmp15 values
-> (
-> "Thisisgood",50
-> );
mysql> insert into tmp15 values
-> ("Thisisgood",50);Query OK, 1 row affected (0.06 sec)
(1)对表tmp15中的整型数值字段price进行算数运算,SQL语句如下:
mysql> 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 |+-------+------------+------------+-----------+-----------+-----------+1 row in set (0.00 sec)
(2)对表tmp15中的整型数值字段price进行比较运算,SQL语句如下:
mysql> select price,
-> price>10,
-> price<10,
-> price != 10,
-> price = 10,
-> price<=>10,
-> price<>10
-> from tmp15;+-------+----------+----------+-------------+------------+------------+-----------+| price | price>10 | price<10 | price != 10 | price = 10 | price<=>10 | price<>10 |+-------+----------+----------+-------------+------------+------------+-----------+| 50 | 1 | 0 | 1 | 0 | 0 | 1 |+-------+----------+----------+-------------+------------+------------+-----------+1 row in set (0.00 sec)
(3)判断price值是否落在30—80区间、返回70、30相比最大的值、判断price是否为in列表(10、20、50、35)中的某个值,SQL语句如下:
mysql> 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 |+-------+-------------------------+-----------------------+-----------------------+1 row in set (0.00 sec)
(4)对tmp15中的字符串数值字段note进行比较运算,判断表tmp15中note字段是否为空、使用LIKE判断是否以字母"t"开头、使用regexp判断是否以字母“y”结尾、判断是否包含字母“g”或者“m”,SQL语句如下:
mysql> select note,
-> note is null,
-> note like 't%',
-> note regexp '$y',
-> note regexp '[gm]'
-> from tmp15;+------------+--------------+----------------+------------------+--------------------+| note | note is null | note like 't%' | note regexp '$y' | note regexp '[gm]' |+------------+--------------+----------------+------------------+--------------------+| Thisisgood | 0 | 1 | 0 | 1 |+------------+--------------+----------------+------------------+--------------------+1 row in set (0.05 sec)
(5)将price字段值与null、0进行逻辑运算,SQL语句如下:
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)
(6)将price字段值与2、4进行按位与、按位或 操作,并对price进行按位操作,SQL语句如下:
mysql> select price,
-> price & 2,
-> price | 4,
-> ~price from tmp15;+-------+-----------+-----------+----------------------+| price | price & 2 | price | 4 | ~price |+-------+-----------+-----------+----------------------+| 50 | 2 | 54 | 18446744073709551565 |+-------+-----------+-----------+----------------------+1 row in set (0.00 sec)
(7)将price字段值分别额左移和右移两位,SQL语句如下:
mysql> select price,
-> price<<2,
-> price>>2
-> from tmp15;+-------+----------+----------+| price | price<<2 | price>>2 |+-------+----------+----------+| 50 | 200 | 12 |+-------+----------+----------+1 row in set (0.00 sec)
关于“mysql中运算符的使用示例”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。