mysql 视图，触发器，存储

一、视图

概念：其实就是一个临时表。

　　视图是一个虚拟表（非真实存在的），其本质是【根据SQL语句获取动态的数据库，并为其命名】，用户使用时只需使用【名称】即可获取结果集。就可以当做表来使用。

# 1.创建视图
  #  --格式：create view 视图名称 as  sql语句

     create view 视图名称 as sql查询语句；

# 2.使用视图
    使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行                 创建、更新和删除操作，仅能做查询用。

     select * from 视图名称 where 条件

# 3.改变视图
-- 格式：alter  view 视图名称 as  sql语句

     alter view ren_wei as  sql查询语句；

# 4.删除视图
--格式：drop view 视图名称

     drop  view ren_view;    基本

基本语法

# 1.创建视图
    create view 视图名称 as sql查询语句
# 2.使用视图
    select * from 视图名称 where 条件
# 3.改变视图
    alter view 视图名称 as sql查询语句
# 4.删除视图
    drop view 视图名称

二、触发器

1、什么是触发器

触发器是一种与表操作有关的数据库对象，当触发器所在表上发生指定事件时，将调用该对象，
即表的操作事件触发表上的触发器的执行。
可以这样来理解：当某个表上的数据发生变化的时候，就会执行一个函数，这个函数可能会去
执行一些对其他表的操作。这个函数就是触发器，它就相当于编程里的监听器一样，一旦监听到这个表
发生了某些变化，就会执行已经写好的一套逻辑。按照面向对象的思想，这个触发器是该表的一个属性。

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

create trigger trigger_name trigger_time trigger_event on tbl_name for each row

# trigger_name： 触发器名称，自己设定（tri_before/after_xxx）
# trigger_time: 触发时机，取值before（之前）、after（之后）
# trigger_event : 触发事件，insert、update、delete。（插入、更新、删除）
# tbl_name : 需要建立触发器的表名

基础

INSERT型触发器 ：插入某一行时激活触发器，可能INSERT、LOAD DATA、REPLACE语句触发。
UPDATE型触发器 ： 更改某一行时激活触发器，可能通过UPDATE语句触发。
DELETE型触发器 ： 删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。

trigger_event详解

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

基本语法

# 商品表
create table shopgoods(
    id int primary key auto_increment,
    name varchar(20),
    num int
);

# 订单表
create table order_table(
    oid int primary key auto_increment,
    gid int,
    much  INT
)

# 触发器
create trigger tg1 after insert on order_table for each ROW
begin
    update shopgoods set num = num - new.much where id = new.gid;
end 

# 插入数据
insert into order_table(gid,much) values (2,3)
insert into shopgoods(name,num) values('商品1',10),('商品2',10),('商品3',10);

# 输出表oid = 1 的数据
DELETE from order_table where oid = '' 

insert into order_table(gid,much) values (1,5)
insert into order_table(gid,much) values (3,2)
# 再建一个触发器 须先删除
drop trigger tg1

# 例一：当用户撤销一个订单的时候
create trigger tg2 after delete on order_table for each row
begin

    update shopgoods set num=num + old.much where id = old.gid;

end 

# 删除订单
delete  from order_table where oid= 5

# 当用户修改一个订单的数量

create trigger tg3 after update on order_table
    for each row
BEGIN
    UPDATE shopgoods set num = num + old.much - new.much where id = new.gid;
END

update order_table set much = 4 where oid = ''

3种触发器的用法（例子）

python的话，尽量在python程序中实现这个触发器的功能

#Python:before insert on cmd
cmd_record={
    'id':10,
    'user':'tom',
    'priv':'rwx',
    'cmd':'tasklist -xxxx',
    'sub_time':'2017-03-01 11:11:11',
    'success':'no'
}
if cmd_record['success'] == 'no':
    insert into errlog(err_cmd,err_time) values(cmd_record['cmd'],cmd_record['sub_time'])

insert into cmd values(...)

# mysql
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

python与mysql

begin。。。end 中间写的是sql语句

# 每句sql语句都必须用分号（；）来结尾（默认值）。

# 所以我们可以使用 delimiter 来指定sql语句结束标志 $$

(注意：我们修改了定界符使用完了，记得修改回来delimiter ;)

#Mysql:
delimiter $$
create trigger tri_before_insert_cmd before insert on cmd for each row
begin
    if (NEW.success = 'no') then
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

#测试
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','','ls -l /etc',NOW(),'yes'),
    ('egon','','cat /etc/passwd',NOW(),'no'),
    ('egon','','useradd xxx',NOW(),'no'),
    ('egon','','ps aux',NOW(),'yes');

例子

# 1.创建触发器
    create trigger 触发器名称 after insert on 表名 for each row（固定写法）
    begin
        sql语句
    end
# 2.删除触发器
    drop trigger 名称

# 3、查看触发器
 #   查看触发器就和查看数据库show databases;或查看表格show tables;一样。
 #   查看触发器SHOW TRIGGERS [FROM schema_name]，这里的schema_name表示的是表名称。

4、触发器的执行顺序
#  触发器建立的一般是InnoDB数据库，上面要使用的表也是要事务支持的。
    1.如果BEFORE触发器执行失败，SQL无法正确执行。
    2.SQL执行失败时，AFTER型触发器不会触发。
    3.AFTER类型的触发器执行失败，SQL会回滚。

（视图 触发器 在应用程序级别python去解决）

三、存储过程

一般用于保险，金融，关乎钱的

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql。

使用存储过程的优点：

　　（1）用于替代程序写的SQL语句，实现程序与sql解耦；

　　（2）基于网络传输，传别名的数据量小，而直接传sql数据量大。缺点是程序员扩展功能不方便。

    存储过程
        1.创建存储过程（体会封装）
            create PROCEDURE p1()
                BEGIN

                    select * from ren;
                    select * from goods;

                end
        2.创建存储过程（体会参数）
            create PROCEDURE p2(in num int)
            BEGIN

            select * from ren where p_sal > num;
            end
        3.创建存储过程（体会控制）
            create PROCEDURE p3(in num int,in flag char(1))
                BEGIN
                    if flag = '' THEN
                            select * from ren where p_sal >num;
                    elseif flag ='' THEN
                            select * from ren where p_sal =num;
                    else
                            select * from ren where p_sal < num;
                    end if;
                END
         4.创建存储过程（体会循环）

            create PROCEDURE p4(inout num int)
            BEGIN
                DECLARE i int DEFAULT 1; -- 声明变量
                DECLARE he int DEFAULT 0;

                while i<= num DO   -- 表示开始循环
                    set he = he +i;
                    set i = i+1;
                end while;
                -- select he; -- 显示结果
                set num = he;
            end

        5.删除存储过程
            drop PROCEDURE 名称;

        6.调用存储过程
            call 名称(参数值)

        7.显示当前库下的所用存储过程
            show PROCEDURE status;
    

基本语法

1、数据库与程序使用方式

#方式一：
    MySQL：存储过程
    程序：调用存储过程

#方式二：
    MySQL：
    程序：纯SQL语句

#方式三：
    MySQL:
    程序：类和对象，即ORM（本质还是纯SQL语句）

2、创建无参存储过程

# 建存储
create procedure p1()
begin
    select * from people;
end 

# 调用存储功能
call p1();

# 存储不能修改，只能删除，再建
drop procedure p1; 

# 存储不能修改，只能删除，再建
drop procedure p1; 

create procedure p2()
begin
    select * from people;
    select * from shopgoods;
end
call p2();   #  2个结果
drop procedure p2;

3、创建传参存储

有参数的情况时，在定义存储过程中必须定义参数的类型，包含三种，解释如下：

#in          仅用于传入参数用
#out         仅用于返回值用
#inout       既可以传入又可以当作返回值

# 存储加参数
# in 代表入参  inout 即进又出
create procedure p3(in num int)
begin
    select * from people where p_sal > num;
end  

call p3(10000);

drop procedure p3;

4、创建传参加控制

# 存储 加 控制
create procedure p4 (in num int,in flag char(1))
begin
    if flag = '' then
        select * from people where p_sal >= num;
    else
        select * from people where p_sal < num;
    end if;
end 

drop procedure p4;
call p4(8000,'');

create procedure p5 (in num int,in flag char(1))
begin
    if flag = '' then
        select * from people where p_sal >= num;
    elseif flag = '' then
        select * from people where p_sal = num;
    else
        select * from people where p_sal < num;
    end if;
end 

drop procedure p5;
call p5(10000,'');

5、创建循环

# 存储 + 循环
-- 体会循环:计算1-100累加的和,并且返回计算结果.
create procedure p6 (inout num int )
begin
    declare i int default 1; # 声明变量
    declare sum int default 0;
    while i<num do
        set sum = sum + i;
        set i = i + 1;
    end while;
    select sum;  # 显示结果
    set num = sum;
end 

set @n = 100;
CALL p6(@n);

drop procedure p6;

-- ********************
create procedure p6 (inout num int )
begin
    declare i int default 1; # 声明变量
    declare he int default 0;
    while i <= num do
        set he = he + i;
        set i = i + 1;
    end while;
    select he;  # 显示结果
    set num = he;
end 

set @n = 100;
CALL p6(@n);

drop procedure p6;

循环例子

存储过程优点：
        1、存储过程增强了SQL语言灵活性。存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；
        2、减少网络流量，降低了网络负载。存储过程在数据库服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行；
        3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
        4、系统管理员通过设定某一存储过程的权限实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。