SQLserver数据库之视图及其他查询

sql数据库视图及其他查询

在看该篇教程时，大家可以去回顾一下上篇sql数据库教程：SQLserver数据库多表连接查询之外连接

该教程所用到的数据库脚本如下：

数据库插入数据表的脚本

1.sql数据库- 视图的概念

• 基表

基表是真实存在于数据库中的表对象，是视图的数据来源表。

• 视图

视图是一个虚拟表，是根据需要从一个或多个基表中查询的结果集。视图只是保存在数据库中的 SELECT 查询，数据来源于基表，可像表一样进行查询、添加、删除和修改数据。

• 视图的用途

（1）为用户集中数据，简化用户的数据查询和处理。

（2）屏蔽数据库的复杂性。

（3）简化用户权限的管理。

（4）便于数据共享。

（5）可以重新组织数据以便输出到其他应用程序中。

• 视图使用说明

（1）只有在当前数据库中才能创建视图。

（2）视图的命名必须遵循标识符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。

（3）不能把规则、默认值或触发器与视图相关联。

（4）不能在视图上建立任何索引，包括全文索引。

2.sql数据库-视图操作

• 创建视图

基本语法：

Create View 视图名

As

select查询语句

注意：

（1）只有在当前数据库中才能创建视图。

（2）视图的命名必须遵循标识符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。

（3）不能把规则、默认值或触发器与视图相关联。

（4）不能在视图上建立任何索引，包括全文索引。

【例1】创建查询学生姓名、身份证号、性别的视图（v_stuInfo）。

 CREATE VIEW v_stuInfo
 AS
 SELECT stuName,stuIdentity,stuSex FROM stuInfo

【例2】创建查询学生姓名、学校名称、区县名称的视图（v_stu_area）。

 CREATE VIEW v_stu_area
 AS
 SELECT stuName,schName,areaName
 From schoolInfo,stuInfo,areaInfo
 INNER JOIN stuInfo ON schoolInfo.schId=stuInfo.stuId
 INNER JOIN areaInfo ON schoolInfo.areaId=areaInfo.areaId

在执行上面的查询代码的时候 应该会出现下图这样的情况

sql数据库报错

我们只需要 将上面代码中的第3行 中的 schName与areaInfo删除然后再点击执行即可！

• 修改视图

基本语法：

Alter View 视图名

As

select查询语句

注意：修改视图即是使用新的查询语句替换原有的视图查询语句。

【例3】修改视图v_stuInfo为查询学生编号、姓名、身份证号码、性别。

 Alter view v_stuInfo
 AS
 SELECT AutoId, stuName,stuIdentity,stuSex FROM stuInfo

• 删除视图

基本语法：

Drop View 视图名

注意：删除视图不影响基表数据。

【例4】删除视图v_stuInfo。

 Drop view v_stuInfo

• 从视图查询数据

视图一经建立，可像一个普通表一样从视图查询数据。

【例5】从视图v_stu_area查询船山区的学生数据。

  Select * from v_stu_area
  Where areaName=‘船山区’

• 查询视图依赖的基表

sp_depends 视图名

如：查看v_stu_area依赖的基表：

 sp_depends v_stu_area

3.sql数据库-视图综合案例

【例6】创建学校人数视图V_SchCounts，包括学校号、学校名、性别和人数；再通过该视图查询女生人数最多的学校名。

 CREATE VIEW V_SchCounts
 AS
 SELECT sch.schId,schName,stuSex,count(*) as counts
 FROM schoolInfo sch left join stuInfo stu
 ON sch.schID=stu.schID
 GROUP BY sch.schID,schName
 GO

 Select schName from V_schCounts
 where counts=(select max(counts)
 from V_SchCounts
 where stuSex='女')

除了上面一些师徒操作我们还可以进行下面的这些操作，这些操作叫做排名次！

sql数据库排名次

RANK() over(order by ….)：有同名次，有断号

DENSE_RANK() over(order by ….)：有同名次，无断号

【例7】查询stuScores表信息，给每个记录按照总分降序排名次。

 select *,名次=Rank() over (order by total desc)
 from stuScores

sql数据库排序号

ROW_NUMBER ( )over(order by ….)

【例8】查询stuScores表信息，给每条记录按照总分降序排序号。

select *,序号=Row_Number() over (order by total desc)
from stuScores

sql数据库排页号

Ntile(…) over(order by ….)

【例9】查询stuInfo表信息，给每条记录按照autoID降序排序号。

select *,页号=NTILE(100) OVER(ORDER BY autoID desc)
from stuInfo

志在指尖-专注于IT技术以及IT技术教学