1.分组
group by 分组字段 having 过滤字段
分组函数可以过滤组信息,having后面可以使用组函数。在使用group by时,select后面可以使用多行函数也可以使用字段,不过字段必须与分组字段相同,且having后面的字段也如此。在任何时候where后面都不可以使用分组函数。
--分组函数,先分组后过滤,select ,having后的字段只能是group by后已有的
select avg(sal) avg_sal,job,deptno from emp group by deptno,job having deptno=10;
--查询 最低平均工资的部门编号
select min(avg_sal) from (select avg(sal) avg_sal from emp group by deptno);
--查询10,30部门的平均工资与薪资总和
select avg(sal),sum(sal) from emp group by deptno having deptno in(10,30);
--查询10,20部门中最高平均工资的部门名称
select max(avg(sal)) from emp group by deptno and deptno;
select deptno from emp group by deptno having avg(sal) in(select max(avg(sal)) from emp group by deptno) and deptno in(10,20);
2.行转列
当我们将一个表的行转列时,需要用到decode函数;
--创建表
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
--============
select * from tb_student;
--===================
--插入字段内容
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
--====================
--查询所有课程成绩都大于80学成的姓名
--查询一共有几门课程
select count(distinct course) from tb_student;
--查询每门课都大于80学生
select name
from tb_student
group by name
having count(course) = (select count(distinct course) from tb_student) and min(score) > 80;
--====================
--行转列
select * from tb_student;
--查询每个学生的每门课成绩
select name,
decode(course, '语文', score) 语文,
decode(course, '数学', score) 数学,
decode(course, '英语', score) 英语
from tb_student;
--按照姓名进行分组==使用了group by,但是decode是单行函数,所以要将单行函数转为组函数
select name,
max(decode(course, '语文', score)) 语文,
max(decode(course, '数学', score)) 数学,
max(decode(course, '英语', score)) 英语
from tb_student
group by name;
3.表连接
当需要查询的数据来自于多张表中时,需要使用表连接。
- 92语法 没有连接条件|行过滤条件
92语法中,多表连接时每个表之间用,号连接,其格式为:
select 数据 from 数据源1,数据源2,…;
在没有添加任何连接条件或行过滤条件时,数据源中的数据分别连接为对乘的效果,即笛卡尔积。
--等值连接 笛卡尔积
select * from emp e,dept d;
- 有连接条件|行过滤条件
select 数据 from 数据源1,数据源2,…where 连接条件|行过滤条件;
注:一条sql中出现多个数据源,建议提供表的别名,且指定查询结果集中的字段名,如果出现同名需指明出处。
--笛卡尔积不满足条件,需要过滤 等值连接
select * from emp e,dept d where e.deptno=d.deptno;
--非等值连接
select t.ename from ( select * from emp e,dept d where e.deptno=d.deptno) t,emp e where e.MGR=t.empno;
当有些数据不满足连接条件 ,但是还是想显示,可以选择使用外连接。在连接条件中主表的对面(+),主表在左为左连接,主表在右表示右连接。
--当有些数据不满足连接条件但是仍然需要显示,需要使用外连接(左连接|由连接)==连接条件中主表的对面增加(+)
select * from emp e1,emp e2 where e1.mgr=e2.empno(+);