目录
视图
视图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的。
通俗的讲,属兔只保存了查询的SQL逻辑,不保存查询结果,所以在创建视图的时候,主要的工作就落在了这条SQL语句上。
创建视图
create or replace view 视图命 as select字段 [with check option ]
查询视图
查看创建视图语句: show create view 视图名称
查看视图数据: select * from 视图名称
修改视图
-- 方式一
create [or replace] view 视图名称[(列名列表)] as select 语句
-- 方式2
alter view 视图名称[(列明列表)]
删除视图
drop view [if exists] 视图名称 [,视图名称]
视图检查选项
当使用with check option子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如,插入、更新、删除,以使其符合使徒的定义,MySQL允许基于另一个视图创建视图,他还会检查时图中的规则一保持一致性。为了确定检查的范围,MySQL提供了两个选项:
CASCADED和LOCAL,默认为CASCADED
视图更新
要使视图可更新,视图中的行与基础表之间必须存在1对1的关系,如果试图包含以下几项,则不能够进行更新:
- 聚合函数或者窗口函数(SUM MIN MAX COUNT)
- DISTINCT
- GROUP BY
- HAVING
- UNION或者UNION ALL
作用
-
操作简单
视图不仅可以简化对数据的理解,也可以简化他们的操作,那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次都是注定全部的条件
-
安全
数据库可以授权,但不能授权到数据库指定行和指定列上,通过视图用户只能查询和修改他们所能看到的数据
-
数据独立
试图可帮助用户屏蔽真实表结构变化带来的影响
存储过程
存储过程是实现经过编译并存储在数据库中的一段SQL语句的集合,调用过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理效率是有好处的。
存储过程思想上很简单,就是数据库SQL语言层面的代码封装与重用
特点
封装,复用。可以接收参数,也可以返回数据,减少网络交互,提高效率
基本语法
创建
create procedure 存储过程名称([参数列表])
begin
-- SQL语句
end;
create procedure p1()
begin
select * from kk.tb_newbee_mall_user;
end;
调用
CALL 名称([参数])
例如:
call p1();
查看结果:
查看
select * from information_schema.poutines where routine_schema='xxx' -- 查询指定数据库的存储过程的状态信息
show create procedure 存储过程名称 --查询某个存储过程的定义
查看整个库的存储过程:
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'test';
指令:
show create procedure p1;
运行结果:
删除
drop procedure [if exists] 存储过程名称;
比如说删掉p1这个存储过程
drop procedure p1;
再次查询p1过程:
在命令行中需要注意,要通过
delimiter
来设置结束符,否则的话,遇到;就直接结束了。存在问题就是设置后后续都得使用你设置的结束符来进行结束
变量
系统变量
系统变量是MySQL服务器提供的,不是用户定义的,属于服务器层面,分为全局变量(global)和会话变量(session)。
查看系统变量
-- 查看所有的系统变量
show [global|session] variables ;
-- 可以通过like模糊匹配的方式查找变量
show [session | global] variables like '...';
-- 查看指定的变量值
select @@[global | session] 系统变量名;
-- session 和global 可以不指定,如果没有指定,默认为session
查看所有的全局变量:
show global variables;
查看所有的session 变量:
show session variables;
模糊匹配
show global variables like 'auto%';
运行结果:
直接查看某个准确的系统变量值
select @@autocommit;
截图:
设置系统变量
set [global | session] 系统变量名= 值;
set @@[global|session] 系统变量名= 值;
注意点
如果没有指定session和global,默认为global。
MySQL重启后global设定会恢复,如果要保证修改永久生效,就需要在配置文件中进行修改(/etc/my.cnf)
用户自定义变量
用户自定义变量根据需要将自己定义的变量,用户变量不用提前声明,在用的时候直接@变量名就可以使用,其作用域为当前连接
复制
set @变量名=值[,@变量值= 值...];
set @变量名:=值
方式2
select @变量名:=值
select 字段值 INTO @变量命 from 表名
例如:
set @wxk_name = 'wxk';
set @wxk_age := 19;
select @wxk_hobby := 'game';
select count(0) from user into @wxk_have;
使用
select @变量名;
进行查询:
select @wxk_age ,@wxk_have, @wxk_hobby, @wxk_name;
查询结果:
注意
用户定义的变量无需对其进行声明或者初始化,只不过获取到的值为null
局部变量
局部变量是根据需要定义在局部生效的变量,访问之前,需要Declare生命,可用作存储过程内的局部变量和输入参数,局部变量的范围是在其内声明的BEGIN…END块
声明
declare 变量名 变量类型 [default ...]
变量类型就是数据库字段类型:int bigint char varchar date time等等
赋值
set 变量名=值
set 变量名:=值
select 字段名 into 变量名 from 表明…
案例:
create procedure p2()
begin
declare name VARCHAR(10) default 'wxk';
set name :='zzz';
select name;
end;
调用输出
call p2();
输出结果:
IF语句
语法:
if 条件1 then
...
elseif 条件2 then
....
else
....
end if;
案例:
create procedure p2()
begin
declare age int default 0;
declare msg varchar(10);
if age <0 then
set msg :='你这年龄不对劲';
elseif age < 18 then
set msg :='未成年,不能打游戏';
else
set msg := '随便玩把,我管不住';
end if;
select msg;
end;
参数
类型 | 含义 | 备注 |
---|---|---|
IN | 该类参数作为输入,也就是需要调用时传入值 | 默认 |
OUT | 该类参数作为输出,也就是该参数可以作为返回值 | |
INOUT | 既可以输入参数,也可以输出参数 |
用法:
create procedure 名称(in |out|inout 参数名 参数类型)
begin
-- SQL 语句
end;
IN 案例:
create procedure p3(in age int)
begin
declare msg varchar(10);
if age <0 then
set msg :='你这年龄不对劲';
elseif age < 18 then
set msg :='未成年,不能打游戏';
else
set msg := '随便玩把,我管不住';
end if;
select msg;
end;
调用:
call p3(66);
结果:
OUT案例:
代码:
create procedure p4(in age int,out msg varchar(10))
begin
if age <0 then
set msg :='你这年龄不对劲';
elseif age < 18 then
set msg :='未成年,不能打游戏';
else
set msg := '随便玩把,我管不住';
end if;
select msg;
end;
调用输出
call p4(60,@result); -- 这里的@result是一个用户自定义变量
select @result;
INOUT
代码
create procedure p4(inout mo int)
begin
set mo := mo-10;
end;
调用输出;
select @mo :=60;
call p4(@mo);
select @mo;
CASE
语法一
CASE case_vale
when when_value1 then SQL语句1
when when_value2 then SQL语句2
else SQL语句
end case;
create procedure p7 (in number int)
begin
declare msg varchar(10);
case
when 10
then set msg := '10';
when 20
then set msg :='20';
else
set msg :='30';
end case;
select msg;
end;
调用:
call p7(20);
语法二
CASE
when 表达式1 then SQL1
when 表达式2 then SQL2
else 表达式3
end case;
案例:
create procedure p7(in number int)
begin
declare msg varchar(10);
case
when number<0
then set msg :='what?';
when number <60
then set msg := 'bad';
when number < 80
then set msg :='OK';
else
set msg := 'good';
end case;
select msg;
end;
调用
call p7(90);
结果
while 循环
while循环是有条件的循环控制语句,满足条件后,在执行循环体中的SQL语句,具体语法为:
语法
while 条件 do
SQL
end while;
案例:
-- 递减求和
create procedure p7(in number int)
begin
declare res int default 0;
while number > 0 do
set res := res + number;
set number := number -1;
end while;
select res;
end;
调用:
call p7(5);
运行结果:
repeat循环
repeat是一个有条件的循环控制语句,当满足条件的时候退出循环,具体语法:
语法:
-- 先执行一次逻辑,然后判断逻辑是否满足,如果满足,则退出,如果不满足,则继续下一次的循环
repeat
SQL
until 条件
end repeat;
案例:
create procedure p7(in number int)
begin
declare res int default 0;
repeat
set res := res +number;
set number := number - 1;
until number=0 end repeat;
select res;
end;
调用
-- 运行结果 3 + 2 +1
call p7(3);
结果:
loop循环
loop 实现简单的循环,如果不在SQL逻辑中增加退出的循环条件,可以用其他实现简单的死循环,Loop可以配合以下两个语句使用:
- LEAVE:配合循环使用,脱出循环
- iterate: 必须用在循环中,作用是跳过当前循环剩下的语句,直接进入下一次循环
语法:
[自定义标记:] loop
SQL 语句
end loop [循环标记];
游标
游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理,游标的使用包括游标的声明、open fetch和close
声明游标
declare 名称 cursor for 查询语句;
打开游标
open 游标名称;
获取游标的记录
fetch 游标名称 into 变量;
关闭游标
close 游标名称;
条件处理程序 –说白了就是异常处理
条件处理程序(handler)可以用来定义流程控制结构执行过程中遇到问题时相应的处理步骤,具体语法
handler_action
continue:继续执行当前程序
exit:种植当前程序
condition_value
SQlSTATE sqlstate_value:状态码 -- 比如说200
SQLWarning: 所有以01开头的SQLSTATE代码的简写
NOTFOUNT :所有以02开头的SQLSTATE 代码简写
SQLEXCEPTION: 所有没有被SQLWARNING或者NOT FOUND捕获的SQLSTATE代码的简写
存储函数
存储函数是有返回值的存储过程,存储函数的参数只能是IN类型的,
语法
create function 存储函数名称([参数列表])
returns type [characteristic...]
BEGIN
SQL 语句
RETURN...;
END
characteristic说明:
- deterministic:相同的输出参数总是产生相同的结果
- no sql :不包含SQL语句
- reads sql data : 包含读取sql,但不包含写入数据的语句
不常用,存储函数能够实现的,存储过程也能够实现,而且存储函数还需要返回值?
触发器
简介
触发器是与表有关的数据库对象,指在insert/update/delete之前或者之后,触发执行触发器中的定义的SQL语句集合,触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作
使用别名OLD和new 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发
,不支持语句级触发
old是原来的引用内容
new是新的引用内容
行级触发器: 表示触发了几行,就执行几次
语句级触发器: 执行了几个语句就触发几次
触发器类型 | new和old |
---|---|
INSERT型触发器 | NEW标识将要或者已经新增的数据 |
UPDATE型触发器 | OLD标识修改之前的数据,new表示将要修改或者已经修改后的数据 |
DELETE型触发器 | OLD表示将要或者已经删除的数据 |
基本语法
创建
create trigger trigger_name
before | after insert | delete | update
on 表名 for each row -- 行级触发器
begin
语句
end;
查看触发器
show triggers;
删除
drop trigger [数据库.]trigger_name; -- 如果没有指定数据库,默认为当前数据库