案例:创建数据表tmp15,其中包含varchar类型的字段note和int类型的字段price。
使用位操作符对数据进行位操作。
(免费学习推荐:mysql视频教程)
首先创建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数据库(视频)
以上就是mysql练习之2:运算符的使用的详细内容,更多请关注 群英网络其它相关文章!
Copyright © QY Network Company Ltd. All Rights Reserved. 2003-2018 群英 版权所有 茂名市群英网络有限公司
增值电信经营许可证 : B1.B2-20140078 粤ICP备09006778号-36 粤公网安备 44090202000006号 粤工商备P091701000595