条件查询
语法:
select
查询列表
from
来源
where
筛选条件
分类:
- 按条件表达式筛选
条件运算符:> ,<, =, !=(<>), >=, <= - 按逻辑表达式筛选
逻辑运算符:&&,||,!
and,or,not - 模糊查询:like, between and, in, is null
1. 按条件表达式筛选
案例1:查询工资大于等于12000的员工信息
select
*
from
employees
where
salary >= 12000;
案例2:查询部门编号不等于90号的员工名和部门编号
select
last_name,
department_id
from
employees
where
department_id <> 90;
2. 按逻辑表达式查询
案例1:查询员工工资在10000到12000之间的员工名,工资和奖金
select
last_name,
salary,
commission_pct
from
employees
where
salary >= 10000 and salary <= 12000;
案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
select
*
from
employees
where
not(department_id >= 90 and department_id <= 110) or salary > 15000;
3. 模糊查询:like,between and,in,is null | is not null
1. like
- 特点:一般和通配符搭配
通配符:
%:任意多个字符,包含0个字符
_:任意单个字符
案例1: 查询员工名包含字符a的员工信息
select
*
from
employees
where
last_name like '%a%';
案例2: 查询员工名中第三个字符为n,第5个为l的员工名和工资
select
last_name,
salary
from
employees
where
last_name like '__n_l%';
案例3:查询员工中第二个字符为下划线的员工名
- 使用转义字符来转义通配符:
select
last_name
from
employees
where
last_name like '_\_%';
- 也可以用任意字符来转义通配符:
select
last_name
from
employees
where
last_name like '_$_%' escape '$';
2. between and
注意事项:
- 包含between and 可以提高语句的简洁度
- 包含临界值
- 两个临界值不能调换顺序
案例1:查询员工编号在100~120之间的员工信息
select
*
from
employees
where
employee_id between 100 and 120;
3. in
含义:判断某字段的值是否属于in列表中的某一项
特点:
1. 使用in提高语句简洁度
2. in列表的值类型必须一直或兼容
案例1:查询员工的工种编号是 IT_PROG, AD_VP, AD_PRES的员工名和工编号
select
last_name,
job_id,
from
employees
where
job_id = 'IT_PROG' OR job_id = 'AD_VR' OR job_id = 'AD_PRES';
用in查询:
select
last_name,
job_id
from
employees
where
job_id IN('IT_PROG', 'AD_VR', 'AD_PRES');
4. is null
注意事项:
1. = 或 <>不能判断 null值
2. is null 或 is not null可以判断null值
案例1:查询有或没有奖金的员工名和奖金率
select
last_name,
commission_pct,
from
employees
where
commission_pct is null;
# commission_pct is not null;
安全等于 <=>:即可一判断null值也可以判断普通的值
案例1:查询有奖金的员工名和奖金率
select
last_name
commission_pct
from
employees
where
commission_pct <=> null;