上一篇文章講的是mysql的基本操作,這一篇會有一點難以理解,本節主要內容mysql視圖,存儲過程,函數,事務,觸發器,以及動態執行sql
視圖是一個虛擬表,其內容由查詢定義。同真實的表一樣,視圖包含一系列帶有名稱的列和行數據。但是,視圖並不在數據庫中以存儲的數據值集形式存在。行和列數據來自由定義視圖的查詢所引用的表,並且在引用視圖時動態生成。對其中所引用的基礎表來說,視圖的作用類似於篩選。定義視圖的篩選可以來自當前或其它數據庫的一個或多個表,或者其它視圖。通過視圖進行查詢沒有任何限制,通過它們進行數據修改時的限制也很少。視圖是存儲在數據庫中的查詢的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 AS SELET A.nid,B. NAME FROM tab1 LEFT JOIN B ON A.id = B.nid LEFT JOIN C ON A.id = C.nid WHERE tab1.id > 2
也就只是改了把create改成alter,中間的語句更換了。
4、使用視圖
使用視圖時,將其當作表進行操作即可,由於視圖是虛擬表,所以無法使用其對真實表進行創建、更新和刪除操作,僅能做查詢用。
select * from v1
1、我們為什麼要用存儲過程呢 ?
我們都知道應用程序分為兩種,一種是基於web,一種是基於桌面,他們都和數據庫進行交互來完成數據的存取工作。假設現在有一種應用程序包含了這兩 種,現在要修改其中的一個查詢sql語句,那麼我們可能要同時修改他們中對應的查詢sql語句,當我們的應用程序很龐大很復雜的時候問題就出現這,不易維 護!另外把sql查詢語句放在我們的web程序或桌面中很容易遭到sql注入的破壞。而存儲例程正好可以幫我們解決這些問題。
2、創建存儲過程
創建存儲過程這塊主要有兩種,一種是帶參數的,一種是不帶參數的,先講不帶參數的再說不帶參數的
不帶參數案例:
-- 創建存儲過程 delimiter // --自定義語句結尾符號,因為這裡要執行好多句sql語句,所以就得自定義,以防止出錯 create procedure p1() BEGIN select * from tab1; END// delimiter ; --自定義局域結尾符號結束 -- 執行存儲過程 call p1()
帶參數案例這塊主要有三個類
-- 創建存儲過程 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; -- 設置變量默認值為3 DECLARE @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 pymysql conn = 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()
在mysql中有很多內置函數,比如我們經常用的求平均值,求和,個數,各式各樣,先給大家來一部門內置函數,然後再說說自定義函數吧,函數也可以傳參數,也可以接收返回值,但是函數沒辦法得到執行語句得到的結果,存儲過程可以。
CHAR_LENGTH(str) 返回值為字符串str 的長度,長度的單位為字符。一個多字節字符算作一個單字符。 對於一個包含五個二字節字符集, LENGTH()返回值為 10, 而CHAR_LENGTH()的返回值為5。 CONCAT(str1,str2,...) 字符串拼接 如有任何一個參數為NULL ,則返回值為 NULL。 CONCAT_WS(separator,str1,str2,...) 字符串拼接(自定義連接符) CONCAT_WS()不會忽略任何空字符串。 (然而會忽略所有的 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,則返回一個空字符串。 若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 int BEGIN 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;
觸發器,簡單來說就是當你在執行這條語句之前或者之後觸發一次增刪改查,觸發器用於定制用戶對表的行進行【增/刪/改】前後的行為。
1、基本語法
# 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN ... END # 插入後 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN ... END # 刪除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN ... END # 刪除後 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN ... END # 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN ... END # 更新後 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN ... END
事例一插入前:
-- 在往tab1插入數據之前往tab2中插入一條name = 張巖林,當然是在判斷往tab1中插入的名字是不是等於aylin delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. NAME == 'aylin' THEN INSERT INTO tb2 (NAME) VALUES ('張巖林') END END// delimiter ;
事例二插入後:
delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN 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(‘張巖林’)
下章給大家更新數據庫索引,這方面東西比較多,所以樓主決定把他從新寫一篇博客,看完的記得點贊喲!!