SQL基础修改数据（课堂PPT）

1、2022-5-31第四节 SQL基础-修改数据通过本节学习，需要掌握：在表格中增加数据修改表格数据删除表格数据提交数据变更和回退操作2022-5-32数据修改语言(DML)DML (Data Manipulation Language)是SQL的核心部分，用于在表格中插入数据更新表格数据删除表格数据一组DML语句构成了一个逻辑整体，称为事务(transaction)。为了保证数据的有效性，当事务中任意一个语句执行失败，都应当停止其他语句的执行。2022-5-33第四节 SQL基础-修改数据在表格中增加数据修改表格数据删除表格数据提交数据变更和回退操作2022-5-34在表中增加一行记录50DE

2、VELOPMENTDETROITDEPTNODNAMELOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON新增行DEPTDEPTNODNAMELOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON50DEVELOPMENTDETROITDEPT在DEPT表中新增一行记录2022-5-35INSERT语句可以在表中增加记录。INSERT INTOtable (column , column ) VALUES(value

3、, value );上述语法中，一次只能增加一行记录。如果省略column，必须按照表格里字段的顺序构造value。否则按column的顺序。建议INSERT语句中写column部分。字符型或者日期型的值必须带单引号。2022-5-36INSERT INTO dept (deptno, dname, loc)VALUES(50, DEVELOPMENT, DETROIT);1 row created.可以先使用DESCRIBE语句查看表结构DESC deptNameNameNull?Null?TypeTypeDEPTNONOT NULLNUMBER(2)DNAMEVARCHAR2(14)LOC

4、VARCHAR2(13)2022-5-37INSERT语句中的NULL值隐式方法：省略赋NULL值的字段INSERT INTOdept (deptno, dname)VALUES(60, MIS);1 row created.显示方法：指定字段的值为NULL。如果字段类型是字符型或者日期型，也可以指定为(空串)。INSERT INTOdeptVALUES(70, FINANCE, NULL);1 row created.2022-5-38INSERT语句中的NULL值(2)NOT NULL的字段不可以赋NULL值INSERT语句中如果存在column部分，未被枚举的字段在新生成的行中的值都为N

5、ULLINSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)VALUES(7196, GREEN, SALESMAN, 7782, SYSDATE, 2000, NULL, 10);SYSDATE表示当前日期和时间，USER表示当前登录数据库的用户名。2022-5-39INSERT语句增加一个员工INSERT INTOempVALUES(2296, AROMANO, SALESMAN, 7782, TO_DATE(FEB 3, 08, MON DD, YY), 1300, NULL, 10);验证SELECT*F

6、ROM empWHEREempno = 2296;缺省时间是0点2022-5-310INSERT语句的替换变元(1)INSERT INTO dept (deptno, dname, loc)VALUES(&department_id, &department_name, &location);Enter value for department_id: 80Enter value for department_name: EDUCATIONEnter value for location: ATLANTA1 row created.2022-5-311INSERT语句的

7、替换变元(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 INTOdept (deptno, dname, loc)VALUES(&department_id, &department_name, &location);Please enter

8、 the department number: 90Please enter the department name: PAYROLLPlease enter the location: HOUSTON1 row created.一条SQL*Plus指令换行的时候要用-连接起来2022-5-312INSERT语句使用已有表格数据INSERT INTO table column (, column) subquery;不需要使用value子句SELECT子查询中字段的数量和顺序需要和INSERT子句中的相同。字段名称不一定相同。2022-5-313INSERT INTO managers(id,

9、 name, salary, hiredate)SELECTempno, ename, sal, hiredateFROM empWHEREjob = MANAGER;3 rows created.2022-5-314第四节 SQL基础-修改数据在表格中增加数据修改表格数据删除表格数据提交数据变更和回退操作2022-5-315修改表数据EMPNOEMPNO ENAMEENAMEJOBJOBDEPTNODEPTNO7839 KINGPRESIDENT107698 BLAKEMANAGER307782 CLARKMANAGER107566 JONESMANAGER20EMPEMPEMPNOEMPN

10、O ENAMEENAMEJOBJOBDEPTNODEPTNO7839 KINGPRESIDENT107698 BLAKEMANAGER307782 CLARKMANAGER207566 JONESMANAGER20更新EMP表中的一行记录2022-5-316UPDATE语句可以在表中修改已有的记录。UPDATEtableSETcolumn = value , column = valueWHEREcondition;上述语法中，一次可以更新多行记录。通常情况下，使用主键(pk)来确定要更新的记录，除非你确定不会意外地更新一些不需要更新的记录。比如，如果使用员工名字来确定记录就是比较不可靠的，因

11、为可能存在重名。2022-5-317使用WHERE子句的时候，表格中由condition确定的记录被更新。UPDATEempSETdeptno = 20WHEREempno = 7782;WHERE子句被省略的时候，表格中的所有记录都被更新。UPDATEempSETdeptno = 20;2022-5-318UPDATE语句使用已有表格数据UPDATEtableSET(column, column, ) =(SELECTcolumn, column, FROMtableWHEREcondition)WHEREcondition;2022-5-319修改7698号员工的职业和部门，使得与7499

12、号员工一致。UPDATEempSET(job, deptno) = (SELECTjob, deptnoFROM empWHEREempno = 7499)WHEREempno = 7698;2022-5-320假设employee表和emp表具有相同的数据把所有和7788号员工相同职业的员工的部门号设置与7788号相同。UPDATEemployeeSETdeptno =(SELECTdeptnoFROM empWHEREempno = 7788)WHEREjob =(SELECTjobFROM empWHEREempno = 7788);2 rows updated.2022-5-321UP

13、DATE语句引发的错误如果更新的字段涉及到完整性约束，UPDATE将可能执行失败。UPDATEempSETdeptno = 55WHERE deptno = 10;UPDATE emp *ERROR at line 1:ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK) violated parent key not foundDEPT表中的deptno字段在EMP表中被当作外键使用。deptno=55不存在于DEPT表中，故在EMP表中不能随意更新。2022-5-322第四节 SQL基础-修改数据在表格中增加数据修改表格数据删除表格数据提交

14、数据变更和回退操作2022-5-323删除表数据DEPTDEPT在DEPT表中删除一行记录DEPTNODEPTNO DNAMEDNAMELOCLOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON50DEVELOPMENTDETROIT60MISDEPTNODEPTNO DNAMEDNAMELOCLOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON60MIS2022-5-324DELETE语句可以在表中删除已有的记录。

15、DELETE FROMtableWHEREcondition;上述语法中，一次可以删除多行记录。类似UPDATE语句，使用主键(pk)来确定要删除的记录，除非你确定不会意外地删除一些不需要删除的记录。2022-5-325使用WHERE子句的时候，表格中由condition确定的记录被删除。DELETE FROM deptWHEREdname = DEVELOPMENT;WHERE子句被省略的时候，表格中的所有记录都被删除。当需要删除一张大表的所有数据时，不建议用这种方法。合适的方法后面的章节会介绍。DELETE FROM dept;2022-5-326DELETE语句使用已有表格数据DELET

16、EtableWHERE(column, column, ) =(SELECTcolumn, column, FROMtableWHEREcondition);2022-5-327删除所有SALES部门的员工记录。DELETE FROM empWHEREdeptno = (SELECTdeptnoFROM deptWHEREdname = SALES);2022-5-328DELETE语句引发的错误如果删除的字段涉及到完整性约束，DELETE将可能执行失败。DELETE FROMdeptWHEREdeptno = 10;DELETE FROM dept *ERROR at line 1:ORA-

17、02292: integrity constraint (USR.EMP_DEPTNO_FK) violated child record foundDEPT表中的deptno字段在EMP表中被当作外键使用。 EMP表中存在deptno=10的记录，故在DEPT表中不能被删除。2022-5-329第四节 SQL基础-修改数据在表格中增加数据修改表格数据删除表格数据提交数据变更和回退操作2022-5-330数据库事务(transaction)ORACLE数据库通过事务来保证数据一致性，即使是在用户退出或者系统强行关闭。事务中包含了一组DML语句，并保证数据变化的一致性。形象地，在两个账户间转账包

18、含了一个账户的入账和另一个账户的出账。这两个动作要么同时成功，要么同时失败。2022-5-331有三种类型的事务类型类型描述描述DML (Data manipulation language)包含一条或一组DML语句，作为逻辑整体DDL (Data definition language)仅包含一条DDL语句DCL (Data control language)仅包含一条DCL语句2022-5-332一个数据库事务起始于一个SQL语句，中止于以下4种事件之一：COMMIT或ROLLBACK语句DDL/DCL隐式提交用户退出时自动提交系统强行关闭时取消事务事务结束后，下一条SQL语句将开始一个新

19、的事务。2022-5-333COMMIT和ROLLBACK可以确保数据一致性在数据变更真正生效前可以预览数据组合逻辑上相关的操作语句语句功能功能COMMIT结束当前事务，并将所有数据变更提交数据库生效SAVEPOINT name在当前事务内部创建一个保存点ROLLBACK TO SAVEPOINT nameROLLBACK指令结束当前事务，并取消所有数据变更。ROLLBACK TO SAVEPOINT name放弃该保存点，并取消所有在保存点之后的数据变更2022-5-334INSERTINSERTUPDATEUPDATEINSERTINSERTDELETEDELETETransactionC

20、OMMITSavepoint ASavepoint BROLLBACK to Savepoint BROLLBACK to Savepoint AROLLBACK2022-5-335隐式事务提交一个事务在下列情况下会被自动提交DDL语句DCL语句用户正常退出数据库，而缺少显式的COMMIT或者ROLLBACK。一个事务在下列情况下会被自动回退用户异常退出数据库系统强行关闭2022-5-336数据在COMMIT/ROLLBACK前的状态事务中所有数据变更都会被临时存放在数据库的缓冲池中，直到事务被提交或者回退。因而事务中早先的状态是可以被恢复的。用户可以通过SELECT语句查询自己发起事务的数据

21、变更，即使事务未被提交。但不能看到他人发起事务中未被提交的数据。用户发起事务中的变更数据将被数据库系统锁定。除该用户外，其他用户不能对锁定的数据进行变更操作，直到锁定的数据被提交或回退。2022-5-337数据在COMMIT后的状态数据变更在数据库中正式生效数据不可被还原所有用户可以看到变更数据数据锁将被释放，一度被锁定的数据重新可以被所有用户修改所有的保存点(savepoint)都被清除2022-5-338提交数据UPDATEempSETdeptno = 10WHEREempno = 7782;1 row updated.COMMIT;Commit complete.2022-5-339IN

22、SERT INTO department(deptno, dname, loc)VALUES(50, ADVERTISING, MIAMI);1 row created.UPDATEemployeeSETdeptno = 50WHEREempno = 7876;1 row updated.COMMIT;Commit complete.2022-5-340数据在ROLLBACK后的状态所有数据变更都取消，数据状态恢复到事务开始前数据锁将被释放，一度被锁定的数据重新可以被所有用户修改DELETE FROM employee;14 rows deleted.ROLLBACK;Rollback com

23、plete.2022-5-341回退到一个保存点使用SAVEPOINT语句创建一个保存点。如果保存点名称已经存在，则新的保存点将覆盖旧的保存点。使用ROLLBACK TO SAVEPOINT语句回退到已有保存点状态UPDATE SAVEPOINT update_done;Savepoint created.INSERT ROLLBACK TO update_done;Rollback complete.2022-5-342语句级的回退事务中如果一条DML语句执行失败，则只有本条语句被回退。之前的数据变更不会被回退，用户可以显式地提交或者回退。ORACLE在任何DDL语句执行后都会隐式地提交，故即使DDL语句执行失败，也不能回退到更早之前的状态。2022-5-343读一致性(Read Consistency)数据库用户有两种类型的数据操作读操作(SELECT)写操作(INSERT, UPDATE, DELETE)读一致性能够保证任何时候数据看起来都是一样的用户在读数据的时候不会看到正在被修改的