mysql查询5000到8000_mysql数据查询

新建一张表 包含列为 employee_id  name gender dept_id(1,2,3,4) join_time salary phone address description

工号 姓名 性别 部门 参加工作时间 薪水 电话号 地址 描述

create table infor(employee_id int,name char(15),gender enum('M','F'),dept_id enum('1','2','3','4'),join_time date,salary float,phone char(11),address text,description text);

添加数据

insert into infor values (1,'liuyongcun','M','1','2012-12-23',45000,13800138001,"天堂1号","CEO");

insert into infor values (3,'liziteng','M','2','2012-12-25',15000,13838438002,"天堂2号","xiaomi");

insert into infor values (4,'zouying','M','3','2012-12-25',25000,13838438003,"天堂3号","fuzong");

insert into infor values (6,'niechangchun','F','4','2012-12-28',25000,13838438004,"天堂4号","fuzong");

insert into infor values (5,'liyansen','M','2','2012-12-26',20000,13838438005,"天堂5号","jingli");

insert into infor values (2,'zhangmingyang','M','4','2012-12-24',8000,13838438006,"天堂6号","zhiyuan");

insert into infor values (7,'xiaoyueyue','F','4','2012-12-28',8000,13838384386,"天堂7号",null);

按名字查找

select * from infor where name="liuyongcun";

按薪水查找，薪水大于16000

select name from infor where salary>=6000;

每个部门有多少人 提示用having

select count(*) from infor group by dept_id ;

每个部门的平均薪资

select avg(salary) from infor group by dept_id;

查找描述字段为空的

select * from infor where description is NULL;

求出每个部门女员工(F)薪水最高的

(1)select name,salary from infor inner join (select dept_id,MAX(salary) as gongzi from infor where gender='F' group by dept_id ) t

on t.dept_id=infor.dept_id

and t.gongzi=infor.salary

(2)select * from (select ROW_NUMBER() over (partition by dept_id order by salary desc) as row,* from infor where gender='F') t where row=1 名字是以L开头的用户 (模糊查询) select * from infor where name like 'l%';