視圖：數據的虛擬窗口
在數據庫操作中，我們經常需要反覆執行復雜的查詢語句。例如，人力資源部門可能需要頻繁查詢員工的基本信息和薪資情況，這通常涉及多表連接。如果每次都編寫完整的SQL語句，不僅效率低下，還容易出錯。這時，視圖就能幫我們解決這個問題。
視圖的創建與管理
視圖是基於SQL查詢結果的虛擬表，它本身不存儲數據，而是動態引用底層表的數據。創建視圖的基本語法如下：
CREATE VIEW 視圖名稱 AS SELECT 查詢語句;例如，我們有一個 student 表和一個 score 表，現在需要創建一個視圖來顯示學生的姓名和對應的平均分：
CREATE VIEW student_avg_score AS
SELECT s.name, AVG(sc.score) AS avg_score
FROM student s
JOIN score sc ON s.id = sc.student_id
GROUP BY s.id, s.name;
創建完成後，我們就可以像查詢普通表一樣使用這個視圖：
SELECT * FROM student_avg_score;如果需要修改視圖，可以使用 ALTER VIEW 語句：
ALTER VIEW student_avg_score AS
SELECT s.name, AVG(sc.score) AS avg_score, s.class
FROM student s
JOIN score sc ON s.id = sc.student_id
GROUP BY s.id, s.name, s.class;刪除視圖則使用 DROP VIEW 語句：
DROP VIEW IF EXISTS student_avg_score;視圖的工作原理
視圖的工作原理主要有兩種算法：合併算法（Merge algorithm） 和 臨時表算法（Temp table algorithm）。
合併算法 是將視圖的查詢語句與用户的查詢語句合併，然後直接對底層表進行操作。這種方式效率較高，因為它避免了中間表的創建。例如，當我們查詢視圖 student_avg_score 中平均分大於80的學生時，MySQL 會將視圖定義中的 JOIN 和 GROUP BY 與用户查詢的 WHERE avg_score > 80 合併，生成一個新的查詢語句來執行。
臨時表算法 則是先執行視圖的查詢語句，將結果存儲在臨時表中，然後再對臨時表執行用户的查詢。這種方式適用於視圖包含聚合函數、DISTINCT 或 GROUP BY 等無法合併的情況。
視圖的優缺點及應用場景
優點：
- 簡化查詢：將複雜的查詢邏輯封裝在視圖中，用户只需簡單查詢視圖即可。
- 數據安全：可以通過視圖限制用户只能訪問特定的數據列，保護敏感信息。例如，我們可以創建一個只包含學生姓名和學號的視圖，而不包含成績等敏感數據。
- 邏輯獨立性：當底層表結構發生變化時，可以通過修改視圖來保持用户查詢的兼容性。
缺點：
- 性能問題：使用臨時表算法的視圖可能會導致性能下降，因為需要創建和操作臨時表。
- 修改限制：並非所有視圖都可更新。如果視圖包含聚合函數、GROUP BY、DISTINCT 或 JOIN 等操作，通常不允許直接修改視圖數據。
應用場景：
- 頻繁使用的複雜查詢，如多表連接、聚合計算等。
- 需要限制數據訪問權限的場景，如給不同用户展示不同的數據列。
- 需要保持查詢邏輯穩定性，即使底層表結構變化的情況。
遊標：逐行處理查詢結果
在 MySQL 中，當我們執行查詢語句時，通常會一次性獲取所有結果。但有時，我們需要逐行處理結果集，例如，對每一行數據進行復雜的計算或判斷。這時，遊標（Cursor） 就是一個非常有用的工具。
遊標的聲明與使用
遊標是一個數據庫對象，它允許我們逐行訪問查詢結果集。使用遊標的基本步驟如下：
- 聲明遊標：定義遊標的名稱和對應的查詢語句。
- 打開遊標：初始化遊標，使其指向結果集的第一行。
- 獲取數據：使用 FETCH 語句獲取當前行的數據，並移動遊標到下一行。
- 關閉遊標：釋放遊標佔用的資源。
以下是一個簡單的遊標使用示例，用於遍歷 student 表中的學生姓名：
DELIMITER //
CREATE PROCEDURE process_students()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE student_name VARCHAR(50);
-- 聲明遊標
DECLARE student_cursor CURSOR FOR
SELECT name FROM student;
-- 聲明異常處理，當遊標沒有更多數據時設置 done 為 TRUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打開遊標
OPEN student_cursor;
-- 循環獲取數據
read_loop: LOOP
-- 獲取當前行數據
FETCH student_cursor INTO student_name;
-- 判斷是否結束循環
IF done THEN
LEAVE read_loop;
END IF;
-- 處理數據，這裏簡單打印學生姓名
SELECT student_name;
END LOOP read_loop;
-- 關閉遊標
CLOSE student_cursor;
END //
DELIMITER ;
-- 調用存儲過程
CALL process_students();遊標的工作流程
遊標的工作流程可以分為以下幾個步驟：
- 聲明遊標：通過 DECLARE 遊標名稱 CURSOR FOR 查詢語句 定義遊標，指定要遍歷的結果集。
- 聲明異常處理：使用 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE 來處理遊標到達結果集末尾的情況。
- 打開遊標：使用 OPEN 遊標名稱 初始化遊標，此時遊標指向結果集的第一行之前。
- 循環獲取數據：使用 LOOP 或 REPEAT 循環，通過 FETCH 遊標名稱 INTO 變量列表 獲取當前行數據，並將遊標移動到下一行。
- 判斷結束條件：當 FETCH 語句無法獲取數據時，done 變量被設置為 TRUE，此時退出循環。
- 關閉遊標：使用 CLOSE 遊標名稱 釋放資源。
遊標在存儲過程中的應用
遊標通常與存儲過程結合使用，用於處理複雜的業務邏輯。例如，我們可以創建一個存儲過程，使用遊標計算每個學生的總分，並更新到 student 表中。
以下是一個示例，假設我們有 student 表（包含 id 和 name 字段）和 score 表（包含 student_id 和 score 字段）：
DELIMITER //
CREATE PROCEDURE calculate_total_score()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE s_id INT;
DECLARE total INT DEFAULT 0;
-- 聲明遊標
DECLARE student_cursor CURSOR FOR
SELECT DISTINCT student_id FROM score;
-- 聲明異常處理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打開遊標
OPEN student_cursor;
read_loop: LOOP
FETCH student_cursor INTO s_id;
IF done THEN
LEAVE read_loop;
END IF;
-- 計算總分
SELECT SUM(score) INTO total FROM score WHERE student_id = s_id;
-- 更新學生總分（假設 student 表有 total_score 字段）
UPDATE student SET total_score = total WHERE id = s_id;
END LOOP read_loop;
CLOSE student_cursor;
END //
DELIMITER ;
-- 調用存儲過程
CALL calculate_total_score();在這個示例中，遊標遍歷了 score 表中所有不同的 student_id，然後計算每個學生的總分，並更新到 student 表的 total_score 字段中。
需要注意的是，遊標操作可能會影響性能，特別是當結果集較大時。因此，在使用遊標時，應儘量優化查詢語句，減少結果集的大小，並確保及時關閉遊標。
實踐任務
任務一：創建和使用視圖
- 創建視圖：假設有 employees 表（包含 id、name、department、salary 字段），創建一個名為 dept_avg_salary 的視圖，顯示每個部門的名稱和平均工資。
CREATE VIEW dept_avg_salary AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;- 查詢視圖：查詢 dept_avg_salary 視圖，找出平均工資大於 6000 的部門。
SELECT * FROM dept_avg_salary WHERE avg_salary > 6000;- 修改視圖：向 dept_avg_salary 視圖中添加部門員工人數，列名為 employee_count。
ALTER VIEW dept_avg_salary AS
SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS employee_count
FROM employees
GROUP BY department;任務二：使用遊標處理數據
- 創建存儲過程：假設有 orders 表（包含 id、product_id、quantity 字段）和 products 表（包含 id、name、price 字段），創建一個存儲過程 calculate_order_total，使用遊標計算每個訂單的總金額（quantity * price），並將結果存儲在 order_totals 表（需提前創建，包含 order_id 和 total 字段）中。
-- 創建 order_totals 表
CREATE TABLE IF NOT EXISTS order_totals (
order_id INT,
total DECIMAL(10, 2),
PRIMARY KEY (order_id)
);
-- 創建存儲過程
DELIMITER //
CREATE PROCEDURE calculate_order_total()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE o_id INT;
DECLARE p_id INT;
DECLARE qty INT;
DECLARE prc DECIMAL(10, 2);
DECLARE total DECIMAL(10, 2) DEFAULT 0;
-- 聲明遊標，獲取訂單ID、產品ID和數量
DECLARE order_cursor CURSOR FOR
SELECT id, product_id, quantity FROM orders;
-- 聲明異常處理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 清空 order_totals 表
TRUNCATE TABLE order_totals;
-- 打開遊標
OPEN order_cursor;
read_loop: LOOP
FETCH order_cursor INTO o_id, p_id, qty;
IF done THEN
LEAVE read_loop;
END IF;
-- 獲取產品價格
SELECT price INTO prc FROM products WHERE id = p_id;
-- 計算總金額
SET total = qty * prc;
-- 插入到 order_totals 表
INSERT INTO order_totals (order_id, total)
VALUES (o_id, total);
END LOOP read_loop;
CLOSE order_cursor;
END //
DELIMITER ;- 調用存儲過程並驗證結果：
CALL calculate_order_total();
SELECT * FROM order_totals;通過今天的學習，我們掌握了視圖和遊標的基本概念、使用方法以及它們的優缺點和應用場景。視圖可以幫助我們簡化複雜查詢和保護數據安全，而遊標則允許我們逐行處理查詢結果，實現更靈活的數據操作。在實際開發中，合理使用視圖和遊標可以提高數據庫操作的效率和靈活性，但也要注意它們可能帶來的性能問題，做好優化工作。
