MySQL(二)

標籤：傳回值   lex   log   alt   handle   刪除   部落格   web   call   

上一篇文章講的是mysql的基本操作，這一篇會有一點難以理解，本節主要內容mysql視圖，預存程序，函數，事務，觸發器，以及動態執行sql

視圖view

視圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行資料。但是，視圖並不在資料庫中以儲存的資料值集形式存在。行和列資料來自由定義視圖的查詢所引用的表，並且在引用視圖時動態產生。對其中所引用的基礎資料表來說，視圖的作用類似於篩選。定義視圖的篩選可以來自當前或其它資料庫的一個或多個表，或者其它視圖。通過視圖進行查詢沒有任何限制，通過它們進行資料修改時的限制也很少。視圖是儲存在資料庫中的查詢的SQL 陳述式，它主要出於兩種原因：安全原因， 視圖可以隱藏一些資料。

1、建立視圖

--格式：CREATE VIEW 視圖名稱 AS  SQL語句CREATE VIEW v1 AS SELET nid, name FROM tab1 WHERE nid > 4

2、刪除視圖

--格式：DROP VIEW 視圖名稱DROP VIEW v1

3、修改視圖

-- 格式：ALTER VIEW 視圖名稱 AS SQL語句ALTER VIEW v1 ASSELET A.nid,B. NAME FROM tab1LEFT JOIN B ON A.id = B.nidLEFT JOIN C ON A.id = C.nidWHERE tab1.id > 2

也就只是改了把create改成alter，中間的語句更換了。

4、使用視圖

使用視圖時，將其當作表進行操作即可，由於視圖是虛擬表，所以無法使用其對真實表進行建立、更新和刪除操作，僅能做查詢用。

select * from v1

預存程序procedure

1、我們為什麼要用預存程序呢 ？

我們都知道應用程式分為兩種，一種是基於web，一種是基於案頭，他們都和資料庫進行互動來完成資料的存取工作。假設現在有一種應用程式套件組合含了這兩 種，現在要修改其中的一個查詢sql語句，那麼我們可能要同時修改他們中對應的查詢sql語句，當我們的應用程式很龐大很複雜的時候問題就出現這，不易維 護！另外把sql查詢語句放在我們的web程式或案頭中很容易遭到sql注入的破壞。而儲存常式正好可以幫我們解決這些問題。

2、建立預存程序

建立預存程序這塊主要有兩種，一種是帶參數的，一種是不帶參數的，先講不帶參數的再說不帶參數的

不帶參數案例：

-- 建立預存程序delimiter //        --自訂語句結尾符號，因為這裡要執行好多句sql語句，所以就得自訂，以防止出錯create procedure p1()BEGIN    select * from tab1;END//delimiter ;         --自訂局域結尾符號結束-- 執行預存程序call p1()

帶參數案例這塊主要有三個類

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

-- 建立預存程序delimiter \create procedure p1(    in i1 int,                        -- 傳入參數i1    in i2 int,                        -- 傳入參數i2    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;end\delimiter ;-- 執行預存程序DECLARE @t1 INT default 3;           --  設定變數預設值為3DECLARE @t2 INT;                     --  設定變數CALL p1 (1, 2 ,@t1, @t2);            --  執行預存程序，並傳入參數，t2自動取消SELECT @t1,@t2;                      --  查看預存程序輸出結果

2、刪除預存程序

drop procedure p1;

3、python用pymysql模組調用預存程序，因為我們學習這些就是為了語言調用

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘‘, db=‘day39b_‘)cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 執行預存程序row = cursor.callproc(‘p1‘,(1,2,3))# 預存程序的查詢結果selc = cursor.fetchall()print(selc)# 擷取預存程序返回effect_row = cursor.execute(‘select @_p1_0,@_p1_1,@_p1_2‘)# 曲預存程序的傳回值ret = cursor.fetchone()print(ret)# 提交，不然無法儲存建立或者修改的資料conn.commit()# 關閉遊標cursor.close()# 關閉串連conn.close()

函數function

 在mysql中有很多內建函數，比如我們經常用的求平均值，求和，個數，各式各樣，先給大家來一部門內建函數，然後再說說自訂函數吧，函數也可以傳參數，也可以接收傳回值，但是函數沒辦法得到執行語句得到的結果，預存程序可以。

 內建函數

更多參考請參考中文文檔http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions

1、自訂建立函數

delimiter \create function f1(    i1 int,    i2 int)returns intBEGIN    declare num int;    set num = i1 + i2;    return(num);END \delimiter ;

2、刪除函數

drop function f1;

3、執行函數

# 擷取傳回值declare @i VARCHAR(32);select UPPER(‘alex‘) into @i;SELECT @i;# 在查詢中使用select f1(11,nid) ,name from tb2;

事務

 事務用於將某些操作的多個SQL作為原子性操作，一旦有某一個出現錯誤，即可復原到原來的狀態，從而保證資料庫資料完整性。例如：當兩張銀行卡之間進行轉賬，甲方錢轉出去了，突然光纜壞了，乙方還沒收到錢，錢跑哪裡去了，就為了防止這種情況，事務就出來了，事務可以防止這種事情發生。

應用事務執行個體：

delimiter \create PROCEDURE p1(    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;                   -- sql語句都放在這個裡面    insert into tb2(name)values(‘seven‘);  COMMIT;    -- SUCCESS   set p_return_code = 0;    END\delimiter ;

執行預存程序：

DECLARE @i TINYINT;call p1(@i);select @i;

觸發器TRIGGER

 觸發器，簡單來說就是當你在執行這條語句之前或者之後觸發一次增刪改查，觸發器用於定製使用者對錶的行進行【增/刪/改】前後的行為。

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

案例一插入前：

-- 在往tab1插入資料之前往tab2中插入一條name = 張岩林，當然是在判斷往tab1中插入的名字是不是等於aylindelimiter //CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROWBEGINIF NEW. NAME == ‘aylin‘ THEN    INSERT INTO tb2 (NAME)VALUES    (‘張岩林‘)ENDEND//delimiter ;

案例二插入後：

delimiter //CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROWBEGIN    IF NEW. num = 666 THEN        INSERT INTO tb2 (NAME)        VALUES            (‘張岩林‘),            (‘很帥‘) ;    ELSEIF NEW. num = 555 THEN        INSERT INTO tb2 (NAME)        VALUES            (‘aylin‘),            (‘非常帥‘) ;    END IF;END//delimiter ;

同樣的刪，改，查都是同樣的道理

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

2、刪除觸發器

DROP TRIGGER tri_after_insert_tb1;

3、使用觸發器

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

insert into tb1(name) values(‘張岩林’)

下章給大家更新資料庫索引，這方面東西比較多，所以樓主決定把他從新寫一篇部落格，看完的記得點贊喲！！

-此文章轉載-轉載自:http://www.cnblogs.com/aylin/p/5746501.html

MySQL(二)