Day4 MySql觸發器視圖索引以及設計最佳化

標籤：表名   情況   sele   1nf   虛擬   約束   column   方法   一般來說   

觸發器

MySQL包含對觸發器的支援。觸發器是一種與表操作有關的資料庫物件，當觸發器所在表上出現指定事件時，將調用該對象，即表的操作事件觸發表上的觸發器的執行。

通過事件觸發,不能傳參

文法

CREATE    [DEFINER = { user | CURRENT_USER }]    TRIGGER trigger_name    trigger_time trigger_event    ON tbl_name FOR EACH ROW    [trigger_order]    trigger_bodytrigger_time: { BEFORE | AFTER }trigger_event: { INSERT | UPDATE | DELETE }trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

其中：

trigger_name：標識觸發器名稱，使用者自行指定；
trigger_time：標識觸發時機，取值為 BEFORE 或 AFTER；
trigger_event：標識觸發事件，取值為 INSERT、UPDATE 或 DELETE；
tbl_name：標識建立觸發器的表名，即在哪張表上建立觸發器；
trigger_stmt：觸發器程式體，可以是一句SQL語句，或者用 BEGIN 和 END 包含的多條語句。

由此可見，可以建立6種觸發器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一個限制是不能同時在一個表上建立2個相同類型的觸發器，因此在一個表上最多建立6個觸發器。

trigger_event  詳解

MySQL 除了對 INSERT、UPDATE、DELETE 基本操作進行定義外，還定義了 LOAD DATA 和 REPLACE 語句，這兩種語句也能引起上述6中類型的觸發器的觸發。

LOAD DATA 語句用於將一個檔案裝入到一個資料表中，相當與一系列的 INSERT 操作。

REPLACE 語句一般來說和 INSERT 語句很像，只是在表中有 primary key 或 unique 索引時，如果插入的資料和原來 primary key 或 unique 索引一致時，會先刪除原來的資料，然後增加一條新資料，也就是說，一條 REPLACE 語句有時候等價於一條。

INSERT 語句，有時候等價於一條 DELETE 語句加上一條 INSERT 語句。

INSERT 型觸發器：插入某一行時啟用觸發器，可能通過 INSERT、LOAD DATA、REPLACE 語句觸發；
UPDATE 型觸發器：更改某一行時啟用觸發器，可能通過 UPDATE 語句觸發；
DELETE 型觸發器：刪除某一行時啟用觸發器，可能通過 DELETE、REPLACE 語句觸發。

BEGIN … END 詳解

在MySQL中，BEGIN … END 語句的文法為：

BEGIN
[statement_list]
END
其中，statement_list 代表一個或多個語句的列表，列表內的每條語句都必須用分號（;）來結尾。
而在MySQL中，分號是語句結束的標識符，遇到分號表示該段語句已經結束，MySQL可以開始執行了。因此，解譯器遇到statement_list 中的分號後就開始執行，然後會報出錯誤，因為沒有找到和 BEGIN 匹配的 END。

這時就會用到 DELIMITER 命令（DELIMITER 是定界符，分隔字元的意思），它是一條命令，不需要語句結束標識，文法為：
DELIMITER new_delemiter
new_delemiter 可以設為1個或多個長度的符號，預設的是分號（;），我們可以把它修改為其他符號，如$：
DELIMITER $
在這之後的語句，以分號結束，解譯器不會有什麼反應，只有遇到了$，才認為是語句結束。注意，使用完之後，我們還應該記得把它給修改回來。

案例一

--建立一個觸發器cls_tri，當從classroom刪除一條資料候，將這條資料添加到clsbak備份表中create TRIGGER cls_tri after DELETE on classroom for each ROWBEGIN -- 添加到clsbak   old   new insert into clsbak values(old.cid,old.cname);END;delete from classroom where cid = 9;

案例二

一個完整的建立觸發器樣本
假設系統中有兩個表：
班級表 class(班級號 classID, 班內學生數 stuCount)
學生表 student(學號 stuID, 所屬班級號 classID)
要建立觸發器來使班級表中的班內學生數隨著學生的添加自動更新

ELIMITER $create trigger tri_stuInsert after inserton student for each rowbegindeclare c int;set c = (select stuCount from class where classID=new.classID);update class set stuCount = c + 1 where classID = new.classID;end$DELIMITER ;

變數詳解

MySQL 中使用 DECLARE 來定義一局部變數，該變數只能在 BEGIN … END 複合陳述式中使用，並且應該定義在複合陳述式的開頭，

即其它語句之前，文法如下：

DECLARE var_name[,...] type [DEFAULT value]
其中：
var_name 為變數名稱，同 SQL 陳述式一樣，變數名不區分大小寫；type 為 MySQL 支援的任何資料類型；可以同時定義多個同類型的變數，用逗號隔開；變數初始值為 NULL，如果需要，可以使用 DEFAULT 子句提供預設值，值可以被指定為一個運算式。

對變數賦值採用 SET 語句，文法為：

SET var_name = expr [,var_name = expr] ...

NEW  與 OLD 詳解

上述樣本中使用了NEW關鍵字，和 MS SQL Server 中的 INSERTED 和 DELETED 類似，MySQL 中定義了 NEW 和 OLD，用來表示

觸發器的所在表中，觸發了觸發器的那一行資料。
具體地：
在 INSERT 型觸發器中，NEW 用來表示將要（BEFORE）或已經（AFTER）插入的新資料；
在 UPDATE 型觸發器中，OLD 用來表示將要或已經被修改的原資料，NEW 用來表示將要或已經修改為的新資料；
在 DELETE 型觸發器中，OLD 用來表示將要或已經被刪除的原資料；
使用方法： NEW.columnName （columnName 為相應資料表某一列名）
另外，OLD 是唯讀，而 NEW 則可以在觸發器中使用 SET 賦值，這樣不會再次觸發觸發器，造成迴圈調用（如每插入一個學生前，都在其學號前加“2013”）。

查看觸發器

和查看資料庫（show databases;）查看錶格（show tables;）一樣，查看觸發器的文法如下：

SHOW TRIGGERS [FROM schema_name];
其中，schema_name 即 Schema 的名稱，在 MySQL 中 Schema 和 Database 是一樣的，也就是說，可以指定資料庫名，這樣就

不必先“USE database_name;”了。

刪除觸發器

和刪除資料庫、刪除表格一樣，刪除觸發器的文法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

觸發器的執行順序

我們建立的資料庫一般都是 InnoDB 資料庫，其上建立的表是事務性表，也就是事務安全的。這時，若SQL語句或觸發器執行失敗，MySQL 會復原事務，有：

①如果 BEFORE 觸發器執行失敗，SQL 無法正確執行。
②SQL 執行失敗時，AFTER 型觸發器不會觸發。
③AFTER 類型的觸發器執行失敗，SQL 會復原

視圖定義

虛擬表。以基表為基礎，動態地在使用的過程中將需要展示的資料查詢出來的虛擬表。

文法

create or replace view view_emp1ASselect * from emp1 where deptno=10;

with check option

保證視圖安全。where子句後的條件欄位不能修改。

視圖的優點

　　a)  簡化查詢

　　b)  資料的安全性(隱藏)

　　c)  實現欄位合并，分割   

索引檢視表的優點

　　1.簡化查詢

　　2.資料的安全性(隱藏)

　　3.實現欄位合并，分割   

適合使用索引情況

　　1.經常作為查詢條件的列。

　　2.在高基數列添加索引。

　　3.在資料量大的表中添加索引。

不適合使用索引的情況

　　1.經常進行增刪改的表(需要維護)

　　2.資料量小，低基數

　　3.不能添加太多索引

案例

//建立索引create index ind_name on user(userid);//刪除索引drop index ind_name on user;

資料庫的設計資料庫的設計範式

冗餘   /   查詢效率  / 擴充

A. 第一範式  1NF

所有的域都具有原子性。欄位值不可分割。

B. 第二範式  2NF

基於第一範式。

所有的非主鍵欄位必須直接與主鍵欄位關聯，而不能與主鍵的部分關聯(聯合主鍵)

一張表描述一個實體。

C. 第三範式 3NF

確保每列都和主鍵列直接相關,而不是間接相關

非主鍵欄位之間不能存在直接關聯。

資料庫的設計步驟A. 需求分析階段

 準確瞭解與分析使用者需求（包括資料與處理）

 是整個設計過程的基礎，是最困難、最耗費時間的一步

B. 概念結構設計階段

 是整個資料庫設計的關鍵

設計資料庫的E-R模型圖，確認需求資訊的正確和完整

C. 邏輯設計階段

將E-R圖轉換為邏輯模型(邏輯模型為關聯式模式則體現為多張表)

應用程式資料庫設計的三大範式進行審核

設計外模式，建立視圖

D. 實體設計階段

確定存取方法（例如索引的設定）

確定儲存結構（檔案存放的位置等），並編寫代碼實現前端應用

E. 資料庫實施階段

將資料載入，並對資料庫進行調試

F. 運行和維護階段

使用和維護資料庫

E-R模型圖

提供了表示實體類型、屬性和聯絡的方法，用來描述現實世界的概念性模型。

在ER圖中有如下四個成分：

   矩形框：表示實體，在框中記入實體名。

   菱形框：表示聯絡，在框中記入聯絡名。

   橢圓形框：表示實體或聯絡的屬性，將屬性名稱記入框中。對於主屬性名稱，則在其名稱下劃一底線。

   連線：實體與屬性之間；實體與聯絡之間；聯絡與屬性之間用直線相連，並在直線上標註聯絡的類型。（對於一對一聯絡，要在兩個實體連線方向各寫1； 對於一對多聯絡，要在一的一方寫1，多的一方寫N；對於多對多關係，則要在兩個實體連線方向各寫N,M。)

實體關聯(表)A.         一對一：（個人資訊和檔案資訊）

外鍵欄位添加唯一約束

主鍵做外鍵

B.       一對多:（emp和dept）C.       多對多:（student和corse）

建立關係表設計聯合主鍵。

資料庫的最佳化

查詢記錄數越少越好

盡量走索引

 最佳化原則

1) 盡量減少使用 *

2) 索引失效:

　　a) 索引列使用is null/is not null，索引失效

　　b) 索引列不能使用函數(upper lower )

　　　　包含也會導致索引失效。

　　c) 索引列不能計算。

　　d) 索引列使用not != <>索引失效。

　　e) 使用or索引失效，使用union代替。

　　f)  用EXISTS替代IN、用NOT EXISTS替代NOT IN

備份與還原

在cmd中執行命令

--備份(匯出)mysqldump -uroot -proot dbname > d:/t.msql;   --匯入mysql -u root -p < C:\backup.sql   

 

Day4 MySql觸發器視圖索引以及設計最佳化