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

约束

非空约束

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 存储过程名;