• Python-Basis-24th


    周四,晴,记录生活分享点滴

    参考博客:https://www.cnblogs.com/wupeiqi/articles/5713323.html

    视图

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

    临时表搜索

    select * from
        (
            select nid, name
            from tb1 where nid > 2
        ) as a
    where
        a. name > 'alex';

    创建视图

    /* 格式:create view 视图名称 as  sql语句 */
    
    create view v1 as 
    selet nid, name from a
    where nid > 4

    删除视图

    /* 格式:drop view 视图名称 */
    
    drop view v1

    修改视图

    /* 格式:alter view 视图名称 as sql语句 */
    
    alter view v1 as
    selet a.nid, b. name from a
    left join b on a.id = b.nid
    left join c on a.id = c.nid
    where a.id > 2 and c.nid < 5

    使用视图

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

    小结:

    • 视图是临时表
    • 只能查看,不可其他操作
    • 执行时才能获取数据 

    存储过程

    存储过程是一个SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行。

    创建存储过程

    无参数存储过程

    /* 创建存储过程 */
    
    delimiter $$
    create procedure p1()
    BEGIN
        select * from class; /* begin...end 中间填写需要执行的语句 */
    END $$  /* $$...$$ 代替 ; 作为执行的符号 */
    delimiter ;

    mysql执行存储过程

    call p1()

    pymysql执行存储过程

    import pymysql
    
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    
    cursor.callproc('p1')  # 相当于pymysql正常执行select语句:cursor.execute('select...')
    
    result = cursor.fetchall()
    cursor.close()
    conn.close()

    有参数的存储过程

    对于存储过程,可以接收参数,其参数有三类:

    • in 仅用于传入参数用

    • out 仅用于返回值用

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

    /* ---------------------- 创建存储过程 ---------------------- */
    delimiter \
    create procedure p1(
        in i1 int,  /* 让内部使用 */
        in i2 int,
        inout i3 int,
        out r1 int
    )
    BEGIN
        /* DECLARE声明变量:在存储过程内部声明变量时,必须使用 */
        DECLARE temp1 int;  /* 创建数字类型的变量temp1 */
        DECLARE temp2 int default 0;  /* 相当于temp2 = 0 */
        
        /* 变量赋值 */
        set temp1 = 1;  /*相当于python中temp1 = 1 */
        
        set r1 = i1 + i2 + temp1 + temp2;
        
        set i3 = i3 + 100;
        
    end\
    delimiter ;
    
    /* ---------------------- 执行存储过程 ---------------------- */
    set @t1 =4;  /* @t1表示引用 */
    set @t2 = 0;
    CALL p1 (1, 2, @t1, @t2);
    SELECT @t1,@t2;  /* 相当于print */
    
    /* -------- 执行存储过程可以获取两类数据:普通值、结果集 -------- */

    1.结果集

    delimiter //
    create procedure p1()
    begin
        select * from v1;
    end //
    delimiter ;

    2.结果集+out值

    delimiter //
    create procedure p2(
        in n1 int,
        inout n3 int,
        out n2 int,
    )
    begin
        declare temp1 int ;
        declare temp2 int default 0;
    
        select * from v1;
        set n2 = n1 + 100;
        set n3 = n3 + n1 + 100;
    end //
    delimiter ;

    3.事务(与python异常处理相似)

    delimiter \
    create PROCEDURE p1(
           OUT p_return_code tinyint
           )
           
           /* 如果存储过程出现异常,执行代码块一 */
           BEGIN
           DECLARE exit handler for sqlexception  /* 声明一个特殊的变量sqlexceptiono,表示执行了sql的异常处理 */
           BEGIN
               -- ERROR
               set p_return_code = 1;
               rollback;  /* 回滚,set p_return_code = 0;不再执行 */
           END; 
    
           /* 如果存储过程出现警告,执行代码块二 */
           DECLARE exit handler for sqlwarning  /* 警告处理 */
           BEGIN 
               -- WARNING 
               set p_return_code = 2; 
               rollback; 
           END; 
       
           /* 如果执行成功,上面的两个代码块不用执行 */
           START TRANSACTION;  /* 开始一个事务 */
               DELETE from tb1;
               insert into tb2(name)values('seven');
           COMMIT;  /* START到COMMIT过程中的操作为一个事务操作,如果中间有一个出错,就回滚到原来的状态 */
           
           -- SUCCESS 
           set p_return_code = 0; 
           
           END\
    delimiter ;

    4.游标

    delimiter //
    create procedure p3()
    begin
        declare ssid int; -- 自定义变量1
        declare ssname varchar(50); -- 自定义变量2
        DECLARE done INT DEFAULT FALSE;
        
        DECLARE my_cursor CURSOR FOR select sid,sname from student;     
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        
        open my_cursor;
            xxoo: LOOP
                fetch my_cursor into ssid,ssname;
                if done then 
                    leave xxoo;
                END IF;
                insert into teacher(tname) values(ssname);
            end loop xxoo;
        close my_cursor;
    end  //
    delimter ;

    5.动态执行SQL

    delimiter 
    CREATE PROCEDURE p4 (
        in nid int
    )
    BEGIN
        PREPARE prod FROM 'select * from student where sid > ?';
        EXECUTE prod USING @nid;
        DEALLOCATE prepare prod; 
    END\
    delimiter ;

    删除存储过程

    drop procedure proc_name;

    执行存储过程

    mysql

    /* 无参数 */
    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)

    pymysql

    import pymysql
    
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='sqlexample', charset='utf8')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 执行存储过程,获取存储过程的结果集,将返回值设置给了 @_存储过程名_序号 = 
    r1 = cursor.callproc('p1', args=(1, 22, 3, 4))  # 如果是 in、inout传进去,out忽略  # 打印此条结果为 (1,22,3,4)
    # set @_p1_0 = 1
    # set @_p1_1 = 22
    # set @_p1_2 = 3
    # set @_p1_3 = 4
    # call p1(1,22,3,4)
    print(r1)
    result1 = cursor.fetchall()
    print(result1)
    
    # 获取执行完存储的参数
    r2 = cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")  # @_p1_0固定用法:p1指p1,0指args的1(第一个数)  # 打印此条结果为 1 ,没有拿到上面的返回值,需要fetchall
    print(r2)
    result2 = cursor.fetchall()  
    print(result2)
    
    conn.commit()
    cursor.close()
    conn.close()

    小结

    • 可写复杂逻辑
    • 参数:in、out、inout
    • 结果集:select...
    • 视图和存储过程都是在mysql服务端写的

    其他

    条件语句

    delimiter \
    CREATE PROCEDURE proc_if ()
    BEGIN
        
        declare i int default 0;
        if i = 1 THEN  /* 加if...else语句时,if后必须加then,相当于python中的冒号:   开头是if,结尾时end if */
            SELECT 1;
        ELSEIF i = 2 THEN
            SELECT 2;
        ELSE
            SELECT 7;
        END IF;
    
    END\
    delimiter ;

    循环语句

    while循环

    delimiter \
    CREATE PROCEDURE proc_while ()
    BEGIN
    
        DECLARE num INT ;  
        SET num = 0 ;
        WHILE num < 10 DO  /* 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

    delimiter \
    CREATE PROCEDURE proc_loop ()
    BEGIN
        
        declare i int default 0;
        loop_label: loop
            select i;
            set i=i+1;
            if i>=5 then
                leave loop_label;
            end if;
        end loop;
    
    END\
    delimiter;

    动态执行SQL语句

    delimiter \
    DROP PROCEDURE IF EXISTS proc_sql \
    CREATE PROCEDURE proc_sql ()
    BEGIN
        declare p1 int;
        set p1 = 11;
        set @p1 = p1;  /* EXECUTE规定的@p1 */
    
        PREPARE prod FROM 'select * from tb2 where nid > ?';  /* ? 为占位符,同% 、 prod代指字符串'select * from tb2 where nid > ?' */
        EXECUTE prod USING @p1; /* 字符串格式化 */
        DEALLOCATE prepare prod; 
    
    END\
    delimiter ;
    
    /*
    防止SQL注入
    第一种 cursor.callproc('p12','select * from tb where nid>?', 13)
    第二种 cursor.excute('select * from tb where nid>%s', 13)
    */

    小结

    • sql是字符串
    • 借助特殊语法并可以进行字符串格式化,用?作为占位符,execute proc using @p1

    触发器

    对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时,可以使用触发器,触发器用于定制用户对表的行进行【增/删/改】前后的行为。

    创建基本语法

    /* 插入前 */
    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

    插入前触发器

    delimiter //
    CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
    BEGIN
        IF NEW. NAME == 'alex' THEN  /* NEW封装了用户传递过来的数据 */
            INSERT INTO tb2 (NAME) VALUES ('aa')
        END IF;
    END//
    delimiter ;

    插入后触发器

    delimiter //
    CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
    BEGIN
        IF NEW. num = 666 THEN
            INSERT INTO tb2 (NAME)
            VALUES
                ('666'),
                ('666') ;
        ELSEIF NEW. num = 555 THEN
            INSERT INTO tb2 (NAME)
            VALUES
                ('555'),
                ('555') ;
        END IF;
    END//
    delimiter ;

    要点:

    • NEW表示即将插入的数据行

    • OLD表示即将删除的数据行

    删除触发器

    DROP TRIGGER tri_after_insert_tb1;

    使用触发器

    触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的。

    insert into tb1(num) values(666)

    小结:

    • 为某一个表 inser前后;update前后;delete前后
    • OLD、NEW
    • 删除多行,插入,更新 for 多行:OLD,NEW(删除之前将删除的值添加到另外一张表,如果是一个表,将表内的每一行数据全部执行一遍)
  • 相关阅读:
    shellshock溢出攻击
    内核编译与系统调用
    模块与系统调用
    20199315《Linux内核原理与分析》第十二周作业
    20199315《Linux内核原理与分析》第十一周作业
    Linux下的静态链接库和动态链接库
    2019-2020-1 20199315《Linux内核原理与分析》第九周作业
    2019-2020-1 20199315 《Linux内核原理与分析》 第八周作业
    2019-2020-1 20199315《Linux内核原理与分析》第七周作业
    2019-2020-1 20199315《Linux内核原理与分析》第六周作业
  • 原文地址:https://www.cnblogs.com/chungzhao/p/13157704.html
Copyright © 2020-2023  润新知