2020-08-28 Mysql2

事务

什么是事务

事务就是将一组SQL语句放在同一批次内去执行

如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行

MySQL事务处理只支持InnoDB和BDB数据表类型

事务的ACID原则

原子性(Atomic)

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性(Consist)

一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

隔离性(Isolated)

隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性(Durable)

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

（注意：mysql数据支持事务，但是要求必须是innoDB存储引擎）
mysql> create table bank(name varchar(20),money decimal(5,1))engine=innodb defau
lt charset=utf8;

mysql> inset into bank values('shaotuo',1000),('laohu',5000);

mysql> select*from bank;
+---------+--------+
| name    | money  |
+---------+--------+
| shaotuo | 1000.0 |
| laohu   | 5000.0 |
+---------+--------+

------没有成功“回滚”执行rollback
mysql> start transaction;     //开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> update bank set money=money+500 where name='shaotuo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update bank set moey=money-500 where name='laohu';
ERROR 1054 (42S22): Unknown column 'moey' in 'field list'
mysql> rollback;   //只要有一个不成功，执行rollback操作
Query OK, 0 rows affected (0.01 sec)

mysql> select*from bank;
+---------+--------+
| name    | money  |
+---------+--------+
| shaotuo | 1000.0 |
| laohu   | 5000.0 |
+---------+--------+
------成功之后 进行commit操作
mysql> start transaction;     //开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> update bank set money=money+500 where name='shaotuo';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update bank set money=money-500 where name='laohu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;  //两个都成功后执行commit（只要不执行commit，sql语句不会对真实的数据库造成影响）
Query OK, 0 rows affected (0.05 sec)

mysql> select*from bank;
+---------+--------+
| name    | money  |
+---------+--------+
| shaotuo | 1500.0 |
| laohu   | 4500.0 |
+---------+--------+

索引

索引的作用

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化.

分类

1. 主键索引 (Primary Key)
2. 唯一索引 (Unique)
3. 常规索引 (Index)
4. 全文索引 (FullText)

主键索引 主键 : 某一个属性组能唯一标识一条记录

特点 :

最常见的索引类型

确保数据记录的唯一性

确定特定数据记录在数据库中的位置

唯一索引 作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

主键索引只能有一个

唯一索引可能有多个

常规索引 作用 : 快速定位特定数据

注意 :

index 和 key 关键字都可以设置常规索引

应加在查询找条件的字段

不宜添加太多常规索引,影响数据的插入,删除和修改操作

全文索引 作用 : 快速定位特定数据

注意 :

只能用于MyISAM类型的数据表

只能用于CHAR , VARCHAR , TEXT数据列类型

适合大型数据集

索引准则

1. 索引不是越多越好
2. 不要对经常变动的数据加索引
3. 小数据量的表建议不要加索引
4. 索引一般应加在查找条件的字段

MySQL备份

数据库备份必要性

• 保证重要数据不丢失
• 数据转移

MySQL数据库备份方法

• mysqldump备份工具
• 数据库管理工具,如SQLyog
• 直接拷贝数据库文件和相关配置文件
• mysqldump客户端

作用 :

• 转储数据库
• 搜集数据库进行备份
• 将数据转移到另一个SQL服务器,不一定是MySQL服务器

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

可以-w携带备份条件

-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
　　source 备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

mysql 数据库的设计三范式

三范式

1NF:字段不可分;

2NF:有主键，非主键字段依赖主键;

3NF:非主键字段不能相互依赖;

解释:

1NF:原子性 字段不可再分,否则就不是关系数据库;

2NF:唯一性 一个表只说明一个事物;

3NF:每列都与主键有直接关系，不存在传递依赖;