MySQL 系列（三）你不知道的 视图、触发器、存储过程、函数、事务、索引、语句

--格式：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
复制

# 插入前
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 == 'nick' 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 ;
复制

DROP TRIGGER tri_after_insert_tb1;
复制

insert into tb1(num) values(666)
复制

# 无参数存储过程


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


# 执行存储过程
call p1()
复制

# 有参数存储过程


# 创建存储过程
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 ;


# 执行存储过程
DECLARE @t1 INT default 3;
DECLARE @t2 INT;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
复制

drop procedure proc_name;
复制

# 无参数
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)
复制