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)
版权声明:本博客原创文章欢迎转载,请转载的朋友最好注明出处,谢谢大家。