视图在SQL中的作用是什么，它是怎样工作的？

原创

码农架构

修改于 2020-10-19 18:01:24

2.1K0

修改于 2020-10-19 18:01:24

文章被收录于专栏：码农架构码农架构

首发公众号：码农架构

视图就是虚拟表:

如何创建，更新和删除视图

创建视图：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