4、SQL基础-修改数据

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