一、分隔符
(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)2 - SUM(expr)2 /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子句指定连接条件。
- 左外连接:LEFT [OUTER] JOIN;
- 右外连接:RIGHT [OUTER] JOIN;
- 完全外连接:FULL [OUTER] JOIN
使用(+)操作符
在Oracle9i之前,当执行外连接时,都是使用连接操作符(+)来完成的。尽管可以使用操作符(+)执行外连接操作,但Oracle9i
开始Oracle建议使用OUTER JOIN执行外连接。使用(+)操作符执外斩首的语法如下:
SELECT table1.column,table2.column FROM table1,table2 where table1.column1(+)=table2.column2;
当使用(+)操作符执行外连接时,应该将该操作符放在显示较少行(完全满连接条件行)的一端。使用时必须注意以下事项。
- (+)操作符只能出现在WHERE子句中,并且不能与OUTER JOIN语法同时使用。
- 当使用(+)操作符执行外连接时,如果在WHERE子句中包含有多个条件,则必须在所有条件中都包含(+)操作符。
- (+)操作符只适用于列,而不能用在表达式上。
- (+)操作符不能与OR和IN操作符一起使用。
- (+)操作符只能用于实现左外连接和右外连接,而不能用于实现完全外连接。
(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