MySQL视图、存储过程、触发器入门使用

最新推荐文章于 2023-09-18 22:48:45 发布

CV高级工程师001

最新推荐文章于 2023-09-18 22:48:45 发布

阅读量507

点赞数

分类专栏： Java 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_46745445/article/details/127735606

版权

Java 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

介绍

视图(View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的5QL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

语法

-- 创建视图
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

-- 查询
-- 查看创建视图语句
SHOW CREATE VIEW 视图名称;
-- 查看视图数据
SELECT * FROM 视图名称.......;

-- 修改
-- 方式一
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)]AS SELECT语句「WTH [ CASCADED | LOCAL ] CHECK OPTION]
-- 方式二
ALTER VEW 视图名称[(列名列表)]AS SELECT语句[WITH[ CASCADED | LOCAL ] CHECK OPTION]

-- 删除
drop VIEW [is exists] 视图名称

作用

简单
- 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
安全
- 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
数据独立
- 视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据
库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

特点

封装，复用
可以接收参数，也可以返回数据
减少网络交互，效率提升

语法

-- 创建存储过程
CREATE PROCEDURE([参数])
BEGIN
	-- sql语句
END;

-- 调用
CALL 名称([参数]);

-- 查看
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM NFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='xxx'; 
-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

-- 删除存储过程
DROP PROCEDURE [IF EXISTS]存储过程名称;

变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。

-- 查看所有系统变量（默认是查询session会话级别系统变量）
show VARIABLES;
show GLOBAL VARIABLES;

-- 查看会话系统变量（模糊匹配）
show SESSION VARIABLES like 'auto%';
-- 查看全局系统变量（模糊匹配）
show GLOBAL VARIABLES like 'auto%';
-- 查看系统变量
select @@autocommit

-- 设置系统变量
set [SESSION|GLOBAL] 系统变量名 = 值
set @@[SESSION|GLOBAL] 系统变量名 = 值

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前
连接。

-- 自定义变量
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name :=expr [ @var_name ;= expr] ... ;
SELECT @var_name := expr [, @var_name := expr] ...;
SELECT 字段名 INTO @var_name FROM 表名;

set @str1 = 'java';
set @str2 := 'c#';
set @str3 := 'c++', @str4 = 'c--';
select @str5 := 'red';
select count(-5) into @str6 from student;

select @str1,@str2,@str3,@str4,@str5,@str6

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN …END块。

-- 局部变量
declare 变量名 变量类型[default...]

create procedure p2()
begin
	declare stuCount int default 0; -- 声明变量
	select count(*) into stuCount FROM student; -- 赋值
	select stuCount; -- 查询值
end;

call p2();

IF语法

CREATE PROCEDURE p3() 
BEGIN
	DECLARE score INT DEFAULT 58;
	DECLARE result VARCHAR(10);
	if
		score >= 90 THEN
			set result='优秀';
	elseif
		score >= 60 THEN
			set result='及格';
	else 
		set result = '不及格';
	END if;
SELECT result;
END;

call p3()

参数

类型	含义	备注
in	该类参数作为输入，也就是需要调用时传入值	默认
out	该类参数作为输出，也就是该参数可以作为返回值
inout	既可以作为输入参数，也可以作为输出参数

-- 语法
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT参数名参数类型]
BEGIN
	-- SQL语句
END;
                        
-- 示例
CREATE PROCEDURE p4(in score int, out result VARCHAR(10)) 
BEGIN
	if
		score >= 90 THEN
			set result='优秀';
	elseif
		score >= 60 THEN
			set result='及格';
	else 
		set result = '不及格';
	END if;
END;

call p4(98, @result);
select @result
                                              
-- 既是入参也是出参
CREATE PROCEDURE p5(inout score int) 
BEGIN
	set score := score * 0.5;
END;

set @score := 198;
call p5(@score);
select @score

循环

create procedure p6(in number int)
begin
	declare tatol int default 0;
	while number > 0 do
		set tatol := tatol + number;
		set number := number-1;
	end while;
	select tatol;
end;

call p6(100)

create procedure p7(in number int)
begin
	declare tatol int default 0;
	repeat 
		set tatol := tatol + number;
		set number := number-1;
	until number <= 0
	end repeat;
	select tatol;
end;

call p7(100)

loop

LOOP实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现简单的死循环，LOOP可以配合一下两个语句使用

LEAVE:配合循环使用，退出循环。
ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

create procedure p8(in nub int)
BEGIN
	declare total int default 0;
	
	xunhun:loop
		if nub <= 0 then
			leave xunhun;
		end if;
		
		set total := total + nub;
		set nub := nub - 1;
	
	end loop xunhun;
	
	select total;

END;

call p8(10)

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的

-- 存储函数
create function fun1(n int)
returns int deterministic
begin
	declare total int default 0;
	while n > 0 do
		set total := total + n;
		set n := n - 1;
	end while;
	
	return total;
end;

select fun1(100);

触发器

介绍

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

语法

-- 语法
CREATE TRIGGER trigger_name
BEFORE AFTER /INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt;
END;

-- 查看
SHOW TRIGGERS;

-- 删除
DROP TRIGGER [schema_name.]trigger_name; -- 如果没有指定schema_name，默认为当前数据库。

示例

-- 触发器
-- 需求:通过触发器记录user表的数据变更日志(user_logs)，包含增加，修改，删除;
-- 表结构
create table user_logs(
	id int(11) not null auto_increment,
	operation varchar(20) not null comment '操作类型 insert/update/delete',
	operate_time datetime not null comment '操作时间',
	operate_id int(11) not null comment '操作的ID',
	operate_params varchar(500) comment '操作参数',
	primary key(`id`)
)engine=innodb default charset=utf8;

-- 创建insert触发器
create trigger tb_stu_insert_trigger
after insert on student for each row
BEGIN
	insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES
	(null, 'insert', now(), new.sno, concat('插入的内容为：sno=', new.sno, ',sname=', new.sname, ',sage=', new.sage));
END;

-- 查看触发器
show TRIGGERS

-- 删除触发器
drop TRIGGER tb_stu_insert_trigger;

-- 往student添加数据，测试触发器是否将数据日志记录user_logs表 
insert into student(sno,sname,sage) VALUES('110','唐德鑫',19)

-- 创建update触发器
create trigger tb_stu_update_trigger
after UPDATE on student for each row
BEGIN
	insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES
	(null, 'update', now(), old.sno, concat('修改之前的内容为：sno=', old.sno, ',sname=', old.sname, ',sage=', 		old.sage,'修改之后的内容为：sno=', new.sno, ',sname=', new.sname, ',sage=', new.sage));
END;

update student set sname = 'tdx', sage = '20' where sno = '110'