mysql标识列和事务 - 自律即自由

  1 #标识列
  2 
 16 
 17 #一、创建表时设置标识列
 18 
 19 
 20 DROP TABLE IF EXISTS tab_identity;
 21 CREATE TABLE tab_identity(
 22     id INT  ,
 23     NAME FLOAT UNIQUE AUTO_INCREMENT,
 24     seat INT 
 25 
 26 
 27 );
 28 TRUNCATE TABLE tab_identity;
 29 
 30 
 31 INSERT INTO tab_identity(id,NAME) VALUES(NULL,"john");
 32 INSERT INTO tab_identity(NAME) VALUES("lucy");
 33 SELECT * FROM tab_identity;
 34 
 35 
 36 SHOW VARIABLES LIKE "%auto_increment%";
 37 
 38 
 39 SET auto_increment_increment=3;
 40 
 41 
 42 
 43 #TCL
 44 
123 
124 SHOW VARIABLES LIKE "autocommit";
125 SHOW ENGINES;
126 
127 #1.演示事务的使用步骤
128 
129 #开启事务
130 SET autocommit=0;
131 START TRANSACTION;
132 #编写一组事务的语句
133 UPDATE account SET balance = 1000 WHERE username="张无忌";
134 UPDATE account SET balance = 1000 WHERE username="赵敏";
135 
136 #结束事务
137 ROLLBACK;
138 #commit;
139 
140 SELECT * FROM account;
141 
142 
143 #2.演示事务对于delete和truncate的处理的区别
144 
145 SET autocommit=0;
146 START TRANSACTION;
147 
148 DELETE FROM account;
149 ROLLBACK;
150 
151 
152 
153 #3.演示savepoint 的使用
154 SET autocommit=0;
155 START TRANSACTION;
156 DELETE FROM account WHERE id=25;
157 SAVEPOINT a;#设置保存点
158 DELETE FROM account WHERE id=28;
159 ROLLBACK TO a;#回滚到保存点
160 
161 
162 SELECT * FROM account;