08数据操作.ppt

1、MD02-Oracle9i SQL,插入数据,新插入的行包含所有列的数据时，要按照表中列的缺省顺序排列数据 也可以在INSERT子句中列出要插入数据的列的名称 将字符型数据和日期型数据放在单引号内,INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, Public Relations, 100, 1700); 1 row created.,INSERT INTO departments VALUES (100, Finance, NULL, NULL); 1 ro

2、w created.,INSERT INTO departments (department_id, department_name ) VALUES (30, Purchasing); 1 row created.,插入空值,隐含方式：在列名列表中省略相应的列名,显式方式：在VALUES子句中给出NULL关键字,INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, departm

3、ent_id) VALUES (113, Louis, Popp, LPOPP, 515.124.4567, SYSDATE, AC_ACCOUNT, 6900, NULL, 205, 100); 1 row created.,插入特殊值,SYSDATE函数记录了当前的日期,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.,插入

4、指定的日期值,插入一个新员工数据： 验证插入的数据,INSERT INTO departments (department_id, department_name, location_id) VALUES (,创建脚本,使用 4 rows created.,从其它表中复制数据行,修改数据,EMPLOYEES,修改EMPLOYEES表中的数据,UPDATE语句,使用UPDATE修改表中的数据 一次可以修改一行，多行或整个表的数据,UPDATE table SET column = value , column = value, . WHERE condition;,使用WHERE子句修改指定的一

5、行或多行 如果不使用WHERE子句将修改表中所有的行,UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated.,修改数据,UPDATE copy_emp SET department_id = 110; 22 rows updated.,UPDATE employees SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE

6、 employee_id = 205) WHERE employee_id = 114; 1 row updated.,使用子查询修改两列数据,将114号员工的job和salary修改成与205号员工的相同,UPDATE copy_emp SET 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.,基于其它表修改数据,在U

7、PDATE语句中使用子查询 ，可以从其它表中选取数据来修改目标表中的数据,UPDATE employees * ERROR at line 1: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found,UPDATE employees SET department_id = 55 WHERE department_id = 110;,修改数据：完整性,55号部门不存在,从表DEPARTMENTS中删除数据,删除数据,DEPARTMENTS,DELETE语句,使用DELETE语句从表中删除数

8、据,DELETE FROM table WHERE condition;,使用WHERE子句删除指定的行 不使用WHERE子句将删除表中所有的行,删除数据,DELETE FROM departments WHERE department_name = Finance; 1 row deleted.,DELETE FROM copy_emp; 22 rows deleted.,DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE %

9、Public%); 1 row deleted.,基于其它表删除数据,在DELETE语句中使用子查询 ，可以从其它表中选取数据来删除目标表中的数据,删除数据：完整性,DELETE FROM departments WHERE department_id = 60;,DELETE FROM departments * ERROR at line 1: ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found,还有子记录存在，其主记录不能被删除,在INSERT语句中使用子查询,INSERT INTO

10、 (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.,在INSERT语句中使用子查询,SELECT employee_id, last_name, email, hire_date, job_id, salary

11、, department_id FROM employees WHERE department_id = 50;,在DML语句中使用WITH CHECK OPTION关键字,使用子查询来指定表名和列名 使用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

12、-JUN-99, DD-MON-RR), ST_CLERK, 5000); INSERT INTO * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation,列的缺省值,当需要设置列的值为其缺省值时，可以使用DEFAULT关键字 这一特性使用户可以灵活控制何时何地需要使用列的缺省值 这一特性可以使用在INSERT语句和UPDATE语句中,在INSERT语句中使用DEFAULT 在UPDATE语句中使用DEFAULT,使用缺省值,INSERT INTO departments(department_id

13、, department_name, manager_id) VALUES (300, Engineering, DEFAULT);,UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10;,MERGE语句,提供一种有条件的修改或插入数据的能力 如果数据存在则进行UPDATE操作，如果数据不存在则进行INSERT操作 避免单独的update语句 提高性能，简化使用 尤其适合于数据仓库的应用,MERGE语句,使用MERGE语句根据条件向表中插入数据或修改表中的数据,MERGE INTO table_name tab

14、le_alias USING (table|view|sub_query) alias ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col_val2 WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);,MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.f

15、irst_name = e.first_name, . c.department_id = e.department_id WHEN 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.manager_id, e.department_id);,合并行,根据表EMPLOYEES中的数据插入或修改表COPY_EMP中的数据,合并行,MERGE INTO cop

16、y_emp c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET . WHEN NOT MATCHED THEN INSERT VALUES.;,SELECT * FROM COPY_EMP; no rows selected,SELECT * FROM COPY_EMP; 20 rows selected.,事务,数据库事务是指： 一组包含一个数据一致性改变的DML语句 一个DDL语句 一个DCL语句,事务,始于第一个DML语句 结束于以下任意一个事件： COMMIT 或 ROL

17、LBACK语句 DDL 或 DCL语句 (自动提交) 用户退出SQL*Plus 系统瘫痪(非正常事务),COMMIT和ROLLBACK语句,使用COMMIT和ROLLBACK语句，你可以做以下事情： 确保数据一致性 将逻辑相关的操作分组,事务控制,ROLLBACK to SAVEPOINT B,ROLLBACK to SAVEPOINT A,ROLLBACK,UPDATE. SAVEPOINT update_done; Savepoint created. INSERT. ROLLBACK TO update_done; Rollback complete.,回退事务到保存点,用SAVEPOI

18、NT语句在当前事务中定义保存点 用ROLLBACK TO SAVEPOINT语句回退事务到相应的保存点,在以下情况下，系统会自动进行提交： 发出一个DDL语句 发出一个DCL语句 正常退出SQL*Plus，即使没有显式的进行COMMIT或ROLLBACK操作 在非正常退出SQL*Plus或系统瘫痪时，系统自动进行回退处理,隐式事务处理,COMMIT或ROLLBACK前的数据状态,数据的前映像可以被恢复 当前用户可以用SELECT 语句查看所发出的DML语句对数据的改变结果 其他用户不能看到当前DML对数据的改变情况 所涉及到的数据行被锁定，其他用户不能修改这些数据行,COMMIT后的数据状态,

19、数据的改变被永久确定下来 数据的前映像丢失，不能被恢复 数据库中的所有用户都能看到数据的改变情况 数据行上的锁被释放，其他用户也可以处理这些行 所有的保存点都被释放,COMMIT; Commit complete.,修改数据 提交数据改变,DELETE FROM employees WHERE employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, Corporate Tax, NULL, 1700); 1 row inserted.,提交数据,ROLLBACK后的数据状态,使用ROLLBACK放弃对数据的所有修改，使数据恢复到修改以前的值 数据修改没有完成 数据恢复到修改以前的值 加在数据上的锁被释放,DELETE FROM copy_emp; 22 rows deleted. ROLLBACK; Rollback complete.,语句级回退,当一个单一的DML语句失败时，只有这一个语句的执行被回退 所有其它的数据