视图、存储过程、触发器

视图:视图是一个虚拟表(临时的)，并不是真实存在的，不可以通过视图对真实表进行修改

创建一个视图

mysql> create view v1 as
    ->     select * from course where teach_id>3;
Query OK, 0 rows affected (0.28 sec)

mysql> select * from v1;
+-----+--------+----------+
| cid | cname  | teach_id |
+-----+--------+----------+
|   9 | 政治   |        4 |
|  10 | 生物   |        6 |
|  11 | 历史   |        7 |
|  12 | 地理   |        8 |
+-----+--------+----------+
4 rows in set (0.26 sec)

存储过程
delimiter $$表示界定,见到$$结束。执行存储过程的时候，只需要使用==call p1()==

mysql> delimiter $$
mysql> create procedure p1()
    -> BEGIN
    ->     select * from course;
    -> END$$
Query OK, 0 rows affected (0.19 sec)

mysql> call p1();
    -> $$
+-----+----------------+----------+
| cid | cname          | teach_id |
+-----+----------------+----------+
|   1 | 生物           |        1 |
|   2 | 体育           |        1 |
|   3 | 物理           |        2 |
|   4 | 语文         |     NULL |
|   5 | 语文           |     NULL |
|   6 | 语文           |     NULL |
|   7 | 数学           |     NULL |
|   8 | 马克思         |     NULL |
|   9 | 政治           |        4 |
|  10 | 生物           |        6 |
|  11 | 历史           |        7 |
|  12 | 地理           |        8 |
+-----+----------------+----------+
12 rows in set (0.11 sec)

Query OK, 0 rows affected (0.11 sec)

pymysql执行存储过程

cursor.callproc('p1')

存储过程是可以接收参数的,但是无法返回值(return)

procedure p1(
    in arg1 int,#让存储过程内部使用
    out arg2 varchar(50) #可以作为一个返回值
    inout args int # 既能传值，又可以获取值
)

begin
    arg2 = 'eric'
    arg3 = arg3 + 666
end

@i2 = 123

call p1(1,@i1,@i2)

select @i1,@i2 #eric 789

在mysql中@i1默认就是@i1=null;

---

def func(arg):
    arg.append(123)

li = [11,22,33]
print(li)
func(li)
print(li)

[11, 22, 33]
[11, 22, 33, 123]

---

DECLARE 在存储过程内部声明变量时，必须使用

declare v int;
declare g int default 9;
ser v = 12;

例:

delimiter \\
CREATE PROCEDURE p11(
    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;

    
    SELECT * from course;

END\\
delimiter ;



import pymysql

#创建连接
conn = pymysql.connect('localhost','root','000000',database='db1',charset='utf8')

#创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#获取存取过程的结果集，将返回值设置给了@_存储过程名称_序号
r1 = cursor.callproc('p11',args=(1,22,3,4))
#set e@p11_0 =  1
#set e@p11_1 =  22
#set e@p11_2 =  3
#set e@p11_3 =  4
#call p11(1,22,3,4)



res1 = cursor.fetchall()
print(res1)

print(r1)

r2 = cursor.execute("select @_p11_0,@_p11_1,@_p11_2,@_p11_3")
print(r2)

res = cursor.fetchall()
print(res)

#conn.commit()
cursor.close()
conn.close()

输出:

[{'teach_id': 1, 'cid': 1, 'cname': '生物'}, {'teach_id': 1, 'cid': 2, 
'cname': '体育'}, {'teach_id': 2, 'cid': 3, 'cname': '物理'}, {'teach_id': 
None, 'cid': 4, 'cname': 'è¯\xadæ–‡'}, {'teach_id': None, 'cid': 5, 'cname': 
'语文'}, {'teach_id': None, 'cid': 6, 'cname': '语文'}, {'teach_id': None, 
'cid': 7, 'cname': '数学'}, {'teach_id': None, 'cid': 8, 'cname': '马克思'}, 
{'teach_id': 4, 'cid': 9, 'cname': '政治'}, {'teach_id': 6, 'cid': 10, 
'cname': '生物'}, {'teach_id': 7, 'cid': 11, 'cname': '历史'}, {'teach_id': 
8, 'cid': 12, 'cname': '地理'}]
(1, 22, 3, 4)
1
[{'@_p11_3': 24, '@_p11_1': 22, '@_p11_0': 1, '@_p11_2': 103}]

select 1,2,3获取的是一张表

动态执行SQL语句
字符串格式化的时候占位符是 ==?==

delimiter \\
DROP PROCEDURE IF EXIT proc_sql \\
CREATE PROCEDURE proc_sql()
BEGIN
    DECLARE p1 int;
    set @p1 = 7;
    PREPARE prod FROM 'select * from course where cid>?';
    EXECUTE prod USING @p1;==注意这里必须要@
    DEALLOCATE PREPARE prod;
    
END\\
delimiter ;


mysql> call proc_sql;
+-----+-----------+----------+
| cid | cname     | teach_id |
+-----+-----------+----------+
|   8 | 马克思    |     NULL |
|   9 | 政治      |        4 |
|  10 | 生物      |        6 |
|  11 | 历史      |        7 |
|  12 | 地理      |        8 |
+-----+-----------+----------+
5 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

触发器,在修改(添加删除修改)前后进行自定制的操作

delimiter //
CREATE TRIGGER tri_before_insert_course1 BEFORE INSERT ON course FOR EACH ROW

BEGIN
    insert course (cname,teach_id) VALUES('xiaoming',12);
END//
delimiter ;

触发器中NEW变量(insert)与OLD(delete)变量的使用：
UPDATE的时候，既有new又有old。