Python Day48 mysql補充

標籤：local   觸發器   fetch   0.00   複雜   exe   har   pass   let   

一、視圖

　　視圖是一個虛擬表（非真實存在），其本質是【根據SQL語句擷取動態資料集，並為其命名】，使用者使用時只需使用【名稱】即可擷取結果集，可以將該結果集當做表來使用。使用視圖我們可以把查詢過程中的暫存資料表摘出來，用視圖去實現，這樣以後再想操作該暫存資料表的資料時就無需重寫複雜的sql了，直接去視圖中尋找即可，但視圖有明顯地效率問題，並且視圖是存放在資料庫中的，如果我們程式中使用的sql過分依賴資料庫中的視圖，即強耦合，那就意味著擴充sql極為不便，因此並不推薦使用。

1、建立視圖

create  view 視圖名 as sql語句

　　注意，上述為建立視圖的語句，但是語句中的sql語句中不能包含子查詢。執行個體如下：

　　可以通過建立視圖的方式，將上述sql查詢語句的結果儲存成視圖形式的虛擬表，下次再次使用時，就可以避免上述重複的sql語句：

2、視圖的修改

　　正如視圖概念中所述，視圖為sql語句查詢結果的虛擬表，它的真實資料是來源sql語句中涉及的真正的表中，當sql語句中只關聯一個表，更改視圖中的資料，所被關聯的表中的資料也會跟著更改，顯然這是不合理的，當sql語句中關聯多個表，則壓根不可以實現對視圖中的資料的更改。總之不可以通過update或者insert等語句對視圖中的內容變更。如果想真正的實現對視圖的更改，其實就是更改原有視圖中的sql語句，具體的更改文法如下：

alter view 原視圖名 as 新sql語句 

3、刪除視圖

drop view 視圖名      #例：drop view course_teacher

　　總結：使用視圖以後就無需每次都重寫子查詢的sql，但是這麼效率並不高，還不如我們寫子查詢的效率高；其次視圖是存放到資料庫裡的，如果我們程式中的sql過分依賴於資料庫中存放的視圖，那麼意味著，一旦sql需要修改且涉及到視圖的部分，則必須去資料庫中進行修改。

二、觸發器

　　使用觸發器可以定製使用者對錶進行【增、刪、改】操作時前後的行為，注意：沒有查詢

1、建立觸發器文法

# 插入前CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROWBEGIN    ...END# 插入後CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROWBEGIN    ...END# 刪除前CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROWBEGIN    ...END# 刪除後CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROWBEGIN    ...END# 更新前CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROWBEGIN    ...END# 更新後CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROWBEGIN    ...END

2、觸發器執行個體

#準備表CREATE TABLE cmd (    id INT PRIMARY KEY auto_increment,    USER CHAR (32),    priv CHAR (10),    cmd CHAR (64),    sub_time datetime, #提交時間    success enum (‘yes‘, ‘no‘) #0代表執行失敗);CREATE TABLE errlog (    id INT PRIMARY KEY auto_increment,    err_cmd CHAR (64),    err_time datetime);#建立觸發器delimiter //CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROWBEGIN    IF NEW.success = ‘no‘ THEN #等值判斷只有一個等號            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必須加分號      END IF ; #必須加分號END//delimiter ;#往表cmd中插入記錄，觸發觸發器，根據IF的條件決定是否插入錯誤記錄檔INSERT INTO cmd (    USER,    priv,    cmd,    sub_time,    success)VALUES    (‘egon‘,‘0755‘,‘ls -l /etc‘,NOW(),‘yes‘),    (‘egon‘,‘0755‘,‘cat /etc/passwd‘,NOW(),‘no‘),    (‘egon‘,‘0755‘,‘useradd xxx‘,NOW(),‘no‘),    (‘egon‘,‘0755‘,‘ps aux‘,NOW(),‘yes‘);#查詢錯誤記錄檔，發現有兩條mysql> select * from errlog;+----+-----------------+---------------------+| id | err_cmd         | err_time            |+----+-----------------+---------------------+|  1 | cat /etc/passwd | 2017-09-14 22:18:48 ||  2 | useradd xxx     | 2017-09-14 22:18:48 |+----+-----------------+---------------------+rows in set (0.00 sec)

　　特別的：NEW表示即將插入的資料行，OLD表示即將刪除的資料行。

 3、觸發器的刪除

　　觸發器無法由使用者直接調用，而是由於對錶的【增/刪/改】操作被動引發的。刪除觸發器的文法如下：

drop trigger 觸發器名;  #例：drop trigger tri_after_insert_cmd;

三、事務

　　事務用於將某些操作的多個SQL作為原子性操作，一旦有某一個出現錯誤，即可復原到原來的狀態，從而保證資料庫資料完整性。

create table user(id int primary key auto_increment,name char(32),balance int);insert into user(name,balance)values(‘wsb‘,1000),(‘egon‘,1000),(‘ysb‘,1000);#原子操作，執行中無異常start transaction;update user set balance=900 where name=‘wsb‘; update user set balance=1010 where name=‘egon‘; update user set balance=1090 where name=‘ysb‘; commit;#出現異常，復原到初始狀態start transaction;update user set balance=800 where name=‘wsb‘; update user set balance=1020 where name=‘egon‘; uppdate user set balance=1180 where name=‘ysb‘; rollback;  
commit;

四、預存程序

　　預存程序包含了一系列可執行檔sql語句，預存程序存放於MySQL中，通過調用它的名字可以執行其內部的一堆sql。使用預存程序的優點：（1）用於替代程式寫的SQL語句，實現程式與sql解耦；（2）基於網路傳輸，傳別名的資料量小，而直接傳sql資料量大。缺點是程式員擴充功能不方便。

1、資料庫與程式使用方式

#方式一：    MySQL：預存程序    程式：調用預存程序#方式二：    MySQL：    程式：純SQL語句#方式三：    MySQL:    程式：類和對象，即ORM（本質還是純SQL語句）

2、建立無參數預存程序

#建立無參預存程序delimiter $$                             #符號不局限於此，但前後需一致create procedure p1()begin    declare i int default 1;    while (i<10) do        select * from course;        set i=i+1;    end while;end $$delimiter ;#調用預存程序call p1();

 

3、建立有參數預存程序

　　有參數的情況時，在定義預存程序中必須定義參數的類型，包含三種，解釋如下：

#in          僅用於傳入參數用#out         僅用於傳回值用#inout       既可以傳入又可以當作傳回值

　　應用執行個體：

#建立有參預存程序delimiter $$create procedure p2(    in x char(5),    out y int)begin    select cid from course where cname=x;    set y=1;end $$delimiter ;#mysql中調用：set @x=‘生物‘;set @y=0;call p2(@x,@y);#查看傳回值select @y;#pymysql中的調用：import pymysqlconn=pymysql.connect(    host=‘localhost‘,    port=3306,    user=‘root‘,    password=‘‘,    database=‘day48‘,    charset=‘utf8‘　　 )cur=conn.cursor()#拿到預存程序sql語句的執行結果cur.callproc(‘p2‘,(‘生物‘,0))print(cur.fetchall())
#拿到預存程序傳回值‘‘‘pymysql預設會把參數變為如下形式名字，分別為生物和0的名字：@_p2_0 ,@_p2_1;‘‘‘cur.execute(‘select @_p2_1;‘)print(cur.fetchone())cur.close()conn.close()

　　帶事務預存程序執行個體：

#介紹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         -- ERROR         set p_return_code = 1;         rollback;     END;     DECLARE exit handler for sqlwarning     BEGIN         -- WARNING         set p_return_code = 2;         rollback;     END;     START TRANSACTION;         DELETE from tb1; #執行失敗        insert into blog(name,sub_time) values(‘yyy‘,now());    COMMIT;     -- SUCCESS     set p_return_code = 0; #0代表執行成功END //delimiter ;#在mysql中調用預存程序set @res=123;call p5(@res);select @res;#在python中基於pymysql調用預存程序cursor.callproc(‘p5‘,(123,))print(cursor.fetchall()) #查詢select的查詢結果cursor.execute(‘select @_p5_0;‘)print(cursor.fetchall())

4、刪除預存程序

drop procedure proc_name;

Python Day48 mysql補充