Oracle分组查询、多表查询及rowid和rownum

Oracle数据库

分组：group by

语法

1. 使用格式：select 数据 from 数据源 where 行过滤条件 group by 分组 having 组过滤信息 order by 排序字段
2. 执行流程 : from -> where -> group by -> having -> select -> order by
3. 注意事项：
   - select后面一旦出现组函数,不能使用非分组字段
   - 一旦分组,只能使用分组字段或者组函数

字段别名的使用注意

根据上述的执行流程，后面的流程可以使用前面流程定义的别名，反之不可

单行函数在分组中的使用

• 单行函数不可以直接在分组的select后使用
• 如需使用需在外层嵌套组函数

多表查询

92年语法

交叉连接（笛卡尔积）

• 交叉连接的结果为表一的每一元组依次连接表二的各个元组，结果集为表一元组个数*表二元组个数
• 语法为：

  select * from emp,dept where emp.deptno = dept.deptno;
  

• 如果要使用的字段来自于多个数据源中都存在,需要指明限定词|出处

外连接（左连接、右连接）&等值连接

• 左连接（右连接）的结果为左表的每一元组根据连接条件依次连接右表的各个元组，未连接成功的左表（右表）给与输出并将右表（左表）部分属性置为null。未连接成功的右表（左表）不予输出。左（右）连接的结果集个数为左（右）表元组个数。
• 语法为：

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

• 使用左连接（右连接）必须指定连接条件，左连接（右连接）则在右表（左表）的属性后加（+）。
• 不在等值连接条件后加如（+）符号的连接为等值连接

非等值连接

• 表的连接条件不是等值连接条件时的连接为非等值连接
• 若连接条件的属性名为不同表的同名属性则需指定表名，若不是则没有必要
• 语法为：

  select * from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal;
  

自连接

• 自连接是将相同的两个表当作不同的表来源做连接的一种方式。
• 自连接可以使用以上的各种连接方式。
• 自连接必须对至少一个表起别名。

99年语法

交叉连接

• 结果集和注意事项同上92
• 语法为：

  select * from emp cross join dept;
  

自然连接

• 数据库底层自动对表的相同字段名进行等值筛选的连接
• 自然连接后会合并相同字段名的列
• 在通过等值筛选的前提下自然左（右）连接可以出现左（右）连接的效果
• 联合查询的表中必须存储有相同字段名，若无相同字段名则进行的是交叉连接

等值连接

• 语法为：

  select * from emp left join dept using(deptno); 
  

• 使用左连接（右连接）必须指定连接条件，使用using作为连接条件时，using后的属性必须为双表共有属性。

非等值连接

• 概念及注意事项同上92
• 语法为：

  select * from emp join salgrade s on emp.sal between s.losal and s.hisal;
  

全连接

• 全连接必须设置连接条件
• 相当于即左连接又右连接
• 语法为：

  select * from emp full join emp e2 on emp.mgr = e2.empno;
  

连表查询的执行流程

• 数据库在from阶段进行表的连接，其余流程不变

rowid和rownum

• rowid和rownum都是虚列，每个表都存在
• rowid记录的是元组的物理地址，在元组插入表的时候存在，一旦存在不可改变
• rownum表示某条记录在整个结果集中的位置，在查询输出的时候存在，每次查询都可能变化
• 在对结果集使用order by排序的时候，rownum的顺序有可能是乱的，此时可以在外层嵌套查询实现rownum的有序
• 在rownum有序的时候，可以通过对rownum筛选来实现限制输出某几个连续元组的效果