MySql視圖觸發器存儲方法

2024-07-24 12:36:16

字體：大中小

來源：轉載

供稿：網友

這篇文章主要講解了MySql視圖觸發器存儲方法，內容清晰明了，對此有興趣的小伙伴可以學習一下，相信大家閱讀完之后會有幫助。

　　視圖：

　　　　一個臨時表被反復使用的時候，對這個臨時表起一個別名，方便以后使用，就可以創建一個視圖，別名就是視圖的名稱。視圖只是一個虛擬的表，其中的數據是動態的從物理表中讀出來的，所以物理表的變更回改變視圖。

　　　　創建：

　　　　　　create view v1 as SQL

　　例如：create view v1 as select * from student where sid<10

　　創建后如果使用mysql終端可以看到一個叫v1的表，如果用navicate可以在視圖中看到生成了一個v1的視圖

　　再次使用時，可以直接使用查詢表的方式。例如：select * from v1

　　　　修改：只能修改視圖中的sql語句

　　　　　　alter view 視圖名稱 as sql

　　　　刪除：

　　　　　　drop view 視圖名稱

　　觸發器：

　　　　當對某張表做增刪改查的時候(之前后者之后)，就可以使用觸發器自定義關聯行為。

　　　　修改sql語句中的終止符號 delimiter

　　before after 之前之后

　　-- delimiter //
　　
　　-- before或者after定義操作（insert或其他）之前或之后的操作
　　-- on 代表那張表發生操作后引發觸發器操作
　　
　　-- CREATE TRIGGER t1 BEFORE INSERT on teacher for EACH row
　　-- BEGIN
　　-- INSERT into course(cname) VALUES('奧特曼');
　　-- END //
　　-- delimiter ;
　　
　　
　　-- insert into teacher(tname) VALUES('triggertest111')
　　--
　　-- delimiter //
　　-- CREATE TRIGGER t1 BEFORE INSERT on student for EACH row
　　-- BEGIN
　　-- INSERT into teacher(tname) VALUES('奧特曼');
　　-- END //
　　-- delimiter ;
　　
　　
　　-- insert into student(gender,sname,class_id) VALUES('男','1小剛111',3);
　　-- 刪除觸發器
　　-- drop trigger t1;
　　
　　
　　-- NEW 和 OLD 代指新老數據使其數據一致
　　-- delimiter //
　　-- create TRIGGER t1 BEFORE insert on student for each row
　　-- BEGIN
　　
　　--這里的new 指定的是新插入的數據，old通常用在delete上
　　
　　-- insert into teacher(tname) VALUES(NEW.sname);
　　-- end //
　　-- delimiter ;
　　insert into student(gender,sname,class_id) VALUES('男','藍色的大螃蟹',3);
　　存儲過程：

　　本質上就是一堆sql的集合，然后給這個集合起個別名。和view的區別就是，視圖是一個sql查詢語句當成一個表。

　　　　　　方式：

　　　　　　　　1 msyql----存儲過程，供程序調用

　　　　　　　　2 msyql---不做存儲過程，程序寫sql

　　　　　　　　3 mysql--不做存儲過程，程序寫類和對象（轉化成sql語句）　　　　

　　　　　　創建方法：

　　-- 1 創建無參數的存儲過程
　　-- delimiter //
　　-- create PROCEDURE p1()
　　-- BEGIN
　　-- select * from student;
　　-- insert into teacher(tname) VALUES('cccc');
　　-- end //
　　-- delimiter ;

　　-- 調用存儲過程

　　 call p2(5,2) pymysql中 cursor.callproc('p1',(5,2))
　　-- 2 帶參數 in 參數
　　
　　-- delimiter //
　　-- create PROCEDURE p2(
　　-- in n1 int,
　　-- in n2 int
　　-- )
　　-- BEGIN
　　-- select * from student where sid<n1;
　　--
　　-- end // call p2(5,2) pymysql中 cursor.callproc('p1',(5,2))
　　-- 3 out參數在存儲過程入參時使用out則該變量可以在外部進行調用
　　-- 存儲過程中沒有return 如果想要在外部調用變量則需要使用out
　　-- delimiter //
　　-- create PROCEDURE p3(
　　-- in n1 int,
　　-- out n2 int
　　-- )
　　-- BEGIN
　　-- set n2=444444;
　　-- select * from student where sid<n1;
　　--
　　-- end //
　　--
　　-- delimiter ;
　　--
　　-- set @v1=999 相當于在session級別創建一個變量
　　-- set @v1=999;
　　-- call p3(5,@v1);
　　-- select @v1; #通過傳一個變量進去，然后監測這個變量就可以監測到存儲過程是否執行成功
　　
　　-- pymsyql中
　　--
　　-- cursor.callproc('p3',(5,2))
　　-- r2=cursor.fetchall()
　　-- print(r2)
　　--
　　-- 存儲過程含有out關鍵字如果想要拿到返回值 cursor.execute('select @_p3_0,@_p3_1')
　　-- # 其中 'select @_p3_0,@_p3_1'為固定寫法 select @_存儲過程名稱_入參索引位置
　　-- cursor.execute('select @_p3_0,@_p3_1')
　　-- r3=cursor.fetchall()
　　-- print(r3)
　　--
　　　　為什么有了結果集，又要有out偽造返回的值？

　　　　　　　　因為存儲過程中含有多個sql語句，無法判斷所有的sql都能執行成功，利用out的特性來標識sql是否執行成功。

　　　　　　　　例如，如果成功標識為1 部分成功標識2 失敗為3

　　　　存儲過程中的事務：

　　　　　　事務：

　　　　　　　　被成為原子性操作。DML（insert，update，delete）語句共同完成，事物只和DML語句相關，或者鎖只有DML才有事物。

　　　　　　事務的特點：

　　　　　　　　原子性 A ：事務是最小單位，不可分割

　　　　　　　　一致性 C ：事務要求所有dml語句操作的時候必須保證全部成功或者失敗

　　　　　　　　隔離性 I ：事務A和事務B之間有隔離性

　　　　　　　　持久性 D ：是事務的保證，事務終結的標志（內存中的數據完全保存到硬盤中）

　　　　　　事務關鍵字：

　　　　　　　　開啟事務：start transaction

　　　　　　　　事務結束：end transaction

　　　　　　　　提交事務：commit transaction

　　　　　　　　回滾事務：rollback transaction

　　　　　　事務的基本操作

　　delimiter //
　　 create procedure p5(
　　 in n1 int,
　　 out n2 int
　　 )
　　 begin
　　 1 聲明如果出現異常執行（
　　 set n2=1;
　　 rollback;
　　）
　　 2 開始事務
　　 購買方賬號-100
　　 賣放賬號+100
　　 commit
　　 3 結束
　　 set n2=2
　　 end //
　　
　　 delimiter ;
　　
　　這樣既可以通過n2 檢測后到錯誤也可以回滾
　　
　　以下是詳細代碼
　　delimiter //
　　 create procedure p6(
　　 out code TINYINT
　　 )
　　 begin
　　 聲明如果碰到sqlexception 異常就執行下邊的操作
　　 DECLARE exit HANDLER for SQLEXCEPTION
　　 begin
　　 --error
　　 set code=1;
　　 rollback;
　　 end;
　　 START TRANSACTION；
　　 delete from tb1;
　　 insert into tb2(name)values('slkdjf')
　　 commit;
　　 ---success
　　 code=2
　　 end //
　　delimiter ;
　　　　　　游標在存儲過程中的使用：

　　delimiter //
　　create procedure p7()
　　 begin
　　 declare row_id int;
　　 declare row_num int;
　　 declare done int DEFAULT FALSE;
　　 聲明游標
　　 declare my_cursor cursor for select id,num from A;
　　 聲明如果沒有數據則將done置為True
　　 declare continue handler for not found set done=True;
　　
　　
　　 open my_cursor; 打開游標
　　 xxoo;LOOP 開啟循環叫xxoo
　　 fetch my_cursor into row_id,row_num;
　　 if done then 如果done為True 離開循環
　　 leave xxoo;
　　 end if;
　　 set temp=row_id+row_num;
　　 insert into B(number)VALUES(temp);
　　 end loop xxoo; 關閉循環
　　 close my_cursor;
　　 end //
　　
　　delimiter ;
　　
　　
　　以上代碼轉化成python
　　for row_id,row_num in my_cursor:
　　檢測循環中是否還有數據，如果沒有則跳出 break
　　 break
　　 insert into B(num) values(row_id+row_num)
　　　　　　動態的執行sql，數據庫層面放置sql注入：

　　delimiter //
　　create procedure p6(
　　 in nid int)
　　 begin
　　 1 預編譯（預檢測）某個東西 sql語句合法性
　　 2 sql=格式化tpl+arg
　　 3 執行sql
　　
　　 set @nid=nid
　　 prepare prod from 'select * from student where sid>?'
　　 EXECUTE prod using @ nid;
　　 deallocate prepare prod
　　 end //
　　 delimiter ;
　　看完上述內容，是不是對MySql視圖觸發器存儲方法有進一步的了解.

（編輯：武林網）

上一篇：Centos7下如何安裝和配置MySQL5.7.20

下一篇：CentOS7版本如何安裝Mysql8.0.20版本數據庫