数据库基本操作15-事务及索引

1.5 事务【transaction】

1. 事务是一个不可分割的执行单元
2. 事务作为一个整体要么一起执行，要么一起回滚

插入测试数据

mysql> create table bank(
    -> cardid char(4) primary key,
    -> money int
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bank values ('1001',1000),('1002',100);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

1.5.1 事务操作

开启事务：start transaction或begin [work]
提交事务：commit
回滚事务：rollback

例题：

mysql> delimiter //            # 更改定界符

mysql> start transaction;			# 开启事务
    -> update bank set money=money-100 where cardid='1001';
    -> update bank set money=money+100 where cardid='1002'  //
Query OK, 0 rows affected (0.00 sec)

mysql> commit //   # 提交事务

mysql> rollback //  # 回滚事务

思考：事务什么时候产生？什么时候结束？
答：开启的时候产生，提交事务或回滚事务都结束

脚下留心：只有innodb和BDB才支持事务，myisam不支持事务。

1.5.2 设置事务的回滚点

语法：

设置回滚点： savepoint 回滚点名
回滚到回滚点： rollback to 回滚点

例题：

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

mysql> insert into bank values ('1003',1000);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint aa;    # 设置回滚点 aa
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bank values ('1004',500);
Query OK, 1 row affected (0.00 sec)
 
mysql> savepoint bb;   # 设置回滚点bb
Query OK, 0 rows affected (0.00 sec)
 
mysql> rollback to aa;    # 回滚到aa点
Query OK, 0 rows affected (0.00 sec)

mysql> commit;    # 提交事务

mysql> select * from bank ;
+--------+-------+
| cardid | money |
+--------+-------+
| 1001   |   800 |
| 1002   |   200 |
| 1003   |  1000 |
+--------+-------+

1.5.3 事务的特性（ACID）

1. 原子性（Atomicity）：事务是一个整体，不可以再分，要么一起执行，要么一起不执行。

2. 一致性（Consistency）：事务完成时，数据必须处于一致的状态。

3. 隔离性（Isolation）：每个事务都是相互隔离的

4. 永久性（Durability）：事务完成后，对数据的修改是永久性的。

   1.6 索引【index】

索引的优点：查询速度快

索引的缺点：

1. 增、删、改（数据操作语句）效率低了

2. 索引占用空间

   1.6.1 索引的类型

3. 普通索引

4. 唯一索引（唯一键）

5. 主键索引：只要主键就自动创建主键索引，不需要手动创建。

6. 全文索引，搜索引擎使用，MySQL不支持中文的全文索引，我们通过sphinx去解决中文的全文索引。

   1.6.2 创建普通索引【create index】

语法：

create index [索引名] on 表名 （字段名）
alter table 表名 add index [索引的名称] （列名）

例题：

# 创建索引方法一
mysql> create index ix_stuname on stuinfo(stuname);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 创建索引方法二
mysql> alter table stuinfo add index ix_address (stuaddress);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 创建表的时候就添加索引
mysql> create table emp(
    -> id int,
    -> name varchar(10),
    -> index ix_name (name)   # 创建索引
    -> );
Query OK, 0 rows affected (0.00 sec)

1.6.3 创建唯一索引

语法一：create unique index 索引名 on 表名 （字段名）
语法二：alter table 表名 add unqiue [index] [索引的名称] （列名）
语法三：创建表的时候添加唯一索引，和创建唯一键是一样的。

例题

# 方法一：
mysql> create unique index UQ_stuname on stu(stuname);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 方法二：
mysql> alter table stu add unique UQ_address (stuaddress);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 方法三
mysql> create table stu2(
    -> id int,
    -> name varchar(20),
    -> unique UQ_name(name)
    -> );
Query OK, 0 rows affected (0.01 sec)

1.6.4 删除索引

语法

drop index 索引名 on 表名

例题

mysql> drop index ix_stuname on stuinfo;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

1.6.5 创建索引的指导原则

1. 该列用于频繁搜索
2. 改列用于排序
3. 公共字段要创建索引
4. 如果表中的数据很少，不需要创建索引。MySQL搜索索引的时间比逐条搜索数据的时间要长。
5. 如果一个字段上的数据只有几个不同的值，改字段不适合做索引，比如性别。