MySQL学习(4)

一 视图

　　预先定义一种对应关系，如：temp_table <-----> select * from class where student_id >10，那么这种对应关系叫做视图。

它仅仅是一种对应关系，当以后调用时，是会重新从内存中拿右侧的真正的表。视图是临时的，没有真是存在内存中的。 

　　创建视图：

create view temp_table as select * from class where student_id >10

　　删除视图：

drop view temp_table

　　修改视图：

alter view temp_table as .......

　　使用视图：

select * from temp_table

 

二 存储过程

　　先预先定义一个语句，如 tmp_table  <==> select * from class where student_id >10, 当我们调用这个语句的时候会返回给我们结果，相当于函数。

　　创建存储过程：

delimiter ..  #delimiter 用来修改执行语句的结尾符，默认是';' ，这里修改为'..'
create procedure tmp_table()
begin 
　　select * from class； 
end ..

　　调用存储过程：

call tmp_table()

　　存储过程不推荐修改，就直接删掉然后重建。

　　pymysql中使用存储过程：

import pymysql

connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='test', charset="utf8")

cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

cursor.callproc('tmp_table')

result = cursor.fetchall()

cursor.close()

connection.close()

 　　

　　存储过程可以接受参数，参数有三种：

　　　　a. in  用于传入参数

　　　　b. out 用于返回值用

　　　　c. inout 即可以传入又可以当返回值　创建带有参数的存储过程：

delimiter //
create procedure tmp_table(
    in arg1 int,
    out arg2 int,
    inout arg3 int
)
begin
　　declare temp1 int;
　　declare temp2 int default 1;
　　
　　set temp1 = 2;
　　set arg2 = temp1 + temp2;
　　set arg3 = arg2 + 10;
end//
delimiter ;

declare @arg2 int default 1; 声明变量, 默认值为1

declare @arg3 int default 1; 声明变量, 默认值为1

set @arg2 = 4; 给变量赋值
set @arg3 = 5
call tmp_table(1, @arg2, @arg3)
select @arg2,@arg3; 

 　　pymysql执行带有参数的存储过程：

import pymysql

connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='test', charset="utf8")

cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

cursor.callproc('tmp_table', args=(1,22,33))

cursor.execute("select @_tmp_table_0,@_tmp_table_1,@_tmp_table_2") #取出返回参数的固定用法

print(cursor.fetchall())

connection.commit()
cursor.close()
connection.close()

 　例子：创建存储过程：

　

 

 　　pymysql执行代码就是上面的，结果：

　　

 

 　　如果在存储过程中有类似select * from '表名'， 那么只需在python代码中，cursor.callproc()下面紧跟着执行 cursor.fetchall()就能拿到结果。

　　条件语句

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 ;

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 ;

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

　　动态执行SQL语句

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
    declare p1 int;
    set p1 = 11;
    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?';
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\\
delimiter ;

动态执行SQL

　　以上语句参考了本片文章：https://www.cnblogs.com/wupeiqi/articles/5713323.html 十分感谢！