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

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

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

事务 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)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中

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