数据操作与事物控制

一、数据操作语言
  • Date Manipulation Language,简称DML,主要用来实现对数据库表中的数据进行操作。
  • 数据操作语言主要包括如下几种:
  • 增加行数据:使用INSERT语句实现
  • 修改行数据:使用UPDATE语句实现
  • 删除行数据:使用DELETE语句实现
  • 合并行数据:按照指定条件合并两个表的数据,使用MERGE语句实现
二、插入数据
  • 使用INSERT语句向表中插入数据,语法结构如下:
INSERT INTO table [(column [, column...])] 
VALUES (value [, value...]);
  • 采用这种语法一次只能追加一条记录;
  • column部分叫做列名列表,value部分叫做值列表,列名列表和值列表必须在个数及数据类型上保持一致;
  • 列名列表部分可以省略,如果省略,默认包括该表的所有列,列的顺序为使用 desc 表名 命令所查看的顺序;
  • 列名列表部分也可以指定部分非空的列,注意值列表必须和列名列表对应;
  • 字符和日期型数据必须要用单引号括起来。
  • 插入多行数据
INSERT INTO表名[(列名1[,列名2,…,列名n])] 子查询 ;
  • 不必书写values子句
  • INSERT子句和数据类型必须和子查询中的列的数量和类型相匹配中列的数量
三、修改数据
  • 修改数据使用UPDATE子句完成,语法
UPDATE table 
SET column = value [, column = value] 
[WHERE condition];
  • WHERE子句用来限定修改哪些行,当WHERE子句忽略时修改所有行
  • SET子句用来限定修改哪些列
  • 嵌入子查询修改
  • 把部门编号为10的员工,部门编号调整为20,工资在原有的基础上,增加所有人的平均工资
UPDATE emp 
SET deptno = 20,sal=sal+(select avg(sal)from emp)
WHERE deptno = 10;
  • 修改记录时不能违反完整性约束
UPDATE emp 
SET deptno = 55 
WHERE deptno = 10;
//注意:违反完整性约束,55号部门根本不存在
  • 相关UPDATE
    用一个相关子查询来更新在一个表中的行,该表中的行基于另一个表中的行
  • 在emp表中增加一个列dname,来存储部门名称
ALTER TABLE emp 
ADD (dname VARCHAR2(14));
  • 使用相关子查询更新dname列为正确的部门名称
UPDATE emp e 
SET dname =  
           (SELECT dname  
            FROM dept d 
            WHERE deptno = e.deptno);
四、删除数据
  • 删除数据主要用来按照指定条件从表中删除某些行

1.使用DELETE语句删除表中满足条件的行记录

  • 删除选中记录
DELETE  
FROM emp
WHERE job  = 'CLERK';  
  • 删除全部记录
DELETE   
FROM emp;

2.在DELETE语句中使用子查询可以基于另一个表删除本表记录

DELETE FROM emp 
WHERE deptno =  
              (SELECT   deptno 
               FROM     dept 
               WHERE    dname ='SALES');

3.使用相关DELETE,你可以使用相关子查询来只删除那些已经存在于另一张表中的行

DELETE FROM dept d 
WHERE not exists 
                (SELECT empno 
                 FROM emp  
                 WHERE deptno = d.deptno);
五、事务处理语言
  • 事务:也称工作单元,是由一个或多个SQL语句所组成的操作序列,这些SQL语句作为一个完整的工作单元,要么全部执行成功,要么全部执行失败。在数据库中,通过事务来保证数据的一致性。
  • 事务处理语言:Transaction Process Language ,简称TPL,主要用来对组成事务的DML语句的操作结果进行确认或取消。确认也就是使DML操作生效,使用提交(COMMIT)命令实现;取消也就是使DML操作失效,使用回滚(ROLLBACK)命令实现。
  • 通过事务的使用,能防止数据库中出现数据不一致现象。如两个银行账户进行转账,涉及到两条更新操作,这两条更新操作只允许全部成功或失败,否则数据会出现不一致的现象。
六、事务组成
  • 在数据库中,事务是由一组相关的DML或SELECT语句,加上一个TPL语句(COMMIT、ROLLBACK)或一个DDL语句(CREAT、ALTER、DROP、TRUNCATE等)或一个DCL(CRANT、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 回滚到指定的保存点
    注意:如上两条语句不结束事务的执行。
十一、锁
  • 锁的概念
  • 锁用来在多用户并发访问和操作数据库时,保证数据的一致性的一种机制
  • 锁由oracle自动管理,如一个DML操作,ORACLE默认的机制是在DML操作影响的行记录上自动加锁
  • 锁在被相关的操作申请并持有后,会一直保持到数据的结束,事务结束后,所才会被释放
  • 查询语句不会被锁定任何记录,如果在查询语句后面加FORUPDARE子句会锁定查询所影响的行记录
十二、提交或回滚前数据状态
  • 提交(COMMIT)或回滚(ROLLBACK)前的数据状态
  • 数据变化前的状态可以被恢复
  • 当前会话可以使用SELECT语句来验证DML操作后的结果
  • 其他会话不能查看由当前用户的DML操作结果
  • 受影响记录被锁定,也就是其他用户不能改变受影响记录中的数据
十三、提交后数据状态
  • 提交(COMMIT)后的数据状态
  • 在数据库中数据变化成永久性的,先前的数据状态永久性的消失
  • 所有用户/会话都可以查询到提交COMMIT后的结果
  • 锁定的记录被释放,可以有效的被其他用户操作
  • 所有的存储节点被清除
十四、回滚后数据状态
  • 回滚(ROLLBACK)后的数据状态
  • 先前的数据状态被恢复
  • 锁定的记录被释放
  • 所有的存储节点被清除
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 217,734评论 6 505
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,931评论 3 394
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 164,133评论 0 354
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,532评论 1 293
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,585评论 6 392
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,462评论 1 302
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 40,262评论 3 418
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 39,153评论 0 276
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,587评论 1 314
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,792评论 3 336
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,919评论 1 348
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,635评论 5 345
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 41,237评论 3 329
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,855评论 0 22
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,983评论 1 269
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 48,048评论 3 370
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,864评论 2 354

推荐阅读更多精彩内容