Oracle学习之常用对象（四）—数据处理

之前，谈过了常用对象的基本使用。我把常用对象当作处理数据的工具，比如：表可以看作是盛酒的容器，一瓶酒不光是容器，还有酒水。酒水就相当于我们处理的数据逻辑。
这些数据逻辑就需要我们下面用到的基本语法来实现。
一、DML
我们需要向表中插入数据：
  INSERT INTO TEST_A VALUES ('a', 'b', 'c', 100);
或者
  INSERT INTO TEST_A
    SELECT A, B, C, D FROM TEST_B;
有时，我们会对表中数据进行更新：
  UPDATE TEST_A SET A = 'a1' WHERE A = 'a';
甚至有时候我们需要删除表中的数据：
  DELETE FROM TEST_A WHERE A = 'a';
  TRUNCATE TABLE TEST_A;--清除表中全部数据
二、查询
1、分组查询
1>简单分组
  SELECT A, B, C, AVG(D), MAX(D)
    FROM TEST_A
   GROUP BY A, B, C
   ORDER BY AVG(D);
2>ROLLUP用于生成横向统计信息
  SELECT A, B, C, AVG(D), MAX(D) FROM TEST_A GROUP BY ROLLUP(A, B, C);
3>CUBE用于生产纵向统计信息
  SELECT A, B, C, AVG(D), MAX(D) FROM TEST_A GROUP BY CUBE(A, B, C);
4>HAVING子句
  SELECT A, AVG(D), MAX(D) FROM TEST_A GROUP BY A HAVING AVG(D) > 2000;
2、连接查询  
1>不等连接
  SELECT A.A, A.B, B.A
    FROM TEST_A A, TEST_B B
   WHERE A.D BETWEEN B.D1 AND B.D2
     AND A.A = 'a';
2>自连接
  SELECT A.A, A.B, B.A FROM TEST_A A, TEST_B B WHERE A.A = B.A;
3>外连接
  SELECT A.A, A.B, B.A FROM TEST_A A LEFT JOIN TEST_B B WHERE A.A = B.A;
  SELECT A.A, A.B, B.A FROM TEST_A A RIGHT JOIN TEST_B B WHERE A.A = B.A;
  SELECT A.A, A.B, B.A FROM TEST_A A FULL JOIN TEST_B B WHERE A.A = B.A;
3、合并查询
1>UNION [注：两个集合的并集，去掉重复行，按第一列结构排序]
  SELECT A, B, C
    FROM TEST_A
   WHERE D = 30
  UNION
  SELECT A, B, C
    FROM TEST_A
   WHERE B = 'manager';
2>UNION ALL [注：两个集合并集，不去重复行，不排序]
  SELECT A, B, C
    FROM TEST_A
   WHERE D = 30
  UNION ALL
  SELECT A, B, C
    FROM TEST_A
   WHERE B = 'manager';
3>INTERSECT [注：只会显示同时存在两个集合中的数据]
  SELECT A, B, C
    FROM TEST_A
   WHERE D = 30
  INTERSECT
  SELECT A, B, C
    FROM TEST_A
   WHERE B = 'manager';
4>MINUS [注：在一个集合存在，在第二个集合不存在的数据，按第一个排序]
  SELECT A, B, C
    FROM TEST_A
   WHERE D = 30
  MINUS
  SELECT A, B, C
    FROM TEST_A
   WHERE B = 'manager';
5>[注：合并查询中，只能有一个 ORDER BY 子句。在这个子句中使用列名或第一个查询的别名。]
  SELECT A, B, C
    FROM TEST_A
   WHERE D = 30
  INTERSECT
  SELECT A, B, C
    FROM TEST_A
   WHERE B = 'manager'
   ORDER BY C;
4、子查询
1>单行子查询
  SELECT A, B, C FROM TEST_A WHERE D = (SELECT MAX(D) FROM TEST_A);
2>多行子查询
[注：WHERE 子句中使用多行子查询，必须使用多行运算符，IN,NOT IN,EXISTS,NOT EXISTS,ALL,ANY]
  SELECT A, B, C, D
    FROM TEST_A
   WHERE C IN (SELECT DISTINCT C FROM TEST_A WHERE D = 20);

  SELECT A, B, C
    FROM TEST_A
   WHERE D > ALL (SELECT D FROM TEST_A WHERE C = 'c');

  SELECT A, B, C
    FROM TEST_A
   WHERE D > ANY (SELECT D FROM TEST_A WHERE C = 'c');
3>相关子查询
  SELECT A, (SELECT MAX(D) FROM TEST_A B WHERE B.A = A.A) AS MAXD
    FROM TEST_A A
   ORDER BY A;

  SELECT A, B, C, D
    FROM TEST_A A
   WHERE EXISTS (SELECT 1
            FROM TEST_B B
           WHERE A.A = B.A
             AND B.C = 'c');
4>多列子查询
  SELECT A, B, C, D
    FROM TEST_A
   WHERE (A, B) = (SELECT A, B FROM TEST_A WHERE C = 'c');

三、基本的Sql编写注意事项
1、尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。
2、不用 NOT IN 操作符，可以用NOT EXISTS或者外连接+替代。
3、Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。
   而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。
   NOT EXISTS 比 NOT IN 效率稍高。但具体在选择IN或EXIST操作时，
   要根据主子表数据量大小来具体考虑。
4、不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，
   可以用“<” or “>”代替。
5、WHERE 子句中出现 IS NULL 或者 IS NOT NULL 时，Oracle会停止使用索引而执行全表扫描。
   可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。
6、当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。
7、对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，
   可以分开连接或者使用不作用在列上的函数替代。
8、如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。
9、WHERE 子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。
10、对数据类型不同的列进行比较时，会使索引失效。
11、用“>=”替代“>”。
12、UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。
    如果不需要删除重复记录，应该使用UNION ALL。
13、Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，
    可以过滤掉最大数量记录的条件必须写在Where子句的末尾。
14、Oracle从右到左处理 From 子句中的表名，所以在From子句中包含多个表的情况下，
    将记录最少的表放在最后。
15、Order BY 语句中的非索引列会降低性能，可以通过添加索引的方式处理。
    严格控制在 ORDER BY 语句中使用表达式。
16、不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，
    防止相同的Sql语句被多次分析。
17、当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。