--=================================================
--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 操作 )
十一、更多
SQL 基础 --> ROLLUP 与 CUBE 运算符实现数据汇总
SQL 基础 --> 层次化查询 (START BY ... CONNECT BY PRIOR)
