第十一章:Oracla数据操作与事务控制

-----------------------------------------------
--数据操作与事务控制
  结构化查询语言
            DQL:数据查询语言 SELECT WHERE ORDER BY...
            DML:数据操作语言 insert update delete
            TPL:事务控制语言 commit rollback
            DDL:数据定义语言
            DCL:数据控制语言
 ------------------------------------------------
------------------------- DML:数据操作语言 insert update delete---------------------------
--insert:新增数据
          语法:
               insert into 表名(列名列表)values(值列表)
               一次只能追加一条记录 
          注意:
                1.值列表中的个数必须与列名一致,值列表和列名列表的数据类型也要一致
                2.如果列名列表不指定列名,默认添加所有列,列的顺序根据表结构的一致
                3.列名列表部分也可以指定部分非空的列,如果该列可以允许为null的话,注意值列表必须和列名列表对应
                4.字符和日期型数据必须要用单引号括起来
--增加一行记录
1、在dept表中新增一条记录
 insert into dept(deptno,dname,loc) values(50,'开发部','neusoft');
 insert into dept(deptno,dname,loc) values('z','开发部','neusoft');--无效数值  值列表和列名列表的数据类型要一致  
 insert into dept(deptno,dname,loc) values('开发部','neusoft'); -- 没有足够的值
 insert into dept(deptno,dname,loc) values(60,'开发部','neusoft',90); --值过多
 insert into dept values(60,'测试部','neusoft');--默认添加所有列
 insert into dept values('测试部','neusoft');--无效数字
 insert into dept(deptno,dname,loc) values(null,'s','s');-- 因为deptno不允许为null,  not ok
 --隐式调加null
 insert into dept(deptno) values(60); --ok dname和loc不给值时,默认为null
--显示调加null
 insert into dept(deptno,dname,loc) values(60,'测试部',null); 
 insert into dept(deptno,dname,loc) values(60,'测试部',''); 
--字符和日期型数据必须要用单引号括起来。
 insert into dept(deptno,dname,loc) values(60,测试部,'');  ---数据类型不匹配 字符类型必须使用单引号引起来
2、在emp表中添加一条记录
insert into emp(empno,hiredate) values(1235,'2015-8-19');-- 文字与字符串不匹配
insert into emp(empno,hiredate) values(1235,'2015-8-19');-- 文字与字符串不匹配
insert into emp(empno,hiredate) values(1235,to_date('2015-8-19','yyyy-mm-dd'));
insert into emp values(1235,'lisi','卖垃圾',7698,sysdate,999,999,20);
--emp表中的deptno数据来至于dept表,所有不能调加未存在dept表中的deptno
insert into emp values(1235,'lisi','卖垃圾',7698,sysdate,999,999,80);--未找到父项关键字
--插入特殊字符&
insert into dept(deptno,dname,loc) values(60,'&',null); -- ok
--&test &表示一个变量符号  test:变量
insert into dept(deptno,dname,loc) values(60,'&test',null); -- not ok
insert into dept(deptno,dname,loc) values(60,'&'||'test',null); -- ok
insert into dept(deptno,dname,loc) values(60,concat('&','test'),null); --ok
 
----插入多行数据
  不必书写values子句
  INSERT子句和数据类型必须和子查询中列的数量和类型相匹配中列的数量
3、创建manager表,只包含emp表结构
create table manager as select * from emp where 1=0
4、创建manager_bak表,包含emp表结构和数据
create table manager_bak as select * from emp
5、将emp表中10号部门的员工编号、员工姓名、入职日期、工作调加到manager表中
insert into manager(empno,ename,hiredate,job) select empno,ename,hiredate,job from emp where deptno=10
6、将emp表中20号部门的所有员工信息到manager表中
insert into manager select * from emp where deptno=20

--------------------------------修改数据-------------------------------
--update:更新数据
       修改数据主要用来按照指定条件修改表中某些行的列数据
       语法:
             update 表名 set 列名=[,列名=..] where条件
             where子句用来限定修改哪些行。
             set子句用来限定修改哪些列。

        注意:值与列名指定的数据类型保持一致

--修改单列
7、将员工编号为1234的职位改为收废品
update emp set job='收废品' where empnp=1234
--修改多列
8、将员工编号为1234的职位改为study,工资改为30,奖金改为500
update emp set job='study',sal=30,comm=500 where empno=1234
--修改多行数据
9、将所有员工的工资下调30%
update emp set sal=sal-sal*0.3
10、将10号部门的所有员工调到20号部门
update emp set deptno=20 where deptno=10
--嵌套update
把部门编号为10的员工,部门编号调整为20,工资在原有的基础上,增加所有人的平均工资
select avg(sal) from emp--所有人的平均工资
update emp set deptno=20,sal=sal+(select avg(sal) from emp) where deptno=10
--相关UPDATE
           用一个相关查询来更新在一个表中的行,该表中的行基于另一个表中的行
在emp表中增加一个列dname,来存储部门名称
使用相关子查询更新dname列为正确的部门名称
update emp e set dname=(select dname from dept where deptno=e.deptno)
--修改记录时的完整性约束错误
--emp表中的deptno数据来至于dept表,所有不能调加未存在dept表中的deptno
update emp set deptno=88 where deptno=10;--未找到父项关键字
--------------------------------删除数据-------------------------------
--delete:删除数据
        删除数据主要用来按照指定条件从表中删除某些行
        语法:
             delete [from] 表名 where条件
             使用 DELETE 语句删除表中满足条件的行记录

11、删除50号部门
delete dept where deptno=50
delete from dept where deptno=50 --MYsql写法
12、删除所有10号部门的员工
delete emp where deptno=10
13、删除emp表所有记录 --不建议
delete emp
--嵌套删除
基于另一个表删除本表记录
在DELETE语句使用子查询可以基于另一个表删除本表记录。
14、删除部门SALES的员工记录
delete emp where deptno=(select deptno from dept where dname='SALES')
删除曾经做过入职的员工记录
delete emp where empno in(select empno from emp_jobhistory)
--相关DELETE
删除曾经做过入职的员工记录
delete emp where 0<(select count(1) from emp_jobhistory where e.empno=empno)

1、删除经理编号为7566的员工记录
delete emp where mgr==7566
2、删除工作在NEW YORK的员工记录
delete emp where deptno=(select deptno from dept where loc='NEW YORK')
3、删除工资大于所在部门平均工资的员工记录
delete emp e where sal>(select avg(sal) from emp where deptno=e.deptno)
---------------------------------事务处理语言 commit  rollback---------------------------
事务:
     概念:也称为工作单元,是由一个或多个SQL语句所组成的操作序列,这些SQL语句作为一个完整的工作单元,
     特点:要么全部执行成功,要么全部执行失败。
     作用:在数据库中,通过事务来保证数据的一致性
COMMIT:使在一个事务中的DML操作生效
ROLLBACK:使在一个事务这种的DML失效

update emp set sal=sal-1000 where empno=7839
update emp set sal=sal+1000 where empno=7869

事务的开启:
           遇到第一条DML语句时事务开启
           在数据库中,事务由一组相关的DML或SELECT语句,加上一个TPL语句(COMMITROLLBACK)
           或一个DDL语句(CREATEALTERDROP、TURNCATE等)或一个DCL(GRANTREVOKE)语句。

事务的结束:
           显示结束:
              遇到commit提交或rollback回滚
           隐式结束:
              隐式提交:
                    执行一个DDL(CREATEALTERDROPTRUNCATERENAME)语句;
                    执行一个DCL(GRANTREVOKE)语句;
                    从SQL*Plus正常退出(即使用EXIT或QUIT命令退出)
              隐式回滚:
                     从SQL*Plus中强行退出
                     客户端连接到服务器端异常中断
                     系统奔溃

事务特性(***):
                原子性:事务就像“原子”一样,不可被分割,组成事务的DML操作语句要么全成功,要么全失败,不可能出现部分成功部分失败的情况。
                一致性:一旦事务完成,不管是成功的,还是失败的,整个系统处于数据一致的状态
                隔离性:一个事务的执行不会被另一个事务所干扰
                       --事务的隔离级别
                                  脏读(读未提交)
                                  幻读(读已提交)
                                  不可重读
                                  串行化

                持久性:也称为永久性,指事务一旦提交,对数据的改变是永久的,不可以再被回滚

select * from dept;
update emp set sal=sal-1000 where empno=7839
create table emp_bak as select * from emp where 1=0;

select * from dept;
select * from emp;

--commit;
--设置事务的保存点
       如果再一个事务内,想要回滚到指定位置,不是回滚到事务的起始点,可以通过保存点(SAVEPOINT)来实现。
update emp set sal=sal+1000 where empno=7839;
update  emp set sal=sal+1000 where empno=7839;
savepoint updatetwo;
delete  emp where empno=1234;
savepoint delone;
delete  emp where empno=1111;
savepoint deltwo;
rollback to updatetwo;

--数据库中的锁
           --行级锁
           --表级锁
           
     锁用来再多用户并发访问和操作数据库时,保证数据的一致性的一种机制;
     ORACLE默认的机制是在DML操作影响的记录上自动加锁;
     锁在被相关的操作申请并持有后,会一致保持到事务的结束,事务结束后,锁才会被释放;

--会话2
update emp set sal=sal+1000 where empno=7839
--会话1
update emp set sal=sal+1000 where empno=7839 --等待会话2将锁资源释放
update emp set sal=sal+1000 where empno=7788

--乐观锁 :版本控制  CAS算法
--悲观锁:共享锁/排它锁  

select * from manager
select * from manager_bak
select * from emp;

在这里插入图片描述

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 1024 设计师:上身试试 返回首页