PL/SQL学习笔记[2]-SQL语句
1 子查询插入数据1.1 使用子查询插入数据???
INSERT INTO customer(name,age) SELECT name,age FROM employee WHERE eNo = 200;?1.2 使用子查询执行直接装载
?
INSERT /*+APPEND */ INTO customer(name,age) SELECT name,age FROM employee WHERE eNo = 200;
Notes:
1、以上两条语句的执行结果一样，但2使用了 /*+APPEND */ 来表示采用直接装载方式;
2、当要装载大批量数据是，采用2方法装载数据的速度要远远优于1。具体区别可查看其执行计划查看;
2 多表插入2.1 语法：INSERT ALL WHEN cId = 1 THEN INTO customer1 WHEN age > 20 THEN INTO customer2 ELSE INTO customer3 SELECT * FROM customer;INSERT ALL WHEN cId = 1 THEN INTO customer1 (cId, name, age) WHEN age > 20 THEN INTO customer2 (cId, name, age) ELSE INTO customer3 (cId, name, age) SELECT cId, name, age FROM customer;
当使用 FIRST 操作符执行多表插入时，如果数据已经满足先前条件，并且已经被插入到某表，那么该行数据在后续插入中将不会被再次使用。
INSERT ALL WHEN cId = 1 THEN INTO customer1 WHEN age > 20 THEN INTO customer2 -- 如果age>20的数据中包含cId=1的数据，那么该条数据将不会被再次插入customer2 ELSE INTO customer3 SELECT * FROM customer;3 TRUNCATE TABLE 截断表
TRUNCATE TABLE emp;
Notes：与 DELETE 的区别
1、DELETE 删除表的所有数据时，不会释放表所占用的空间。
2、TRUNCATE 删除表时，不仅会删除表的所有数据，还会释放表所占用空间
3、DELETE 操作可以回滚，而TRUNCATE则无法回滚
只读事务只允许执行查询操作，而不允许执行任何DML操作的事物。使用只读事务可以获取特定的时间点的数据.
-- 会话ASQL> SET TRANSACTION READ ONLY; -- 1 设置当前事务为只读事务。SQL> SELECT * FROM emp; -- 3 由于设置了当前事务为只读事务，此时其他会话对表的更新等操作都不会影响该查询SQL。该SQL获取的仍是其他会话更新前的数据-- 会话BSQL> UPDATE emp SET sal = 3000 where ename = 'jack'; -- 2SQL> COMMIT; 假设会话A在 1 设置了只读事务，会话B在 2 更新了数据，那么会话A在3处得到的数据是在时间点1时的数据，而不是会话B更新后的数据
或者：
SQL> exec dbms_transaction.read_only;4.3 顺序事务
使用顺序事务时,除了具有只读事务的特点外,顺序事务允许执行DML操作.
-- 会话ASQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 1SQL> SELECT sal FROM emp where ename = 'jack'; --3SQL> UDPATE dept SET loc = 'beijing' WHERE deptno = 6;SQL> COMMIT;-- 会话BSQL> UPDATE emp SET sal = 3000 where ename = 'jack'; --2SQL> COMMIT;假设会话A在 1 设置了顺序事务，会话B在 2 更新了数据，那么会话A在 3 查询时将会获取时间点在 1 的数据，而不是会话B在 2 更新后的数据。5 查询分组5.1 ROLLUP
ROLLUP 操作符在生成原有统计结果的基础上，还会生成横向小计结果。
?
SELECT name,address ,SUM(money) FROM customer GROUP BY ROLLUP(name,address);
??? ???? NAME?????????? ADDRESS? COUNT(*) SUM(MONEY)
?? ??? ?------------------ ------- ---------- ----------
?? ??? ?oxcow?? ??? ??? ?? abcdefg?? ??? ???? 2?? ??? ??? ??? ?118.03
?? ??? ?oxcow?? ??? ??? ?????????????????????????? 2?? ??? ??? ??? ?118.03
?? ??? ?leeyee?? ??? ??? ?? a?? ??? ??? ??? ????? 1?? ??? ??? ??? ?32
?? ??? ?leeyee?? ??? ????? 152号大街?? ??? ? 1?? ??? ??? ??? ?12.23
?? ??? ?leeyee?? ??? ??? ????????????????????????? 2?? ??? ??? ??? ?44.23
?? ??? ??? ??? ??? ??? ??? ??????????????????????????? 4?? ??? ??? ??? ?162.26
CUBE 操作符在生成原有统计结果的基础上，还会生成横向小计、纵向小计结果。
SELECT name,address ,SUM(money) FROM customer GROUP BY CUBE(name,address);
??? ??? NAME??????????? ADDRESS? COUNT(*) SUM(MONEY)
??? ??? -------------- -------------- ---------- ----------
??? ??? ??? ??? ??? ??? ??? ????????????????????? 4??? ??? ??? ??? 162.26
??? ??? ??? ??? ??? ??? ??? ? ? a??? ??? ??? ??? 1??? ??? ??? ??? 32
??? ??? ??? ??? ??? ??? ???? abcdefg??? ???? 2??? ??? ??? ?? 118.03
??? ??? ??? ??? ??? ??? ??? ? 152号大街???? 1??? ??? ??? ??? 12.23
??? ??? oxcow??? ??? ??? ??? ??? ??? ??? ??? 2??? ??? ??? ??? 118.03
??? ??? oxcow??? ??? ?? abcdefg??? ??? 2??? ??? ??? ??? 118.03
??? ??? leeyee??? ??? ??? ??? ??? ??? ??? ?? 2??? ??? ??? ??? 44.23
??? ??? leeyee??? ??? ??? ? a??? ??? ??? ?? 1??? ??? ??? ??? 32
??? ??? leeyee??? ??? ??? 152号大街??? 1??? ??? ??? ??? 12.23
GROUPING 函数用于确定统计结果是否用到了特定列。如果返回0，则表示统计结果使用了该列；如果返回1，则表示统计结果未使用该列
SELECT name,address ,SUM(money),GROUPING(name),GROUPING(address) FROM customer GROUP BY CUBE(name,address);
??? ???? NAME???????????????????????? ADDRESS? COUNT(*) SUM(MONEY) GROUPING(name)? GROUPING(address)
?? ??? ?---------------------------- ------- ---------- --------- --------------? -----------------
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? 4?? ??? ??? ??? ?162.26?? ??? ??? ??? ??? ?1?? ??? ??? ??? ??? ??? ??? ??? ?1
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ???? a?? ??? ??? ??? ? 1?? ??? ??? ??? ?32?? ??? ??? ??? ??? ??? ??? ?1?? ??? ??? ??? ??? ??? ??? ??? ?0
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?? abcdefg?? ??? ? 2?? ??? ??? ??? ?118.03?? ??? ??? ??? ??? ?1?? ??? ??? ??? ??? ??? ??? ??? ?0
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? 152号大街?? ? 1?? ??? ??? ??? ?12.23?? ??? ??? ??? ??? ??? ?1?? ??? ??? ??? ??? ??? ??? ??? ?0
?? ??? ?oxcow?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ????? 2?? ??? ??? ??? ?118.03?? ??? ??? ??? ??? ?0?? ??? ??? ??? ??? ??? ??? ??? ?1
?? ??? ?oxcow?? ??? ??? ??? ??? ??? abcdefg?? ??? ? 2?? ??? ??? ??? ?118.03?? ??? ??? ??? ??? ?0?? ??? ??? ??? ??? ??? ??? ??? ?0
?? ??? ?leeyee?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ???? 2?? ??? ??? ??? ?44.23?? ??? ??? ??? ??? ??? ?0?? ??? ??? ??? ??? ??? ??? ??? ?1
?? ??? ?leeyee?? ??? ??? ??? ??? ?????? a?? ??? ??? ??? ?1?? ??? ??? ??? ?32?? ??? ??? ??? ??? ??? ??? ?0?? ??? ??? ??? ??? ??? ??? ??? ?0
?? ??? ?leeyee?? ??? ??? ??? ??? ??? 152号大街?? ?? 1?? ??? ??? ??? ?12.23?? ??? ??? ??? ??? ??? ?0?? ??? ??? ??? ??? ??? ??? ??? ?0
GROUPING SETS 操作符可以合并多个分组结果。
SELECT name, COUNT(*) FROM customer GROUP BY name;
??? ???? NAME?????????????????????????????????????? COUNT(*)
?? ??? ?---------------------------------------- ----------
?? ??? ?leeyee??????????????????????????????????????????? 2
?? ??? ?oxcow???????????????????????????????????????????? 2
SELECT address,COUNT(*) FROM customer GROUP BY address
??? ???? ADDRESS?????????????????????????????????? COUNT(*)
?? ??? ?---------------------------------------- ----------
?? ??? ?a?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? 1
?? ??? ?abcdefg?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? 2
?? ??? ?152号大街?? ??? ??? ??? ??? ??? ??? ??? ??? ?? 1
SELECT name,address,COUNT(*) FROM CUSTOMER GROUP BY GROUPING SETS(name,address);
??? ???? NAME ? ADDRESS?? COUNT(*)
?? ??? ?-----?? ??? ?---------??? ---------- ?
?? ??? ?leeyee?? ??? ??? ??? ??? ??? ??? 2
?? ??? ?oxcow?? ??? ??? ??? ??? ??? ???? 2
?? ??? ??? ??? ??? ??? ? a?? ??? ??? ??????? 1
?? ??? ??? ??? ??? ?? abcdefg?? ??? ???? 2
?? ??? ??? ??? ??? ?? 152号大街?? ??? ? 1?? ????
SELECT table1.column, table2.column FROM table1 [INNER | LEFT | RIGTH | FULL ] JOIN table2 ON table1.column1 = table2.column2;6.2 内连接
内连接用于返回满足连接条件的所有记录；默认情况下，在执行连接查询时如果没有指定任何连接操作符，那么这些连接查询都属于内连接。
SELECT a.dname,b.ename FROM department a, employee b WHERE a.deptNo = b.deptNo;-- 或者SELECT a.dame,b.ename FROM department a INNER JOIN employee b ON a.deptNo = b.deptNo;
从Ooracle9i 开始，如果主表主键列和从表外键列名称相同，那么还可以使用 NATURAL JOIN 关键字自动执行内连接。
SELECT dname,ename FROM department NATRUAL JOIN employee;6.3 外连接
外连接是内连接的扩展，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的记录.
6.3.1 左外连接左外连接通过 LEFT [OUTER] JOIN 实现；左外连接返回满足连接条件的记录，同时返回不满足条件的连接操作符左边表的其他行；
SELECT a.dname, b.ename FROM department a LEFT JOIN employee b ON a.deptno = b.deptno;-- 或者SELECT a.dname, b.ename FROM department a, employee b WHERE a.deptno = b.deptno(+);
??? ??? ??? DNAME?????? ENAME
??? ??? ??? ------??? ??? -------
??? ??? ??? test??? ??? ???? king
??? ??? ??? test ??? ??? ??? king2
??? ??? ??? test1??? ??? ??? ???
??? ??? ??? test2
右外连接通过 RIGTH [OUTER] JOIN 实现；右外连接返回满足连接条件的记录，同时返回不满足条件的连接操作符右边表的其他行；
SELECT a.dname, b.ename FROM department a RIGHT JOIN employee b ON a.deptno = b.deptno;-- 或者SELECT a.dname, b.ename FROM department a, employee b WHERE a.deptno(+) = b.deptno;
??? ??? ??? DNAME?????? ENAME
??? ??? ??? ------??? ??? -------
??? ??? ??? test??? ??? ??? ? king
??? ??? ??? test ??? ??? ???? king2
??? ??? ??? ??? ??? ??? ??? ??? king3
??? ??? ??? ??? ??? ??? ??? ??? king4
完全外连接通过 FULL [OUTER] JOIN 实现。完全外连接时左外连接和右外连接的结合.
SELECT a.dname, b.ename FROM department a FULL JOIN employee b ON a.deptno = b.deptno;
??? ??? ??? DNAME?????? ENAME
??? ??? ??? ------??? ??? -------
??? ??? ??? test??? ??? ???? king
??? ??? ??? test ??? ??? ??? king2
??? ??? ??? test1
??? ??? ??? test2
??? ??? ??? ??? ??? ??? ??? ?? king3
??? ??? ??? ??? ??? ??? ??? ?? king4
Oracle9i 前使用(+)操作符。Oracle9i 后建议使用 OUTER JOIN 执行外连接。使用(+)操作符语法如下：
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1(+) = table2.column2;
当使用(+)操作符时，应该将该操作符放在显示较少行（完全满足连接条件）的一端。
Notes：
1、(+)操作符只出现在 WHERE 子句中，并且不能同 OUTER JOIN 语法同用；
2、当使用(+)操作符执行外连接时，如果 WHERE 子句中包含多个条件，则必须所有条件中都包含(+)操作符；
3、(+)操作符只适用于列，不能用在表达式上；
4、(+)操作符不能与 OR 和 IN 操作符一起使用；
5、(+)操作符只能实现左外、右外连接，不能实现完全连接
只返回一行数据的子查询语句。当在 WHERE 中引用单行子查询时，可以使用单行比较符 =,>,<,>=,<=,<>
SELECT ename, salary, deptNo FROM employee WHERE deptNo = (SELECT deptNo FROM employee WHERE ename = 'SCOTT');7.2 多行子查询
返回多行数据的子查询语句。当在 WHERE 中引用多行子查询时，必须要使用多行比较符 IN,ALL,ANY
--------------------------------
| 运算符???????????? |???????? 含义??????????? ??? ??? ??? ??? ??? |
------------------------------
|? IN??? ??? ??? ?? ? |??? 匹配于子查询结果的任一个值即可? ??? |
|? ALL??? ??? ?????? | 必须要符合子查询结果的所有值??? ??? ??? |
|? ANY??? ? ? ?? ?? | 只要符合子查询结果的任一个值即可?? ?? |
-----------------------------
SELECT ename, job, sal, deptno FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10);7.2.2 ALL 操作符
SELECT ename, job, sal, deptno FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 10);?7.2.3 ANY 操作符
SELECT ename, job, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 10);7.3 多列子查询
SELECT ename, sal, comm, deptno FROM emp WHERE (sal, nvl(comm,-1)) IN (SELECT sal, nvl(comm,-1) FROM emp WHERE deptno = 10 )?7.3.2 非成对比较示例
SELECT ename, sal, comm, deptno FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30) AND NVL (comm, -1) IN (SELECT NVL (comm, -1) FROM emp WHERE deptno = 30);7.4 其他子查询7.4.1 相关子查询
相关子查询是指需要引用主查询表列的子查询语句，相关子查询是通过 EXISTS 谓词实现的。
SELECT ename, sal, job, depton FROM emp WHERE EXISTS (SELECT 1 FROM dept WHERE dept.depton = emp.depton);
当使用 EXISTS 谓词时，如果子查询存在返回结果，则条件为 TRUE; 如果子查询没有返回结果，则条件为 FALSE
7.4.2 在 FROM 子句中使用子查询FROM 中的子查询会被当作视图对待，因此也被称作内嵌视图.
SELECT ename, job, sal FROM emp, ( SELECT deptno, AVG (sal) avgsal FROM emp GROUP BY deptno) dept WHERE emp.deptno = deptno AND sal > dept.avgsal;
Notes：FROM子句中使用子查询时，必须要给子查询指定别名
7.4.3 在 DML 语句中使用子查询-- 在 INSERT 语句中使用子查询INSERT INTO customer (name, age) SELECT name, age FROM employee WHERE eNo = 200;-- 在 UPDATE 语句中使用子查询UPDATE emp SET (sal,comm) = (SELECT sal, comm FROM emp WHERE ename = 'jack') WHERE job = (SELECT job FROM emp WHERE ename = 'jack');-- 在 DELETE 语句中使用子查询DELETE emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'jack');7.4.4 在 DDL 语句中使用子查询
-- 在 CREATE TABLE 语句中使用-- 使用子查询可以在建立新表的同时复制表中的数据CREATE TABLE new_emp( id, name, sal, job)AS SELECT empno, ename, esal, ejob FROM emp;-- 在 CREATE VIEW 中使用-- 创建视图时必须要指定视图所对应的子查询语句CREATE OR REPLACE VIEW dept_10 AS SELECT empno,ename,esal,ejob FROM emp ORDER BY empno; -- 在 CREATE MATERIALIZED VIEW 中使用-- 创建实体化视图时，必须要指定实体化视图所对应的SQL语句，并且该SQL语句将来可用于查询重写。CREATE MATERIALIZED VIEW summary_emp AS SELECT deptno, job, AVG(sal) avasal, SUM(sal) sumsal FROM emp GROUP BY CUBE(deptno, job);8 合并查询
语法:
?
SELECT 语句1 [UNION | UNION ALL | INTERSECT | MINUS] SELECT 语句2
1、这些集合操作符具有相同的优先级。同时使用时会按照从左至右的方式引用这些集合操作符;
2、使用集合操作符时，必须确保不同查询的列个数和数据类型都要匹配;
3、对于LOB、VARRAY和嵌套表列来说，集合操作符无效;
4、对于LONG列来说，UNION、INTERSECT、MINUS操作无效;
5、如果选择列表包含了表达式，则必须要为其指定列别名;
8.1?UNION合并结果集，并会自动去掉结果集中的重复行，并且会以第一列的结果进行排序.
?
SELECT ename,sal,job FROM emp WHERE sal > 2500UNIONSELECT ename,sal,job FROM emp WHERE job = 'manager';
?
8.2?UNION ALL合并结果集，但不会去掉结果集中的重复行，也不会进行任何排序，只是简单的做合并.
?
SELECT ename,sal,job FROM emp WHERE sal > 2500UNION ALLSELECT ename,sal,job FROM emp WHERE job = 'manager';8.3?INTERSECT
获取两个结果集的交集，并以第一列的结果进行排序.
?
SELECT enme,sal,job FROM emp WHERE sal > 2500 INTERSECTSELECT ename,sal,job FROM emp WHERE job = 'manager';8.4?MINUS
获取两个结果集的差集。只显示在第一个结果集中存在，在第二结果集中不存在的数据，并以第一列的结果进行排序.
?
SELECT ename,sal,job FROM emp WHERE sal > 2500MINUSSELECT ename,sal,job FROM emp WHERE job = 'manager';9 其他复杂查询9.1 层次查询
当表具有层次结构时，使用层次查询可以更直观的显示数据结果，并显示其数据之间的层次关系.
语法：
?
SELECT 语句 START WITH condition CONNECT BY ..
?
START WITH: 用于指定层次查询的根行;
CONNECT BY: 用户指定父行和子行之间的关系。在condition表达式中，必须使用PRIOR引用父行。语法如下
... PRIOR expr = expr 或者 ... expr = PRIOR expr
?
-- 假如emp表具层次结构，其中empno列对应雇员号，而mgr列对应管理者编号。那么通过层次查询，可以显示雇员之间的上下级关系。SQL> col ename format a15SQL> col job format a15SQL> SELECT LPAD(' ', 3 * (LEVEL-1))||ename ename,SQL> LPAD(' ', 3 * (LEVEL-1))||job job FROM empSQL> WHERE job <> 'clean' START WITH mgr IS NULLSQL> CONNECT BY mgr = PRIOR empno;9.2 使用 CASE 表达式为了在 SQL 语句中使用 IF..THEN..ELSE 语法，可以使用 CASE 表达式。当使用 CASE 表达式时，可以使用 WHERE子句指定条件语句.
?
SELECT ename,sal, CASE WHEN sal > 3000 THEN 3 WHEN sal > 2000 THEN 2 ELSE 1 END gradeFROM empWHERE deptno = 10;9.3 使用 WITH 子句重用子查询
Oracle9i开始，通过 WITH 子句可以给子查询指定一个名称，并且使得在一条语句中可以完成所有任务，避免使用临时表
WITH summary AS (SELECT dname, SUM(sal) AS dept_total FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY dname )SELECT dname, dept_totalFROM summaryWHERE dept_total > ( SELECT SUM(dept_total) * 1/3 FROM summary );9.4 倒叙查询
默认情况下执行查询操作只能看到最近提交的数据。从Oracle9i开始，通过使用倒叙查询(Flashback Query)特征，可以查看到过去某个时间点所提交的数据。
Notes：
SELECT ename, sal FROM emp WHERE ename = 'jack';9.4.2?查看历史数据
执行倒叙查询时，通过在 FROM 子句后指定 AS OF 子句可以查看过去的历史数据。AS OF 中既可以指定时间，也可以指定SCN.
SELECT ename,salFROM emp AS OF TIMESTAMP to_timestamp('2011-10-12 16:00:00','YYYY-MM-DD HH24:MI:SS')WHERE ename = 'jack';Notes：使用倒叙查询只能看到5分钟之前变化数据。
9.4.3?使用DBMS_FLASHBACK包获取特定SCN的数据SQL> exec dbms_flashback.enable_at_system_change_number(717402);SQL> SELECT sal FROM emp WHERE ename = 'jack';SQL> exec dbms_flashback.disable;SQL> SELECT sal FROM emp WHERE ename = 'jack';
?