1、处理数据 目标 通过本章学习 您将可以 使用DML语句向表中插入数据更新表中数据从表中删除数据将表中数据和并控制事务 数据控制语言 DML可以在下列条件下执行 向表中插入数据修改现存数据删除现存数据事务是由完成若干项工作的DML语句组成的 插入数据 DEPARTMENTS 新行 INSERT语句语法 使用INSERT语句向表中插入数据 使用这种语法一次只能向表中插入一条数据 INSERTINTOtable column column VALUES value value 插入数据 为每一列添加一个新值 按列的默认顺序列出各个列的值 在INSERT子句中随意列出列名和他们的值 字符和日期型数据应
2、包含在单引号中 INSERTINTOdepartments department id department name manager id location id VALUES 70 PublicRelations 100 1700 1rowcreated INSERTINTOdepartmentsVALUES 100 Finance NULL NULL 1rowcreated INSERTINTOdepartments department id department name VALUES 30 Purchasing 1rowcreated 向表中插入空值 隐式方式 在列名表中省略该列的
3、值 显示方式 在VALUES子句中指定空值 INSERTINTOemployees 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 1rowcreated 插入指定的值 SYSDATE记录当前系统的日期和时间 INSERTINTOemployeesVALUES
4、 114 Den Raphealy DRAPHEAL 515 127 4561 TO DATE FEB3 1999 MONDD YYYY AC ACCOUNT 11000 NULL 100 30 1rowcreated 插入指定的值 加入新员工检查插入的数据 INSERTINTOdepartments department id department name location id VALUES 创建脚本 在SQL语句中使用 变量指定列值 变量放在VALUES子句中 在INSERT语句中加入子查询 不必书写VALUES子句 子查询中的值列表应于INSERT子句中的列名对应 INSERTINT
5、Osales reps id name salary commission pct SELECTemployee id last name salary commission pctFROMemployeesWHEREjob idLIKE REP 4rowscreated 从其它表中拷贝数据 更新数据 EMPLOYEES 更新EMPLOYEES表 UPDATE语句语法 使用UPDATE语句更新数据 可以一次更新多条数据 UPDATEtableSETcolumn value column value WHEREcondition UPDATEemployeesSETdepartment id 7
6、0WHEREemployee id 113 1rowupdated 使用WHERE子句指定需要更新的数据 如果省略WHERE子句 则表中的所有数据都将被更新 更新数据 UPDATEcopy empSETdepartment id 110 22rowsupdated UPDATEemployeesSETjob id SELECTjob idFROMemployeesWHEREemployee id 205 salary SELECTsalaryFROMemployeesWHEREemployee id 205 WHEREemployee id 114 1rowupdated 在UPDATE语句中
7、使用子查询 更新114号员工的工作和工资使其与205号员工相同 UPDATEcopy empSETdepartment id SELECTdepartment idFROMemployeesWHEREemployee id 100 WHEREjob id SELECTjob idFROMemployeesWHEREemployee id 200 1rowupdated 在UPDATE语句中使用子查询 在UPDATE中使用子查询 使更新基于另一个表中的数据 UPDATEemployees ERRORatline1 ORA 02291 integrityconstraint HR EMP DEPT
8、 FK violated parentkeynotfound UPDATEemployeesSETdepartment id 55WHEREdepartment id 110 更新中的数据完整性错误 不存在55号部门 从表DEPARTMENTS中删除一条记录 删除数据 DEPARTMENTS DELETE语句 使用DELETE语句从表中删除数据 DELETE FROM table WHEREcondition 使用WHERE子句指定删除的记录 如果省略WHERE子句 则表中的全部数据将被删除 删除数据 DELETEFROMdepartmentsWHEREdepartment name Fina
9、nce 1rowdeleted DELETEFROMcopy emp 22rowsdeleted DELETEFROMemployeesWHEREdepartment id SELECTdepartment idFROMdepartmentsWHEREdepartment nameLIKE Public 1rowdeleted 在DELETE中使用子查询 在DELETE中使用子查询 使删除基于另一个表中的数据 删除中的数据完整性错误 DELETEFROMdepartmentsWHEREdepartment id 60 DELETEFROMdepartments ERRORatline1 ORA
10、 02292 integrityconstraint HR EMP DEPT FK violated childrecordfound Youcannotdeletearowthatcontainsaprimarykeythatisusedasaforeignkeyinanothertable 在INSERT语句中使用子查询 INSERTINTO SELECTemployee id last name email hire date job id salary department idFROMemployeesWHEREdepartment id 50 VALUES 99999 Taylor
11、 DTAYLOR TO DATE 07 JUN 99 DD MON RR ST CLERK 5000 50 1rowcreated 在INSERT语句中使用子查询 Verifytheresults SELECTemployee id last name email hire date job id salary department idFROMemployeesWHEREdepartment id 50 在DML语句中使用WITHCHECKOPTION关键字 使用子查询表示DML语句中使用的表WITHCHECKOPTION关键字避免修改子查询范围外的数据 INSERTINTO SELECTe
12、mployee id last name email hire date job id salaryFROMemployeesWHEREdepartment id 50WITHCHECKOPTION VALUES 99998 Smith JSMITH TO DATE 07 JUN 99 DD MON RR ST CLERK 5000 INSERTINTO ERRORatline1 ORA 01402 viewWITHCHECKOPTIONwhere clauseviolation 显式默认值 使用DEFAULT关键字表示默认值可以使用显示默认值控制默认值的使用显示默认值可以在INSERT和UP
13、DATE语句中使用 显示使用默认值 INSERTINTOdepartments department id department name manager id VALUES 300 Engineering DEFAULT UPDATEdepartmentsSETmanager id DEFAULTWHEREdepartment id 10 在插入操作中使用默认值 在更新操作中使用默认值 合并语句 按照指定的条件执行插入或更新操作如果满足条件的行存在 执行更新操作 否则执行插入操作 避免多次重复执行插入和删除操作提高效率而且使用方便在数据仓库应用中经常使用 合并语句的语法 可以使用merge语
14、句 根据指定的条件进行插入或更新操作 MERGEINTOtable nametable aliasUSING table view sub query aliasON joincondition WHENMATCHEDTHENUPDATESETcol1 col val1 col2 col2 valWHENNOTMATCHEDTHENINSERT column list VALUES column values MERGEINTOcopy empcUSINGemployeeseON c employee id e employee id WHENMATCHEDTHENUPDATESETc fir
15、st name e first name c last name e last name c department id e department idWHENNOTMATCHEDTHENINSERTVALUES 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 合并语句举例 在对表COPY EMP使用merge语句 根据指定的条件从表EMPLOYEES中插入或更新数据 合并语句举
16、例 MERGEINTOcopy empcUSINGemployeeseON c employee id e employee id WHENMATCHEDTHENUPDATESET WHENNOTMATCHEDTHENINSERTVALUES SELECT FROMCOPY EMP norowsselected SELECT FROMCOPY EMP 20rowsselected 数据库事务 数据库事务由以下的部分组成 一个或多个DML语句一个DDL语句一个DCL语句 数据库事务 以第一个DML语句的执行作为开始以下面的其中之一作为结束 COMMIT或ROLLBACK语句DDL或DCL语句 自
17、动提交 用户会话正常结束系统异常终了 COMMIT和ROLLBACK语句的优点 使用COMMIT和ROLLBACK语句 我们可以 确保数据完整性 数据改变被提交之前预览 将逻辑上相关的操作分组 控制事务 ROLLBACKtoSAVEPOINTB ROLLBACKtoSAVEPOINTA ROLLBACK UPDATE SAVEPOINTupdate done Savepointcreated INSERT ROLLBACKTOupdate done Rollbackcomplete 回滚到保留点 使用SAVEPOINT语句在当前事务中创建保存点 使用ROLLBACKTOSAVEPOINT语句回
18、滚到创建的保存点 自动提交在以下情况中执行 DDL语句 DCL语句 不使用COMMIT或ROLLBACK语句提交或回滚 正常结束会话 会话异常结束或系统异常会导致自动回滚 事务进程 提交或回滚前的数据状态 改变前的数据状态是可以恢复的执行DML操作的用户可以通过SELECT语句查询之前的修正其他用户不能看到当前用户所做的改变 直到当前用户结束事务 DML语句所涉及到的行被锁定 其他用户不能操作 提交后的数据状态 数据的改变已经被保存到数据库中 改变前的数据已经丢失 所有用户可以看到结果 锁被释放 其他用户可以操作涉及到的数据 所有保存点被释放 COMMIT Commitcomplete 改变数
19、据提交改变 DELETEFROMemployeesWHEREemployee id 99999 1rowdeleted INSERTINTOdepartmentsVALUES 290 CorporateTax NULL 1700 1rowinserted 提交数据 数据回滚后的状态 使用ROLLBACK语句可使数据变化失效 数据改变被取消 修改前的数据状态可以被恢复 锁被释放 DELETEFROMcopy emp 22rowsdeleted ROLLBACK Rollbackcomplete 语句级回滚 单独DML语句执行失败时 只有该语句被回滚 Oracle服务器自动创建一个隐式的保留点 其
20、他数据改变仍被保留 用户应执行COMMIT或ROLLBACK语句结束事务 读一致性 读一致性为数据提供一个一致的视图 一个用户的对数据的改变不会影响其他用户的改变 对于相同的数据读一致性保证 查询不等待修改 修改不等待查询 SELECT FROMuserA employees 读一致性 UPDATEemployeesSETsalary 7000WHERElast name Goyal 数据块 回滚段 改变和未改变的数据 改变之前的数据 UserA UserB 快照 锁 Oracle数据库中 锁是 并行事务中避免资源竞争 避免用户动作 自动使用最低级别的限制 在事务结束结束前存在 两种类型 显示
21、和隐式 锁 两种模式 独占锁 屏蔽其他用户 共享锁 允许其他用户操作 高级别的数据并发性 DML 表共享 行独占Queries 不需要加锁DDL 保护对象定义提交或回滚后锁被释放 总结 功能插入修正删除合并提交保存点回滚 语句INSERTUPDATEDELETEMERGECOMMITSAVEPOINTROLLBACK 通过本章学习 您应学会如何使用DML语句改变数据和事务控制 输出时间会话1会话2 t1 t2 t3 t4 t5 SELECTsalaryFROMemployeesWHERElast name King 24000 UPDATEemployeesSETsalary salary 10000WHERElast name King 24000 COMMIT 34000 SELECTsalaryFROMemployeesWHERElast name King SELECTsalaryFROMemployeesWHERElast name King 读一致性举例
