比较运算符
=
|
1 2 3 4 5 6 7 8 |
//sql标准 select name,sal from money where sal = 2000; //mysql会自动类型转换 select name,sal from money where sal = '2000'; //sql标准 select job,name from emp where job = 'MANAGER'; //mysql会自动类型转换 select job,name from emp where job = "MANAGER"; |
<> !=
-
选择不等于特定值的行
1234select name,sal from money where sal <> 2000;select name,sal from money where sal != 2000;select job,name from emp where job <> 'MANAGER';select job,name from emp where job != 'MANAGER';
<
|
1 |
select name,sal from money where sal < 2000; |
<=
|
1 |
select name,sal from money where sal <= 2000; |
>
|
1 |
select name,sal from money where sal > 2000; |
>=
|
1 |
select name,sal from money where sal >= 2000; |
逻辑运算符
and
-
所有条件都必须为真
12select name,sal from money where sal > 2000 and sal <3000;select name,sal from money where sal > 1900 and (number = 20 or number = 30);
or
-
至少一个条件为真
1select name,sal from money where sal = 2000 or sal = 3000;
not
|
1 2 3 |
select name,sal from money where sal not in (100,200); SELECT * FROM employees WHERE NOT salary = 5000; |
范围运算符
between...and
-
选择在一个范围内的值（包含边界）
1select name,sal from money where sal between 2000 and 3000;
in
- 选择在指定集合中的值
|
1 |
SELECT * FROM employees WHERE dept_id IN (1, 2, 3); |
空值运算符
is null
|
1 |
select name from money where comm is null; |
is not null
|
1 |
SELECT * FROM employees WHERE dept_id IS NOT NULL; |
模糊匹配运算符
like
|
1 2 3 4 5 6 7 8 |
//通配符% //占位符_ //name里面有s select name from money where name like "%s%"; //首字母是s的name select name from money where name like "s%"; //第二个字母是d的name select name from money where name like "_d%"; |
正则表达式
REGEXP
RLIKE
|
1 |
SELECT * FROM employees WHERE emp_name REGEXP '^A.*e$'; -- 匹配以A开头并以e结尾的名字 |
子查询运算符
- 子查询是嵌套在其他查询中的查询，可以用于更复杂的条件过滤
|
1 |
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); -- 薪水高于平均值的员工 |
EXISTS
- 用于检查子查询是否返回任何行
|
1 |
SELECT * FROM departments WHERE EXISTS (SELECT * FROM employees WHERE employees.dept_id = departments.dept_id); -- 有员工的部门 |
ALL
- 条件对子查询返回的所有值都必须为真
|
1 |
SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 1); -- 薪水高于部门1所有员工的薪水 |
ANY或SOME
- 条件对子查询返回的任何一个值为真即可
|
1 |
SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 1); -- 薪水高于部门1中至少一个员工的薪水 |
条件处理
CASE
- 允许在查询中进行条件处理
|
1 2 3 4 5 6 7 |
SELECT emp_name, CASE WHEN salary < 3000 THEN 'Low' WHEN salary BETWEEN 3000 AND 6000 THEN 'Medium' ELSE 'High' END AS salary_grade FROM employees; |
本文为原创文章，版权归Aet所有，欢迎分享本文，转载请保留出处！
你可能也喜欢
- ♥ 视图10/28
- ♥ 表操作_查询-结果去重10/24
- ♥ 表_唯一约束、非空约束10/26
- ♥ 索引相关10/28
- ♥ 事务_介绍 && 事务隔离11/01
- ♥ 数据库设计三范式简介10/28
热评文章
- 数据库设计三范式简介 0
- 连接查询-笛卡尔积 || 连接方式 0
- 视图 0
- 表操作_查询-分组 || 分组筛选 0
- 索引相关 0
- 表操作_查询-结果去重 0