Oracle的DML语言必备基础知识

原文: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的时候,请务必带上、带对条件!!  

 

参考原文:http://blog.csdn.net/ziwen00/article/details/6697769

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。