2019.03.31 -- MySQL-优快云博客

本文详细介绍了SQL中的视图、触发器、函数及存储过程的创建与使用方法，并通过具体实例展示了如何利用这些特性增强数据库应用的功能。此外，还涉及了事务处理的基本概念。

1.视图

视图一般较少用到，只是为某个查询语句设置别名；我们不应该修改视图中的记录

1 CREATE VIEW v1 AS SELECT id,paword from student;       #创建视图
2 
3 ALTER VIEW v1 AS SELECT paword FROM student;           #修改视图
4 
5 drop VIEW v1;                                          # 删除

2.触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

 1 CREATE TABLE cmd(                                   # 准备表
 2 id INT PRIMARY KEY auto_increment,
 3 USER CHAR(10),
 4 cmd CHAR(10),
 5 sub_time datetime,                         # 插入时间
 6 success enum('yes','no')
 7 )engine=innodb DEFAULT charset=utf8;  
 8 
 9 create TABLE errlog(
10 id INT PRIMARY KEY auto_increment,
11 err_cmd char(10),
12 err_time datetime
13 )engine=innodb DEFAULT charset=utf8;
14 
15 delimiter // 
16 CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW          # 生成触发器
17 BEGIN
18     if NEW.success='no' THEN
19         INSERT INTO errlog(err_cmd,err_time) VALUES (NEW.cmd,NEW.sub_time);
20     END IF;
21 END //
22 delimiter ;
23 
24 INSERT INTO cmd(                   # 往表中插入数据，并判断是否需要写入errlog
25     USER,
26     cmd,
27     sub_time,
28     success
29 )
30 VALUES
31     ('egon','ls -l /etc',NOW(),'yes'),
32     ('egon','cat /etc/passwd',NOW(),'no'),
33     ('egon','useradd xxx',NOW(),'no'),
34     ('egon','ps aux',NOW(),'yes');

3.函数

SELECT DATE_FORMAT(NOW(),'%Y - %m')      # select执行函数

 1 delimiter \\
 2 CREATE FUNCTION f1(                 # 创建函数
 3     i1 INT,
 4     i2 INT)
 5 RETURNS INT
 6 BEGIN
 7     DECLARE num INT DEFAULT 0;
 8     SET num = i1 + i2;
 9     RETURN(num);
10 END \\
11 delimiter ;
12 select f1(2,3) ;

4.存储过程

 1 delimiter \\
 2 CREATE procedure p1(
 3     in i1 INT,                # 所传的参数有三种类型：in（输入）、 out（输出）、inout（可输入可输出）
 4     out i2 INT
 5 )
 6 BEGIN
 7     SET i2=123;
 8     SELECT * from student WHERE id>i1;
 9 END \\
10 delimiter ;
11 SET @v=10;
12 CALL p1(2,@v);                # 用call来调用存储过程
13 SELECT @v;

 1 delimiter \\
 2 CREATE PROCEDURE p2(
 3     IN n1 INT,
 4     INOUT n2 INT
 5 ) 
 6 BEGIN
 7     SET n2 = 123123;
 8     select * FROM dbwork.student WHERE sid>n1;
 9 END \\
10 delimiter ;
11 SET @_p2_0=12;
12 SET @_p2_1=2;
13 CALL p2(@_p2_0,@_p2_1);
14 SELECT @_p2_1;

事务

 1 delimiter \\
 2 CREATE PROCEDURE p1(
 3 
 4     OUT p_return_code INT
 5 
 6 )
 7 BEGIN
 8     DECLARE EXIT HANDLER FOR SQLEXCEPTION
 9     
10     BEGIN
11         -- ERROR
12         SET p_return_code=1;
13         ROLLBACK;
14     END;
15 
16     START TRANSACTION;
17         DELETE FROM tb1;
18         INSERT INTO tb2(tname) VALUES ('seven');
19     COMMIT;
20     -- success
21     SET p_return_code=2;
22 
23 END\\
24 delimiter ;
25 
26 #在mysql中调用存储过程
27 SET @res=123;
28 call p1(@res);                     # 因为参数为out，不是传实数，而是传参
29 
30 # 通过查看返回值来看事务是否成功执行
31 SELECT @res;

View Code

在Python中基于pymysql调用数据库

1 cursor.callproc('p1',(123,))
2 print(cursor.fetchall())             #查询select的查询结果
3 
4 cursor.execute('select @res;')
5 print(cursor.fetchall())             # 查看返回值

执行存储过程

 1 -- 无参数
 2 call proc_name()
 3 
 4 -- 有参数，全in
 5 call proc_name(1,2)
 6 
 7 -- 有参数，有in，out，inout
 8 set @t1=0;
 9 set @t2=3;
10 call proc_name(1,2,@t1,@t2)

转载于:https://www.cnblogs.com/Chen-char/p/10635254.html