视图、触发器、存储过程、自定义函数

一、视图

　　视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行的数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来定义视图的查询所引用的表，并且在引用视图时动态生成。

视图的好处：

　　a、简化表之间的廉洁(把联结写在select中)；

　　b、适当的利用视图可以更清晰地表达查询；

　　c、过滤不想要或不公开的数据(select部分)视图能够对机密数据提供安全保护；

　　d、使用视图计算字段值，如汇总这样的值。

　1、创建视图

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

　2、使用视图

select * from 视图名称 where 条件语句;

　3、更新视图数据

update 视图名 set 字段名='新值' where 字段名='旧值';

　4、删除视图数据　　

delete from 视图名 where 字段名='数值';

　5、删除视图

drop view 视图名;

二、触发器（trigger）　　

触发器(trigger):监视某种情况，并触发某种操作。

触发器创建语法四要素:

　　1、监视地点(table)

　　2、监视事件(insert/update/delete)　　

　　3、触发时间(after/before)

　　4、触发事件(insert/update/delete)

1、创建触发器语法

create trigger 触发器名称 after/before insert/uodate/delete on 表名 for each row #这句话是固定的
begin
     # 需要执行的 insert/update/delete语句;
 end


注意1：after/before:只能选一个，after表示 后置触发；before表示 前置触发
注意2：insert/update/delete：只能选一个

创建两张表

#商品表 create table goods( 　　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 );

添加3条商品数据

insert into goods(name,num) values('商品1','10'),('商品2','10'),('商品3','10')　　

在使用触发器之前：假设我们现在卖了3个商品1，则需要做两件事

1、往订单表插入一条记录

insert into order_table(gid,much) values(1,3);

2、更新商品表商品1的剩余数量

update goods set num=num-3 where id='1';

现在来创建一个触发器：

create trigger tg1 after insert on order_table for each row
begin
    uodate goods set num=num-3 where id = '1';
end

这时候我们只要执行：

insert into order_table(gid,much) values(1,3);

此时会发现商品1的数量变成了7，说明在插入一条订单的时候，触发器会自动帮我们做了更新的操作。但是现在触发器中的num和id都是写死的，所以不管买的是什么商品，最终更新的都是商品1的数量。此时：新插入的行用new来表示，行中的每一列的值用new列名来表示。所以现在可以将触发器改一改：　

create trigger tg2 after insert on order_table for each row
begin
    update goods set num = num - new.much where id = new.gid;
end

第二个触发器创建完毕，我们先把第一个触发器删掉

drop trigger tg1;　　

　再测试一下，插入一条订单记录：

insert into order_table (gid,much) values(2,3);

执行之后发现商品2的数量变为7。

 

现在还存在两种情况：　　

1、当用户撤销一个订单的时候。我们这边直接删除一个订单，此时需要将对应的商品数量再加回去。对于delete而言：原本有一行，后来被删除，想引用被删除的这一行，用old来表示，old.列名 可以引用被删除的行的值。

此时的触发器如下：

create trigger tg3 after delete on order_table for each row
begin
    update goods set num = num +old.much where id = old.gid;
end 

2、当用户修改一个订单的数量时，此时的触发器：

update order_table set much = '2' where much = '4'

create TRIGGER g1 before update on order_table for each ROW
BEGIN
	update goods set num=num-old.much+new.much where id = old.gid;
END

四、存储过程

1、概念：什么是存储过程：类似于函数(方法)，简单的说存储过程就是为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句(对数据的增删改查)、条件语句和循环语句等。

　　1、查看现有的存储过程

show procedure status;

　　2、删除存储过程

drop procedure 存储过程名称;

　　3、调用存储过程

call 存储过程名称(参数1 参数1类型 , 参数2 参数2类型);

　　4、创建存储过程

1)体会封装

#体会封装
create procedure p1()
begin
    select * from 表名;    
end

# 调用
call p1()

2)体会参数：

　　#  in   仅用于传入参数用

　　#  out    仅用于返回值用

　　#   inout  既可以传入又可以当作返回值

#SQL体会参数
create procedure p2(in m int)
begin
    select * from 表名 where 字段名>m;
end

# 调用
call p2(参数值)

3)SQL体会控制

# 没有返回值
create procedure p3(in x int,in c char(1))
begin
    if c='d' then 
        select * from 表名 where 字段名>x;
    else
        select * from 表名 where 字段名<x;
    end if;
end    

# 调用
call p3(3,'a')

 

# 有返回值

create procedure p3( in n1 int , out res int )
begin
    select * from blog where id>n1;
    set res=1;
end

# 调用

set @res=0
call p3(3,@res);
select @res;

　　

4)体会循环：计算从1加到100的和，且返回计算结果。

create procedure p4(inout n int)
    begin
        declare sum int ;
        declare i int;
        set sum=0;
        set i=1;
            while i<n do    -- 开始循环
                set sum=sum+i;
                set i=i+1;
            end while;       -- 结束循环
            select sum;      -- 提供结果
            set n = sum;    -- 将计算结果提供给输出变量n;
    end;
-- 调用:
    set @n=100;
    call p4(@n);
    -- select @n;   -- 此句在没有上面的select sum的时候写

执行存储过程：

-----无参数
call proc_name()

-----有参数，全 in
call proc_name(1,2)

-----有参数，有 in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

　　

存储过程与事务相结合：

# 介绍
create peocedure p4(
    out status int
)
begin
    1.声明如果出现异常则执行{
        set status = 1;
        rollback;
     }
     开始事务
        ---wsb账户减去10
        ---egon账户加90
        --- ysb账户加10
     commit;
    结束
    set status = 2;
end 


#实现
create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);

create procedure p5(
    out p_return_code tinyint
)
begin
    declare exit handler for sqlexception
    begin
         -- error
         set p_return_code = 1;
         rollback;
    end;
    declare exit handler for aqlwarning
    begin
        --warning
        set p_return_code = 2;
        rollback;
    end;
    start transaction;
        delete from tb1;     # 执行失败
        insert into blog(name,sub_time) values ('yy',now());
    commit;
    -----success
    set p_return_code = 0;
end



# 在mysql中调用存储过程
set @res = 123;
call p5(@res);
select @res;

　　

存储过程的缺点：程序员扩展功能不方便。

存储过程的优点：

1、增强了SQL语言灵活性。存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；

2、减少网络流量，降低了网络负载。存储过程在数据库服务器端创建成功后，只需要调用该存储过程即可，而传统的做法每次都是将大量的SQL语句通过网络发送至数据库服务器端然后再执行；

3、存储过程只在创造时进行编译，以后每次执行存储过程都不需要重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。

4、系统管理员通过设定某一存储过程的权限实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。　　

 MySQL提供的內键函数：

 

char_length(str)
        返回值为字符串str的长度，长度的单位为字符。一个多字节字符算作一个单字符。
        对于一个包含五个二字节字符集，length()返回值为10，而char_length()的返回值为5.
    concat(str1,str2,.......)
        字符串拼接
        如有任何一个参数为NULL，则返回值为NULL。
    concat_ws()不会忽略任何空字符串。(然而会忽略所有的NULL)。
    
conv(N,from_base,to_base)
    进制转换
    例如：
            select conv('a',16,2);    -- 表示将a 由16进制转换为2进制字符串表示

format(x,d)
　　将数字x的格式写成'#,###,###.##',以四舍五入的方式保留小数点后d位，并将结果以字符串的形式返回。若d为0，则返回结果不带有小数点，或不含小数的部分。
    
　　例如：
        select formate(12332.3 , 5); 结果为: '12332.30000'

insert(str,pos,len,newstr)
    在str的指定位置插入字符串
        pos:要替换位置
        len:替换的长度
        newstr:新字符串
    特别的：
        如果pos超过原字符串长度，则返回原字符串
        如果len超过原字符串长度，则由新字符串完全替换

instr(str,substr)
    返回字符串 str 中子字符串的第一个出现位置。


left(str,len)
    返回字符串str 从开始的len位置的子序列字符。

lower(str)
    变小写

upper(str)
    变大写

ltrim(str)
    返回字符串str,其引导空格字符被删除。

substring(str,pos,len)
       获取字符串子序列
    总结：
       例如：
              select substring('quadratically',5)  -- 从第五位开始取值
              --结果：'ratically'
              select substring ('foobarbar' from 4);  -- 从第四位开始取值
               --结果：'barbar'
               select substring('quadratically',5,6)  -- 从第五位开始取值，一共取六位
               --结果：'ratica'
               select substring('sakila',-3)   -- 从倒数第三位开始向后取值
               -- 结果：'ila'
               select substring('sakila',-5,3)  -- 从倒数第五位开始取值，取3个
               -- 结果：'aki'
               select substring('sakila' from -4 for 2)  -- 从倒数第四位开始取2个值
               -- 结果：'ki'

locate(substr,str,pos)
    获取子序列索引位置

repeat(str,count)
    返回一个由重复的字符串str组成的字符串，字符串str的数目等于count。
    若 count <=0,则返回一个空字符串。
    若str 或count 为NULL，则返回 NULL。

replace(str,from_str,to_str)
    返回字符串str 以及所有被字符串to_str替代的字符串from_str。

reverse(str)
    返回字符串 str，顺序和字符顺序相反。

right(str,len)
    从字符串str开始，返回从后边开始的len 个字符组成的子序列

space(n)
    返回一个由n空格组成的字符串。

date_format(date,format)  根据format字符串格式化date值
         例如：
           select date_format('2009-10-04 22:23:00','%W %M %Y')
           -- 结果：'Sunday October 2009'
           select date_format('2009-10-04 22:23:00','%H:%i:%s')
           -- 结果：'22:23:00'
           select date_format('2009-10-04 22:23:00','%D %y %a %d %m %b %j')
           -- 结果：'4th 09 Sun 04 Oct 277'
           select date_format('2009-10-04 22:23:00','%H %k %I %r %T %S %w')
           -- 结果：'22 22 10 10:23:00 PM 22:23:00 00 0'
           select date_format('2009-10-04','%X %V')
           -- 结果：'2009 40'
           select date_format('2009-10-04','%d')
           -- 结果：'04'

1、自定义函数：在函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能；若想在begin.....end......中写sql，需要用存储过程。

create function fun1(i1 int ,i2 int)
returns int      -- 设置返回类型
begin
    declare sum int default 0;
    set sum = i1+i2;
    return(sum);     -- 返回结果
end

2、调用自定义函数

#直接调用自定义函数
select fun1(1,3)

#在sql语句中使用自定义函数
select fun1(参数1，参数2),name from 表名

3、删除自定义函数

drop function 函数名
例如：drop function fun_name;

 

流程控制：

一、条件语句

create procedure proc_if()
begin 
	declare i int default 0;
	if i = 1 then 
		select 1;
	elseif i = 2 then 
		select 2;
	else 
		select 7;
	end if;
end 

# 调用
call proc_if();//

#结果
+---+
| 7  |
+---+
| 7  |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

二、循环语句

mysql> create procedure proc_while()
    -> begin
    -> DECLARE num int;
    -> set num=0;
    -> while num < 10 DO
    -> select num;
    -> set num = num + 1;
    -> end while;
    -> end//


#调用
call proc_while()//


#结果
+------+
| num  |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

+------+
| num  |
+------+
|    2 |
+------+
1 row in set (0.03 sec)

+------+
| num  |
+------+
|    3 |
+------+
1 row in set (0.04 sec)

+------+
| num  |
+------+
|    4 |
+------+
1 row in set (0.06 sec)

+------+
| num  |
+------+
|    5 |
+------+
1 row in set (0.07 sec)

+------+
| num  |
+------+
|    6 |
+------+
1 row in set (0.08 sec)

+------+
| num  |
+------+
|    7 |
+------+
1 row in set (0.09 sec)

+------+
| num  |
+------+
|    8 |
+------+
1 row in set (0.11 sec)

+------+
| num  |
+------+
|    9 |
+------+
1 row in set (0.12 sec)

Query OK, 0 rows affected (0.13 sec)

 

delimiter//
create procedure proc_repeat()
BEGIN
	declare i int;
	set i = 0;
	REPEAT
		select i;
		set i = i + 1;
		until i >= 5
	end repeat;
end//

# 调用
call proc_repeat();//


# 结果
+------+
| i    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0.03 sec)

+------+
| i    |
+------+
|    3 |
+------+
1 row in set (0.04 sec)

+------+
| i    |
+------+
|    4 |
+------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.07 sec)