MySQL 视图、触发器、函数、存储过程

SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');
INSERT INTO `course` VALUES ('3', '英语');
INSERT INTO `course` VALUES ('4', '物理');
INSERT INTO `course` VALUES ('5', '');


-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `course_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_student_course` (`course_id`),
  CONSTRAINT `fk_student_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '小飞', '1');
INSERT INTO `student` VALUES ('2', 'hukey', '2');
INSERT INTO `student` VALUES ('3', '小王', '3');
INSERT INTO `student` VALUES ('4', '阿狗', '4');


表定义及数据
复制

SELECT sid, sname, student.name from course 
LEFT JOIN student on course.sid = student.course_id
where student.name = '小飞';
复制

create ALGORITHM = UNDEFINED
DEFINER = 'root'@'%'
SQL SECURITY DEFINER
VIEW view_student_course AS (
SELECT sid, sname, student.name from course 
LEFT JOIN student on course.sid = student.course_id
);
复制

ALGORITHM=UNDEFINED：指定视图的处理算法；
DEFINER=`root`@`localhost`：指定视图创建者；
SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式；
复制

SELECT * from view_student_course where name = '小飞';
复制

show table status where comment = 'view';
复制

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body


trigger_time: { BEFORE | AFTER }


trigger_event: { INSERT | UPDATE | DELETE }


trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
复制

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;
复制

mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());
复制

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
        执行语句列表
END;
复制

mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> INSERT INTO time VALUES(NOW());
    -> INSERT INTO time VALUES(NOW());
    -> END||
mysql> DELIMITER ;
复制

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);


mysql> delimiter $$
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    -> IF NEW.amount < 0 THEN
    -> SET NEW.amount = 0;
    -> ELSEIF NEW.amount > 100 THEN
    -> SET NEW.amount = 100;
    -> END IF;
    -> END$$
mysql> delimiter ;


mysql> update account set amount=-10 where acct_num=137;


mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |    0.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+


mysql> update account set amount=200 where acct_num=137;


mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |  100.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
复制

mysql> SHOW TRIGGERS\G;
……


结果，显示所有触发器的基本信息；无法查询指定的触发器。




在information_schema.triggers表中查看触发器信息
mysql> SELECT * FROM information_schema.triggers\G
……


结果，显示所有触发器的详细信息；同时，该方法可以查询制定触发器的详细信息。
mysql> select * from information_schema.triggers 
    -> where trigger_name='upd_check'\G;
复制

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
复制

Create function function_name(参数列表)
returns 返回值类型
BEGIN
函数体内容
END
复制

delimiter $$


CREATE FUNCTION hello()
RETURNS VARCHAR(255)
BEGIN
RETURN 'Hello world, i am mysql';
END $$


delimiter ;
复制

MariaDB [db1]> select hello();
+-------------------------+
| hello()                 |
+-------------------------+
| Hello world, i am mysql |
+-------------------------+
复制

delimiter $$
CREATE FUNCTION f1(
    t1 int,
    t2 int)
RETURNS INT
BEGIN
    DECLARE num int;
  set num = t1 + t2;
  RETURN(num);
END $$
delimiter ;
复制

MariaDB [db1]> select f1(1, 100);
+------------+
| f1(1, 100) |
+------------+
|        101 |
+------------+
复制

-- 查看函数
show FUNCTION status;
-- 查看函数的创建过程：
show create function func_name;
复制

CREATE
    [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body


proc_parameter:
    [ IN | OUT | INOUT ] param_name type


characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }


routine_body:
Valid SQL routine statement


[begin_label:] BEGIN
[statement_list]
……
END [end_label]
复制

SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '周杰伦');
INSERT INTO `teacher` VALUES ('2', '那英');
INSERT INTO `teacher` VALUES ('3', '汪峰');
INSERT INTO `teacher` VALUES ('4', '哈林');


-- ----------------------------
-- Table structure for userinfo
-- ----------------------------
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES ('1', 'admin', 'admin');
INSERT INTO `userinfo` VALUES ('2', 'superman', '123456');
INSERT INTO `userinfo` VALUES ('3', 'batman', '666');


表结构及数据
复制

delimiter $$
CREATE PROCEDURE p1()
BEGIN
    select * from teacher;
    insert into userinfo(username, password) VALUES ('xiaoA', '123');
END $$
delimiter ;
复制

MariaDB [db1]> call p1;
+-----+-----------+
| tid | name      |
+-----+-----------+
|   1 | 周杰伦    |
|   2 | 那英      |
|   3 | 汪峰      |
|   4 | 哈林      |
+-----+-----------+
4 rows in set (0.00 sec)


Query OK, 1 row affected (0.00 sec)
复制

delimiter $$
CREATE PROCEDURE p2(in t1 int)
BEGIN
    SELECT t1;
  set t1 = 2;
    SELECT t1;
END $$
delimiter ;
复制

MariaDB [db1]> set @t1 = 1;
Query OK, 0 rows affected (0.00 sec)


MariaDB [db1]> call p2(@t1);
+------+
| t1   |
+------+
|    1 |
+------+




+------+
| t1   |
+------+
|    2 |
+------+


MariaDB [db1]> select @t1;
+------+
| @t1  |
+------+
|    1 |
+------+
复制

delimiter $$
CREATE PROCEDURE p3(out t_out int)
BEGIN
    SELECT t_out;
  set t_out = 2;
    SELECT t_out;
END $$
delimiter ;
复制

MariaDB [db1]> set @t_out =1 ;


MariaDB [db1]> call p3(@t_out);
+-------+
| t_out |
+-------+
|  NULL |
+-------+
# 因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null


+-------+
| t_out |
+-------+
|     2 |
+-------+


MariaDB [db1]> select @t_out;
+--------+
| @t_out |
+--------+
|      2 |
+--------+
# 调用了 p3 存储过程，输出参数，改变了 t_out 变量的值
复制

delimiter $$
CREATE PROCEDURE p4(inout t_inout int)
BEGIN
    SELECT t_inout;
  set t_inout = 2;
    SELECT t_inout;
END $$
delimiter ;
复制

MariaDB [db1]> set @t_inout = 1;


MariaDB [db1]> call p4(@t_inout);
+---------+
| t_inout |
+---------+
|       1 |
+---------+


+---------+
| t_inout |
+---------+
|       2 |
+---------+


MariaDB [db1]> select @t_inout;
+----------+
| @t_inout |
+----------+
|        2 |
+----------+
复制

delimiter //
create procedure p4(
    out status int
)
BEGIN
    1. 声明如果出现异常则执行{
        set status = 1;
        rollback;
    }
       
    开始事务
        -- 由秦兵账户减去100
        -- 方少伟账户加90
        -- 张根账户加10
        commit;
    结束
    
    set status = 2;
    
    
END //
delimiter ;
复制

delimiter $$
CREATE PROCEDURE p5(out p_return_code tinyint)
BEGIN
    DECLARE exit HANDLER for SQLEXCEPTION
    BEGIN
    -- 执行失败，则返回 1
        set p_return_code = 1;
        ROLLBACK; -- 如果出错，则回滚
    END;
    START TRANSACTION;
    INSERT into userinfo(username, password) VALUES ('xiaoB', '222');
    COMMIT;
    -- 执行成功，则返回 2
    set p_return_code = 2;
END $$
delimiter ;


执行：
MariaDB [db1]> set @p_return_code=0;


MariaDB [db1]> call p5(@p_return_code);


MariaDB [db1]> select @p_return_code;
+----------------+
| @p_return_code |
+----------------+
|              2 |
+----------------+
复制

import  pymysql


config = {
    'host': '192.168.118.11',
    'user': 'root',
    'password': '123456',
    'database': 'db1'
}


db = pymysql.connect(**config)
with db.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
    cursor.callproc('p3', (0,))   # 使用 callproc 调用存储过程
    cursor.execute('select @_p3_0') # 查询 out 参数的返回值
    r2 = cursor.fetchall()  # 获取返回值
    print(r2)


执行结果：
[{'@_p3_0': 2}]
复制

-- 查看存储过程：
show procedure status; 
-- 查看存储过程创建的过程：
show create procedure proc_name;
复制

版权声明：本文内容来自CSDN：hukey，遵循CC 4.0 BY-SA版权协议上原文接及本声明。
本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行可。
原文链接：https://www.cnblogs.com/hukey/p/10396404.html
如有涉及到侵权，请联系，将立即予以删除处理。
在此特别鸣谢原作者的创作。
此篇文章的所有版权归原作者所有，与本公众号无关，商业转载建议请联系原作者，非商业转载请注明出处
复制