视图
虚拟存在的表,行和列的数据是来自定义视图的查询中使用的表,是动态生成的
编写视图主要是创建按sql查询语句
如何使用
如何创建视图:
create [or replace] view 视图名称 (列名列表) as select 语句 [with [cascaede local check option ]
eg: create or repalce view stu_q as select 语句
查询视图
show create 视图名称;
select * from 视图名称;
修改视图:
create [or replace] view 视图名称 (列名列表) as select 语句 [with [cascaede local check option ]
alter view 视图名称(列名列表) as select语句 with [cascaede local check option ]
删除视图:
drop view [if exists] 视图名称
视图的增删改:
视图不存数据 插入的数据其实是存到基表
insert into 视图 values (..)
with local check option 在创建视图时使用这条数据 会阻止不符合视图条件插入
视图的检查选项:
with local check option使用时会通过视图检查正在更改的每个行 ,例如插入 更新 删除 以使其符合视图的定义 ,mysql允许基于一个视图创建另一个视图 且还会检查试图在的规则以保持一致性。为了确定检查范围 ,mysql提供了cascaded 和local,默认选项casecaded
casecaded:使用这个时会 默认关联的视图也默认添加了,v1没有添加 v2基于v1,v2添加了,默认v1也添加,检查的时候会检查当前视图所依赖的所有视图的条件是否满足
local:
也会检查当前视图及所依赖的视图条件是否满足
视图的更新
视图和基础表之间必须存在一对一的关系,如果视图包含以下任何一项,则视图不可更新:
聚合函数 求和 最大最小 count
distinct
group by
having
union 或 union all
视图的作用
操作加单 可以把一些复杂的条件写到视图里,安全性,通过视图用户只能查询修改他们所见的数据,数据独立,屏蔽了基表的变化对业务的影响
存储过程
存储过程就是对sql语言层面的代码封装与重用
特点:
封装 复用
可以接受参数 也可以返回数据
减少网络交互 效率提升
基本语法
创建:
create procedure 存储过程名称([参数列表])
begin
---sql语句
end;
调用:
call 名称([参数])
查看存储过程:
select * from information_schema.rountines where rountine_schema="XXX"----查询指定数据库的存储过程和状态信息
show create producer 存储过程 ---查询某个存储过程的定义
删除:
drop procedure [if exits] 存储过程的名称
在命令行的时候设置结束符号,最后加上结束符号
delimiter 结束符号
变量
系统变量 mysql服务器提供,不是用户定义的 属于服务器层面 分为全局变量和会话变量
没加级别默认会话级别,mysql重启服务以后 所设置的全局参数会失效 想要不失效可以在etc /my.cnf中配置
查看系统变量
show [session |global] variables;查看所有系统变量
show [session |global] variables like '....'查看指定变量的值
select @@ [session |global] 系统变量名 ---查看指定变量的值
设置系统变量
set [session|global]系统变量名=值
set @@[session|global]系统变量名= 值
用户自定义的变量
用户变量不需要提前声明, 用的时候直接@变量名 使用就可以,其作用域为当前连接,不需要对其声明或者初始化,没有初始化获取到null
赋值:set可以一次为多个变量赋值
set @var_name =espr [,@var_name=expr]...;
set @var_name :=espr [,@var_name=expr]...;
select @var_name := expr [,@var_name : = espr]...;
select 字段名 into @var_name from 表名;
使用
select @ var_name;
局部变量
属于声明里的变 需要declare声明,使用set 赋值或select 字段名 into 变量名from 表名
使用: select 变量名;
变量类型就是数据库与字段类型 int,begint,char,varchar, date,time等
if
语法:
if 条件 1 then。。elseif 条件2 then 。。else。。end if
参数
in:输入参数 默认
out:输出参数 做返回值
inout:既可以输入也可以输出
如何声明一个参数类型
用法:
create procedure 存储过程名称([in/out/inout/参数名 参数类型])
begin
---sql语句
end;
case
case 变量 when 条件then结果 else 结果
end case;
while
while 条件
do sql逻辑
end while;
repeat
满足条件退出循环,会先执行,满足再退出;
repeat
sql 逻辑
until 条件
end repeat;
loop
实现简单的循环,如果不在sql逻辑中增加退出循环的额条件,可以用其来实现简单的死循环,loop可以配合以下俩个语句使用:
leave:配合循环使用,退出循环
iterate: 必须用在循环中,作用是跳过当前循环剩下的语句 直接进入下一次循环
[begin _label]:loop
sql 逻辑
end loop:[end_label];
leave label;退出指定标记的循环
iterate label:直接进入下一次循环
游标 cursor
用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环处理,游标的使用包括游标的声明、open、fentch和close
我们之前写的sql语句,虽然可以通过筛选条件来限定返回的记录,但是我们却没有办法在结果集里面,像指针一样,定位每一条记录,向前定位,向后定位,或随意定位到某一条记录。为了解决这个问题,我们可以使用游标。
游标可以让我们可以对结果集中的每一条记录进行定位,并对指向的记录中的数据进行操作
语法如下:
声明游标:
declare 游标名称 cursor for 查询语句
open 游标名称 ;打开游标
fetch 游标名称 into 变量;获取游标记录
close 游标名称;
条件处理handler
可以定义一个条件处理程序 用来解决在流程控制中遇到的问题相应的处理步骤
可以在SQL procedure中定义一个条件处理器(Handler)来处理特殊的行为。通用的处理器(Handler)定义格式:
DECLARE handler-type HANDLER FOR conditionSQL-procedure-statement
handler_action:
continue:继续执行当前程序
exit:终止执行当前程序
condition_value
SQLSTATE sqlstate_value:状态码入0200;
SQLWARNING :以01开头的
NOT FOUND :以02 开头的
SQLEXECEPTION:没有01 也没有02 的
存储函数:
是有返回值的存储过程,存储函数的参数只能是in类型的,具体语法如下:
func_name :存储函数的名称
param_name type:可选项,指定存储函数的参数。type参数用于指定存储函数的参数类型,该类型可以是MySQL数据库中所有支持的类型
RETURNS type:指定返回值的类型
characteristic:可选项,指定存储函数的特性
routine_body:SQL代码内容。包含局部变量、逻辑代码、sql语句…
和存储过程本质区别
1、存储函数有返回值,而存储过程没有返回值。
2、如果存储过程想实现有返回值的业务,我们就必须使用out类型的参数。
触发器
触发器概念:是与表有关的数据库对象,触发并执行触发器中定义的sql语句集合,触发器是一种特殊的存储过程,它在试图更改触发器所保护的数据时自动执行。
触发器与存储过程的异同
相同点:1. 触发器是一种特殊的存储过程,触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段。
不同点:2. 存储器调用时需要调用SQL片段,而触发器不需要调用,当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行,无需手动调用。
在MySQL中,只有执行insert,delete,update操作时才能触发触发器的执行;
触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作;
使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发;