MYSQL學習筆記——串連以及預存程序

標籤：style   blog   http   color   io   ar   使用   strong   sp   

串連                                                                                              

      當我們需要從多個表查詢資料時，我們就需要使用到串連操作，mysql支援內串連，左串連以及右串連三種串連方式。

資料庫準備

      首先我們建立兩個表t1, t2:

create table t1(i1 int, c1 varchar(10));create table t2(i2 int, c2 varchar(10));

　　插入資料：

insert into t1 values (1, ‘a‘), (2, ‘b‘), (3, ‘c‘);insert into t2 values (2, ‘c‘), (3, ‘b‘), (4, ‘a‘);

　　最後可以查看我們的資料庫表如下：

內串連

      如果在SELECT語句的FROM子句中列出多個資料表，並用INNER JOIN將它們的名字隔開，MYSQL將執行內串連操作，這將通過把一個資料表裡的資料行與另一個資料表裡的資料行進行匹配產生結果。比如，下面的查詢將把t1裡的每一個資料行與t2裡的每一個資料行組合：

      不加任何條件的內串連將產生笛卡爾積，結果行數是兩張資料表行數的乘積，例如兩張表分別有1000條資料，那麼結果行數將有100萬條資料，所以我們在用內串連時一般會加上條件，下面的查詢只會選出t1.i1=t2.i2的行，大大減少了檢出行：

外串連

      內串連只顯示在兩個資料表裡都能找到匹配的資料行，外串連除了顯示同樣的匹配結果，還可以把其中一個資料表在另一個資料表裡沒有匹配的資料行也顯示出來，mysql支援的外串連分左外串連（LEFT JOIN）和右外串連（RIGHT JOIN）。

      LEFT JOIN的工作情況是這樣的：兩個資料表，以左表為基準，當來自左表的某個資料行與右表的某個資料行匹配時，那兩個資料表的內容就會作為一個輸出行；如果右表沒有匹配行，那麼也會產生輸出行，此時右表的內容用NULL填充。

      RIGHT JOIN與LEFT JOIN相似，只是以右表為基準，如果左表不匹配，用NULL填充。

      我們用LEFT JOIN代替上例的INNER JOIN，得到的輸出如下：

      有時候，我們在編寫左串連時，真正感興趣的是那些左資料表裡沒被匹配的行，這時候我們可以用IS NULL判斷條件把這些行挑選出來：

      RIGHT JOIN與LEFT JOIN十分相似，而且所有RIGHT JOIN都能改寫成LEFT JOIN這裡就不舉例了，而且就我的工作經驗來看，工作中統一寫LEFT JOIN更好。

預存程序                                                                                           

      預存程序簡單來說，就是為以後的使用而儲存的一條或多條MYSQL語句的集合，可將其視為批檔案，雖然它們的作用不僅限於批處理。

1、資料庫準備

      首先使用以下命令建立一個資料庫並批量插入資料：

create table g(num int,value  varchar(10)); insert into g values(1, ‘1‘),(10, ‘10‘),(60, ‘60‘),(100, ‘100‘);

      查看該表資料如下：

　　

2、調用預存程序

      在建立預存程序之前，先說一下如何調用預存程序，調用預存程序的命令如下：

call 預存程序名字();

2.1、建立封裝sql預存程序

      預存程序的一個作用就是封裝sql，我們建立的第一個預存程序就是封裝一個select語句：

DELIMITER //CREATE PROCEDURE p1()BEGINselect * from g;END//DELIMITER ;

     "DELIMITER //"的作用是把行結束符替換為//，預設的行結束符是";"。為什麼要替換行結束符呢？因為建立預存程序是一條獨立語句，但是這條建立語句內部一般都會包含以";"結束的語句，如果我們不更改結束符，那麼語句解析到";"就會提前結束。

      以上面的語句為例，如果我們不更改行結束符，那麼建立語句到"select * from g;"就會結束（因為遇到了行結束符";"），這顯然不是我們想要的，但是我們更改結束符後，建立語句會一直解析到"END //"，成功建立預存程序。

      最後別忘了調用"DELIMITER ;"把行結束符改回來。

      建立完後我們調用一下該預存程序，結果如下：

      　　

2.2、建立包含參數的預存程序

      預存程序還可以包含參數，下面我們建立一個包含參數的預存程序　

DELIMITER //CREATE PROCEDURE p2(IN n int, OUT avg double, INOUT min int)BEGINselect avg(num) from g where num > n INTO avg;select min(num) from g where num > n INTO min;END//DELIMITER ;

      注意，在參數前麵包含IN，OUT，INOUT關鍵字，IN表示傳入值，OUT表示傳出值，INOUT表示即可以傳入也可以傳出。

      最後，我們調用一下我們建立的預存程序如下：

 

2.3、包含控制結構的預存程序

      預存程序還可以包含if-else結構，例子如下：

DELIMITER //CREATE PROCEDURE p3(IN n int, IN switch char(1))BEGINif switch=‘a‘ then        select * from g where num > n;    else     select * from g where num > n;end if;     END//DELIMITER ;

　　調用該預存程序的結果如下： 

2.4、包含迴圈的預存程序

      除了包含if-else結構，預存程序還可以包含while迴圈結構，舉例如下：

DELIMITER //CREATE PROCEDURE p4(IN n int)BEGINdeclare i int;declare s int;set i = 1;set s = 0;while i <= n do    set s = s + i;    set i = i + 1;end while;select s;END//DELIMITER ;

　　該預存程序將計算從1到n的累加，調用該預存程序的例子如下：

3、查看現有的預存程序

      如果想知道資料庫有哪些預存程序，我們可以使用以下命令查看：

show procedure status

4、刪除預存程序

      如果想要刪除預存程序，我們可以使用如下命令：

drop procedure 預存程序的名字

5、查看預存程序的建立語句

      有的時候我們想要查看預存程序的建立語句那麼我們可以使用以下命令：

show create procedure 預存程序的名字

6、預存程序與儲存函數　　

　　在mysql中除了預存程序，還有儲存函數，預存程序與儲存函數的區別有兩點：

      1、建立語句不一樣，預存程序使用命令"CREATE PROCEDURE"建立，儲存函數使用命令"CREATE FUNCTION"建立；

      2、預存程序沒有傳回值，而函數有傳回值。

MYSQL學習筆記——串連以及預存程序