4.mysql 之 sql基础查询

数据准备：创建表

create table emploees
(
id int,
name varchar(20),
gender varchar(100),
saraly double(10,2),
department_id int,
manger_man  varchar(10)
)

数据准备 ：插入数据

insert into emploees values(101,'tom','m',5000,20,'zhangsan');
insert into emploees values(102,'tom1','m',6000,30,'zhangsan');
insert into emploees values(103,'tom2','m',7000,40,'zhangsan');
insert into emploees values(104,'tom3','m',8000,50,'zhangsan');
insert into emploees values(105,'tom4','m',9000,60,'zhangsan');
insert into emploees values(106,'tom5','m',10000,70,'zhangsan');
insert into emploees values(107,'kate1','f',5000,20,'lisi');
insert into emploees values(108,'kate2','f',6000,30,'lisi');
insert into emploees values(109,'kate3','f',7000,40,'lisi');
insert into emploees values(107,'kate4','f',8000,50,'lisi');
insert into emploees values(107,'kate5','f',9000,60,'lisi');
insert into emploees values(107,'kate6','f',9000,60,null);

#查询所有内容

select * from emploees

#查询特定列内容

select id,name,saraly from emploees

#去除id的重复内容

select distinct id from emploees

查询manger_man 值为null的内容

select * from emploees where manger_man is null

#查询saraly等于5000的内容

select * from emploees where saraly =5000

#查询saraly为5000到7000的内容（包含5000和7000）

select * from emploees where saraly  between 5000 and 7000

select * from emploees where saraly >=5000 and  saraly <=7000

#查询saraly不在5000到7000的内容

select * from emploees where saraly not between 5000 and 7000

select * from emploees where saraly < 5000 or  saraly >7000

#查询department_id为20 和30的内容

select * from emploees where department_id  in (20,30)

select * from emploees where department_id =20 or department_id =30

#查询department_id不为20 和30的内容

select * from emploees where department_id  not in (20,30)

查询name 包含k的内容

select * from emploees where name like'%k%'

查询name 包含k和t的内容

select * from emploees where name like'%k%t%'or name like '%t%k%'

查询name 以m结尾的内容（regexp 为匹配正则）

select * from emploees where name regexp 'm$'

查询name 第二个字母为o的内容（一个下划线代表一个任意字符）

select * from emploees where name like '_o%'