数据处理(DML、RETURNING、MERGE INTO)_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2121 | 回复: 0

主题： 数据处理(DML、RETURNING、MERGE INTO)

shylone

注册用户

等级：下士
经验：199
发帖：90
精华：0
注册：2011-11-24
状态：离线
发送短消息息给shylone

加好友发送短消息息给shylone

发消息

发表于：

2015-7-22 10:19:48 | [全部帖] [楼主帖]

楼主

一、常用的DML语句及事物处理

向表中插入数据(INSERT)

更新表中数据(UPDATE)

从表中删除数据(DELETE)

将表中数据和并(MERGE)

控制事务(TRANSACTION)

二、DML 可以在下列条件下执行:

向表中插入数据

修改现存数据

删除现存数据

事务是由完成若干项工作的DML语句组成的。

三、插入数据

INSERT语句语法:

INSERTINTOtable [(column [, column...])]
VALUES(value [, value...]);

使用这种语法一次只能向表中插入一条数据。

为每一列添加一个新值。

按列的默认顺序列出各个列的值。

在INSERT子句中随意列出列名和他们的值。

字符和日期型数据应包含在单引号中。

--查看emp表的表结构
SQL>DESC emp;
NameNull?    Type
----------------------------------------------------- -------- -----------------------
EMPNO                                                 NOTNULL NUMBER(4)
ENAME                                                          VARCHAR2(30)
JOB                                                            VARCHAR2(9)
MGR                                                            NUMBER(4)
HIREDATE                                                       DATE
SALARY                                                         NUMBER(8,2)
DEPTNO                                                         NUMBER(2)

1.向表中插入空值

隐式方式:在列名表中省略该列的值。

SQL>INSERTINTO emp(empno,ename,job,salary)--列出部分列名
2  VALUES(1234,'Frank','saleman',8000);
1 row created.
SQL>SELECT*FROM emp WHERE ename='Frank';
EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO
---------- ------------------------------ --------- ---------- --------- ---------- ----------
1234 Frank                          saleman                              8000

显示方式:在VALUES子句中指定空值NULL。

注意此处省略了列列表，当列的列表被省略时，则values关键字中应当为所有的字段列提供列值

SQL>INSERTINTO emp VALUES(100,'Jack','manager',null,null,20000,10);
1 row created.

2.插入指定的值

SYSDATE 记录当前系统的日期和时间。

SQL>INSERTinto EMP(empno,ename,job,hiredate,salary)
2  VALUES(1235,'Tony','boy',sysdate,7000);
1 row created.
SQL>ALTER SESSION SET nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL>SELECT*FROM emp WHERE ename ='Tony';
EMPNO ENAME           JOB              MGR HIREDATE                SALARY     DEPTNO
---------- --------------- --------- ---------- ------------------- ---------- ----------
1235 Tony            boy                  2010-06-28 13:48:59       7000

插入特定的日期值

SQL>INSERTINTO emp
2 VALUES(1236,'Ben','IT',null,TO_DATE('JUN 28 2010','MON DD YYYY'),3000,20);

3.在列中使用单引号和双引号

--单引号的使用
SQL>INSERTINTO emp VALUES
2  (1238,'Dan','Malley''K','',sysdate,2900,20);
1 row created.
--双引号的使用
SQL>INSERTINTO emp VALUES
2  (1239,'Dane','A "Big" L','',sysdate,2900,20);
1 row created.
SQL>SELECT*FROM emp WHERE ename LIKE'Dan%';
EMPNO ENAME              JOB              MGR HIREDATE                SALARY     DEPTNO
---------- ------------------ --------- ---------- ------------------- ---------- ----------
1238 Dan                Malley'K             2010-06-28 14:04:35       2900         20
1239 Dane               A "Big" L            2010-06-28 14:07:12       2900         20

4.从其它表中拷贝数据（利用子查询向表中插入数据）

在INSERT语句中加入子查询。

不必书写VALUES子句。

子查询中的值列表应与INSERT子句中的列名对应

--克隆表结构
SQL>CREATETABLE test ASSELECT*FROM scott.emp WHERE 0 = 1;
Table created.
--使用select 子句插入新值
SQL>INSERTINTO test SELECT*FROM scott.emp;
21 rows created.
--使用select 子句插入部分列值
SQL>INSERTINTO test(empno,ename,job,salary)
2  SELECT empno,ename,job,salary
3  FROM scott.emp
4  WHERE ename LIKE'Dan%';
2 rows created.

5.利用替代变量向表中插入数据

SQL>INSERTINTO test(empno,ename,job,salary)
2  VALUES(&empno,'&ename','&job',&salary);
Enter valuefor empno: 1240
Enter valuefor ename: Andy
Enter valuefor job: Singer
Enter valuefor salary: 3600
old   2:VALUES(&empno,'&ename','&job',&salary)
new   2:VALUES(1240,'Andy','Singer',3600)
1 row created.

四、更新数据(UPDATE)

语法：

UPDATEtable
SETcolumn=value [, column = value, ...]
[WHERE condition];

可以一次更新多条数据。

使用WHERE子句指定需要更新的数据，如果省略WHERE子句，则表中的所有数据都将被更新。

1.直接更新

SQL>UPDATE emp SET salary = salary + 100 WHERE ename ='SCOTT';
1 row updated.

2.在UPDATE语句中使用子查询

SQL>UPDATE emp SET salary =
2 (SELECT salary FROM emp WHERE ename ='SCOTT')
3 WHERE empno = 7839;
1 row updated.

3.使用多列子查询来修改记录：

SQL>UPDATE emp SET(job,salary)
2 =(SELECT job,salary FROM emp WHERE ename ='SCOTT')
3 WHERE ename ='Jack';
1 row updated.

五、删除数据

使用DELETE语句从表中删除数据。

DELETE [FROM] table [WHERE condition];

1.使用WHERE子句指定删除的记录，如果省略WHERE子句，则表中的全部数据将被删除。

SQL>DELETEFROM emp WHERE empno = 1234;
1 row deleted.

2.在DELETE中使用子查询

在DELETE中使用子查询，使删除基于另一个表中的数据。

SQL>DELETEFROM emp
2 WHERE deptno =
3 (SELECT deptno FROM scott.dept WHERE dname ='ACCOUNTING');
4 rows deleted.

六、在DML语句中使用WITHCHECKOPTION

子查询可以用来指定DML语句的表和列

WITHCHECKOPTION关键字可以防止更改不在子查询中的行

--deptno列不在select列表中，故不能被更新
SQL>INSERTINTO
2      (SELECT empno,ename,job,mgr,salary FROM emp
3       WHERE deptno = 20 WITHCHECKOPTION)
4  VALUES(1250,'Smith','Clerk',7902,3000);
(SELECT empno,ename,job,mgr,salary FROM emp

ERROR at line 2:

ORA-01402:viewWITHCHECKOPTIONwhere-clause violation

七、TRUNCATETABLE截断表

删除所有数据，保留表结构

TRUNCATETABLE语句不能回滚

SQL> TURNCATE TABLE emp;

八、使用默认值

显式默认值

使用DEFAULT关键字表示默认值

可以使用显示默认值，控制默认值的使用

显示默认值可以在INSERT和UPDATE语句中使用

SQL>CREATETABLE tb

2 (

3 orderid INTPRIMARYKEY,

             4      status VARCHAR2(20)DEFAULT'Delivery'NOTNULL,
5      last_update DATE DEFAULT sysdate
6  );
Table created.
--自动使用默认值
SQL>INSERTINTO tb(orderid)SELECT 10 FROM DUAL;
1 row created.
--指定新值来覆盖默认值
SQL>INSERTINTO tb SELECT 20,'No Delivery','28-MAY-10'FROM DUAL;
1 row created.
--使用default关键字来设置为默认值
SQL>SELECT*FROM tb;
ORDERID STATUS               LAST_UPDA
---------- -------------------- ---------
10 Delivery             28-JUN-10
20 No Delivery          28-MAY-10
SQL>UPDATE tb SET status =DEFAULTWHERE orderid = 20;
1 row updated.
SQL>SELECT*FROM tb;
ORDERID STATUS               LAST_UPDA
---------- -------------------- ---------
10 Delivery             28-JUN-10
20 Delivery             28-MAY-10

九、RETURNING 子句

使用RETURNING子句返回聚合函数的结果集

SQL> VARIABLE avg_salary NUMBER
SQL>UPDATE emp SET salary = salary + 100
2 RETURNING AVG(salary)INTO: avg_salary;
16 rows updated.
SQL>PRINT avg_salary;
AVG_SALARY
----------
2554.6875

十、MERGE INTO

将一个表中的行合并到另一个表中

MERGE INTO语法：

MERGE INTO table_name table_alias
USING (table view sub_query) alias
ON(join condition)
WHEN MATCHED THEN
UPDATESET
col1 = col_val1,
col2 = col2_val
WHENNOT MATCHED THEN
INSERT(column_list)
VALUES(column_values);
--创建演示环境
SQL> CONN hr/hr;
Connected.
--从hr.job_history提取唯一的数据并复制到新表job_hs中
SQL>CREATETABLE job_hs AS
2  SELECT employee_id,start_date,end_date,job_id,department_id  FROM job_history jh
3  WHERE end_date =
4      (SELECTMAX(end_date)FROM job_history WHERE employee_id = jh.employee_id);
Table created.
SQL>SELECT*FROM job_hs ORDERBY employee_id;
EMPLOYEE_ID START_DAT END_DATE  JOB_ID     DEPARTMENT_ID
----------- --------- --------- ---------- -------------
101 28-OCT-93 15-MAR-97 AC_MGR               110
102 13-JAN-93 24-JUL-98 IT_PROG               60
114 24-MAR-98 31-DEC-99 ST_CLERK              50
122 01-JAN-99 31-DEC-99 ST_CLERK              50
176 01-JAN-99 31-DEC-99 SA_MAN                80
200 01-JUL-94 31-DEC-98 AC_ACCOUNT            90
201 17-FEB-96 19-DEC-99 MK_REP                20
--将hr.employees中的记录当job_hs中存在时，则更新相关项，否则插入到job_hs表中
SQL> MERGE INTO job_hs h
2  USING employees e
3  ON(h.employee_id = e.employee_id)
4  WHEN MATCHED THEN
5  UPDATE
6      SET
7         start_date = sysdate,
8         end_date = sysdate + 100,
9         job_id = e.job_id,
10         department_id = e.department_id
11  WHENNOT MATCHED THEN
12      INSERT(h.employee_id,h.start_date,h.end_date,h.job_id,h.department_id)
13      VALUES(e.employee_id,e.hire_date,sysdate,e.job_id,e.department_id);
SQL>SELECTCOUNT(*)FROM job_hs;
COUNT(*)
----------
107

MERGE INTO使用注意事项

MERGE INTO子句应指明需要合并的目的表

USING ...ON子句用于表之间的连接

WHEN MATCHED THEN子句指明当条件满足时则对目的表执行何种操作(此处是UPDATE操作)

WHENNOT MATCHED THEN子句指明当条件不满足时对目的表执行何种操作(此处是INSERT操作)

--转自