本文详细介绍了MySQL中的视图，包括视图的概念、好处、创建、修改、查看和删除。视图提供了一种简化SQL查询的方法，增强了数据的安全性。接着，讨论了MySQL中的变量，包括系统变量和用户自定义变量，以及它们的使用和赋值方式。此外，还阐述了存储过程的创建、调用和流程控制结构，如分支结构和循环结构。最后，提到了触发器的原理和创建方法，强调了其在数据完整性方面的作用。
一、视图
2.1 、什么是视图
视图mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
2.2、使用视图的好处
简化sql语句，用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
保护基表的数据，提高了安全性，使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
2.3、创建视图
CREATE [OR REPLACE] VIEW view_name
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
如果在创建视图的时候制定了“WITH CHECK OPTION”，那么更新数据时不能插入或更新不符合视图限制条件(where语句)的记录。
LOCAL ： 只要满足本视图的条件就可以更新。
CASCADED(默认值) ： 必须满足所有针对该视图的所有视图的条件才可以更新。
2.4、修改视图
修改方式一：
CREATE [OR REPLACE] VIEW view_name
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
方式二：
alter view view_name
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
2.3、查看视图
查看视图的方式和查看表的方式差不多
desc view_name
show create view view_name
show tables;-- 不仅可以显示当前数据库下的表还可以显示出视图,没有show views
2.4、删除视图
drop view view_name1,view_name2
注意：视图一般是用于查询，而不是更新，具有以下特点的视图都不允许更新
包含分组函数、group by、distinct、having、union、join
视图字段来自字段表达式或常数，不可执行INSERT和UPDATE，但允许执行DELETE
where后的子查询用到了from中的表
不可更新的视图导出的视图
2.5视图与表的比较
关键字
是否使用物理空间
作用于
视图
view
只保留sql逻辑，占用空间小
一般用于查询
表
table
保存实际的数据
可用于增删改查
二、mysql中的变量
mysql中的变量按作用范围可以分为以下几个类型：局部变量，会话变量，全局变量。按是否是用户自定义的又可以分为系统变量和用户自定义变量。
2.1、系统变量
系统变量就是由系统提供的变量，不用用户自定义。
查看系统变量
-- 查看所有系统变量
show [global | session ] variables [like]
-- 查看指定的系统变量
select @@[global | session].变量名 -- 类似与对象.属性
-- 给系统变量赋值
set [global | session] 变量名 = 值
set @@[global | session].变量名=值;
global表示全局变量，服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接(会话)有效
session表示会话变量，作用域为当前的会话(连接)
注意:local是session的近义词，session可以使用local代替，在不指定session或者global的情况下，默认是session
注意:无论是global变量还是session变量，在mysql服务重启之后，数据库的配置又会重新初始化，一切按照my.ini的配置进行初始化。global和session的配置都会失效了
2.2、用户自定义变量
局部变量
局部变量一般用在sql语句块中，比如存储过程的begin/end，只在当前begin/end代码块中有效，且只能放在begin end中，而且只能放在第一句。在该语句块执行完毕后，局部变量就消失了。declare语句专门用于定义局部变量，可以使用default来说明默认值。
声明方式：declare 变量名 类型 【default 值】;
赋值方式:
set 变量名 = 值；
set 变量名: = 值；
select xx into 变量名 from 表;
CREATE PROCEDURE myprocedure()
BEGIN
declare age int default 0;
set age = 5;
select age;
end
用户变量(一次会话有效)
MySQL中用户变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。用户变量 在客户端链接到数据库实例整个过程中用户变量都是有效的。可以在begin end里面，也可以放在外面。
注意：无论使用还是赋值用户变量都需要在前面记得加上@
赋值方式:
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
select xx into @变量名 from 表;
select @num:=字段名 from 表名;
注意上面两种赋值符号，使用set时可以用“=”或“:=”，但是使用select时必须用“:=赋值”
使用方式:
select @变量名;
三、存储过程
存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有
存储函数:是一个有返回值的存储过程
存储过程：是一个没有返回值的存储函数
3.2、创建存储过程
CREATE PROCEDURE procedure_name (参数模式 参数名 参数类型)
begin
SQL语句
end
-- 参数类型有三种
in(默认):表示输入的参数
out:表示输出的参数
inout:可以输入又可以输出
1.参数模式：in、out、inout，其中in可以省略
2.存储过程体的每一条sql语句都需要用分号结尾
在使用命令行写存储过程时，由于过程中的每一条sql都需要以‘;’结尾mysql会误以为是整个过程的结束，我们可以通过delimiter 重新定义一个分隔符
delimiter $
create procedure pro_test1()
begin
select 'Hello Mysql' ;
end$
DELIMITER:
该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。
调用存储过程：
call procedure_name(参数)
调用in模式的参数：call sp1(‘值’);
调用out模式的参数：set @name; call sp1(@name);select @name;
调用inout模式的参数：set @name=值; call sp1(@name); select @name;
查看存储过程：
-- 查询某个存储过程的定义
show create procedure 存储过程名;
-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
删除存储过程
DROP PROCEDURE [IF EXISTS] procedure_name ；
四、流程控制结构
4.1、分支结构
if函数
-- if函数实现简单的单双分支，可以放在表达式的任何位置
if(条件,值1,值2)
if条件判断
实现多分支条件判断
语法:
-- id条件判断语句只能放在 begin end 语句中
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if;
-- 例子
create procedure procedure1()
begin
declare height int default 175;
declare description varchar(50);
if height >= 180 then
set description = '身材高挑';
elseif height >= 170 and height < 180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select description ;
end
case结构实现多分支条件判断
case结构可以放在任何位置，如果放在begin end 外面，作为表达式结合着其他语句使用如果放在begin end 里面，一般作为独立的语句使用
方式一：
case 表达式或字段
when 值1 then 语句1;
when 值2 then 语句2；
..
else 语句n;
end [case];
-- 例子
create procedure procedure2(day int)
begin
declare result varchar(20);
case day
when 1 then
set result = '星期一';
when 2 then
set result = '星期二';
when 3 then
set result = '星期三';
when 4 then
set result = '星期四';
when 5 then
set result = '星期五';
when 6 then
set result = '星期六';
when 7 then
set result = '星期七';
else
set result = '输入错误';
end case;
select concat('今天是 :',result) as dayofweek ;
end
方式二：
case
when 条件1 then 语句1;
when 条件2 then 语句2；
..
else 语句n;
end [case];
-- 例子
create procedure procedure3(month int)
begin
declare result varchar(20);
case
when month >= 1 and month <=3 then
set result = '第一季度';
when month >= 4 and month <=6 then
set result = '第二季度';
when month >= 7 and month <=9 then
set result = '第三季度';
when month >= 10 and month <=12 then
set result = '第四季度';
end case;
select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content ;
end
4.2、循环结构
while循环
while循环是满足条件就继续执行
语法：
[名称:] while 循环条件 do
循环体
end while [名称];
-- 例子：
-- 从0加到n
create procedure procedure4(n int)
begin
declare total int default 0;
declare num int default 1;
name1:while num<=n do
set total = total + num;
set num = num + 1;
end while name1;
select total;
end
repeat循环
repeat是有条件的循环控制语句, 当满足条件的时候退出循环 。
【名称:】repeat
循环体
until 结束条件
end repeat 【名称】;
-- 例子从1加到n
create procedure procedure5(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
-- 知道until满足就退出循环
loop循环
loop 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：
【名称：】loop
循环体
end loop 【名称】;
-- 例子从0加到n
CREATE PROCEDURE procedure6(n int)
BEGIN
declare total int default 0;
ins: LOOP
IF n <= 0 then
/* 注意这里使用了 leave来跳出循环，类似于break,
iterate可以用来结束本次循环开启下一次循环，类似于continue,且使用了leave或者iterate都必须指定循环的名称
*/
leave ins;
END IF;
set total = total + n;
set n = n - 1;
END LOOP ins;
select total;
END
循环控制语句
- leave：类似于break，用于跳出所在的循环
- iterate：类似于continue，用于结束本次循环，继续下一次
循环语句的总结:
- 三种循环只能放在begin end中，都能实现循环结构
- 这三种循环都可以省略名称，但如果循环中添加了循环控制语句(leave或 iterate)则必须添加名称
- loop 一般用于实现简单的死循环，while 先判断后执行repeat 先执行后判断，无条件至少执行一次。
五、存储函数
存储函数相对于存储过程最大的一个不同点就是，存储函数必须有返回值,另外函数的参数类型只能是in，不能是inout或者out.
CREATE FUNCTION function_name(参数模式 参数名 参数类型) --参数模式是in可以省略
-- 声明返回类型，这历史returns,不是return
RETURNS type
BEGIN
sql语句
return 返回值;
END;
-- 例子，将输入的数乘2并返回
create FUNCTION fun1(num int)
returns int
BEGIN
DECLARE cnum int default 0;
set cnum = num*2;
return cnum;
end
-- 使用函数
select fun1(5);
六、触发器
6.1、简介
触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。触发事件的操作和触发器里的SQL语句是一个事务操作，具有原子性,要么全部执行，要么都不执行；
使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容
触发器类型
NEW 和 OLD的使用
INSERT 型触发器
NEW 表示将要或者已经新增的数据
UPDATE 型触发器
OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器
OLD 表示将要或者已经删除的数据
6.2创建触发器
create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] -- 行级触发器
begin
trigger_stmt ;
end;
before/after ：触发器触发的时间
insert/update/delete：触发器触发的条件
根据出发时间和触发条件的组合(笛卡尔积)可以看出一种有6中触发器。
for each row：行级触发器，针对每一行的改动执行一次
在 InnoDB 数据库中，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：
①如果 BEFORE 触发器执行失败，SQL 无法正确执行。
②SQL 执行失败时，AFTER 型触发器不会触发。
③AFTER 类型的触发器执行失败，SQL 会回滚。
注意:触发器使用的表需要支持事务(就是InnoDB),创建触发器的表，不能再sql语句(就是BEGIN...END)中再次操作该张表。
-- 在emp表中插入数据后执行触发器
create trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));
end
6.3、删除触发器
-- 如果不指定schema_name，就默认当前的数据库
drop trigger [schema_name.]trigger_name
6.4、查看触发器
-- 可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。
show triggers;
扫一扫
558
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
