SQL --事务

目录

1， 什么是事务

2， 回滚事务

---

1， 什么是事务

事务就是将多条sql语句作为一个整体来执行，要不全部成功，要么全部失败

语法：

begin; 

sql语句1;

...;

sql语句n；

commit；

# 注意begin到commit的四条语句为一个事务，每条语句都是以分号结束， commit表示提交该事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update dept set sal = sal - 100 where deptno = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update dept set sal = sal + 100 where deptno = 20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.15 sec)

mysql> select * from dept;
+--------+------------+----------+------+
| deptno | dname      | loc      | sal  |
+--------+------------+----------+------+
|     10 | ACCOUNTING | NEW YORK |  900 |
|     20 | RESEARCH   | DALLAS   | 1100 |
|     30 | SALES      | CHICAGO  | 1000 |
|     40 | OPERATIONS | BOSTON   | 1000 |
|     50 | fdsf       | fdsaf    | 1000 |
|     60 | fdsf       | fdsaf    | 1000 |
|     70 | aaa        | bbb      | 1000 |
|     80 | fdabb      | bbb      | 1000 |
+--------+------------+----------+------+
8 rows in set (0.00 sec)

2， 回滚事务

若先让事务执行失败，可以用如下的回滚事务实现

语法：

begin;

sql语句1;

...;

sql语句n;

rollback;

mysql> select * from dept;
+--------+------------+----------+------+
| deptno | dname      | loc      | sal  |
+--------+------------+----------+------+
|     10 | ACCOUNTING | NEW YORK |  900 |
|     20 | RESEARCH   | DALLAS   | 1100 |
|     30 | SALES      | CHICAGO  | 1000 |
|     40 | OPERATIONS | BOSTON   | 1000 |
|     50 | fdsf       | fdsaf    | 1000 |
|     60 | fdsf       | fdsaf    | 1000 |
|     70 | aaa        | bbb      | 1000 |
|     80 | fdabb      | bbb      | 1000 |
+--------+------------+----------+------+
8 rows in set (0.00 sec)

# begin表示开始事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update dept set sal = sal + 100 where deptno = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update dept set sal = sal - 100 where deptno = 20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dept;
+--------+------------+----------+------+
| deptno | dname      | loc      | sal  |
+--------+------------+----------+------+
|     10 | ACCOUNTING | NEW YORK | 1000 |
|     20 | RESEARCH   | DALLAS   | 1000 |
|     30 | SALES      | CHICAGO  | 1000 |
|     40 | OPERATIONS | BOSTON   | 1000 |
|     50 | fdsf       | fdsaf    | 1000 |
|     60 | fdsf       | fdsaf    | 1000 |
|     70 | aaa        | bbb      | 1000 |
|     80 | fdabb      | bbb      | 1000 |
+--------+------------+----------+------+
8 rows in set (0.00 sec)

# rollback表示回滚该事务
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from dept;
+--------+------------+----------+------+
| deptno | dname      | loc      | sal  |
+--------+------------+----------+------+
|     10 | ACCOUNTING | NEW YORK |  900 |
|     20 | RESEARCH   | DALLAS   | 1100 |
|     30 | SALES      | CHICAGO  | 1000 |
|     40 | OPERATIONS | BOSTON   | 1000 |
|     50 | fdsf       | fdsaf    | 1000 |
|     60 | fdsf       | fdsaf    | 1000 |
|     70 | aaa        | bbb      | 1000 |
|     80 | fdabb      | bbb      | 1000 |
+--------+------------+----------+------+
8 rows in set (0.00 sec)