Mysql學習筆記（十）預存程序與函數 + 知識點補充（having與where的區別）

標籤：

原文:Mysql學習筆記（十）預存程序與函數 + 知識點補充（having與where的區別）

學習內容：
儲存程式與函數。。。這一章學的我是雲裡霧裡的。。。

1.預存程序。。。

  Mysql預存程序是從mysql 5.0開始增加的一個新功能.預存程序的優點其實有很多，不過我覺得預存程序最重要的優點就是實現了SQL代碼的封裝，那麼我們為什麼需要封裝SQL語句呢？原因就是當我們在面對一個龐大的資料庫的時候，當我們使用外部程式去訪問資料庫的時候。。。我們總不能在外部程式中內嵌很多的SQL語句吧。。。那樣執行的效率不高，並且也不容易維護...因此預存程序將我們的操作進行封裝，當我們需要對其進行操作的時候，我們只需要調用預存程序就可以了...

create procedure cl_add(    a int,    b int)begin                 //預存程序的執行過程需要定義在begin。。。。end語句中...  declare c int;      //聲明一個變數。。declare只能使用在預存程序或者函數裡面，否則會出錯..  if a is null then   //if 語句 ，用來進行條件判斷。。滿足條件則執行滿足條件的語句..這裡的if和if()是不同的。。if()是控制流程程函數。。if表示條件判斷的語句...二者是不一樣的..     set a=0;  end if;  if b is null then     set b=0;         //set 指派陳述式...可以接簡單的語句還可以接複雜的函數...  end if;  set c=a+b;  select c as sum;  /*    return c; 這會產生錯誤...  */end;註：預存程序中不能使用return...return只能使用在函數中...預存程序的調用...call cl_add(10,20); //預存程序需要使用call函數來進行調用...set @a=10; set @b=20; //我們還可以定義兩個使用者變數...call cl_add(@a,@b); //將使用者變數的值傳遞過去...

2.儲存函數。。。

 

儲存函數也是由一個或多個SQL語句組成的，目的是將代碼封裝以便重新使用...也是為了方便開發人員操作資料庫...

 

存 儲函數使用的限制：1.不能使用暫存資料表。2.不能在儲存函數中定義timestamp，cursor,table的資料類型...3.定義函數時參數只允 許是in類型...4.系統內建了一些儲存函數，在調用這些函數的時候...需要加::首碼...並啟用allow updates伺服器選項，才能將使用者自訂的函數的所有者定義為系統類別型..

 

 

DELIMITER //CREATE FUNCTION NameByT()RETURNS CHAR(50)RETURN (SELECT NAME FROM t3 WHERE id=2);//DELIMITER ;

 

建立儲存函數，名稱為NameByT，該函數返回SELECT語句的查詢結果，數實值型別為字串型...

注意：RETURNS CHAR(50)資料類型的時候，RETURNS 是有S的，而RETURN (SELECT NAME FROM t3 WHERE id=2)的時候RETURN是沒有S的

 

3.儲存函數和預存程序的區別...

本質上都是實現SQL代碼塊的封裝，方便對資料庫的操作...

預存程序和函數存在以下幾個區別：

    1）一般來說，預存程序實現的功能要複雜一點，而函數的實現的功能針對性比較強。預存程序，功能強大，可以執行包括修改表等一系列資料庫操作；使用者定義函數不能用於執行一組修改全域資料庫狀態的操作。     2）對於預存程序來說可以返回參數，如記錄集，而函數只能傳回值或者表對象。函數只能返回一個變數；而預存程序可以返回多個。預存程序的參數可以有 IN,OUT,INOUT三種類型，而函數只能有IN類~~預存程序聲明時不需要傳回型別，而函式宣告時需要描述傳回型別，且函數體中必須包含一個有效 RETURN語句。     3）預存程序，可以使用非確定函數，不允許在使用者定義函數主體中內建非確定函數。     4）預存程序一般是作為一個獨立的部分來執行（ EXECUTE 語句執行），而函數可以作為查詢語句的一個部分來調用（SELECT調用），由於函數可以返回一個表對象，因此它可以在查詢語句中位於FROM關鍵字的後 面。 SQL語句中不可用預存程序，而可以使用函數。 總結：

  使用者自訂函數在處理同一資料行中的各個欄位時，特別方便有用。雖然這裡使用預存程序也能達到查詢目的，但是顯然沒有使用函數方便。而且，即使使用預存程序也無法處理SELECT查詢中的同一資料行中的各個欄位的運算。因為預存程序不傳回值（唯一可以直接返回整型值，雖然沒有傳回值，但是可以在預存程序中輸出參數來完成返回），使用時只能單獨調用；而函數卻能出現在能放置運算式的任何位置。

4.Mysql也可以使用declare定義條件和儲存程式來解決一些問題...這裡所說的問題一般就是錯誤，當我們在處理一些錯誤的時候，我們可以自訂一個程式來處理錯誤....

CREATE TABLE t8(s1 INT,PRIMARY KEY(s1))DELIMITER //CREATE PROCEDURE handlerdemo()BEGINDECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000‘ SET @X2=1;SET @X=1;INSERT INTO t8 VALUES(1);SET @X=2;INSERT INTO t8 VALUES(1);SET @X=3;END;//DELIMITER ;/* 調用預存程序*/CALL handlerdemo();/* 查看調用預存程序結果*/SELECT @X

  這裡我們插入了兩次1。。這在主鍵裡是不允許出現的....因此我們使用了DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000‘ SET @X2=1;這個語句來處理這個錯誤發生...如果沒有這句話。。這段代碼就是錯誤的....

5.知識點有所遺漏,也是今天偶爾發現自己不會的一個知識點。。。

資料庫查詢語句中的having與where的區別。。。

一個很小的知識點。。。不過比較重要。。。

  一般在sql中，大多數情況下都是使用where，而很少使用having，where和having基本差不多，having子句在查詢過程中慢於彙總語句，where子句在查詢過程中快與查詢語句，因此大多數的情況下都是使用where的。。。

SELECT * FROM `welcome` HAVING id >1 LIMIT 0 , 30SELECT * FROM `welcome` WHERE id >1 LIMIT 0 , 30//這兩種運行結果是一樣的。。。在多數情況下能使用where的時候就盡量不要使用having，。因為where要快於彙總語句。。。having的使用是要彌補where在分組資料判斷時的不足。。。比如說下面代碼。。。。SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary)>10;SELECT user, MAX(salary) FROM users GROUP BY user WHERE MAX(salary)>10;第二種語句就會出現錯誤，在資料庫中where的後面是不允許加帶判斷性的彙總函式的。。因此如果當我們統計資料的時候使用到了彙總語句...我們就只能使用having了。。如果不用這些關係，那麼當然where是首選。。。

再補充幾點：

1、SQL標準要求HAVING必須引用GROUP BY子句中的列或用於總計函數中的列。不過，MySQL支援對此工作性質的擴充，並允許HAVING涉及SELECT清單中的列和外部子查詢中的列。

2、HAVING子句必須位於GROUP BY之後ORDER BY之前。

3、如果HAVING子句引用了一個意義不明確的列，則會出現警告。在下面的語句中，col2意義不明確，因為它既作為別名使用，又作為列名使 用：mysql> SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

標準SQL工作性質具有優先權，因此如果一個HAVING列名既被用於GROUP BY，又被用作輸出資料行清單中的起了別名的列，則優先權被給予GROUP BY列中的列。

4、HAVING子句可以引用總計函數，而WHERE子句不能引用。【這應該是開發人員在特定的情況下採用HAVING子句的最大原因】

5、不要將HAVING用於應被用於WHERE子句的條目，從我們開頭的2條語句來看，這樣用並沒有出錯，但是mysql不推薦。而且也沒有明確說明原因，但是既然它要求，我們遵循就可以了。

 

Mysql學習筆記（十）預存程序與函數 + 知識點補充（having與where的區別）