mysl事务（innodb支持事务处理）
在Mysql控制台使用事务来操作
mysql> begin； ＃开始一个事务
mysql> insert into a (a) values(555);
mysql>rollback; 回滚 ， 这样数据是不会写入的
当然如果上面的数据没问题，就输入commit提交命令就行；
mysql> use oldboydb
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> begin; （开启事务）
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex |
+----+----------+---------------+------+
| 1 | ZhanYang | 2018-06-20 | NULL |
| 4 | LiuJia | 2018-05-30 | NULL |
| 8 | JiaLiu | 2018-06-20 | NULL |
| 9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
+----+----------+---------------+------+
8 rows in set (0.00 sec)
mysql> insert into student (name,register_date,sex) values("Alex","2016-05-21","M");
Query OK, 1 row affected (0.00 sec)
mysql> mysql> insert into student (name,register_date,sex) values("Jack","2016-05-21","M");
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex |
+----+----------+---------------+------+
| 1 | ZhanYang | 2018-06-20 | NULL |
| 4 | LiuJia | 2018-05-30 | NULL |
| 8 | JiaLiu | 2018-06-20 | NULL |
| 9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
| 14 | Alex | 2016-05-21 | M |
| 15 | Jack | 2016-05-21 | M |
+----+----------+---------------+------+
10 rows in set (0.00 sec)
mysql> rollback; （回滚）
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex |
+----+----------+---------------+------+
| 1 | ZhanYang | 2018-06-20 | NULL |
| 4 | LiuJia | 2018-05-30 | NULL |
| 8 | JiaLiu | 2018-06-20 | NULL |
| 9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
+----+----------+---------------+------+
8 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student (name,register_date,sex) values("ZhanXing","2018-05-21","M");
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (name,register_date,sex) values("XingYan","2018-05-21","M");
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex |
+----+----------+---------------+------+
| 1 | ZhanYang | 2018-06-20 | NULL |
| 4 | LiuJia | 2018-05-30 | NULL |
| 8 | JiaLiu | 2018-06-20 | NULL |
| 9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
| 16 | ZhanXing | 2018-05-21 | M |
| 17 | XingYan | 2018-05-21 | M |
+----+----------+---------------+------+
10 rows in set (0.00 sec)
mysql> commit; （从内存写入磁盘）
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex |
+----+----------+---------------+------+
| 1 | ZhanYang | 2018-06-20 | NULL |
| 4 | LiuJia | 2018-05-30 | NULL |
| 8 | JiaLiu | 2018-06-20 | NULL |
| 9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
| 16 | ZhanXing | 2018-05-21 | M |
| 17 | XingYan | 2018-05-21 | M |
+----+----------+---------------+------+
10 rows in set (0.00 sec)
MySQL索引：
创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构
ALTER mytable ADD INDEX [indexName] ON (username(length))
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
删除索引的语法
DROP INDEX [indexName] ON mytable;
mysql> mysql> show index from student; （查看索引）
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> create index index_name on student (name(32)); （创建索引）
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
| student | 1 | index_name | 1 | name | A | 10 | NULL | NULL | | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> drop index index_name on student; （删除索引）
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
唯一索引：
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。
如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);
mysql事务、索引
原文：http://blog.51cto.com/zhanx/2154926
