一、常用的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操作)
--转自