比较运算符
比较运算符的结果会有三个可能：
- 结果为真，返回1
- 结果为假，返回0
- 其他情况返回NULL
符号型
- 等于
=和安全等于<=>：二者的作用是相似的，唯一的作用就是<=>可以对NULL进行判断。NULL <=> NULL的返回值为1，NULL <=> 1返回值为0，不为NULL
- 不等于
<>或者!=
< > <= >=
非符号型
判空/非空
IS NULL或者ISNULL()：为空
1
2
3
4
5
6
7
8
9
10
| SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NULL;
# 等价于
# WHERE ISNULL(commission_pct);
+
| last_name | salary | commission_pct |
+
| King | 24000.00 | NULL |
| Kochhar | 17000.00 | NULL |
|
IS NOT NULL：不为NULL
1
2
3
4
5
6
7
8
| SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
+
| last_name | salary | commission_pct |
+
| Russell | 14000.00 | 0.40 |
| Partners | 13500.00 | 0.30 |
|
最值
最小值LAST()，最大值GREATEST()
1
2
3
4
5
6
7
| SELECT LEAST(first_name, last_name) "姓/名中较大者"
FROM employees;
+
| 姓/名中较大者 |
+
| King |
| Kochhar |
|
区间
BETWEEN 条件1 AND 条件2：查询[条件1, 条件2]（包含边界）范围内的数据
1
2
3
4
5
6
7
8
| SELECT employee_id, last_name, salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
+
| employee_id | last_name | salary |
+
| 104 | Ernst | 6000.00 |
| 111 | Sciarra | 7700.00 |
|
在/不在集合内
IN (集合)，NOT IN(集合)
1
2
3
4
5
6
7
8
9
10
| SELECT last_name, salary, department_id
FROM employees
WHERE department_id IN (10, 20, 30);
+
| last_name | salary | department_id |
+
| Whalen | 4400.00 | 10 |
| Hartstein | 13000.00 | 20 |
| Fay | 6000.00 | 20 |
| Raphaely | 11000.00 | 30 |
|
模糊查询
LIKE关键字
1
2
3
4
5
6
7
8
9
10
11
12
| SELECT last_name
FROM employees
# % 代表任意个不确定字符
WHERE last_name LIKE 'A%';
+
| last_name |
+
| Austin |
| Atkinson |
| Ande |
| Abel |
+
|
正则表达式
expr REGEXP 正则表达式，若expr满足正则表达式，返回1，否则返回0