数据库事务，视图，触发器，索引，存储过程，函数，三大范式

事务 Transaction

事务是逻辑上的一组操作，要么都执行，要么都不执行

事务方法

• 开启事务：start transaction
• 提交事务：commit
• 回滚事务：rollback

事务的特性：ACID

• 原子性(Atomicity)：事务中包含的操作，要么都成功执行，要么都执行失败。
• 一致性(Consistency)：数据库总数从一个一致性的状态转换到另一个一致性状态。（总数总保持不变）
• 隔离性(Isolation)：一个事务执行的操作不对其他事务内部数据和操作造成影响，并发执行的事务相互独立。
• 持久性(Durability)：事务提交后，对数据库中的数据的改变是永久性的，不会因系统崩溃而丢失。

事务的并发时的状态

• 脏读：一个事务正在访问一个数据时，另一个事务修改了这同一个数据而为提交时，之前的事务读取到了修改后的数据，即：“脏数据”。

一个线程中的事务读到了另外一个线程事务中未提交的update数据.

• 不可重复读：一个事务正在访问一个数据时，另一个事务修改了这个数据，在提交后，之前的事务读取到的数据跟修改前的数据不同。

一个线程中的事务读到了另外一个线程事务中已经提交的update的数据.

• 虚度(幻读)：一个事务正在访问一个表时，另一个数据插入一条数据，之前的事务再次读时，比之前多了一条数据。

一个线程中的事务读到了另外一个线程事务中已经提交的insert的数据.

注意点：

MySQL默认的隔离级别：repeatable read

ORACLE默认的隔离级别：read committed

查询事务的隔离级别

mysql 5
select @@session.tx_isolation;//查询当前会话事务隔离级别
select @@global.tx_isolation;//查询全局会话事务隔离级别

mysql 8
select @@session.transaction_isolation;//查询当前会话事务隔离级别
[email protected]@global.transaction_isolation;//查询全局会话事务隔离级别
select @@transaction_isolation;//查询事务隔离级别

设置事务的隔离级别

set session transaction isolation level 隔离登记;

事务隔离级别

• read uncommitted(读取未提交)：最低隔离级别，允许读取尚未提交的数据变更，可能会导致脏读，不可重复读，幻读。
• read committed(读取提交)：允许读取已提交的数据变更，防脏读，但是还可能发生不可重复读，幻读
• repeatable read(可重复度)：对同一数据读取的结果始终不变，除非是本身事务所修改。否则事务结束前，数据都不变，事务结束后，读取的数据会改变，防脏读，不可重复读，幻读仍可能发生
• serializable(可串行化)：最高隔离级别，所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，执行效率低，可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

视图 View

create [algorithm = {undefined | merge | temptable}]
View 视图名
AS
select 查询语句

--删除视图
drop View 视图名;

• algorithm 表示视图选择的算法。
• undefined 表示自动选择算法
• merge 表示将使用视图的语句与视图定义合并起来，使得视图的某一个部分取代语句对应的部分。
• temptable 表示将视图结果存入临时表，然后使用临时表执行语句
• with check option 表示更新视图时要保证在该视图的权限范围内。
  简单来说可以这么解释：通过视图进行的修改，必须也能通过该视图看到修改后的结果。

触发器 Trigger

create trigger 触发器名
after/before insert/update/delete on 表名
for each row
begin
	sql语句；
end;

sql语句操作;

-- 删除触发器
drop trigger 触发器名;

示例：

create trigger tg4
after update on tbl_order
for each row
begin

 update goods set num = num + old.much-new.much where id= old.gid/new.gid;
end;

-- 会发现商品1的数量又变为8了
update tbl_order set much = 4 where oid= 4;

• New：范围：可在insert、update触发器中使用
• Old： 范围：可在delete、update触发器中使用

索引 Index

CREATE TABLE table_name [col_name data type]
[unique|fulltext] [index|key] [index_name](col_name[length])[asc|desc]

• unique|fulltext为可选参数，分别表示唯一索引、全文索引
• index和key为同义词，两者作用相同，用来指定创建索引
• index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
• col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
• asc或desc指定升序或降序的索引值存储

#查看:
show indexes from `表名`;
#或
show keys from `表名`;
 
#删除
alter table `表名` drop index 索引名;

###普通索引

（1）直接创建索引

CREATE INDEX index_name ON table_name(col_name);

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

（3）创建表的时候同时创建索引

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL,
    PRIMARY KEY (`id`),
    INDEX index_name (title(255))
);

（4）删除索引

DROP INDEX index_name ON table_name;
或者
alter table `表名` drop index 索引名;

###复合索引（组合索引）

复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

（1）创建一个复合索引

create index index_name on table_name(col_name1,col_name2,...);

唯一索引

唯一索引：唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值）。

（1）创建唯一索引

# 创建单个索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

# 创建多个索引
CREATE UNIQUE INDEX index_name on table_name(col_name,...);

（2）创建表的时候直接指定索引

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    UNIQUE index_name_unique(title)
)

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

（1）主键索引(创建表时添加)

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`)
)

全文索引

创建表的适合添加全文索引

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` text  NOT NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
     PRIMARY KEY (`id`),
    FULLTEXT (content)
)

（2）直接创建索引

CREATE FULLTEXT INDEX index_fulltext_content ON table_name(col_name)

存储过程

​ 存储过程是一组可编程的函数，是为了完成特定的SQL语句集，用户可通过存储过程的名字并给参数赋值来调用执行。

创建存储过程

create procedure 过程名(in/out/inout 参数名 参数类型)
begin
	sql语句 --> 一段业务逻辑
	可用sql语法
end;

create procedure demo_inout_parameter(inout p_inout int)
begin
 select p_inout;
 set p_inout=2;
 select p_inout;
end;

-- 调用存储过程
set @p_inout=1;
call demo_inout_parameter(@p_inout);
select @p_inout;

drop procedure 存储过程名

IN：参数值可在存储过程改变(set)，用select 变量名 来查看参数值。该参数值无法被返回。

OUT：参数值可在存储过程改变，并可返回

INOUT：调用时指定，并且可被改变和返回

函数

 -- 如果有这个函数，就删除
drop function if exists 函数名;

-- 创建一个无参的函数
create function hello () 
 -- 设置函数的返回类型
returns 参数类型，如：varchar (255)
begin-- 函数头
    -- 中间的是函数体
    return '一个简单的mysql函数'; -- 函数的返回值
end; -- 函数结尾

--调用函数
select hello();   -- select 函数名

存储过程 和 函数的区别

• 本质上没区别，函数只能通过return语句返回单个值或表对象

  ​ 存储过程不允许使用return，但可通过out参数返回多个值

  ​ 函数可嵌入SQL中使用，可以在select中调用；存储过程不行

• 函数只能是in类型，存储过程可以in/out/inout类型

数据库三大范式

• 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。

  **列不能再分 ** 要求属性具有原子性，不可再分解；

• 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。即：一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中

  满足范式一下，非主属性完全依赖于码，消除部分依赖。

• 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.每一列数据都和主键直接相关，而不能间接相关。

  例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。

  满足范式二下，消除传递依赖