【MySQL】运算符，MOD，%，<=>，<>，ISNULL，LEAST，GREATEST，BETWEEN AND，NOT IN，LIKE，REGEXP，ESCAPE，与或非，移位

1.算术运算符

（1）加法与减法

SELECT 100,100+0,100-0,100+50,100+50-30,100+35.5,100-35.5
FROM DUAL;

在MySQL中+只表示数值相加，如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。字符串拼接使用CONCAT()实现。

（2）乘法与除法

除以一个数不管是否除尽结果都为浮点数，并保留4位小数，0作为除数结果为null

SELECT 100,100*1,100*1.0,100/1.0,100/2,100+2*5/2,100/3,100 DIV 0
FROM DUAL;

在Navicat中执行则为

计算员工年基本工资

SELECT employee_id,salary,salary*12 annual_sal
FROM employees;

（3）求模求余运算

SELECT 12%3,12 MOD 5 FROM DUAL;

查询id为偶数的员工

SELECT * FROM employees
WHERE employee_id MOD 2=0;

2.比较运算符

比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回null。

（1）等号=

• 字符串：比较每个字符串中字符的ANSI编码是否相等
• 整数：比较值大小
• 一个整数一个字符串：将字符串转化为数字进行比较
• 有一个为null：比较结果为null
• SQL中赋值符号使用:=

SELECT 1=1,1='1',1=0,'a'='a',(5+3)=(2+6),''=NULL,NULL=NULL;

SELECT 1=2,0='abc',1='abc' FROM DUAL;

SQL中用=，在Java用==

SELECT employee_id,salary FROM employees WHERE salary=8000;

（2）安全等于<=>

<=> 和 = 的区别在于，<=> 可以用来对NULL进行判断。

• 两个都为NULL：返回1
• 一个为NULL：返回0

SELECT 1 <=>'1',1<=>0,'a'<=>'a',(5+3)<=>(2+6),''<=>NULL,NUll<=>NUll FROM DUAL;

使用

SELECT employee_id,commission_pct FROM employees WHERE commission_pct = 0.40;
 
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> 0.40;

（3）不等于<>和!=

• 不相等则返回1
• 相等则返回0
• 任意一个为NULL或都为NULL，则结果为NULL

SELECT 1<>1,1!=2,'a'!='b',(3+4)<>(2+6),'a'!=NULL,NULL<>NULL;

(4)空运算符IS NUll或ISNULL

判断一个值是否为NULL，为NULL则返回1，否则返回0。

SELECT NULL IS NULL,ISNULL(NULL),ISNULL('a'),1 IS NULL;

四种比较方式

SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=>NULL;
SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct);
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;

等号=和NULL运算，结果都为NULL

SELECT last_name,manager_id
FROM employees
WHERE manager_id IS NULL;

（5）非空IS NOT NULL

判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。

SELECT NULL IS NOT NULL,'a' IS NOT NULL,1 IS NOT NULL;

SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);

（6）最小值LEAST(值1,值2,...,值n)

在有两个或多个参数的情况下，返回最小值。
参数为字符串时，返回字母表中最靠前的字符。
参数中有NULL时，返回NULL。

SELECT LEAST(1,0,2),LEAST('b','a','c'),LEAST(1,NULL,2),LEAST('fjhiawehof','geas','fjho');

（7）最大值GREATEST(值1,值2,...,值n)

当有两个或多个参数时，返回值为最大值。
参数中有NULL则返回NULL。
参数为字符串时，返回字母表中最靠后的字符。

SELECT GREATEST(1,0,2),GREATEST('b','a','c'),GREATEST(1,NULL,2),GREATEST('fjhiawehof','geas','fjho');

（8）BETWEEN AND

值在区间内则返回1，不存在返回0

SELECT 1 BETWEEN 0 AND 1,10 BETWEEN 11 AND 12,'b' BETWEEN 'a' AND 'c';

查询工资为2500到3500之间的员工

SELECT last_name,salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

（9）IN

列表中存在返回1，不存在返回0
判断值为NULL或IN列表中存在NULL，则结果为NULL

SELECT 'a' IN('a','b','c'),1 IN(2,3),NULL IN('a','b'),'a' IN('a',NULL);

查询manager_id存在于(100,101,201)中的员工

SELECT employee_id,last_name,salary,manager_id
FROM employees
WHERE manager_id IN(100,101,201);

（10）NOT IN

不是列表中的值则返回1，否则返回0

SELECT 'a' NOT IN('a','b','c'),1 NOT IN(2,3);

（11）LIKE

模糊匹配，满足条件则返回1，否则返回0。
如果给定的值或者匹配条件为NULL，则返回结果为NULL。

"%"：匹配0个或多个字符
"_"：只能匹配一个字符

SELECT NULL LIKE 'abc','abc' LIKE NULL;

查询first_name以S开头的员工，查询last_name中第二个字符为o的员工

SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';
 
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';

ESCAPE转义符

如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。
""：表示通配符，占一位，加上ESCAPE '$'则后面的""不作为通配符，而是普通字符

SELECT job_id
FROM jobs
WHERE job_id LIKE 'IT\_%';
 
SELECT job_id
FROM jobs
WHERE job_id LIKE 'IT$_%' ESCAPE '$';

（12）REGEXP

用来匹配字符串，满足条件则返回1，否则返回0，匹配值或匹配条件中任意一个为NULL，则结果为NULL。
匹配值 REGEXP 匹配条件
常用的通配符

'^s'匹配以s开头的字符串。
't$'匹配以t结尾的字符串。
'.'匹配任何一个单字符。
"[abc]"匹配方括号内的任何字符，"a","b"或"c"。"[a-z]"匹配任何字母，"[0-9]"匹配任何数字。
"*"匹配任何数量的任何字符，"x*"匹配任何数量的'x'字符，"[0-9]*"匹配任何数量的数字。

SELECT 'helloworld' REGEXP '^h','helloworld' REGEXP 'd$','helloworld' REGEXP 'lo';
 
SELECT 'fujianjian' REGEXP 'ji.nji','fujianjian' REGEXP '[ijk]';

3.逻辑运算符

（1）逻辑非NOT或！

给定的值为0时返回1，给定的值为非0时返回0。给定的值为NULL时返回NULL。

SELECT NOT 1,NOT 0,NOT(1+1),NOT !1,NOT NULL;

SELECT last_name,job_id
FROM employees
WHERE job_id NOT IN('IT_PROG','ST_CLERK','SA_REP');

（2）逻辑与AND或&&

当给定的所有值都为非0，并且都不为NULL，返回1；给定的值中有0时返回0；否则返回NULL

SELECT 1 AND -1,0 AND 1,0 AND NULL,1 AND NULL;

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary>=10000 AND job_id like '%MAN%';

（3）逻辑或OR或||

当给定的值都不为NULL，并且任何一个值为非0时，则返回1，否则返回0；
当一个值为NULL并且另一个值为非0时，返回1，否则返回NULL；
当两个值都为NULL时，返回NULL。

OR可以和AND一起使用，AND的优先级高于OR，运算时，先对AND两边的操作数进行操作，再与OR中操作数结合。

SELECT 1 OR -1,1 OR 0,1 OR NULL,0||NULL,NULL||NULL;

查询工资不在9000到12000之间的员工的几种方式

SELECT employee_id,salary
FROM employees
WHERE NOT (salary>=9000 AND salary<=12000);
 
SELECT employee_id,salary
FROM employees
WHERE salary<9000 OR salary>12000;
 
SELECT employee_id,salary
FROM employees
WHERE salary NOT BETWEEN 9000 AND 12000;

（4）逻辑异或XOR

给定的值中任意一个为NULL时，返回NULL；
如果两个非NULL值都是0或者都不等于0时，则返回0；
如果一个值为0，另一个值不为0时则返回1。

异或：两个值不相同时返回1，不相同表示结果为真-假

SELECT 1 XOR -1,1 XOR 0,0 XOR 0,1 XOR NULL,1 XOR 1,1 XOR 1 XOR 1,0 XOR 0 XOR 0;

SELECT last_name,department_id,salary
FROM employees
WHERE department_id IN (10,20) XOR salary>8000;

返回结果为：真 XOR 假；假 XOR 真 两种情况

4.位运算符

位运算符是在二进制数上进行计算的。因此会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。

（1）按位与&

都为1时返回1，否则返回0。

SELECT 1&10,20&30;

（2）按位或|

有1时返回1，否则返回0。

SELECT 1|10,20|30;

（3）按位异或^

二进制位数值不同时返回1，否则返回0。

SELECT 1^10,20^30;

SELECT 12&5,12|5,12^5 FROM DUAL;

（4）按位取反~

将1变为0，0变为1，取反运算符的优先级高于&运算符。

SELECT 10&~1;

（5）按位右移>>

二进制值右移指定位数，右边低位丢弃，左边高位补0。

SELECT 1>>2,4>>2;

（6）按位左移<<

二进制值左移指定位数，左边高位丢弃，右边低位补0。

SELECT 1<<2,4<<2;

5.运算符的优先级

6.正则表达式查询

（1）查询以字母b开头的记录。
（2）查询以字母y结尾的记录。
（3）查询a与g之间只有一个字母的记录。
（4）*表示匹配任意数量，包括0次，查询以字母b开头且后面出现字母a的记录。
（5）加号+表示至少匹配一次，查询以字母b开头且后面出现字母a至少一次的记录。

SELECT * FROM fruits WHERE f_name REGEXP '^b';
 
SELECT * FROM fruits WHERE f_name REGEXP 'y$';
 
SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
 
SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
 
SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
 

（6）查询包含字符串on的记录。
（7）查询包含字符串on或ap的记录。
（8）LIKE匹配的字符串如果在文本中出现，则找不到它，相应的行也不会返回。REGEXP则可以。
（9）查询包含字母o或t的记录。
（10）查询包含4、5或6的记录。

SELECT * FROM fruits WHERE f_name REGEXP 'on';
 
SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
 
SELECT * FROM fruits WHERE f_name LIKE 'on';
 
SELECT * FROM fruits WHERE f_name REGEXP '[ot]'
 
SELECT * FROM fruits WHERE s_id REGEXP '[456]'

（11）"[^字符集合]"匹配不在指定集合中的任何字符。查询不包含a_e和数字12字符的记录。
（12）查询字母x至少出现2次的记录。
（13）查询字符串ba至少出现1次，最多3次的记录。

SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
 
SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
 
SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}'