> 数据库视图，触发器，事物，存储过程，函数

数据库视图，触发器，事物，存储过程，函数

视图：
视图是一个虚拟表，是在内存中的表。
创建视图：

mysql> create view author_view  as select name from book_author where id>2;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+----------------------------+
| Tables_in_bms              |
+----------------------------+
| auth_group                 |
| auth_group_permissions     |
| auth_permission            |
| auth_user                  |
| auth_user_groups           |
| auth_user_user_permissions |
| author_view                |
| book_author

可以看到创建了author_view表，但在硬盘中只保存了author_view.frm文件，没有那个.idb存放数据的文件。视图只有表结构，没有真实数据。

注意：
使用视图后无需每次得了重写子查询的sql,开发时是方便了很多，但效率并不高
视图是存放在数据库里的，如果sql要修改，并且涉及到视图部分，则必须修改数据，并修改sql语句。
视图尽量只用于查询，不要修改，插入，删除等，因为它是多表联合起来生成的结果，如果你修改它，就导致很多表的数据被修改。

使用视图

mysql> select * from author_view;
+--------+
| name   |
+--------+
| Jack   |
| 小明   |
| 小黑   |
| 老王   |
| 小王   |
+--------+
5 rows in set (0.00 sec)

修改视图
语法：alter view view_name as SQL
它类似于删除旧的视图再创建一个新的视图，可以选择直接重新建一个。

mysql> alter view author_view as select * from book_author where id >2;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from author_view;
+----+--------+-----+------------------+
| id | name   | age | author_detail_id |
+----+--------+-----+------------------+
|  3 | Jack   |  20 |                5 |
|  6 | 小明   |  18 |                6 |
|  7 | 小黑   |  38 |                7 |
|  9 | 老王   |  59 |               10 |
| 10 | 小王   |  14 |               11 |
+----+--------+-----+------------------+
5 rows in set (0.00 sec)

删除视图：
drop view author_view;

触发器
触发器是定制用户对某张表进行 “增，删， 改”之后 进行的操作行为。有点类似于回调函数，简单点讲就是进行到某一步之后 触发了机关。

创建触发器：
    create trigger tri_before_insert_tb1 before insert on tb1 for each row
    Begin
         …. # begin end之间写sql要做的事情，有缩进
    end
    
    可能 的选择有: before insert ,after insert, before delete, after delete, before update, after update
    
   

delimiter //      
    CREATE TRIGGER tri_after_insert_cmd AFTER 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 ;      


    
    delimiter //将sql语句的结束 符号改成//
    sql每插入一条数据时都被封闭成一个NEW对象，OLD表示即将删除的对象
    中间的insert语句必须加；结尾，并且用delimiter包裹，否则mysql一看到；就结束了
    
    触发器无法由用户直接调用，而是由对表的增删改 被动触发。
    
    删除触发器： drop trigger triger_name;
    
事务
      事务用于将多个sql操作转成原子性操作，一损俱损（只要有一个不成功就都不成功）

      下面看实例：
   

mysql> create table user(id int primary key auto_increment,
        -> name char(32),
        -> balance int);
    Query OK, 0 rows affected (0.11 sec)
    
    mysql> insert into user(name, balance) values
        -> ('a', 1000),
        -> ('b',1000),
        -> ('c',1000);
    Query OK, 3 rows affected (0.07 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from user;
    +----+------+---------+
    | id | name | balance |
    +----+------+---------+
    |  1 | a    |    1000 |
    |  2 | b    |    1000 |
    |  3 | c    |    1000 |
    +----+------+---------+
    3 rows in set (0.00 sec)


    
    现在三个人都有1000块。让他们进行交易
   

 mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update user set balance=900 where name='a';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> update user set balance=1010 where name='b';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> update user set balance = 1090 where name ='c';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> commit;
    Query OK, 0 rows affected (0.03 sec)


    
    a买东西用支付宝支付了100块，卖家c只拿到了90，支付宝收了10块手续费。一旦这三个过程中任何一个出了问题就会回滚到原始状态，每个人还是1000块。
    
    下面写存储过程来捕异常：注意不要缩进
   

      delimiter //
    create PROCEDURE p5()
    BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
    rollback;
    END;
    
    START TRANSACTION;
    update user set balance=900 where name='wsb'; #买支付100元
    update user set balance=1010 where name='chao'; #中介拿走10元
    #update user2 set balance=1090 where name='ysb'; #卖家拿到90元
    update user set balance=1090 where name='ysb'; #卖家拿到90元
    COMMIT;
    
    END //
    delimiter ;


    
存储过程
    存储过程包含一系列可执行的sql语句
    
    创建存储过程
   

 delimiter //
    create procedure p1()
    BEGIN
        select * from book_book;
        INSERT into book_book(name,price) values("python", 120)
    END //
    delimiter ;


    
    调用
    mysql中调用： call p1();
    pymysql调用：cursor.callproc('p1')
    
    这么看存储过程不就是函数吗？那它们有什么区别，我们知道mysql中的函数如count(), max(),min()是放在sql语句中，不能单独调用，但存储过程则不同，可以直接调用call procedure_name();即可。
    
    传参数
    create procedure p2(
        in n1 int,  
        in n2 int
    )
    
    为里的in表示是传入的值，out则表示返回值，还有inout即可传入又可返回
    create procedure p3(
        in n1 int,
        out res int
    )
    
    这里的out可以通过set 设定
    set res = 1;
    
    在mysql中使用：
    set ＠res=0
    
    call p3(3,@res);
    
    而pymysql中使用：
    cursor.callproc('p3',(3,0))，这里我们写的常数3，0,实际上pymysql会自动帮你改成 @_p3_0=3, @_p3_1=0也就是p3的第一个参数，第二个参数。
    
    
    执行存储过程
    call proc_name(), call proc_name(1,2), 
    set @t1=0;
    set @t2=3;
    call proc_name(1,2, @t1, @t2)
    
    删除存储过程
    drop procedure pro_name;
    
函数
    数学：
    round(x,y) 参数x的保留y位小数
    rand() 返回0 ~1 内随机值
    
    聚合函数
    avg(col) 返回指定列的平均值
    count(col)返回指定列中非nulll值的个数
    min(col)返回指定列的最小值 
    max(col)返回指定列的最大值
    sum(col)返回指定列的和
    group_contat(col) 拼结
    
    字符串
    char_length(str) 返回长度
    concat(str1, str2…) 字符串拼接，当有任何一个为null,则返回null
    concat_ws(separator, str1,str2…) 自定义拼接符，会忽略所有的null,但汪会忽略空字符串
    conv(N,from_base, to_base)进制转换如 conv('a',16,2);
    format(x,d) 将数字x 四舍五入保留小数点后d位，以字符串形式返回。
    insert(str, pos, len, newstr) 在str pos位置插入字符串， len指替换的长度，newstr新字串
    instr(str, substr) 返回字符串str中子字符串中第一个出现的位置
    left(str, len) 返回字符串str从开始的len位置的子序列字符 
    lower(str) 小写
    upper(str) 大写
    reverse(str) 逆序
    substring(str, pos) 从pos处返回一个子字符串
    
    日期时间
    CURDATE()或CURRENT_DATE() 返回当前的日期
    CURTIME()或CURRENT_TIME() 返回当前的时间
    DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
    DAYOFMONTH(date)  返回date是一个月的第几天(1~31)
    DAYOFYEAR(date)   返回date是一年的第几天(1~366)
    DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
    FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
    HOUR(time)   返回time的小时值(0~23)
    MINUTE(time)   返回time的分钟值(0~59)
    MONTH(date)   返回date的月份值(1~12)
    MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
    NOW()    返回当前的日期和时间
    QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
    WEEK(date)   返回日期date为一年中第几周(0~53)
    YEAR(date)   返回日期date的年份(1000~9999)
    
    DATE_FORMAT(date,format) 根据format字符串格式化date值
    
    加密函数：
    MD5（） md5校验值
    PASSWORD(str) 返回str加密版本
    
    控制流函数
    case when[test1] then [result1]  else [default ] end
    
    case [test] when [val] then [result] … else [default] end
    
    if (test, t,f) 如果test为真，则t，否则f
    
    ifnull(arg1,arg2) 如果arg1 不是空，则arg1,否则arg2
    
    
流程控制：
    条件语句
   

delimiter //
    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 //
    delimiter ;


    
    循环语句：
   

delimiter //
    CREATE PROCEDURE proc_while ()
    BEGIN
    
        DECLARE num INT ;
        SET num = 0 ;
        WHILE num < 10 DO
            SELECT
                num ;
            SET num = num + 1 ;
        END WHILE ;
    
    END //
    delimiter ;


    
    repeat 循环：
   

 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 //
    delimiter ;


    
    loop:
   

BEGIN
        
        declare i int default 0;
        loop_label: loop
            
            set i=i+1;
            if i<8 then
                iterate loop_label;
            end if;
            if i>=10 then
                leave loop_label;
            end if;
            select i;
        end loop loop_label;
    
    END


 

上一篇：mysql 索引及查询优化

下一篇：数据库权限管理