Oracle SQL 编码

一、分隔符

（1） 单符号分隔符

 

符号	含义
+	加法操作符
%	属性提示符
‘	字符串分隔符
.	组件分隔符
/	除法操作符
(	表达式或列表分隔符
)	表达式或列表分隔符
:	非 PL/SQL 变量提示符
,	项分隔符（表名、列名等分隔符）
*	乘法操作符
“	双引号变量分隔符
=	相符操作符
<	小于操作符
>	大于操作符
@	远程数据库访问操作符
;	语句终止符
-	减法操作符或负数操作符

 

（2）组合分隔符

符号	含义
:=	赋值操作符
->	关联操作符
||	连接操作符
**	幂操作符
<<	标号开始分隔符
>>	标号线束分隔符
/*	多行注释开始分隔符
*/	多行注释结束分隔符
..	范围操作符
<>	不等操作符
!=	不等操作符
^=	不等操作符
<=	小于等于操作符
>=	大于等于操作符
--	单行注释提示符

二、Oracle SQL DML(INSERT、UPDATE、DELETE)

1、INSERT

（1）使用子查询插入数据
    示例一：
   

insert into employee(empno,ename,sal,deptno) 
select empno,ename,sal,deptno from emp 
where deptno=20;

    示例二：

insert /* +APPEND */ into employee(empno,ename,sal,deptno) 
select empno,ename,sal,deptno from emp 
where deptno=20;

    注意，尽管以上两条语句的执行结果一样，但第二条语句使用/* +APPEND */ 表示采用直接装载方式。当要装载大批量数据时，采用第二种方法装载数据的速度要远远优于第一种方法。

（2）使用多表插入数据
    在Oracle9i之前，当执行INSERT语句插入数据时，只能为单个表插入数据。从Oracle9i开始，使用INSERT语句可以将某张表的数据同时插入到多张表中。
    语法如下：
    INSERT ALL insert_into_claus [value_clause] subquery;
    INSERT conditional_insert_clause subquery;
    如上所示， insert_into_claus用于指定INSERT子句；value_clause用于指定值子句；subquery用于指定提供数据的子查询；conditional_insert_clause用于指定INSERT条件子句。
    示例一：使用ALL操作符执行多表插入
    当使用ALL操作符执行多表插入时，在每个条件子句上都要执行INTO子句后的子查询。

    INSERT ALL
    WHEN deptno=10 THEN INTO dept10
    WHEN deptno=20 THEN INTO dept20
    WHEN deptno=30 THEN INTO dept30
    WHEN job='CLERK' THEN INTO clerk
    ELSE INTO other
    SELECT * FROM emp;

    示例二：使用FIRST操作符执行多表插入
    当使用FIRST操作符执行多表插入时，如果数据已经满足了先前条件，并且已经被插入到某表，那么该行数据在后续插入中将不会被再次使用。

    INSERT FIRST
    WHEN deptno=10 THEN INTO dept10
    WHEN deptno=20 THEN INTO dept20
    WHEN deptno=30 THEN INTO dept30
    WHEN job='CLERK' THEN INTO clerk
    ELSE INTO other
    SELECT * FROM emp;

 

2、UPDATE

 

(1)使用子查询更新数据
    当使用UPDATE语句更新数据时，不仅可以使用表达式或数值直接更新数据，也可以使用子查询更新数据。某些情况下，使用子查询执效率更好。另外，当使用触发器复制表之间数据时，使用子查询可以更新相关表的数据。下面通过示例说明使用子查询更新数据的方法。
    <1>更新关联数据
    当更新关联数据时，使用子查询可以降低网络开销。假设你希望雇员SCOTT的岗位、工资、补助与雇员SMITH完全相同，如果使用表达式或列值进行修改，那么需要取得SMITH的岗位、工资、补助，然后执行UPDATE语句进行修改。
    示例如下：

    select job,sal,comm from emp where ename='SMITH';
   
    JOB    SAL    COMM
    --------------------
    CLERK    2200    200
   
    update emp set job='CLERK',sal=2200,comm=200;

    在存在可以确定的条件时，通过使用子查询只需要编写一条语句就可以完成这项任务，从而降低了网络开销。
    示例如下：

    UPDATE    emp SET (job,sal,comm)=(SELECT job,sal,comm FROM emp WHERE ename='SMITH')
    WHERE ename='SCOTT';

    <2>复制表数据
    当使用触发器复制表数据时，如果表A数据被修改，那么表B数据也应该修改。通过使用子查询，可以基于一张表修改另一张表的数据。
    示例如下：

    UPDATE employee SET deptno=(SELECT deptno FROM emp WHERE empno=7788)
    WHERE job=(SELECT job FROM emp WHERE empno=7788);

 

3、DELETE

（1）使用子查询删除数据
    当使用DELETE语句删除数据时，可以直接在WHERE子句中指定值，并根据条件来删除数据。另外，也可以在WHERE子句中使用子查询作为条件。假定要解雇SALES部门的所有雇员，那么在WEHERE子句中需要使用子查询。
    示例如下：

DELETE FROM emp where deptn= (SELECT deptno FROM dept WHERE dname='SALES');

（2）使用TRUNCATE TABLE截断表
    当使用DELETE语句删除表的所有数据时，不会释放表所占用的空间。如果用户确定要删除表的所有数据，那么使用“TRUNCATE TABLE”语句速度更快。
    示例如下：

TRUNCATE TABLE emp;

    如上所示，使用TRUNCATE TABLE语句不仅会删除表的所有数据，而且还会释放表所占用的空间。注意，DELETE语句的操作可以回退的，但TRUNCATE TABLE语句的操作是不能回退。

 

三、分组函数 （GROUP BY）
    分组函数用于统计表的数据。与单行函数不同，分组函数作用于多行，并返回一个结果，所以有时也被称为多行函数。一般情况下，分组函数要与GROUP BY子句结合使用。在使用分组函数时，如果忽略了GROUP BY子句，那么会汇总所有行，并产行一个结果。Oralce数据库提供了大量的分组函数，在这里给大家介绍最常用的七个分组函数：
    <1>MAX ：该函数用于取得列或表达式的最大值，它适用于任何数据类型。
    <2>MIN ：该函数用于取得列或表达式的最小值，它适用于任何数据类型。
    <3>AVG ：该函数用于取得列或表达式的平均值，它适用于数字类型。
    <4>SUM ：该函数用于取得列或表达式的总和，它适用于任数字类型。
    <5>COUNT ：该函数用于取得总计行数。
    <6>VARIANCE ：该函数用于取得列或表达式的方差，并且该函数只适用于数字类型。当只有一行数据时，其返回值为0；当存在多行数据时，方差是按照如下公式计算取得：

                (SUM(expr)² - SUM(expr)² /COUNT(expr))/( COUNT(expr)-1)
    <7>STDDEV ：该函数用于取得列或表达式的标准偏差，并且该函娄只适用于数字类型。当只有一行数据时，其返回值为0；当存在多行数据时，Oracle按照方差的平方根来计算标准偏差。
        示例：

SELECT variance(sal),stddev(sal) FROM emp;

 

1、ROLLUP和CUBE
    当直接使用GROUP BY执行数据统计时，只会生成列的相应数据统计。例如，如果要统计不同部门不同岗位的平均工资，那么直接使用GROUP BY子句。
    示例：

select deptno,job,avg(sal) from emp group by deptno,job;

    其统计结果如下表格：

部门号   岗位	CLERK	ANALYST	MANAGER	PERSIDENT	SALESMAN
10	1300		2450	5000
20	2050	3500	2975
30	950		2850		1400
合计

    在实际应用中，如果以上统计结果还不能满足需求，还希望产生横向、纵向的统计结果，此时可以使用ROLLUP和CUBE操作符。当使用ROLLUP操作符时，在生成原有统计结果的基础上，还会生成横向的小计结果（部门平均工资、所有雇员平均工资）。
    示例

select deptno,job,avg(sal) from emp group by rollup(deptno,job);

    其统计结果如下表格：

部门号   岗位	CLERK	ANALYST	MANAGER	PERSIDENT	SALESMAN	部门平均工资
10	1300		2450	5000		2916
20	2050	3500	2975			2815
30	950		2850		1400	1566
合计						2301

    当使用CUBE操作符时，在原有ROLLUP统计结果的基础上，还会生成纵向小计结果（岗位平均工资）。
    示例:

select deptno,job,avg(sal) from emp group by cube(deptno,job);

    其统计结果如下表格：

部门号 岗位	CLERK	ANALYST	MANAGER	PERSIDENT	SALESMAN	部门平均工资
10	1300		2450	5000		2916
20	2050	3500	2975			2815
30	950		2850		1400	1566
岗位平均工资	1587	3500	2758	5000	1400
合计						2301

2、GROUPING SETS
    当使用GROUP BY子句执行数据分组统计时，默认情况下只会显示相应列的分组统计结果。在编写应用程序时，有些情况可能需要生成多种分组数据结果。在早期Oralce版本中，显示多个分组统计的结果，必须要编写多条数据分组语句来实现；从Oracle9i开始，使用GROUPING SETS操作符可以合并多个分组的结果。
    下面以示例说明GROUPING SETS操作的作用及使用方法：
    示例一：显示部门平均工资

select deptno,avg(sal) from emp group by deptno;

 结果：

    DEPTNO        AVG(SAL)
    ------------------------
    10        2916.66667
    20        2175
    30        1566.66667

    示例二：显示岗位平均工资

select job,avg(sal) from emp group by job;

结果：

    JOB        AVG(SAL)
    ------------------------
    ANALYST        30000
    CLERK        1037.5
    MANAGER        2758.333
    PERSIDENT    5000
    SALESMAN    1400

    示例三：显示部门平均工资和岗位平均工资
    为了显示多个分组统计结果，可以使用GROUPING SETS操作符合并分组统计结果。例如，如果既要显示部门的平均工资，也要显示岗位的平均工资，那么可使用GROUPING SETS操作符合并分组结果。
    示例如下：

select deptno,job,avg(sal) from emp group by job;

 结果：

    DEPTNO JOB    AVG(SAL)
    ------------------------
    10        2916.66667
    20        2175
    30        1566.66667
    ANALYST        30000
    CLERK        1037.5
    MANAGER        2758.333
    PERSIDENT    5000
    SALESMAN    1400

 

  

四、连接查询

    连接查询是指基于两个或两个以上表或视图的查询。在实际应用中查询单个表可能无法满足应用程序的实际需求，在这种情况下就需要进行连接查询。

• 当使用连接查询时，必须在FROM子句后指定两个或两个以上的表。

• 当使用连接查询时，应该在列名前加表名作为前缀。但是，如果不同表之间的列名不同，那么不需要在列名前加表名作为前缀；如果不同表之间的存在同名列，那么在列名之前必须要加表名作为前缀，否则会因为列的二义性而报错。
• 当使用连接查询时，必须在WHERE子句中指定有效的连接条件（在不同表的列之间进行连接）。如果不指定连接条件，或者指定了无效的连接条件，那么导致生成笛卡儿积（X*Y）。
• 当进行连接查询时，使用表别名可以简化连接查询语句。当指定表别名时，别名应该跟在表名后面

   
    内连接和外连接
        内连接用于返回满足连接条件的记录；而外连接则是内连接的扩展，它不仅会返回满足连接条件的所有记录，而且会还返

回满足连接条件的记录。
       

SELECT table1.column,table2.column FROM table [INNER | LEFT | RIGHT | FULL] JOIN table2 ON table1.column1=table2.column2;

  

         注意，如果使用FROM子句指定内、外连接，则必须要使用ON子句指定连接条件；如果使用（+）操作符指定外连接，则必须使用WHERE子句指定连接条件。

1. 左外连接：LEFT [OUTER] JOIN；
2. 右外连接：RIGHT [OUTER] JOIN；
3. 完全外连接：FULL [OUTER] JOIN

    使用（+）操作符
    在Oracle9i之前，当执行外连接时，都是使用连接操作符（+）来完成的。尽管可以使用操作符（+）执行外连接操作，但Oracle9i

开始Oracle建议使用OUTER JOIN执行外连接。使用（+）操作符执外斩首的语法如下：

SELECT table1.column,table2.column FROM table1,table2 where table1.column1(+)=table2.column2;

    当使用（+）操作符执行外连接时，应该将该操作符放在显示较少行（完全满连接条件行）的一端。使用时必须注意以下事项。

1. （+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
2. 当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
3. （+）操作符只适用于列，而不能用在表达式上。
4. （+）操作符不能与OR和IN操作符一起使用。
5. （+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

   （1） 使用（+）操作符执行左外连接。

    当使用左外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的左另表的其他行。因为（+）操作符要放在行数少的一端，所以在WHERE子句中应当将该操作符放在右表的一端。
    实现左外连接，要把（+）放在右表的列，使结果集的右边行数变少，下面示例当中是emp表或emp表的别名b。
    示例如下：
   

SELECT a.dname,b.ename FROM dept a, emp b WHERE a.deptno=b.deptno(+) AND b.deptno(+)=10;

 

    DNAME        ENAME
    -------------------------------
    ACCOUNTING    CLARK
    ACCOUNTING    KING
    ACCOUNTING    MILLER
    SALES
    OPERATIONS
    PESEARCH

 
    （2）使用（+）操作符执行右连接。
    当使用右外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的右另表的其他行。因为（+）操作符要放在行数少的一端，所以在WHERE子句中应当将该操作符放在左表的一端。
    实现右外连接，要把（+）放在左表的列上，使结果集的左边行数变少，下面示例当中是dept 表或dept 表的别名a。
    示例如下：
   

SELECT a.dname,b.ename FROM dept a, emp b WHERE a.deptno(+) =b.deptnoAND a.deptno(+)=10;

 

	DNAME		ENAME
	-------------------------------
	ACCOUNTING	CLARK
	ACCOUNTING	KING
	ACCOUNTING	MILLER
				JAMES
				TURNER