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);