Oracle的DML语言必备基础知识

前提是咱们都已经对常用的数据操纵语言非常熟悉了，对标准SQL：

SELECT子句 --指定查询结果集的列

DROM子句 --指定查询来自哪个表或者试图

[WHERE 子句] --指定查询的条件

[GROUP BY 子句] --指定查询结果集的分组的条件

[HAVING 子句] --指定分组或者集合的查询条件

[ORDERBY 子句] --对查询的排列顺序

[UNION 子句] --多个SELET语句组合,得到结果集的并集

掌握的比较熟悉了。

下面是我参考《Oracle学习笔记》整理的可能会对咱们平时的开发有帮助的SQL基础：

准备工作：

CREATE TABLE dept(

deptno NUMBER(20),--部门编号

NAME VARCHAR2(20) --部门名称

);

CREATE TABLE emp(

empno NUMBER(20),--员工编号

empname VARCHAR2(100),--员工姓名

deptno number(20)--所属部门

);

INSERT INTO dept VALUES('1','新农保组');

INSERT INTO dept VALUES('2','老农保组');

INSERT INTO emp VALUES('1','杨以通','1');

INSERT INTO emp VALUES('2','杨敬义','1');

INSERT INTO emp VALUES('3','曹艳芳','1');

INSERT INTO emp VALUES('4','郝岔蕾','1');

INSERT INTO emp VALUES('5','王炜','1');

INSERT INTO emp VALUES('6','陈字文','1');

INSERT INTO emp VALUES('7','某某人','3');

1、 Oracle中ROWNUM的使用

SELECT * FROM emp WHERE ROWNUM=1; --可以查询到数据

SELECT * FROM emp WHERE ROWNUM=2; --不能查询到数据

SELECT * FROM emp WHERE ROWNUM<3; --可以查询到数据

SELECT * FROM emp WHERE ROWNUM>3; --不能查询到数据

SELECT * FROM emp WHERE ROWNUM BETWEEN 2 AND 5; --不能查询

1） 新农保系统内的分页功能的实现

SELECT *

FROM (SELECT ROWNUM AS MYROWNUM, C.* FROM (

SELECT * FROM EMP --这个地方是咱们写的SQL语句

) C)

WHERE MYROWNUM <= 5 --分页的结束值

AND MYROWNUM > 1 --分页的开始值

系统里面，凡是在JSP里面对LIST进行分页的地方，均会采用上面的方式将咱们编写的SQL语句进行封装一下，从而可以支持数据的分页显示。

2） ROWNUM与排序不是对应的

SELECT ROWNUM,emp.* FROM emp ORDER BY 3;

相应的结果：

可以看出来，ROWNUM并不是1,2，3，4,5那样子顺序排列下去，也就是ROWNUM这个伪列仍旧是在数据取出来的时候就产生了该列，排序并不会改变该列的顺序，所以，ROWNUM与排序操作之间并不存在必然的联系。

2、 实现模糊查询中的通配符

在整个新农保系统的SQL代码中模糊查询通配符基本上都是%，例如：

SELECT * FROM emp WHERE empname LIKE'%艳%';

其实还有其他的通配符：

通配符	含义
%	包含零个或者多个任意字符的字符串
_	任意单个字符
?	任意单个字符 (啥时候用?在LIKE子句中怎么用?)
#	表示0-9的数字(啥时候用?在LIKE子句中怎么用?)
[]	指定范围或者集合中的任意单个字符,例如[a-f]表示a~f中的一个字符 (啥时候用?在LIKE子句中怎么用?)

3、 HAVING子句和WHERE子句的区别

HAVING子句和WHERE子句的区别在于:WHERE子句搜索条件在进行分组操作之前进行,而HAVING子句则是在分组操作之后应用，HAVING的语法和WHERE的语法类似，HAVING可以包含聚合函数，这也从一个方面说明HAVING子句是在分组操作之后进行的。

4、 连接查询的内连接、外连接、交叉连接

1) 内连接

内连接根据每个表共有的列的值来匹配两个表中的列，只有每个表中都存在相同的记录才会出现在结果集中，在内连接中，两个表没有主次之分。在内连接中，出了用‘=’还可以使用INNER JOIN来定义表内部的连接关系，内连接是咱们最常用的一种连接了，以下两种方式得到的结果是一样的：

SELECT * FROM emp a,dept b WHERE a.deptno=b.deptno;

SELECT * FROM emp a INNER JOIN dept b ON a.deptno=b.deptno;

2) 外连接

① 左外连接

左外连接以JOIN子句左侧的表为主表，主表中所有的记录都出现在结果集当中。左外连接可以使用LEFT OUTER JOIN或者LEFT JOIN关键字定义左外连接，以下三个查询语句得到的结果是一致的，需要重点关注（+）是加在从表的后面的哦！

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno(+);

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno;

SELECT * FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno;

② 右外连接

右外连接以连接（JOIN）子句右侧的表为主表，主表中的所有的记录都将出现在结果集中，如果主表中的记录在左表中没有匹配的记录，则结果集中右表的记录为NULL，右外连接可以使用RIGHT OUTER JOIN或者RIGHT JOIN来定义，以下三个操作的结果是一致的：

SELECT * FROM emp,dept WHERE emp.deptno(+)=dept.deptno;

SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.deptno=dept.deptno;

SELECT * FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno;

③ 完整外部连接

完整外部连接包含连接表中的所有的行，无论他们是否匹配。在Oracle中可以使用FULL OUTER JOIN或者FULL JOIN关键字定义完整外部连接。

SELECT * FROM emp FULL OUTER JOIN dept ON emp.deptno=dept.deptno;

SELECT * FROM emp FULL JOIN dept ON emp.deptno=dept.deptno;

结果如下：

3) 交叉连接

SELECT * FROM emp CROSS JOIN dept;

结果如下:

这个结果得到的是两个表之间进行操作的笛卡尔积,交叉连接基本上不会被用到,除非你想穷举两个表之间有多少种记录组合，关键字CROSS JOIN是让两个表进行组合，所以不需要ON关键字来指定两个表的连接关系。

5、 EXISTS关键字的使用

从效率上分析，是选择IN还是选择EXISTS？

IN到EXISTS的转换：

--使用IN来写的代码，Oracle先转换为多表连接，如果不能转换，则先执行SELECT DEPTNO FROM DEPT WHERE DEPT.DEPTNO = 1部分，然后执行外面的那一块

SELECT *

FROM EMP

WHERE EMP.DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DEPT.DEPTNO = 1);

--使用EXITS来实现的两个表之间的连接，Oracle先执行外面的循环SELECT * FROM EMP，在此过程中，去关联检索DEPT表中的内容，根据EXISTS子句的内容看该列是否在DEPT中存在，如果存在则返回TRUE，如果不存在则返回FALSE，因此EXITS后面SELECT 1 还是SELECT 100 都是一样的

SELECT *

FROM EMP A

WHERE EXISTS (SELECT 1

FROM DEPT

WHERE DEPT.DEPTNO = 1

AND DEPT.DEPTNO = A.DEPTNO);

6、 UNION关键字的使用

使用UNION挂件子组合两个查询的结果集，结合的基本规则如下：

① 所有查询中的列数和列的顺序必须要一致

② 数据类型必须要兼容

使用UNION关键字进行合并查询的时候，数据库引擎会自动过滤掉结果集中的重复记录。

使用UNION ALL关键字进行合并查询的时候，数据库引擎就不会过滤结果集中的重复数据，因此在执行效率上将UNION ALL比UNION要高上很多。

7、 SELECT DECODE语句的使用

在新农保系统中，经常会用到SELECT DECODE语句，例如查询该人是男，还是女？如果代码表SELECT * FROM AA10 WHERE AAA100=UPPSER(‘’);的结果集比较小，就没有必要再关联上一张代码值表（视情况），直接使用DECODE函数：

SELECT AAC001,

AAC002,

AAC003,

DECODE(AAC004,'1','男','2','女','未知') AS aac004

FROM AC01;

DECODE函数的语法如下：

DECODE (<输入值>,<值1>,<结果1>[,<值2>,<结果2>…][,<默认结果>])

说明:

如果输入值等于值1,则DECODE函数返回值1,如果输入值等于值2,则DECODE函数返回值2,依次类推.如果参数列表中没有输入值相等的,则DECODE的函数返回默认结果。

8、 在SELECT语句中使用CASE函数

SELECT

CASE

WHEN empno=1 THEN empname||'懒洋洋'

WHEN empno=3 THEN empname||'小屁孩'

ELSE empname

END

FROM emp;

查询的结果：

使用CASE函数可以实现与DECODE同样的功能，但是CASE函数有更强大的作用，尤其是在搜索、统计功能方面，语法如下：

CASE

WHEN <逻辑表达式1> THEN <结果1>

[WHEN<逻辑表达式2> THEN <结果2> …]

[ELSE <默认结果>]

END

9、 保存查询结果、在正式库修改数据之前备份要操作的表

我们在开库修改某一个表的数据的时候，除了要保存执行的脚本，以及保存相应的文档的同时，也需要丢该表进行备份工作。从而能够在万一出现错误的时候，保留恢复的依据，这是一个比较好的习惯。例如：

我们即将对AC43表进行UPDATE或者DELETE操作，除了保存相应的执行脚本，还应当对AC43表进行整体的备份。备份语句如下：

CREATE TABLE ac43_20110816 AS SELECT * FROM ac43;

这样就在数据库中建立了一张新表ac43_20110816。

CREATE TABLE语句的格式如下：

CREATE TABLE <新的表名> AS

<SELECT 子句>

<FROM 子句>

<WHERE 子句>……

10、 修改数据的时候，避免唯一性约束列

首先为EMP的EMPNO字段增加唯一性约束:

ALTER TABLE emp

ADD CONSTRAINT uk_empno

UNIQUE(empno);

然后对EMP的EMPNO列进行UPDATE或者INSERT操作:

INSERT INTO emp VALUES('1','羊羊羊','1');

UPDATE emp SET empno=4 WHERE empname LIKE '杨_通';

都会有一个提示:

插入或修改都不能违反唯一性约束条件XAXNB.UK_EMPNO。

ALTER TABLE emp DROP CONSTRAINT uk_empno; --删除该约束条件

11、 修改数据的时候，不能违法检查性约束

首先在EMP的表上，添加一个检查性约束，检查条件为empno<10;

ALTER TABLE emp

ADD CONSTRAINT ck_empno CHECK(empno<10);

然后执行以下语句:

INSERT INTO emp VALUES('10','羊羊羊','1');

UPDATE emp SET empno=10 WHERE empname LIKE '杨_通';

则会出现一个提示:

ALTER TABLE emp DROP CONSTRAINT ck_empno; --删除该检查约束

12、 修改数据的时候，不能违反外键约束

首先准备数据:

① UPDATE emp SET deptno=2 WHERE empno=7; 目的是防止在创建外键的时候,找不到dept对应的主键。

② alter table DEPT add constraint pk primary key (DEPTNO); 给DEPT表增加一个主键，这样子EMP的外键才能指向DEPT的主键。

③ ALTER TABLE emp

ADD CONSTRAINT fk_emp

FOREIGN KEY(deptno) REFERENCES dept(deptno); 增加外键约束

进行如下操作：

INSERT INTO emp VALUES('10','羊羊羊','4');

UPDATE emp SET deptno=10 WHERE empname LIKE '杨_通';

则会有如下提示产生：

ALTER TABLE emp DROP CONSTRAINT fk_emp; --删除EMP表上的外键约束

13、 如何删除表中的数据（TRUNCATE）

可以使用DELETE语句删除表中的所有的数据。DELETE语句的语法结构如下：

DELETE FROM <表名> WHERE <删除条件>

例如删除EMP和DEPT表中的数据：

DELETE FROM DEPT WHERE DEPTNO<4;

DELETE FROM EMP WHERE EMPNO<3;

如果要删除表中的所有的记录，而不记录单个行的删除操作的话，就可以使用TRUNCATE TABLE操作。TRUNCATE TABLE语句的语法结构如下：

TRUNCATA TABLE <表名>

需要注意的事情是：

① 曾经新农保系统中的几张LOG表的数据量非常的大（上亿条），最开始的时候使用DELETE进行删除，速度非常的慢，使用了大概十几二十分钟,而且最后需要COMMIT。最后在确定了该表没有任何价值之后，使用TRUNCATE TABLE 的方式，速度非常快，大概需要不到一分钟的时间就完成了该表内容的删除。

② TRUNCATE使用有危险，因为默认COMMIT，一旦删除，不容易找回。使用DELETE的时候，请务必带上、带对条件！！