Mysql的学习6____事物，索引，备份，视图，触发器

1.Mysql事务：

就是将一组的SQL语句放在一个批次去执行，要是一条语句出错，该批次的SQL语句都会取消执行。

Mysql事物处理只支持InnoDB和BDB数据表类型。

1.1 事物的ACID原则：

原子性（Atomic）：

事物中的SQL语句要么全部执行，要么全不执行，不可能停滞在中间的某个状态，若在执行中发生了错误，会进行事物的回滚（RollBack）即从事物开始处重新执行，就像从来没有执行过一样。

一致性（Consist）：

一致性是指事务必须使得数据库从一个一致性状态转变到另一个一致性状态，也就是说事务在执行前，执行后都必须处于一致性状态。

拿转账转账来说，开始时A,B账户上共有100元，然后A,B之间无论进行怎样的转账操作，结束后A,B账户上还是有100元，这就是一致性。

隔离性（Isolated）：

当多个用户并发的访问数据库，比如都操作一张表，数据库给每个用户开启的事物之间不能互相干扰，多个并发事物要相互隔离。

持久性（Durability）：

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

1.2 Mysql事物的实现方法：

1/* 使用set语句来改变自动提交模式 */
2SET autocommit = 0;   /*关闭*/
3SET autocommit = 1;   /*开启*/
4
5/* 注意
6    1.MySQL中默认是自动提交
7    2.使用事务时应先关闭自动提交
8*/
9
10/*开始一个事务,标记事务的起始点*/
11START TRANSACTION   
12
13/*提交一个事务给数据库*/
14COMMIT
15
16/*将事务回滚,数据回到本次事务的初始状态*/
17ROLLBACK
18
19/*还原MySQL数据库的自动提交*/
20SET autocommit =1;
21
22-- 保存点
23    SAVEPOINT 保存点名称 -- 设置一个事务保存点
24    ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
25    RELEASE SAVEPOINT 保存点名称 -- 删除保存点

1.3 Mysql事物的处理步骤：

首先关闭自动提交（set autocommit = 0）-----》开始一个事务，标记事务的起始点（start transaction）-----》提交事务（commit）/ 回滚（rollback）

还原Mysql数据库的自动提交（set autocommit = 1）

1.4 数据库事务的实例：

1/*
2课堂测试题目
3
4A在线买一款价格为500元商品,网上银行转账.
5A的银行卡余额为2000,然后给商家B支付500.
6商家B一开始的银行卡余额为10000
7
8创建数据库shop和创建表account并插入2条数据
9*/
10
11CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci; 
12USE `shop`; 
13
14CREATE TABLE `account` (
15  `id` INT(11) NOT NULL AUTO_INCREMENT,
16  `name` VARCHAR(32) NOT NULL,
17  `cash` DECIMAL(9,2) NOT NULL,
18  PRIMARY KEY (`id`)
19) ENGINE=INNODB DEFAULT CHARSET=utf8
20
21INSERT INTO account (`name`,`cash`)
22VALUES('A',2000.00),('B',10000.00)
23
24# 转账实现
25SET autocommit = 0;
26START TRANSACTION;
27UPDATE account SET cash=cash-500 WHERE `name`='A';
28UPDATE account SET cash=cash+500 WHERE `name`='B';
29COMMIT;
30# rollback;
31SET autocommit = 1;

2.数据库的索引：

2.1索引的作用：

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

2.2索引的分类：

• 主键索引（Primary key）
• 唯一索引（Unique）
• 常规索引（Index）
• 全文索引（FullText）

主键索引：

主键：某个属性组可以唯一的标识一条记录。

 特点：

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

唯一索引：

作用：避免同一个表中，数据列中值的重复

和主键索引的区别：主键索引只能有一个，唯一索引可以有多个

常规索引：

作用：快速定位特定数据

注意：

• INDEX和KEY都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多的常规索引，影响数据的插入，删除，修操作

全文索引：

作用：快速定位特定数据

注意：

• 只能用于MyISAM类型的数据表
• 只能用于 varchar，text，char数据列类型
• 适合大型数据集

2.3创建 / 删除索引：

1/*
2#方法一：创建表时
3    　　CREATE TABLE 表名 (
4                字段名1  数据类型 [完整性约束条件…],
5                字段名2  数据类型 [完整性约束条件…],
6                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
7                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
8                );
9
10
11#方法二：CREATE在已存在的表上创建索引
12        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
13                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
14
15
16#方法三：ALTER TABLE在已存在的表上创建索引
17        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
18                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
19                             
20                             
21#删除索引：DROP INDEX 索引名 ON 表名字;
22#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
23
24
25#显示索引信息: SHOW INDEX FROM student;
26*/
27
28/*增加全文索引*/
29ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`); 
30
31/*EXPLAIN : 分析SQL语句执行性能*/
32EXPLAIN SELECT * FROM student WHERE studentno='1000';
33
34/*使用全文索引*/
35EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

2.4索引的两大类型：hash 和 btree

hash：查询单条快，查询范围慢；

btree：b+树，层数越多，数据量指数级增长，我们就用它，因为innodb默认支持他

1#我们可以在创建上述索引的时候，为其指定索引类型，分两类
2hash类型的索引：查询单条快，范围查询慢
3btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
4
5#不同的存储引擎支持的索引类型也不一样
6InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
7MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
8Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
9NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
10Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

2.5索引的准则：

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

3.Mysql的备份：

数据库备份的重要性：保证重要数据不会丢失，方便数据的转移

Mysql数据库备份的方法：Mysqldump备份工具；数据库管理工具 eg：SQLyog；直接拷贝数据库文件和相关配置文件。

4.视图：

1/* 视图 */ ------------------
2什么是视图：
3    视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
4    视图具有表结构文件，但不存在数据文件。
5    对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
6    视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。
7
8-- 创建视图
9CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
10    - 视图名必须唯一，同时不能与表重名。
11    - 视图可以使用select语句查询到的列名，也可以自己指定相应的列名。
12    - 可以指定视图执行的算法，通过ALGORITHM指定。
13    - column_list如果存在，则数目必须等于SELECT语句检索的列数
14
15-- 查看结构
16    SHOW CREATE VIEW view_name 
17
18-- 删除视图
19    - 删除视图后，数据依然存在。
20    - 可同时删除多个视图。
21    DROP VIEW [IF EXISTS] view_name ...
22
23-- 修改视图结构
24    - 一般不修改视图，因为不是所有的更新视图都会映射到表上。
25    ALTER VIEW view_name [(column_list)] AS select_statement
26
27-- 视图作用
28    1. 简化业务逻辑
29    2. 对客户端隐藏真实的表结构
30
31-- 视图算法(ALGORITHM)
32    MERGE        合并
33        将视图的查询语句，与外部查询需要先合并再执行！
34    TEMPTABLE    临时表
35        将视图执行完毕后，形成临时表，再做外层查询！
36    UNDEFINED    未定义(默认)，指的是MySQL自主去选择相应的算法。

5.触发器：

1/* 锁表 */
2表锁定只用于防止其它客户端进行不正当地读取和写入
3MyISAM 支持表锁，InnoDB 支持行锁
4-- 锁定
5    LOCK TABLES tbl_name [AS alias]
6-- 解锁
7    UNLOCK TABLES
8
9
10/* 触发器 */ ------------------
11    触发程序是与表有关的命名数据库对象，当该表出现特定事件时，将激活该对象
12    监听：记录的增加、修改、删除。
13
14-- 创建触发器
15CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
16    参数：
17    trigger_time是触发程序的动作时间。它可以是 before 或 after，以指明触发程序是在激活它的语句之前或之后触发。
18    trigger_event指明了激活触发程序的语句的类型
19        INSERT：将新行插入表时激活触发程序
20        UPDATE：更改某一行时激活触发程序
21        DELETE：从表中删除某一行时激活触发程序
22    tbl_name：监听的表，必须是永久性的表，不能将触发程序与TEMPORARY表或视图关联起来。
23    trigger_stmt：当触发程序激活时执行的语句。执行多个语句，可使用BEGIN...END复合语句结构
24
25-- 删除
26DROP TRIGGER [schema_name.]trigger_name
27
28可以使用old和new代替旧的和新的数据
29    更新操作，更新前是old，更新后是new.
30    删除操作，只有old.
31    增加操作，只有new.
32
33-- 注意
34    1. 对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。
35
36
37-- 字符连接函数
38concat(str1[, str2,...])
39
40-- 分支语句
41if 条件 then
42    执行语句
43elseif 条件 then
44    执行语句
45else
46    执行语句
47end if;
48
49-- 修改最外层语句结束符
50delimiter 自定义结束符号
51    SQL语句
52自定义结束符号
53
54delimiter ;        -- 修改回原来的分号
55
56-- 语句块包裹
57begin
58    语句块
59end
60
61-- 特殊的执行
621. 只要添加记录，就会触发程序。
632. Insert into on duplicate key update 语法会触发：
64    如果没有重复记录，会触发 before insert, after insert;
65    如果有重复记录并更新，会触发 before insert, before update, after update;
66    如果有重复记录但是没有发生更新，则触发 before insert, before update
673. Replace 语法 如果有记录，则执行 before insert, before delete, after delete, after insert