模糊查询
1. like/not like
功能:一般和通配符搭配使用,对字符型数据进行部分匹配查询。
常见的通配符:
_ 任意单个字符
% 任意多个字符(0-多个)
案例一:查询姓名中包含有字符a的员工信息
select * from employees where last_name like ‘%a%’;
查询姓名中包含有最后一个字符为e的员工信息
select * from employees where last_name like ‘%e’;
查询姓名中包含有第一个字符为e的员工信息
select * from employees where last_name like ‘e%’;
查询姓名中包含有第三个字符为x的员工信息
select * from employees where last_name like ‘__x%’;
查询姓名中包含有第二个字符为_的员工信息
一:select * from employees where last_name like ‘_%’; 注解:\ 转义符
二:select * from employees where last_name like 'KaTeX parse error: Expected group after '_' at position 1: _̲% escape '’; 将$设为转移符。
2. in/not in
功能:查询某字段的值是否属于指定的列表之内
a in(常量值1,常量值2……);
a not in(常量值1,常量值2……);
案例一:查询部门编号是30/50/90的员工名,部门编号
方式一:select last_name,department_id fron employees where department_id in(30,50,90);
方式二:select last_name,department_id fron employees where department_id =30 or department_id =50 or department_id =90;
案例二:查询工种编号不是sh_clerk或it_prog的员工信息
方式一:select * from employees where job_id not in (‘sh_clerk’,‘it_prog’);
方式二:select * from employees where not ( job_id=‘sh_clerk’ or job_id=‘it_prog’);
3.between and
功能:判断某个字段的值是否介于XX之间
between and/ not between and
案例一:查询部门编号在50-90之间的部门编号,员工姓名。
select department_id,last_name from employees where department_id between 50 and 90;
4.is null/is not null
案例一:查询没有奖金的员工信息
select * from commission_pct is null;
案例二:查询有奖金的员工信息
select * from commission_pct is not null;
补充:= is <=>
= 只能判断普通的内容
is 只能判断null值
<=> 安全等于 既可以判断普通的内容又可以判断null值(阅读性查差)