MySQL比较运算符一览表(带解析)
当使用 SELECT 语句进行查询时,MySQL 允许用户对表达式的左边操作数和右边操作数进行比较,比较结果为真,则返回 1,为假则返回 0,比较结果不确定则返回 NULL。MySQL 支持的比较运算符如下表所示。
运算符 | 作用 |
---|---|
= | 等于 |
<=> | 安全的等于 |
<> 或者 != | 不等于 |
<= | 小于等于 |
>= | 大于等于 |
> | 大于 |
IS NULL 或者 ISNULL | 判断一个值是否为空 |
IS NOT NULL | 判断一个值是否不为空 |
BETWEEN AND | 判断一个值是否落在两个值之间 |
比较运算符可以用于比较数字、字符串和表达式的值。注意,字符串的比较是不区分大小写的。
1) 等于运算(=)
= 运算符用来比较两边的操作数是否相等,相等的话返回 1,不相等的话返回 0。具体的语法规则如下:
- 若有一个或两个操作数为 NULL,则比较运算的结果为 NULL。
- 若两个操作数都是字符串,则按照字符串进行比较。
- 若两个操作数均为整数,则按照整数进行比较。
- 若一个操作数为字符串,另一个操作数为数字,则 MySQL 可以自动将字符串转换为数字。
注意:NULL 不能用于 = 比较。
【实例1】使用 = 进行相等判断,SQL 语句如下:
mysql> SELECT 1=0,'2'=2,2=2,'0.02'=0,'b'='b',(1+3)=(2+2),NULL=null; +-----+-------+-----+----------+---------+-------------+-----------+ | 1=0 | '2'=2 | 2=2 | '0.02'=0 | 'b'='b' | (1+3)=(2+2) | NULL=null | +-----+-------+-----+----------+---------+-------------+-----------+ | 0 | 1 | 1 | 0 | 1 | 1 | NULL | +-----+-------+-----+----------+---------+-------------+-----------+ 1 row in set (0.01 sec)
对运行结果的分析:
2=2
和'2' =2
的返回值相同,都为 1,因为在进行判断时,MySQL 自动进行了转换,把字符 '2' 转换成了数字 2。'b'='b'
为相同的字符比较,因此返回值为 1。- 表达式
1+3
和表达式2+2
的结果都为 4,因此结果相等,返回值为 1; - 由于 = 不能用于空值 NULL 的判断,因此
NULL=null
的返回值为 NULL。
2) 安全等于运算符(<=>)
<=> 操作符和 = 操作符类似,不过 <=> 可以用来判断 NULL 值,具体语法规则为:
- 当两个操作数均为 NULL 时,其返回值为 1 而不为 NULL;
- 而当一个操作数为 NULL 时,其返回值为 0 而不为 NULL。
【实例2】使用 <=> 进行相等的判断,SQL 语句如下:
mysql> SELECT 1<=>0,'2'<=>2,2<=>2,'0.02'<=>0,'b'<=>'b',(1+3) <=> (2+1),NULL<=>NULL;
+-------+---------+-------+------------+-----------+-----------------+-------------+
| 1<=>0 | '2'<=>2 | 2<=>2 | '0.02'<=>0 | 'b'<=>'b' | (1+3) <=> (2+1) | NULL<=>NULL |
+-------+---------+-------+------------+-----------+-----------------+-------------+
| 0 | 1 | 1 | 0 | 1 | 0 | 1 |
+-------+---------+-------+------------+-----------+-----------------+-------------+
1 row in set (0.00 sec)
由结果可以看到,<=> 在执行比较操作时和 = 的作用是相似的,唯一的区别是 <=> 可以用来对 NULL 进行判断,两者都为 NULL 时返回值为 1。
3) 不等于运算符(<> 或者 !=)
与 = 的作用相反,<> 和 != 用于判断数字、字符串、表达式是否不相等。对于 <> 和 !=,如果两侧操作数不相等,返回值为 1,否则返回值为 0;如果两侧操作数有一个是 NULL,那么返回值也是 NULL。
【实例3】使用 <> 和 != 进行不相等的判断,SQL 语句如下:
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 row in set (0.00 sec)
由结果可以看到,两个不等于运算符作用相同,都可以进行数字、字符串、表达式的比较判断。
4) 小于等于运算符(<=)
<= 是小于等于运算符,用来判断左边的操作数是否小于或者等于右边的操作数;如果小于或者等于,返回值为 1,否则返回值为 0;如果两侧操作数有一个是 NULL,那么返回值也是 NULL。
【实例4】使用 <= 进行比较判断,SQL 语句如下:
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 | +---------------+------+------+--------+--------------+------------+ 1 row in set (0.00 sec)
由结果可以看到:
- 左边操作数小于或者等于右边时,返回值为 1,例如 4<=4;
- 当左边操作数大于右边时,返回值为 0,例如“good”第 3 个位置的“o”字符在字母表中的顺序大于“god”中的第 3 个位置的“d”字符,因此返回值为0;
- 同样,比较 NULL 值时返回 NULL。
5) 小于运算符(<)
< 是小于运算符,用来判断左边的操作数是否小于右边的操作数;如果小于,返回值为 1,否则返回值为 0;如果两侧操作数有一个是 NULL,那么返回值也是 NULL。
【实例5】使用 < 进行比较判断,SQL 语句如下:
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 | 0 | 0 | 0 | NULL | +--------------+-----+-----+-------+-------------+-----------+ 1 row in set (0.02 sec)
由结果可以看到:
- 当左边操作数小于右边时,返回值为 1,例如 1<2;
- 当左边操作数大于右边时,返回值为 0,例如“good”第 3 个位置的“o”字符在字母表中的顺序大于“god”中的第 3 个位置的“d”字符,因此返回值为 0;
- 同样,比较 NULL 值时返回 NULL。
6) 大于等于运算符(>=)
>= 是大于等于运算符,用来判断左边的操作数是否大于或者等于右边的操作数;如果大于或者等于,返回值为 1,否则返回值为 0;如果两侧操作数有一个是 NULL,那么返回值也是 NULL。
【实例6】使用 >= 进行比较判断,SQL 语句如下:
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 | 0 | 1 | 1 | 1 | NULL | +---------------+------+------+--------+--------------+------------+ 1 row in set (0.00 sec)
由结果可以看到:
- 左边操作数大于或者等于右边时,返回值为 1,例如 4>=4;
- 当左边操作数小于右边时,返回值为 0,例如 1>=2;
- 同样,比较 NULL 值时返回 NULL。
7) 大于运算符(>)
> 是大于运算符,用来判断左边的操作数是否大于右边的操作数;如果大于,返回值为 1,否则返回值为 0;如果两侧操作数有一个是 NULL,那么返回值也是 NULL。
【实例7】使用 > 进行比较判断,SQL 语句如下:
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 | 0 | 0 | 1 | 1 | NULL | +--------------+-----+-----+-------+-------------+-----------+ 1 row in set (0.00 sec)
mysql数据库实战训练https://edu.csdn.net/course/detail/39021
由结果可以看到:
- 左边操作数大于右边时,返回值为 1,例如 5.5>5;
- 当左边操作数小于右边时,返回 0,例如 1>2;
- 同样,比较 NULL 值时返回 NULL。
8) IS NULL(ISNULL) 和 IS NOT NULL 运算符
IS NULL 或 ISNULL 运算符用来检测一个值是否为 NULL,如果为 NULL,返回值为 1,否则返回值为 0。ISNULL 可以认为是 IS NULL 的简写,去掉了一个空格而已,两者的作用和用法都是完全相同的。
IS NOT NULL 运算符用来检测一个值是否为非 NULL,如果是非 NULL,返回值为 1,否则返回值为 0。
【实例8】使用 IS NULL、ISNULL 和 IS NOT NULL 判断 NULL 值和非 NULL 值,SQL 语句如下:
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 | +--------------+--------------+------------+----------------+ 1 row in set (0.01 sec)
由结果可以看到,IS NULL 和 ISNULL 的作用相同,只是写法略有不同。ISNULL 和 IS NOT NULL 的返回值正好相反。
9) BETWEEN AND 运算符
BETWEEN AND 运算符用来判断表达式的值是否位于两个数之间,或者说是否位于某个范围内,它的语法格式如下:
expr BETWEEN min AND max
expr 表示要判断的表达式,min 表示最小值,max 表示最大值。如果 expr 大于等于 min 并且小于等于 max,那么返回值为 1,否则返回值为 0。
【实例9】使用 BETWEEN AND 进行值区间判断,输入 SQL 语句如下:
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 | +-------------------+-------------------+---------------------+ 1 row in set (0.00 sec)
由结果可以看到:
- 4 在端点值区间内或者等于其中一个端点值,BETWEEN AND 表达式返回值为 1;
- 12 并不在指定区间内,因此返回值为 0;
- 对于字符串类型的比较,按字母表中字母顺序进行比较,“x”不在指定的字母区间内,因此返回值为 0,而“b”位于指定字母区间内,因此返回值为 1。
总结
使用比较运算符时需要注意空值 NULL,大部分比较运算符遇到 NULL 时也会返回 NULL,上面我们都进行了说明。
另外,有些教程把 IN 和 NOT IN 也划分为比较运算符,但是我并不这样认为,我认为它们的初衷是判断某个值是否位于给出的列表中,只是使用了比较的功能而已
MySQL位运算符
所谓位运算,就是按照内存中的比特位(Bit)进行操作,这是计算机能够支持的最小单位的运算。程序中所有的数据在内存中都是以二进制形式存储的,位运算就是对这些二进制数据进行操作。
位运算一般用于操作整数,对整数进行位运算才有实际的意义。整数在内存中是以补码形式存储的,正数的补码形式和原码形式相同,而负数的补码形式和它的原码形式是不一样的,这一点大家要特别注意;这意味着,对负数进行位运算时,操作的是它的补码,而不是它的原码。
对整数存储不了解的读者请猛击《整数在内存中是如何存储的,为什么它堪称天才般的设计》。
MySQL 中的整数字面量(常量整数,也就是直接书写出来的整数)默认以 8 个字节(Byte)来表示,也就是 64 位(Bit)。例如,5 的二进制形式为:
0000 0000 ... 0000 0101
省略号部分都是 0,101 前面总共有 61 个 0。
注意:为了方便大家阅读,本节在介绍正数的补码时,省略了前面的 0。
MySQL 支持 6 种位运算符,如下表所示。
运算符 | 说明 | 使用形式 | 举例 |
---|---|---|---|
| | 位或 | a | b | 5 | 8 |
& | 位与 | a & b | 5 & 8 |
^ | 位异或 | a ^ b | 5 ^ 8 |
~ | 位取反 | ~a | ~5 |
<< | 位左移 | a << b | 5 << 2,表示整数 5 按位左移 2 位 |
>> | 位右移 | a >> b | 5 >> 2,表示整数 5 按位右移 2 位 |
位运算中的&
、|
、~
和逻辑运算中的&&
、||
、!
非常相似。
位或运算符 |
参与|
运算的两个二进制位有一个为 1 时,结果就为 1,两个都为 0 时结果才为 0。例如1|1
结果为 1,0|0
结果为0,1|0
结果为1,这和逻辑运算中的||
非常类似。
例 1
使用位或运算符进行正数运算,SQL 语句如下:
mysql> SELECT 10|15,9|4|2; +-------+-------+ | 10|15 | 9|4|2 | +-------+-------+ | 15 | 15 | +-------+-------+ 1 row in set (0.00 sec)
10 的补码为 1010,15 的补码为 1111,按位或运算之后,结果为 1111,即整数 15;9 的补码为 1001,4 的补码为 0100,2 的补码为 0010,按位或运算之后,结果为 111,即整数 15。
例 2
使用位或运算符进行负数运算,SQL 语句如下:
mysql> SELECT -7|-1; +----------------------+ | -7|-1 | +----------------------+ | 18446744073709551615 | +----------------------+ 1 row in set (0.00 sec)
-7 的补码为 60 个‘1’加 1001,-1 的补码为 64 个‘1’,按位或运算之后,结果为 64 个‘1’,即整数 18446744073709551615。
可以发现,任何数和 -1 进行位或运算时,最终结果都是 -1 的十进制数。
位与运算符 &
参与&
运算的两个二进制位都为 1 时,结果就为 1,否则为 0。例如1|1
结果为 1,0|0
结果为 0,1|0
结果为 0,这和逻辑运算中的&&
非常类似。
例 3
使用位与运算符进行正数运算,SQL 语句如下:
mysql> SELECT 10 & 15,9 & 4 & 2; +---------+-----------+ | 10 & 15 | 9 & 4 & 2 | +---------+-----------+ | 10 | 0 | +---------+-----------+ 1 row in set (0.00 sec)
10 的补码为 1010,15 的补码为 1111,按位与运算之后,结果为 1010,即整数 10;9 的补码为 1001,4 的补码为 0100,2 的补码为 0010,按位与运算之后,结果为 0000,即整数 0。
例 4
使用位与运算符进行负数运算,SQL 语句如下:
mysql> SELECT -7&-1; +----------------------+ | -7&-1 | +----------------------+ | 18446744073709551609 | +----------------------+ 1 row in set (0.01 sec)
-7 的补码为 60 个‘1’加 1001,-1 的补码为 64 个‘1’,按位与运算之后,结果为 60 个‘1’加 1001,即整数 18446744073709551609。
可以发现,任何数和 -1 进行位与运算时,最终结果都为任何数本身的十进制数。
位异或运算符 ^
参与^
运算的两个二进制位不同时,结果为 1,相同时,结果为 0。例如1|1
结果为 0,0|0
结果为 0,1|0
结果为1。
例 5
使用位异或运算符进行正数运算,SQL 语句如下:
mysql> SELECT 10^15,1^0,1^1; +-------+-----+-----+ | 10^15 | 1^0 | 1^1 | +-------+-----+-----+ | 5 | 1 | 0 | +-------+-----+-----+ 1 row in set (0.00 sec)
10 的补码为 1010,15 的补码为 111,按位异或运算之后,结果为 0101,即整数 5;1 的补码为 0001,0 的补码为 0000,按位异或运算之后,结果为 0001;1 和 1 本身二进制位完全相同,因此结果为 0。
例 6
使用位异或运算符进行负数运算,SQL 语句如下:
mysql> SELECT -7^-1; +-------+ | -7^-1 | +-------+ | 6 | +-------+ 1 row in set (0.00 sec)
-7 的补码为 60 个‘1’加 1001,-1 的补码为 64 个‘1’,按位异或运算之后,结果为 110,即整数 6。
位左移运算符 <<
位左移是按指定值的补码形式进行左移,左移指定位数之后,左边高位的数值被移出并丢弃,右边低位空出的位置用 0 补齐。
位左移的语法格式为:
expr << n
其中,n 指定值 expr 要移位的位数,n 必须为非负数。
例 7
使用位左移运算符进行正数计算,SQL 语句如下:
mysql> SELECT 1<<2,4<<2; +------+------+ | 1<<2 | 4<<2 | +------+------+ | 4 | 16 | +------+------+ 1 row in set (0.00 sec)
1 的补码为 0000 0001,左移两位之后变成 0000 0100,即整数 4;4 的补码为 0000 0100,左移两位之后变成 0001 0000,即整数 16。
例 8
使用位左移运算符进行负数计算,SQL 语句如下:
mysql> SELECT -7<<2; +----------------------+ | -7<<2 | +----------------------+ | 18446744073709551588 | +----------------------+ 1 row in set (0.00 sec)
-7 的补码为 60 个‘1’加 1001,左移两位之后变成 56 个‘1’加 1110 0100,即整数 18446744073709551588。
位右移运算符 >>
位右移是按指定值的补码形式进行右移,右移指定位数之后,右边低位的数值被移出并丢弃,左边高位空出的位置用 0 补齐。
位右移语法格式为:
expr >> n
其中,n 指定值 expr 要移位的位数,n 必须为非负数。
例 9
使用位右移运算符进行正数运算,SQL 语句如下:
mysql> SELECT 1>>1,16>>2; +------+-------+ | 1>>1 | 16>>2 | +------+-------+ | 0 | 4 | +------+-------+ 1 row in set (0.00 sec)
1 的补码为 0000 0001,右移 1 位之后变成 0000 0000,即整数 0;16 的补码为 0001 0000,右移两位之后变成 0000 0100,即整数 4。
例 10
使用位右移运算符进行负数运算,SQL 语句如下:
mysql> SELECT -7>>2; +---------------------+ | -7>>2 | +---------------------+ | 4611686018427387902 | +---------------------+ 1 row in set (0.00 sec)
-7 的补码为 60 个‘1’加 1001,右移两位之后变成 0011 加 56 个‘1’加 1110,即整数 4611686018427387902。
位取反运算符 ~
位取反是将参与运算的数据按对应的补码进行反转,也就是做 NOT 操作,即 1 取反后变 0,0 取反后变为 1。
例 11
下面看一个经典的取反例子,对 1 进行位取反运算,具体如下所示:
mysql> SELECT ~1,~18446744073709551614; +----------------------+-----------------------+ | ~1 | ~18446744073709551614 | +----------------------+-----------------------+ | 18446744073709551614 | 1 | +----------------------+-----------------------+ 1 row in set (0.00 sec)
常量 1 的补码为 63 个‘0‘加 1 个’1‘,位取反后就是 63 个’1‘加一个’0‘,转换为二进制后就是 18446744073709551614。
可以使用 BIN() 函数查看 1 取反之后的结果,BIN() 函数的作用是将一个十进制数转换为二进制数,SQL 语句如下:
mysql> SELECT BIN(~1); +------------------------------------------------------------------+ | BIN(~1) | +------------------------------------------------------------------+ | 1111111111111111111111111111111111111111111111111111111111111110 | +------------------------------------------------------------------+ 1 row in set (0.01 sec)
1 的补码表示为最右边位为 1,其他位均为 0,取反操作之后,除了最低位,其他位均变为 1。
例 12
使用位取反运算符进行运算,SQL 语句如下:
mysql> SELECT 5 & ~1; +--------+ | 5 & ~1 | +--------+ | 4 | +--------+ 1 row in set (0.00 sec)
逻辑运算5&~1
中,由于位取反运算符‘~’的级别高于位与运算符‘&’,因此先对 1 进行取反操作,结果为 63 个‘1’加一个‘0’,然后再与整数 5 进行与运算,结果为 0100,即整数 4。
mysql数据库实战训练https://edu.csdn.net/course/detail/39021