子查询必须安照下面的顺序编写SQL语句:
from:从哪些表中筛选on:关联多表查询时,去除笛卡尔积where:从表中筛选的条件group by:分组依据having:在统计结果中再次筛选order by:排序limit:分页示例:
#查询每个部门的男生的人数,并且显示人数超过5人的,按照人数降序排列,
#每页只能显示10条,我要第2页
SELECT deptno,COUNT(*) "人数"
FROM tb_emp
WHERE gender = '男'
GROUP BY deptno
HAVING COUNT(*)>5
ORDER BY 人数 DESC
LIMIT 10,10
7.2 group by与分组函数
可以使用GROUP BY子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression];
注意:WHERE一定放在FROM后面GROUP BY 前面
在SELECT列表中所有未包含在分组函数中的列都应该包含在 GROUP BY子句中
SELECT deptno, AVG(salary)
FROM tb_emp
GROUP BY deptno ;
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
SELECT AVG(salary)
FROM tb_emp
GROUP BY deptno ;
使用多个列分组
SELECT deptno dept_id, job, SUM(salary)
FROM tb_emp
GROUP BY deptno, job ;
7.3 having与分组函数
统计各个部门的平均工资(只显示平均工资2000以上的)
select avg(salary) from tb_emp -- 1 -- 不是各个部门的,是所有部门的
select deptno from tb_emp -- 14
-- 字段不能和分组函数共存,除非该字段是分组group by字段
select deptno,avg(salary) from tb_emp
select deptno, avg(salary) --
from tb_emp
group by deptno;
获取最小工资小于2000的职位
-- min(sal) job min(sal)<2000
-- 获取各个职位的最小工资
select job,min(sal)
from tb_emp
group by job
order by min(sal)
-- 获取各个职位的最小工资,筛选出小于2000的
select job,min(sal)
from tb_emp
group by job
having min(sal)<2000
order by min(sal)
-- 统计[人数小于4的]部门的平均工资。
select deptno,count(1),avg(sal)
from tb_emp
group by deptno
having count(1)<4
-- 统计各部门的最高工资,排除最高工资小于3000的部门。
select deptno,max(sal)
from tb_emp
group by deptno
having max(sal) >=3000
having与where的区别:
where是从表中筛选的条件,而having是分组(统计)结果中再次筛选where后面不能加“分组/聚合函数”,而having后面可以跟分组函数#统计部门平均工资高于8000的部门和平均工资
SELECT deptno, AVG(salary)
FROM tb_emp
WHERE AVG(salary) > 8000 #错误
GROUP BY deptno;
#统计每一个部门,薪资高于10000元的女员工的数量,显示人数超过1人
SELECT deptno,COUNT(*)
FROM tb_emp
WHERE gender ='女' AND salary>10000
GROUP BY deptno
HAVING COUNT(*) > 1;
7.4 order by
order by:
降序:desc
升序:要么默认,要么加asc
7.5 limitlimit:
limit m,n
其中:
示例:
/*
每页显示2条 展示第三页
*/
select * from tb_emp limit 4,2;
求员工的姓名,薪水,部门编号,部门名称,工作编号,工作名称,按照薪水排序,每页显示3条显示第4页
SELECT
emp.`ename`,
emp.`salary`,
emp.`deptno`,
dept.`dname`,
dept.`deptno`,
job.`job_name`
FROM
tb_emp emp
JOIN tb_dept dept ON emp.`deptno` = dept.`deptno`
JOIN t_job job ON emp.`job` = job.`job`
ORDER BY
emp.`salary`
LIMIT 9,
3;
7.6 where型子查询
①子查询是单值结果,那么可以对其使用(=,>等比较运算符)
②子查询是多值结果,那么可对其使用(【not】in(子查询结果),或 >all(子查询结果),或>=all(子查询结果),any(子查询结果),或>=any(子查询结果),
查询全公司最高工资的员工信息
select * from 员工表 where 薪资 = (select max(薪资) from 员工表);
select * from 员工表 where 薪资 > all(select salary from 员工表 where 员工编号 in(...));
7.7 from型子查询
子查询的结果是多行多列的结果,类似于一张表格。
必须给子查询取别名,即临时表名,表的别名不要加“”和空格。
查询每个部门的编号,名称,平均工资
select 部门编号, 部门名称, 平均工资
from 部门表 inner join (select 部门编号,avg(薪资) from 员工表 group by 部门编号) temp
on 部门表.部门编号 = temp.部门编号
-- 查询每个部门的编号,名称,平均工资
7.8 exists型子查询
查询那些有员工的部门
select 部门编号, 部门名称 from 部门表
where exists (select * from 员工表 where 部门表.部门编号 = 员工表.部门编号);
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)