SQL视图/存储过程/触发器

一、视图

• 介绍

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

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

• 创建

• 查询

• 修改

• 删除

• 视图的检查选项

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

• 视图的更新

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

1.聚合函数或窗口函数(SUM()、MIN()、MAX()、COUNT()等)
2. DISTINCT
3. GROUP BY
4.HAVING
5. UNION 或者 UNION ALL

• 作用

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

案例：

1.为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。
2.查询每个学生所选修的课程(三张表联查)，这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。

1.create view tb user view as select id,name,profession, age,gender,status,createtime from tb_user;
select * from tb user view;
2.create view tb stu course view as select s.name student_name ,s.no student_no,c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;
select * from tb_stu_course_view;

---

二、存储过程

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

• 特点

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

• 创建

• 调用

• 查看

• 删除

创建
create procedure p1()
begin
  select count(*) from student;
end;

调用
call p1();

查看
方式一：select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
方式二：show create procedure p1;

删除
drop procedure if exists p1;

• 变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)
会话变量(SESSION)。

1、查看系统变量

2、设置系统变量

查看系统变量
show session variables;
show session variables like 'auto%';
show global variables like 'auto%';

设置系统变量
set session autocommit=0;

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

赋值

使用

赋值
set @myname ='itcast';
set @myage := 10;
set@mygender :=男',@myhobby :='java';

使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor ,@mycount;

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

声明：

变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等

赋值：

声明
赋值
create procedure p2()
begin
  declare stu_count int default 0;
  select count(*)into stu_count from student;
  select stu_count;
end;

call p2();

• if

例子：

根据定义的分数score变量，判定当前分数对应的分数等级，
1. score >= 85分，等级为优秀。
2. score>=60分且score<85分，等级为及格。
3. score<60分，等级为不及格。

create procedure p3();
begin
  declare score int default 58;
  declare result varchar(10);
  if score >= 85 then
    set result :='优秀';
  elseif score >= 60 then
    set result :='及格';
  else
    set result :='不及格';
  end if;
  select result;
end;

• 参数

用法：

例子：

1、根据传入参数score，判定当前分数对应的分数等级，并返回
score>= 85分，等级为优秀。
score>=60分且score<85分，等级为及格。
score<60分，等级为不及格。

2、将传入的200分制的分数，进行换算，换算成百分制，然后返回。

1、
create procedure p4(in score int, out result varchar(10))
begin
  if score >= 85 then
    set result :='优秀'
  elseif score >= 60 then
    set result :='及格';
  else
    set result :='不及格';
  end if;
end;

call p4(68,@result);
select @result;

2、
create procedure p5(inout score double)
begin
  set score := score * 0.5;
end;

set @score=78;
call p5(@score);
select @score;

• case

语法一：

语法二：

例子：

根据传入的月份，判定月份所属的季节(要求采用case结构)。
1.1-3月份，为第一季度
2.4-6月份，为第二季度
3. 7-9月份，为第三季度
4. 10-12月份，为第四季度

create procedure p6(in month int)
begin
  declare result varchar(10);
  case
    when month >= 1and 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 :='第四季度';
    else
      set result :='非法参数';
  end case
  select concat('您输入的月份为:',month，',所属的季度为:',result);
end;

call p6(4);

• while

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为:

计算从1累加到n的值，n为传入的参数值.
create procedure p7(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 p7(10);

• repeat

repeat是有条件的循环控制语句,当满足条件的时候退出循环。具体语法为:

计算从1累加到n的值，n为传入的参数值.
create procedure p8(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 p8(10);

• loop

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

1.计算从1累加到n的值，n为传入的参数值。

create procedure p9(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;


2.计算从1到n之间的偶数累加的值，n为传入的参数值。
create procedure p10(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 p10(10);

• 游标

游标(CURSOR)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和 CLOSE，其语法分别如下。

声明游标：

打开游标：

获取游标记录：

关闭游标：

例子：

根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业（profession)，并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

create procedure p11(in uage int)
begin
  declare uname varchar(100);
  declare upro varchar(100);
  declare u_cursor cursor for select name,profession from tb_user where age <= uage;
  drop table if exists tb user_pro;
  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   #这里会报错 如何解决见下一条
    fetch u_cursor into uname,upro;
    insert into tb_user_pro values(null, uname, upro);
  end while;
  close u_cursor;
end;

call p11(40);

• 条件处理程序

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

---

三、存储函数

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

例子：从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;

fun1(100);

---

四、触发器

• 介绍

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

• 语法

创建：

查看：

删除：

例子：

通过触发器记录 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'操作的ID',
  operate_params varchar(500) comment'操作参数',
  primary key( 'id')
)engine=innodb default charset=utf8;

create trigger tb_user_insert_trigger
  after insert on tb_user for each row
begin
  insert into user logs(id, operation, operate time, operate id, operate_params) VALUES
(null, 'insert', now(),new.id, concat('插入的数据内容为:id=',new.id,',name=',new.name, ',phone=', NEW.phone, ', email=',NEW.email,',profession=',NEW.profession)
end;

insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES(25,'二皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());