SQL基础--> 数据处理(DML、RETURNING、MERGE INTO)

--=================================================

--SQL基础--> 数据处理(DML、RETURNING、MERGE INTO)

--=================================================

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

向表中插入数据(INSERT)

更新表中数据(UPDATE)

从表中删除数据(DELETE)

将表中数据和并(MERGE)

控制事务(TRANSACTION)

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

向表中插入数据

修改现存数据

删除现存数据

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

三、插入数据

INSERT 语句语法:

INSERT INTO table [(column [, column...])]

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

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

为每一列添加一个新值。

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

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

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

--查看emp表的表结构

SQL> DESC emp;

Name Null? Type

----------------------------------------------------- -------- -----------------------

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(30)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SALARY NUMBER(8,2)

DEPTNO NUMBER(2)

1.向表中插入空值

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

SQL> INSERT INTO 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> INSERT INTO emp VALUES(100,'Jack','manager',null,null,20000,10);

1 row created.

2.插入指定的值

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

SQL> INSERT into 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> INSERT INTO emp

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

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

--单引号的使用

SQL> INSERT INTO emp VALUES

2 (1238,'Dan','Malley''K','',sysdate,2900,20);

1 row created.

--双引号的使用

SQL> INSERT INTO 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> CREATE TABLE test AS SELECT * FROM scott.emp WHERE 0 = 1;

Table created.

--使用select 子句插入新值

SQL> INSERT INTO test SELECT * FROM scott.emp;

21 rows created.

--使用select 子句插入部分列值

SQL> INSERT INTO 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> INSERT INTO test(empno,ename,job,salary)

2 VALUES(&empno,'&ename','&job',&salary);

Enter value for empno: 1240

Enter value for ename: Andy

Enter value for job: Singer

Enter value for salary: 3600

old 2: VALUES(&empno,'&ename','&job',&salary)

new 2: VALUES(1240,'Andy','Singer',3600)

1 row created.

四、更新数据(UPDATE)

语法：

UPDATE table

SET column = 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> DELETE FROM emp WHERE empno = 1234;

1 row deleted.

2.在DELETE 中使用子查询

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

SQL> DELETE FROM emp

2 WHERE deptno =

3 (SELECT deptno FROM scott.dept WHERE dname = 'ACCOUNTING');

4 rows deleted.

六、在DML语句中使用WITH CHECK OPTION

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

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

--deptno列不在select列表中，故不能被更新

SQL> INSERT INTO

2 (SELECT empno,ename,job,mgr,salary FROM emp

3 WHERE deptno = 20 WITH CHECK OPTION)

4 VALUES(1250,'Smith','Clerk',7902,3000);

(SELECT empno,ename,job,mgr,salary FROM emp

*

ERROR at line 2:

ORA-01402: view WITH CHECK OPTION where-clause violation

七、TRUNCATE TABLE 截断表

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

TRUNCATE TABLE语句不能回滚

SQL> TURNCATE TABLE emp;

八、使用默认值

显式默认值

使用DEFAULT 关键字表示默认值

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

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

SQL> CREATE TABLE tb

2 (

3 orderid INT PRIMARY KEY,

4 status VARCHAR2(20) DEFAULT 'Delivery' NOT NULL,

5 last_update DATE DEFAULT sysdate

6 );

Table created.

--自动使用默认值

SQL> INSERT INTO tb(orderid) SELECT 10 FROM DUAL;

1 row created.

--指定新值来覆盖默认值

SQL> INSERT INTO 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 = DEFAULT WHERE 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

UPDATE SET

col1 = col_val1,

col2 = col2_val

WHEN NOT MATCHED THEN

INSERT (column_list)

VALUES (column_values);

--创建演示环境

SQL> CONN hr/hr;

Connected.

--从hr.job_history提取唯一的数据并复制到新表job_hs中

SQL> CREATE TABLE job_hs AS

2 SELECT employee_id,start_date,end_date,job_id,department_id FROM job_history jh

3 WHERE end_date =

4 (SELECT MAX(end_date) FROM job_history WHERE employee_id = jh.employee_id);

Table created.

SQL> SELECT * FROM job_hs ORDER BY 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 WHEN NOT 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> SELECT COUNT(*) FROM job_hs;

COUNT(*)

----------

107

MERGE INTO 使用注意事项

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

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

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

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

十一、更多

Oracle 用户、对象权限、系统权限

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

SQL 基础--> 视图(CREATE VIEW)

Oracle 常用目录结构(10g)