oracle知识点学习收藏

SQL Select语句完整的执行顺序：

1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、select；
7、使用order by对结果集进行排序
--------------------- 
 

1.oracle中rownum伪列和rowid的用法和区别

 ROWNUM是动态的，也就是必须先有查询到的结果集，然后再给这个结果集加上一个列.而且rownum的记录是从一开始的,所以一般的oracle分页是这样的: 

--分页查询一
select a1.* from (select student.*,rownum rn from student where rownum <=5) a1 where rn >=3;

--分页查询二
select a1.* from (select student.*,rownum rn from student) a1 where rn between 3 and 5;

同ROWNUM伪列不同的是，它是物理存在的,同ROWNUM伪列不同的是，它是物理存在的，ROWID是一种数据类型，它使用基于64为编码的18个字符来唯一标识一条记录物理位置的一个ID，类似于Java中一个对象的哈希码，都是为了唯一标识对应对象的物理位置.

dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情。dual表只返回一条记录.

 

2.分组函数(聚合函数)--返回的是一个值

AVG , SUM, MIN, MAX, COUNT, WM_CONTACT(行转列)

select count(distinct deptno) from emp;

行转列

** 分组函数与空值问题

注意: 分组函数会自动忽略空值(空值不考虑在内)

     --   使用NVL函数(滤空处理)

     nvl(cnm,o)  如果cnm列值为空,则为0

  

3. 分组

1.group by 子句

-- select a,b ,c , 组函数(X) from table group by a ,b ,c

 在oracle中,没有包含在组函数中的列必须包含在group列中, a,b,c的顺序进行分组,

2.过滤分组(having 子句)

having子句用于分组后 过滤分组后 的 结果

having和where的区别

where后面不能接分组函数

求部门评价工资:

 select * from (
   select avg(sal) sal, deptno from emp group by deptno
 ) where sal > 2000;

<==> select avg(sal) sal, deptno from emp group by deptno having avg(sal) > 2000;

** 注意,sql优化来说,尽量避免使用 having,使用where效率更高 ( 跟sql语句执行顺序有关 )

   原因: having 先分组,再过滤   where 先过滤筛选再分组

 语法: group by 语句的增强

 group by rollup(a,b) <==> group by a,b + group by a + group by null

 

4.多表查询

   1.笛卡尔全集

     表一: 2列2行

     表二: 4列3行

     表的列数相加 * 行数相乘 = (2+4)*(2*3)     -- 新的表 是6列6行

 

   2.为了避免使用笛卡尔全集, 使用where作连接条件

      &等值连接   from a ,b  where a.deptNo= b.deptNo

      &非等值连接

      &  外连接(左外连接,右外连接)

         fom a,b where deptNo(+) = b.deptNo  --左外连接

     外连接的作用: 将对于连接条件不成立的记录,任然包含在最后的记过中!!!

      & 自连接: 通过表的别名,将同一张表视为多张表 ,本质上也是多表查询

        select a.name 员工姓名, b.name  老板姓名 from emp a ,emp b where a.mgid = b.id 

         *注意: 自连接存在的问题

           1.自连接不适合操作大表(笛卡尔积太大) ,解决方法是层次查询

           

       & 层次查询,可以取代自连接,本质上是一个单表查询

          树的深度: level    1  2   3  4

      自连接结果: 查询结果比较直观,不适合大表操作

      层次查询 : 层次表明显 , 查询结果不是很直观

      select level ,empno,ename,sal,mgr from emp connect by  prior empno = mgr  start with mgr is null  order by 1    

 

5.子查询(select语句的嵌套)

   作用: 问题不能一步求解,就需要采用子查询

    子查询需要注意的问题: 

     1. 子查询的书写风格

     2. 子查询的位置  select, where , from  , where

     3. 位置

      写在select后面,则为单行子查询

      * 子查询 vs 多表查询

      * 子查询好还是多表查询好

         子查询会访问数据库多次,多表查询会产生笛卡尔积,根据实际情况而定

      * rownum 和orderby 问题  解决TOP-N问题

      1. 行号只能按照默认的顺序生成,跟order by 无关

           select rownum, empno,ename

          from (select * from emp order by salary

         ) where rownum < =3;

     

    4.相关子查询

        一般先执行子查询,再执行主查询,相关子查询除外,相关子查询需要使用主查询传递过来的参数

    5.单行子查询和多行子查询

        单行: <  >  =

        多行:  in    any   all

     6. 多行子查询的null 值问题

         not in (子查询结果) ,要注意查询的结果不能含有空值

    ================================================

    sql语句的执行计划: 

     explain plan for  sql语句

     select* from table(dbms_xplan_display)

 

========================================

统计1981年出生的员工人数:

-- select sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)  '1981'' from emp