sql server 视图查询增加显示伪列

视图

视图也就是虚拟表, 本身不具备数据, 是SQL中的一个变红要概念. 如图

视图可以帮助我们使用表的一部分, 而不是所有的表, 另一方面可以针对不同的用户制定不同的查询视图.

创建, 更新与删除视图

视图作为一张虚拟表, 帮我们封装了底层与数据表的接口. 相当于是一张表或多张表的数据结果集. 视图的这个特点, 可以帮我们简化复杂的SQL查询, 并且可以重用.

创建视图

创建视图的语法是:

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

view_name表示视图名, column1等表示列名, condition表示查询过滤条件.

以前面的NBA球员数据表为例, 查询比NBA球员平均身高高的球员有哪些, 显示球员ID和身高:

CREATE VIEW player_height AS
SELECT player.player_id, player.height
FROM player
WHERE player.height > (SELECT AVG(height) FROM player); 

select * from player_height; // 视图是虚拟表, 可以直接使用查询语句进行查询

嵌套视图

嵌套视图的概念就是在视图中使用视图, 比如在前视图的基础上, 我想继续查询比视图表中球员平均身高高的球员信息(实际上并没有这些需求, 这里仅仅是举个例子):

CREATE VIEW player_above_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) FROM player_height);

注意的是可以再condition中使用虚拟表, 但是在前面的SELECT中必须是实体表, 不能是虚拟表, 这个需要注意一下.

修改视图

修改视图的语法是:

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

其实看上去和创建视图的语法差不多, 仅仅是将CREATE换成ALTER就可以了. 这里用前面的视图, 现在想多返回一个play_name的字段

ALTER VIEW player_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) FROM player);

删除视图

删除视图就很简单了

DROP VIEW view_name;

上面的例子可以看到视图是对SELECT语句进行了封装, 其实就像Java中的方法一样, 将常用的东西放在一起查询.

视图完成复杂的连接

现在创建视图来查询球员和身高等级的信息:

CREATE VIEW player_height_level AS
SELECT player.player_id, player.player_name, player.height, height_grades.height_level
FROM player, height_grades
WHERE player.height BETWEEN height_grades.height_lowest AND height_grades.height_highest;

现在可以直接在视图中查询身高介于1.90m和2.08m之间的球员信息和等级

SELECT * FROM player_height_level WHERE height BETWEEN 1.90 AND 2.08;

利用视图对数据进行格式化

现在想输出球员姓名和对应的球队, 对应的格式是player_name(team_name), 这个也可以使用视图来解决:

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

不过说实话, 能用视图做的, 基本使用单SQL也可以完成.

视图的用户还有很多, 这里仅仅列了两个使用案例.

视图的优点

1. 安全性：虚拟表是基于底层数据表的，我们在使用视图时，一般不会轻易通过视图对底层数据进行修改，即使是使用单表的视图，也会受到限制，比如计算字段，类型转换等是无法通过视图来对底层数据进行修改的，这也在一定程度上保证了数据表的数据安全性。同时，我们还可以针对不同用户开放不同的数据查询权限，比如人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。
2. 简单清晰：视图是对 SQL 查询的封装，它可以将原本复杂的 SQL 查询简化，在编写好查询之后，我们就可以直接重用它而不必要知道基本的查询细节。同时我们还可以在视图之上再嵌套视图。这样就好比我们在进行模块化编程一样，不仅结构清晰，还提升了代码的复用率。