算数运算符

符号 表达式 作用
+ X1+X2 加法
- X1-X2 减法
* X1*X2 乘法
/ X1/X2 除法
DIV X1 DIV X2 除法
% X1 % X2 取余
MOD MOD(X1,X2) 取余 
1
2
3
4
5
6
7
mysql> SELECT 1+1,1-1,2*4,3/8;
+-----+-----+-----+--------+
| 1+1 | 1-1 | 2*4 | 3/8    |
+-----+-----+-----+--------+
|   2 |   0 |   8 | 0.3750 |
+-----+-----+-----+--------+
1 row in set (0.00 sec)

当除数为0时，结果为 NULL。

比较运算符

符号 形式 作用
= X1=X2 判断是否相等
<>或!= X1<>X2或X1!=X2 判断是否不相等
<=> X1<=>X2 判断是否相等(包括NULL)
>、>= X1>X2、X1>=X2 判断是否大于等于
<、<= X1<X2、X1<=X2 判断是否小于等于
IS NULL、IS NOT NULL X1 IS NULL、X1 IS NOT NULL 判断是否等于NULL
BETWEEN AND 或 NOT BETWEEN X1 BETWEEN m AND n 判断是否在范围内
IN 或 NOT IN X1 IN(值1,…) 判断是否在一固定范围内
LIKE 或者 NOT LIKE X1 LIKE 表达式 判断是否匹配
REGEXP X1 REGEXP 正则 判断是否匹配正则

比较运算符的结果为真或者假。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> SELECT username,username='king' FROM student;
+----------+-----------------+
| username | username='king' |
+----------+-----------------+
| king     |               1 |
| king1    |               0 |
| king2    |               0 |
| king3    |               0 |
| king4    |               0 |
| king5    |               0 |
| king6    |               0 |
| king7    |               0 |
| king8    |               0 |
+----------+-----------------+
9 rows in set (0.02 sec)

mysql> SELECT id,username,score,score>=70 FROM student;
+----+----------+-------+-----------+
| id | username | score | score>=70 |
+----+----------+-------+-----------+
|  1 | king     |    95 |         1 |
|  2 | king1    |    35 |         0 |
|  3 | king2    |    45 |         0 |
|  4 | king3    |    55 |         0 |
|  5 | king4    |    65 |         0 |
|  6 | king5    |    75 |         1 |
|  7 | king6    |    80 |         1 |
|  8 | king7    |    90 |         1 |
|  9 | king8    |    25 |         0 |
+----+----------+-------+-----------+
9 rows in set (0.00 sec)

mysql> SELECT 's' LIKE '_';
+--------------+
| 's' LIKE '_' |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

逻辑运算符

符号 形式 作用
&& 或 AND 与 并且
|| 或 OR 或 或者
! 或 NOT 非 取反
XOR 异或 不同为真 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SELECT 2&&2,2&&0,2&&NULL,1||1,1||0,1||NULL,0||NULL;
+------+------+---------+------+------+---------+---------+
| 2&&2 | 2&&0 | 2&&NULL | 1||1 | 1||0 | 1||NULL | 0||NULL |
+------+------+---------+------+------+---------+---------+
|    1 |    0 |    NULL |    1 |    1 |       1 |    NULL |
+------+------+---------+------+------+---------+---------+
1 row in set (0.01 sec)

mysql> SELECT !1,!0,!NULL;
+----+----+-------+
| !1 | !0 | !NULL |
+----+----+-------+
|  0 |  1 |  NULL |
+----+----+-------+
1 row in set (0.00 sec)

mysql> SELECT 1 XOR 0,0 XOR 1 ,1 XOR 1,0 XOR 0;
+---------+---------+---------+---------+
| 1 XOR 0 | 0 XOR 1 | 1 XOR 1 | 0 XOR 0 |
+---------+---------+---------+---------+
|       1 |       1 |       0 |       0 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

运算符的优先级

优先级 运算符 优先级 运算符
1 ! 8 |
2 ~ 9 =,<=>,<,<=,>,>=,!=,<>,IN,IS NULL,LIKE,REGEXP
3 ^ 10 BETWEEN AND,CASE,WHEN,THEN,ELSE
4 *,/,DIV,%,MOD 11 NOT
5 +,- 12 &&,AND
6 >>,<< 13 ||,OR,XOR
7 & 14 ;=

可以通过括号()改变优先级。