5、事务，索引

事务：

事务用于处理操作量大，复杂的数据。如公司中处理离职人员的资料，需要删除基本资料，和与他相关的邮箱，帐号等众多信息，所有这些数据库的操作就构成一个事务。

事务具有原子性、稳定性、隔离性和可靠性四个特点。

事务支持insert、update、delete语句

使用：

begin；　　#开启事务

insert、update、delete数据库操作

rollback；　　#回滚，事务终止

commit；　　#提交，事务终止

mysql> select * from students;
+----+--------+--------+---------------------+----------+
| id | name   | gender | birthday            | isDelete |
+----+--------+--------+---------------------+----------+
|  1 | 鲤鱼   |       | 1991-01-01 00:00:00 |          |
|  4 | mike   |        | NULL                |          |
+----+--------+--------+---------------------+----------+
2 rows in set (0.00 sec)

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

mysql> delete from students where name='mike';
Query OK, 1 row affected (0.03 sec)

mysql> select * from students;
+----+--------+--------+---------------------+----------+
| id | name   | gender | birthday            | isDelete |
+----+--------+--------+---------------------+----------+
|  1 | 鲤鱼   |       | 1991-01-01 00:00:00 |          |
+----+--------+--------+---------------------+----------+
1 row in set (0.00 sec)

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

mysql> select * from students;
+----+--------+--------+---------------------+----------+
| id | name   | gender | birthday            | isDelete |
+----+--------+--------+---------------------+----------+
|  1 | 鲤鱼   |       | 1991-01-01 00:00:00 |          |
|  4 | mike   |        | NULL                |          |
+----+--------+--------+---------------------+----------+
2 rows in set (0.00 sec)

索引：

索引提高查询速度，分单列索引（一个索引对应一列）和组合索引（多列对应一个索引）

创建，查看索引：

create index  indexname  on  tablename（索引字段(length)）；　　#在index前加unique就是唯一索引

show index  from  tablename；

mysql> select * from students;
+----+--------+--------+---------------------+----------+
| id | name   | gender | birthday            | isDelete |
+----+--------+--------+---------------------+----------+
|  1 | 鲤鱼   |       | 1991-01-01 00:00:00 |          |
|  4 | mike   |        | NULL                |          |
+----+--------+--------+---------------------+----------+

mysql> create index sex on students(name);　　#创建索引，把创建的索引命名为sex
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment |
| name     | char(20) | NO   | MUL | NULL    |                |
| gender   | bit(1)   | YES  |     | b'1'    |                |
| birthday | datetime | YES  |     | NULL    |                |
| isDelete | bit(1)   | YES  |     | b'0'    |                |

mysql> create index se on students(gender);　　#创建索引
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment |
| name     | char(20) | NO   | MUL | NULL    |                |
| gender   | bit(1)   | YES  | MUL | b'1'    |                |
| birthday | datetime | YES  |     | NULL    |                |
| isDelete | bit(1)   | YES  |     | b'0'    |                |
+----------+----------+------+-----+---------+----------------+

mysql> show index from students;　　#查看索引，此时有三个索引。主键自动就是索引
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | sex      |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | se       |            1 | gender      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

删除索引：

drop index se on students;　　#se是索引名称

转载于:https://www.cnblogs.com/lybpy/p/8046146.html