MySQL(二)，mysql

MySQL(二)，mysql

MYSQL(二)

上一篇文章講的是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 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

存儲過程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;           --  設置變量默認值為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()

函數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 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;

觸發器TRIGGER

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

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(‘張巖林’)

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

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