单表:
HAVING过滤
二次筛选 只能是group by 之后的字段
1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(1) from employee group by post having count(1)<2;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) as a from employee group by post having a >10000;(怕avg为关键字,用as a 代替)
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) as a from employee group by post having a>10000 and a<20000;
order by 查询排序
排序 order by age desc / asc
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from employee order by age asc,hire_date desc;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) asc; (order by 后面一定要有参数)limit 限制查询的记录数:
select * from employee order by salary desc limit 5,5; (默认为0
从排好序的第五条开始,找5个)
多表查询
- 多表连接查询
select * from employee,department where employee.dep_id = department.id
外链接操作:
1.语法
inner /left/right join (显示全部 有nul不显示,优先左边,没有关联的表为null/右边优先)
select * from employe inner join department on employee.dep_id = department.id
全外链接 union
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
符合条件查询
以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，
即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age >25;
(on 后面跟着where 条件)
1、查询平均年龄在25岁以上的部门名
select name from department where id in( select dep_id from employee group by dep_id having avg(age) >25) ;
通过在emp 表中找到的dep_id 对应上 dep 表中的id 而找到name
2、查看不足1人的部门名
select name from department where id not in(select dep_id from employee group by dep_id) ;
(利用共同的dep_id来判断)
3、查询大于所有人平均年龄的员工名与年龄
select name,age from employee where age> (select avg(age) from employee);
(这里找的是员工名和年龄 跟部门没有关系,,,)
4、查询大于部门内平均年龄的员工名、年龄
1）先对员工表(employee)中的人员分组(group by)，查询出dep_id以及平均年龄。
(2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。
(3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。
select * from employee inner join (select dep_id,avg(age) as b from employee group by dep_id) as A on employee.dep_id = A.dep_id where employee.age>A.b;
5.查询每个部门最新入职的那位员工
select name from employee inner join( select post,max(hire_date) as newtime from employee group by post) as A on employee .post = A.post where employee.hire_date=A.newtime;
同上,都是关于进行 on dep_id 的判断 ,第二个则是关于where 的判断
pymysql模块的使用
