一步一步学MySQL---17 MySQL运算符

17.1 算术运算符

MySQL中，算术运算符包含加、减、乘、除、求模。

运算符	描述	表达式形式
+	加法	x1+x2+x3+…+xn
-	减法	x1-x2-x3…-xn
*	乘法	x1*x2*x3…*xn
/（DIV）	除法	x1/x2/x3…/xn 或 x1 DIV x2 DIV x3…DIVxn
%（MOD）	求余	x1%x2%x3…%xn 或 x1 MOD x2 MOD x3…MOD xn

例1：执行SQL语句select，获取各种算术运算后的结果

mysql> select 6+4 加法运算,
-> 6-4 减法运算,
-> 6*4 乘法运算,
-> 6/4 除法运算,
-> 6 div 4 除法运算,
-> 6%4 求余运算,
-> 6 mod 4 求余运算;

或者：

mysql> select 6+4 加法运算, 6-4 减法运算, 6*4 乘法运算, 6/4 除法运算, 6 div 4 除法运算, 6%4 求余运算, 6 mod 4 求余运算;

例2：查询员工的月工资、年薪

mysql> select ename 员工, salary 月薪, salary*12 年薪 from employee;

注意：在做除法运算或者求余操作时，如果除数为0，将是非法操作，返回结果为NULL，如下所示：

mysql> select 6/0 除法运算, 6 div 0 除法运算, 6%0 求余运算, 6 mod 0 求余运算;

17.2 比较运算符

MySQL所支持的比较运算符有：

运算符	描述	表达式形式
>	大于	x1>x2
<	小于	x1
= 或者 <=>	等于	x1=x2 或者 x1<=>x2
!= 或者 <>	不等于	x1!=x2 或者 x1<>x2
>	大于等于	x1>=x2
<=	小于等于	x1<=x2
between and	存在于指定范围	x1 between and x2
is null	为空	x1 is null
in	存在于指定范围	X1 in(value1,value2,value3,…valuen)
like	通配符匹配	x1 like expression
regexp	正则表达式匹配	x1 regexp regularexpression

例1：执行带有“=”和“<=>”比较运算符的SQL语句select

mysql> #等号的使用

mysql> select 1=1 数值比较,
-> ‘ShangHai’=’ShangHai’,
-> 1+2=3+1 表达式比较,
-> 1<=>1 数值比较,
-> ‘ShangHai’<=>’ShangHai’ 字符串比较,
-> 1+2<=>3+3 表达式比较;

也可以写成：

mysql> select 1=1 数值比较, “ShangHai”=”ShangHai”, 1+2=3+1 表达式比较, 1<=>1 数值比较, “ShangHai”<=>”ShangHai” 字符串比较, 1+2<=>3+3 表达式比较;

说明：“=”和“<=>”比较运算符可以判断数值、字符串和表达式是否相等。如果相等，则返回1；如果不相等，则返回0。

注意：“=”和“<=>”比较运算符在比较字符串是否相等时，依据字符的ASC||码来进行判断。前者不能操作NULL（空值），后者可以。如：

mysql> select NULL=NULL “=符号效果”, NULL<=>NULL “<=>符号效果”;

例2：执行带有“！=”和“<>”比较运算符的SQL语句select

mysql> #不等号的使用#

mysql> select 1!=1 数值比较, “Shanghai”!=”ShangHai” 字符串比较, 1+2!=3+3 表达式比较, 1<>1 数值比较, “ShangHai”<>”ShangHai” 字符串比较, 1+2<>3+3 表达式比较;

说明：符号“！=”和“<>”用来判断数值、字符串和表达式是否不相等。如果不相等则返回1,；如果相等则返回0。
注意：从上面的结果可以看出，字符串比较时，MySQL是不区分大小写的，ShangHai与Shanghai是一样的。

例3：执行带有“>”、“>=”、“<”和“<=”比较运算符的SQL语句select。

mysql> select 1>=1 数值比较,
-> ‘abcdefg’>=’abcdfhg’ 字符串比较,
-> 1+2>=3+4 表达式比较,
-> 1>1 ‘>符号使用’,
-> ‘abcdefg’<=’abcdeeg’ as ‘<=符号使用’,
-> 1+2<3+4 ‘<符号使用’;

或者

mysql> select 1>=1 数值比较, ‘abcdefg’>=’abcdfhg’ 字符串比较, 1+2>=3+4 表达式比较, 1>1 ‘>符号使用’, ‘abcdefg’<=’abcdeeg’ as ‘<=符号使用’, 1+2<3+4 ‘<符号使用’;

说明：符号“>”、“>=”、“<”和“<=”主要用来判断数值、字符串和表达式的相关比较，如果表达式成立则返回1，否则返回0。
注意：“>”、“>=”、“<”和“<=”比较运算符也不能操作NULL（空值）

17.3 正则表达式运算符

所谓正则表达式，就是通过模式去匹配一类字符串。

MySQL支持的模式字符如下：

模式字符	含义
^	匹配字符串的开始部分
$	匹配字符串的结束部分
.	匹配字符串中的任意一个字符
[字符集合]	匹配字符集合中的任意一个字符
[^字符集合]	匹配字符集合外的任意一个字符
	匹配str1、str2和str3中任意一个字符串
*	匹配字符，包含0个和多个
+	匹配字符，至少包含1个
字符串{N}	字符串出现N次
字符串（M,N）	字符串出现至少M次，至多N次

例1：执行带有“^”模式字符的SQL语句select，实现比较是否以特定字符或字符串开头。

mysql> select “ShangHaiPuDong” regexp “^s” 特定字符开头,
-> “ShangHaiPuDong” regexp “^Shang” 特定字符串开头,
-> “ShangHaiPuDong” regexp “^P” 特定字符开头,
-> “ShangHaiPuDong” regexp “^SHANG” 特定字符串开头,
-> “ShangHaiPuDong” regexp “^Hai” 特定字符串开头;

说明：通过模式字符“^”可以比较是否以特定字符或特定字符串开头。如果相符，则返回1；如果不符，则返回0。
$
例2：执行带有“$”模式字符的SQL语句select，实现比较是否以特定字符或字符串结尾。

mysql> select “Shanghai” regexp 特定字符结尾, “Shanghai” regexp 特定字符串结尾, “ShangHai” regexp 特定字符结尾, “Shanghai” regexp 特定字符串结尾;

或者

mysql> select “Shanghai” regexp “” 特定字符结尾,
-> “Shanghai” regexp 特定字符串结尾,
-> “ShangHai” regexp 特定字符结尾,
-> “Shanghai” regexp 特定字符串结尾;

说明：通过模式字符“$”可以比较是否以特定字符或特定字符串结尾。如果相符，则返回1；如果不符，则返回0。

例3：执行带有“.”模式字符的SQL语句select，实现比较是否包含固定数目的任意字符。

mysql> select “shanghaipudong” regexp “^s…” 匹配以字符s开头的3个任意字符;

例4：执行带有“[]”和“[^]”模式字符的SQL语句select，可以实现比较是否包含指定字符中的任意一个和指定字符外任意一个。

mysql> select “shanghaipudong” regexp “[aefgh]” 指定字符集合中任一字符,
-> “shanghaipudong” regexp “[a-zA-Z]” 指定字符中的集合区间,
-> “shanghaipudong” regexp “[^aefgh]” 指定字符集合外的任一字符,
-> “shanghaipudong” regexp “[^a-zA-Z0-9]” 指定字符外集合区间;

说明：通过模式字符“[]”和“[^]”可以匹配指定的字符中合字符外的任意一个字符。如果相符，则返回1；如果不符，则返回0。

例5：执行带有“*”和“+”模式字符的SQL语句select，可以实现比较是否包含多个指定字符。

mysql> select “shanghai” regexp “b*g”,”shanghai” regexp “b+n”;

说明：通过模式字符“*”和“+”可以匹配字符g之前是否有多个字符a，前者可以表示有0个或任意个字符，后者至少表示一个字符。因此结果是1和0。

例6：执行带有“|”模式字符的SQL语句select，可以实现比较是否包含指定字符串中任意一个字符串。

mysql> select “mysqljavapython” regexp “java” 指定单个字符串,
-> “mysqljavapython” regexp “mysql|java” 指定多个字符串,
-> “mysqljavapython” regexp “mysql|java|python” 指定多个字符串,
-> “mysqljavapython” regexp “spark|python” 指定多个字符串,
-> “mysqljavapython” regexp “spark|html” 指定多个字符串;

或者：

mysql> select “mysqljavapython” regexp “java” 指定单个字符串, “mysqljavapython” regexp “mysql|java” 指定多个字符串, “mysqljavapython” regexp “mysql|java|python” 指定多个字符串, “mysqljavapython” regexp “spark|python” 指定多个字符串, “mysqljavapython” regexp “spark|html” 指定多个字符串;

说明：通过模式字符“|”可以匹配指定的任意一个字符串，如果只有一个字符串，则不需要模式字符“|”。如果相符，则返回1，否则返回0。

例7：执行带有“{M}”和“{M,N}”模式字符的SQL语句select，可以实现比较是否包含多个指定字符串。

mysql> select “cccjgong” regexp “c{3}” 匹配3个c,
-> “cccjgongg” regexp “g{2}” 匹配2个g,
-> “cgong” regexp “cj{1,2}” 至少1个最多2个,
-> “cjcjgong” regexp “cj{1,2}” 至少1个最多2个;

说明：c{3}表示字符c连续出现3次，cj{1,2}表示字符串cj至少出现1次，最多出现2次。

mysql> select “cccjgong” regexp “c{3}” 匹配3个c,
-> “chccjgong” regexp “c{3}” 匹配3个c,
-> “chccjggong” regexp “g{3}” 匹配3个g,
-> “chccjggong” regexp “g{2}” 匹配2个g,
-> “chccjggong” regexp “cj{1,2}” 至少1个最多2个cj,
-> “cjccjggong” regexp “cj{1,2}” 至少1个最多2个cj,
-> “cjccjcjgng” regexp “cj{1,2}” 至少1个最多2个cj,
-> “cjcjcjcjgng” regexp “cj{1,2}” 至少1个最多2个cj;

17.4 逻辑运算符

在MySQL中，逻辑运算符包含与、或、非、异或运算。

运算符	描述	表达式形式
and（&&）	与	x1 and x2 或者 x1 && x2
or（）	或	x1 or x2 或者 x1
not（!）	非	not x1 或者 !x1
xor	异或	x1 xor x2

例1：执行带有“and”或者“&&”逻辑运算符的SQL语句select

mysql> select 3 and 4, 3 && 4,
-> 0 and 4, 0 && 4,
-> 0 and NULL, 0 && null,
-> 3 and NULL, 3 && NULL;

说明：与运算符“and”和“&&”作用一样，所有操作数不为0且不为NUL（空值）时，结果返回1；存在一个操作数为0时，结果返回0；存在任意一个操作数为NULL且没有操作数为0时，结果返回NULL。

例2：执行带有“or”或者“||”逻辑运算符的SQL语句select

mysql> select 3 or 4,
-> 3 || 4,
-> 0 or 4,
-> 0 || 4,
-> 3 or NULL,
-> 3 || NULL,
-> 0 or NULL,
-> 0 || NULL,
-> 0 or 0,
-> 0 || 0;

说明：或运算符“or”和“||”作用一样，所有操作数中存在任何一个操作数不为0，结果返回1；所有操作数中不包含非0的数字，但包含NULL（空值），结果返回NULL；所有操作数都为0，结果返回0。

例3：执行带有“not”或者“!”逻辑运算符的SQL语句select

mysql> select not 3,
-> !3,
-> not 0,
-> !0,
-> not NULL,
-> !NULL;

说明：非运算符“not”和“!”作用一样，如果操作数为非0数字，结果返回0；如果操作数为0，结果返回1；如果操作数为NULL（空值），结果返回NULL。

例4：执行带有“xor”逻辑运算符的SQL语句select

mysql> select 3 xor 4,
-> 0 xor 0,
-> NULL xor NULL,
-> 0 xor 4,
-> 0 xor NULL,
-> 3 xor NULL,
->3 xor 4 xor 5;

说明：对于异或xor运算符，如果操作数中包含NULL（空值），结果返回NULL；如果操作数同为0或者同为非0，结果返回0；如果一个操作数为0，另一个不为0，结果返回1。

17.5 位运算

MySQL支持的位运算符如下：

运算符	描述	表达式形式
&	按位与	x1 & x2
	按位或	x1 x2
~	按位取反	~x1
^	按位异或	x1 ^ x2
<<	按位左移	x1 << x2
	按位右移	x1 >> x2

例1：执行带有“&”位运算符的SQL语句select

mysql> select bin(1 & 1), bin(1 & 0), bin(0 & 0);

mysql> select 5 & 6, bin(5 & 6) 二进制表示,
-> 4 & 5 & 6, bin(4 & 5 & 6) 二进制表示;

说明：按位与运算（&）时，首先将十进制数转换成二进制数，然后按位与操作，即两个数都为1时，结果才为1，其他都为0，最后将与后的结果转换成十进制数。

例2：执行带有“|”位运算符的SQL语句select

mysql> select bin(1 | 1), bin(1 | 0), bin(0 | 0);

mysql> select 5 | 6, bin(5 | 6) 二进制表示,
-> 4 | 5 | 6, bin(4 | 5 | 6) 二进制表示;

说明：按位或运算（|）时，首先将十进制数转换成二进制数，然后按位或操作，即只要有一个操作数为1，按位或的结果就为1，0与0按位或的结果为0。

例3：执行带有“~”位运算符的SQL语句select

mysql> select bin(~1), bin(~0);

mysql> select ~4, bin(~4) 二进制表示;

说明：按位取反运算（~）时，1取反的结果为0，0取反的结果为1。

例4：执行带有“^”位运算符的SQL语句select

mysql> select bin(1 ^ 1), bin(1 ^ 0), bin(0 ^ 1), bin(0 ^ 0);

mysql> select 4 ^ 5, bin(4 ^ 5);

说明：按位异或（^）运算时，相同的数异或的结果为0，不同的数异或的结果为1。

例5：执行带有“<<”和“>>”位运算符的SQL语句select
mysql> select bin(5) 二进制表示,
-> 5<<2, bin(5<<2) 二进制表示,
-> 5>>3, bin(5>>3) 二进制表示;

说明：左移右边补0，右移左边补0。