时间戳

首页 > 技术文章 > MySQL约束事务视图和存储过程

MySQL约束事务视图和存储过程

IT_CH 2020-08-07 10:26 原文

约束

非空约束

not null

默认约束

default

主键约束

primary key

唯一约束

unique

检查约束

check mysql不支持检查约束，不报错但是无效果

外键约束

foreign key

CREATE TABLE EmpInfo(
   EmpID INT PRIMARY KEY AUTO_INCREMENT,	主键约束
   EmpName VARCHAR(20) NOT NULL,	非空约束
   EmpSex VARCHAR(2) NOT NULL DEFAULT '男',	默认约束
   EmpBirth DATETIME NOT NULL,	
   EmpTel VARCHAR(20) NOT NULL UNIQUE,	唯一约束
   DeptID VARCHAR(10) NOT NULL,
   CONSTRAINT FK_DeptID FOREIGN KEY(DeptID) REFERENCES DeptInfo(DeptID)	外键约束
)ENGINE=INNODB CHARSET=utf8 AUTO_INCREMENT=1001;

事务

事务的ACID属性

原子性：事务是一组不可再分的业务逻辑单元，要么全部执行，要么全部不执行

一致性：事务使数据从一个一致状态切换到另外一个一致的状态

隔离性：事务的执行不受其他事务的干扰

持久性：一个事务一旦提交则不能反悔

事务并发问题

脏读
读到了别人还未提交的数据

不可重复读（修改）
前后读取，数据内容不一致，操作过于频繁，每次拿到的都是新的数据，主要是修改引发的

幻读（插入/删除）
前后读取，数据的条数不一致，主要是插入和删除引起的

事务隔离级别

读未提交
会产生脏数据（脏读）

读已提交【SQLServer默认隔离级别】
解决了脏读问题，但是未解决不可重复读和幻读

可重复读【MySQL的默认隔离级别】
解决了脏读，不可重复读和幻读

串行读
会出现等待的情况

视图

(1)视图是一张虚拟的表，和普通标一样的使用方法
(2)视图只保存了SQL逻辑，但是没有保存查询结果

应用场景：
-- 多个地方用到同样的查询结果
-- 该sql查询语句比较复杂

视图的创建

语法：
create view 视图名
as
查询语句;

-- 视图的创建
create view myv1
as
select * from student;

select * from myv1 where score between 80 and 100;

视图的修改

语法1：
create or replace view 视图名
as
查询语句;

语法2：
alter view 视图名
as
查询语句;

-- 视图的修改
-- (1)
create or replace view myv1
as
select * from student where score between 80 and 100;

select * from myv1;

-- (2)
alter view myv1
as 
select * from student where score between 60 and 80;

select * from myv1;

视图的删除

语法：
drop view 视图名,视图名,……;

视图的查看

(1)desc 视图名;

(2)show create view 视图名;[可视化界面显示效果不好]
可以使用：show create view 视图名\G;[命令行使用]

存储过程

存储过程是一组预先编译好的SQL语句的集合（批处理语句）

好处：(存储过程类似于java中的方法)
(1)提高了代码的重用性
(2)简化操作
(3)减少编译次数，并且减少和数据库连接的次数，提高效率

存储过程的创建

语法：
create procedure 存储过程名(参数列表)
begin
	存储过程体(一组合法的SQL语句)
end

注意：
参数列表包含三部分：参数模式	参数名	参数类型
	例如：in name varchar(50)
	
参数模式：
in：输入，该参数需要调用放传入值
out：输出，改参数作为返回值
inout，既可以做输入也可以做输出，既可以传入值，也可以返回值

存储过程体：
(1)如果只有一句话，可以省略begin end
(2)多条SQL语句，每条结尾必须加分号;存储过程的结尾可以使用delimiter重新设置
语法：
delimiter 结束标记
案例
delimiter $

调用：call 存储过程名(实参列表);

-- 视图的创建
create view myv1
as
select * from student;

select * from myv1 where score between 80 and 100;

-- 视图的修改
-- (1)
create or replace view myv1
as
select * from student where score between 80 and 100;

select * from myv1;

-- (2)
alter view myv1
as 
select * from student where score between 60 and 80;

select * from myv1;

-- 存储过程的创建
-- (1)空参列表
delimiter $
create procedure myp1()
begin
	insert into student
	values(6,53),(7,88),(8,72);
end
$

call myp1();

-- (2)有参列表
delimiter $
create procedure myp2(in inId int)
begin
	select * from student where id = inId;	
end
$

call myp2(3);

-- 实例：创建存储过程，判断用户是否登录成功
delimiter $
create procedure myp3(in uname varchar(20),in upwd varchar(20))
begin
	declare result int default 0; # 声明并初始化
	
	select count(*) into result # 赋值
	from `user` u
	where u.uname = uname and u.upwd = upwds
	
	select if(result > 0, '成功', '失败')
end
$

call myp3('张三','123456');

-- (3)out参数
delimiter $
create procedure myp4(in inId int,out outScore int) 
begin
	select score into outScore
	from student 
	where id = inID;
end
$

call myp4(1,@outScore);	# @outScore是用户变量
select @outScore;

-- inout一样的用法就不举例了

存储过程的删除

drop procedure 存储过程名;
-- 不支持一次删除多个存储过程

存储过程的查看

desc 存储过程名; # 不支持，报错
show create procedure 存储过程名;

推荐阅读