Mysql笔记二之 运算符

时间:2022-10-16 17:20:03

Mysql笔记二之 运算符

运算符分别有:算术运算符、比较运算符、逻辑运算符、位运算符。

接下来分别讲述这四个运算符。

1.算术运算符
用于各类数值运算。包括加(+)、减(-)、乘(*)、除(/)、求余(%).
注意:
a.”+”和”-“的优先级相同
b.除数为0,结果为NULL

2.比较运算符

运算符 作用
== 等于
<=> 安全的等于
<>(!=) 不等于
<= 小于等于
>= 大于等于
> 大于
IS NULL 判断一个值是否为NULL
IS NOT NULL 判断一个值是否不为NULL
LEAST 在有两个或多个参数时,返回最小值
GREATEST 当有2或多个参数时,返回最大值
BETWEEN AND 判断一个值是否落在两个值之间
ISNULL 与IS NULL作用相同
IN 判断一个值是否落在两个值之间
NOT IN 判断一个值不是IN列表中的任意一个值
LIKE 通配符匹配
REGEXP 正则表达式匹配

2. 算术运算符
2.1 等于运算符 =

mysql> SELECT 1=0,'2'=2,2=2,'0.02'=0,'b'='b',(1+4)=(2+3),NULL=NULL;
+-----+-------+-----+----------+---------+-------------+-----------+

| 1=0 | '2'=2 | 2=2 | '0.02'=0 | 'b'='b' | (1+4)=(2+3) | NULL=NULL |
+-----+-------+-----+----------+---------+-------------+-----------+

| 0 | 1 | 1 | 0 | 1 | 1 | NULL |
+-----+-------+-----+----------+---------+-------------+-----------+

1 row in set (0.03 sec)

数值比较规则:
a.若有一个或两个参数为NULL,则比较运算结果为NULL。
b.若同一个比较运算中的两个参数都是字符串,则按照字符串进行比较。
c.若两个参数均为整数,则按照整数进行比较。
d.若一个字符串和数字进行相等判断,则MySQL可以自动将字符串转换为数字。

2.2 安全等于运算符 <=>

mysql> SELECT 1<=>0,'2'<=>2,2<=>2,'0.02'<=>0,'b'<=>'b',(1+4)<=>(2+3),NULL<=>NULL;
+-------+---------+-------+------------+-----------+---------------+-------------+

| 1<=>0 | '2'<=>2 | 2<=>2 | '0.02'<=>0 | 'b'<=>'b' | (1+4)<=>(2+3) | NULL<=>NULL |
+-------+---------+-------+------------+-----------+---------------+-------------+

| 0 | 1 | 1 | 0 | 1 | 1 | 1 |
+-------+---------+-------+------------+-----------+---------------+-------------+

1 row in set (0.00 sec)

总结:
“<=>”在执行比较操作时和“=”的作用是相似的,唯一的区别是“<=>”可以用来对NULL进行判断,两者都为NULL时返回1。

2.3 不等于运算符 <> 或者 !=

mysql> SELECT 'good'<>'god',1<>2,4!=4,5!=5,(1+3)!=(2+1),NULL<>NULL;
+---------------+------+------+------+--------------+------------+

| 'good'<>'god' | 1<>2 | 4!=4 | 5!=5 | (1+3)!=(2+1) | NULL<>NULL |
+---------------+------+------+------+--------------+------------+

| 1 | 1 | 0 | 0 | 1 | NULL |
+---------------+------+------+------+--------------+------------+

1 row in set (0.00 sec)

2.4 小于或等于运算符 <=

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)

总结:good 第三个位置 o 字符在字母表中的顺序大于god 的第三个位置 d 因此返回0;
比较NULL值时,返回NULL;

2.5 小于 <

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.05 sec)

2.6 大于或等于运算符 >=

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)

2.7 大于运算符 >

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)

2.8 IS NULL(ISNULL) ,IS NOT NULL 运算符

mysql> SELECT NULL IS NULL,ISNULL(NULL),ISNULL(0),10 IS NOT NULL;
+--------------+--------------+-----------+----------------+

| NULL IS NULL | ISNULL(NULL) | ISNULL(0) | 10 IS NOT NULL |
+--------------+--------------+-----------+----------------+

| 1 | 1 | 0 | 1 |
+--------------+--------------+-----------+----------------+

1 row in set (0.00 sec)

总结:

IS NULL 和ISNULL 检验一个值是否为NULL,如果为NULL,返回1,否则返回0
IS NOT NULL ,如果是非NULL 返回1,否则返回0;

2.9 BETWEEN AND 运算符

mysql> SELECT 4 BETWEEN 4 AND 5,4 BETWEEN 4 AND 6,12 BETWEEN 9 AND 10;
+-------------------+-------------------+---------------------+

| 4 BETWEEN 4 AND 5 | 4 BETWEEN 4 AND 6 | 12 BETWEEN 9 AND 10 |
+-------------------+-------------------+---------------------+

| 1 | 1 | 0 |
+-------------------+-------------------+---------------------+

1 row in set (0.00 sec)

mysql> SELECT 'x' BETWEEN 'f' AND 'g' ,'b' BETWEEN 'a' AND 'c';
+-------------------------+-------------------------+

| 'x' BETWEEN 'f' AND 'g' | 'b' BETWEEN 'a' AND 'c' |
+-------------------------+-------------------------+

| 0 | 1 |
+-------------------------+-------------------------+

1 row in set (0.00 sec)

总结:
语法格式为:expr BETWEEN min AND max.如果expr 大于或等于min 且小于或等于max,则返回1,否则返回0;

2.10 LEAST 运算符

mysql> SELECT LEAST(2,0),LEAST(10.0,3.0,55.5),LEAST('a','b','c'),LEAST(10,NULL);
+------------+----------------------+--------------------+----------------+

| LEAST(2,0) | LEAST(10.0,3.0,55.5) | LEAST('a','b','c') | LEAST(10,NULL) |
+------------+----------------------+--------------------+----------------+

| 0 | 3.0 | a | NULL |
+------------+----------------------+--------------------+----------------+

1 row in set (0.00 sec)

总结:
LEAST() 返回最小值,假如任意一个自变量为NULL,则返回值为NULL。
当参数是整数或浮点数,返回最小值
参数为字符串时,返回字母表顺序靠前的字符。

2.11 GREATEST 运算符

mysql> SELECT GREATEST(2,0),GREATEST(10.0,3.0,55.5),GREATEST('a','b','c'),GREATEST(10,NULL)
;
+---------------+-------------------------+-----------------------+-------------------+

| GREATEST(2,0) | GREATEST(10.0,3.0,55.5) | GREATEST('a','b','c') | GREATEST(10,NULL) |
+---------------+-------------------------+-----------------------+-------------------+

| 2 | 55.5 | c | NULL |
+---------------+-------------------------+-----------------------+-------------------+

1 row in set (0.00 sec)

总结:
与LEAST 相反,返回最大值;如果参数有NULL ,返回NULL;

2.12 IN、NOT IN 运算符

mysql> SELECT 2 IN (1,3,5,'asd'),'asd' IN(1,3,5,'asd');
+--------------------+-----------------------+

| 2 IN (1,3,5,'asd') | 'asd' IN(1,3,5,'asd') |
+--------------------+-----------------------+

| 0 | 1 |
+--------------------+-----------------------+

1 row in set, 3 warnings (0.00 sec)

mysql> SELECT 2 NOT IN (1,3,5,'asd'),'asd' NOT IN (1,3,5,'asd');
+------------------------+----------------------------+

| 2 NOT IN (1,3,5,'asd') | 'asd' NOT IN (1,3,5,'asd') |
+------------------------+----------------------------+

| 1 | 0 |
+------------------------+----------------------------+

1 row in set, 3 warnings (0.00 sec)

总结:
IN 运算符用来判断操作数是否为IN 列表中的其中一个值,如果是返回1,否则返回0;
在左侧表达式为NULL的情况下,或是表中找不到匹配项并且其中一个表达式为NULL的情况下,IN的返回值均为NULL.
IN 和 NOT IN的返回值正好正好相反。

2.13 LIKE

mysql> SELECT 'stud' LIKE 'stud','stud' LIKE 'stu_' , 'stud' LIKE '%d' , 'stud' LIKE 't___' ,'s' LIKE NULL;
+--------------------+--------------------+------------------+--------------------+---------------+

| 'stud' LIKE 'stud' | 'stud' LIKE 'stu_' | 'stud' LIKE '%d' | 'stud' LIKE 't___' | 's' LIKE NULL |
+--------------------+--------------------+------------------+--------------------+---------------+

| 1 | 1 | 1 | 0 | NULL |
+--------------------+--------------------+------------------+--------------------+---------------+

1 row in set (0.00 sec)

总结:
语法格式:expr LIKE 匹配条件,如果满足条件,返回1,否则返回0;
“%”匹配任何数目的字符,甚至包括零字符。
“_”只能匹配一个字符。
“stu_”表示匹配以stu开头的长度为4个字符的字符串。
“%d” 表示匹配以字母d结尾的字符串。
当字符s 与 NULL匹配时,结果为NULL.

2.14 REGEXP

mysql> SELECT 'ssky' REGEXP '^s','ssky' REGEXP 'y$' , 'ssky' REGEXP '.sky','ssky' REGEXP '[ab]';
+--------------------+--------------------+----------------------+----------------------+
| 'ssky' REGEXP '^s' | 'ssky' REGEXP 'y$' | 'ssky' REGEXP '.sky' | 'ssky' REGEXP '[ab]' |
+--------------------+--------------------+----------------------+----------------------+
| 1 | 1 | 1 | 0 |
+--------------------+--------------------+----------------------+----------------------+
1 row in set (0.00 sec)

总结:
a.REGEXP 用来匹配字符串(正则表达式),格式: expr REGEXP 匹配条件
b.’^’ 匹配以该字符后面的字符开头的字符串
c.’$’ 匹配以该字符后面的字符结尾的字符串
d.’.’ 匹配任何一个单字符
e.’[…]’ 匹配在方括号内的任何字符 例’[abc]’ 匹配 a、b和c
f.’*’ 匹配零个或多个在它前面的字符。


3.逻辑运算符

运算符 作用
NOT 或者 ! 逻辑非
AND 或者 && 逻辑与
OR 或者 | | 逻辑或
XOR 逻辑异或

3.1 NOT 或者 !

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 |
+--------+-----------+--------+----------+---------+

1 row in set (0.00 sec)

mysql> SELECT !10,!(1-1),!-5,! NULL,! 1+1;
+-----+--------+-----+--------+-------+

| !10 | !(1-1) | !-5 | ! NULL | ! 1+1 |
+-----+--------+-----+--------+-------+

| 0 | 1 | 0 | NULL | 1 |
+-----+--------+-----+--------+-------+

1 row in set (0.00 sec)

总结:
“!”的优先级高于”+” ,因此”!1+1” 结果为 1;
NOT 的 优先级低于”+”,因此NOT 1+1 结果为 0;

3.2 AND 或者 &&

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 |
+----------+---------+------------+------------+

1 row in set (0.04 sec)

mysql> SELECT 1 && -1,1 && 0,1 && NULL,0 && NULL;
+---------+--------+-----------+-----------+

| 1 && -1 | 1 && 0 | 1 && NULL | 0 && NULL |
+---------+--------+-----------+-----------+

| 1 | 0 | NULL | 0 |
+---------+--------+-----------+-----------+

1 row in set (0.00 sec)

总结:
AND 和”&&” 的作用相同
有0则为0,没0有NULL 则为NULL.

3.3 OR 或者 ||

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 |
+--------------+--------+-----------+-----------+--------------+

1 row in set (0.00 sec)

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 |
+--------------+--------+-----------+-----------+--------------+

1 row in set (0.00 sec)

总结:
OR 和 “||” 作用相同

3.4 XOR

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 row in set (0.00 sec)

总结:
a.任意一个操作数为NULL,返回NULL.
b.没有NULL时,都为true 则 为0,一个true,一个false为1;(相同为0,相反为1)

4 位运算符

运算符 作用
| 位或
& 位与
^ 位异或
<< 位左移
>> 位右移
~ 位取反,反转所有比特

4.1 位或运算符 |

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

4.2 位与运算符 &

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.

4.3 位异或运算符 ^

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的二进制为1111,位异或运算后 0101,所以为5。

4.4 位左移运算符 <<

mysql> SELECT 1 << 2, 4 << 2;
+--------+--------+

| 1 << 2 | 4 << 2 |
+--------+--------+

| 4 | 16 |
+--------+--------+

1 row in set (0.00 sec)

总结:
a.位左移运算符<<使指定的二进制的所有位都左移指定位数。左移指定位数之后,左边高位的数值将被移出并丢弃,右边低位空出的位置用0补齐
b.1的二进制为0000 0001 ,左移两位变成 0000 0100.则返回4(十进制)

4.5 位右移运算符 >>

mysql> SELECT 1 >> 1, 16 >> 2;
+--------+---------+

| 1 >> 1 | 16 >> 2 |
+--------+---------+

| 0 | 4 |
+--------+---------+

1 row in set (0.00 sec)

总结:
a.位右移运算符>>使指定的二进制的所有位都右移指定位数。右移指定位数之后,右边低位的数值将被移出并4丢弃,左边高位空出的位置用0补齐
b.1的二进制为0000 0001 ,右移两位变成 0000 0000.则返回0(十进制)
16的二进制为0001 0000,右移两位变成 0000 0100.则为4(十进制)

4.6 位取反运算符 ~

mysql> SELECT 5 & ~1;
+--------+

| 5 & ~1 |
+--------+

| 4 |
+--------+

1 row in set (0.00 sec)

总结:
逻辑运算5&~ 1,由于位取反运算符”~”的级别高于位与运算符”&” ,因此先对1取反操作。
取反之后,除了最低位为0其他都为1 (1111 1110),然后再与十进制5 (0000 0101)进行位与运算 结果为 0000 0100 结果为4。

最后附上运算符优先级排序

header 1 header 2
row 1 col 1 row 1 col 2
row 2 col 1 row 2 col 2
优先级 运算符
1(最高) !
2 -(负号),~(按位取反)
3 ^(按位异或)
4 *,/(DIV),%(MOD)
5 +,-
6 >>,<<
7 &
8 |
9 =(比较运算),<=>,<,<=,>,>=,!=,<>,IN,IS NULL,LIKE,REGEXP
10 BETWEEN AND,CASE,WHEN,THEN,ELSE
11 NOT
12 &&,AND
13 XOR
14 | |,OR
15(最低) =(赋值运算),:=