MySQ常见运算符介绍
(1).算数运算符
(2).比较运算符
(3).逻辑运算符
(4).位运算符
(5).运算符的优先级
MySQL常见运算符介绍
运算符是告诉MySQL执行特性算数或逻辑操作的符号。MySQL的内部运算很丰富,主要有四大类:
1.算数运算符
用于各类数值运算,包括:+、-、*、/、%。
2.比较运算符
用于比较运算,包括:>、<、=、>=、<=、!=,以及in、between add、is null、greatest、least、like、regexp等。
3.逻辑运算符
逻辑运算的求值所得结果均为1(true)or0(false),有:逻辑与(add/&&)、逻辑或(or/||)、逻辑非(not/!)、异或(xor)。
4.位运算符
位运算的操作数按二进制位进行计算,有:与(&)、或(|)、非(~)、异或(^)、左移(<<)、右移(>>)6种
(1).算数运算符
运算符 | 作用 |
---|---|
+ | 加法运算 |
- | 减法运算 |
* | 乘法运算 |
/ | 除法运算,返回商 |
% | 求余运算,返回余数 |
- MySQL将求商的结果保存到了小数点后面4位。
- 除法运算汇总的除数不能为0,如果被0除,则返回结果为null。
【例1】创建tmp14,定义数据类型为int的字段num,插入值64,对num值进行算数运算
mysql> create table tmp14 (num int);
Query OK, 0 rows affected (0.13 sec)
mysql> insert into tmp14 value(64);
Query OK, 1 row affected (0.06 sec)
mysql> select num, num+10,num-3+5,num+5-3,num+36.5 from tmp14;
+------+--------+---------+---------+----------+
| num | num+10 | num-3+5 | num+5-3 | num+36.5 |
+------+--------+---------+---------+----------+
| 64 | 74 | 66 | 66 | 100.5 |
+------+--------+---------+---------+----------+
1 row in set (0.00 sec)
【例2】对tmp14表中的num进行乘法、除法、取余运算。
mysql> select
-> num,
-> num *2,
-> num /2,
-> num /3,
-> num %3
-> from tmp14;
+------+--------+---------+---------+--------+
| num | num *2 | num /2 | num /3 | num %3 |
+------+--------+---------+---------+--------+
| 64 | 128 | 32.0000 | 21.3333 | 1 |
+------+--------+---------+---------+--------+
1 row in set (0.00 sec)
【例3】用0除num。
mysql> select
-> num,
-> num /0,
-> num %0
-> from tmp14;
+------+--------+--------+
| num | num /0 | num %0 |
+------+--------+--------+
| 64 | NULL | NULL |
+------+--------+--------+
1 row in set (0.00 sec)
(2)比较运算符
MySQL中的比较运算符如下:
运算符 | 作用 |
---|---|
= | 等于 |
<=> | 安全等于 |
<>(!=) | 不等于 |
<= | 小于等于 |
>= | 大于等于 |
> | 大于 |
is null | 判断一个值是否为null |
is not null | 判断一个值是否不为null |
least | 在有两个或多个参数时,返回最小值 |
greatest | 当有两个或多个参数时,返回最大值 |
between and | 判断一个值是否落在两个值之间 |
isnull | 与is null作用相同 |
in | 判断一个值是in列表中的任意一个值 |
not in | 判断一个值不是in列表中的任意一个值 |
like | 通配符匹配 |
regexp | 正则表达式匹配 |
- MySQL若要执行区分大小写的字符串比较,在字符串前面添加binary关键字。
1.等于运算符(=)
作用:判断数字、字符串、表达式是否相等,如果相等返回1,否则返回0。
- 若有一个或两个参数为null,则比较运算的结果为null。
- 若一个字符串和数字进行相等判断,则MySQL可以自动将字符串转换为数字。
【例1】使用“=”进行判断
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.00 sec)
2.安全等于运算符(<=>)
作用:和=执行相同的比较操作,但<=>可以用来判断null值。
- 当两个操作数均为null时,返回值为1而不为null,当一个操作数为null时,返回值为0而不为null。
【例】使用"<=>"进行相等的判断,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)
3.不等于运算符(<>或者!=)
用于判断数字、字符串、表达式不相等的判断。
- 如果不相等就返回1,否则返回0。不能用来判断空值null。
- 两个不等于运算符的作用相同,都可以进行数字、字符串、表达式的比较判断。
【例】使用"<>“和”!="进行不相等的判断,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。
【例】使用"<="进行比较判断,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)
5.小于运算符(<)
- 如果小于,返回值为1,否则返回值为0。不能用于判断空值null。
【例】使用<进行比较判断,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.00 sec)
6.大于等于运算符(>=)
- 如果大于等于返回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 | 0 | 1 | 1 | 1 | NULL |
+---------------+------+------+--------+--------------+------------+
1 row in set (0.00 sec)
7.大于运算符(>)
如果大于返回1,否则返回0。不能判断null。
【例】使用">",进行判断,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)
8.is null(isnull)和is not null 运算符
- is null 如果为null,返回1,否则返回0。
- is not null 如果为非null,返回1,否则返回0。
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.00 sec)
9.between add 运算符
- 语法格式为:
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 |
+-------------------+-------------------+---------------------+
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)
10.LEAST运算符
语法格式:least(值1,值2,...值n)。
在有两个或多个参数的情况下,返回最小值。若有自变量为null,则返回null。
【例】使用select运算符进行大小判断,SQL语句如下:
mysql> select least(2,0),
-> least(20.0,3.0,100.5),
-> least('a','c','b'),
-> least(10,null);
+------------+-----------------------+--------------------+----------------+
| least(2,0) | least(20.0,3.0,100.5) | least('a','c','b') | least(10,null) |
+------------+-----------------------+--------------------+----------------+
| 0 | 3.0 | a | NULL |
+------------+-----------------------+--------------------+----------------+
1 row in set (0.00 sec)
11.GREATEST(value1,value2,…)
语法格式为:greatest(值1,值2,值3…值n);
【例】使用greatest运算符进行大小判断,SQL语句如下:
mysql> select greatest(2,0),
-> greatest(20.0,3.0,100.5),
-> greatest('a','c','b'),
-> greatest(10,null);
+---------------+--------------------------+-----------------------+-------------------+
| greatest(2,0) | greatest(20.0,3.0,100.5) | greatest('a','c','b') | greatest(10,null) |
+---------------+--------------------------+-----------------------+-------------------+
| 2 | 100.5 | c | NULL |
+---------------+--------------------------+-----------------------+-------------------+
1 row in set (0.00 sec)
12.in、not in 运算符
- in运算符判断操作数是否为in列表中的其中一个值,如果是返回1,否则返回0。
- not in与in相反。
- 左侧表达式为null或表中找不到匹配项且表中一个表达式为null时,in的返回值为null。
- in()语法也可以用在select语句中进行嵌套子查询。
【例1】使用in、not in运算符进行判断。
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 |
+--------------------+-------------------------+
1 row in set, 2 warnings (0.00 sec)
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 |
+-------------------------+-----------------------------+
1 row in set, 2 warnings (0.00 sec)
【例2】存在null值时的in查询,SQL语句如下:
mysql> select null in (1,3,5,'thks'),
-> 10 in (1,3,null,'thks');
+------------------------+-------------------------+
| null in (1,3,5,'thks') | 10 in (1,3,null,'thks') |
+------------------------+-------------------------+
| NULL | NULL |
+------------------------+-------------------------+
1 row in set, 1 warning (0.00 sec)
13.LIKE
- like运算符用来匹配字符串,语法格式:
expr LIKE匹配条件。
- 如果expr满足匹配条件,就返回1(true),否则返回0(false)。
- 若expr或匹配条件中任何一个为null,则结果为null。
- 通配符‘%’,能匹配任何数目的字符,甚至包括零字符。
- 通配符‘_’,只能匹配一个字符。
【例】使用运算符LIKE进行字符串匹配运算,SQL语句如下:
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)
14.REGEXP
- 如果regexp满足匹配条件,就返回1,否则返回0。
regexp运算符进行匹配时,常用下面几种通配符:
- 1.’^'匹配以该字符后面的字符开头的字符串。
- 2.’$'匹配以该字符后面的字符结尾的字符串。
- 3.’.'匹配任何一个单字符。
- 4.’[…]'匹配在方括号内的任何字符。
- 5."
*
"匹配零个或多个在它前面的字符,例如,"x*“匹配任何数量的’x’字符,”[0-9]“匹配任何数量的数字,而”*"匹配任何数量的任何字符。
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.02 sec)
(3)逻辑运算符
- 在SQL中,所有逻辑运算符的求值所得结果均为true、false或null;而在MySQL中,它们表现为1(true)、0(false)和null。其大多数都与不同的数据库SQL通用。
MySQL中的逻辑运算符如下表所示:
运算符 | 作用 |
---|---|
and或&& | 逻辑与 |
or或|| | 逻辑或 |
not/! | 逻辑非 |
XOR | 逻辑异或 |
1.add或者&&
- 逻辑与运算符and或&&表示所有操作数均为非零值且不为null时,计算所得结果为1;当一个或多个操作数为0时,所得结果为0;其余情况返回值为null。
- 多个操作数运算时,and两边一定要使用空格隔开,不然会影响结果的正确性。
【例】分别使用与运算符"and"和"&&"进行逻辑判断,SQL语句如下:
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.00 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)
2.OR或||
- 逻辑或运算符or或者||表示当两个操作数均为非null值且任意一个操作数为非零值时,结果为1,否则结果为0。
- 当有一个操作数为null,且另一个操作数为非零值时,结果为1,否则结果为null。
- 当两个操作数均为null时,所得结果为null。
【例】分别使用或运算符"OR"he "||"进行逻辑判断,SQL语句如下:
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.05 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.05 sec)
3.not或!
- 逻辑非运算,当操作数为0时,所得值为1。
- 当操作数为非零时 ,所得值为0。
- 操作数为null时,所得值为null。
【例】分别使用费运算符"not"和"!"进行逻辑判断,SQL语句如下:
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.05 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)
4.XOR
- 逻辑异或运算符XOR,当任意一个操作数为null时,返回值为null。
- 对于非null的操作数,如果两个操作数都是非0值或者都是0值,则返回结果为0。
- 如果一个为0的值,另一个为非0的值,返回结果为1。
a XOR b
的计算等同于(a and(not b))
或者((not a) and b)
【例】使用异或运算符"XOR",进行逻辑判断,SQL语句如下:
mysql> select 1 xor 1,
-> 0 xor 1,
-> 1 xor 0,
-> 1 xor null,
-> 1 xor 1 xor 1;
+---------+---------+---------+------------+---------------+
| 1 xor 1 | 0 xor 1 | 1 xor 0 | 1 xor null | 1 xor 1 xor 1 |
+---------+---------+---------+------------+---------------+
| 0 | 1 | 1 | NULL | 1 |
+---------+---------+---------+------------+---------------+
1 row in set (0.05 sec)
(4)位运算符
MySQL中的位运算符:
运算符 | 作用 |
---|---|
& | 位与 |
| | 位或 |
^ | 位异或 |
<< | 位左移 |
>> | 位右移 |
~ | 位取反,反转所有位 |
1.位与运算符(&)
- 将参与运算的几个操作数,
按对应二进制数逐位进行逻辑与运算。
- 对应的二进制位都为1,则该位的运算结果为1,否则为0。
【例】使用位与运算符进行运算,SQL语句如下:
mysql> select
-> 10 & 15,
-> 9 & 4 & 2;
+---------+-----------+
| 10 & 15 | 9 & 4 & 2 |
+---------+-----------+
| 10 | 0 |
+---------+-----------+
1 row in set (0.00 sec)
2.位或运算符(|)
- 将参与运算的几个数据
按对应的二进制数逐位进行逻辑或运算。
- 对应的二进制位有一个或两个为1则该位的运算结果为1,否则为0。
【例】使用位或运算符进行运算,SQL语句如下:
mysql> select
-> 10 | 15,
-> 9 | 4 | 2;
+---------+-----------+
| 10 | 15 | 9 | 4 | 2 |
+---------+-----------+
| 15 | 15 |
+---------+-----------+
1 row in set (0.00 sec)
3.位异或运算符(^)
- 将参与运算的两个数据,
按对应的二进制数逐位进行逻辑异或运算。
- 对应位的二进制数不同时,对应位的结果才为1,。
- 如果两个对应的二进制数逐位都为0或者都为1,则对应位的结果为0。
【例】使用位异或运算符进行运算,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的二进制数值为1111,按位异或运算之后,结果为0101,即整数5。
4.位左移运算符(<<)
- 位左移运算符<<使指定的二进制值的所有位都左移指定的位数,左移指定位数之后,左边高位的数值将被移出并丢弃,右边低位空出的位置补0。
【例】使用位左移运算符进行运算,SQL语句如下:
mysql> select 1<<2,
-> 4<<2;
+------+------+
| 1<<2 | 4<<2 |
+------+------+
| 4 | 16 |
+------+------+
1 row in set (0.05 sec)
1的二进制值为0000 0001,左移两位后变成0000 0100,即十进制的整数4。
5.位右移运算符(>>)
- 位右移运算符>>使指定的二进制值的所有位都右移指定的位数。右移指定位数后,右边低位的数值将被移出并丢弃,左边高位空出的位置用0补齐。
【例】使用位右移运算符进行运算,SQL语句如下:
mysql> select
-> 1>>1,
-> 16>>2;
+------+-------+
| 1>>1 | 16>>2 |
+------+-------+
| 0 | 4 |
+------+-------+
1 row in set (0.00 sec)
6.位取反运算符(~)
- 位取反运算的实质是将参与运算的数据,按对应的二进制逐位反转,即1取反后变0,0取反后变1。
【例】使用位取反运算符进行运算,SQL语句如下:
mysql> select 5 & ~1;
+--------+
| 5 & ~1 |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
可以使用bin()函数查看1取反之后的结果,SQL语句如下:
mysql> select bin(~1);
+------------------------------------------------------------------+
| bin(~1) |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
(5).运算符的优先级
一般情况下,级别高的运算符先进行计算,如果级别相同,MySQL按表达式的顺序从左到右依次计算。无法确定优先级的情况下,可以使用圆括号()改变优先级。