《数据库课件：第7章数据操纵与事务处理》由会员分享，可在线阅读，更多相关《数据库课件：第7章数据操纵与事务处理（40页珍藏版）》请在金锄头文库上搜索。
1、第第7章章 数据操纵与事务处理数据操纵与事务处理主要内容主要内容l数据插入l数据修改l数据合并l数据删除l事务控制7.1 数数 据据 插插 入入l利用INSERT INTO语句插入数据l利用子查询插入数据l向案例数据库表插入初始数据l向多个表中插入数据7.1.1 利用利用INSERT INTO语句插入数据语句插入数据l语法INSERT INTO table_name|view_name (column1,column2) VALUES(value1,values, ) l注意如果在如果在INTO子句中没有指明任何列名，则子句中没有指明任何列名，则VALUES子句中列值子句中列值的个数、顺序、类
2、型必须与表中列的个数、顺序、类型相匹配。的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配。如果在如果在INTO子句中指定了列名，则子句中指定了列名，则VALUES子句中提供的列值子句中提供的列值的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对应。应。向表或视图中插入的数据必须满足表的完整性约束。向表或视图中插入的数据必须满足表的完整性约束。字符型和日期型数据在插入时要加单引号。日期类型数据需要按字符型和日期型数据在插入时要加单引号。日期类型数据需要按系统默认格式输入，或使用系统默认格式输入，或使用TO_DATE函数进行日期转
3、换。函数进行日期转换。 l向departments表中插入一行记录。INSERT INTO departmentsVALUES(departments_seq.nextval, Teaching,200,1800);l向employees表中插入一行记录。INSERT INTO employees(employee_id, last_name,email,hire_date,job_id,department_id)VALUES(employees_seq.nextval,sun,,sysdate,AC_ACCOUNT,200); 7.1.2 利用子查询插入数据利用子查询插入数据l语法INSE
4、RT INTO table_name|view_name(column1,column2,)subquery;l注意INTO子句中指定的列的个数、顺序、类型必须与子查子句中指定的列的个数、顺序、类型必须与子查询中列的个数、顺序和类型相匹配。询中列的个数、顺序和类型相匹配。 l统计各个部门的部门号、部门最高工资和最低工资，并将统计的结果写入到表dept_salary_stat（假设该表已经创建）中。 INSERT INTO emp_salary_stat SELECT department_id,max(salary), min(salary) FROM employees GROUP BY d
5、epartment_id; l向employees表中插入一个员工信息，员工号为1234，员工姓为“Liu”，email为“”，其他信息与员工号为150的员工信息相同。INSERT INTO employees SELECT 1234, first_name,Liu,,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_idFROM employees WHERE employee_id=150; l如果要将大量数据插入表中，可以利用子查询直接装载的方式进行。由于直接装载数据的操作过程不写入日志文件
6、，因此数据插入操作的速度大大提高。l利用子查询装载数据语法为：INSERT /*+APPEND*/ INTO table_name|view_name(column1,column2,)subquery;l复制employees表中employee_id，salary，job_id,department_id四列的值，并插入到backup_emp表中 ，语句为：INSERT /*+APPEND*/ INTO backup_empSELECT employee_id,salary,job_id,department_idFROM employees;7.1.3 向案例数据库表插入初始数据向案例数
7、据库表插入初始数据lINSERT INTO ehr.regions SELECT * FROM hr.regions;lINSERT INTO ehr.countries SELECT * FROM hr. countries;lINSERT INTO ehr.locations SELECT * FROM hr. locations;lINSERT INTO ehr.departments SELECT * FROM hr.departments;lINSERT INTO ehr.jobs SELECT * FROM hr.jobs;lINSERT INTO ehr.employees SE
8、LECT * FROM hr.employees;lINSERT INTO ehr.sal_grades SELECT * FROM hr.sal_grades;lINSERT INTO ehr.job_history SELECT * FROM hr.job_history;7.1.4 向多个表中插入数据向多个表中插入数据l在Oracle 11g中，可以使用INSERT语句同时向多个表中插入数据。l根据数据插入的条件不同，分为：无条件插入：将数据插入所有指定的表中无条件插入：将数据插入所有指定的表中有条件插入：将数据插入符合条件的表中。有条件插入：将数据插入符合条件的表中。l无条件多表插入的
9、基本语法为INSERT ALL INTO table1 VALUES(column1,column2,)INTO table2 VALUES(column1,column2,)subquery;l利用无条件多表插入，查询employees表中工资高于8000元的员工信息并分别插入emp_sal和emp_mgr表。INSERT ALLINTO emp_sal VALUES(employee_id,hire_date,salary)INTO emp_mgr VALUES(employee_id,manager_id,salary)SELECT employee_id,hire_date,manag
10、er_id,salaryFROM employees WHERE salary8000; l有条件多表插入语法为INSERT ALL|FIRST WHEN condition1 THEN INTO table1(column1,) WHEN condition2 THEN INTO table2(column1,)ELSE INTO tablen(column1,)subquery;l参数说明ALL：表示一条记录可以同时插入多个满足条件的表中；：表示一条记录可以同时插入多个满足条件的表中；FIRST：表示一条记录只插入第一个满足条件的表中。：表示一条记录只插入第一个满足条件的表中。l 将emp
11、loyees表中员工信息按照不同部门号分别复制到emp10，emp20，emp30和emp_other表中。同时，将工资低于5000元的员工信息复制到lowsal表中，将工资高于10000元的员工信息复制到highsal表中，将工资在500010000元之间的员工信息复制到middlesal表中。 INSERT ALLWHEN department_id=10 THEN INTO emp10WHEN department_id =20 THEN INTO emp20WHEN department_id =30 THEN INTO emp30WHEN department_id =40 THEN
12、 INTO emp_otherWHEN salary4000 THEN INTO highsalELSE INTO middlesal SELECT * FROM employees;l多表插入的应用利用多表插入技术可以实现不同数据源之间的数据转利用多表插入技术可以实现不同数据源之间的数据转换，可以将非关系数据库的一条记录转换为关系数据换，可以将非关系数据库的一条记录转换为关系数据库中的多条记录。库中的多条记录。 l将SALES_SOURCE_DATA表中的记录转换为SALES_INFO表中的记录。CREATE TABLE sale_source_data( emp_id NUMBER(6),
13、 week_id NUMBER(2), sale_MON NUMBER(8,2), sale_TUE NUMBER(8,2), sale_WED NUMBER(8,2), sale_THUR NUMBER(8,2), sale_FRI NUMBER(8,2); INSERT INTO sale_source_data VALUES(7844,1,100,200,300,400,500); lCREATE TABLE sale_info( emp_id NUMBER(6), week NUMBER(2), sale NUMBER(8,2);lINSERT ALL INTO sale_info V
14、ALUES (emp_id,week_id,sale_MON) INTO sale_info VALUES (emp_id,week_id,sale_TUE) INTO sale_info VALUES (emp_id,week_id,sale_WED) INTO sale_info VALUES (emp_id,week_id,sale_THUR) INTO sale_info VALUES (emp_id,week_id, sale_FRI) SELECT * FROM sale_source_data; 7.2数据修改数据修改l基本语法l修改单行记录l修改多行记录l带有子查询的修改l基本
15、语法UPDATE table_name|view_nameSET column1=value1,column2=value2WHERE conditionl修改单条记录UPDATE employees SET salary=salary+100,commission_pct=0.4 WHERE employee_id=100;l修改多条记录UPDATE employees SET salary=salary+150 WHERE department_id=20;l利用子查询修改记录利用子查询修改记录UPDATE employees SET salary=300+(SELECT avg(sala
16、ry) FROM employees WHERE department_id=30)WHERE department_id=50;7.3数据合并数据合并l利用MERGE语句可以同时完成数据的插入与更新操作。l将源表的数据分别与目标表中的数据根据特性条件进行比较（每次只比较一条记录），如果匹配，则利用源表中的记录更新目标表中的记录，如果不匹配，则将源表中的记录插入目标表中。l使用MERGE语句操作时，用户需要具有源表的SELECT对象权限以及目标表的INSERT，UPDATE对象权限。lMERGE语句的基本语法为：MERGE INTO schema.target_table target_ali
17、as USING schema.source_table|source_view|source_subquery source_aliasON (condition)WHEN MATCHED THEN UPDATE SET column1=expression1 ,column2=expression2 where_clauseDELETE where_clauseWHEN NOT MATCHED THEN INSERT (column2,column2) VALUES (expresstion1,expression2) where_clause;l参数说明INTO：指定进行数据更新或插入的
18、目标表；：指定进行数据更新或插入的目标表；USING：指定用于目标表数据更新或插入的源表或视图或子查询；：指定用于目标表数据更新或插入的源表或视图或子查询；ON：决定：决定MERGE语句执行更新操作还是插入操作的条件。对于语句执行更新操作还是插入操作的条件。对于目标表中满足条件的记录，则利用源表中的相应记录进行更新；目标表中满足条件的记录，则利用源表中的相应记录进行更新；而源表中不满条件的记录将被插入目标表中；而源表中不满条件的记录将被插入目标表中；where_clause：只有当该条件为真时才进行数据的更新或插：只有当该条件为真时才进行数据的更新或插入操作；入操作；DELETE where_
19、clause：当目标表中更新后的记录满足该条：当目标表中更新后的记录满足该条件时，则删除该记录。件时，则删除该记录。 l使用“雇员”表（别名e）作为数据源，在该表 的副本“副本_雇员”表（别名c）中插入并更新行。lMERGE INTO 副本_雇员 c USING 雇员elON (c.雇员标识= e.雇员标识)lWHEN MATCHED THEN UPDATElSETlc.姓氏 = e.姓氏,lc.部门标识 = e.部门标识lWHEN NOT MATCHED THEN INSERT lVALUES (e.雇员标识, e.姓氏, e.部门标识);MERGE INTO 副本_雇员 c USING 雇
20、员e ON (c.雇员标识= e.雇员标识) WHEN MATCHED THEN UPDATE SET c.姓氏 = e.姓氏, c.部门标识 = e.部门标识WHEN NOT MATCHED THEN INSERT VALUES (e.雇员标识, e.姓氏, e.部门标识); MERGE 语句执行前的“副本_雇员”雇员标识 姓氏部门标识100Smith 40103Chang 30MERGE 执行后的“副本_雇员”雇员标识 姓氏部门标识100King90103Hunold60152Davies 50雇员（来源表）雇员标识 姓氏部门标识100King90103Hunold60152Davies
21、50l现有表source_emp和target_emp，表中数据如下。利用source_emp表中的数据更新target_emp表中的数据，对target_emp表中存在的员工信息进行更新，对不存在的员工进行信息插入。 SELECT * FROM source_emp;EMPNO ENAME DEPTNO-100 JOAN 10110 SMITH 20120 TOM 30 lSELECT * FROM target_emp;lEMPNO ENAME DEPTNOl-l100 MARRY 20l20 JACK 40 lMERGE INTO target_emp tl USING source_e
22、mp sl ON (t.empno=s.empno)l WHEN MATCHED THEN UPDATE SETl t.ename=s.ename,t.deptno=s.deptnol WHEN NOT MATCHED THEN INSERTl VALUES(s.empno,s.ename,s.deptno)；lSELECT * FROM target_emp;lEMPNO ENAME DEPTNOl-l100 JOAN 10l20 JACK 40l110 SMITH 20l120 TOM 30 7.4删除数据删除数据l基本语法l删除单条记录l删除多条记录l带有子查询的删除操作l利用TRUNC
23、ATE删除数据lTRUNCATE与DELETE区别l基本语法 DELETE FROM table|view WHERE condition l删除单条记录DELETE FROM employees WHERE employee_id=152; l删除多条记录DELETE FROM employees WHERE department_id=130 ;l带有子查询的删除操作DELETE FROM employees WHERE salary(SELECT salary FROM employees WHERE employee_id=101);l利用TRUNCATE删除数据TRUNCATE TA
24、BLE table_namelTRUNCATE与DELETE区别释放存储空间释放存储空间不写入日志文件，因此执行效率较高，但该操作不可不写入日志文件，因此执行效率较高，但该操作不可回滚。回滚。 7.5事务控制事务控制l事务概念lOracle事务的隔离级别 lOracle事务处理7.5.1 事务概念事务概念l原子性（Atomicity）：事务是数据库的逻辑工作单位，事务中的所有操作要么都做，要么都不做，不存在第三种情况。l一致性（Consistency）：事务执行的结果必须是使数据库从一个一致性状态转变到另一个一致性状态，不存在中间的状态。l隔离性（Isolation）：数据库中一个事务的执行不
25、受其他事务干扰，每个事务都感觉不到还有其他事务在并发执行。l持久性（Durability）：一个事务一旦提交，则对数据库中数据的改变是永久性的，以后的操作或故障不会对事务的操作结果产生任何影响。 7.5.2 Oracle事务的隔离级别事务的隔离级别 l数据库中事务的并发运行，可能导致下列问题：丢失修改：两个事务同时读取数据库中的同一数据并丢失修改：两个事务同时读取数据库中的同一数据并进行修改，一个事务提交的结果破坏了另一个事务提进行修改，一个事务提交的结果破坏了另一个事务提交的结果，导致第一个事务对数据的修改丢失。交的结果，导致第一个事务对数据的修改丢失。读读“脏脏”数据：一个事务对数据的修改
26、在提交之前被数据：一个事务对数据的修改在提交之前被其他事务读取。其他事务读取。不可重复读：在某个事务读取一次数据后，其他事务不可重复读：在某个事务读取一次数据后，其他事务修改了这些数据并进行了提交，当该事务重新读取这修改了这些数据并进行了提交，当该事务重新读取这些数据时就会得到与前一次不一样的结果。些数据时就会得到与前一次不一样的结果。lOracle数据库事务隔离级：READ COMMITED（提交读）提交读）p这是事务的默认隔离等级，用于设置语句级的一致性。每个事务所执行的查询操作只能获取在该查询开始之前（不是该事务开始之前）已经提交的数据。该隔离级别可以防止丢失修改和脏读的问题，但不能防止
27、不可重复读的问题。在该级别的事务中可以执行DML操作（若数据被加锁则等待其他事务解锁）。SERIALIZABLE（串行化）（串行化）p用于设置事务级的一致性，每个事务只能看到在该事务开始之前已经提交的数据。该隔离级的事务可以防止丢失修改、脏读和不可重复读的问题。在该级别的事务中可以执行DML操作（若数据被加锁则不等待，返回错误）。l如果数据库中具有大量并发事务，并且应用程序的事务处理能力和响应速度是关键因素，则READ COMMITED隔离级比较合适。如果数据库中多个事务并发访问数据的概率很低，并且大部分的事务都会持续执行很长时间，这时应用程序更加适合使用SERIALIZABLE隔离级。lSE
28、T TRANSACTION ISOLATION LEVEL SERIALIZABLE;lSET TRANSACTION ISOLATION LEVEL READ COMMITTED;lALTER SESSION SET ISOLATION_LEVEL =SERIALIZABLE;lALTER SESSION SET ISOLATION_LEVEL =READ COMMITTED;l在Oracle数据库中可以设置事务为Read-Only（只读），每个事务只能看到在该事务开始之前已经提交的数据，并且不能在该事务中对数据进行INSERT，UPDATE，DELETE等操作，从而确保取得特定时间点的数据
29、信息。SET TRANSACTION READ ONLY; 7.5.3 Oracle事务处理事务处理 l事务提交方式用户显式执行用户显式执行COMMIT命令命令执行特定操作时系统自动提交。执行特定操作时系统自动提交。l说明当事务提交后，用户对数据库修改操作的日志信息由当事务提交后，用户对数据库修改操作的日志信息由日志缓冲区写入重做日志文件中，释放该事务所占据日志缓冲区写入重做日志文件中，释放该事务所占据的系统资源和数据库资源。此时，其他会话可以看到的系统资源和数据库资源。此时，其他会话可以看到该事务对数据库的修改结果。该事务对数据库的修改结果。当执行当执行CREATE，ALTER，DROP，RENAME，REVOKE，GRANT，CONNECT，DISCONNECT等命令时，系统将自动提交。等命令时，系统将自动提交。 l事务回滚方式事务全部回滚事务全部回滚 pROLLBACK事务部分回滚事务部分回滚pSAVEPOINT XpROLLBACK TO XlROLLBACK TO B;（回滚最后一个INSERT操作）lROLLBACK TO A;（回滚后面的INSERT操作和UPDATE操作）lROLLBACK;（回滚全部操作） 总结总结l数据插入l数据修改l数据合并l数据删除l事务控制
