《oracle内部培训 (7)》由会员分享，可在线阅读，更多相关《oracle内部培训 (7)（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 可以在下列条件下执行:向表中插入数据修改现存数据删除现存数据 事务是由完成若干项工作的DML语句组成的
2、。8-4Copyright Oracle Corporation, 2001. All rights reserved.插入数据DEPARTMENTS 新行向向 DEPARMENTS 表中插入 新的记录8-5Copyright Oracle Corporation, 2001. All rights reserved.INSERT 语句语法使用 INSERT 语句向表中插入数据。使用这种语法一次只能向表中插入一条数据。INSERT INTOtable (column , column.) VALUES(value , value.);8-6Copyright Oracle Corporatio
3、n, 2001. All rights reserved.插入数据为每一列添加一个新值。 按列的默认顺序列出各个列的值。 在 INSERT 子句中随意列出列名和他们的值。 字符和日期型数据应包含在单引号中。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.I
4、NSERT INTOdepartments VALUES(100, Finance, NULL, NULL); 1 row created.1 row created.INSERT INTOdepartments (department_id, department_name ) VALUES(30, Purchasing); 1 row created.1 row created.向表中插入空值隐式方式: 在列名表中省略该列的值。显示方式: 在VALUES 子句中指定空值。8-8Copyright Oracle Corporation, 2001. All rights reserved.I
5、NSERT INTO employees (employee_id, first_name, last_name, email, 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-9Copy
6、right Oracle Corporation, 2001. All rights reserved.INSERT INTO employees VALUES (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
7、.INSERT INTO departments (department_id, department_name, location_id) VALUES (创建脚本 在SQL 语句中使用4 rows created.4 rows created.从其它表中拷贝数据8-12Copyright Oracle Corporation, 2001. All rights reserved.更新数据EMPLOYEES更新 EMPLOYEES 表8-13Copyright Oracle Corporation, 2001. All rights reserved.UPDATE 语句语法使用 UPDATE
8、 语句更新数据。可以一次更新多条数据。UPDATEtable SETcolumn = value , column = value, . WHERE condition;8-14Copyright Oracle Corporation, 2001. All rights reserved.UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated.1 row updated.使用 WHERE 子句指定需要更新的数据。如果省略WHERE子句，则表中的所有数据都将被更新。更新数据UPDATE copy
9、_emp SET department_id = 110; 22 rows updated.22 rows updated.8-15Copyright Oracle Corporation, 2001. All rights reserved.UPDATE employees SET 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 r
10、ow updated.1 row updated.在UPDATE语句中使用子查询更新 114号员工的工作和工资使其与 205号员工相同。8-16Copyright Oracle Corporation, 2001. All rights reserved.UPDATE copy_emp SET department_id = (SELECT department_idFROM employeesWHERE employee_id = 100) WHERE job_id = (SELECT job_idFROM employeesWHERE employee_id = 200); 1 row u
11、pdated.1 row updated.在UPDATE语句中使用子查询在 UPDATE 中使用子查询，使更新基于另一个表中的数据。 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 employees SET department_id = 55 WHERE department_i
12、d = 110;更新中的数据完整性错误不存在 55 号部门8-18Copyright Oracle Corporation, 2001. All rights reserved.从表DEPARTMENTS 中删除一条记录。删除数据 DEPARTMENTS 8-19Copyright Oracle Corporation, 2001. All rights reserved.DELETE 语句使用 DELETE 语句从表中删除数据。DELETE FROM table WHERE condition;8-20Copyright Oracle Corporation, 2001. All right
13、s reserved.使用WHERE 子句指定删除的记录。如果省略WHERE子句，则表中的全部数据将被删除。删除数据DELETE FROM departmentsWHERE 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 employees WHERE department_id =(
14、SELECT department_idFROM departmentsWHERE department_name LIKE %Public%); 1 row deleted.1 row deleted.在 DELETE 中使用子查询在 DELETE 中使用子查询，使删除基于另一个表中的数据。 8-22Copyright Oracle Corporation, 2001. All rights reserved.删除中的数据完整性错误DELETE FROM departments WHERE department_id = 60;DELETE FROM departments* ERROR a
15、t 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.在INSERT语句中使用子查询INSERT INTO(SELECT employee_id, last_name,email, hire_date, job_id, salary, department_idFROM employeesWHERE 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 rights reserved.在INSERT语句中使用子查询
