MySQL之扩展（触发器，存储过程等）

视图

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

SELECT * FROM 
(
SELECT nid,name FROM tb1 WHERE nid > 2
)
AS A WHERE A.id > 2;

1、创建视图

--格式 ： CREATE VIEW 视图名称 AS SQL 语句

CREATE VIEW v1 AS 
SELECT nid,name FROM A WHERE nid > 4;

2.删除视图
--格式：DROP VIEW 视图名称

DROP VIEW v1；

3.修改视图

--格式：ALTER VIEW 视图名称 AS SQL 语句

ALTER VIEW v1 AS 
SELECT A.nid,B.name, FROM A
LEFT JOIN B ON A.id = B.id
LEFT JOIN C ON A.id = C.id
WHERE A.id > 2 AND C.nid < 5

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

select * from v1

触发器

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

1.创建基本语法

# 插入前
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
    INSERT INTO tb2 (NAME)
VALUES
    ('aa')
END
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表示即将删除的数据行。

对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及OLD同时使用。下面是一个UPDATE中同时使用NEW和OLD的例子。

    CREATE TRIGGER up    
    BEFORE UPDATE ON student     
    FOR EACH ROW     
    BEGIN     
    SET @old = OLD.age;     
    SET @new = NEW.age;     
    END;  

现在如果student表中的age列的值是20，那么执行了"UPDATE age SET age=22"之后@old的值会变成20，而@new的值将会变成22。

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=3;

CALL p1 (1,2,@t1,@t2);
SELECT @t1,@t2;

-- ------------------------------存储过程--既能得到结果集，又能拿到结果
-- delimiter $$
-- DROP PROCEDURE IF EXISTS proc_p1 $$
-- CREATE PROCEDURE proc_p1(
-- in i1 INT,
-- INOUT ii int,
-- out i2 int
-- )
-- BEGIN
--     DECLARE d2 INT DEFAULT 3;
--     SET ii = ii + 1;
--     select * FROM man ;
--     if i1 = 1 THEN
--         SET i2 = 100 + d2;
--     ELSEIF i1 = 2 then
--         SET i2 = 200 + d2;
--     ELSE 
--         SET i2 = 1000 + d2;
--     END IF;
-- 
-- END $$
-- delimiter $$
-- 
-- set @o=1;
-- CALL proc_p1(1,@o,@u);
-- SELECT @o,@u;

DROP TABLE
IF EXISTS testdb;

CREATE TABLE testdb (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uname VARCHAR(20),
    content INT
)  CHARSET=UTF8;

delimiter \

DROP PROCEDURE
IF EXISTS nb1\
CREATE PROCEDURE nb1(IN i1 INT)
BEGIN

DECLARE num INT DEFAULT 0 ;
DECLARE temp VARCHAR(20) ;
DECLARE num2 INT DEFAULT 0 ; 
WHILE num < i1 DO
    set num2 = 100 +num ;
    set temp = concat('alex',num);
    INSERT INTO testdb(uname , content)
VALUES
    (temp , num2) ;
SET num = num + 1 ;
END
WHILE ;
END\

delimiter ; 
call nb1(1000000);

 

2、删除存储过程

drop procedure proc_name;

3、执行存储过程

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
DECLARE @t1 INT;
DECLARE @t2 INT default 3;
call proc_name(1,2,@t1,@t2)

#!/usr/bin/env python
# -*- coding:utf-8 -*-
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', 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)

事物

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 ;

DECLARE @i TINYINT;
call p1(@i);
select @i;