SQL_DML语句基础知识(二)

1.利用查询语句建表

CREATE TABLE employe AS

SELECT * FROM emp;

不过这样建表会把emp表中的数据也都复制过来了.如果只想复制表的结构而不复制数据的话则这样

CREATE TABLE employe AS

SELECT * FROM emp WHERE 1=2; --此处1=2可以换成任何一个表达式,只要是使WHERE语句后面条件返回FALSE就行.比如改成4=5啊

注:通过SELECT语句建的表只是每个列名,列的类型与原表相同.原表中的约束条件,索引等都不会复制过来

2.DECODE和CASE

3.只读事务:

我们知道在数据库中在任何时候都有可能有用户在表中插入或删除数据.这样不同的时间查找会得到不同的结果.假如有这样一种业务需求,我们只去统计30号6点之前数据.

之后的不去管.财务会计部门可能会有这种需求.那怎么办呢.我们在六点钟时连接到数据库,然后执行下面的命令

SET TRANSACTION READ ONLY;

从此刻开始在这个会话中所有的查询语句都会忽略掉6点以后的操作.至少oracle后台是怎么实现的还不清楚.反正这有点像把数据库里面的数据都拷贝一份过来让你查.别人之后做的改动影响不到你.举个例子吧.

(1)假如你用sql*plus 连接到oracle.--这为会话1

SET TRANSACTION READ ONLY;

查询表emp:SELECTename,empno FROM emp;结果为

scott 123

(2)另一用户也用sql*plus连接到oracle--这为会话2

INSERT INTO emp(ename,empno) VALUES('arwen',321);

COMMIT;

(3)在会话1中查找SELECT ename,empno FROM emp;结果仍为

scott 123

注意:会话1中只能找查找操作.不用做插入，更新或删除等操作

4.顺序事务:

在只读事务中只能查询不能做DML操作.而顺序事务除了有只读事务的所有功能外还有DML操作的功能.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

--不过要注意当在此会话中如果做了DML操作并提交了.顺序事务貌似就失效了.跟正常的操作又一样了.觉得这功能有点怪怪的啊

MERGE修改表数据

假如我们要对数据库中某个重要的表备份.并定时更新备份的表,以保持与原表同步.oracle里面有个简单的方法实现这功能.就是用merge.下面是语法

MERGE INTO SCHEMA .TABLE ALIAS

USING SCHEMA . { TABLE | VIEW | SUBQUERY } ALIAS

ON (CONDITION)

WHEN MATCHED THEN

UPDATE SET column = value[,column=value]...

WHEN NOT MATCHED THEN

MERGE_INSERT_CLAUSE / MERGE_DELETE_CLAUSE;

示例:

先创建原表ta:

CREATE TABLE ta(ename char(5), eno int);

INSERT INTO ta VALUES('arwen',10);

INSERT INTO ta VALUES('alice',20);

创建备份表ta_copy

CREATE TABLE ta_copy(ename char(5), eno int);

INSERT INTO ta_copy VALUES('god',10);

1.使两表同步:

MERGE INTO ta_copy t_c

USING ta t

ON(t_c.eno=t.eno)

WHEN MATCHED THEN

UPDATE SET t_c.ename = t.ename

--WHERE t_c.ename ='god' --此行可加可不加

WHEN NOT MATCHED THEN

INSERT (t_c.ename,t_c.eno)

VALUES(t.ename,t.eno)

--WHERE t_c.ename='god'--此行可加可不加

;

2.我们注意到1中的例子同时用到了update和insert.但我们也可只使用其中一种.只更新或只插入

只更新:

MERGE INTO ta_copy t_c

USING ta t

ON(t_c.eno=t.eno)

WHEN MATCHED THEN

UPDATE SET t_c.ename = t.ename;

只插入:

MERGE INTO ta_copy t_c
USING ta t
ON(t_c.eno=t.eno)
WHEN NOT MATCHED THEN
INSERT (t_c.ename,t_c.eno)
VALUES(t.ename,t.eno);

3.将原表的数据全部插入到备份表中

MERGE INTO ta_copy t_c
USING ta t
ON(1=0) --此处只要条件为假就行,表示不管重复与否,插入全部数据
WHEN NOT MATCHED THEN
INSERT (t_c.ename,t_c.eno)
VALUES(t.ename,t.eno);