存储对象(MySQL笔记第五期)

p.s.这是萌新自己自学总结的笔记，如果想学习得更透彻的话还是请去看大佬的讲解

目录 视图视图的检查选项视图的更新及作用 存储过程变量系统变量用户定义变量局部变量 IF判断、casewhile、pereat、loop游标(cursor)条件处理程序(handler) 存储函数触发器

-- 表结构 create table worker( name char(3) comment '姓名', phone char(11) comment '手机号', age tinyint unsigned comment '年龄', profession varchar(4) comment '职称', gender char(1) comment '性别', email varchar(16) comment '邮箱地址' ); insert into worker values ('张三',123456,18,'经理','男','1122@123 '), ('李四',696587,20,'员工','女','2265@125 '), ('王五',642681,19,'员工','女','123365@123 '), ('张三',125462,40,'保安','男','1652@125 ');

---

---

视图

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

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

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

修改视图： 方法一：CREATE OR REPLACE VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH | CASCADED | LOCAL | CHECK | OPTION]; 方法二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH | CASCADED | LOCAL | CHECK | OPTION];

删除视图：DROP VIEW [IF EXISTS] 视图名称;

举例

-- 创建 create or replace view wok_v_1 as select name,age from worker where age>18; -- 查询 show create view wok_v_1; select * from wok_v_1; -- 修改 create or replace view wok_v_1 as select name,age,phone from worker where age>18; -- 删除 drop view wok_v_1; 视图的检查选项 create or replace view wok_v_1 as select name,age from worker where age>18; -- 插入数据(实际上是将数据插入视图对应的表中) insert into wok_v_1 values ('赵六',24); -- 插入数据,但视图中查不到，因为有where限制 insert into wok_v_1 values ('赵六',17); -- 为了避免这种插入数据但视图中未显示的情况，我们可以创建视图时加上检查选项 create or replace view wok_v_1 as select name,age from worker where age>18 with cascaded check option ; -- 此时检查选项就会阻止我们插入数据并提示 insert into wok_v_1 values ('赵六',17);

当使用WITH CHECK OPTION子句创建视图，MySQL会通过视图检查正在更改的每个行，例如插入、更新、删除，以使其符合视图的定义。MySQL允许基于另一个视图(通常称为依赖视图)创建视图 ，它还会检查依赖视图中的规则以保持唯一性，为了确定检查的范围，mysql提供了两个选项：CASCADED和LOCAL，默认值为CASCADED . 。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。 . CASCADED和LOCAL的区别 . CASCADED . LOCAL

视图的更新及作用

要是视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含一下任何一项，则该视图不可更新 1.聚合函数或窗口函数 2.DISTINCT 3.GROUP BY 4.HAVING 5.UNION或UNION ALL …

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

视图使用场景举例

存储过程

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

特点： 封装、复用 可以接受参数、也可以返回数据 减少网络交互、效率提升

创建语法

CREATE PROCEDURE 存储过程名称([参数列表]) BEGIN SQL语句 END;

调用：CALL 名称 ([参数列表])；

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

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

举例

-- 查询所有系统变量 show variables; show session variables like 'auto%'; show global variables like 'auto%'; select @@autocommit; select @@session.autocommit; -- 设置 set session autocommit=1;

在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符

变量 系统变量

系统变量：由MySQL服务器提供，不是用户定义的，属于服务器层面，分为全局变量(GLOBAL)、会话变量(SESSION) 一个查询控制台可看作一个会话

查看所有系统变量：SHOW [SESSION | GLOBAL] VARIABLES; 模糊匹配查找：SHOW [SESSION | GLOBAL] VARIABLES LIKE '...'; 查看指定变量的值：SELECT @@[SESSION | GLOBAL] 系统变量名;

设置系统变量：SET [SESSION | GLOBAL] 系统变量名 = 值; 设置系统变量：SET @@[SESSION | GLOBAL] 系统变量名 = 值;

注意事项： 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量 mysql服务器重新启动之后，所设置的全局参数会失效

用户定义变量

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

赋值 SET @变量名 = 值; SET @变量名 := 值;（推荐） SELECT @变量名 := 值; SELECT 字段名 INTO @变量名 FROM 表名;

使用 SELECT @变量名;

举例

-- 赋值 set @myName = '张三'; set @myAge := 18; #(推荐) set @myGender := '男',@myHobby:= '玩游戏'; select @myColor = 'green'; select phone into @myPhone from worker where age=18; -- 使用 select @myAge,@myName,@myPhone;

注意：用户定义的变量无需对其声明或初始化，只不过获取到的值为NULL

局部变量

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

声明：DECLARE 变量名 变量类型 [DEFAULT ...];(变量类型就是字段类型)

赋值 SET 变量名 = 值; SET 变量名 := 值;（推荐） SELECT 字段名 INTO 变量名 FROM 表名;

举例

create procedure p2() begin declare wok_count int default 0; select count(*) into wok_count from worker; select wok_count; end; call p2(); IF判断、case

if 不含参数

-- 根据定义的年龄age变量，判定当前年龄对应的阶段 -- age>=40:中年 -- 20<=age<40:壮年 -- age<20:青年 create procedure p3() begin declare age tinyint default 25; declare result varchar(10); if age >=40 then set result := '中年'; elseif age>=20 then set result := '壮年'; else set result := '青年'; end if; select result; end; call p3();

有参数

-- 根据传入的年龄age参数，判定当前年龄对应的阶段，并返回 -- age>=40:中年 -- 20<=age<40:壮年 -- age<20:青年 create procedure p4(in age tinyint,out result varchar(10)) begin if age >=40 then set result := '中年'; elseif age>=20 then set result := '壮年'; else set result := '青年'; end if; end; call p4(15,@result);#使用一个自定义变量来接收返回的result值 select @result; #-------------------------------------------------------------------- -- 将传入的200分制分数转换为百分制 create procedure p5(inout score double) begin set score := score*0.5; end; set @score = 111; call p5(@score);#@score先传进p5进行转换后再被p5传出来 select @score;

---

case

#根据传入的月份判断月份所属的季度 -- 1-3：第一季度 -- 以此类推 -- 第一种case create procedure p1(in month tinyint) begin declare result varchar(20); case when month in (1,2,3) then set result := '第一季度'; when month in (4,5,6) then set result := '第二季度'; when month in (7,8,9) then set result := '第三季度'; when month in (10,11,12) then set result := '第四季度'; else set result := '非法参数'; end case; select concat('输入月份为',month,'所属季度为',result); end; call p1(3); while、pereat、loop

while

# 计算1到n的和 create procedure p2(in n int) begin declare total int default 0; while n>0 do set total := total + n; set n := n -1; end while; select total; end; call p2(100);

repeat repeat是有条件的循环控制语句，当满足条件时就退出循环，即 先执行一次逻辑，然后判定逻辑是否满足，如果满足则退出，如果不满足继续循环

# 计算1到n的和 create procedure p3(in n int) begin declare total int default 0; repeat set total := total + n; set n:= n-1; until n<=0 end repeat; select total; end; call p3(10);

loop

loop实现简单的循环，如果不在SQL逻辑中增减退出循环的条件，可以用其来实现简单的死循环，loop可以配合一下两个语句来使用 LEAVE：配合循环来用，退出循环 ITERATE：必须用在循环中，作用就是跳过循环剩下的语句，直接进入下一循环

举例

# 计算1到n的和 create procedure p4(in n int) begin declare total int default 0; sum:loop if n<=0 then leave sum; end if; set total := total + n; set n := n - 1; end loop sum; select total; end; call p4(10); #-------------------------------------------------- # 计算1到n的偶数和 create procedure p5(in n int) begin declare total int default 0; sum:loop if n<=0 then leave sum; end if; if n%2 = 1 then set n := n - 1; iterate sum; end if; set total := total + n; set n := n - 1; end loop sum; select total; end; call p5(10); 游标(cursor)

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理；游标的使用包括游标的声明、OPEN、FETCH、CLOSE

声明游标：DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标：OPEN 游标名称;

获取游标记录：FETCH 游标 INTO 变量,变量...;

关闭游标：CLOSE 游标名称;

举例

#根据传入的参数userAge查询worker表中年龄小于等于userAge的用户姓名及职位，并将这两个字段插入到创建的一张新表中 #声明游标、存储查询结果集 #创建表结构 #开启游标 #获取游标中的记录 #插入数据到新表中 #关闭游标 create procedure p1(in userAge tinyint) begin #要在游标声明前声明 declare uname varchar(3); declare upro varchar(4); declare u_cursor cursor for select name,profession from worker where age<=userAge; drop table if exists wok_user_pro; create table if not exists wok_user_pro( id int primary key auto_increment, name varchar(3), profession varchar(4) ); open u_cursor; while true do fetch u_cursor into uname,upro; insert into wok_user_pro values (null,uname,upro); end while; close u_cursor; end; #drop procedure p1; call p1(40);

由于while语句是死循环，所以会报错； 于是我们要用到下面的知识

条件处理程序(handler)

条件处理程序(handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤

声明：DECLARE 条件处理程序类型 HANDLER FOR 执行程序条件 SQL语句

条件处理程序类型有两种：CONTINUE:继续执行当前程序；EXIT：终止执行当前程序

执行程序条件： SQLSTATE sqlstate_value:状态码 SQLWARING:所有以01开头的SQLSTATE代码的简写 NOT FOUND：所有以02开头的SQLSTATE代码的简写 SQLEXCEPTION:所有没有被SQLWARING或NOT FOUND捕获的SQLSTATE代码的简写

即

#根据传入的参数userAge查询worker表中年龄小于等于userAge的用户姓名及职位，并将这两个字段插入到创建的一张新表中 #声明游标、存储查询结果集 #创建表结构 #开启游标 #获取游标中的记录 #插入数据到新表中 #关闭游标 create procedure p1(in userAge tinyint) begin #要在游标声明前声明 declare uname varchar(3); declare upro varchar(4); declare u_cursor cursor for select name,profession from worker where age<=userAge; declare exit handler for sqlstate '02000' close u_cursor; drop table if exists wok_user_pro; create table if not exists wok_user_pro( id int primary key auto_increment, name varchar(3), profession varchar(4) ); open u_cursor; while true do fetch u_cursor into uname,upro; insert into wok_user_pro values (null,uname,upro); end while; close u_cursor; end; #drop procedure p1; call p1(40); 存储函数

存储函数即有返回值的存储过程，存储函数的参数只能是IN类型的 创建语法

CREATE FUNCTION 存储过程名称([参数列表]) RETURNS TYPE [characteristic] BEGIN SQL语句 RETURN ...; END;

characteristic(存储参数特性)说明： DETERMINISTIC:相同的输入参数总是产生相同的结果 NO SQL：不包含SQL语句 READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

举例

#从1到n的和 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 触发器名 BEFORE/AFTER INSERT/UPDATE/DELETE ON 表名 FOR EACH ROW -- 行级触发器 BEGIN 逻辑实现 END;

查看：SHOW TRIGGERS;

删除:DROP TRIGGER [数据库名(没有指定默认为当前数据库)] 触发器名;

举例

create table worker( name char(3) comment '姓名', phone char(11) comment '手机号', age tinyint unsigned comment '年龄', profession varchar(4) comment '职称', gender char(1) comment '性别', email varchar(16) comment '邮箱地址' ); insert into worker values ('张三',123456,18,'经理','男','1122@123 '), ('李四',696587,20,'员工','女','2265@125 '), ('王五',642681,19,'员工','女','123365@123 '), ('张三',125462,40,'保安','男','1652@125 '); create table user_logs( id int(11) not null auto_increment primary key , operation varchar(20) not null comment '操作类型', operate_time datetime not null comment '操作时间', operate_id varchar(11) not null comment '操作id', operate_params varchar(500) comment '操作参数' ) comment '日志表'; -- 定义触发器 -- 插入数据触发器 create trigger wok_insert_trigger after insert on worker for each row begin insert into user_logs(id, operation, operate_time, operate_id, operate_params) values (null,'insert',now(),new.name,new.phone); end; -- 查看 show triggers ; -- 插入数据 insert into worker values ('张律',126662,34,'律师','男','2652@125 '); -- 修改数据触发器 create trigger wok_update_trigger after update on worker for each row begin insert into user_logs(id, operation, operate_time, operate_id, operate_params) values (null,'update',now(),new.name,new.phone); end; -- 删除数据触发器 create trigger wok_delete_trigger after delete on worker for each row begin insert into user_logs(id, operation, operate_time, operate_id, operate_params) values (null,'delete',now(),old.name,old.phone); end;