《Les08处理数据课件》由会员分享，可在线阅读，更多相关《Les08处理数据课件（53页珍藏版）》请在金锄头文库上搜索。
1、8Copyright Oracle Corporation, 2001. All rights reserved.处理数据处理数据8-2Copyright Oracle Corporation, 2001. All rights reserved.目标目标通过本章学习，您将可以通过本章学习，您将可以:使用使用 DML 语句语句向表中插入数据向表中插入数据更新表中数据更新表中数据从表中删除数据从表中删除数据将表中数据和并将表中数据和并控制事务控制事务8-3Copyright Oracle Corporation, 2001. All rights reserved.数据控制语言数据控制语言DML
2、 可以在下列条件下执行可以在下列条件下执行:向表中插入数据向表中插入数据修改现存数据修改现存数据删除现存数据删除现存数据事务是由完成若干项工作的事务是由完成若干项工作的DML语句组成的。语句组成的。8-4Copyright Oracle Corporation, 2001. All rights reserved.插入数据插入数据DEPARTMENTS 新行新行向向向向 DEPARMENTS 表中插入表中插入新的记录新的记录8-5Copyright Oracle Corporation, 2001. All rights reserved.INSERT 语句语法语句语法使用使用 INSERT
3、语句向表中插入数据。语句向表中插入数据。使用这种语法一次只能向表中插入一条数据。使用这种语法一次只能向表中插入一条数据。INSERT INTOtable (column , column.)VALUES(value , value.);8-6Copyright Oracle Corporation, 2001. All rights reserved.插入数据插入数据为每一列添加一个新值。为每一列添加一个新值。按列的默认顺序列出各个列的值。按列的默认顺序列出各个列的值。 在在 INSERT 子句中随意列出列名和他们的值。子句中随意列出列名和他们的值。字符和日期型数据应包含在单引号中。字符和日期
4、型数据应包含在单引号中。INSERT INTO departments(department_id, department_name, manager_id, location_id)VALUES (70, Public Relations, 100, 1700);1 row created.8-7Copyright Oracle Corporation, 2001. All rights reserved.INSERT INTOdepartmentsVALUES(100, Finance, NULL, NULL);1 row created.1 row created.INSERT INTO
5、departments (department_id, department_name )VALUES(30, Purchasing);1 row created.1 row created.向表中插入空值向表中插入空值隐式方式隐式方式: 在列名表中省略该列的值。在列名表中省略该列的值。显示方式显示方式: 在在VALUES 子句中指定空值。子句中指定空值。8-8Copyright Oracle Corporation, 2001. All rights reserved.INSERT INTO employees (employee_id, first_name, last_name, ema
6、il, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)VALUES (113, Louis, Popp, LPOPP, 515.124.4567, SYSDATE, AC_ACCOUNT, 6900, NULL, 205, 100);1 row created.1 row created.插入指定的值插入指定的值SYSDATE 记录当前系统的日期和时间。记录当前系统的日期和时间。8-9Copyright Oracle Corporation, 2001. All rights
7、 reserved.INSERT INTO employeesVALUES (114, Den, Raphealy, DRAPHEAL, 515.127.4561, TO_DATE(FEB 3, 1999, MON DD, YYYY), AC_ACCOUNT, 11000, NULL, 100, 30);1 row created.1 row created.插入指定的值插入指定的值加入新员工加入新员工检查插入的数据检查插入的数据8-10Copyright Oracle Corporation, 2001. All rights reserved.INSERT INTO departments
8、 (department_id, department_name, location_id)VALUES (&department_id, &department_name,&location);创建脚本创建脚本 在在SQL 语句中使用语句中使用& 变量指定列值。变量指定列值。& 变量放在变量放在VALUES子句中。子句中。1 1 row created.row created.8-11Copyright Oracle Corporation, 2001. All rights reserved.在在 INSERT 语句中加入子查询。语句中加入子查询。 不必书写不必书写 VALUES 子句。子
9、句。 子查询中的值列表应于子查询中的值列表应于 INSERT 子句中的列名对应。子句中的列名对应。INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE %REP%;4 rows created.4 rows created.从其它表中拷贝数据从其它表中拷贝数据8-12Copyright Oracle Corporation, 2001. All rights re
10、served.更新数据更新数据EMPLOYEES更新更新 EMPLOYEES 表表8-13Copyright Oracle Corporation, 2001. All rights reserved.UPDATE 语句语法语句语法使用使用 UPDATE 语句更新数据。语句更新数据。可以一次更新多条数据。可以一次更新多条数据。UPDATEtableSETcolumn = value , column = value, .WHERE condition;8-14Copyright Oracle Corporation, 2001. All rights reserved.UPDATE emplo
11、yeesSET department_id = 70WHERE employee_id = 113;1 row updated.1 row updated.使用使用 WHERE 子句指定需要更新的数据。子句指定需要更新的数据。如果省略如果省略WHERE子句，则表中的所有数据都将被更新。子句，则表中的所有数据都将被更新。更新数据更新数据UPDATE copy_empSET department_id = 110;22 rows updated.22 rows updated.8-15Copyright Oracle Corporation, 2001. All rights reserved.U
12、PDATE employeesSET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114;1 row updated.1 row updated.在在UPDATE语句中使用子查询语句中使用子查询更新更新 114号员工的工作和工资使其与号员工的工作和工资使其与 205号员工相同。号员工相同。8-16Copyright Oracle Corpora
13、tion, 2001. All rights reserved.UPDATE copy_empSET department_id = (SELECT department_id FROM employees WHERE employee_id = 100)WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200);1 row updated.1 row updated.在在UPDATE语句中使用子查询语句中使用子查询在在 UPDATE 中使用子查询，使更新基于另一个表中的数据。中使用子查询，使更新基于另一个表中的数
14、据。 8-17Copyright Oracle Corporation, 2001. All rights reserved.UPDATE employees *ERROR at line 1:ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not foundUPDATE employeesSET department_id = 55WHERE department_id = 110;更新中的数据完整性错误更新中的数据完整性错误不存在不存在 55 号部门号部门8-18Copyright Oracle
15、Corporation, 2001. All rights reserved.从表从表DEPARTMENTS 中删除一条记录。中删除一条记录。删除数据删除数据 DEPARTMENTS 8-19Copyright Oracle Corporation, 2001. All rights reserved.DELETE 语句语句使用使用 DELETE 语句从表中删除数据。语句从表中删除数据。DELETE FROM tableWHERE condition;8-20Copyright Oracle Corporation, 2001. All rights reserved.使用使用WHERE 子句
16、指定删除的记录。子句指定删除的记录。如果省略如果省略WHERE子句，则表中的全部数据将被删除。子句，则表中的全部数据将被删除。删除数据删除数据 DELETE FROM departments WHERE department_name = Finance;1 row deleted.1 row deleted.DELETE FROM copy_emp;22 rows deleted.22 rows deleted.8-21Copyright Oracle Corporation, 2001. All rights reserved.DELETE FROM employeesWHERE depa
17、rtment_id = (SELECT department_id FROM departments WHERE department_name LIKE %Public%);1 row deleted.1 row deleted.在在 DELETE 中使用子查询中使用子查询在在 DELETE 中使用子查询，使删除基于另一个表中的数据。中使用子查询，使删除基于另一个表中的数据。 8-22Copyright Oracle Corporation, 2001. All rights reserved.删除中的数据完整性错误删除中的数据完整性错误DELETE FROM departmentsWHER
18、E department_id = 60;DELETE FROM departments *ERROR at line 1:ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record foundYou cannot delete a row that contains a primary key that is used as a foreign key in another table.8-23Copyright Oracle Corporation, 2001. All rights reserved.在
19、在INSERT语句中使用子查询语句中使用子查询INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50) VALUES (99999, Taylor, DTAYLOR, TO_DATE(07-JUN-99, DD-MON-RR), ST_CLERK, 5000, 50);1 row created.8-24Copyright Oracle Corporation, 2001. All rig
20、hts reserved.在在INSERT语句中使用子查询语句中使用子查询Verify the resultsSELECT employee_id, last_name, email, hire_date, job_id, salary, department_idFROM employeesWHERE department_id = 50;8-25Copyright Oracle Corporation, 2001. All rights reserved.在在DML语句中使用语句中使用 WITH CHECK OPTION 关关键字键字使用子查询表示使用子查询表示 DML 语句中使用的表语句
21、中使用的表WITH CHECK OPTION 关键字避免修改子查询范围外关键字避免修改子查询范围外的数据的数据INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id = 50 WITH CHECK OPTION)VALUES (99998, Smith, JSMITH, TO_DATE(07-JUN-99, DD-MON-RR), ST_CLERK, 5000);INSERT INTO *ERROR at line 1:ORA-
22、01402: view WITH CHECK OPTION where-clause violation8-26Copyright Oracle Corporation, 2001. All rights reserved.显式默认值显式默认值使用使用 DEFAULT 关键字表示默认值关键字表示默认值可以使用显示默认值控制默认值的使用可以使用显示默认值控制默认值的使用显示默认值可以在显示默认值可以在 INSERT 和和 UPDATE 语句中使用语句中使用8-27Copyright Oracle Corporation, 2001. All rights reserved.显示使用默认值显示使用
23、默认值INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, Engineering, DEFAULT);UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10;在插入操作中使用默认值在插入操作中使用默认值:在更新操作中使用默认值在更新操作中使用默认值:8-28Copyright Oracle Corporation, 2001. All rights reserved.合并语句合并语句按照指定的条件执行插
24、入或更新操作按照指定的条件执行插入或更新操作如果满足条件的行存在，执行更新操作；否则执行插入如果满足条件的行存在，执行更新操作；否则执行插入操作操作:避免多次重复执行插入和删除操作避免多次重复执行插入和删除操作提高效率而且使用方便提高效率而且使用方便在数据仓库应用中经常使用在数据仓库应用中经常使用8-29Copyright Oracle Corporation, 2001. All rights reserved.合并语句的语法合并语句的语法可以使用可以使用merge语句，根据指定的条件进行插入或更新操作语句，根据指定的条件进行插入或更新操作MERGE INTO table_name tabl
25、e_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);8-30Copyright Oracle Corporation, 2001. All rights reserved.MERGE INTO copy_emp c USING employees e ON (c.emp
26、loyee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, . c.department_id = e.department_idWHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, mission_pct, e.mana
27、ger_id, e.department_id);合并语句举例合并语句举例在对表在对表COPY_EMP使用使用merge语句，根据指定的条件从表语句，根据指定的条件从表 EMPLOYEES中插入或更新数据。中插入或更新数据。8-31Copyright Oracle Corporation, 2001. All rights reserved.合并语句举例合并语句举例MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET .WHEN NOT MAT
28、CHED THEN INSERT VALUES.;SELECT * FROM COPY_EMP;no rows selectedSELECT * FROM COPY_EMP;20 rows selected.8-32Copyright Oracle Corporation, 2001. All rights reserved.数据库事务数据库事务数据库事务由以下的部分组成数据库事务由以下的部分组成:一个或多个一个或多个DML 语句语句一个一个 DDL 语句语句一个一个 DCL 语句语句8-33Copyright Oracle Corporation, 2001. All rights rese
29、rved.数据库事务数据库事务以第一个以第一个 DML 语句的执行作为开始语句的执行作为开始以下面的其中之一作为结束以下面的其中之一作为结束:COMMIT 或或 ROLLBACK 语句语句DDL 或或 DCL 语句（自动提交）语句（自动提交）用户会话正常结束用户会话正常结束系统异常终了系统异常终了8-34Copyright Oracle Corporation, 2001. All rights reserved.COMMIT和和ROLLBACK语句的优点语句的优点使用使用COMMIT 和和 ROLLBACK语句语句,我们可以我们可以: 确保数据完整性。确保数据完整性。数据改变被提交之前预览。
30、数据改变被提交之前预览。将逻辑上相关的操作分组。将逻辑上相关的操作分组。8-35Copyright Oracle Corporation, 2001. All rights reserved.控制事务控制事务保存点保存点 B保存点保存点 ADELETEINSERTUPDATEINSERTCOMMITTime 事务事务ROLLBACK to SAVEPOINT BROLLBACK to SAVEPOINT AROLLBACK8-36Copyright Oracle Corporation, 2001. All rights reserved.UPDATE.SAVEPOINT update_don
31、e;Savepoint created.Savepoint created.INSERT.ROLLBACK TO update_done;Rollback complete.Rollback complete.回滚到保留点回滚到保留点使用使用 SAVEPOINT 语句在当前事务中创建保存点。语句在当前事务中创建保存点。使用使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保语句回滚到创建的保存点。存点。8-37Copyright Oracle Corporation, 2001. All rights reserved.自动提交在以下情况中执行自动提交在以下情况中执行:DDL
32、语句。语句。DCL 语句。语句。不使用不使用 COMMIT 或或 ROLLBACK 语句提交或回滚，正常结语句提交或回滚，正常结束会话。束会话。会话异常结束或系统异常会导致自动回滚。会话异常结束或系统异常会导致自动回滚。事务进程事务进程8-38Copyright Oracle Corporation, 2001. All rights reserved.提交或回滚前的数据状态提交或回滚前的数据状态改变前的数据状态是可以恢复的改变前的数据状态是可以恢复的执行执行 DML 操作的用户可以通过操作的用户可以通过 SELECT 语句查询之前的修正语句查询之前的修正其他用户不能看到当前用户所做的改变，直
33、到当前用户结束事其他用户不能看到当前用户所做的改变，直到当前用户结束事务。务。DML语句所涉及到的行被锁定，语句所涉及到的行被锁定， 其他用户不能操作。其他用户不能操作。8-39Copyright Oracle Corporation, 2001. All rights reserved.提交后的数据状态提交后的数据状态数据的改变已经被保存到数据库中。数据的改变已经被保存到数据库中。改变前的数据已经丢失。改变前的数据已经丢失。所有用户可以看到结果。所有用户可以看到结果。锁被释放，锁被释放， 其他用户可以操作涉及到的数据。其他用户可以操作涉及到的数据。所有保存点被释放。所有保存点被释放。8-40
34、Copyright Oracle Corporation, 2001. All rights reserved.COMMIT;Commit complete.Commit complete.改变数据改变数据提交改变提交改变DELETE FROM employeesWHERE employee_id = 99999;WHERE employee_id = 99999;1 row deleted.1 row deleted.INSERT INTO departments VALUES (290, Corporate Tax, NULL, 1700);1 row inserted.1 row ins
35、erted.提交数据提交数据8-41Copyright Oracle Corporation, 2001. All rights reserved.数据回滚后的状态数据回滚后的状态使用使用 ROLLBACK 语句可使数据变化失效语句可使数据变化失效:数据改变被取消。数据改变被取消。修改前的数据状态可以被恢复。修改前的数据状态可以被恢复。锁被释放。锁被释放。DELETE FROM copy_emp;22 rows deleted.22 rows deleted.ROLLBACK;Rollback complete.Rollback complete.8-42Copyright Oracle Co
36、rporation, 2001. All rights reserved.语句级回滚语句级回滚单独单独 DML 语句执行失败时，只有该语句被回滚。语句执行失败时，只有该语句被回滚。Oracle 服务器自动创建一个隐式的保留点。服务器自动创建一个隐式的保留点。其他数据改变仍被保留。其他数据改变仍被保留。用户应执行用户应执行 COMMIT 或或 ROLLBACK 语句结束事务。语句结束事务。8-43Copyright Oracle Corporation, 2001. All rights reserved.读一致性读一致性读一致性为数据提供一个一致的视图。读一致性为数据提供一个一致的视图。一个用
37、户的对数据的改变不会影响其他用户的改变。一个用户的对数据的改变不会影响其他用户的改变。对于相同的数据读一致性保证对于相同的数据读一致性保证:查询不等待修改。查询不等待修改。修改不等待查询。修改不等待查询。8-44Copyright Oracle Corporation, 2001. All rights reserved.SELECT *FROM userA.employees;读一致性读一致性UPDATE employeesSET salary = 7000WHERE last_name = Goyal;数据块数据块回滚段回滚段改变和未改变改变和未改变的数据的数据改变之前的改变之前的数据数据
38、User AUser B 快照快照8-45Copyright Oracle Corporation, 2001. All rights reserved.锁锁Oracle 数据库中，锁是数据库中，锁是 :并行事务中避免资源竞争。并行事务中避免资源竞争。避免用户动作。避免用户动作。自动使用最低级别的限制。自动使用最低级别的限制。在事务结束结束前存在。在事务结束结束前存在。两种类型两种类型: 显示和隐式。显示和隐式。8-46Copyright Oracle Corporation, 2001. All rights reserved.锁锁两种模式两种模式:独占锁独占锁: 屏蔽其他用户。屏蔽其他用户
39、。共享锁共享锁: 允许其他用户操作。允许其他用户操作。高级别的数据并发性高级别的数据并发性:DML: 表共享，行独占表共享，行独占Queries: 不需要加锁不需要加锁DDL: 保护对象定义保护对象定义提交或回滚后锁被释放。提交或回滚后锁被释放。8-47Copyright Oracle Corporation, 2001. All rights reserved.总结总结功能功能插入插入修正修正删除删除合并合并提交提交保存点保存点回滚回滚语句语句INSERTUPDATEDELETEMERGECOMMITSAVEPOINTROLLBACK通过本章学习通过本章学习, 您应学会如何使用您应学会如何使用DML语句改变数据和事务语句改变数据和事务控制控制
