mysql单表多条件查询_mysql之单表条件查询

create table staff_info(

id int primary key auto_increment,

name varchar(32) not null,

age int(3) unsigned not null,

sex enum('male','female') not null default 'male',

duty varchar(50) not null,

salary decimal(15,2) not null,

remark varchar(100)

) charset=utf8;

insert into staff_info(name,age,sex,duty,salary,remark) values

('张三',18,'male','programmer',12000.27,'我高新，我骄傲！'),

('zhang',88,'male','charger',80000084,'缺钱，冲我来！'),

('wang',24,'male','teacher',19999.990,'我可不是一朵娇花，不用怜惜我！'),

('刘二丫',36,'female','cooker',6000.87,null),

('李四五',47,'male','teacher',15550,null),

('echo',18,'male','teacher',20000,'走到哪里，我都要问别人：睡我不？'),

('翠花',17,'female','seller',7500.27,null),

('朵朵',23,'female','形象代言',12300.556,null);

1.查看岗位是teacher的员工姓名、年龄;

select name,age from staff_info where duty='teacher';

2.查看岗位是teacher且年龄大于30岁的员工姓名、年龄;

select name,age from staff_info where age>30 and duty='teacher';

3.查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资

select name,age,salary from staff_info where duty='teacher' and salary between 9000 and 10000;

4.查看岗位描述不为NULL的员工信息

select * from staff_info where remark !='null';

5.查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资

select name,age,salary from staff_info where duty='teacher' and salary in(10000,9000,30000);

6.查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资

select name,age,salary from staff_info where duty='teacher' and salary not in(10000,9000,30000);

7.查看岗位是teacher且名字是e开头的员工姓名、年薪

select name,salary from staff_info where name like 'e%';