数据操作与事务处理.ppt

Oracle数据查询与管理 第7章 数据处理 本章内容 处理数据使用INSERT在新表中添加行使用UPDATE更改表中的数据使用DELETE从表中删除数据使用TRUNCATE清空表数据库事务处理 本章目标 掌握DML语句了解约束相关概念处理控制事务描述本章重难点 处理数据 知识点概述 在表中添加行 向表中添加行的过程 在表中添加行 使用INSERT语句可在表中添加新行语法使用此语法一次只能插入一行语法说明 在表中添加行 插入新行插入一个新行 此行的每一列都含有值按照表中列的默认顺序列出这些值在INSERT子句中列出这些列 也可以不写任何列 但不能只写一部分列将字符和日期值放在单引号中示例 使用INSERT语句向DEPARTMENTS表中添加一行 在表中添加行 插入一条新纪录时 某些字段没有值NULL关键字可以用来为一列指定一个空值显式方法 在VALUES子句中指定NULL关键字隐式方法 在列的列表中省略该列 从其他表中复制行 编写带有子查询的INSERT语句在INSERT语句中 可以不使用列值 而是使用查询从一个表向另外一个表复制行要求源表和目标表的列数和列的类型匹配不要使用VALUES子句示例 使用SELECT语句来检索顾客 1的first name和last name列 并将其提供给INSERT语句注意新行的customer id被设置为10 INSERTINTOcustomers customer id first name last name SELECT10 first name last nameFROMcustomersWHEREcustomer id 1 在插入数据时使用计算表达式和函数在Insert语句的VALUES子句中 可以使用各种函数和表达式来作为新插入的值日期计算 使用当前日期和使用特定日期 序列数字计算 字符转换等操作 插入数据时可能发生的错误NOTNULL列缺少非空值违反唯一性约束条件或主键约束条件的重复值违反CHECK约束条件的任何值违反外键维护的引用完整性约束条件数据类型不匹配或者值太长无法放入列中注意 使用INSERT时 尽量指明列名 这样可以增加可读性和可靠性 逐个演示各种错误 修改表中的数据 更改表中的数据 使用UPDATE语句修改现有的行语法如果需要 一次可以更新多个行通过查询来确认更新操作 如果要更新某一行的数据 请使用主键或唯一键作为限定个条件示例 如果用last name来限定 则有可能会更新多行 更改表中的数据 如果省略了WHERE子句 则会修改表中的所有行可以一次更新多个字段 例如 114号员工的职务需要从SA REP修改为IT PROG 并且其佣金要清除 更改表中的数据 用子查询更新两个列通过编写多个子查询 可以在UPDATE语句的SET子句中更新多个列示例 更改员工113的职位与薪资 使之与员工205的职务和薪资相同 从表中删除行 在表中删除行 可以使用DELETE语句从表中删除现有的行如果没有删除任何行 则会返回 0rowsdeleted 的消息 在表中删除行 从表中删除行如果指定了WHERE子句 则可以删除特定的行如果省略了WHERE子句 则会删除表中所有行通过SELECT语句确认删除操作 DELETEFROMemployeesWHEREemployee id 4 DELETEFROMemployees 在表中删除行 根据另一个表删除行使用子查询可以根据另一个表中的值来删除表中的行示例 删除部门名称中包含字符串Public的部门的所有雇员信息子查询查询departments表 根据部门名称找到部门编号子查询将部门编号返回给主查询 主查询根据编号删除雇员信息 使用TRUNCATE 从表中删除所有的行 使表为空并保留表结构不变属于DDL而不是DML 无法取消其操作 慎用 数据库事务处理 知识点概述 什么是事务 数据库事务 transcaction 就是一组SQL语句 这组SQL语句是一个逻辑工作单元数据库事务的一个例子 将钱从一个银行帐号中转到另外一个银行帐号中 此时通常包含两步操作 一条UPDATE语句负责从一个银行帐号的总额中减去一定的钱数一条UPDATE语句负责向另外一个银行帐号中增加相应的钱数减少和增加这两个操作必须永久性地记录到数据库中 否则就会丢失如果钱的转账有问题 则必须同时取消减少和增加这两个操作 事务的ACID特性 数据库理论采用了严格的定义 说明事务有4个基本特性 原子性 Atomic 事务是原子的 这就是说一个事务中包含的所有SQL语句都是一个不可分割的工作单元一致性 Consist 事务必须确保数据库的状态保持一致 这就是说事务开始时 数据库的状态是一致的 在事务结束时 数据库的状态也是一致的 隔离性 Isolated 多个事务可以独立运行 而不会彼此产生影响 持久性 Durable 一旦事务被提交之后 数据库的变化就会永远保留下来 即使运行数据库软件的及其后来崩溃也是如此 事务的开始与结束 事务的开始 连接到数据库上 并执行一条DML语句 INSERT UPDATE DELETE 前一个事务结束后 又输入了另外一条DML语句事务结束执行COMMIT或ROLLBACK语句执行一条DDL DCL语句 自动提交 断开与数据库的连接 在退出SQL PLUS时 使用EXIT此时会自动执行COMMIT如果SQL PLUS被意外终止了 会自动执行ROLLBACK系统崩溃 事务的提交与回滚 要永久性地记录事务中SQL语句的结果 需要执行COMMIT 从而提交 commit 事务要取消SQL语句的结果 需要执行ROLLBACK语句 从而回滚 rollback 事务 将所有行重新设置为原始状态COMMIT和ROLLBACK语句的优点确保数据的一致性在更改永久化之前预览数据更改按逻辑关系对相关操作进行分组COMMIT或ROLLBACK操作之前的数据状态可以将数据恢复到以前的状态当前用户可以用SELECT语句复查DML操作结果其他用户不能查看当前用户的DML语句的结果受影响的行会被锁定 其他用户无法更改这些行中的数据 COMMIT操作之后的数据状态数据库中的数据更改被永久化以前的数据状态永久丢失所有用户都可以查看结果受影响行的锁定被释放 其他用户可以对这些行进行处理所有保存点被清除ROLLBACK操作之后数据的状态数据更改被撤销数据还原到以前的状态受影响的行锁定被释放 显式的事务处理控制语句 隐式事务处理 下列情况下发生自动提交发出DDL语句发出DCL语句从SQLDeveloper或SQLPLUS正常退出而未发出COMMIT或ROLLBACK语句发生故障时自动回退 演示提交和回退操作 读一致性 读一致性可以确保用户在任何时候看到的数据都是一致的一个用户进行的更改不会与另一个用户进行的更改冲突读一致性可以确保对于同一数据 读取者不必等待写入者完成操作即可读取写入者不必等待读取者完成操作即可写入写入者必须等待其他写入者完成操作才可以写入 演示读一致性 读一致性来确保达到如下效果保证数据库读取者和写入者看到的数据是一致的读取者看不到正在更改的数据保证写入者对数据库的更改是在一致方式下完成的一个写入者进行的更改不会中断另一个写入者正在进行的更改 也不会与之冲突 用同一用户登录不同的会话 每个会话均以上述方式维护读一致性 演示 Oracle如何实施读一致性 Oracle如何实施读一致性 读一致性是自动实施的当对数据库进行插入 更新或删除操作时 Oracle服务器会在数据被更改之前制作它的复本 并写到还原段中除发出更改的人之外 所有的读取者看到的仍然是更改开始之前的数据库 他们看见的是数据是回滚段 快照 在更改被提交到数据库之前 只有正在修改数据的用户看到的是更改过的数据库 其他所有人看到的都是还原段中的快照 这样可以保证数据读取者读取的是一致的数据 而不是正在进行更改的数据 在提交了DML语句后 任何执行SELECT语句的用户都可以看到对数据库所做的更改 还原段文件中原有数据占用的空间被释放出来留待重新使用如果事务处理被回退 更改会被撤销 还原段中原始的旧版本数据被写回到表中所有用户看到的都是事务处理开始之前的数据库 事务锁 锁是一种机制 它可以防止同一资源的事务处理之间的破坏性交互作用 资源可以是用户对象 表或行 或用户无法看到的系统对象在Oracle数据库中 锁具有以下特性 防止并发事务处理之间的破坏性交互作用不需要用户操作自动使用最低级别的限制在事务处理期间保持锁定具有两种类型 显示锁定和隐式锁定 两种锁定模式排它模式 不允许其他用户访问共享模式 允许其他用户访问高级别数据并发DML 表共享 行排它查询 不需要锁定DDL 保护对象定义在提交或回退之前一直保持锁定 示例 两个事务T1和T2试图修改employees表中ID为200的员工的记录T1执行一条UPDATE语句修改员工200的记录 但是