基本认知
IN
IN 可以过滤出某个字段的多个值
例如,想过滤出年龄在19-20岁的,使用IN就显得很方便了。
LIKE
LIKE ,查询出含未知条件的数据,即模糊查询,常与%和_连用
例如,想过滤出通讯录中所有姓刘的用户,就使用刘%进行模糊匹配
|==================================================================
实践出真知
1.首先创建一个员工表
create table employee(
id int not null auto_increment primary key,
name varchar(30) comment '姓名',
sex varchar(1) comment '性别',
salary int comment '薪资(元)'
);
insert into employee(name, sex, salary) values('张三', '男', 5500);
insert into employee(name, sex, salary) values('李洁', '女', 4500);
insert into employee(name, sex, salary) values('李小梅', '女', 4200);
insert into employee(name, sex, salary) values('欧阳辉', '男', 7500);
insert into employee(name, sex, salary) values('李芳', '女', 8500);
insert into employee(name, sex, salary) values('张江', '男', 6800);
insert into employee(name, sex, salary) values('李四', '男', 12000);
insert into employee(name, sex, salary) values('王五', '男', 3500);
insert into employee(name, sex, salary) values('马小龙', '男', 6000);
insert into employee(name, sex, salary) values('龙五', '男', 8000);
insert into employee(name, sex, salary) values('冯小芳', '女', 10000);
insert into employee(name, sex, salary) values('马小花', '女', 4000);
没有in之前,我们想查询出id=1,2,3的员工,
只能通过下面这种:
select * from employee where id=1 or id=2 or id=3;
有了IN之后,我们用这种,是不是很方便了呢?
select * from employee where id in (1,2,3);
得到的结果是相同的
result:
|---------------------------------------------------------------------------------------------------------------------
2.筛选出所有姓李的员工
select * from employee where name like '李%';
result:
|---------------------------------------------------------------------------------------------------------------------
3.筛选出所有姓李的员工,名字共两个字符。
select * from employee where name like '李_';
result:
|---------------------------------------------------------------------------------------------------------------------
4.筛选出所有芳结尾的员工
select * from employee where name like '%芳';
result:
|---------------------------------------------------------------------------------------------------------------------
5.筛选出所有名字中间是小的员工
select * from employee where name like '%小%';
result: