--【注】混合函数的查询
select job,max(sal) from emp ; --错误
select job,max(sal) from emp group by job; --正确
使用GROUP BY来分组同一类对象,函数是选择这一类中的某个字段的函数结果。
--分组:
1、分组函数
avg、sum、max、min、count
2、avg、sum类型只能是number,max、min、count 不限制数据类型,count不统计空值(null)
select avg(sal) from emp;
select avg(ename) from emp;
select sum(sal) from emp;
select sum(ename) from emp;
select max(sal) from emp;
select min(ename) from emp;
select min(hiredate) from emp;
select count(comm) from emp;
select count(ename) from emp;
--***工作中,如果要统计一个表有多少数据,用*
select count(*) from emp;
【补充】
1、可以做运算(字段必须是number)
select sum(sal+nvl(comm,0)) from emp;
2、不能带表达式
select avg(sal>2500) from emp;--错误
select avg(sal) from emp where sal>2500;--正确
--**************************************工作中n多人犯错误
3、where中不能直接写分组函数,得展开写
select ename from emp where sal>avg(sal);
select ename from emp where sal>(select avg(sal) from emp);
完整的解释:where中可以做运算但不能带表达式且不能直接使用分组函数
--**********************************************
--必须会,必须理解,必须明白,必须知道为什么,否则你写sql会炸的
--混合:
新人会很糊涂什么时候对,什么时候不对,没有经验!!!
写和读的习惯:
1、当我写完select后,马上检查是否有组函数,如果有,想都不用想,马上 group by
分组规则为:除开组函数外的其他字段全部参与分组
看的习惯:
2、当我打开一个表后,读到表字段的内容时,如发现这个内容有重复数据,
那么针对这个字段进行分组就有意义(统计该数据中的行为数据)
select job,max(sal) from emp;--错误
select job,max(sal) from emp group by job;
select job,max(sal),ename from emp group by job;--错误
select job,max(sal),ename from emp group by job,ename;
select max(sal),ename from emp group by job,ename;
select max(sal) from emp group by job,ename;
select ename from emp group by job,ename;
select job,max(sal),ename,min(hiredate),count(*),avg(sal) from emp group by job,ename;
select job,sum(sal),count(*) from emp group by job;
select deptno,job,count(*)
from emp
group by deptno,job;
3、having
对分组后的数据进行过滤,就用having
select deptno,job,count(*)
from emp
group by deptno,job
having count(*)>1;
【补充】
1、having中不能使用别名
select deptno,job,count(*) total_person
from emp
group by deptno,job
having total_person>1;
2、having中只能使用select中的字段
select deptno,job,count(*)
from emp
group by deptno,job
having sal>2000;
3、having中可以使用组函数(因为隐藏了啊)
select deptno,job,count(*)
from emp
group by deptno,job
having max(sal)>2000;
select deptno,job,count(*)
from emp
group by deptno,job
having min(sal)>2000;
--***********************
总结:having不能使用别名,只能使用select中的字段,组函数除外
--求10,20部门的最大薪水
select deptno,max(sal)
from emp
group by deptno
having deptno in (10,20);
select deptno,max(sal)
from emp
where deptno in (10,20)
group by deptno;
---where和having的区别??面试特别喜欢问
where是先过滤再分组,可以单独使用,不能使用组函数
having是先分组后过滤,只能依附于group by,随便使用组函数
--子查询:
做题思路:菜式原则
1、粗略的读一下题目,找‘的’,他的右边就是我们要的字段,马上 select 出来
2、这些字段来自什么表呢?? 找出来, from 一下
3、如果是多表,马上写出他们的关系,这个关系一般是a.id=b.id
既2表中有相同关系的马上相等,注意(+)
4、再读一下题目,用拍大腿的精神想问题,自然翻译
--***************
注意:
新人很容易忘记 where a.id=b.id
1、列出至少有1个雇员的所有部门
select dname
from dept
where deptno in (select distinct deptno from emp );
2、列出薪金比"SMITH"多的所有雇员
select ename
from emp
where sal> (select sal from emp where ename='SMITH');
3、列出所有雇员的姓名及其直接上级的姓名
4、列出入职日期早于其直接上级的所有雇员
5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
6、列出所有“CLERK”(办事员)的姓名及其部门名称
7、列出各种工作类别的最低薪金,显示最低薪金大于1500的记录
8、列出从事“SALES”(销售)工作的雇员的姓名,假定不知道销售部的部门编号
9、列出薪金高于公司平均水平的所有雇员
10、列出与“SCOTT”从事相同工作的所有雇员
11、列出某些雇员的姓名和薪金,条件是他们的薪金等于部门30中任何一个雇员的薪金
12、列出某些雇员的姓名和薪金,条件是他们的薪金高于部门30中所有雇员的薪金
13、列出每个部门的信息以及该部门中雇员的数量
14、列出所有雇员的雇员名称、部门名称和薪金
15、列出各种类别工作的最低工资
16、列出各个部门的MANAGER(经理)的最低薪金
17、列出按年薪排序的所有雇员的年薪
18、列出按年薪排名的所有雇员的年薪
19、列出按年薪排序的第四名雇员的年薪
20、列出薪金水平处于第四位的雇员
21、列出按月薪排序的所有雇员的年薪取第三条记录和第五记录
各种连接,重点是左右连接
select * from emp,dept; --全连接
select * from emp,dept where emp.deptno=dept.deptno;--内连接
select * from emp inner join dept on emp.deptno=dept.deptno;--内连接
oracle采用(+)来表示左、右连接
(+)在左,表示右连接,以右边字段为参考
(+)在右,表示左连接,以左边字段为参考
--参考:它的所有数据全部都要得到展示
a b
id id
1 2
2 6
3 7
4 10
5 11
6
7
--***************************
select * from a,b; --35
select * from a,b where a.id=b.id;--3
select * from a,b where a.id=b.id(+);
id id
1
2 2
3
4
5
6 6
7 7
select * from a,b where a.id(+)=b.id;
id id
2 2
6 6
7 7
10
11
其他数据库不支持(+),他们采用 left join、right join 来表示左、右连接
select * from a,b where a.id=b.id(+);--oracle
--其他数据库
select * from a left join b on a.id=b.id;
select * from b right join a on a.id=b.id;
select * from a,b where a.id(+)=b.id;--oracle
--其他数据库
select * from a right join b on a.id=b.id;
select * from b left join a on a.id=b.id;