04. 运算符的使用

4.1 算数运算符

重要的事情说三遍：NULL参与运算值为NULL! NULL参与运算值为NULL! NULL参与运算值为NULL!

先介绍比较简单的加减乘除以及取余运算。

运算符	名称	示例
+	加法运算符	SELECT A+B
-	减法运算符	SELECT A-B
*	乘法运算符	SELECT A*B
/或DIV	除法运算符	SELECT A/B
%或者MOD	取余运算符	SELECT A%B

实际使用

# 加减法使用
SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5
FROM DUAL;
/*
+-----+---------+---------+----------+--------------+------------+------------+
| 100 | 100 + 0 | 100 - 0 | 100 + 50 | 100 + 50 -30 | 100 + 35.5 | 100 - 35.5 |
+-----+---------+---------+----------+--------------+------------+------------+
| 100 |     100 |     100 |      150 |          120 | 135.5      | 64.5       |
+-----+---------+---------+----------+--------------+------------+------------+
1 row in set (0.02 sec)
*/

SELECT "1" + 1;
/*
+---------+
| "1" + 1 |
+---------+
|       2 |
+---------+
1 row in set (0.03 sec)
*/

SELECT 1 + NULL;
/*
+----------+
| 1 + NULL |
+----------+
| NULL     |
+----------+
1 row in set (0.03 sec)
*/

• 整数之间加减运算结果还是整数
• 整数与浮点数运算结果是浮点数
• JAVA中可以用+进行字符串拼接，但是在MYSQL中+只能用来进行数值运算，如果遇到非数值类型，会先转换成数值类型，如果无法转换，就按照0计算（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）。

# 乘除使用
SELECT 100 * 1, 100 * 1.0, 100 / 1, 100 / 6, 100.0 DIV 1.0, 100 / 0.0 
FROM DUAL;
/*
+---------+-----------+----------+---------+---------------+-----------+
| 100 * 1 | 100 * 1.0 | 100 / 1  | 100 / 6 | 100.0 DIV 1.0 | 100 / 0.0 |
+---------+-----------+----------+---------+---------------+-----------+
|     100 | 100.0     | 100.0000 | 16.6667 |           100 | NULL      |
+---------+-----------+----------+---------+---------------+-----------+
1 row in set (0.03 sec)
*/

• 一个数(无论是浮点数还是整数)乘以整数1为原来的数(原来是浮点现在就是浮点，原来是整数现在就是整数)；乘以浮点数1后变为浮点数(无论原来整数还是浮点数)，大小与原来相同。
• 一个数除以一个整数或者浮点数，无论能否整除，结果都是浮点数；无论整除还是非整除结果保留4位小数(不会四舍五入)
• MYSQL中除以0结果为NULL
• DIV和/不一样，DIV的结果会取整，去掉小数

# 取余运算符
SELECT 100 % 2, 100 % 2.0, 100 % 3, 100 % 3.0, 100 % 0, 100 % 0.0
FROM DUAL;
/*
+---------+-----------+---------+-----------+---------+-----------+
| 100 % 2 | 100 % 2.0 | 100 % 3 | 100 % 3.0 | 100 % 0 | 100 % 0.0 |
+---------+-----------+---------+-----------+---------+-----------+
|       0 | 0.0       |       1 | 1.0       | NULL    | NULL      |
+---------+-----------+---------+-----------+---------+-----------+
1 row in set (0.03 sec)
+---------+-----------+---------+-----------+---------+-----------+
| 100 % 2 | 100 % 2.0 | 100 % 3 | 100 % 3.0 | 100 % 0 | 100 % 0.0 |
+---------+-----------+---------+-----------+---------+-----------+
|       0 | 0.0       |       1 | 1.0       | NULL    | NULL      |
+---------+-----------+---------+-----------+---------+-----------+
1 row in set (0.03 sec)
*/

• 取余只要除数和被除数有一个是浮点数，结果就是浮点数；都是整数，结果就是整数
• 取余除数如果是0.0或者0，取余结果都是NULL

4.2 比较运算符

比较运算符用来对表达式左右两边的操作数进行比较，结果为真返回1，结果为假返回0；

运算符	名称	示例
=	等于	SELECT C FROM TABLE WHERE A = B
<=>	安全等于	SELECT C FROM TABLE WHERE A <=> B
<>(!=)	不等于	SELECT C FROM TABLE WHERE A <> B
<	小于	SELECT C FROM TABLE WHERE A < B
<=	小于等于	SELECT C FROM TABLE WHERE A <= B
>	大于	SELECT C FROM TABLE WHERE A > B
>=	大于等于	SELECT C FROM TABLE WHERE A >= B

实际使用

# 等于运算符
SELECT 1.0 = 1, 'a' = 'b', '1' = 1, 'a' = 1, 'a' = 0, NULL = 0;
/*
+---------+-----------+---------+---------+---------+----------+
| 1.0 = 1 | 'a' = 'b' | '1' = 1 | 'a' = 1 | 'a' = 0 | NULL = 0 |
+---------+-----------+---------+---------+---------+----------+
|       1 |         0 |       1 |       0 |       1 | NULL     |
+---------+-----------+---------+---------+---------+----------+
1 row in set (0.03 sec)
*/

• 等号两边的值都是数字，MySQL会比较两个数值是否相同(例如1.0 = 1会返回真)。
• 等号两边都是字符串，MYSQL会比较两边的字符串是否一致
• 如果一边是字符串一边是数字，会先将字符串转换成数字，无法转换视为0，然后比较数字大小
• 等号两边有一个为NULL，结果为NULL

MySQL中赋值符号为 :=

# 安全等于
SELECT NULL <=> NULL;
/*
+---------------+
| NULL <=> NULL |
+---------------+
|             1 |
+---------------+
1 row in set (0.03 sec)
*/

• 安全等于的唯一区别是 可以比较NULL，如果安全等于两边都是NULL，返回1

# 不等运算
SELECT 'a' < 'abf', 'a' < 1, 'a' <> 0;
/*
+-------------+---------+----------+
| 'a' < 'abf' | 'a' < 1 | 'a' <> 0 |
+-------------+---------+----------+
|           1 |       1 |        0 |
+-------------+---------+----------+
1 row in set (0.04 sec)
*/

• 不等运算符就比较简单，相等就返回1，等于返回0
• 对于两边都是字符串比较大小，从第一位开始比较，ASCII码大的就大，如果一样大比较下一位，最终字符串长的大。

4.3 非符号类型运算符

IS NULL

空运算符(ISNULL或者IS NULL)，判断一个值为NULL，如果为NULL返回1，否则返回0

SELECT NULL IS NULL, ISNULL(NULL), 1 IS NULL, ISNULL(1);
/*
+--------------+--------------+-----------+-----------+
| null is null | ISNULL(NULL) | 1 IS NULL | ISNULL(1) |
+--------------+--------------+-----------+-----------+
|            1 |            1 |         0 |         0 |
+--------------+--------------+-----------+-----------+
1 row in set (0.05 sec)
*/

IS NOT NULL

和IS NULL相反

SELECT NULL IS NOT NULL,1 IS NOT NULL;
/*
+------------------+---------------+
| NULL IS NOT NULL | 1 IS NOT NULL |
+------------------+---------------+
|                0 |             1 |
+------------------+---------------+
*/

LEAST

最小值运算符，语法格式LEAST(值1, 值2, 值3, ...., 值n)。其中，n表示参数列表中数据个数

# 如果都是字符串或者是数字，直接取最小的即可
SELECT LEAST(1,'a',2);
# 如果数值里面有字符串还有数字，那么在MySQL8和MySQL5中的结果不一致
/*
MYSQL8中，将字符串转换成数字，无法转换去除掉
+----------------+
| LEAST(1,'a',2) |
+----------------+
| 1              |
+----------------+

MySQL5中还是和上面保持一直，将字符串转换成数字，无法转换默认为0
+----------------+
| LEAST(1,'a',2) |
+----------------+
| 0              |
+----------------+
*/

GREATEST

最大值运算符，用法同最小值运算符。

BETWEEN AND

BETWEEN AND可以确定一个区间，用来判断数据是否在区间内，是一个闭区间。

SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c', 0 BETWEEN 'a' AND 'c';
/*
+-------------------+----------------------+-------------------------+-----------------------+
| 1 BETWEEN 0 AND 1 | 10 BETWEEN 11 AND 12 | 'b' BETWEEN 'a' AND 'c' | 1 BETWEEN 'a' AND 'c' |
+-------------------+----------------------+-------------------------+-----------------------+
|                 1 |                    0 |                       1 |                     0 |
+-------------------+----------------------+-------------------------+-----------------------+
*/

如果判断的值和区间的值不是同种类型，还是先将字符串转换成数字，无法转换视为0

IN 和 NOT IN

IN是用于判断数值是否是在IN列表中，NOT IN正好相反

SELECT 'a' IN ('a','b','c'), 'b' IN ('a',2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
/*
+--------------------+------------------+-------------------+--------------------+
| 0 IN ('a','b','c') | 'b' IN ('a',2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+--------------------+------------------+-------------------+--------------------+
|                  1 |                0 | NULL              |                  1 |
+--------------------+------------------+-------------------+--------------------+
*/

• 这里是将数值一个个与列表中的数据比较，所以当判断的数据是数字，列表中是字符串，会先将字符串转换成数字，无法转换的视为0。

LIKE

LIKE运算符主要是用来进行模糊搜索的

# 主要有 % 和 _ 通配符
% 匹配一个或者多个字符
_ 只能匹配一个字符

实际使用

# 有NULL进行的操作结果都为NULL
SELECT NULL LIKE 'abc', 'abc' LIKE NULL, NULL LIKE NULL;
/*
+-----------------+-----------------+----------------+
| NULL LIKE 'abc' | 'abc' LIKE NULL | NULL LIKE NULL |
+-----------------+-----------------+----------------+
| NULL            | NULL            | NULL           |
+-----------------+-----------------+----------------+
1 row in set (0.05 sec)
*/

# 搜索fisrt_name是S开头的人
SELECT * from employees WHERE first_name LIKE 'S%';
# 搜索last_name中第二个字符是o的人
SELECT * from employees WHERE last_name LIKE '_o%';

ESCAPE

在模糊搜索的时候，如果匹配的字符串里面有%或者_，需要用转移字符

# 在%和_的前面需要用转移字符
# 这里匹配以IT_开头的job_id
SELECT * FROM jobs WHERE job_id LIKE "IT\_%";

如果不想用转义字符\，可以利用ESCAPE来指定转移字符

# 指定$作为转义字符
1SELECT * FROM jobs WHERE job_id LIKE "IT$_%" ESCAPE "$";

REGEXP

REGEXP是用来匹配正则表达式字符串的，语法格式为：expr REGEXP 匹配条件

常用通配符：

• ^ 匹配以该字符后面的字符开头的字符串
• $ 匹配以该字符前面的字符结尾的字符串
• . 匹配任何一个单字0-9符
• [....] 方括号内的任何字符，例如[abc] 匹配a或者b或者c，[a-z]匹配所有字母，[0-9]匹配所有数字
• *匹配零个或者多个在他面前的字符，例如 x*就是匹配任何数量的x字符，[0-9]*匹配任何数量的数字，*匹配任何数量的任何字符

SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk';
/*
+------------------------+------------------------+------------------------+
| 'shkstart' REGEXP '^s' | 'shkstart' REGEXP 't$' | 'shkstart' REGEXP 'hk' |
+------------------------+------------------------+------------------------+
|                      1 |                      1 |                      1 |
+------------------------+------------------------+------------------------+
1 row in set (0.06 sec)
*/

SELECT 'atguigu' REGEXP 'gu.gu', 'atguigu' REGEXP '[ab]';
/*
+--------------------------+-------------------------+
| 'atguigu' REGEXP 'gu.gu' | 'atguigu' REGEXP '[ab]' |
+--------------------------+-------------------------+
|                        1 |                       1 |
+--------------------------+-------------------------+
1 row in set (0.04 sec)
*/

4.4 逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL

运算符	作用	示例
NOT 或 !	非	SELECT NOT A
AND 或 &&	与	SELECT A AND B
OR 或 ||	或	SELECT A OR B
XOR	异或	SELECT A XOR B

SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;
/*
+-------+-------+----------+--------+----------+
| NOT 1 | NOT 0 | NOT(1+1) | NOT !1 | NOT NULL |
+-------+-------+----------+--------+----------+
|     0 |     1 |        0 |      1 | NULL     |
+-------+-------+----------+--------+----------+
1 row in set (0.04 sec)
*/

SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL;
/*
+----------+---------+------------+------------+
| 1 AND -1 | 0 AND 1 | 0 AND NULL | 1 AND NULL |
+----------+---------+------------+------------+
|        1 |       0 |          0 | NULL       |
+----------+---------+------------+------------+
1 row in set (0.05 sec)
*/

SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL;
/*
+---------+--------+-----------+-----------+--------------+
| 1 OR -1 | 1 OR 0 | 1 OR NULL | 0 || NULL | NULL || NULL |
+---------+--------+-----------+-----------+--------------+
|       1 |      1 |         1 | NULL      | NULL         |
+---------+--------+-----------+-----------+--------------+
1 row in set (0.04 sec)
*/

SELECT 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0;
/*
+----------+---------+---------+------------+---------------+---------------+
| 1 XOR -1 | 1 XOR 0 | 0 XOR 0 | 1 XOR NULL | 1 XOR 1 XOR 1 | 0 XOR 0 XOR 0 |
+----------+---------+---------+------------+---------------+---------------+
|        0 |       1 |       0 | NULL       |             1 |             0 |
+----------+---------+---------+------------+---------------+---------------+
1 row in set (0.04 sec)
*/

OR的优先级高于AND

4.5 位运算符

按位运算和java中类似

运算符	作用	示例
&	按位与	A & B
|	按位或	A | B
^	按位异或	A ^ B
~	按位取反	~A
>>	按位右移	A>>2
<<	按位左移	A<<2

4.6 运算符的优先级

优先级	运算符
1	:=, = 赋值
2	||, OR, XOR
3	&&、AND
4	NOT
5	BETWEEN、CASE、WHEN、THEN、ELSE
6	比较运算符(=, <=>, >=, <=, <, >, <>, !=, IS, LIKE, REGEXP, IN, NOT IN)
7	|
8	&
9	<<和>>
10	-、+
11	*，/，DIV, %
12	^
13	-和~
14	!
15	()

4.7 练习题

# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT first_name, last_name, salary 
FROM employees WHERE salary < 5000 OR salary > 12000;

SELECT first_name, last_name, salary 
FROM employees WHERE salary NOT BETWEEN 5000 AND 12000;

# 2.选择在20或50号部门工作的员工姓名和部门号
SELECT first_name, last_name, department_id 
FROM employees WHERE department_id IN(20, 50);

# 3.选择公司中没有管理者的员工姓名及job_id
SELECT first_name, last_name, job_id
FROM employees WHERE manager_id IS NULL;

# 4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT first_name, last_name, salary, commission_pct
FROM employees WHERE commission_pct IS NOT NULL;

# 5.选择员工姓名的第三个字母是a的员工姓名
SELECT first_name, last_name
FROM employees WHERE last_name LIKE "__a%";

# 6.选择姓名中有字母a和k的员工姓名
SELECT first_name, last_name
FROM employees WHERE last_name LIKE "%a%k%" OR last_name LIKE "%k%a%";

# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT * FROM employees WHERE first_name LIKE "%e";
SELECT * FROM employees WHERE first_name REGEXP "e$";

# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT first_name, last_name, job_id, manager_id
FROM employees WHERE manager_id BETWEEN 80 AND 100;

# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT first_name, last_name, salary, manager_id
FROM employees WHERE manager_id IN(100, 101, 110);