Oracle--聚合&分组&表连接

目录

1.  多行|聚合|组函数

2.  分组

3.  行转列

4.  rowid 与 rownum

5.  表连接

5.1  等值连接

5.2  非等值连接

5.3 自连接

5.4 内连接与外连接

5.5 左外连接与右外连接

6.  表设计

 6.1  三范式

 6.2  表与表之间的关系

---

1.  多行|聚合|组函数

即多条数据返回一个结果，常见的组函数：avg 、sum、 min、max、count

2.  分组

语法：select 数据 from 数据源 where 行过滤条件 group by 分组 having 组过滤条件 order by； 

执行流程：from -> where -> group by -> having -> select ->  order by

3.  行转列

数据（行记录）   分组（学生+行转列 decode）

4.  rowid 与 rownum

ROWID 是一个伪列，相对唯一行地址值，实现没有主键,唯一字段的表中完全相同数据的去重

ROWNUM:必须排列，不能直接取大于1的数；从1开始,每次+1,有规律,有顺序

如果存在排序之后,rownum序号被打乱,可以在select外部再次嵌套select语句,外部的select语句的rownum就是有规律的,可以作为判断使用的.

在oracle数据库中可以使用rownum实现分页查询

--如果想要对rownum进行判断,需要针对一条已经确定的rownum进行判断,解决方案,外部嵌套select
select empno,ename,sal,n,rownum num 
  from (select empno,ename,sal,rownum n from emp order by sal desc);

--分页 :  每页显示数据个数 n = 3   当前显示第几页 i    起始位置rownum : rownum> (i-1)*n;    
结束位置rownum : rownum<=i*n
select *
  from (select empno, ename, sal, rownum num
          from (select empno, ename, sal, rownum n from emp order by sal desc))
 where num <= 6
   and num > 3;

5.  表连接

表连接条件：过滤通过连表产生的不满足要求的表连接数据

5.1  等值连接

判断两个数据源中的某个字段值相等或者不相等

--查询20部门员工的信息以及员工所在部门信息
select empno,ename,sal,e.deptno,dname from emp e,dept d where e.deptno=d.deptno and e.deptno=20;

5.2  非等值连接

判断区间与范围的条件

--查询员工信息以及所在部门信息以及薪资等级信息
select *
  from emp e, dept d, salgrade s
 where e.deptno = d.deptno
   and e.sal between s.losal and s.hisal;

5.3 自连接

一张表当两个数据源使用

select * from emp e1,emp e2 where e1.mgr=e2.empno;

5.4 内连接与外连接

内连接 : 满足连接条件查询到不满足过滤掉
外连接 : 某一个数据源中的数据不满足连接条件的时候也能显示

5.5 左外连接与右外连接

左外连接 | 左连接 : from后面主表在左边
右外连接 | 右连接 : from后面主表在右边
主表 : 主表中的数据无论是否满足连接条件都能显示
92语法 : 在连接条件位置,主表的对面添加(+)

--查询所有员工信息以及上级经理人信息
--主表 : 员工表
select * from emp e1,emp e2 where e1.mgr=e2.empno(+);
select * from emp e1,emp e2 where e1.mgr(+)=e2.empno;

6.  表设计

字段类型  ：number char varchar2  date

字段约束  ：主键约束(唯一+非空)、唯一约束、非空约束、默认约束、检查约束、外键约束  Emp Dept、
--涉及到两张表：父表|主表 
                            子表|从表 : 子表中添加一个外键字段,关联主表的主键字段
 --外键字段的值只能为主表中主键字段已有的值

 6.1  三范式

最终的目的避免数据重复冗余
1NF-->列不可再分最小原子 （避免重复);
2NF-->主键依赖(确定唯一)；
3NF-->消除传递依赖(建立主外键关联 拆分表);
 

 6.2  表与表之间的关系

一对一：主外键关联关系

一对多：主外键关联关系 : 在多的一方设置外键,关联一的一方的主键

多对多：设置中间表 : 定义两张表的关联关系