运算符的使用
# 1. 运算符分类
- 算数运算符
- 比较运算符
- 逻辑运算符
- 位运算符
# 2. 算数运算符
# 运算符
+、-、*、/、DIV、%、MOD
- 加
+ - 减
- - 乘
* - 除
/或DIV - 取模
%或MOD
# 数值运算符 + 和 -
+ 和 - 运算符处理数值运算，当用于其他类型，比如字符串类型，会隐式转换。
SELECT
100+'0',
100+'222',
100+'A',
100+'100a',
100+true,
100+false
FROM DUAL;
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
| 100+'0' | 100+'222' | 100+'A' | 100+'100a' | 100+true | 100+false |
|---|---|---|---|---|---|
| 100 | 322 | 100 | 200 | 101 | 100 |
# NULL 参与运算结果为 NULL
SELECT
111+NULL
FROM DUAL;
1
2
3
2
3
| 111+NULL |
|---|
NULL |
# 被除数为 0 为非法计算，返回值为 NULL
SELECT
10/0
FROM DUAL;
1
2
3
2
3
# 3. 比较运算符
比较结果为真的返回1，假的返回0，其它情况返回 NULL。
# 运算符
- 等于
= - 安全等于
<=> - 不等于
<>!= - 小于
< - 小于等于
<= - 大于
> - 大于等于
>= - 为空
IS NULL - 不为空
IS NOT NULL - 最小值
LEAST() - 最大值
GREATEST() - 范围
BETWEEN...AND ... - 为空
ISNULL() - 属于
IN(a,b,...) - 不属于
NOT IN(a,b,...) - 模糊
LIKE - 正则
REGEXP - 正则
REGLIKE
# 等于 =
但是无法比较两个操作数为 NULL 的情况，只要其中一个操作数为 NULL，返回值为 NULL
SELECT * FROM employees WHERE department_id=90;
1
# 安全等于 <=>
可以比较两个操作数都为 NULL 的情况，返回值为1
SELECT * FROM employees WHERE commission_pct<=>NULL;
1
# 不等于 <> !=
SELECT * FROM employees WHERE department_id!=90;
SELECT * FROM employees WHERE department_id<>90;
1
2
3
2
3
# 小于 <
SELECT * FROM employees WHERE department_id<90;
1
# 小于等于 <=
SELECT * FROM employees WHERE department_id<=90;
1
# 大于 >
SELECT * FROM employees WHERE department_id>90;
1
# 大于等于 >=
SELECT * FROM employees WHERE department_id>=90;
1
# 为空 IS NULL ISNULL()
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE ISNULL(commission_pct);
1
2
3
2
3
# 不为空 IS NOT NULL
SELECT * FROM employees WHERE commission_pct IS NOT NULL;
1
# 最小值 LEAST()
给定列表中的最小值，数值按小到大，字符串从左往右按照 ASCII 码
SELECT LEAST(444,222,11,66,888) AS m_col FROM DUAL;
SELECT LEAST('acc','abc','xyz') AS m_col FROM DUAL;
1
2
3
2
3
11
abc
1
2
3
2
3
# 最大值 GREATEST
给定列表中的最小值，数值按大到小，字符串从左往右按照 ASCII 码
SELECT GREATEST(444,222,11,66,888) AS m_col FROM DUAL;
SELECT GREATEST('acc','abc','xyz') AS m_col FROM DUAL;
1
2
3
2
3
888
xyz
1
2
3
2
3
# 范围 BETWEEN AND
BETWEEN 最小取值 AND 最大取值 相当于 >= 最小取值 && <= 最大取值
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;
1
# 属于 IN(a,b,...)
SELECT * FROM employees WHERE salary IN(17000,24000);
1
# 不属于 NOT IN(a,b,...)
SELECT * FROM employees WHERE salary NOT IN(17000,24000);
1
# 模糊 LIKE
需要搭配通配符使用
%匹配0个或多个任意字符_匹配1个任意字符
SELECT * FROM employees WHERE last_name LIKE 'ki';
SELECT * FROM employees WHERE last_name LIKE '%ki%';
SELECT * FROM employees WHERE last_name LIKE '_i%';
1
2
3
4
5
2
3
4
5
% 和 _ 被用于通配符，如果像匹配这两个字符，可以使用转义符 \ 转义
\% \_
SELECT * FROM employees WHERE job_id LIKE 'IT\_%';
1
同时，\ 是默认转义符，我们可以使用 ESCAPE 进行修改
SELECT * FROM employees WHERE job_id LIKE 'IT!_%' ESCAPE '!';
1
# 正则 REGEXP REHLIKE
SELECT * FROM employees WHERE job_id REGEXP '^IT';
SELECT * FROM employees WHERE job_id RLIKE '^IT';
1
2
3
2
3
# 4. 逻辑运算符
# 运算符
- 非
!NOT - 与
AND&& - 或
OR|| - 异或
XOR
# 非 ! NOT
SELECT * FROM employees WHERE NOT department_id=90;
1
# 与 AND &&
SELECT * FROM employees WHERE department_id =90 AND salary>10000;
SELECT * FROM employees WHERE department_id =90 && salary>10000;
1
2
3
2
3
# 或 OR ||
SELECT * FROM employees WHERE department_id =90 || salary>10000;
SELECT * FROM employees WHERE department_id =90 OR salary>10000;
1
2
3
2
3
# 异或 XOR
SELECT * FROM employees WHERE department_id =90 XOR salary>10000;
1
# 5. 位运算符
# 运算符
- 按位与
& - 按位或
| - 按位取反
~ - 按位异或
^ - 按位右移
>> - 按位左移
<<
# 按位与 &
SELECT 11 & 7 FROM DUAL;
1
3
1
11 1 0 1 1
7 0 1 1 1
& 0 0 1 1
=3
1
2
3
4
2
3
4
# 按位或 |
SELECT 8 | 7 FROM DUAL;
1
15
1
8 1 0 0 0
7 0 1 1 1
| 1 1 1 1
=15
1
2
3
4
2
3
4
# 按位取反 ~
SELECT 11 & ~7 FROM DUAL;
1
8
1
// 只考虑4位二进制
7 0 1 1 1
~7 1 0 0 0
11 1 0 1 1
& 1 0 0 0
=8
1
2
3
4
5
6
2
3
4
5
6
# 按位异或 ^
SELECT 7 ^ 5 FROM DUAL;
1
2
1
7 0 1 1 1
5 0 1 0 1
^ 0 0 1 0
=2
1
2
3
4
2
3
4
# 按位右移 >>
SELECT 17>>3 FROM DUAL;
1
2
1
17 1 0 0 0 1
>>3 1 0 (0 0 1)
1 0
=2
1
2
3
4
2
3
4
# 按位左移 <<
SELECT 3<<2 FROM DUAL;
1
3 0 0 1 1
<<2 1 1 (0 0)
1 1 0 0
=12
1
2
3
4
2
3
4
上次更新: 2023/07/20, 16:47:14