Oracle-SQL开发 —— 数据操作与事务控制
原创约 3538 字大约 12 分钟...
57-Oracle-SQL开发 —— 数据操作与事务控制.md
注意
本博文仅供学术研究和交流参考，严禁将其用于商业用途。如因违规使用产生的任何法律问题，使用者需自行负责。
概念
数据操作语言
- Data Manipulation Language ,简称DML，主要用来实现对数据库表中的数据进行操作。
- 数据操作语言主要包括如下几种：
- 增加行数据：使用INSERT语句实现
- 修改行数据：使用UPDATE语句实现
- 删除行数据：使用DELETE语句实现
- 合并行数据：按照指定条件合并两个表的数据，使用MERGE语句实现
事务(Transaction)概念
- 事务：也称工作单元，是由一个或多个SQL语句所组成的操作序列，这些SQL语句作为一个完整的工作单元，要么全部 执行成功，要么全部执行失败。在数据库中，通过事务来 保证数据的一致性。
- 事务处理语言：Transaction Process Language ,简称TPL， 主要用来对组成事务的DML语句的操作结果进行确认或取消。 确认也就是使DML操作生效，使用提交(COMMIT)命令实现； 取消也就是使DML操作失效，使用回滚(ROLLBACK)命令实现。
- 通过事务的使用，能防止数据库中出现数据不一致现象。 如两个银行账户进行转账，涉及到两条更新操作，这两条 更新操作只允许全部成功或失败，否则数据会出现不一致的现象。
事务组成
- 在数据库中，事务由一组相关的DML或SELECT语句，加上一 个TPL语句（COMMIT、ROLLBACK）或一个DDL语句(CREATE、 ALTER、DROP、TRUNCATE等)或一个DCL（GRANT、REVOKE） 语句。
- 例：如下语句组成两个事务。
- INSERT….
- UPDATE….
- DELETE….
- SELECT….
- INSERT…
- COMMIT;-- 前6条语句，组成第1个事务
- UPDATE…
- DELETE….
- CREATE… ;--后3条语句，组成第2个事务
事务特征可用四个字母的缩写表示：即ACID
- 原子性（Atomicity）
事务就像“原子”一样，不可被分割，组成事务的DML操作语 句要么全成功，要么全失败，不可能出现部分成功部分失败 的情况。
- 一致性（Consistency）
- 一旦事务完成，不管是成功的，还是失败的，整个系统处于 数据一致的状态。
- 隔离性（Isolation）
一个事务的执行不会被另一个事务所干扰。比如两个人同时 从一个账户从取钱，通过事务的隔离性确保账户余额的正确 性。
- 持久性（Durability）
- 也称为永久性，指事务一旦提交，对数据的改变就是永久的， 不可以再被回滚。
显示结束
- 提交(COMMIT)：使用COMMIT命令实现，以成功的方式结束事 务，组成事务的DML语句操作全部生效。
- 回滚(ROLLBACK)：使用ROLLBACK命令实现，以失败的方式结 束事务，组成事务的DML语句操作全部被取消。
隐式结束
- 隐式提交：当下列任意一种情况发生时，会发生隐式提交
- 执行一个DDL(CREATE、ALTER、DROP、TRUNCATE、RENAME） 语句；
- 执行一个DCL(GRANT、REVOKE)语句；
- 从SQL*Plus正常退出（即使用EXIT或QUIT命令退出）；
隐式回滚：当下列任意一种情况发生时，会发生隐式回滚
- 从SQL*Plus中强行退出
- 客户端连接到服务器端异常中断
- 系统崩溃
事务开启
- 事务自动开启于上一个事务结束后，执行的第一个DML语句。
- 例如：
- ….
- COMMIT; --结束一个事务；
- SELECT….
- INSERT…. --开启一个事务
- UPDATE….
- DELETE….
- SELECT….
- INSERT…
- COMMIT; --结束一个事务
- UPDATE… --开启一个事务
- DELETE….
- ROLLBACK… ;--结束一个事务
设置保存点
- 设置保存点：如果在一个事务内，想要回滚到指 定位置，不是回滚到事务的起始点，可以通过保 存点(SAVEPOINT)来实现。
- SAVEPOINT savepointname；--定义一个保存点语句；
- ROLLBACK TO savepointname;--回滚到指定保存点
- 注意：如上两条语句不结束事务的执行
- 设置保存点：如果在一个事务内，想要回滚到指 定位置，不是回滚到事务的起始点，可以通过保 存点(SAVEPOINT)来实现。
锁的概念
- 锁用来在多用户并发访问和操作数据库时,保证数据的一致 性的一种机制;
- 锁由Oracle自动管理，如一个DML操作，ORACLE默认的机制 是在DML操作影响的行记录上自动加锁;
- 锁在被相关的操作申请并持有后，会一直保持到事务的结 束，事务结束后，锁才会被释放;
- 查询语句不会锁定任何记录，如果在查询语句后面加FOR UPDATE子句会锁定查询所影响的行记录;
提交或回滚前数据状态
- 提交(COMMIT)或回滚(ROLLBACK)前的数据状态
- 数据变化前的状态可以被恢复;
- 当前会话可以使用SELECT语句来验证DML操作后的结果;
- 其它会话不能查看由当前用户的DML操作结果;
- 受影响记录被锁定，也就是其它用户不能改变受影响记录中 的数据;
- 提交(COMMIT)或回滚(ROLLBACK)前的数据状态
提交后数据状态
- 提交(COMMIT) 后的数据状态
- 在数据库中数据变化成为永久性的，先前的数据状态永久性 的消失；
- 所有用户/会话都可以查询到提交COMMIT后的结果；
- 锁定的记录被释放，可以有效地被其他用户操作；
- 所有的存储节点被清除；
- 提交(COMMIT) 后的数据状态
回滚(ROLLBACK)后的数据状态
- 先前的数据状态被恢复;
- 锁定的记录被释放;
- 所有的存储节点被清除;
示例代码
数据查询语言 DQL(Data Query Language): select
数据操作语言 DML(Data Manipulation Language): insert update delete
事务处理语言 TPL(Transaction Process Language):commit rollback
--------------------------------------------------------------------------------------------------
-- 新增数据(插入数据)
-- 语法： insert into 表名(列1，列2....) values(值1，值2....); 一次新增一行
-- insert into 表名(列1，列2....) 子查询; 一次新增N行
--
-- 1.列名列表与值列表的个数、类型必须保持一致
-- 2.列名列表可以省略，默认值列表给所有列赋值
-- 3.值列表中日期、字符串必须使用''单引号括起来
--------------------------------------------------------------------------------------------------
--插入空值NULL 隐含法: 在列名列表中忽略该列。
insert into dept(deptno,dname) values(50,'开发部');
--插入空值NULL 显示法: 指定 NULL关键字或者''
insert into dept(deptno,dname,loc) values(60,'需求部','东软大厦125C');--全部给值
insert into dept(deptno,dname,loc) values(70,'测试部',null);--loc给空值
insert into dept values(80,'实施部',''); --2.列名列表可以省略，默认值列表给所有列赋值
insert into dept values(90,'运维部'); --not OK 没有足够的值
--插入日期值 SYSDATE 函数记录当前日期和时间
insert into emp(empno,ename,hiredate) values(7777,'李四',sysdate);
--插入日期值 可以使用RR日期格式，也可以使用to_date()函数转换YY日期格式
insert into emp(empno,ename,hiredate) values(8888,'李二','2019-08-01');--文字与格式字符串不匹配
insert into emp(empno,ename,hiredate) values(8888,'李二','1-8月-19'); --RR日期格式
insert into emp(empno,ename,hiredate) values(8888,'李二',to_date('2019-08-01','yyyy-mm-dd'));--YY日期格式
--插入特殊字符
insert into dept(deptno,dname) values(50,'~!@#$%^*()-+');--ok
insert into dept(deptno,dname) values(50,'&'); --ok
insert into dept(deptno,dname) values(50,'&需求部');-- not OK &修饰的字符表示一个变量，等待用户输入变量值
insert into dept(deptno,dname) values(50,'\&需求部');--not ok
insert into dept(deptno,dname) values(50,'&' || '需求部'); --ok
insert into dept(deptno,dname) values(50,chr(38) || '需求部'); --ok
--ascii() 查看指定字符的ascii编码
select ascii('&') from dual; --38
--chr() 查看指定ascii编码代表的字符
select chr(38) from dual;
----------------------
--批量新增（备份数据）
----------------------
1. 将部门10所有的员工信息备份到emp_dept10表中
①复制表结构：创建表emp_dept10，且结构与emp表一致
create table emp_dept10 as select * from emp; --1.复制emp表的表结构与所有数据
create table emp_dept10 as select * from emp where 1=0; --1.仅复制emp表的表结构
② 使用子查询批量新增数据：将部门10的员工数据备份到emp_dept10表中
insert into emp_dept10 select * from emp where deptno = 10;--2.复制所有列数据
insert into emp_dept10(empno,ename,hiredate) select empno,ename,hiredate from emp where deptno = 10;--2.复制指定列数据
--------------------------------------------------------------------------------------------------
-- 修改数据
-- 语法： update 表名 set 列名1=值1[,列名2=值2.....] [where 限制条件]
--------------------------------------------------------------------------------------------------
--使用 WHERE 子句指定要修改的记录
1.把员工编号为7782的部门编号修改为20
update emp set deptno = 20 where empno = 7782;
--如果要修改所有记录，WHERE子句可以忽略
2.把所有员工的部门编号修改为20
update emp set deptno = 20;
--一次修改多列
3.把部门编号为10的员工，部门编号调整为20，工资增加100
update emp set deptno = 20,sal = sal+100 where deptno = 10;
--嵌入子查询修改
4.把部门编号为10的员工，部门编号调整为20，工资在原有的基础上，增加所有人的平均工资
select avg(sal) from emp;--1.所有人的平均工资 2073.5
update emp set deptno=20,sal= sal+(select avg(sal) from emp) where deptno = 10;
--修改记录时的完整性约束错误
5.把部门编号为10的员工，部门编号调整为55
update emp set deptno=55 where deptno =10;--not ok 未找到父项关键字 emp.deptno 的数据来源于 dept.deptno,此时dept中没有55编号的部门存在
--相关子查询修改
1.在emp中新增dname列
alter table emp add(dname varchar2(14));
2.将emp表中的dname更新为实际部门的名称
update emp set dname = (select dname from dept where deptno = emp.deptno);
--练习
2.修改奖金为null的员工，奖金设置为0
update emp set comm = 0 where comm is null;
select * from emp where comm = null;--null与任意值计算均为null
select * from emp where comm in(null);--null与任意值计算均为null
--------------------------------------------------------------------------------------------------
-- 删除数据
-- 语法： delete [from] 表名 [where 限制条件]
--------------------------------------------------------------------------------------------------
--删除选中记录
1.删除职位是CLERK的员工记录
delete from emp where job = 'CLERK';
--删除全部记录
2.删除所有员工记录
delete from emp;
delete emp;
--基于另一个表删除本表记录
3.删除部门SALES的员工
select deptno from dept where dname = 'SALES';--1.SALES的部门编号为多少？ 30
delete from emp where deptno = (select deptno from dept where dname = 'SALES');
--删除记录时的完整性约束错误
4.删除部门编号为10的部门记录
delete from dept where deptno = 10; --not ok 已找到子记录 因为emp.deptno引用dept.deptno,而dept.deptno为10的部门已经存在3个员工
--相关DELETE
5.删除曾经做过入职的员工记录
delete from emp where 曾经做过入职;
delete from emp where exists (select * from emp_jobhistory where empno = emp.empno);
6.删除没有员工的部门记录
delete from dept where 没有员工的部门;
delete from dept where not exists (select * from emp where deptno = dept.deptno);
--------------------------------------------------------------------------------------------------
-- 事务管理: 由一组SQL语句组成的管理单元,要么全部执行成功，要么全部执行失败
--
-- 事务四大特性：
-- 1. 原子性：一个事务中所有的SQL语句，执行时，要么全部执行成功，要么全部执行失败
-- 2. 一致性：不论SQL语句执行成功，还是失败，整个数据处于平衡状态。
-- 3. 隔离性：事务与事务之间相互隔离，互不影响
-- 4. 持久性：一旦数据被提交，永久性被保存到数据库中，不能再撤回
--
-- 事务开启：上一个事务结束以后，执行下一个DML(增、删、改)语句即开始新的事务
--
-- 事务结束：
-- 1.显示结束
-- commit:显示提交
-- rollback:显示回滚（撤销所有的操作）
--
-- 2.隐式结束
-- 隐式提交：当下列任意一种情况发生时，会发生隐式提交
-- 1.执行一个DDL(CREATE、ALTER、DROP、TRUNCATE、RENAME）语句；
-- 2.执行一个DCL(GRANT、REVOKE)语句；
-- 3.从SQL*Plus正常退出（即使用EXIT或QUIT命令退出）；
--
-- 隐式回滚：当下列任意一种情况发生时，会发生隐式回滚
-- 1. 从SQL*Plus中强行退出
-- 2. 客户端连接到服务器端异常中断
-- 3. 系统崩溃
--------------------------------------------------------------------------------------------------
--开启事务
insert into dept(deptno,dname) values(50,'财务部');
insert into dept(deptno,dname) values(60,'人力资源部');
rollback;--显示回滚,事务结束
insert into dept(deptno,dname) values(70,'后勤部');
commit;--显示提交，事务结束
------------------------------------------------------
--设置保存点
insert into dept(deptno,dname) values(81,'需求部');
savepoint poin1;--设置保存点1
insert into dept(deptno,dname) values(82,'UI部');
insert into dept(deptno,dname) values(83,'前端部');
savepoint poin2;--设置保存点2
insert into dept(deptno,dname) values(84,'开发部');
insert into dept(deptno,dname) values(85,'测试部');
savepoint poin3;--设置保存点3
insert into dept(deptno,dname) values(86,'实施部');
rollback to poin2;--回滚保存点2的位置
commit; --提交部分事务SQL语句
rollback;--事务已经被提交，不可能再被回滚
------------------------------------------------------
------------------------------------------------------
-- 锁：
--
-- 行级锁 和 表级锁 （悲观锁 和 乐观锁）
--
-- 行级锁: ORACLE默认的机制是在DML操作影响的行记录上自动加锁
-- 当会话1在修改指定行数据，且没有commit提交事务或者rollback回滚事务之前,
-- 该数据行被锁定，其他会话2不允许修改该数据。
-- 如果会话1结束事务，锁释放，其他会话2可以继续操作该数据
--
-- 表级锁：当会话1在操作指定数据时，表将被锁定，其他会话不允许对表结构进行修改（新增列、修改列、删除列）
------------------------------------------------------
--会话1 （执行一个事务）
update dept set loc='125C教室' where deptno = 83;
select * from dept;
--会话2（执行一个事务）
delete from dept where deptno = 50;
select * from dept;
分割线
相关信息
以上就是我关于 Oracle-SQL开发 —— 数据操作与事务控制 知识点的整理与总结的全部内容，希望对你有帮助。。。。。。。
Powered by Waline v2.15.4