SQL基礎隨記2 視圖 存儲過程

View

• CREATE/ALTER/DROP VIEW ViewName as SELECT(...)
  

• 可以在視圖的基礎上繼續創建視圖，即，將之前創建的視圖當做表名放在新視圖的select語句中

• 利用視圖進行數據格式化（就是對欄位進行重新組合與拼接）

  CREATE VIEW View_player_team AS 
  SELECT CONCAT(player_name, '(' , team.team_name , ')') AS player_team
  FROM player JOIN team WHERE player.team_id = team.team_id
  

• 視圖具有安全性，因為視圖是虛擬表，即使對視圖做出了修改，也不會影響底層數據。

PROCEDURE

• 存儲過程就像“自定義函數”，它是SQL語句和流控制語句的集合，它可以接受參數，也可以返回參數。

• 存儲過程可以（不是一定）直接對底層數據表進行操作

• CREATE PROCEDURE name(參數)
  BEGIN
  	需要執行的過程
  END
  

  舉例1，

  DELIMITER //
  
  CREATE PROCEDURE `存儲過程名`(形參)
  BEGIN
  	迴圈體
  END //
  
  DELIMITER;
  

  調用存儲過程舉例

  CALL `存儲過程名`(實參);
  select @(out參數)
  

  • DELIMITER是將分隔符變為 // （也可以使用其他的），因為流控制語句中的語句需要;分割，此舉是避免混淆

  • 開頭和結尾的DELIMITER都是另起一行mysql shell

  • 存儲過程名用的不是單引號，是英文的 "頓點"

  • 流控制語句

    DECLIARE --- 聲明變數
    SET --- 初始化，賦值
    IF...ELSE...
    
    REPEAT...UNTIL...END REPEAT --- 滿足UNTIL後面的表達式則退出迴圈
    WHILE...DO...END WHILE --- 先判斷條件，滿足則迴圈
    CONTINUT;
    BREAK;
    RETURN;
    GOTO (label)
    WAITFOR TIME 'time' --- 在某個時刻執行
    WAITFOR DELAY 'time' --- 延遲某個時間執行
    SELECT...INTO... --- 將查詢表中的結果放入變數中
    

• 參數類型

  • IN --- 傳入參數，無法返回，不寫參數類型預設為IN
  • OUT --- 將存儲過程的計算結果放入OUT參數中並用於返回
  • INOUT --- 既用於傳入參數又可以將結果存入該參數中用於返回

• PROCEDURE的優點

  • 安全性強，設定存儲過程的時候可以設置對用戶的使用許可權
  • 執行效率快，在MySQL資料庫伺服器端執行
  • 通過封裝，減少網路傳輸量

• PROCEDURE的缺點 --- 阿裡等公司不推薦使用的原因

  • 調試困難，僅少部分DBMS支持調試
  • 可移植性差
  • 版本管理困難
  • 不適合高併發場景 --- 高併發需要減小資料庫壓力，可能會採用分庫分表的方式且對拓展性要求高。在這種情況下，存儲過程變得難以維護且會增加資料庫的壓力

procedure 舉例1 按某個欄位對錶進行查詢

• 查看已有測試表

  mysql> SELECT * FROM t; 
  +----+------+
  | Id | nums |
  +----+------+
  |  1 |    2 |
  +----+------+
  1 row in set (0.00 sec)
  

  創建存儲過程

  mysql> DELIMITER //
  mysql> CREATE PROCEDURE `findByNums`(IN n int)
      -> BEGIN
      -> SELECT * FROM t WHERE nums = n;
      -> END //
  Query OK, 0 rows affected (0.03 sec)
  

  檢驗

  mysql> DELIMITER ;
  mysql> CALL findByNums(2);
  +----+------+
  | Id | nums |
  +----+------+
  |  1 |    2 |
  +----+------+
  1 row in set (0.03 sec)
  
  Query OK, 0 rows affected (0.03 sec)
  

  （傳入不同參數）

  mysql> CALL findByNums(1);
  Empty set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)
  

   

• 查詢存儲過程

  mysql> SELECT * FROM mysql.proc WHERE db = 'test' AND type = 'procedure';
  

procedure 舉例2 計算累和結果

• 使用存儲過程傳入/傳出參數，查詢輸出

  mysql> delimiter //
  mysql> create procedure `add_sum`(in n int, out result int)
      -> begin
      -> declare i int;
      -> declare sum int;
      -> set i = 1;
      -> set sum = 0;
      -> while i < n do
      -> set sum = sum + i;
      -> set i = i + 1;
      -> end while;
      -> end //
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> delimiter ;
  mysql> call add_sum(50,@result);
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select @result;
  +---------+
  | @result |
  +---------+
  |    1225 |
  +---------+
  1 row in set (0.00 sec)
  
  

• 無法直接@存儲過程中的變數

  mysql> create procedure `add_sum_procedure`(in n int)
      -> begin
      -> declare i int;
      -> declare sum int;
      -> set i = 1;
      -> set sum = 0;
      -> while i < n do
      -> set sum = sum + i;
      -> set i = i + 1;
      -> end while;
      -> end //
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> delimiter ;
  mysql> call add_sum_procedure(10);
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> select @sum;
  +------+
  | @sum |
  +------+
  | NULL |
  +------+
  1 row in set (0.01 sec)
  

查詢所有存儲過程/視圖 等等

• https://www.cnblogs.com/G-Aurora/p/13196812.html