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)

  --  2 帶參數 in 參數     -- delimiter //  -- create PROCEDURE p2(  --  in n1 int,  -- in n2 int  -- )  -- BEGIN  --  select * from student where sid<n1;  --   -- end //

 call p2(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 ;