MySQL进阶

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

1.创建视图

--格式：CREATE VIEW 视图名称 AS  SQL语句CREATE VIEW v1 AS SELET nid,     nameFROM    AWHERE    nid > 4

2.删除视图

--格式：DROP VIEW 视图名称DROP VIEW v1

3.修改视图

-- 格式：ALTER VIEW 视图名称 AS SQL语句ALTER VIEW v1 ASSELET A.nid,    B. NAMEFROM    ALEFT JOIN B ON A.id = B.nidLEFT JOIN C ON A.id = C.nidWHERE    A.id > 2AND C.nid < 5

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

select * from v1

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

1.创建触发器

# 插入前CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROWBEGIN    ...END# 插入后CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROWBEGIN    ...END# 删除前CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROWBEGIN    ...END# 删除后CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROWBEGIN    ...END# 更新前CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROWBEGIN    ...END# 更新后CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROWBEGIN    ...END

插入前触发器：

delimiter //CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROWBEGINIF NEW. NAME == 'alex' THEN    INSERT INTO tb2 (NAME)VALUES    ('aa')ENDEND//delimiter ;

插入后触发器：

delimiter //CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROWBEGIN    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表示即将删除的数据行。

2.删除触发器

DROP TRIGGER tri_after_insert_tb1;

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

insert into tb1(num) values(666)

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

1.创建存储过程

-- 创建存储过程delimiter //create procedure p1()BEGIN    select * from t1;END//delimiter ;-- 执行存储过程call p1()

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

• in          仅用于传入参数用
• out        仅用于返回值用
• inout     既可以传入又可以当作返回值

有参数的存储过程：

-- 创建存储过程delimiter \\create procedure p1(    in i1 int,    in i2 int,    inout i3 int,    out r1 int)BEGIN    DECLARE temp1 int;    DECLARE temp2 int default 0;        set temp1 = 1;    set r1 = i1 + i2 + temp1 + temp2;        set i3 = i3 + 100;end\\delimiter ;-- 执行存储过程set @t1 =4;set @t2 = 0;CALL p1 (1, 2 ,@t1, @t2);SELECT @t1,@t2;

结果集：

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

结果集+out值：

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

事务：

                        delimiter \\                        create PROCEDURE p1(                            OUT p_return_code tinyint                        )                        BEGIN                           DECLARE exit handler for sqlexception                           BEGIN                             -- ERROR                             set p_return_code = 1;                             rollback;                           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;                                                    -- SUCCESS                           set p_return_code = 0;                                                    END\\                    delimiter ;

游标：

                    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 ;

动态执行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 ;

2.删除存储过程

drop procedure proc_name;

3.执行存储过程

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

4.pymysql执行存储过程

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = 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', args=(1, 22, 3, 4))# 获取执行完存储的参数cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")result = cursor.fetchall()conn.commit()cursor.close()conn.close()print(result)