以下笔记借鉴于黑马MYSQL视频，记录为个人学习笔记参考。

包含视图/存储过程/触发器等概念

目录

视图 view语法视图的检查选项视图更新作用 存储过程 procedure语法变量系统变量语法 用户自定义变量语法 局部变量语法 If语法 参数语法 case循环whilerepeatloop 游标语法 条件处理程序语法 存储函数 funcation语法 触发器 tirgger语法

视图 view

概述

视图(Viw)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

语法

创建视图

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;-- oralter view 视图名称 as select语句 [with [cascaded | local]] check option;

删除

drop view [if|exists] 视图名称

with cascaded/local check option

#创建视图create or replace view stu_v_1 as select id,name from student where id <20 with local check option;#查询视图select from stu_v_1;I#插入数据insert into stu_v_1 values(6,'Tom');#如果没有with cascaded check option将插入成功但视图查询不到数据，有with cascaded check option将插入失败insert into stu_v_1 values(30,'Tom');

视图的检查选项

当使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：

cascaded 和 local，默认为cascaded。

cascaded：

将检查当前操作是否满足条件和关联操作是否满足条件(插入数据要>=10且<=20)

local:

将检查当前操作是否满足条件，不检查关联条件

视图更新

要使视图可更新，视图中的行与基础表中的行之间**必须存在一对一的关系。**如果视图包含以下任何一项，则该视图不可更新：

聚合函数或窗口函数(SUM)、MIN)、MAXO、COUNT(O等)DISTINCTGROUP BYHAVINGUNION或者UNION ALL

作用

简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作，每次指定全部的条件。

安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

数据独立

视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程 procedure

概念

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

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

特点

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

语法

创建

create procedure 存储过程名([参数列表])begin-- sql语句;end;-- 在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符。delimiter $$create procedure 存储过程名([参数列表])begin-- sql语句;end$$

调用

call 存储过程名([参数列表])

查看

-- 查询指定数据库的存储过程和状态信息select * from information_schema.routines where routine_schema = 'xxx';-- 查询某个存储过程的定义show create procedure 存储过程名;

删除

drop procedure [if exists] 存储过程名;

变量

系统变量

系统变量是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服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/f中配置。

用户自定义变量

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

语法

赋值

set @名称 := 值 [,@名称 = 值]...;#例set @myname := 'itcast';select @名称 := 值 [,@名称:=值]...;#例select @mycolor := 'red',@love := 'blue';#将表中的查询结果赋值给变量select 字段名 into @名称 from 表名;select count(*) into @mycount from stu;

使用

select @名称;

注意

若不赋值，将为null

局部变量

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

语法

声明

declare 变量名 变量类型[default...];-- 变量类型为 int bigint char varchar date time 等数据库字段类型-- default 为默认值

赋值

set 变量名 = 值;set 变量名 := 值;select 字段名 into 变量名 from 表名;

举例

create procedure p2() begindeclare stu_count int default 0;select count(*)into stu_count from student;select stu_count;end;#查询存储过程call p2();

If

语法

if 条件1 then...elseif 条件2 then -- 可选...else; -- 可选...end if;

举例

根据定义的分数score变量，判定当前分数对应的分数等级。

score>=85分，等级为优秀。

score>=60分且score<85分，等级为及格。

score<60分，等级为不及格。

create procedure p3() begindeclare score int default 58; declare result varchar(10);if score >=85 thenset result:='优秀';elseif score >60 thenset result:='及格';elseset result:='不及格'; end if;select result;end;-- 开始限定死58了，所以要传参数

参数

语法

create procedure 存储过程名(in/ out/ inout[参数名 参数类型])begin-- sql语句end;

举例

-- 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)。-- score>=85分，等级为优秀。-- score>=60分且 sc0re<85分，等级为及格。 -- sc0re<60分，等级为不及格create procedure p4(in score int,out result varchar(10)) beginif score >=85 thenset result:='优秀';elseif score >=60 thenset result:='及格';elseset result:='不及格';end if;end;#调用存储过程 传入98 将结果返回给用户自定义变量call p4(98,@result); #查看select @result;

-- 将传入的200分制的分数,进行换算,换算成百分制,然后返回分数create procedure p5(inout score double) beginset score :=score*0.5; end;-- 赋值set @score 198;-- 调用call p5(@score);-- 查看select @score;

case

语法

-- 语法一case 条件when 条件1 then 语句1 -- 如果满足条件1执行[when 条件2 then 语句2] -- 满足条件2执行[else 语句2] -- 都不满足执行end case;-- 语法二case when 条件表达式 then 语句 -- 满足条件执行[when 条件表达式 then 语句] -- 满足条件执行[else 语句] -- 都不满足执行end case;

举例

# 根据传入的月份，判定月份所属的季节（要求采用case结构。# 1-3月份，为第一季度# 4-6月份，为第二季度# 7-9月份，为第三季度# 10-12月份，为第四季度create procedure p6(in month int)begindeclare result varchar(10);casewhen month >1 and month <3 thenset resu几t:='第一季度';when month >4 and month <=6 thenset resu几t:='第二季度';when month >7 and month <=9 thenset resu几t:='第三季度';when month >10 and month <12 thenset resu几t:='第四季度';elseset result:='非法参数';end case;select concat('您输入的月份为:',month,',所属的季度为:',result);

循环

while

语法

-- 先判定条件，如果条件为tue，则执行逻辑，否则，不执行逻辑while 条件 dosql逻辑end while;

repeat

#先执行一次逻辑然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环repeatsql语法until条件end repeat;

loop

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

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

-- begin lable标记[begin label:]loopSQL逻辑end loop [end label];-- 退出指定标记的循环体leave label;-- 直接进入下一次循环iterate label;

举例

-- loop计算从1到n之间的偶数累加的值，n为传入的参数值。-- a.定义局部变量，记录累加之后的值;-- b.每循环一次，就会n进行-1，如果n减到0，则退出循环->Leave xx-- c.如果当次累加的数据是奇数，则直接进入下一次循环->iterate xxcreate procedure p1(in n int)begindeclare total int default 0;sumd:Loop-- 判断是否小于0 leave退出循环if n<=0 thenleave sumd; end if;-- 判断是否为偶数 非偶数iterate退出本次循环if n%2 =1 thenset n :n -1; iterate sumd; end if;set total :=total+n;set n :=n-1;-- 结束循环end Loop sumd;-- 查询select total;end;#调用call p1(10); --> 30

游标

游标(CUSO)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。

游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法分别如下。

语法

声明

declare 游标名称 cursor for 查询语句;

打开游标

open 游标名称;

获取游标记录

fetch 游标名称 into 变量[,变量2];

关闭

close 游标名称;

举例

-- 根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业( profession)，并将用户的姓名和专业插入到所创建的一张新表(id，name，profession)中。-- A.声明游标，存储查询结果集-- B.准备：创建表结构-- C.开启游标-- D.获取游标中的记录-- E.插入数据到新表中-- F.关闭游标create procedure p11(in uage int)begin-- 局部变量declare uname varchar(100); declare upro varchar(100);-- 声明条件处理程序 declare exit handler for sqlstate not found close u_cursor;-- 声明游标 存储查询结果集declare u_cursor cursor for select name,profession from tb_user where age <uage;-- 创建表create table if not exists tb_user_pro(id int primary key auto_increment, name varchar(100),profession varchar(100));-- 开启游标 open u_cursor;-- 获取游标里的数据while true do-- 将name赋给uname，将profession赋给uprofetch u_cursor into uname,upro;-- 插入到新表insert into tb_user_pro values (null,uname,upro);end while;-- 关闭游标close u_cursor;end;-- 将tb_user年龄小于40的数据插入到tb_user_procall p11(40);

条件处理程序

条件处理程序(Handler)。可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

语法

declare handler_action handler for condition_value [,condition value]...statement;-- handler_actioncontinue #继续执行当前程序exit #终止执行当前程序-- condition_valuesqlstate sqlstate value #状态码,如02000sqlwarning #所有以O1开头的sqlstate代码的简写not found #所有以02开头的sqlstate代码的简写sqlexception #所有没有被sqlwarning或not found捕获的sqlstate代码的简写

存储函数 funcation

存储函数是有返回值的存储过程，存储函数的参数只能是in类型的。具体语法如下：

语法

create function 存储函数名 ([参数列表])returns type [characteristic]beginsql语句return...;end;-- characteristicDETERMINISTIC #相同的输入参数总是产生相同的结果NO SQL #不包含SQL语句。READS SQL DATA #包含读取数据的语句，但不包含写入数据的语句。

举例

-- 从1到n的累加create function fun1(n int)returns intbegindeclare total int default 0;while n>0 doset total :=total + n;set n :=n-1;end while;return total;end;select fun1(100);

触发器 tirgger

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

语法

创建

create trigger 触发器名（ before / after） （ insert / update / delete）on 表名 for each row -- 行级触发器begin语句实现end；

查看

show triggers;

删除

drop trigger [数据库名.]触发器名; -- 如果没指定数据库名默认当前

类型

举例

# 通过触发器记录tb_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'操作的D', operate_params varchar(500)comment'操作参数', primary key('id'))engine=innodb default charset=utf8;-- 插入数据触发器create trigger tb_user_insert_triggerafter insert on tb_user for each rowbegininsert into user_logs (id,operation,operate_time,operate_id,operate_params) values (null,'insert',now(),new.id,concat('插入数据内容为id=',new.id,',name=',name.name,',phone='new.phone,',email=',new.email,',profession=',new.profession)end;