One 、 Business

1. Concept

One or a group of sql Statements form an execution unit , This execution unit is either all executed , Or none at all . If one of a group sql Once the execution fails or an error occurs , The whole group sql Statements are rolled back . All changed data will also return to the state before the start of the transaction .

2. Storage engine

mysql> show engines;

InnoDB Storage engine support transactions .MyISAM 、MEMORY I won't support it

3. Characteristics of the transaction

3.1 Atomicity

Refer to A transaction is an indivisible unit of work , All operations in a transaction are either performed , Either not

3.2 Uniformity

Transactions must transform the database from one consistency state to another

3.3 Isolation,

Refer to The execution of one transaction cannot be disturbed by other transactions

3.4 persistence

Once a transaction is committed , It changes the data in the database permanently .

Two 、 The transaction operations

1. Implicit transaction

​ Transactions have no obvious start and end marks . such as insert update delete
​ mysql Auto submit in is automatically turned on .

mysql> show variables like 'autocommit';

2. According to the transaction

​ A transaction has distinct open and end tags .

# Turn off the auto commit transaction function . You can start a transaction without turning off auto commit 
mysql> set autocommit=0;  
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

3. Operation of transaction

begin : Start a transaction
commit ： Commit a transaction
rollback ： Roll back a transaction

example ：

mysql> create database pangbing;
mysql> use pangbing
mysql> create table abc (name char(20),nianling int(3));
mysql> show tables;
+--------------------+
| Tables_in_pangbing |
+--------------------+
| abc                |
+--------------------+
1 row in set (0.00 sec)

mysql> insert into abc values ('a',20);
Query OK, 1 row affected (0.01 sec)

mysql> select * from abc;
+------+----------+
| name | nianling |
+------+----------+
| a    |       20 |
+------+----------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into abc values ('b',18);
mysql> insert into abc values ('c',22);
mysql> select * from abc;
+------+----------+
| name | nianling |
+------+----------+
| a    |       20 |
| b    |       18 |
| c    |       22 |
+------+----------+
3 rows in set (0.00 sec)

# Roll back the transaction 
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from abc;
+------+----------+
| name | nianling |
+------+----------+
| a    |       20 |
+------+----------+
1 row in set (0.00 sec)

 Pay attention to is ： To roll back a transaction , Cannot commit transaction , Commit transactions cannot be rolled back .mysql The default is auto submit .

# Commit transaction 
mysql> commit;

3、 ... and 、 View

A view is a virtual table , Use the same method as the table

 grammar ：
create view  View name  as sql sentence ;

mysql> select * from abc where name='a';
+------+----------+
| name | nianling |
+------+----------+
| a    |       20 |
+------+----------+

# Create a view 
mysql> create view shitu as select * from abc where name='a';
Query OK, 0 rows affected (0.00 sec)

# Use view 
mysql> select * from shitu;
+------+----------+
| name | nianling |
+------+----------+
| a    |       20 |
+------+----------+
1 row in set (0.00 sec)