MySQL运算符运算符简介
运算符是用来连接表达式中各个操作数的符号
算术运算符，比较运算符，逻辑运算符和位运算符
算术运算符:[加减乘除和求余]
/ x1/x2 除法运算,返回x1除以x2的商
DIV X1 DIV x2 除法运算,返回x1除以x2的商
% x1%x2 求余运算,返回x1除以x2的余数
MOD MOD(x1,x2) 求余运算,返回x1除以x2的余数
eg:
mysql> select * from t1;
+------+------+------+
| a | b | c |
+------+------+------+
| 30 | 40 | 50 |
+------+------+------+
1 row in set (0.00 sec)
mysql> selecta,a+5+2,a-5-2,a*5*2 from t1;
+------+-------+-------+-------+
| a | a+5+2 | a-5-2 | a*5*2 |
+------+-------+-------+-------+
| 30 | 37 | 23 | 300 |
+------+-------+-------+-------+
1 row in set (0.00 sec)
比较运算符：
= x1=x2 判断x1是否等于x2
<>或!= x1<>x2 x1!=x2 判断x1是否不等于x2
<=> x1<=>x2 判断x1是否等于x2
> x1>x2 判断x1是否大于x2
>= x1>=x2 判断x1是否大于等于x2
< x1<x2 判断x1是否小于x2
<= x1<=x2 判断x1是否小于等于x2
IS NULL x1 IS NULL 判断x1是否等于NULL
IS NOT NULL X1 IS NOT NULL 判断x1是否不等于NULL
BETWEEN AND x1 BETWEEN m AND n 判断x1的取值是否落在m和n之间
IN x1 IN (值1，值2,值3,值n) 判断x1的取值是否是值1到值n中的一个
LIKEx1 LIKE 表达式 判断x1是否与表达式匹配
REGEXP x1 REGEXP 正则表达式判断x1是否与正则表达式匹配
mysql> select a,a=30,a=20from t1;
+------+------+------+
| a | a=30 | a=20 |
+------+------+------+
| 30 | 1 | 0 |
+------+------+------+
1 row in set (0.01 sec)
mysql> select a,a=29,NULL=NULLfrom t1; //不能用来判断空值
+------+------+-----------+
| a | a=29 | NULL=NULL |
+------+------+-----------+
| 30 | 0 | NULL |
+------+------+-----------+
1 row in set (0.01 sec)
mysql> select a ,a<>20,a!=23,a!=NULLFROM t1; //不能用来判断空值
+------+-------+-------+---------+
| a | a<>20 | a!=23 | a!=NULL |
+------+-------+-------+---------+
| 30 | 1 | 1 | NULL |
+------+-------+-------+---------+
1 row in set (0.00 sec)
mysql> selecta,a<=>30,NULL<=>NULL from t1; // <=>可以用来判断空值
+------+--------+-------------+
| a | a<=>30 | NULL<=>NULL |
+------+--------+-------------+
| 30 | 1 | 1 |
+------+--------+-------------+
1 row in set (0.01 sec)
mysql> select a,a>20,null>nullfrom t1; //不能用来判断空值
+------+------+-----------+
| a | a>20 | null>null |
+------+------+-----------+
| 30 | 1 | NULL |
+------+------+-----------+
1 row in set (0.01 sec)
mysql> select a,a>=30 ,null>=nullfrom t1; //不能用来判断空值
+------+-------+------------+
| a | a>=30 | null>=null |
+------+-------+------------+
| 30 | 1 | NULL |
+------+-------+------------+
1 row in set (0.01 sec)
mysql> select a,a<=40,null<=nullfrom t1; //不能用来判断空值
+------+-------+------------+
| a | a<=40 | null<=null |
+------+-------+------------+
| 30 | 1 | NULL |
+------+-------+------------+
1 row in set (0.00 sec)
mysql> select a,a is null,ais not null from t1;
+------+-----------+---------------+
| a | a is null | a is not null |
+------+-----------+---------------+
| 30 | 0 | 1 |
+------+-----------+---------------+
1 row in set (0.00 sec)
mysql> select a,a between 10and 40, a between 40 and 50 from t1;
+------+---------------------+---------------------+
| a | a between 10 and 40 | a between 40 and 50|
+------+---------------------+---------------------+
| 30 | 1 | 0 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select a ,a in(0,20,30)from t1;
+------+---------------+
| a | a in(0,20,30) |
+------+---------------+
| 30 | 1 |
+------+---------------+
1 row in set (0.00 sec)
mysql> select a,a like'beijing' from t2; //
+---------+------------------+
| a | a like 'beijing' |
+---------+------------------+
| beijing | 1 |
+---------+------------------+
1 row in set (0.00 sec)
mysql> select a,a regexp '^b'from t2;
+---------+---------------+
| a | a regexp '^b' |
+---------+---------------+
| beijing | 1 |
+---------+---------------+
1 row in set (0.01 sec)
逻辑运算符
&& 或者 AND 与 可以有多个操作数进行与运算
|| 或者 OR 或
! 或者 NOT 非
XOR 异或 //只要其中任何一个操作数为null时，结果返回null,如果x1和x2都是非0的数字或者都是0时，结果返回0,如果x1和x2中一个是非0，另一个是0时，结果返回1 ,所有大于-1小于1的数字都被视为逻辑0,其他数字视为逻辑1
mysql> select -1 and 2,3&&2,0 and null,3 and null;
+----------+------+------------+------------+
| -1 and 2 | 3&&2 | 0 andnull | 3 and null |
+----------+------+------------+------------+
| 1 | 1 | 0 | NULL |
+----------+------+------------+------------+
1 row in set (0.00 sec)
mysql> select1||-1||null||0,3||null,0||null,null|null,0||0;
+----------------+---------+---------+-----------+------+
| 1||-1||null||0 | 3||null |0||null | null|null | 0||0 |
+----------------+---------+---------+-----------+------+
| 1 | 1 | NULL | NULL | 0 |
+----------------+---------+---------+-----------+------+
1 row in set (0.00 sec)
mysql> select!1,!0.3,!-3,!0,!null;
+----+------+-----+----+-------+
| !1 | !0.3 | !-3 | !0 | !null |
+----+------+-----+----+-------+
| 0 | 0 | 0 | 1| NULL |
+----+------+-----+----+-------+
1 row in set (0.01 sec)
位运算符
位运算符是在二进制数上进行计算的运算符，位运算会先将操作数变成二进制数，然后进行位运算，最后再将计算结果从二进制数变回十进制数
按位与 &，按位或|，
按位取反~
将操作数化为二进制数后，每位都进行取反运算,1取反后变成0, 0取反后变成1
按位异或^
每位都进行异或，相同的数异或之后结果是0,不同的数异或之后结果为1
接位左移<<和按位右移>>
mysql> select5&6,5&6&7;
+-----+-------+
| 5&6 | 5&6&7 |
+-----+-------+
| 4 | 4 |
+-----+-------+
1 row in set (0.00 sec)
5转换成二进制 101
6转换成二进制 110
5和6相与为 100
100十进制数为 4
mysql> select 5|6;
+-----+
| 5|6 |
+-----+
| 7 |
+-----+
1 row in set (0.00 sec)
5转换成二进制 101
6转换成二进制 110
5和6相或为 111
十进制数为 7
mysql> select ~1;
+----------------------+
| ~1 |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.00 sec)
mysql> select bin(~1);
+------------------------------------------------------------------+
| bin(~1) |
+------------------------------------------------------------------+
|1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select5<<2,5>>2;
+------+------+
| 5<<2 | 5>>2 |
+------+------+
| 20 | 1 |
+------+------+
1 row in set (0.01 sec)
5转换成二进制 101
左移2位变成10100
右移2位变成001
运算符的优先级
优先极 运算符
1 !
2 ~
3 ^
4 *,/,DIV,%,MOD
5 +,-
6 >>,<<
7 &
8 |
9 =,<=>,<,<=,>,>=,!=,<>,IN,ISNULL,LIKE,REGEXP
10 BETWEENAND ,CASE,WHEN,THEN,ELSE
11 NOT
12 &&,AND
13 ||,OR,XOR
14 :=
