实现SQL Server数据库中的视图和查询

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,实用教程,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,实用教程,*,1 视图基本概念,一、视图基本概念,1基本表,基本表是独立存在的表。在SQL Server中创建了一个基本表，那么可以在表中添加记录，这些记录存放在硬盘上。“学生表”、“课程表”、“成绩表”等等，都是基本表。,1,2视图,视图看上去同表似乎一模一样，具有一组命名的字段和数据项，但它其实是一个虚拟的表，在物理上并不实际存在。视图是由查询数据库表产生的，它限制了用户能看到和修改的数据。视图一旦定义后，就可以和基本表一样被查询、被删除，也可以在一个视图基础上再定义新的视图。,视图兼有表和查询的特点：与查询相类似的是，视图可以用来从一个或多个相关联的表或视图中提取有用信息；与表相类似的是，视图可以用来更新其中的信息，并将更新结果永久保存在磁盘上。我们可以用视图使数据暂时从数据库中分离成为游离数据，以便在主系统之外收集和修改数据。,2,二、视图的优缺点,当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化，同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。,视图有很多优点，主要表现在：,1,、,视点集中,视图集中即是使用户只关心它感兴趣的某些特定数据和他们所负责的特定任务。这样通过只允许用户看到视图中所定义的数据而不是视图引用表中的数据而提高了数据的安全性。,3,2,、简化操作,视图大大简化了用户对数据的操作。因为在定义视图时，若视图本身就是一个复杂查询的结果集，这样在每一次执行相同的查询时，不必重新写这些复杂的查询语句,只要一条简单的查询视图语句即可。可见视图向用户隐藏了表与表之间的复杂的连接操作。,3,、定制数据,视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。因此，当有许多不同水平的用户共用同一数据库时，这显得极为重要,。,4,4,、合并分割数据,在有些情况下，由于表中数据量太大，故在表的设计时常将表进行水平分割或垂直分割，但表的结构的变化却对应用程序产生不良的影响。如果使用视图就可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。,5,、,组合分区数据,用户可以把来自不同表的两个或多个查询结果组合成单一的结果集。这在用户看来是一个单独的表，称为分区视图。,5,6,、安全性,视图可以作为一种安全机制。通过视图用户只能查看和修改他们所能看到的数据。其它数据库或表既不可见也不可以访问。如果某一用户想要访问视图的结果集，必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。,7、视图的缺点,视图的缺点主要表现在其对数据修改的限制上。,6,三、使用视图的场合,1）表的行或列的子集。,2）两个或多个表的联合。,3）两个或多个表的连接。,4）其他视图的子集。,5）视图与表的组合。,7,2 创建视图,创建视图时应该注意以下情况：,只能在当前数据库中创建视图。,视图中最多只能引用,1024,列。,如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。,如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。,8,当视图所引用不同基表的列中有相同列名时或者希望给视图中的列指定新的列名时则需要重新指定列的别名。,不能在视图上创建索引，不能在规则、缺省、触发器的定义中引用视图。,当通过视图查询数据时，,SQL Server,要检查以确保语句中涉及的所有数据库对象存在，而且数据修改语句不能违反数据完整性规则。,视图的名称必须遵循标识符的规则，且对每个用户必须是唯一的。此外，该名称不得与该用户拥有的任何表的名称相同。,9,注意：,一个视图可以基于一个或若干个基表，也可以基于一个或若干个视图，同时也可以基于基表和视图的混合体。,10,使用,Transact-SQL,语言创建视图,创建视图的语法格式如下：,CREATE VIEW .视图名(列名,.n),WITH ENCRYPTION|SCHEMABINDING|VIEW_METADATA,AS,SELECT查询语句,WITH CHECK OPTION,11,参数说明：,(1),视图名称必须符合标识符规则。可以选择是否指定视图所有者名称。,(2),CREATE VIEW,子句中的列名是视图中显示的列名。只有在下列情况下，才必须命名,CREATE VIEW,子句中的列名：当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称。当然也可以在,SELECT,语句中指派列名。,注意,：,如果未指定列名，则视图列将获得与 SELECT 语句中的列相同的名称。,12,（3）定义视图的语句是一个 SELECT查询语句。该语句可以使用多个表或其它视图。若要从创建视图的 SELECT 子句所引用的对象中选择，必须具有适当的权限。视图不必是具体某个表的行和列的简单子集。可以用具有任意复杂性的 SELECT 子句，使用多个表或其它视图来创建视图。,（4）在索引视图定义中，SELECT 语句必须是单个表的语句或带有可选聚合的多表 JOIN。,13,（5）,在,CREATE VIEW,语句中，对于,SELECT,查询语句有如下限制：,创建视图的用户必须对该视图所参照或引用的表或视图具有适当的权限。,在查询语句中，不能包含,ORDER BY(,如果要包含的话,SELECT,子句中要用,TOP n percent),、,COMPUTE,或,COMPUTE BY,关键字。也不能包含,INTO,关键字。,不能在临时表中定义视图(不能引用临时表)。,14,（,6,）,WITH CHECK OPTION,：强制视图上执行的所有数据修改语句都必须符合由,SELECT,查询语句设置的准则。通过视图修改数据行时，,WITH CHECK OPTION,可确保提交修改后，仍可通过视图看到修改的数据。,（,7,）,WITH ENCRYPTION,：表示,SQL Server,加密包含,CREATE VIEW,语句文本的系统表列。使用,WITH ENCRYPTION,可防止将视图作为,SQL Server,复制的一部分发布。,15,（,8,）,SCHEMABINDING,：将视图绑定到架构上。指定,SCHEMABINDING,时，,SELECT,查询语句必须包含所引用的表、视图或用户定义函数的两部分名称,(owner.object),。不能除去参与用架构绑定子句创建的视图中的表或视图，除非该视图已被除去或更改，不再具有架构绑定。否则，,SQL Server,会产生错误。另外，如果对参与具有架构绑定的视图的表执行,ALTER TABLE,语句，而这些语句又会影响该架构绑定视图的定义，则这些语句将会失败,。,16,（,9,）,VIEW_METADATA,：指定为引用视图的查询请求浏览模式的元数据时，,SQL Server,将向,DBLIB,、,ODBC,和,OLE DB API,返回有关视图的元数据信息，而不是返回基表或表。浏览模式的元数据是由,SQL Server,向客户端,DB-LIB,、,ODBC,和,OLE DB API,返回的附加元数据，它允许客户端,API,实现可更新的客户端游标。浏览模式的元数据包含有关结果集内的列所属的基表信息。,对于用,VIEW_METADATA,选项创建的视图，当描述结果集中视图内的列时，浏览模式的元数据返回与基表名相对的视图名。当用,VIEW_METADATA,创建视图时，如果该视图具有,INSERT,或,UPDATE INSTEAD OF,触发器，则视图的所有列（,timestamp,除外）都是可更新的。,17,3 管理视图,1、使用系统存储过程查看视图信息,sp_help 表/视图,sp_helptext 表/视图,sp_depends 表/视图,18,2、使用,T-SQL,语句修改视图,ALTER VIEW,语句的语法格式为：,ALTER,VIEW .视图名(列名,.n),WITH,ENCRYPTION|SCHEMABINDING|VIEW_METADATA,AS,SELECT查询语句,WITH CHECK OPTION,参数说明同CREATE VIEW相同。,19,3、,使用,DROP VIEW,删除视图,删除视图的语法格式如下。,DROP VIEW,视图名1，视图名n,使用该语句一次可以删除多个视图。,20,4 通过视图访问数据,使用视图管理表中的数据包括插入、更新和删除三种操作，在操作时要注意以下几点：,修改视图中的数据时，可以对基于两个以上基表或视图的视图进行修改，但是不能同时影响两个或者多个基表，每次修改都只能影响一个基表。,不能修改那些通过计算得到的列，例如年龄和平均分等。,若在创建视图时定义了,WITH CHECK OPTION,选项，那么使用视图修改基表中的数据时，必须保证修改后的数据满足定义视图的限制条件。,21,执行,UPDATE,或,DELETE,命令时，所更新或删除的数据必须包含在视图的结果集中。,如果视图引用多个表时，无法用,DELETE,命令删除数据。,如果视图引用多个表，使用,INSERT,或,UPDATE,语句对视图进行操作时，被插入或更新的列必须属于同一个表。,视图的字段来自集合函数，则此视图不允许修改操作。,若视图定义中含有GROUP BY 子句，则此视图不允许修改操作。,若视图定义中含有DISTINCT短语，则此视图不允许修改操作。,一个不允许修改操作视图上定义的视图，也不允许修改操作。,22,5 SELECT语句,SELECT语句能够从数据库中检索出符合用户需求的数据，并将结果以表格的形式返回，是SQL Server中使用最频繁的语句之一。基本语法格式如下：,SELECT 列名1 ,列名2 ., INTO 新表名 ,FROM 表名1 ,表名2 ., WHERE 条件 , GROUP BY 列名列表 , HAVING 条件 , ORDER BY 列名列表 ASC | DESC ,其中，用 表示可选项。,SELECT语句至少包含两个子句：SELECT和FROM，SELECT子句指定要查询的指定表中的列，FROM子句指定查询的表。,23,SELECT子句,SELECT子句用于指定要返回的列，其完整的语法如下：,SELECT ALLDISTINCT , TOP n PERCENT,列名,AS 别名别名=表达式, ,.n ,24,FROM子句,只要SELECT子句有要查询的列，就必须使用FROM子句指定进行查询的单个或者多个表。此外，SELECT语句要查询的数据源除了表以外还可以是视图，视图相当于一个临时表，其语法格式如下：,FROM 表名|视图名 ,.n ,当有多个数据源时，可以使用逗号,“,”,分隔，但是,最多只能有16个数据源,。数据源也可以像列一样指定别名，该别名只在当前的SELECT语句中起作用，方法为：数据源名 AS 别名，或者数据源名 别名。指定别名的好处在于以较短的名字代替原本见名知意的长名。,25,WHERE子句,WHERE子句指定查询的条件，限制返回的数据行。其语法格式如下：,WHERE 指定条件,WHERE子句用于指定搜索条件，过滤不符合查询条件的数据记录，使用比较灵活且复杂。可以使用的条件包括比较运算、逻辑运算、范围、模糊匹配以及未知值等。,26,一、使用通配符*,select * from 表名/视图名,返回from中指定的表中的所有列。,二、使用指定的列,select 列名1,列名2,列名n,from 表名/视图名,返回from中指定的表中的指定列。,27,三、使用TOP关键字,select top n percent 列名,四、使用DISTINCT关键字,使用DISTINCT关键字时，,1、无论遇到多少个空值，只返回一个。,2、表达式只包含一个列名，且不能包含算术表达式。,3、不能包含text、ntext和image类型字段。,4、是sum、avg和count的可选关键字。,28,五、使用计算列,六、使用列的别名,1、select 列名1,别名1,2、select 别名1=列名1,3、 select 列名1 as 别名1,七、使用SELECT语句进行无数据源检索,29,6 使用WHERE子句,select 列名 from 表名/视图名,where ,说明：,1、中的条件数目无限制。,2、where子句必须紧跟在from子句的后面。,30,一、使用比较运算符,用于比较两个表达式是否满足条件。,用于除text、ntext和image类型外的所有表达式。其结果返回值是布尔类型，即true或false。,二、使用逻辑运算符,三种逻辑运算符：or、and和not。,31,三、使用BETWEEN关键字,在where子句中使用BETWEEN关键字来限制查询数据的 范围。,BETWEEN关键字的效果可以用含有=的表达式来代替；NOT BETWEEN关键字的效果可以用含有 和、：、!或=)引入做为比较运算符的条件，子查询必须返回单个值做为外部查询中WHERE子句的比较参数。如果这样的子查询返回多个值，服务器将返回错误信息。,57,二、使用IN的子查询,使用IN(或NOT IN)关键字引入子查询时，允许子查询返回一列零值或多个结果值。它判断IN关键字前所指定的列值是否在子查询的结果中，IN是嵌套查询中最常用的关键字。,许多查询都可以通过执行一次子查询并将结果值代入外部查询的 WHERE 子句进行评估。,在包括相关子查询的查询中，子查询依靠外部查询获得值。这意味着子查询是重复执行的，为外部查询可能选择的每一行均执行一次。,可以将某些嵌套查询转化为等价的连接查询，以提高查询效率。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。,58,三、使用EXISTS的子查询,使用EXISTS(NOT EXISTS)关键字引入一个子查询时，就相当于进行一次数据是否存在的测试。为了便于理解，我们可以把EXISTS想象为一个函数，而子查询是这个EXISTS函数的参数。它的作用是在WHERE子句中测试子查询返回的行是否存在。EXISTS子查询实际上不产生任何数据，它只返回TRUE或FALSE值。,59,注意,：,(1)EXISTS关键字前面没有列名、常量或其他表达式。,(2)EXISTS所采用的嵌套查询之间的连接不是列之间的关系，而是表之间的关系，它只是测试在子查询中是否存在符合子查询中指定条件的行，所以子查询的选择列表通常几乎都是由星号(*)组成，不必列出列名。,(3)事实上服务器在对外部查询的每一行数据处理时都需要进行一次嵌套查询，但这个嵌套查询不一定需要执行完，只要发现条件成立，就会退出子查询。因此从查询效率考虑，能使用EXISTS的查询就不要使用IN查询，能使用连接查询就不要使用嵌套查询。,60,四、用于替代表达式的子查询,附：,子查询与连接,1、许多包含子查询的 T-SQL 语句都可以改为用联接表示。,2、而其它一些问题只能由子查询提出。,3、在 Transact-SQL 中，包括子查询的语句和不包括子查询但语义上等效的语句在性能方面通常没有区别。,4、但是，在一些必须检查存在性的情况中，使用联接会产生更好的性能。,61,查找所有曾出版过商业书籍的出版商的名称。,（使用子查询）,USE pubs,SELECT pub_name,FROM publishers,WHERE pub_id IN,(SELECT pub_id,FROM titles,WHERE type = business),62,查找所有曾出版过商业书籍的出版商的名称。（使用连接）,select pub_name,from publishers join titles,on publishers.pub_id = titles.pub_id,where titles.type=business,63,只能使用联接的情况：,使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于，联接使您可以在结果中显示多个表中的列。,查找所有曾出版过商业书籍的出版商的名称和书籍的书名。,USE pubs,SELECT pub_name, title,FROM publishers INNER JOIN titles ON publishers.pub_id = titles.pub_id,AND type = business,64,五、使用SELECT INTO语句,对于查询的结果集，有几种处理办法：查看结果；生成报表：,利用结果集创建表。,创建表的方法有很多，如利用向导、执行SQL语句等，利用SELECT INTO语句创建表与以前的方法有一些差异，即利用这个结果集合可以创建临时表和永久表。在使用SELECT INTO语句时，,应该注意下列事项：,(1) SELECT INTO语句指定的表必须在当前数据库中不存在。,(2) 系统默认的是创建临时表，既可以是局部表(在表前添加#)，又可以是全局表(在表前添加#)。局部表只能在当前会话中可见；全局表对所有会话都是可见的。,(3) 如果需要利用SELECT INTO语句创建永久表，需要打开Selectintobulkcopy选项。,(4) 必须在SELECT语句中指定新创建表的字段名称。,65,使用INTO子句可以创建一个新表，并用SELECT的结果集填充该表。新表的结构由选择列表中列的特性定义。基本语法如下：,SELECT子句, INTO 子句 ,FROM 子句,可以在SELECT子句中使用等号将当前记录的字段值赋值到指定的变量中，基本语法如下：,SELECT 变量名 = 列名, .,FROM子句,66,