第五篇 : MySQL 之 檢視、觸發器、儲存過程、函式、事物與資料庫鎖

Q_Q_JOE發表於2018-03-01
MySql觸發器儲存過程函式資料庫

一、檢視

檢視是一個虛擬表（非真實存在的），其本質是‘根據SQL語句獲取動態的資料集，併為其命名‘ ，使用者使用時只需使用“名稱”即可獲取結果集，可以將該結果集當做表來使用。

使用檢視我們可以把查詢過程中的臨時表摘出來，用檢視去實現，這樣以後再想操作該臨時表的資料時就無需重寫複雜的SQL了，直接去檢視中查詢即可，但檢視有明顯的效率問題，並且檢視是存放在資料庫中的，如果我們程式中使用的SQL過分依賴資料庫中的檢視，即強耦合，那就意味著擴充套件SQL極為不便，因此並不推薦使用

1
2
3
4
5
6
7
檢視有如下的特點：
 
    1.檢視的列可以來自不同的表，是表的抽象和邏輯意義上建立的新關係
    2.檢視是有基本表（實表）產生的表（虛表）
    3.檢視的建立和刪除不影響基本表
    4.對檢視內容的更新（新增、刪除和修改）直接影響基本表
    5.當檢視來自多個基本表時，不允許新增，修改和刪除資料

　1.建立檢視

1
語法：<br>create view 檢視名稱 as sql語句<br><br>ps：create view db1 as select name from ren;
1
2
3
4
#！！！注意注意注意：
#1. 使用檢視以後就無需每次都重寫子查詢的sql，但是這麼效率並不高，還不如我們寫子查詢的效率高
 
#2. 而且有一個致命的問題：檢視是存放到資料庫裡的，如果我們程式中的sql過分依賴於資料庫中存放的檢視，那麼意味著，一旦sql需要修改且涉及到檢視的部分，則必須去資料庫中進行修改，而通常在公司中資料庫有專門的DBA負責，你要想完成修改，必須付出大量的溝通成本DBA可能才會幫你完成修改，極其地不方便

　2　使用檢視

1
2
語法
select * from 檢視名稱;ps：修改檢視，原始表也會跟著改偽需求1)查詢表select * from course2）建立一個course的檢視create view course_view as select *from course;3）查詢course_view檢視select *from course_vies 4)更新course_view檢視的內容update course_view set cname='xxxx';5)往檢視中插入資料insert into course_view values(5,'yyy',2);6)檢視原始表，發現原始表的記錄也跟著修改了
select * from course;

　我們不應該修改檢視中的記錄，而且在涉及多個表的情況下是根本無法修改檢視中的記錄的

3.修改檢視

1
2
3
語法：
 
alter view 檢視名稱 as sql語句<br><br>ps：
alter view teacher_view as select * from course where cid>3;

4.刪除檢視

1
2
語法：
drop view 檢視名稱<br>ps：
DROP VIEW teacher_view

二、觸發器

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

1
2
3
4
5
6
7
觸發器：監視某種情況，並觸發某種操作
觸發器建立語法四要素：
 
    1.監視地點（table）
    2.監視事件（insert/update/delete）
    3.觸發時間（after/before）
    4.觸發事件（insert/uodate/delete）

1.建立觸發器語法

1
2
3
4
5
6
7
8
9
10
11
12
create trigger 觸發器名稱 after/before insert/update/delete
    on 表名 for each row
begin
 
#需要執行的sql語句
 
end
 
--------------------------------------------------------
 
注意1：after/before：只能選一個，after 表示 後置觸發，before 表示前置觸發
注意2:insert/update/delete:只能選一個

需求：建立兩張表

#商品表

create table goods(

  id int primary key auto_increment

  name varchar(20),

  num int

);

#訂單表

create table order_table(

    oid int primary key auto_increment,

    gid int,

    much int

);

問題

1.新增3條商品資料

1
insert into goods(name,num) values ('蘋果','10'),('草莓','15'),('金桔','20');

如果我們在沒使用觸發器之前：假設我們現在賣了3個商品1，我們需要做兩件事　　

NO.1 往訂單表插入一條記錄

1
insert into ORDER_table(gid,much) VALUES (1,3) ;

NO.2 更新商品表蘋果的剩餘數量　　

1
update goods set num = num - 3 WHERE name = '蘋果';

------------------------------------------------------------------------------------------------------------------------------------------------

NOW現在，我們來建立一個觸發器：

1
2
3
4
5
create trigger tg1 after insert on order_table
for each row
begin
 update goods set num = num -3 where id = 1;
end

這時候我們只要執行：

1
insert into order_table (gid,much) values(1,3);

就會發現蘋果的數量變成了7，說明在我們插入一條訂單的時候，

觸發器自動幫我們做了更新操作。

但現在會有一個問題，因為我們觸發器裡面num和ID都寫死了，所以不管我們買那個商品，最終更新的都是蘋果的數量。比如：我們往訂單表再插入一條記錄：

1
insert into order_table(gid,much) values(2,3);

　執行完後會發現蘋果的數量變4了，而草莓的數量沒變，這樣顯然不是我們想要的結果。我們需要改改我們之前建立的觸發器。

　

我們如何在觸發器引用行的值，也就是說我們要得到我們新插入的訂單記錄中的gid或much的值。

對於insert而言，新插入的行用new來表示，行中的每一列的值用new.列名來表示。

所以現在我們可以這樣來改我們的觸發器:

1
2
3
4
5
6
7
create trigger tg2 after insert on order_table for each ROW
 
begin
 
UPDATE goods set num = num - new.much where id = new.gid;
 
end

第二個觸發器建立完畢，我們先把第一個觸發器刪掉

1
drop trigger tg1;

再來測試一下，插入一條訂單記錄：

1
insert into order_table(gid,name) values(2,3);

執行完程式碼，發現草莓的num變成了7，現在就對了　

相關文章