1. 运算符分类

  • 算数运算符
  • 比较运算符
  • 逻辑运算符
  • 位运算符

2. 算数运算符

运算符

+、-、*、/、DIV、%、MOD

  • 加 +
  • 减 -
  • 乘 *
  • 除 / 或 DIV
  • 取模 % 或 MOD

数值运算符 + 和 -

+ 和 - 运算符处理数值运算，当用于其他类型，比如字符串类型，会隐式转换。

SELECT 
100+'0',
100+'222',
100+'A',
100+'100a',
100+true,
100+false
FROM DUAL;
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;
111+NULL
NULL

被除数为 0 为非法计算，返回值为 NULL

SELECT 
10/0
FROM DUAL;

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;

安全等于 <=>

可以比较两个操作数都为 NULL 的情况，返回值为1

SELECT * FROM employees WHERE commission_pct<=>NULL;

不等于 <> !=

SELECT * FROM employees WHERE department_id!=90;

SELECT * FROM employees WHERE department_id<>90;

小于 <

SELECT * FROM employees WHERE department_id<90;

小于等于 <=

SELECT * FROM employees WHERE department_id<=90;

大于 >

SELECT * FROM employees WHERE department_id>90;

大于等于 >=

SELECT * FROM employees WHERE department_id>=90;

为空 IS NULL ISNULL()

SELECT * FROM employees WHERE commission_pct IS NULL;

SELECT * FROM employees WHERE ISNULL(commission_pct);

不为空 IS NOT NULL

SELECT * FROM employees WHERE commission_pct IS NOT NULL;

最小值 LEAST()

给定列表中的最小值，数值按小到大，字符串从左往右按照 ASCII 码

SELECT LEAST(444,222,11,66,888) AS m_col FROM DUAL;

SELECT LEAST('acc','abc','xyz') AS m_col FROM DUAL;
11

abc

最大值 GREATEST

给定列表中的最小值，数值按大到小，字符串从左往右按照 ASCII 码

SELECT GREATEST(444,222,11,66,888) AS m_col FROM DUAL;

SELECT GREATEST('acc','abc','xyz') AS m_col FROM DUAL;
888

xyz

范围 BETWEEN AND

BETWEEN 最小取值 AND 最大取值 相当于 >= 最小取值 && <= 最大取值

SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

属于 IN(a,b,...)

SELECT * FROM employees WHERE salary IN(17000,24000);

不属于 NOT IN(a,b,...)

SELECT * FROM employees WHERE salary NOT IN(17000,24000);

模糊 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%';

% 和 _ 被用于通配符，如果像匹配这两个字符，可以使用转义符 \ 转义

\% \_

SELECT * FROM employees WHERE job_id  LIKE 'IT\_%';

同时，\ 是默认转义符，我们可以使用 ESCAPE 进行修改

SELECT * FROM employees WHERE job_id  LIKE 'IT!_%' ESCAPE '!';

正则 REGEXP REHLIKE

SELECT * FROM employees WHERE job_id  REGEXP '^IT';

SELECT * FROM employees WHERE job_id  RLIKE '^IT';

4. 逻辑运算符

运算符

  • 非 ! NOT
  • 与 AND &&
  • 或 OR ||
  • 异或 XOR

非 ! NOT

SELECT * FROM employees WHERE NOT department_id=90;

与 AND &&

SELECT * FROM employees WHERE  department_id =90 AND salary>10000;

SELECT * FROM employees WHERE  department_id =90 && salary>10000;

或 OR ||

SELECT * FROM employees WHERE  department_id =90 || salary>10000;

SELECT * FROM employees WHERE  department_id =90 OR salary>10000;

异或 XOR

SELECT * FROM employees WHERE  department_id =90 XOR salary>10000;

5. 位运算符

运算符

  • 按位与 &
  • 按位或 |
  • 按位取反 ~
  • 按位异或 ^
  • 按位右移 >>
  • 按位左移 <<

按位与 &

SELECT 11 & 7 FROM DUAL;
3
11  1 0 1 1
7   0 1 1 1
&   0 0 1 1
=3

按位或 |

SELECT 8 | 7 FROM DUAL;
15
8   1 0 0 0
7   0 1 1 1
|   1 1 1 1
=15

按位取反 ~

SELECT 11 & ~7 FROM DUAL;
8
// 只考虑4位二进制
7   0 1 1 1
~7  1 0 0 0
11  1 0 1 1
&   1 0 0 0
=8

按位异或 ^

SELECT 7 ^ 5 FROM DUAL;
2
7   0 1 1 1
5   0 1 0 1
^   0 0 1 0
=2

按位右移 >>

SELECT 17>>3 FROM DUAL;
2
17  1 0 0 0 1
>>3 1 0 (0 0 1)
    1 0
=2

按位左移 <<

SELECT 3<<2 FROM DUAL;
3   0 0 1 1
<<2 1 1 (0 0)
    1 1 0 0
=12