【数据库SQL server】关系数据库标准语言SQL之视图

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 【数据库SQL server】关系数据库标准语言SQL之视图



🌌关系数据库标准语言SQL之视图

🌍视图

视图的特点

  1. 虚表,是从一个或几个基本表(或视图)导出的表
  2. 只存放视图的定义,不存放视图对应的数据
  3. 基表中的数据发生变化,从视图中查询出的数据也随之改变

🪐1  定义视图

🌕1.1 建立视图

语句格式

      CREATE  VIEW
            <视图名>  [(<列名>  [,<列名>]…)]
      AS  <子查询>
      [WITH  CHECK  OPTION];

说明:

  • WITH CHECK OPTION
    对视图进行UPDATE,INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)
  • 子查询可以是任意的SELECT语句,是否可以含有ORDER BY子句和DISTINCT短语,则决定具体系统的实现。

组成视图的属性列名:全部省略或全部指定

  • 全部省略: 由子查询中SELECT目标列中的诸字段组成
  • 明确指定视图的所有列名:
    某个目标列是聚集函数或列表达式
    多表连接时选出了几个同名列作为视图的字段
    需要在视图中为某个列启用新的更合适的名字

注意:

  1. 关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典,并不执行其中的SELECT语句。
  2. 在对视图查询时,按视图的定义从基本表中将数据查出。
  3. 定义IS_Student视图时加上了WITH CHECK OPTION子句,对该视图进行插入、修改和删除操作时,RDBMS会自动加上Sdept='IS'的条件。
  4. 若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,我们称这类视图为行列子集视图。

【1】建立信息系学生的视图。

       CREATE VIEW IS_Student

       AS

       SELECT Sno,Sname,Sage

       FROM     Student

       WHERE  Sdept= 'IS';

【1】建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。

        CREATE VIEW IS_Student

       AS

       SELECT Sno,Sname,Sage

       FROM  Student

       WHERE  Sdept= 'IS'

       WITH CHECK OPTION;

【1】 建立信息系选修了1号课程的学生的视图(包括学号、姓名、成绩)。

       CREATE VIEW IS_S1(Sno,Sname,Grade)

       AS

       SELECT Student.Sno,Sname,Grade

       FROM  Student,SC

       WHERE  Sdept= 'IS' AND

                      Student.Sno=SC.Sno AND

                      SC.Cno= '1';

【1】 建立信息系选修了1号课程且成绩在90分以上的学生的视图。

       CREATE VIEW IS_S2

       AS

       SELECT Sno,Sname,Grade

       FROM  IS_S1

       WHERE  Grade>=90;

带表达式的视图

【1】定义一个反映学生出生年份的视图。

       CREATE  VIEW BT_S(Sno,Sname,Sbirth)

       AS

       SELECT Sno,Sname,2014-Sage

       FROM  Student;

分组视图

【1】将学生的学号及平均成绩定义为一个视图

          CREAT  VIEW S_G(Sno,Gavg)

            AS  

            SELECT Sno,AVG(Grade)

            FROM  SC

            GROUP BY Sno;

【1】将Student表中所有女生记录定义为一个视图

     CREATE VIEW F_Student(F_Sno,name,sex,age,dept)

     AS

     SELECT  *                        /*没有不指定属性列*/

     FROM  Student

     WHERE Ssex=‘女’;

缺点:修改基表Student的结构后,Student表与F_Student视图  的映象关系被破坏,导致该视图不能正确工作。


🌕1.2 删除视图

语句格式:

       DROP  VIEW  <视图名>[CASCADE];

说明:

  • 该语句从数据字典中删除指定的视图定义
  • 如果该视图上还导出了其他视图,使用CASCADE级联删除语句,把该视图和由它导出的所有视图一起删除
  • 删除基表时,由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除

【1】删除视图BT_S和IS_S1

       DROP VIEW BT_S;    /*成功执行*/

       DROP VIEW IS_S1;    /*拒绝执行*/      

【1】要删除IS_S1,需使用级联删除:

          DROP VIEW IS_S1 CASCADE;            


🪐2  查询视图

用户角度:查询视图与查询基本表相同

关系数据库管理系统实现视图查询的方法:视图消解法

  • 进行有效性检查
  • 转换成等价的对基本表的查询
  • 执行修正后的查询

【1】在信息系学生的视图中找出年龄小于20岁的学生。

      SELECT   Sno,Sage

      FROM      IS_Student

      WHERE   Sage<20;

视图消解转换后的查询语句为:

SELECT  Sno,Sage      

FROM  Student

WHERE  Sdept= 'IS'  AND  Sage<20;

【1】查询选修了1号课程的信息系学生

SELECT  IS_Student.Sno,Sname

FROM     IS_Student,SC

WHERE  IS_Student.Sno =SC.Sno AND SC.Cno= '1';

【1】在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

SELECT *

FROM   S_G

WHERE  Gavg>=90;

      S_G视图的子查询定义:

           CREATE VIEW S_G (Sno,Gavg)

           AS

SELECT  Sno,AVG(Grade)

FROM  SC

GROUP BY Sno;

错误:

SELECT Sno,AVG(Grade)

FROM     SC

WHERE  AVG(Grade)>=90

GROUP BY Sno;

正确:

SELECT  Sno,AVG(Grade)

FROM  SC

GROUP BY Sno

HAVING AVG(Grade)>=90;

【1】也可以用如下SQL语句完成

   SELECT *

   FROM  (SELECT Sno,AVG(Grade)

              FROM  SC

               GROUP BY Sno) AS S_G(Sno,Gavg)

   WHERE Gavg>=90;


🪐3  更新视图

【1】将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。

UPDATE  IS_Student

SET  Sname= '刘辰'

WHERE  Sno= ' 201215122 ';

转换后的语句:

UPDATE  Student

SET Sname= '刘辰'

WHERE Sno= ' 201215122 ' AND Sdept= 'IS';

【1】向信息系学生视图IS_S中插入一个新的学生记录,其中学号为”201215129”,姓名为”赵新”,年龄为20岁

INSERT

INTO IS_Student

VALUES(‘201215129’,’赵新’,20);

转换为对基本表的更新:

INSERT

INTO   Student(Sno,Sname,Sage,Sdept)

VALUES(‘200215129 ','赵新',20,'IS' );

【1】删除信息系学生视图IS_Student中学号为”201215129”的记录

DELETE

FROM IS_Student

WHERE Sno= ' 201215129 ';

转换为对基本表的更新:

DELETE

FROM Student

WHERE Sno= ' 201215129 ' AND Sdept= 'IS';

更新视图的限制:一些视图是不可更新的,因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

【1】定义的视图S_G为不可更新视图。

将学生的学号及平均成绩定义为一个视图

          CREAT  VIEW S_G(Sno,Gavg)

            AS  

            SELECT Sno,AVG(Grade)

            FROM  SC

            GROUP BY Sno;

UPDATE  S_G

SET          Gavg=90

WHERE  Sno= '201215121';

这个对视图的更新无法转换成对基本表SC的更新

DB2对视图更新的限制:

  1. 若视图是由两个以上基本表导出的,则此视图不允许更新。
  2. 若视图的字段来自字段表达式或常数,则不允许对此视图执行INSERT和UPDATE操作,但允许执行DELETE操作。
  3. 若视图的字段来自集函数,则此视图不允许更新。
  4. 若视图定义中含有GROUP BY子句,则此视图不允许更新。
  5. 若视图定义中含有DISTINCT短语,则此视图不允许更新。
  6. 若视图定义中有嵌套查询,并且内层查询的FROM子句中涉及的表也是导出该视图的基本表,则此视图不允许更新。
  7. 一个不允许更新的视图上定义的视图也不允许更新

【1】将SC中成绩在平均成绩之上的元组定义成一个视图

     CREATE VIEW GOOD_SC

     AS

     SELECT  Sno,Cno,Grade

     FROM     SC

     WHERE Grade >

                             (SELECT AVG(Grade)

                               FROM     SC);


🪐4  视图的作用

  1. 视图能够简化用户的操作
  2. 视图使用户能以多种角度看待同一数据
  3. 视图对重构数据库提供了一定程度的逻辑独立性
  4. 视图能够对机密数据提供安全保护
  5. 适当的利用视图可以更清晰的表达查询

📝总结

数据库SQL Server领域就像一片未被勘探的信息大海,引领你勇敢踏入数据科学的神秘领域。这是一场独特的学习冒险,从基本概念到算法实现,逐步揭示更深层次的模式分析、匹配算法和智能模式识别的奥秘。

目录
相关文章
|
22天前
|
SQL 存储 数据库
sql事务、视图和索引
sql事务、视图和索引
14 0
|
30天前
|
SQL 数据库 数据安全/隐私保护
Sql Server数据库Sa密码如何修改
Sql Server数据库Sa密码如何修改
|
23天前
|
SQL 数据库
sql server中创建数据库和表的语法
sql server中创建数据库和表的语法
17 1
|
2天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
35 6
|
2天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
35 10
|
2天前
|
SQL 存储 数据挖掘
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
服务器数据恢复环境: 一台安装windows server操作系统的服务器。一组由8块硬盘组建的RAID5,划分LUN供这台服务器使用。 在windows服务器内装有SqlServer数据库。存储空间LUN划分了两个逻辑分区。 服务器故障&初检: 由于未知原因,Sql Server数据库文件丢失,丢失数据涉及到3个库,表的数量有3000左右。数据库文件丢失原因还没有查清楚,也不能确定数据存储位置。 数据库文件丢失后服务器仍处于开机状态,所幸没有大量数据写入。 将raid5中所有磁盘编号后取出,经过硬件工程师检测,没有发现明显的硬件故障。以只读方式将所有磁盘进行扇区级的全盘镜像,镜像完成后将所
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
|
23天前
|
SQL Oracle 关系型数据库
干货!sqlserver数据库所有知识点总结整理,含代码(挺全的)
干货!sqlserver数据库所有知识点总结整理,含代码(挺全的)
11 0
|
24天前
|
前端开发 Java 数据库
HTML、sqlserver数据库和java前七章节的复习(一)
HTML、sqlserver数据库和java前七章节的复习(一)
8 0
|
30天前
|
SQL 存储 数据库
数据安全无忧,SQL Server 2014数据库定时备份解密
数据安全无忧,SQL Server 2014数据库定时备份解密
|
30天前
|
数据库
SQLSERVER 2014 删除数据库定时备份任务提示失败DELETE 语句与 REFERENCE 约束“FK_subplan_job_id“冲突
SQLSERVER 2014 删除数据库定时备份任务提示失败DELETE 语句与 REFERENCE 约束“FK_subplan_job_id“冲突