mysql的使用20

mysql> #事务  是数据库区别于文件系统的重要特性之一   
mysql> #在sql编程中 事务编程成为必不可少的一个部分，事务能够保证数据从一种一致状态转化为另一种状态
mysql> #在数据库提交时  可以确保其要么对所有的修改都已经保存 要么对所有的操作都不保存
mysql> #事务是访问并更新数据库中各种数据库项的一个程序执行单元
mysql> #原子性：是指整个数据库事务是不可分割的工作单位，只有事务中所有的数据库操作都执行成功，整个事务的执行才算完成，只要一个操作失败，则事务操作失败
mysql>
mysql> #一致性：指事务将数据库从一种状态转变为另一种一致的状态，在事务的开始和结束以后，他的完整性并没有破坏
mysql>
mysql> #隔离性：事务的隔离性要求每个事务读写事务的对象与其他事务的对象能互相分离，即该事务提前对其他事务不可见
mysql>
mysql> #持久性：事务一旦提交，其结果就是永久性的，即使发生宕机故障，数据库也能回复
mysql>
mysql> #现在介绍事务的用法
mysql>
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| MonthlyOrders  |
| Nums           |
| a              |
| animals        |
| charTest       |
| customers      |
| dept_manager   |
| emp            |
| employees      |
| g              |
| mintable       |
| new_emp        |
| orders         |
| pp             |
| sales          |
| sessions       |
| t              |
| test01         |
| timetest       |
| tt             |
| ttt            |
| tttt           |
| updatetime     |
| x              |
| xx             |
| y              |
| yeartest       |
| yy             |
| z              |
+----------------+
29 rows in set (0.00 sec)

mysql> create table back(a int primary key)engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> set @@completion_type = 1;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t select 1;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into back select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> insert into back select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from back;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> #commit work已经提交了额。。。。
mysql>
mysql> insert into back select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from back;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql> #rollback表示回滚， 前面commit后的就无效了
mysql>
mysql> #completion_type为2时，表示commit and release 当事务提交后会自动断开与服务器的连接
mysql>
mysql> #现在讲讲保存点
mysql>
mysql> create table point (a int primary key)engine=innodb;
Query OK, 0 rows affected (0.13 sec)

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

mysql> insert into point select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> #以上保存了一个节点
mysql>
mysql> insert into t select 2;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into point select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> insert into point select 2;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> rollback to savepoint t2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from point;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from point;
Empty set (0.00 sec)

mysql> exit