MySQL視圖view／預存程序和函數的使用

標籤：測試資料   子程式   rmi   root使用者   github   空間   輸入參數   roc   ping   

**************************************

MySql視圖view的使用：建立、修改、刪除

***************************************

1. MySql創建視圖

建立視圖與建立表文法類似，不同的是建立視圖是從一條查詢語句建立的。視圖建立後，可以像一張表一樣使用，但只能用於資料查詢，如：可以在一個查詢中使用、可以在預存程序中、可以在另一個視圖中使用。MySql建立視圖文法如下：

CREATE VIEW 視圖名 AS SELECT 查詢語句;

 

2. MySql視圖修改

已經建立的視圖，有時會需要修改其查詢欄位或查詢條件，MySql視圖修改文法如下：

ALTER VIEW 視圖名 AS SELECT 查詢語句;

 

3. MySql視圖刪除

MySql視圖刪除文法與刪除表DROP TABLE類型，文法如下：

DROP VIEW 視圖名;

 

3. MySql視圖使用

建立好視圖之後，可以直接通過視圖名使用視圖，文法如下：

SELECT * FROM 視圖名

 

***************************************

CREATE VIEW語句簡介

***************************************

要在MySQL中建立新視圖，請使用CREATE VIEW語句。 在MySQL中建立視圖的文法如下：

 

讓我們更詳細地查看瞭解以上文法。

視圖處理演算法

演算法屬性允許您控制MySQL在建立視圖時使用的機制。MySQL提供三種演算法：MERGE，TEMP TABLE和UNDEFINED。

• 使用MERGE演算法，MySQL首先將輸入查詢與定義視圖的SELECT語句組合成單個查詢。 然後MySQL執行組合查詢以返回結果集。如果SELECT語句包含彙總函式，如：MIN, MAX, SUM, COUNT, AVG或DISTINCT, GROUP BY, HAVING, LIMIT, UNION, UNION ALL，子查詢 等等則不允許MERGE演算法。如果SELECT語句沒有參考資料表，則也不允許MERGE演算法。如果不允許MERGE演算法，MySQL將演算法更改為UNDEFINED。 注意，輸入查詢和視圖定義中的查詢到一個查詢的組合被稱為視圖決議。
• 使用TEMP TABLE演算法，MySQL首先根據定義視圖的SELECT語句建立一個暫存資料表，然後對該暫存資料表執行輸入查詢。因為MySQL必須建立一個暫存資料表來儲存結果集，並將資料從基表移動到暫存資料表，TEMP TABLE演算法的效率低於MERGE演算法。此外，使用TEMP TABLE演算法的視圖不可更新。
  
• UNDEFINED是建立視圖時未指定顯式演算法的預設演算法。UNDEFINED演算法讓MySQL做出選擇使用MERGE或TEMP TABLE演算法。MySQL優先選擇MERGE演算法再到TEMP TABLE演算法，因為MERGE演算法更有效率。
  

視圖名稱

在資料庫中，視圖和表共用相同命名空間，因此，視圖和表不能具有相同的名稱。 此外，視圖的名稱必須遵循表命名規則。

SELECT語句

在SELECT語句中，可以從資料庫中存在的任何錶或視圖查詢資料。SELECT語句必須遵循幾個規則：

• SELECT語句可以在WHERE子句中包含子查詢，但不能在FROM子句中。
• SELECT語句不能引用任何變數，包括局部變數，使用者變數和會話變數。
• SELECT語句不能引用預先處理語句的參數。

注意，SELECT語句不需要引用任何錶。

 

 

 

 

 

**********************************************

Mysql中建立預存程序和函數的文法

**********************************************

mysql中預存程序和函數的文法非常接近所以就放在一起，主要區別就是函數必須有傳回值，並且函數的參數只有IN類型而預存程序有IN、OUT、INOUT這三種類型。

 

例子：

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

#建立資料庫

DROP DATABASE IF EXISTS Dpro;

CREATE  DATABASE Dpro

CHARACTER SET utf8

;

 

USE Dpro;

 

#建立部門表

DROP TABLE IF EXISTS Employee;

CREATE TABLE Employee

(id INT NOT NULL PRIMARY KEY COMMENT ‘主鍵‘,

 name VARCHAR(20) NOT NULL COMMENT ‘人名‘,

 depid INT NOT NULL COMMENT ‘部門id‘

);

 

#插入測試資料

INSERT INTO Employee(id,name,depid) VALUES(1,‘陳‘,100),(2,‘王‘,101),(3,‘張‘,101),(4,‘李‘,102),(5,‘郭‘,103);

 

#建立預存程序

#改變預設的分隔字元(;)為“$$”來作為預存程序結束的標識，隨後再恢複預設值。

DELIMITER $$    

 

DROP PROCEDURE IF EXISTS Pro_Employee;

CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )

READS SQL DATA

SQL SECURITY INVOKER

BEGIN

SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

# 多條sql語句

…

END$$

# $$代表格儲存體過程的結束，再恢複預設的分隔字元為(;)

DELIMITER ;

 

#執行預存程序, 變數名前面加上@，

CALL Pro_Employee(101,@pcount);

 

# 變數的查詢也需要加上@

SELECT @pcount;

 

# 列出所有預存程序

SHOW PROCEDURE STATUS;

# 查看一個已存在的預存程序的資訊

SHOW CREATE PROCEDURE <sp_name>;

 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

DELIMITER $$

 

create procedure 預存程序名字()   

(   

[in|out|inout] 參數 datatype   

)   

begin   

MySQL 語句;   

end$$

 

DELIMITER ;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

注意??

• MySQL 預存程序參數如果不顯式指定“in”、“out”、“inout”，則預設為“in”。習慣上，對於是“in” 的參數，我們都不會顯式指定。
• MySQL 預存程序名字後面的“()”是必須的，即使沒有一個參數，也需要“()”
• MySQL 預存程序參數，不能在參數名稱前加“@”，
• MySQL 用戶端使用者變數要加個“@”
• 提供了結構控制語句, 比如 IF, WHILE, CASE等等, 可以完成複雜的操作.另外, 定義預存程序, 以usp_首碼是區別系統預存程序和使用者自訂預存程序的最佳實務.SET 對已聲明的變數賦值或重新賦值，SELECT 顯示變數; SELECT var into out_var 將變數值寫入OUT參數。
  BEGIN
       DECLARE myvar INT;

       SET myvar = (SELECT id FROM users LIMIT 1);

           SELECT myvar into num;
       END $$

 

• MySQL 預存程序的參數不能指定預設值
• 如果 MySQL 預存程序中包含多條 MySQL 語句，則需要 begin end 關鍵字。
• MySQL 預存程序中的注釋。/* … */多行注釋
• 條件陳述式：declare c int; if a is null then  set c = 0;  end if； 
• 調用 MySQL 預存程序時候，需要在過程名字後面加“()”，即使沒有一個參數，也需要“()”
• 因為 MySQL 預存程序參數沒有預設值，所以在調用 MySQL 預存程序時候，不能省略參數。可以用 null 來替代。

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

 文法解釋：

 

在建立預存程序的時候一般都會用

DELIMITER $$…BEGIN…END$$  DELIMITER ;放在開頭和結束，目的就是避免mysql把預存程序內部的";"解釋成結束符號，最後通過“DELIMITER ;”來告知預存程序結束。

 

預存程序的參數有三種類型:

• IN: 輸入參數. 在調用預存程序時指定, 預設未指定類型時則是此類型.
• OUT: 輸出參數. 在預存程序裡可以被改變, 並且可返回.
• INOUT: 輸入輸出參數. IN 和 OUT 結合

 

characteristic部分：

 

LANGUAGE SQL：用來說明語句部分是SQL語句，未來可能會支援其它類型的語句。

[NOT] DETERMINISTIC：如果程式或線程總是對同樣的輸入參數產生同樣的結果，則被認為它是“確定的”，否則就是“非確定”的。如果既沒有給定DETERMINISTIC也沒有給定NOT DETERMINISTIC，預設的就是NOT DETERMINISTIC（非確定的）CONTAINS SQL：表示子程式不包含讀或寫資料的語句。

 

NO SQL：表示子程式不包含SQL語句。

READS SQL DATA：表示子程式包含讀資料的語句，但不包含寫資料的語句。

MODIFIES SQL DATA：表示子程式包含寫資料的語句。

SQL SECURITY DEFINER：表示執行預存程序中的程式是由建立該預存程序的使用者的許可權來執行。

SQL SECURITY INVOKER：表示執行預存程序中的程式是由調用該預存程序的使用者的許可權來執行。（例如上面的預存程序我寫的是由調用該預存程序的使用者的許可權來執行，當前預存程序是用來查詢Employee表，如果我當前執行預存程序的使用者沒有查詢Employee表的許可權那麼就會返回許可權不足的錯誤，如果換成DEFINER如果預存程序是由ROOT使用者建立那麼任何一個使用者登入調用預存程序都可以執行，因為執行預存程序的許可權變成了root）

 

COMMENT ‘string‘:備忘，和建立表的欄位備忘一樣。

 

注意：在編寫預存程序和函數時建議明確指定上面characteristic部分的狀態，特別是存在複製的環境中，如果建立函數不明確指定這些狀態會報錯，從一個非複製環境將帶函數的資料庫遷移到複製環境的機器上如果沒有明確指定DETERMINISTIC, NO SQL, or READS SQL DATA該三個狀態也會報錯。

 

 

 

**********************************************

Mysql中交易處理塊

**********************************************

http://gaunthan.leanote.com/post/MySQL-%E4%BA%8B%E5%8A%A1%E4%B8%8E%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86

 

https://github.com/MrLining/mysql/wiki/Mysql%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86#mysql%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86

MySQL視圖view／預存程序和函數的使用