SQL Server 2012数据库原理与应用第六讲 视图.pptVIP

6-* Copyright ? SWFU-CISC, 2016. All rights reserved. SQL Server 2012 数据库原理及应用 Made by Ning Lu Database Principle Application 6 Copyright ? SWFU-CISC, 2016. All rights reserved. SQL Server 2012 数据库原理及应用 Made by Ning Lu Database Principle Application Schedule: Timing Topic 40 minutes Lecture 25 minutes Practice 65 minutes Total Lesson Aim To extract data from the database, you need to use the structured query language (SQL) SELECT statement. You may need to restrict the columns that are displayed. This lesson describes all the SQL statements needed to perform these actions.You may want to create SELECT statements that can be used more than once. This lesson also covers the iSQL*Plus environment where you execute SQL statements. Note: iSQL*Plus is new in the Oracle9i product. It is a browser environment where you execute SQL commands. In prior releases of Oracle, SQL*Plus was the default environment where you executed SQL commands. SQL*Plus is still available and is described in Appendix C. 第六讲 视图 教学内容 6.1 视图介绍 6.1.1 视图概念 6.1.2 视图优点 6.1.3 视图注意事项 6.2 使用SQL管理视图 6.2.1 创建视图 6.2.2 使用视图 6.3 使用SQL Server Management Studio建视图 6.1.1 视图概念 视图是保存在数据库中的选择查询 视图相当于从一个或多个数据表中派生出的虚拟表 对视图的操作与对表的操作一样 视图可进行查询、修改、删除和更新操作 视图一般用于查询操作 6.1.2 视图优点 为用户集中数据，简化用户的数据查询和处理 屏蔽数据库的复杂性 简化用户权限的管理 便于数据共享 可以重新组织数据以便输出到其他应用程序中 6.1.3 视图注意事项 只有在当前数据库中才能创建视图 视图命名符合标识符命名规范，视图名必须唯一 不能把规则、默认值或触发器与视图相关联 不能在视图上建立任何索引，包括全文索引 6.2 使用SQL创建视图 语法 CREATE VIEW [ database_name .] [ owner .] view_name [ ( column [ ,...n ] ) ] [ WITH view_attribute [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] view_attribute ::= { ENCRYPTION | SCHEMABINDING | VIEW_METADATA } 创建视图示例 创建学生信息视图 Create View V_StudInfo AS Select StudNo,StudName,StudSex,ClassID From StudInfo 使用视图 查询视图记录 Select * From V_StudInfo Select * From ClassInfo C,V_StudInfo V Where C.ClassID=V.ClassID