SQL基础-修改数据

第四节 SQL基础-修改数据通过本节学习，需要掌握：在表格中增加数据修改表格数据删除表格数据提交数据变更和回退操作 2021年5月20日19时46分1厦门大学计算机系工程硕士教材 数据修改语言(DML) DML (Data Manipulation Language)是SQL的核心部分，用于在表格中插入数据更新表格数据删除表格数据一组DML语句构成了一个逻辑整体，称为事务(transaction)。为了保证数据的有效性，当事务中任意一个语句执行失败，都应当停止其他语句的执行。 2021年5月20日19时46分2厦门大学计算机系工程硕士教材 第四节 SQL基础-修改数据在表格中增加数据修改表格数据删除表格数据提交数据变更和回退操作 2021年5月20日19时46分3厦门大学计算机系工程硕士教材 在表中增加一行记录 2021年5月20日19时46分4 50 DEVELOPMENT DETROITDEPTNO DNAME LOC10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON新增行DEPT DEPTNO DNAME LOC10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON50 DEVELOPMENT DETROITDEPT在DEPT表中新增一行记录 厦门大学计算机系工程硕士教材 INSERT语句可以在表中增加记录。INSERT INTO table (column , column ) VALUES (value , value );上述语法中，一次只能增加一行记录。如果省略column，必须按照表格里字段的顺序构造value。否则按column的顺序。建议INSERT语句中写column部分。 字符型或者日期型的值必须带单引号。2021年5月20日19时46分5厦门大学计算机系工程硕士教材 INSERT INTO dept(deptno, dname, loc)VALUES (50, DEVELOPMENT , DETROIT );1 row created.可以先使用DESCRIBE语句查看表结构DESC dept 2021年5月20日19时46分6Name Null? TypeDEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)厦门大学计算机系工程硕士教材 INSERT语句中的NULL值隐式方法：省略赋NULL值的字段INSERT INTO dept (deptno, dname)VALUES (60, MIS );1 row created.显示方法：指定字段的值为NULL。如果字段类型是字符型或者日期型，也可以指定为 (空串)。INSERT INTO deptVALUES (70, FINANCE , NULL);1 row created. 2021年5月20日19时46分7厦门大学计算机系工程硕士教材 INSERT语句中的NULL值(2) NOT NULL的字段不可以赋NULL值 INSERT语句中如果存在column部分，未被枚举的字段在新生成的行中的值都为NULLINSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)VALUES (7196, GREEN , SALESMAN , 7782, SYSDATE, 2000, NULL, 10); SYSDATE表示当前日期和时间，USER表示当前登录数据库的用户名。2021年5月20日19时46分8厦门大学计算机系工程硕士教材 INSERT语句增加一个员工INSERT INTO empVALUES (2296, AROMANO , SALESMAN , 7782, TO_DATE( FEB 3, 08 , MON DD, YY ), 1300, NULL, 10);验证SELECT *FROM empWHERE empno = 2296; 2021年5月20日19时46分9 缺省时间是0点 厦门大学计算机系工程硕士教材 INSERT语句的替换变元(1)INSERT INTO dept (deptno, dname, loc)VALUES (Enter value for department_id: 80Enter value for department_name: EDUCATIONEnter value for location: ATLANTA1 row created. 2021年5月20日19时46分10厦门大学计算机系工程硕士教材 INSERT语句的替换变元(2)ACCEPT department_id PROMPT Please enter the -department number: ACCEPT department_name PROMPT Please enter the - department name: ACCEPT location PROMPT Please enter the location: INSERT INTO dept (deptno, dname, loc)VALUES (Please enter the department number: 90Please enter the department name: PAYROLLPlease enter the location: HOUSTON1 row created. 2021年5月20日19时46分11一条SQL*Plus指令换行的时候要用-连接起来厦门大学计算机系工程硕士教材 INSERT语句使用已有表格数据INSERT INTO table column (, column) subquery;不需要使用value子句 SELECT子查询中字段的数量和顺序需要和INSERT子句中的相同。字段名称不一定相同。 2021年5月20日19时46分12厦门大学计算机系工程硕士教材 INSERT INTO managers(id, name, salary, hiredate)SELECT empno, ename, sal, hiredateFROMempWHERE job = MANAGER ;3 rows created. 2021年5月20日19时46分13厦门大学计算机系工程硕士教材 第四节 SQL基础-修改数据在表格中增加数据修改表格数据删除表格数据提交数据变更和回退操作 2021年5月20日19时46分14厦门大学计算机系工程硕士教材 修改表数据 2021年5月20日19时46分15 EMPNO ENAME JOB DEPTNO7839 KING PRESIDENT 107698 BLAKE MANAGER 307782 CLARK MANAGER 107566 JONES MANAGER 20EMPEMP EMPNO ENAME JOB DEPTNO7839 KING PRESIDENT 107698 BLAKE MANAGER 307782 CLARK MANAGER 207566 JONES MANAGER 20更新EMP表中的一行记录 厦门大学计算机系工程硕士教材 UPDATE语句可以在表中修改已有的记录。UPDATE tableSET column = value , column = valueWHERE condition;上述语法中，一次可以更新多行记录。 通常情况下，使用主键(pk)来确定要更新的记录，除非你确定不会意外地更新一些不需要更新的记录。比如，如果使用员工名字来确定记录就是比较不可靠的，因为可能存在重名。2021年5月20日19时46分16厦门大学计算机系工程硕士教材 使用WHERE子句的时候，表格中由condition确定的记录被更新。UPDATE empSET deptno = 20WHERE empno = 7782; WHERE子句被省略的时候，表格中的所有记录都被更新。UPDATE empSET deptno = 20; 2021年5月20日19时46分17厦门大学计算机系工程硕士教材 UPDATE语句使用已有表格数据UPDATE tableSET (column, column, ) =(SELECT column, column, FROMtableWHERE condition)WHERE condition; 2021年5月20日19时46分18厦门大学计算机系工程硕士教材 修改7698号员工的职业和部门，使得与7499号员工一致。UPDATE empSET (job, deptno) = (SELECT job, deptnoFROMempWHERE empno = 7499)WHERE empno = 7698; 2021年5月20日19时46分19厦门大学计算机系工程硕士教材 假设employee表和emp表具有相同的数据把所有和7788号员工相同职业的员工的部门号设置与7788号相同。UPDATE employeeSET deptno = (SELECT deptnoFROM empWHERE empno = 7788)WHERE job = (SELECT jobFROM empWHERE empno = 7788);2 rows updated. 2021年5月20日19时46分20厦门大学计算机系工程硕士教材 UPDATE语句引发的错误如果更新的字段涉及到完整性约束，UPDATE将可能执行失败。UPDATE empSET deptno = 55WHERE deptno = 10;UPDATE emp *ERROR at line 1:ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK) violated parent key not found DEPT表中的deptno字段在EMP表中被当作外键使用。deptno=55不存在于DEPT表中，故在EMP表中不能随意更新。2021年5月20日19时46分21厦门大学计算机系工程硕士教材 第四节 SQL基础-修改数据在表格中增加数据修改表格数据删除表格数据提交数据变更和回退操作 2021年5月20日19时46分22厦门大学计算机系工程硕士教材 删除表数据 2021年5月20日19时46分23 DEPTDEPT在DEPT表中删除一行记录DEPTNO DNAME LOC10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON50 DEVELOPMENT DETROIT60 MIS DEPTNO DNAME LOC10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON60 MIS 厦门大学计算机系工程硕士教材 DELETE语句可以在表中删除已有的记录。DELETE FROM tableWHERE condition;上述语法中，一次可以删除多行记录。类似UPDATE语句，使用主键(pk)来确定要删除的记录，除非你确定不会意外地删除一些不需要删除的记录。 2021年5月20日19时46分24厦门大学计算机系工程硕士教材 使用WHERE子句的时候，表格中由condition确定的记录被删除。DELETE FROM deptWHERE dname = DEVELOPMENT ; WHERE子句被省略的时候，表格中的所有记录都被删除。当需要删除一张大表的所有数据时，不建议用这种方法。合适的方法后面的章节会介绍。DELETE FROM dept; 2021年5月20日19时46分25厦门大学计算机系工程硕士教材 DELETE语句使用已有表格数据DELETE tableWHERE (column, column, ) =(SELECT column, column, FROMtableWHERE condition); 2021年5月20日19时46分26厦门大学计算机系工程硕士教材 删除所有SALES部门的员工记录。DELETE FROM empWHERE deptno = (SELECT deptnoFROMdeptWHERE dname = SALES ); 2021年5月20日19时46分27厦门大学计算机系工程硕士教材 DELETE语句引发的错误如果删除的字段涉及到完整性约束，DELETE将可能执行失败。DELETE FROM deptWHERE deptno = 10;DELETE FROM dept *ERROR at line 1:ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK) violated child record found DEPT表中的deptno字段在EMP表中被当作外键使用。 EMP表中存在deptno=10的记录，故在DEPT表中不能被删除。2021年5月20日19时46分28厦门大学计算机系工程硕士教材 第四节 SQL基础-修改数据在表格中增加数据修改表格数据删除表格数据提交数据变更和回退操作 2021年5月20日19时46分29厦门大学计算机系工程硕士教材 数据库事务(transaction) ORACLE数据库通过事务来保证数据一致性，即使是在用户退出或者系统强行关闭。事务中包含了一组DML语句，并保证数据变化的一致性。形象地，在两个账户间转账包含了一个账户的入账和另一个账户的出账。这两个动作要么同时成功，要么同时失败。 2021年5月20日19时46分30厦门大学计算机系工程硕士教材 有三种类型的事务 2021年5月20日19时46分31 类型描述DML (Data manipulation language)包含一条或一组DML语句，作为逻辑整体DDL (Data definition language)仅包含一条DDL语句DCL (Data control language)仅包含一条DCL语句 厦门大学计算机系工程硕士教材 一个数据库事务起始于一个SQL语句，中止于以下4种事件之一： COMMIT或ROLLBACK语句 DDL/DCL隐式提交用户退出时自动提交系统强行关闭时取消事务事务结束后，下一条SQL语句将开始一个新的事务。 2021年5月20日19时46分32厦门大学计算机系工程硕士教材 COMMIT和ROLLBACK可以确保数据一致性在数据变更真正生效前可以预览数据组合逻辑上相关的操作 2021年5月20日19时46分33 语句功能COMMIT结束当前事务，并将所有数据变更提交数据库生效SAVEPOINT name在当前事务内部创建一个保存点ROLLBACK TO SAVEPOINT name ROLLBACK指令结束当前事务，并取消所有数据变更。ROLLBACK TO SAVEPOINT name放弃该保存点，并取消所有在保存点之后的数据变更 厦门大学计算机系工程硕士教材 INSERT UPDATE INSERT DELETETransactionCOMMIT Savepoint A Savepoint BROLLBACK to Savepoint BROLLBACK to Savepoint AROLLBACK 2021年5月20日19时46分34厦门大学计算机系工程硕士教材 隐式事务提交一个事务在下列情况下会被自动提交 DDL语句 DCL语句用户正常退出数据库，而缺少显式的COMMIT或者ROLLBACK。一个事务在下列情况下会被自动回退 用户异常退出数据库系统强行关闭2021年5月20日19时46分35厦门大学计算机系工程硕士教材 数据在COMMIT/ROLLBACK前的状态事务中所有数据变更都会被临时存放在数据库的缓冲池中，直到事务被提交或者回退。因而事务中早先的状态是可以被恢复的。用户可以通过SELECT语句查询自己发起事务的数据变更，即使事务未被提交。但不能看到他人发起事务中未被提交的数据。 用户发起事务中的变更数据将被数据库系统锁定。除该用户外，其他用户不能对锁定的数据进行变更操作，直到锁定的数据被提交或回退。2021年5月20日19时46分36厦门大学计算机系工程硕士教材 数据在COMMIT后的状态数据变更在数据库中正式生效数据不可被还原所有用户可以看到变更数据数据锁将被释放，一度被锁定的数据重新可以被所有用户修改 所有的保存点(savepoint)都被清除2021年5月20日19时46分37厦门大学计算机系工程硕士教材 提交数据UPDATE empSET deptno = 10WHERE empno = 7782;1 row updated.COMMIT;Commit complete. 2021年5月20日19时46分38厦门大学计算机系工程硕士教材 INSERT INTO department(deptno, dname, loc)VALUES (50, ADVERTISING , MIAMI );1 row created.UPDATE employeeSET deptno = 50WHERE empno = 7876;1 row updated.COMMIT;Commit complete. 2021年5月20日19时46分39厦门大学计算机系工程硕士教材 数据在ROLLBACK后的状态所有数据变更都取消，数据状态恢复到事务开始前数据锁将被释放，一度被锁定的数据重新可以被所有用户修改DELETE FROM employee;14 rows deleted.ROLLBACK;Rollback complete. 2021年5月20日19时46分40厦门大学计算机系工程硕士教材 回退到一个保存点使用SAVEPOINT语句创建一个保存点。如果保存点名称已经存在，则新的保存点将覆盖旧的保存点。使用ROLLBACK TO SAVEPOINT语句回退到已有保存点状态UPDATE SAVEPOINT update_done;Savepoint created.INSERT ROLLBACK TO update_done;Rollback complete. 2021年5月20日19时46分41厦门大学计算机系工程硕士教材 语句级的回退事务中如果一条DML语句执行失败，则只有本条语句被回退。之前的数据变更不会被回退，用户可以显式地提交或者回退。 ORACLE在任何DDL语句执行后都会隐式地提交，故即使DDL语句执行失败，也不能回退到更早之前的状态。 2021年5月20日19时46分42厦门大学计算机系工程硕士教材 读一致性(Read Consistency)数据库用户有两种类型的数据操作读操作(SELECT)写操作(INSERT, UPDATE, DELETE)读一致性能够保证任何时候数据看起来都是一样的 用户在读数据的时候不会看到正在被修改的数据用户正在修改的数据不会被其他用户意外修改读一致性保证在面对相同的数据时读操作不必等待写操作写操作不必等待读操作2021年5月20日19时46分43厦门大学计算机系工程硕士教材 当User A在数据库变更了某张表格的数据，ORACLE服务器创建了这张表格在修改前的副本，存放在回滚段中(rollback segment)。除了User A能看到修改后的数据外，其他用户在查询这张表格时，看到的是回滚段中的副本。只有当变更记录被User A正式提交给数据库后，其他用户才能看到变更。此时回滚段中存放副本的空间被释放。 如果事务被回退，回滚段中的副本将回写到表中，数据恢复原样。2021年5月20日19时46分44厦门大学计算机系工程硕士教材 2021年5月20日19时46分45厦门大学计算机系工程硕士教材 数据锁数据锁可以阻止多个用户同时对同一资源（表格或者系统对象）进行修改操作。 ORACLE的数据锁是全自动的，使用最低级别的约束，不需要用户关注。除了SELECT语句，其他SQL语句都会自动为修改的数据上锁，数据锁的生命周期到事务结束为止。（COMMIT或者ROLLBACK） 数据锁有两种模式：排他锁(exclusive)：阻止资源共享，通常指表格中的行锁。只有一个事务可以占有排他锁。共享锁(share lock)：确保资源共享，通常指对表格的锁。可以有多个事务占有共享锁。2021年5月20日19时46分46厦门大学计算机系工程硕士教材 第四节 SQL基础-修改数据 小结语句功能INSERT在表中新增行UPDATE在表中更改行DELETE在表中删除行COMMIT把所有未提交的变更正式提交数据库SAVEPOINT设置回退点ROLLBACK取消所有未提交的变更 2021年5月20日19时46分47Oracle服务器通过数据锁保证数据的一致性。厦门大学计算机系工程硕士教材 第四节 SQL基础-修改数据 习题1.在SALEGRADE表中新增一行记录(0,0,699)。2.把EMP表中平均工资小于2000的部门(deptno)中所有员工的工资增加20%。3.删除SALEGRADE表的所有记录。 2021年5月20日19时46分48厦门大学计算机系工程硕士教材