SQL教程——TCL语言

我就是我 2022-12-25 08:52 174阅读 0赞

> 本教程中所使用的数据库的建表语句都在“SQL教程——索引”这篇文章中，点击链接直达：[索引&建表语句][Link 1]

> 摘要：本文主要介绍SQL的TCL语法

# TCL语言 #

*----Transaction Control Language 事务控制语言*

**事务：**

一个或一组SQL语句组成的一个执行单元，这个执行单元要么全部执行，要么全部不执行。

**案例：转账**

张三丰    1000

郭襄       1000

update 表 set 张三丰的余额=500 where name='张三丰'
    
    gg了，停电   
    
    update 表 set 郭襄的余额=500 where name=郭襄'

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 1][]

**事务的创建**

*隐式事务：事务没有明显的开启和结束的标记*

比如insert、update、delete语句

show variables like 'autocommit';

*显式事务：事物具有明显的开启和结束的标记*

set autocommit = 0;

**用法：**

#开启事务
    
    set autocommit = 0;
    
    start transaction;
    
    #编写一组事务的语句
    
    update account set balance = 500 where username = '张无忌';
    
    update account set balance = 1500 where username  = '赵敏';
    
    
    
    #结束事务
    
    commit;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 2][]

查看事务级别：

select @@transaction_isolation;
    
    set @@transaction_isolation = 'repeatable-read';
    
    #也可以
    
    set transaction isolation level repeatable read;

#1.演示savepoint使用
    
    set autocommit = 0;
    
    start transaction;
    
    delete from account where id = 25;
    
    savapoint a;  #设置保存点
    
    delete from account where id = 28;
    
    rollback to a;  #回滚到保存点
    
    
    
    #2.delete和truncate在事务使用时的区别
    
    
    
    #演示delete
    
    set autocommit = 0;
    
    start transaction;
    
    delete from account;
    
    rollback;
    
    
    
    #演示truncate
    
    set autocommit = 0;
    
    start transaction;
    
    truncate table account;
    
    rollback;

[Link 1]: https://blog.csdn.net/IT_TIfarmer/article/details/110382794
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70]: /images/20221120/9ee59f478ce246c0ac158e43e7258f7a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201201102718880.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2020120110271926.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy,size_16,color_FFFFFF,t_70