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

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

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)

版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。