mysql高级 视图，触发器，存储过程

视图View


作用：简化查询语句

创建视图
create view 视图名
as
select语句;


修改视图
alter view 视图名
as
select语句子



删除视图
drop view 视图名;


使用视图：把视图当表用
select * from 视图名;
select 字段,字段... from 视图名;
select * from 视图名 where 条件 order by 字段 limit 起始值,条数;




查看视图
1、查看现存的所有表
   show tables;
2、查看information_schema库->views表
   select table_schema,table_name from information_schema.views 
     where table_schema='库名';


视图注意：
1、简化查询语句(视图中只能存放select查询语句)
2、把视图当表用(建议只对视图执行查询操作)


视图知识点
1、创建视图
2、修改视图
3、查看视图
4、删除视图
5、使用视图


视图使用的场合
一个多表查询语句，频繁的使用，那么就要考虑是否创建一个视图
-----------------------------------------------------------
触发器Trigger：数据库中的事件


作用：当用户对表进行增删改操作的同时，是否要执行其他操作


触发器执行时间
before：当用户对表进行增删改时，先调用触发器，后执行增删改操作
after：当用户对表进行增删改时，先执行增删改操作，后调用触发器


触发器的类型
insert：当用户对表进行insert操作时，系统将自动调用触发器
update：当用户对表进行update操作时，系统将自动调用触发器
delete：当用户对表进行delete操作时，系统将自动调用触发器


创建触发器
delimiter //
drop trigger if exists 触发器名//
create trigger 触发器名
before|after  insert|update|delete
on 表名
for each row
begin
  sql语句;
  sql语句;
  sql语句;
end//
delimiter ;



删除触发器
drop trigger 触发器名;
drop trigger updateBbs;


查看触发器
1、查看information_schema库->triggers表
   select trigger_schema,trigger_name from information_schema.triggers
     where trigger_schema='库名';


例一：修改bbsInfo表中的记录，同时向userInfo表添加一条记录
delimiter //
drop trigger if exists updateBbs//
create trigger updateBbs
after update
on bbsInfo
for each row
begin
  insert into userInfo(userName,password)values('张三','123');
end//
delimiter ;



例二：向新闻表添加记录的同时，将分类表中指定分类下的数量加一
newsTypes：typeId、typeName、articleNums
newsArticles：articleId、title、content...
注意：该功能无法用触发器实现(原因：触发器无法传参)



例三：统计功能
员工表
create table employee
(
  empId     int         auto_increment primary key,
  userName  varchar(20) unique not null,
  sex       char(3)     default '男',
  age       int         default 20
);
部门表
create table bumen
(
  bmId      int         auto_increment primary key,
  bmName    varchar(50)  not null
);
统计表
create table tongji
(
  empCount   int   default 0,#员工总数
  bmCount    int   default 0 #部门总数
);
insert into tongji values(0,0);
题目：向员工表添加记录时，员工数量加一，相返的减一
      向部门表添加记录时，部门数量加一，相返的减一
添加员工的触发器
delimiter //
drop trigger if exists addEmp//
create trigger addEmp
after insert
on employee
for each row
begin
  update tongji set empCount=empCount+1;
end//
delimiter ;
删除员的触发器
delimiter //
drop trigger if exists delEmp//
create trigger delEmp
after delete
on employee
for each row
begin
  update tongji set empCount=empCount-1;
end//
delimiter ;
添加部门的触发器
delimiter //
drop trigger if exists addBm//
create trigger addBm
after insert
on bumen
for each row
begin
  update tongji set bmCount=bmCount+1;
end//
delimiter ;
删除部门的触发器
delimiter //
drop trigger if exists delBm//
create trigger delBm
after delete
on bumen
for each row
begin
  update tongji set bmCount=bmCount-1;
end//
delimiter ;
测试
insert into employee(userName,sex,age)values('张三','男',25);
insert into employee(userName,sex,age)values('李四','女',25);
insert into employee(userName,sex,age)values('王五','女',27);
insert into employee(userName,sex,age)values('赵六','男',30);
insert into employee(userName,sex,age)values('田七','男',50);
insert into bumen(bmName)values('开发部');
insert into bumen(bmName)values('就业部');


触发器的知识点
1、创建触发器
2、删除触发器
3、查看触发器


触发器注意
1、一个触发器最多只能有一个类型
2、一个表最多只能添加三个触发器
3、一个触发器最多只能监听一个表的动作
4、触发器是由系自动调用(触发器是由用户隐式调用)
5、触发器没有参数、触发器没有返回值




触发器的使用场合
当用户对表进行增删改的同时，是否要执行其他操作
----------------------------------------------------------
存储过程Procedure：相当于数据库中的自定义函数


作用：解决了代码重用(省代码)




创建存储过程
delimiter //
drop procedure if exists 存储过程名//
create procedure 存储过程名(参数...)
begin
  sql语句;
  sql语句;
  sql语句;
end//
delimiter ;




调用存储过程
call 存储过程名();
call 存储过程名(值,值,值...);




删除存储过程
drop procedure 存储过程名;




查看存储过程
1、查看mysql库->proc表
   select db,name from mysql.proc where db='库名';




例一：利用存储过程查询bbsInfo表的记录
delimiter //
drop procedure if exists getBbs//
create procedure getBbs()
begin
  select * from bbsInfo;
end//
delimiter ;
调用上边的存储过程
call getBbs();


php语言：弱语言、脚本语言




例二：利用存储过程查询bbsInfo表的记录
delimiter //
drop procedure if exists getBbsById//
create procedure getBbsById(bid int)#参数名不能和表的字段名相同
begin
  select * from bbsInfo where bbsId=bid;
end//
delimiter ;
调用上边的存储过程
call getBbsById(7);




外键约束名词：
主表：包含主键的那个表
从表(外表)：包含外键的那个表


外键限制
1、建表 先建主表，后建从表
2、删表 先删从表，后删主表
3、添加记录 先给主表添加记录，后给从表添加记录
4、删除记录 先删除从表记录，后删除主表的记录


用户表(主表)
userId  userName...
1       张三
2       李四
3       王五
4       赵六
文章表(从表)
id  userId  title  content...
1   2       xxxxxx xxxxxxxxxxxxxxxxxx
2   4       xxxxxx xxxxxxxxxxxxxxxxxx




例三：利用删除过程，删除指定的分类$typeId=3
newsTypes： 分类表(typeId,typeName...)
newsArticles： 新闻表(articleId,typeId,title...)
reviews： 评论表(id,articleId,body...)








delimiter //
drop procedure if exists deleteBbs//
create procedure deleteBbs(tid int)
begin
  delete from reviews where articleId in (select articleId from newsArticles where typeId=tid);
  delete from newsArticles where typeId=(tid);
  delete from newsTypes where typeId=(tid);
 
end//
delimiter ;
调用上边的存储过程
call deleteBbs(1);