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

时间：2022-08-01 07:18:17

视图

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

SELECT * FROM

(

SELECT nid,name FROM tb1 WHERE nid >

)

AS A WHERE A.id > ;

临时表搜索

1、创建视图

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

CREATE VIEW v1 AS

SELECT nid,name FROM A WHERE nid > ;

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 >  AND C.nid <

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 =  THEN

        INSERT INTO tb2 (NAME)

        VALUES

            (''),

            ('') ;

    ELSEIF NEW. num =  THEN

        INSERT INTO tb2 (NAME)

        VALUES

            (''),

            ('') ;

    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()

存储过程

存储过程是一个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 ;

--

--     set temp1 = ;

--

--     set r1 = i1 + i2 + temp1 + temp2;

--

--     set i3 = i3 + ;

-- END $$

-- delimiter;

执行过程

--

set @t1=;

CALL p1 (,,@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 ;

--     SET ii = ii + ;

--     select * FROM man ;

--     if i1 =  THEN

--         SET i2 =  + d2;

--     ELSEIF i1 =  then

--         SET i2 =  + d2;

--     ELSE

--         SET i2 =  + d2;

--     END IF;

--

-- END $$

-- delimiter $$

--

-- set @o=;

-- CALL proc_p1(,@o,@u);

-- SELECT @o,@u;

有参数存储结构二

DROP TABLE

IF EXISTS testdb;

CREATE TABLE testdb (

    id INT AUTO_INCREMENT PRIMARY KEY,

    uname VARCHAR(),

    content INT

)  CHARSET=UTF8;

delimiter \

DROP PROCEDURE

IF EXISTS nb1\

CREATE PROCEDURE nb1(IN i1 INT)

BEGIN

DECLARE num INT DEFAULT  ;

DECLARE temp VARCHAR() ;

DECLARE num2 INT DEFAULT  ;

WHILE num < i1 DO

    set num2 =  +num ;

    set temp = concat('alex',num);

    INSERT INTO testdb(uname , content)

VALUES

    (temp , num2) ;

SET num = num +  ;

END

WHILE ;

END\

delimiter ;

call nb1();

创建一个存储过程-内部循环数据1个亿

2、删除存储过程

drop procedure proc_name;

3、执行存储过程

-- 无参数

call proc_name()

-- 有参数，全in

call proc_name(,)

-- 有参数，有in，out，inout

DECLARE @t1 INT;

DECLARE @t2 INT default ;

call proc_name(,,@t1,@t2)
#!/usr/bin/env python

# -*- coding:utf- -*-

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=, user='root', passwd='', db='t1')

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

# 执行存储过程

cursor.callproc('p1', args=(, , , ))

# 获取执行完存储的参数

cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")

result = cursor.fetchall()

conn.commit()

cursor.close()

conn.close()

print(result)

pymysql执行存储过程

事物

delimiter \\

create PROCEDURE p1(

    OUT p_return_code tinyint

)

BEGIN

  DECLARE exit handler for sqlexception

  BEGIN

    -- ERROR

    set p_return_code = ;

    rollback;

  END; 

  DECLARE exit handler for sqlwarning

  BEGIN

    -- WARNING

    set p_return_code = ;

    rollback;

  END; 

  START TRANSACTION;

    DELETE from tb1;

    insert into tb2(name)values('seven');

  COMMIT; 

  -- SUCCESS

  set p_return_code = ; 

  END\\

delimiter ;
DECLARE @i TINYINT;

call p1(@i);

select @i;

执行存储结果

相关文章