• 视图、触发器、事物、存储过程、函数、流程控制


    一、视图

      视图是一张虚拟表,并不是真是存在的,用户可以直接使用创建完的视图名称获取结果集,该结果集可当表使用。

    1.创建视图

    语法:create view 视图名称  as sql语句

    注意:字段名不能重复

         视图是存放至数据库当中的,且视图是为了简化查询的sql语句,但是不应该更改视图中的记录。若更改了视图中的记录,有可能会影响到原来数据库中的记录。

    mysql> select * from t;
    +----+--------+----------+
    | id | NAME   | password |
    +----+--------+----------+
    |  5 | renren | 1234     |
    | 10 | daben  | 1        |
    | 11 | daben  | 1        |
    | 12 | daben  | 1        |
    | 13 | douqi  | 123      |
    +----+--------+----------+
    表t
    mysql> select * from t1;
    +----+----------+----------+
    | id | name     | password |
    +----+----------+----------+
    |  1 | hello    | 12       |
    |  2 | mysql    | 1        |
    |  3 | concat   | 1        |
    |  4 | sunshine | 1        |
    |  5 | moon     | 5        |
    +----+----------+----------+
    表t1
    create view t_view as select t1.id from t1 where name='hello';
    mysql> select * from t_view;
    +----+
    | id |
    +----+
    |  1 |
    +----+
    先创建视图
    mysql> select t1.name from t1 where t1.id=(select id from t_view);
    +-------+
    | name  |
    +-------+
    | hello |
    +-------+
    使用视图

    2.修改视图

    mysql> alter view t_view as select * from t where t.id>1;
    mysql> select * from t_view;
    +----+--------+----------+
    | id | NAME   | password |
    +----+--------+----------+
    |  5 | renren | 1234     |
    | 10 | daben  | 1        |
    | 11 | daben  | 1        |
    | 12 | daben  | 1        |
    | 13 | douqi  | 123      |
    +----+--------+----------+
    结果

    3.删除视图

    语法:DROP VIEW 视图名称
    DROP VIEW teacher_view

    二、触发器

    触发器是可以定制用户对表进行的 增、删、改 前后的行为,但是不包括查询

    1.创建触发器

    插入前
    create trigger tri_before_insert_t before insert on t for each row
    begin
        ...
    end
    
    插入后
    create trigger tri_after_insert_t after insert on t for each row
    begin
        ...
    end
    
    删除前
    create trigger tri_before_delete_t before delete on t for each row
    begin
        ...
    end
    
    删除后
    create trigger tri_after_delete_t after delete on t for each row
    begin
        ...
    end
    
    更新前
    create trigger tri_before_update_t before update on t for each row
    begin 
        ...
    end
    
    更新后
    create trigger tri_after_update_t after update on t each row
    begin
        ...
    end
    创建触发器

    2.简单实例

     1 1 准备表
     2 create table cmd(
     3     id int primary key auto_increment,
     4     user char(32),
     5     priv char(10),
     6     cmd char(64),
     7     sub_time datetime,
     8     success enum('yes','no')
     9 );
    10 
    11 2 准备另一张表,用来存放插入失败的数据
    12 create table errlog(
    13     id int primary key auto_increment,
    14     err_id int
    15 );
    16 
    17 
    18 3 创建触发器
    19 delimiter $$
    20 create trigger tri_after_insert_cmd after insert on cmd for each row
    21 begin
    22     if new.success ='no' then
    23         insert into errlog(err_id) values(new.id);
    24     end if;
    25 end $$
    26 delimiter
    27 
    28 4 插入数据
    29 insert into cmd(user,priv,cmd,sub_time,success) values
    30     ('hello','0755','ls -l /etc',NOW(),'yes'),
    31     ('hello','0755','cat /etc/passwd',NOW(),'no'),
    32     ('hello','0755','useradd xxx',NOW(),'no'),
    33     ('hello','0755','ps aux',NOW(),'yes');

    3. 删除触发器

    drop trigger tri_after_insert_cmd;

    四、事物(transaction)

      事物可以包含一系列的sql语句,事物的执行具有原子性(包含多条sql语句---要么都执行成功,要么都执行失败)。

     1 create table user(
     2     id int primary key auto_increment,
     3     name char(32),
     4     balance int
     5 );
     6 
     7 insert into user(name,balance) values
     8 ('bao',2000),
     9 ('ren',2000),
    10 ('yun',2000);
    11 
    12 原子操作
    13 start transaction;
    14 try:
    15     update user set balance=1500 where id=1;
    16     update user set balance=2200 where id=2;
    17     update user set balance=2300 where id=3;
    18     commit;
    19 except Exception:
    20     出现异常情况所有的数据就会滚回初始状态
    21     rollback;
    View Code

    五、存储过程

      存储过程中包含了一系列的sql语句,存储过程又存放于MySQL中,可通过调用它的名字来执行一堆的sql语句。

    优点:

      替代程序写的SQL语句,实现程序于SQL解耦;基于网络传输,直接传sql数据量大,而传名字的数据量会大大降低。

    缺点:程序员拓展功能不方便

    方案一:
        由MySQL编写存储过程
        应用程序不用负责任何事情
    
    方案二:
        由应用程序使用原生sql编写存储过程
        MySQL不用负责任何事情
        
    方案三:
        由应用程序创建ORM,创建类(可视为表格),通过类来实例化得到一个对象(可视为表格中的记录) ,在其中写入原生sql
        MySQL不负责任何事情
    
    执行效率:
        方案一>方案二>方案三
    开发效率:
        方案一>方案三>方案二
    三种方案

    1. 创建简单的存储过程(无参)

    delimiter $$
     创建过程
    create procedure p()
    begin
        select * from 表名;
    end $$
    delimiter;
    
     在MySQL中调用方法
    call p;
    
     在python中基于pymysql调用
    cursor.callproc('p')
     取出记录
     print(cursor.fetchall())
     1  create table data(
     2     id int primary key auto_increment,
     3     name varchar(20),
     4     gender char(6),
     5     email varchar(50)
     6 );
     7 
     8 delimiter $$
     9 create procedure p()
    10 begin
    11     声明n仅用作传入参数使用
    12     declare n int default 1;
    13     while (n<50) do
    14         insert into data values(n,concat('moon',n),'male',concat('moon',n,'@qq.com'))
    15         这里和python语法不一样
    16         set n=n+1
    17     end while;
    18 end $$
    19 delimiter;
    小例子

    2. 创建并执行存储过程(有参)

    对于存储过程,可以接收参数,其参数有三类:
    
    #in          仅用于传入参数用
    #out        仅用于返回值用
    #inout     既可以传入又可以当作返回值
     1 delimiter $$
     2 create procedure p(
     3     in n int,
     4     out res int
     5     # inout n
     6 )
     7 begin
     8     select * from 表名 where id>n;
     9     set res=0
    10 end $$
    11 delimiter;
    12 
    13 直接在mysql中调用执行:
    14 call p(6,@x);
    15 set @x=123;
    16 select @x;
    17 
    18 在python的pymysql中调用执行:
    19 cursor.callproc('p',(1,234))  # 实际上是set @_p_0=1; set @_p_1=234  # 0和1分别是下标
    20 print(cursor.fetchall())
    21 cursor.execute('select @_p_1;')
    22 print(cursor.fetchone())

    3. 删除存储过程

    drop procedure proc_name;----proc_name是创建存储过程时的名字

    六、函数

    1.需掌握函数  date_format

    create table article(
        id int primary key auto_increment,
        name char(32),
        sub_time datetime
    );
    
    insert into article (NAME, sub_time) values
        ('第1篇','2015-03-01 11:31:21'),
        ('第2篇','2015-03-11 16:31:21'),
        ('第3篇','2016-07-01 10:21:31'),
        ('第4篇','2016-07-22 09:23:21'),
        ('第5篇','2016-07-23 10:11:11'),
        ('第6篇','2016-07-25 11:21:31'),
        ('第7篇','2017-03-01 15:33:21'),
        ('第8篇','2017-03-01 17:32:21'),
        ('第9篇','2017-03-01 18:31:21');
    
    选出xub_time字段的值,按照年月的格式来排序
    select date_format(sub_time,'%Y-%m'),count(1), from article group by date_format(sub_time,'%Y-%m');

    七、流程控制

    1.if 条件语句

    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 3;
        end if;
        
    end //
    delimiter;

    2.循环语句

    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;
    while循环
    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 ;
    repeat循环
    begin 
        declare i int default o;
        loop_label:loop
            set i=i+1;
            if i<8 then
                iterate loop_label;
            end if;
            if i>=10 then
                leave loop_label;
            endif;
            select i;
        end loop loop_label;
    end
    loop循环
  • 相关阅读:
    centos 启动报错 “error:failure reading sector 0x9b268 from 'hd0'”
    Jenkins安装及插件管理
    svn关联Jenkins自动发布代码
    LVS逻辑卷的创建、扩展、销毁
    Linux上安装SVN服务端及SVN的可视化工具
    CentOS7下配置防火墙放过Keepalived
    Prometheus apache_exporter 监控配置
    CentOS启动报错:Error at boot time: "[Firmware Bug]: TSC_DEADLINE disabled due to Errata
    linux上安装open***
    python学习day15 Your CPU supports instructions that this TensorFlow binary was not compiled to use: AVX2 FMA
  • 原文地址:https://www.cnblogs.com/Smart1san/p/9365275.html
Copyright © 2020-2023  润新知