欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

MySQL数据库高级--事务、索引、视图、触发器

程序员文章站 2022-03-03 20:57:07
...

事务

1. 事务的定义：

事务是由一组DML语句组成，完成一个相对完整的功能,这一组DML语句要么全部执行，要么全部放弃执行。
MySQL 事务主要用于处理操作量大，复杂度高的数据

2. 事务四大特性

  1. 原子性（Atomicity，或称不可分割性）

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

  1. 一致性（Consistency）

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

  1. 隔离性（Isolation，又称独立性）

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

  1. 持久性（Durability）

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3.事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，*；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ U NCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

4. 事务处理方法
1）用 BEGIN, ROLLBACK, COMMIT来实现

   - BEGIN 开始一个事务
   -  ROLLBACK 事务回滚
   - COMMIT 事务确认

2）直接用 SET 来改变 MySQL 的自动提交模式
  - SET AUTOCOMMIT=0 禁止自动提交
  -  SET AUTOCOMMIT=1 开启自动提交

5. 三大范式

  • 第一范式：表的每个字段必须是不可分割的独立单元
  • 第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖
  • 第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系

索引

1. 定义：

索引在Mysql中是存储引擎用于快速找到记录的一种数据结构（BTree，平衡树），索引对于数据库的良好性能是非常关键的，尤其是当表中的数据量越来越大的时候，索引对于性能的影响愈发重要。
索引相当于字典的目录，如果查找某个字的时候可以通过目录，提高查询速度，否则需要翻阅字典的每一页。 使用索引的目的：提高查询速度。
索引特点：创建和维护索引会消耗很多时间与磁盘空间，但是查询速度大大提高。

2. 语法

语法1：创建表时，添加索引。

    create table 表名(
    字段名1 数据类型，
    字段名2 数据类型,
    ...，
    Index/key  [索引名称]  (字段名) 
    );

语法2：创建完表后，添加索引。

  create table 表名(
  字段名1 数据类型，
  字段名2 数据类型,
  ...， 
  );
  Alter table 表名 add   unique | primay key| index...索引种类  索引名(字段名)

3. 索引分类

  • 普通索引 就是一个普通的索引，可以为空，可以重复。

ALTER TABLE table_name ADD INDEX name_index(column);

  • 唯一索引 可以为空，不可以重复。

ALTER TABLE table_name ADD UNIQUE(column); # 添加唯一索引
ALTER TABLE table_name ADD column type UNIQUE; # 添加列并且添加索引。

  • 主键索引 不可以为空，不可以重复。

ALTER TABLE table_name ADD PRIMARY KEY(column);
只要是主键，就是索引。

  • 多列索引。

ALTER TABLE table_name ADD INDEX(column1,column2,column3);

4. 删除索引

Drop index 索引名 on 表名；
drop index index_name on table_name;

使用索引：
1、少量数据不使用索引
2、查询次数少不使用索引
3、查询要携带索引字段，通常放在select之后第一个
4、索引提高了查询的效率，占有跟多的资源。

注意：
索引不是越多越好。

视图

1. 定义：

视图就是一条SELECT语句执行后返回的结果集,
视图是一个虚拟表，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来*定义视图的查询所引用的表。

2. 作用
（1）简化用户的操作
关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作；
（2）对机密数据提供保护作用
不希望用户访问表中某些含敏感信息的列，比如salary…

3. 操作视图
（1）创建视图:
Create view 视图名称 as DQL语句(select …);
（2）删除视图:
Drop view 视图名称;

4. 更新视图

在MySQL中，更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据，所以通过视图更新时，都是转换到基本表来更新。

更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

不可更新的视图：

某些视图是可更新的。也就是说，可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含下述结构中的任何一种，那么它就是不可更新的：
· 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
· DISTINCT
· GROUP BY
· HAVING
· UNION
· 位于选择列表中的子查询
· Join
· FROM子句中的不可更新视图
· WHERE子句中的子查询，引用FROM子句中的表。

注意:

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

触发器 （trigger）

1. 定义：

触发器（trigger）：监视某种情况，并触发某种操作，它的执行是由事件来触发的，例如当对一个表进行操作（ insert，delete， update）时就会**它执行。

触发器经常用于加强数据的完整性约束和业务规则等。
触发器创建语法四要素：

  1. 监视地点(table)
  2. 监视事件(insert/update/delete)
  3. 触发时间(after/before)
  4. 触发事件(insert/update/delete)

2. 触发器基本用法

  1. 创建触发器：
Create trigger trigger_name trigger_time trigger_event on tb_name for each row trigger_stmt;

  • trigger_time ：触发器的触发时机，可以为before（在检查约束前触发）
    或after（在检查约束后触发）；

  • trigger_event：是触发器的触发事件，包括insert、update和delete；

  • tb_name： 表示建立触发器的表名，就是在哪张表上建立触发器

  • trigger_stmt： 触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句；

  • FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器。

-- 创建触发器
-- 修改结束符

delimiter $ //修改结束符

create TRIGGER tg1 after insert on o for each row 
begin 
update g set num = num-3 where id = 1;
end $

delimiter ;//恢复结束符

insert into o(gid,much) VALUES(1,3);

  1. 删除触发器：
    Drop trigger 触发器名称。

  2. new 和old 的使用：
    MySQL数据库高级--事务、索引、视图、触发器NEW.columnname：新增行的某列数据
    OLD.columnname：删除行的某列数据

  3. before 和after 的区别：

  • after是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作；也就是说先插入订单记录，再更新商品的数量；
  • before是先完成触发，再增删改，触发的语句先于监视的增删改，我们就有机会判断，修改即将发生的操作；

上一篇： Java数据库篇8——索引、视图、存储过程、触发器

下一篇： Linux驱动开发常用调试工具---之内存读写工具devmem

推荐阅读