优先级从低到高排列:
1. =(赋值运算)
2. =(算术运算符)
3. || ,OR
4. XOR
5. && ,AND
6. NOT
7. BETWEEN , CASE , WHEN , THEN , ELSE
8. =(比较运算),<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
9. |
10. &
11. <<,>>
12. -,+
13. *,/(DIV),%(MOD)
14. ^
15. -(负号),~(位反转)
16. !
在无法确定优先级的情况下,可以使用括号 ( ) 来改变优先级,这样会使计算过程更加清晰。
运算符 | 作用 |
+ | 加法运算 |
- | 减法运算 |
* | 乘法运算 |
/ | 除法运算,返回商 |
% | 求余运算,返回余数 |
例子:
- mysql> create table tmp14 ( num int ); //创建表tmp14,定义数据类型为INT的字段num
- mysql> insert into tmp14 value(64); //插入值64
- mysql> select num, num+10, num-3+5, num+5-3, num+36.5 from tmp14; //对num值进行加减法运算
- +------+--------+---------+---------+----------+
- | num | num+10 | num-3+5 | num+5-3 | num+36.5 |
- +------+--------+---------+---------+----------+
- | 64 | 74 | 66 | 66 | 100.5 |
- +------+--------+---------+---------+----------+
- mysql> select num, num*2, num/2, num/3, num%3 from tmp14; //对num值进行乘除法运算
- +------+-------+---------+---------+-------+
- | num | num*2 | num/2 | num/3 | num%3 |
- +------+-------+---------+---------+-------+
- | 64 | 128 | 32.0000 | 21.3333 | 1 |
- +------+-------+---------+---------+-------+
- mysql> select num, num/0, num%0 from tmp14; //除数为0的除法是没有意义的,返回结果为NULL
- +------+-------+-------+
- | num | num/0 | num%0 |
- +------+-------+-------+
- | 64 | NULL | NULL |
- +------+-------+-------+
运算符 | 作用 |
= | 等于 |
<=> | 安全的等于 |
<> 或 != | 等于 |
<= | 小于或等于 |
>= | 大于或等于 |
> | 大于 |
< | 小于 |
IS NULL 或 ISNULL | 判断一个值是否为 NULL |
IS NOT NULL | 判断一个值是否不为 NULL |
LEAST | 在有两个或多个参数时,返回最小值 |
CREATEST | 在有两个或多个参数时,返回最大值 |
BETWEEN AND | 判断一个值是否落在两个值之间 |
IN | 判断一个值是 IN 列表中的任意一个值 |
NOT IN | 判断一个值不是 IN 列表中的任意一个值 |
LIKE | 通配符匹配 |
REGEXP | 正则表达式匹配 |
等号(=)用来判断数字、字符串、表达式是否相等,如果相等,返回值为 1,否则返回值为 0,数值比较时有如下规则:
1. 若有一个或两个参数为 NULL,则比较运算的结果为 NULL
2. 若同一个比较运算中的两个参数都是字符串,则按照字符串进行比较
3. 若两个参数均为整数,则按照整数进行比较
4. 若一个字符串和一个数字进行比较,则 MySQL 可以自动将字符串转换为数字
- mysql> select 1<=>0, '2'<=>2, '0.02'<=>0, 'b'<=>'b', (1+3) <=> (2+2), NULL<=>NUL L;
- +-------+---------+------------+-----------+-----------------+-------------+
- | 1<=>0 | '2'<=>2 | '0.02'<=>0 | 'b'<=>'b' | (1+3) <=> (2+2) | NULL<=>NULL |
- +-------+---------+------------+-----------+-----------------+-------------+
- | 0 | 1 | 0 | 1 | 1 | 1 |
- +-------+---------+------------+-----------+-----------------+-------------+
"<>" 或 "!=" 用于判断数字、字符串、表达式不想等的判断,如果不相等,返回值为 1,否则返回 0,这两个运算符不能用于判断空值 NULL
- mysql> select 'good'<>'god', 1<>2, 4!=4, 5.5!=5, (1+3)!=(2+1), NULL<>NULL;
- +---------------+------+------+--------+--------------+------------+
- | 'good'<>'god' | 1<>2 | 4!=4 | 5.5!=5 | (1+3)!=(2+1) | NULL<>NULL |
- +---------------+------+------+--------+--------------+------------+
- | 1 | 1 | 0 | 1 | 1 | NULL |
- +---------------+------+------+--------+--------------+------------+
"<=" 用来判断左边的操作数是否小于或者等于右边的操作数,如果小于或者等于,返回值为 1,否则返回值为 0,"<=" 不能用于判断空值 NULL
- mysql> select 'good'<='god', 1<=2, 4<=4, 5.5<=5, (1+3) <= (2+1), NULL<=NULL;
- +---------------+------+------+--------+----------------+------------+
- | 'good'<='god' | 1<=2 | 4<=4 | 5.5<=5 | (1+3) <= (2+1) | NULL<=NULL |
- +---------------+------+------+--------+----------------+------------+
- | 0 | 1 | 1 | 0 | 0 | NULL |
- +---------------+------+------+--------+----------------+------------+
其他的运算符(>=、>、<)都是同理的,不再举例子
- mysql> select NULL IS NULL, ISNULL(NULL), ISNULL(10), 10 IS NOT NULL; //注意格式
- +--------------+--------------+------------+----------------+
- | NULL IS NULL | ISNULL(NULL) | ISNULL(10) | 10 IS NOT NULL |
- +--------------+--------------+------------+----------------+
- | 1 | 1 | 0 | 1 |
- +--------------+--------------+------------+----------------+
语法格式为:expr BETWEEN min AND max,假如 expr 大于或等于 min 且小于或等于 max,则 BETWEEN 的返回值为 1,否则返回值为 0
- mysql> SELECT 4 BETWEEN 2 AND 5, 4 BETWEEN 4 AND 6, 12 BETWEEN 9 AND 10;
- +-------------------+-------------------+---------------------+
- | 4 BETWEEN 2 AND 5 | 4 BETWEEN 4 AND 6 | 12 BETWEEN 9 AND 10 |
- +-------------------+-------------------+---------------------+
- | 1 | 1 | 0 |
- +-------------------+-------------------+---------------------+
- mysql> SELECT 'x' BETWEEN 'f' AND 'g', 'b' BETWEEN 'a' AND 'c';
- +-------------------------+-------------------------+
- | 'x' BETWEEN 'f' AND 'g' | 'b' BETWEEN 'a' AND 'c' |
- +-------------------------+-------------------------+
- | 0 | 1 |
- +-------------------------+-------------------------+
LEAST:在有两个或多个参数时,返回最小值,语法格式:LEAST(值 1,值 2,......,值 n) ,假如任意一个自变量为 NULL,则 LEAST ( ) 的返回值为 NULL
- mysql> select least(2,0), least(20.0,3.0,100.5), least('a','c','b'), least(10,NU LL);
- +------------+-----------------------+--------------------+----------------+
- | least(2,0) | least(20.0,3.0,100.5) | least('a','c','b') | least(10,NULL) |
- +------------+-----------------------+--------------------+----------------+
- | 0 | 3.0 | a | NULL |
- +------------+-----------------------+--------------------+----------------+
- mysql> select greatest(2,0), greatest(20.0,3.0,100.5), greatest('a','c','b'), gr eatest(10,NULL);
- +---------------+--------------------------+-----------------------+------------ -------+
- | greatest(2,0) | greatest(20.0,3.0,100.5) | greatest('a','c','b') | greatest(10 ,NULL) |
- +---------------+--------------------------+-----------------------+------------ -------+
- | 2 | 100.5 | c | NULL |
- +---------------+--------------------------+-----------------------+------------ -------+
- mysql> select 2 in (1,3,5,'thks'), 'thks' in (1,3,5,'thks');
- +---------------------+--------------------------+
- | 2 in (1,3,5,'thks') | 'thks' in (1,3,5,'thks') |
- +---------------------+--------------------------+
- | 0 | 1 |
- +---------------------+--------------------------+
- mysql> select 2 not in (1,3,5,'thks'), 'thks' not in (1,3,5,'thks');
- +-------------------------+------------------------------+
- | 2 not in (1,3,5,'thks') | 'thks' not in (1,3,5,'thks') |
- +-------------------------+------------------------------+
- | 1 | 0 |
- +-------------------------+------------------------------+
- mysql> select 'stud' like 'stud', 'stud' like 'stu_', 'stud' like '%d', 'stud' l ike 't___', 's' like NULL;
- +--------------------+--------------------+------------------+------------------ --+---------------+
- | 'stud' like 'stud' | 'stud' like 'stu_' | 'stud' like '%d' | 'stud' like 't___ ' | 's' like NULL |
- +--------------------+--------------------+------------------+------------------ --+---------------+
- | 1 | 1 | 1 | 0 | NULL |
- +--------------------+--------------------+------------------+------------------ --+---------------+
- mysql> select 'ssky' regexp '^s', 'ssky' regexp 'y$', 'ssky' regexp '.sky', 'ssk y' regexp '[ab]';
- +--------------------+--------------------+----------------------+-------------- --------+
- | 'ssky' regexp '^s' | 'ssky' regexp 'y$' | 'ssky' regexp '.sky' | 'ssky' regexp '[ab]' |
- +--------------------+--------------------+----------------------+-------------- --------+
- | 1 | 1 | 1 | 0 |
- +--------------------+--------------------+----------------------+-------------- --------+
运算符 | 作用 |
NOT 或 ! | 逻辑非 |
AND 或 && | 逻辑与 |
OR 或 || | 逻辑或 |
XOR | 逻辑异或 |
逻辑非运算符 NOT 或者 ! 表示当操作数为 0 时,所得的返回值为 1;当操作数为非零值时,所得的返回值为 0;当操作数为 NULL 时,所得的返回值为 NULL
- mysql> select not 10, not (1-1), not -5, not null, not 1+1;
- +--------+-----------+--------+----------+---------+
- | not 10 | not (1-1) | not -5 | not null | not 1+1 |
- +--------+-----------+--------+----------+---------+
- | 0 | 1 | 0 | NULL | 0 |
- +--------+-----------+--------+----------+---------+
- mysql> select ! 10, ! (1-1), ! -5, ! null, ! 1+1;
- +------+---------+------+--------+-------+
- | ! 10 | ! (1-1) | ! -5 | ! null | ! 1+1 |
- +------+---------+------+--------+-------+
- | 0 | 1 | 0 | NULL | 1 |
- +------+---------+------+--------+-------+
- //注意:由结果可以看到,前4列 NOT 和 ! 的返回值都相同,但是注意最后一列,为什么会出现不同的值呢?这是因为 NOT 与 ! 的优先级不同,NOT 的优先级低于 + , 因此 NOT 1+1 相当于 NOT(1+1),先计算 1+1 , 然后再进行 NOT 运算,因为操作数不为 0 , 因此 NOT 1+1 的结果是 0 ; 相反,由于 ! 的优先级要高于 + 运算,因此 ! 1+1 相当于 (!1)+1 ,先计算 !1 结果为 0 ,再加 1 ,最后结果为 1 。 因此在使用运算符运算时,一定要注意不同运算符的优先级不同,如果不能确定计算顺序,最好使用括号,以保证运算结果的正确。
- mysql> select 1 and -1, 1 and 0, 1 and NULL, 0 and NULL;
- +----------+---------+------------+------------+
- | 1 and -1 | 1 and 0 | 1 and NULL | 0 and NULL |
- +----------+---------+------------+------------+
- | 1 | 0 | NULL | 0 |
- +----------+---------+------------+------------+
- mysql> select 1 && -1 , 1 && 0 , 1 && NULL , 0 and NULL ;
- +---------+--------+-----------+------------+
- | 1 && -1 | 1 && 0 | 1 && NULL | 0 and NULL |
- +---------+--------+-----------+------------+
- | 1 | 0 | NULL | 0 |
- +---------+--------+-----------+------------+
- mysql> select 1 or -1 or 0 , 1 or 2 , 1 or null , 0 or null , null or null ;
- +--------------+--------+-----------+-----------+--------------+
- | 1 or -1 or 0 | 1 or 2 | 1 or null | 0 or null | null or null |
- +--------------+--------+-----------+-----------+--------------+
- | 1 | 1 | 1 | NULL | NULL |
- +--------------+--------+-----------+-----------+--------------+
- mysql> select 1 || -1 || 0 , 1 || 2 , 1 || null , 0 || null , null || null;
- +--------------+--------+-----------+-----------+--------------+
- | 1 || -1 || 0 | 1 || 2 | 1 || null | 0 || null | null || null |
- +--------------+--------+-----------+-----------+--------------+
- | 1 | 1 | 1 | NULL | NULL |
- +--------------+--------+-----------+-----------+--------------+
- mysql> select 1 xor 1 , 0 xor 0 , 1 xor 0 , 1 xor null , 1 xor 1 xor 1 ;
- +---------+---------+---------+------------+---------------+
- | 1 xor 1 | 0 xor 0 | 1 xor 0 | 1 xor null | 1 xor 1 xor 1 |
- +---------+---------+---------+------------+---------------+
- | 0 | 0 | 1 | NULL | 1 |
- +---------+---------+---------+------------+---------------+
- //注意最后一段,1 xor 1 xor 1 ,运算顺序从左到右依次计算,1 xor 1 的结果为 0 ,再与 1 进行异或运算,因此结果为 1
运算符 | 作用 |
| | 位或 |
& | 位与 |
^ | 位异或 |
<< | 位左移 |
>> | 位右移 |
~ | 位取反,反转所有比特 |
位或运算的实质是将参与运算的几个数据,按对应的二进制数逐位进行逻辑或运算,对应的二进制位有一个或两个为 1,则该为的运算结果为 1, 否则为 0
- mysql> select 10 | 15 , 9 | 4 | 2 ;
- +---------+-----------+
- | 10 | 15 | 9 | 4 | 2 | //10的二进制数值为1010,15的二进制数值为1111,按位或运算之后,结果为1111,即整数15
- +---------+-----------+
- | 15 | 15 | //9的二进制数值为1001,4的二进制数值为0100,2的二进制数值为0010,位或结果为1111,即整数15
- +---------+-----------+
位与运算的实质是将参与运算的几个操作数,按对应的二进制数逐位进行逻辑与运算,对应的二进制位都为 1,则该位的运算结果为 1,否则为 0
- mysql> select 10 & 15 , 9 & 4 & 2 ;
- +---------+-----------+ 9:1001
- | 10 & 15 | 9 & 4 & 2 | 10:1010 4:1001
- +---------+-----------+ 15:1111 2:0100
- | 10 | 0 | -------- --------
- +---------+-----------+ 1010 0000
- mysql> select 10 ^ 15 , 1 ^ 0 , 1 ^ 1 ;
- +---------+-------+-------+ 10:1010 1:0001 1:0001
- | 10 ^ 15 | 1 ^ 0 | 1 ^ 1 | 15:1111 0:0000 1:0001
- +---------+-------+-------+ ------- ------ ------
- | 5 | 1 | 0 | 0101 0001 0000
- +---------+-------+-------+
位左移运算符 << 使指定的二进制值得所有位都左移指定的位数,左移指定位数之后,左边高位的数值将被移出并丢弃,右边低位空出的位置用 0 补齐
- mysql> select 1<<2 , 4<<2 ;
- +------+------+
- | 1<<2 | 4<<2 | 1 的二进制值为 0000 0001 ,左移两位之后变成 0000 0100 ,即十进制整数 4
- +------+------+
- | 4 | 16 | 4 的二进制值为 0000 0100 ,左移两位之后变成 0001 0000 ,即十进制整数 16
- +------+------+
位右移运算符 >> 使指定的二进制值得所有位都右移指定的位数,右移指定位数之后,右边低位的数值将被移出并丢弃,左边高位空出的位置用 0 补齐
- mysql> select 1>>1 , 16>>2 ;
- +------+-------+
- | 1>>1 | 16>>2 | 1 的二进制值为 0000 0001 ,右移一位之后变成 0000 0000 ,即十进制整数 0
- +------+-------+
- | 0 | 4 | 16 的二进制值为 0001 0000 ,右移两位之后变成 0000 0100 ,即十进制整数 4
- +------+-------+
位取反运算的实质是将参与运算的数据,按对应的二进制数逐位反转,即 1 取反后变为 0,0 取反后变为 1
- mysql> select 5 & ~1 ;
- +--------+
- | 5 & ~1 | //由于位取反运算符的优先级高于位与运算符,因此先对1做取反操作,然后再与十进制数值5进行与运算
- +--------+
- | 4 |
- +--------+
- mysql> select bin(~1); //使用bin()函数可以查看1取反之后的结果
- +------------------------------------------------------------------+
- | bin(~1) |
- +------------------------------------------------------------------+
- | 1111111111111111111111111111111111111111111111111111111111111110 |
- +------------------------------------------------------------------+
来源: http://www.bubuko.com/infodetail-1987464.html