MYSQL系列之(二)

標籤：too   har   value   root   ret   com   imp   left join   mysq   

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

//內建函數  CHAR_LENGTH(str)        傳回值為字串str 的長度，長度的單位為字元。一個多位元組字元算作一個單字元。        對於一個包含五個二位元組字元集, LENGTH()傳回值為 10, 而CHAR_LENGTH()的傳回值為5。    CONCAT(str1,str2,...)        字串拼接        如有任何一個參數為NULL ，則傳回值為 NULL。    CONCAT_WS(separator,str1,str2,...)        字串拼接（自訂串連符）        CONCAT_WS()不會忽略任何Null 字元串。 (然而會忽略所有的 NULL）。    CONV(N,from_base,to_base)        進位轉換        例如：            SELECT CONV(‘a‘,16,2); 表示將 a 由16進位轉換為2進位字串表示    FORMAT(X,D)        將數字X 的格式寫為‘#,###,###.##‘,以四捨五入的方式保留小數點後 D 位， 並將結果以字串的形式返回。若  D 為 0, 則返回結果不帶有小數點，或不含小數部分。        例如：            SELECT FORMAT(12332.1,4); 結果為： ‘12,332.1000‘    INSERT(str,pos,len,newstr)        在str的指定位置插入字串            pos：要替換位置其實位置            len：替換的長度            newstr：新字串        特別的：            如果pos超過原字串長度，則返回原字串            如果len超過原字串長度，則由新字串完全替換    INSTR(str,substr)        返回字串 str 中子字串的第一個出現位置。    LEFT(str,len)        返回字串str 從開始的len位置的子序列字元。    LOWER(str)        變小寫    UPPER(str)        變大寫    LTRIM(str)        返回字串 str ，其引導空白字元被刪除。    RTRIM(str)        返回字串 str ，結尾空白字元被刪去。    SUBSTRING(str,pos,len)        擷取字串子序列    LOCATE(substr,str,pos)        擷取子序列索引位置    REPEAT(str,count)        返回一個由重複的字串str 組成的字串，字串str的數目等於count 。        若 count <= 0,則返回一個Null 字元串。        若str 或 count 為 NULL，則返回 NULL 。    REPLACE(str,from_str,to_str)        返回字串str 以及所有被字串to_str替代的字串from_str 。    REVERSE(str)        返回字串 str ，順序和字元順序相反。    RIGHT(str,len)        從字串str 開始，返回從後邊開始len個字元組成的子序列    SPACE(N)        返回一個由N空格組成的字串。    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)        不帶有len 參數的格式從字串str返回一個子字串，起始於位置 pos。帶有len參數的格式從字串str返回一個長度同len字元相同的子字串，起始於位置 pos。
 使用 FROM的格式為標準 SQL 文法。也可能對pos使用一個負值。假若這樣，則子字串的位置起始於字串結尾的pos 字元，而不是字串的開頭位置。在以下格式的函數中可以對pos 使用一個負值。        mysql> SELECT SUBSTRING(‘Quadratically‘,5);            -> ‘ratically‘        mysql> SELECT SUBSTRING(‘foobarbar‘ FROM 4);            -> ‘barbar‘        mysql> SELECT SUBSTRING(‘Quadratically‘,5,6);            -> ‘ratica‘        mysql> SELECT SUBSTRING(‘Sakila‘, -3);            -> ‘ila‘        mysql> SELECT SUBSTRING(‘Sakila‘, -5, 3);            -> ‘aki‘        mysql> SELECT SUBSTRING(‘Sakila‘ FROM -4 FOR 2);            -> ‘ki‘    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)        返回字串 str ， 其中所有remstr 首碼和/或尾碼都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的,則假設為BOTH 。 remstr 為可選項，在未指定情況下，可刪除空格。        mysql> SELECT TRIM(‘  bar   ‘);                -> ‘bar‘        mysql> SELECT TRIM(LEADING ‘x‘ FROM ‘xxxbarxxx‘);                -> ‘barxxx‘        mysql> SELECT TRIM(BOTH ‘x‘ FROM ‘xxxbarxxx‘);                -> ‘bar‘        mysql> SELECT TRIM(TRAILING ‘xyz‘ FROM ‘barxxyz‘);                -> ‘barx‘

 

更多參考請參考中文文檔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(‘張岩林’)

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

MYSQL系列之(二)