mysql 学习记录（四）--数值计算、逻辑判断、范围选择、位运算

最新推荐文章于 2021-12-03 21:01:48 发布

bwshqh

最新推荐文章于 2021-12-03 21:01:48 发布

阅读量1.1k

点赞数

分类专栏： mysql mysql学习文章标签：数据库 mysql

本文链接：https://blog.csdn.net/bwshqh/article/details/48878255

版权

mysql 同时被 2 个专栏收录

26 篇文章 0 订阅

订阅专栏

mysql学习

26 篇文章 2 订阅

订阅专栏

1.数值计算
mysql> select 0.1+0.3333,0.1-0.3333,0.1*0.3333,1/2,1%3;
+------------+------------+------------+--------+------+
| 0.1+0.3333 | 0.1-0.3333 | 0.1*0.3333 | 1/2    | 1%3  |
+------------+------------+------------+--------+------+
|     0.4333 |    -0.2333 |    0.03333 | 0.5000 |    1 |
+------------+------------+------------+--------+------+
1 row in set (0.00 sec)

mysql> select 1/0,100%0;
+------+-------+
| 1/0  | 100%0 |
+------+-------+
| NULL |  NULL |
+------+-------+
1 row in set (0.00 sec)

mysql> select 3%2,mod(7,2);
+------+----------+
| 3%2  | mod(7,2) |
+------+----------+
|    1 |        1 |
+------+----------+
1 row in set (0.00 sec)

mysql> select 3%2,mod(7,5);
+------+----------+
| 3%2  | mod(7,5) |
+------+----------+
|    1 |        2 |
+------+----------+
1 row in set (0.00 sec)

2.逻辑判断
mysql> select 1=0,1=1,null=null;
+-----+-----+-----------+
| 1=0 | 1=1 | null=null |
+-----+-----+-----------+
|   0 |   1 |      NULL |
+-----+-----+-----------+
1 row in set (0.00 sec)

mysql> select 1<>0,1<>1,null<>null;
+------+------+------------+
| 1<>0 | 1<>1 | null<>null |
+------+------+------------+
|    1 |    0 |       NULL |
+------+------+------------+
1 row in set (0.00 sec)

mysql> select 1<=>1,2<=>0,0<=>0,null<=>null;
+-------+-------+-------+-------------+
| 1<=>1 | 2<=>0 | 0<=>0 | null<=>null |
+-------+-------+-------+-------------+
|     1 |     0 |     1 |           1 |
+-------+-------+-------+-------------+
1 row in set (0.00 sec)

mysql> select 'bdf'<='b','b'<='b',0<1;
+------------+----------+-----+
| 'bdf'<='b' | 'b'<='b' | 0<1 |
+------------+----------+-----+
|          0 |        1 |   1 |
+------------+----------+-----+
1 row in set (0.00 sec)

mysql> select 'a'>'b','abc'>'a',1>0;
+---------+-----------+-----+
| 'a'>'b' | 'abc'>'a' | 1>0 |
+---------+-----------+-----+
|       0 |         1 |   1 |
+---------+-----------+-----+
1 row in set (0.00 sec)

mysql> select 'a'>='b','abc'>='a',1>=0,1>=1;
+----------+------------+------+------+
| 'a'>='b' | 'abc'>='a' | 1>=0 | 1>=1 |
+----------+------------+------+------+
|        0 |          1 |    1 |    1 |
+----------+------------+------+------+
1 row in set (0.00 sec)

3.范围选择
mysql> select 10 between 10 and 20,9 between 10 and 20;
+----------------------+---------------------+
| 10 between 10 and 20 | 9 between 10 and 20 |
+----------------------+---------------------+
|                    1 |                   0 |
+----------------------+---------------------+
1 row in set (0.00 sec)

mysql> select 1 in (1,2,3) ,'t' in ('t','a','b','l','e') ,0 in (1,2);
+--------------+------------------------------+------------+
| 1 in (1,2,3) | 't' in ('t','a','b','l','e') | 0 in (1,2) |
+--------------+------------------------------+------------+
|            1 |                            1 |          0 |
+--------------+------------------------------+------------+
1 row in set (0.00 sec)

mysql> select 0 is null,null is null;
+-----------+--------------+
| 0 is null | null is null |
+-----------+--------------+
|         0 |            1 |
+-----------+--------------+
1 row in set (0.00 sec)

mysql> select 0 is not null,null is not null;
+---------------+------------------+
| 0 is not null | null is not null |
+---------------+------------------+
|             1 |                0 |
+---------------+------------------+
1 row in set (0.00 sec)

mysql> select 123456 like '123%',123456 like '%123%',123456 like '%321%';
+--------------------+---------------------+---------------------+
| 123456 like '123%' | 123456 like '%123%' | 123456 like '%321%' |
+--------------------+---------------------+---------------------+
|                  1 |                   1 |                   0 |
+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select 'abcdef' regexp 'ab','abcddefg' regexp 'k';
+----------------------+-----------------------+
| 'abcdef' regexp 'ab' | 'abcddefg' regexp 'k' |
+----------------------+-----------------------+
|                    1 |                     0 |
+----------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select not 0,not 1,not null;
+-------+-------+----------+
| not 0 | not 1 | not null |
+-------+-------+----------+
|     1 |     0 |     NULL |
+-------+-------+----------+
1 row in set (0.00 sec)

mysql> select ( 1 and 1),(0 and 1 ),(3 and 1),(1 and null);
+------------+------------+-----------+--------------+
| ( 1 and 1) | (0 and 1 ) | (3 and 1) | (1 and null) |
+------------+------------+-----------+--------------+
|          1 |          0 |         1 |         NULL |
+------------+------------+-----------+--------------+
1 row in set (0.00 sec)

mysql> select ( 1 or 0),(0 or 0),(1 or null),(1 or 1),(null or null);
+-----------+----------+-------------+----------+----------------+
| ( 1 or 0) | (0 or 0) | (1 or null) | (1 or 1) | (null or null) |
+-----------+----------+-------------+----------+----------------+
|         1 |        0 |           1 |        1 |           NULL |
+-----------+----------+-------------+----------+----------------+
1 row in set (0.00 sec)

mysql> select 1 xor 1,0 xor 0,1 xor 0,0 xor 1 ,null xor 1;
+---------+---------+---------+---------+------------+
| 1 xor 1 | 0 xor 0 | 1 xor 0 | 0 xor 1 | null xor 1 |
+---------+---------+---------+---------+------------+
|       0 |       0 |       1 |       1 |       NULL |
+---------+---------+---------+---------+------------+
1 row in set (0.00 sec)

mysql> select 2&3;
+-----+
| 2&3 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

mysql> select 2&3&4;
+-------+
| 2&3&4 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> select 2|3;
+-----+
| 2|3 |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

mysql> select 2^3;
+-----+
| 2^3 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

4.取反、位运算、二进制
mysql> select ~1,~18446744073709551614;
+----------------------+-----------------------+
| ~1                   | ~18446744073709551614 |
+----------------------+-----------------------+
| 18446744073709551614 |                     1 |
+----------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select bin(18446744073709551614);
+------------------------------------------------------------------+
| bin(18446744073709551614)                                        |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 100>>3;
+--------+
| 100>>3 |
+--------+
|     12 |
+--------+
1 row in set (0.00 sec)

mysql> select 100<<3;
+--------+
| 100<<3 |
+--------+
|    800 |
+--------+
1 row in set (0.00 sec)