視圖在SQL中的作用是什麼，它是怎樣工作的？

首發公衆号：碼農架構

視圖就是虛拟表:

如何建立，更新和删除視圖

建立視圖：CREATE VIEW

CREATE VIEW player_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player)           

當視圖建立之後，它就相當于一個虛拟表，可以直接使用：

SELECT * FROM player_above_avg_height           

嵌套視圖

CREATE VIEW player_above_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player_above_avg_height)           

修改視圖：ALTER VIEW

ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition           

删除視圖：DROP VIEW

DROP VIEW view_name           

需要說明的是，SQLite 不支援視圖的修改，僅支援隻讀視圖，也就是說你隻能使用 CREATE VIEW 和 DROP VIEW，如果想要修改視圖，就需要先 DROP 然後再 CREATE。

如何使用視圖簡化 SQL 操作

利用視圖完成複雜的連接配接

CREATE VIEW player_height_grades AS
SELECT p.player_name, p.height, h.height_level
FROM player as p JOIN height_grades as h
ON height BETWEEN h.height_lowest AND h.height_highest           

利用視圖對資料進行格式化

CREATE 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           

使用視圖與計算字段

CREATE VIEW game_player_score AS
SELECT game_id, player_id, (shoot_hits-shoot_3_hits)*2 AS shoot_2_points, shoot_3_hits*3 AS shoot_3_points, shoot_p_hits AS shoot_p_points, score  FROM player_score           

總結

使用視圖有很多好處，比如安全、簡單清晰。