第五讲 利用视图进行查询优化.

Slides:



Advertisements
Similar presentations
第二章 简单的 SQL 语句. 本章要点  创建一个简单的表  SQL 语句介绍  最简单的查询语句 select  带有限制条件的查询  查询结果的排序显示.
Advertisements

2010 年 6 月课件制作人:王亚楠 1 模块 2 项目开发概论 教学课件 年 6 月课件制作人:王亚楠 2 目录 目标 了解:数据库技术的基本概念与结构 理解:数据模型的分类与结构组成 掌握:关系数据库及 SQL 的基本理论 知识 掌握:数据库设计的方法与步骤 内容 2.1 数据库技术基础.
Data type P64 ‘’ 转义字符 P67 P68 EXE,选出某个教师的学生中最新的一 个,要姓名, ID (,LIMIT ) EXISTS,NOT EXISTS P409 Q,EXISTS 和 in 的区别( 1000 ,查询结果)
第 7 章 数据库 1. Overview  数据库概述  数据库管理系统  数据库的体系结构和数据库模型  SQL 语言  数据库技术  构建数据库系统 2.
目 录 第 1 章 数据库技术基础 第 2 章 SQL Server基础 第 3 章 数据库管理 第 4 章 查询和视图
SQL的简单查询.
第5章 关系数据库标准语言SQL 主讲:张丽芳.
十一 ASP对数据库的访问.
第2章 SQL语言初步 2.1 SQL的基本概念 2.2 基本表、索引的创建、删除和修改操作 2.3 SQL的查询语句——SELECT
第2讲 Transact-SQL语言.
第5章 索引及视图操作 数据库原理应用与实践 SQL SERver2014(第2版) 主编 贾铁军 科学出版社 编著 陈国秦 万程 邢一鸣
第四章 数据库对象.
第3章 SQL语言初步 2017/3/14.
第14章 預存程序 14-1 預存程序的基礎 14-2 建立與執行預存程序 14-3 預存程序的參數傳遞 14-4 預存程序的傳回值
第4章 关系数据库标准语言SQL 4.1 SQL语言概述 4.2 SQL数据查询功能 4.3 SQL数据操作功能 4.4 SQL数据定义功能.
資料庫 (Database) SQL Server 2008實作
第3章 SQL的基础知识 数据库管理及应用 3.1 SQL简介 3.2 SQL的数据模型 3.3 标识符 3.4 使用SQL语句管理数据库
数据库概述 简而言之,数据库(DataBase)就是一个存储数据的仓库。为了方便数据的存储和管理,它将数据按照特定的规律存储在磁盘上。通过数据库管理系统,可以有效的组织和管理存储在数据库中的数据。如今,已经存在了Oracle、SQL Server、MySQL等诸多优秀的数据库。在这一章中将讲解的内容包括。
数据库原理及设计 --作业.
第一讲 数据查询优化.
Chap 13 視界與資料庫程式設計.
第三章 管理信息系统的技术基础 主要内容: 数据处理 数据组织 数据库技术 4. 计算机网络.
计算机应用基础 上海大学计算中心.
第3章 数据查询与SQL命令.
資料庫管理 資管二 賴柏融.
数据库应用与程序设计.
建立/修改/刪除資料庫 資料庫組成檔案 建立資料庫: 資料(庫)檔案 交易記錄檔 使用Enterprise Manager工具 自助法:
第六章 學習SQL語言.
第 8 章 資料的 新增、修改與刪除.
課程名稱:資料庫系統 授課老師:李春雄 博士
数据库技术 第三章 关系数据库标准语言SQL 中国科学技术大学网络学院 阚卫华.
2、掌握SQL中各种查询方法和数据更新方法 3、掌握SQL中视图的定义方法和用法 4、掌握SQL的授权机制
Chap 10 SQL定義、操作與控制指令.
第 11 章 建立檢視表.
第 7 章 建立資料表與 資料庫圖表.
資料庫安全 (Database Security)
彰化縣政府補助辦理網頁設計資料庫應用班 ASP與資料庫介紹 建國技術學院資管系 饒瑞佶.
Transact-SQL 語言設計教學.
課程名稱:資料庫系統 授課老師:李春雄 博士
第 16 章 觸發程序.
SQL Server 2000 数据库入门.
5 数据库管理与保护 数据库运行的最小逻辑工作单位是事务,所有对数据库的操作,都以事务作为一个整体来执行或撤销。
第 6 章 使用者權限.
SQL SERVER 一些经典语句 1.
教 师:曾晓东 电 话: 数据库技术 教 师:曾晓东 电 话:
医院职工公费医疗系统.
第四讲 数据库对象(视图、序列、同义词、索引)
第十七章 資料庫SQL 17-1 SELECT 17-2 INSERT 17-3 UPDATE 17-4 DELETE.
iRepor报表设计基础 IReport安装 普通实体报表 数据结果集报表 工作流主从报表 饼状图报表 柱状图,曲线图报表 条形码报表
SQL Injection (資料隱碼) 學生:a 吳倩瑜 指導教授:梁明章.
数据库技术.
数据库应用技术 SQL Server 2005.
第20章 MySQL数据库.
数据库技术与应用.
ORACLE 第九讲 触发器.
4.2 视图 (1) 视图是一个虚拟表,其内容来自对表查询的基础上。
CS, ZJU 4/18/2019 Chapter 7 数据库.
3.2 Mysql 命令行 1 查看数据库 SHOW DATABASES; 2 创建一个数据库test1 CREATE DATABASE test1; 3 选择你所创建的数据库 USE test1; (按回车键出现Database changed 时说明操作成功!) 4 查看现在的数据库中存在什么表.
6 SQL語言與資料庫建置 6-1 SQL語言的基礎 6-2 SQL Server的資料庫結構 6-3 建立使用者資料庫
3. SQL语言的应用 3.1 SQL历史和优点 3.2 数据查询 3.3 数据操纵.
查询与视图 蔡海洋.
第14章 SQL数据查询与操纵 内容提要 本章知识点
SQL查询语句 蔡海洋.
第三章 SQL Server数据管理.
第 7 章 建立資料表與資料庫圖表.
第9章 存储过程的创建和使用 9.1 存储过程简介 9.2 创建存储过程 9.3 修改存储过程 9.4 删除存储过程 9.5 执行存储过程.
第9章 视 图 教学提示:用户在查询数据库中的数据时,除了直接查看数据库中的表中的数据以外,还可以通过视图来查看表中的数据,也就是说,视图为用户提供了另外一种查看表中的数据的方式。 视图是一种虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自于定义视图的查询所引用的表,并且在引用视图时动态生成。
11 檢視表的建立 11-1 檢視表的基礎 11-2 建立檢視表 11-3 修改與刪除檢視表 11-4 編輯檢視表的內容.
第4章 数据查询.
数据库应用技术 天津电大 翟迎建.
Presentation transcript:

第五讲 利用视图进行查询优化

第五讲 利用视图进行查询优化

学习情境引入 在前几讲中,我们针对商品采购管理系统相关查询功能进行了讲解。 为了简化数据查询操作和保护数据安全,数据库提供了视图工具。 数据库中视图是一个虚拟表,其内容由查询定义。 同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在;行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。

基本表1 基本表2 视图 视图结构示意图

从用户角度来看,一个视图是从一个特定的角度 来查看数据库中的数据。 从数据库系统内部来看,一个视图是由SELECT语句组成的查询定义的虚拟表。 从数据库系统外部来看,视图就如同一张表一样,对表能够进行的一般操作都可以应用于视图,例如查询,插入,修改,删除操作等。

视图是存储在数据库中的查询的SQL 语句,它主要出于两种原因: 安全原因: 视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等; 另一原因:可使复杂的查询易于理解和使用。

视图的作用如下:  * 简单性。看到的就是需要的。视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。  * 安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上,但不能授权到数据库特定行和特定的列上。  * 逻辑数据独立性。视图可帮助用户屏蔽真实表结构变化带来的影响。

视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。视图实际上就是给查询语句指定一个名字,将查询语句定义为一个独立的对象保存。

视图是个虚表,是从一个或者多个表或视图中导出的表,其结构和数据是建立在对表的查询基础上的。 它表示一张表的部分数据或多张表的综合数据,其结构和数据是建立在对表的查询基础上 视图中并不存放数据,而是存放在视图所引用的原始表(基表)中同一张原始表,根据不同用户的不同需求,可以创建不同的视图。

视图(View):是由SELECT语句定义的用于集中、简化和定制显示数据库中数据的数据库对象。 1. 视图与表的关系: 视图(除索引视图)并不存储任何数据 2. 视图的数据源 一个基表(视图)中行的子集——水平视图 一个基表(视图)中列的子集——投影视图 多个基表(视图)连接操作的结果集——连接视图 基表(视图)进行运算汇总的结果集——计算视图

视图举例 商品信息表 商品类别表 商品单位表 视图的创建 教员讲笑话,活跃气氛: 大家都知道,我们国家现在“神六”上天了。从美国的月球登月开始,人类上天不再是神话。 听说,在美国,你只要出几十万美元,您就可以上一次月球进行太空旅行,所以,我们相信: 在不久的将来,上天旅行将走进我们百姓的生活,那是一件多么新鲜神奇的事情啊。 好多美国人上天后,他们用望远镜观看我们的地球,结果看到的地球形状各不相同。 有的说象绿色的橘子  ,有的说象白色的大鸭梨,有的说象蓝色的大西瓜。 提问:为什么呢? 大多学员肯定会说:那是因为从不同的角度(视角),看到的不同形状而也。 鼓励学员:大家真聪明,确实如此。 既然同一个地球,从不同视角看到不同的结果。 从不同的视角,将看到不同的“图形”,这就是我们马上讲到的视图。 我们的学员信息也是如此:对于学员的信息和成绩 而教员比较关心学员成绩以及是否参加考试,包括姓名、学号、笔试、机试、是否通过等。 班主任比较关心学生档案,包括姓名、学号、性别和年龄, 我们来看看幻灯片所示的例子。

演示使用T-SQL管理平台创建视图 【视图dbo-View1】标签页 显示条件窗格 显示SQL窗格 显示结果窗格 显示关系图窗格 参见学生用书:第五章5.5.2节 如何创建视图 部分。

视图的创建和应用 在SQL Server 2005中一般用以下两种方法创建视图: (1)用SQL SERVER管理平台创建视图; (2)用Transact-SQL语句中的CREATE VIEW命令创建视图 创建视图时应该注意以下情况: (1)只能在当前数据库中创建视图,在视图中最多只能引用1024列,视图中记录的数目限制只由其基表中的记录数决定。 (2)如果视图引用的基表或者视图被删除,则该视图不能再被使用,直到创建新的基表或者视图。 (3)如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同,则必须为列定义名称。

利用SQL SERVER管理平台创建视图 视图的创建和应用 (1)在SQL SERVER管理平台中,展开指定的服务器,打开要创建视图的数据库文件夹,选择指定的数据库,右击该数据库图标,从弹出的快捷菜单中依次选择“新建(New)→视图”选项,如图示。接着就出现添加表、视图、函数对话框。

创建视图的基本语法如下: CREATE VIEW <视图名> [(<视图列名表>)] [WITH ENCRYPTION] AS Select_语句 其中:WITH ENCRYPTION子句对视图进行加密。 此命令必须是GO指令后的第一条!

使用T-SQL语句创建视图 CREATE VIEW 视图名 AS <select语句> 任务实施 在数据库中,根据商品信息表(goods)创建一个只包含商品类别号为“P001”和“P002”视图,视图名为View_GoodsInfo_GoodsClass。视图包含的列为商品编号、商品类别号、商品名称和价格。

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'View_GoodsInfo_GoodsClass ') DROP VIEW View_GoodsInfo_GoodsClass GO CREATE VIEW View_GoodsInfo_GoodsClass AS SELECT goods_id 商品编号, classification_id 商品类别号, goods_name 商品名称,unit_price 价格 FROM goods WHERE classification_id =‘P001' OR classification_id =‘P002' 检测视图是否存在 删除视图 创建命令 创 建 命 令 AS关键字不能少 视图名 创建视图 Select语句 告诉学员: 说明:从一个或者多个表或视图中导出的虚拟表,其结构和数据是建立在对表的查询基础上的。 理论上它可以像普通的物理表一样使用,例如增、删、改、查等,修改视图中的数据实际上是修改 原始数据表。因为修改视图有许多限制,所以在实际开发中一般视图仅做查询使用 使用视图

创建有限制的视图操作 任务实施 在数据库中,根据商品信息表(goods)创建一个只包含商品类别号为“P001”和“P002”视图,视图名为View_GoodsInfo_GoodsClass2。视图包含的列为商品编号、商品类别号、商品名称和价格,列名为中文别名。 并要求通过该视图进行的更新操作只涉及商品类别号为“SPLB01”和“SPLB03”的商品信息。 CREATE VIEW View_GoodsInfo_GoodsClass2 AS SELECT goods_id 商品编号, classification_id 商品类别号, goods_name 商品名称,unit_price 价格 FROM goods WHERE classification_id =‘P001' OR classification_id =‘P002' WITH CHECK OPTION

基于多个基本表的视图 CREATE VIEW View_3 AS 任务实施 在employee表和department表中创建一视图名为View_3,其视图列名列表中含有的列为:部门号、部门名称、部门人员名称。 CREATE VIEW View_3 AS SELECT employee.department_id,department_name,employee_name FROM employee,department WHERE employee.department_id=department.department_id SELECT * FROM View_3

建立统计分组视图 CREATE VIEW View_PurchaseCount AS 任务实施 对于数据库的的商品表(goods),创建一个查询每种商品的进货数量的视图,视图名为View_ PurchaseCount。 CREATE VIEW View_PurchaseCount AS SELECT classification_id 商品编号,SUM(stock_quantity) 进货数量 FROM goods GROUP BY classification_id 必须给列SUM()指定列名

视图的创建和应用 修改视图 (1)在SQL SERVER管理平台中,右击要修改的视图,从弹出的快捷菜单中选择“设计视图”选项,出现视图修改对话框。该对话框与创建视图时的对话框相同,可以按照创建视图的方法修改视图。 (2)使用ALTER VIEW语句修改视图,但首先必须拥有使用视图的权限,然后才能使用ALTER VIEW语句,该语句的语法形式如下: ALTER VIEW view_name [(column[,...n])] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION ]

视图的创建和应用 任务实施 对View_GoodsInfo_GoodsClass 视图 进行修改。 alter view View_GoodsInfo_GoodsClass as SELECT GoodsID 商品编号,ClassID 商品类别号 FROM GoodsInfo WHERE ClassID='SPLB01' OR ClassID='SPLB03‘

视图的创建和应用 重命名视图 (1)在SQL SERVER管理平台中,选择要修改名称的视图,并右击该视图,从弹出的快捷菜单中选择“重命名”选项。或者在视图上再次单击,也可以修改视图的名称。接着该视图的名称变成可输入状态,可以直接输入新的视图名称。  (2)使用系统存储过程sp_rename来修改视图的名称,该过程的语法形式如下: exec sp_rename old_name,new_name

对View_GoodsInfo_GoodsClass 视图 进行重命名。 视图的创建和应用 对View_GoodsInfo_GoodsClass 视图 进行重命名。 exec sp_rename View_GoodsInfo_GoodsClass , View3‘ 注意: SP_rename 后 紧跟 旧名称,后面是新名称 任务实施

对于不再使用的视图,可以使用SQL SERVER管理平台或者Transact-SQL语句中的DROP VIEW命令删除它。 删除视图 对于不再使用的视图,可以使用SQL SERVER管理平台或者Transact-SQL语句中的DROP VIEW命令删除它。 使用Transact-SQL语句DROP VIEW删除视图,其语法形式如下: DROP VIEW {view_name} [,…n] 可以使用该命令同时删除多个视图,只需在要删除的各视图名称之间用逗号隔开即可。

使用视图操作数据表 除了在SELECT中使用视图作为数据源进行查询以外,还可以通过视图对数据表的数据进行添加、修改和删除的操作。 使用视图对数据表的记录进行操作时,所创建的视图必须满足的要求: 1) 修改视图中的数据时,不能同时修改两个或者多个基本表,当对基于两个或多个表创建的视图进行修改时,每次的修改只能影响一张基本表。 2) 视图的字段中不能包含计算列,计算列是不能更新的。

使用视图操作数据表 3) 如果在创建视图时指定了WITH CHECK OPTION选项,那么使用视图修改数据库时,必须保证修改后的数据满足视图定义的要求。 4) 如果在视图定义中使用了GROUP BY、UNION、DISTINCT或TOP子句,则视图不允许更新。 5) 如果在视图定义中有嵌套查询,并且内层查询的FROM子句涉及的表也是导出该视图的基本表,则视图不允许更新。

INSERT INTO department_view VALUES(‘G004’,’总经理室’) 通过视图插入数据 1.插入(INSERT) 条件:视图来自单表,且非空项均需在视图中。 【案例】创建视图department_view CREATE VIEW department_view AS SELECT department_id, department_name FROM department 【案例】 向视图department_view中插入一个部门(‘ G004 ’,‘总经理室’)。 INSERT INTO department_view VALUES(‘G004’,’总经理室’)

通过视图更新数据 2. 修改(UPDATE)和删除(DELETE) update department_view set department_name=‘董事长室' where department_id='G004'Go Delete from department_view where department_id='G004'

视图可以屏蔽数据的复杂性,简化用户对数据库的操作,还可以使用视图重新组织数据。 总 结    视图可以屏蔽数据的复杂性,简化用户对数据库的操作,还可以使用视图重新组织数据。    视图可以让不同的用户以不同的方式看到不同或者相同的数据集。    视图可以定制不同用户对数据的访问权限。    因此,视图是在数据库系统开始时进行查询优化的常用方法。