--=================================================
--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)
