一 、单表查询select 例题:
题目:
1、查询出部门编号为2的所有员工
select * from staff where dep_id =2 ;
2、所有销售员的姓名、编号和部门编号。
select name,id,dep_id from staff where worktype='销售';
3、找出奖金高于工资的员工。
select * from staff where bonus > wage;
4、找出奖金高于工资10%的员工。
select * from staff where bonus > wage*1.1;
5、找出部门编号为02中的经理,和部门编号为01中所有销售员的详细资料。
select * from staff where (dep_id=2 and worktype='经理') or (dep_id=1 and worktype='销售');
6、找出部门编号为01中所有经理,部门编号为03中所有销售员,还有即不是经理又不是销售员但其工资大或等于2000的所有员工详细资料。
select * from staff where (dep_id=1 and worktype in ('经理','总经理','副经理')) or (dep_id=3 and worktype = '销售') or (worktype not in ('总经理','经理','销售') and wage > 2000);
7、有奖金的工种
select distinct worktype from staff where bonus is not null;
8、无奖金或奖金低于1000的员工。
select * from staff where (bonus is null) or (bonus<1000);
9、查询名字由三个字组成的员工。
select * from staff where name like "___";
select * from staff where char_length(name) =3;
10、查询2015年入职的员工。
select * from staff where hiredate like '2015%';
select * from staff where year(hiredate) = 2015;
select * from staff where date_format(hiredate,"%Y") = 2015;
11、查询所有员工详细信息,用编号升序排序
select * from staff order by id;
12、查询所有员工详细信息,用工资降序排序,如果工资相同使用入职日期升序排序
select * from staff order by wage desc,hiredate asc;
13、查询每个部门的平均工资
select dep_id,avg(wage) from staff group by dep_id;
15、求出每个部门的雇员数量
select dep_id 部门编号,count(name) as 部门人数 from staff group by dep_id;
16、查询每种工作的最高工资、最低工资、人数
select worktype,max(wage),min(wage),count(*) from staff group by worktype;
17、显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于10000,输出结果按月工资的合计升序排列
1.排除 销售
2.同一工作月工资的总和
3.月工资的总和>10000
4.排序
select worktype,sum(wage) as total from staff where worktype != '销售' group by worktype having total > 10000 order by total;