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

1、1 视图基本概念 一、视图基本概念 1基本表 基本表是独立存在的表。在SQL Server中创建了一个基本表，那么可以在表中添加记录，这些记录存放在硬盘上。“学生表”、“课程表”、“成绩表”等等，都是基本表。,2视图 视图看上去同表似乎一模一样，具有一组命名的字段和数据项，但它其实是一个虚拟的表，在物理上并不实际存在。视图是由查询数据库表产生的，它限制了用户能看到和修改的数据。视图一旦定义后，就可以和基本表一样被查询、被删除，也可以在一个视图基础上再定义新的视图。 视图兼有表和查询的特点：与查询相类似的是，视图可以用来从一个或多个相关联的表或视图中提取有用信息；与表相类似的是，视图可以用来更新

2、其中的信息，并将更新结果永久保存在磁盘上。我们可以用视图使数据暂时从数据库中分离成为游离数据，以便在主系统之外收集和修改数据。,二、视图的优缺点 当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化，同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。 视图有很多优点，主要表现在： 1、视点集中 视图集中即是使用户只关心它感兴趣的某些特定数据和他们所负责的特定任务。这样通过只允许用户看到视图中所定义的数据而不是视图引用表中的数据而提高了数据的安全性。,2、简化操作 视图大大简化了用户对数据的操作。因为在定义视图时，若视图本身就是一个复杂查询的结果集，这样在每一次执行相

3、同的查询时，不必重新写这些复杂的查询语句,只要一条简单的查询视图语句即可。可见视图向用户隐藏了表与表之间的复杂的连接操作。 3、定制数据 视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。因此，当有许多不同水平的用户共用同一数据库时，这显得极为重要。,4、合并分割数据 在有些情况下，由于表中数据量太大，故在表的设计时常将表进行水平分割或垂直分割，但表的结构的变化却对应用程序产生不良的影响。如果使用视图就可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。 5、组合分区数据 用户可以把来自不同表的两个或多个查询结果组合成单一的结果集。这在用户看来是

4、一个单独的表，称为分区视图。,6、安全性 视图可以作为一种安全机制。通过视图用户只能查看和修改他们所能看到的数据。其它数据库或表既不可见也不可以访问。如果某一用户想要访问视图的结果集，必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。 7、视图的缺点 视图的缺点主要表现在其对数据修改的限制上。,三、使用视图的场合 1）表的行或列的子集。 2）两个或多个表的联合。 3）两个或多个表的连接。 4）其他视图的子集。 5）视图与表的组合。,2 创建视图 创建视图时应该注意以下情况： 只能在当前数据库中创建视图。 视图中最多只能引用1024列。 如果视图引用的基表或者视图被删除，则该视

5、图不能再被使用，直到创建新的基表或者视图。 如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。,当视图所引用不同基表的列中有相同列名时或者希望给视图中的列指定新的列名时则需要重新指定列的别名。 不能在视图上创建索引，不能在规则、缺省、触发器的定义中引用视图。 当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库对象存在，而且数据修改语句不能违反数据完整性规则。 视图的名称必须遵循标识符的规则，且对每个用户必须是唯一的。此外，该名称不得与该用户拥有的任何表的名称相同。,注意： 一个视图可以基于一个或若干个基表，也可以基于一个或若干个视图

6、，同时也可以基于基表和视图的混合体。,使用Transact-SQL语言创建视图 创建视图的语法格式如下： CREATE VIEW .视图名(列名,.n) WITH ENCRYPTION|SCHEMABINDING|VIEW_METADATA AS SELECT查询语句 WITH CHECK OPTION,参数说明： (1)视图名称必须符合标识符规则。可以选择是否指定视图所有者名称。 (2)CREATE VIEW子句中的列名是视图中显示的列名。只有在下列情况下，才必须命名 CREATE VIEW 子句中的列名：当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联

7、接），视图中的某列被赋予了不同于派生来源列的名称。当然也可以在 SELECT 语句中指派列名。 注意：如果未指定列名，则视图列将获得与 SELECT 语句中的列相同的名称。,（3）定义视图的语句是一个 SELECT查询语句。该语句可以使用多个表或其它视图。若要从创建视图的 SELECT 子句所引用的对象中选择，必须具有适当的权限。视图不必是具体某个表的行和列的简单子集。可以用具有任意复杂性的 SELECT 子句，使用多个表或其它视图来创建视图。 （4）在索引视图定义中，SELECT 语句必须是单个表的语句或带有可选聚合的多表 JOIN。,（5）在CREATE VIEW语句中，对于SELECT查

8、询语句有如下限制： 创建视图的用户必须对该视图所参照或引用的表或视图具有适当的权限。 在查询语句中，不能包含ORDER BY(如果要包含的话SELECT子句中要用TOP n percent)、COMPUTE或COMPUTE BY关键字。也不能包含INTO关键字。 不能在临时表中定义视图(不能引用临时表)。,（6）WITH CHECK OPTION：强制视图上执行的所有数据修改语句都必须符合由 SELECT查询语句设置的准则。通过视图修改数据行时，WITH CHECK OPTION 可确保提交修改后，仍可通过视图看到修改的数据。 （7）WITH ENCRYPTION：表示 SQL Server

9、加密包含 CREATE VIEW 语句文本的系统表列。使用 WITH ENCRYPTION 可防止将视图作为 SQL Server 复制的一部分发布。,（8）SCHEMABINDING：将视图绑定到架构上。指定 SCHEMABINDING 时，SELECT查询语句必须包含所引用的表、视图或用户定义函数的两部分名称 (owner.object)。不能除去参与用架构绑定子句创建的视图中的表或视图，除非该视图已被除去或更改，不再具有架构绑定。否则，SQL Server 会产生错误。另外，如果对参与具有架构绑定的视图的表执行 ALTER TABLE 语句，而这些语句又会影响该架构绑定视图的定义，则这些

10、语句将会失败 。,（9）VIEW_METADATA：指定为引用视图的查询请求浏览模式的元数据时，SQL Server 将向 DBLIB、ODBC 和 OLE DB API 返回有关视图的元数据信息，而不是返回基表或表。浏览模式的元数据是由 SQL Server 向客户端 DB-LIB、ODBC 和 OLE DB API 返回的附加元数据，它允许客户端 API 实现可更新的客户端游标。浏览模式的元数据包含有关结果集内的列所属的基表信息。 对于用 VIEW_METADATA 选项创建的视图，当描述结果集中视图内的列时，浏览模式的元数据返回与基表名相对的视图名。当用 VIEW_METADATA 创建

11、视图时，如果该视图具有 INSERT 或 UPDATE INSTEAD OF 触发器，则视图的所有列（timestamp 除外）都是可更新的。,3 管理视图 1、使用系统存储过程查看视图信息 sp_help 表/视图 sp_helptext 表/视图 sp_depends 表/视图,2、使用T-SQL语句修改视图 ALTER VIEW语句的语法格式为： ALTER VIEW .视图名(列名,.n) WITH ENCRYPTION|SCHEMABINDING|VIEW_METADATA AS SELECT查询语句 WITH CHECK OPTION 参数说明同CREATE VIEW相同。,3、使

12、用DROP VIEW删除视图 删除视图的语法格式如下。 DROP VIEW 视图名1，视图名n 使用该语句一次可以删除多个视图。,4 通过视图访问数据 使用视图管理表中的数据包括插入、更新和删除三种操作，在操作时要注意以下几点： 修改视图中的数据时，可以对基于两个以上基表或视图的视图进行修改，但是不能同时影响两个或者多个基表，每次修改都只能影响一个基表。 不能修改那些通过计算得到的列，例如年龄和平均分等。 若在创建视图时定义了 WITH CHECK OPTION选项，那么使用视图修改基表中的数据时，必须保证修改后的数据满足定义视图的限制条件。,执行UPDATE或DELETE命令时，所更新或删除

13、的数据必须包含在视图的结果集中。 如果视图引用多个表时，无法用DELETE命令删除数据。 如果视图引用多个表，使用INSERT或UPDATE语句对视图进行操作时，被插入或更新的列必须属于同一个表。 视图的字段来自集合函数，则此视图不允许修改操作。 若视图定义中含有GROUP BY 子句，则此视图不允许修改操作。 若视图定义中含有DISTINCT短语，则此视图不允许修改操作。 一个不允许修改操作视图上定义的视图，也不允许修改操作。,5 SELECT语句 SELECT语句能够从数据库中检索出符合用户需求的数据，并将结果以表格的形式返回，是SQL Server中使用最频繁的语句之一。基本语法格式如下

14、： SELECT 列名1 ,列名2 . INTO 新表名 FROM 表名1 ,表名2 . WHERE 条件 GROUP BY 列名列表 HAVING 条件 ORDER BY 列名列表 ASC | DESC 其中，用 表示可选项。 SELECT语句至少包含两个子句：SELECT和FROM，SELECT子句指定要查询的指定表中的列，FROM子句指定查询的表。,SELECT子句 SELECT子句用于指定要返回的列，其完整的语法如下： SELECT ALLDISTINCT TOP n PERCENT 列名 AS 别名别名=表达式 ,.n ,FROM子句 只要SELECT子句有要查询的列，就必须使用FR

15、OM子句指定进行查询的单个或者多个表。此外，SELECT语句要查询的数据源除了表以外还可以是视图，视图相当于一个临时表，其语法格式如下： FROM 表名|视图名 ,.n 当有多个数据源时，可以使用逗号“,”分隔，但是最多只能有16个数据源。数据源也可以像列一样指定别名，该别名只在当前的SELECT语句中起作用，方法为：数据源名 AS 别名，或者数据源名 别名。指定别名的好处在于以较短的名字代替原本见名知意的长名。,WHERE子句 WHERE子句指定查询的条件，限制返回的数据行。其语法格式如下： WHERE 指定条件 WHERE子句用于指定搜索条件，过滤不符合查询条件的数据记录，使用比较灵活且复

16、杂。可以使用的条件包括比较运算、逻辑运算、范围、模糊匹配以及未知值等。,一、使用通配符* select * from 表名/视图名 返回from中指定的表中的所有列。 二、使用指定的列 select 列名1,列名2,列名n from 表名/视图名 返回from中指定的表中的指定列。,三、使用TOP关键字 select top n percent 列名 四、使用DISTINCT关键字 使用DISTINCT关键字时， 1、无论遇到多少个空值，只返回一个。 2、表达式只包含一个列名，且不能包含算术表达式。 3、不能包含text、ntext和image类型字段。 4、是sum、avg和count的可选

17、关键字。,五、使用计算列 六、使用列的别名 1、select 列名1 别名1, 2、select 别名1=列名1, 3、 select 列名1 as 别名1, 七、使用SELECT语句进行无数据源检索,6 使用WHERE子句 select 列名 from 表名/视图名 where 说明： 1、中的条件数目无限制。 2、where子句必须紧跟在from子句的后面。,一、使用比较运算符 用于比较两个表达式是否满足条件。 用于除text、ntext和image类型外的所有表达式。其结果返回值是布尔类型，即true或false。 二、使用逻辑运算符 三种逻辑运算符：or、and和not。,三、使用BE

18、TWEEN关键字 在where子句中使用BETWEEN关键字来限制查询数据的 范围。 BETWEEN关键字的效果可以用含有=的表达式来代替；NOT BETWEEN关键字的效果可以用含有 和的表达式来代替。,四、使用IN关键字 IN关键字用以确定一个给定值是否与子查询或列表中的值相匹配。 要查询不在子查询或列表中的记录，可以使用NOT IN。 注：在where中使用between是记录的一段取值范围；使用in是记录的离散取值范围。,五、使用LIKE关键字,注： 1、一定要把LIKE运算符与=运算符区别开。通配符只有在LIKE子句中才有意义。 2、万一要查询的字符串中包含了通配符作为字符串的一部分

19、，如何处理？ 假设一个表test的某一列col有以下4个值： xyz %xyz x_yz xyz 查找以“X_”开头的字符串 Select col From test Where col LIKE X_% ESCAPE ,7 ORDER BY子句 ORDER BY 列名 ASC|DESC 说明： 1、 ASC-升序(默认)，DESC-降序 2、不能使用text、ntext和image类型的字段 3、引用列的数量不能大于select语句中的列的数量,8 使用表的别名 SELECT 表的别名.列名1 ,表的别名.列名2 . FROM 表名 AS 表的别名 9 多表查询 在很多情况下，需要从多个表中

20、提取数据，组合成一个结果集。如果一个查询需要对多个表进行操作，则将此查询称为连接查询。连接查询包括内连接、外连接和交叉连接等。 一、内连接 内连接使用比较运算符（最常使用的是等号，即等值连接），根据每个表共有列的值匹配两个表中的行。只有每个表中都存在相匹配列值的记录才出现在结果集中。在内连接中，所有表是平等的，没有主次之分。,1.在where子句中指定连接 select 表.查询列名1,表.查询列名n from 表1,表2 where 表1.列名=表2. 列名 2.使用join和on关键字指定连接条件 select 表.查询列名1,表.查询列名n from 表1 inner join 表2 o

21、n 表1.列名=表2. 列名 3.内连接的条件查询,二、外连接 与内连接相对，参与外连接的表有主次之分。以主表的每一行数据去匹配从表中的数据列，符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中。 外连接可以分为左连接、右连接和完全连接3种情况。,1、左连接 将左表中的所有记录分别与右表中的每条记录进行组合，结果集中除返回内部连接的记录以外，还在查询结果中返回左表中不符合条件的记录，并在右表的相应列中填上NULL，由于BIT类型不允许为NULL，就以0值填充。左外连接的语法格式如下： SELECT 列名列表 FROM 表名1 AS A LEF

22、T OUTER JOIN 表名2 AS B ON A.列名=B.列名,2、右连接 和左连接类似，右连接是将左表中的所有记录分别与右表中的每条记录进行组合，结果集中除返回内部连接的记录以外，还在查询结果中返回右表中不符合条件的记录，并在左表的相应列中填上NULL，由于BIT类型不允许为NULL，就以0值填充。右连接的语法格式如下： SELECT 列名列表 FROM 表名1 AS A RIGHT OUTER JOIN 表名2 AS B ON A.列名=B.列名,3、完全连接 完全连接是将左表中的所有记录分别与右表中的每条记录进行组合，结果集中除返回内部连接的记录以外，还在查询结果中返回两个表中不符

23、合条件的记录，并在左表或右表的相应列中填上NULL，BIT类型以0值填充。全外连接的语法格式如下： SELECT 列名列表 FROM 表名1 AS A FULL OUTER JOIN 表名2 AS B ON A.列名=B.列名,三、 交叉连接 交叉连接的结果是两个表的笛卡儿积，在实际应用中一般是没有意义的，但在数据库的数学模式上有重要的作用。交叉连接的语法格式如下： SELECT 列名列表 FROM 表名1 CROSS JOIN 表名2 或者 SELECT 列名列表 FROM 表名1, 表名2,四、 连接两个以上的表 五、自连接 自连接就是一个表的两个副本之间的内连接。表名在FROM子句中出现

24、两次，必须对表指定不同的别名，在SELECT子句中引用的列名也要使用表的别名进行限定。自连接的语法格式如下： select A.列名 B.列名 from 表名 as A 连接方式 join 表名 as B ,10 使用UNION子句 UNION运算符用于将两个或多个检索结果合并成一个结果，当使用UNION时，需遵循以下两个规则。 （1）所有查询中的列数和列的顺序必须相同。 （2）所有查询中按顺序对应列的数据类型必须兼容。,11 使用GROUP BY子句 命令格式： SELECT 列名 FROM 表名 GROUP BY ALL 组名,命令说明： (1)在SELECT语句中所指定的列必须是GROU

25、P BY子句中的列名，或是被聚合函数所使用的列。 (2)ALL关键字，它指定返回由GROUP BY子句产生的所有组，即使某些组没有符合WHERE子句中指定条件的行。 (3)text、ntext和image类型的列不能用于GROUP BY子句。 (4)分组的列中包含多个NULL时，这些空值将放入一个组中进行显示。 (5)在GROUP BY子句中必须使用列的名称。,使用GROUP BY注意事项： 1、什么时候使用GROUP BY子句? 当需要按某一列数据的值进行分类，在分类的基础上对数据进行统计，就需要使用GROUP BY子句。 2、指定GROUP BY时，选择列表中任一非聚合表达式内的所有列都应

26、包含在GROUP BY列表中。 3、选择列表中的列如果不在聚合函数里面，就必须在GROUP BY列表里面。否则语法上虽然不会出错，但是执行起来会出错。,USE pubs GO SELECT type,pub_id, avg(price) FROM titles group by type GO USE pubs GO SELECT type,pub_id, avg(price) FROM titles group by type,pub_id order by 1,2 GO,ERROR!,OK!,一、在GROUP BY子句中使用多个列 可以在GROUP BY子句中列出多个列，以实现嵌套分组。

27、二、GROUP BY子句和ALL关键字 在包含GROUP BY子句的查询中可以使用WHERE子句。利用WHERE中的条件可在分组之前消除不符合WHERE条件的行。 在GROUP BY语句中使用ALL关键字，只有在SELECT语句中含有WHERE子句时才显得有意义。 没有ALL关键字，包含GROUP BY子句的SELECT语句将不显示不符合条件的行的组。如果使用ALL关键字，那么查询结果将包括由GROUP BY子句产生的所有组，不符合查询条件的列在使用统计函数时将返回NULL值。,12 使用HAVING子句 在包含GROUPBY子句的查询中可以使用WHERE子句。利用WHERE中的条件可在分组之

28、前消除不符合WHERE条件的行。当完成对数据结果的查询和统计后，可以使用HAVING关键字对查询和计算的结果进行进一步的筛选。 注意HAVING和WHERE的区别： 1、WHERE子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。 2、HAVING语法与WHERE语法类似，但HAVING可以包含聚合函数。 3、HAVING子句可以引用选择列表中出现的任意项。,13 使用COMPUTE和COMPUTE BY子句 使用COMPUTE子句可以在结果集的最后生成附加的汇总行，因此既可以查看明细行，又可以查看汇总行。 命令格式： COMPUTE 函数(被统计列名) B

29、Y (列名) 命令说明： (1)该语句按关键字BY后面的列清单实现数据的分组汇总。 (2)在COMPUTE或COMPUTE BY子句中，不能包含text、ntext或image数据类型。 (3)在COMPUTE后的列名只能出现在聚合函数中。 (4)不能在使用COMPUTE子句的同时使用SELECT INTO子句。,当COMPUTE不带BY子句时，查询结果包含： 第一个结果集是包含查询结果的所有明细行。 第二个结果集有一行，其中包含COMPUTE子句中所指定的聚合函数的合计。 当COMPUTE与BY一起使用时，COMPUTE子句可以对结果集进行分组并在每一组之后附加汇总行，符合查询条件的每个组都

30、包含： 每个组的第一个结果集是明细行集。 每个组的第二个结果集有一行，其中包含该组的COMPUTE子句中所指定的聚合函数的小计。,使用COMPUTE和COMPUTE BY子句需要注意以下几点： (1) COMPUTE子句不能和SELECT INTO子句一起使用。 (2) 如果具有ORDER BY子句，列名必须出现在SELECT中。 (3) 聚集函数不能带DISTINCT关键字。 (4) 如果具有COMPUTE BY子句，必须使用ORDER BY子句；在COMPUTE BY中出现的列，只能等于或者小于ORDER BY的列，且顺序要相同。,14 使用嵌套查询 在一个SELECT语句中嵌入另一个完整

31、的SELECT语句称为嵌套查询。嵌入的SELECT语句称为子查询，而包含子查询的SELECT语句称为外部查询。子查询自身可以包括一个或多个子查询，也可以嵌套任意数量的子查询。但子查询中返回的数据类型是有限制的，它不能使用image和text等数据类型，并且子查询返回的数据类型还必须和外部查询WHERE子句中的数据类型相匹配。 子查询既可以嵌套在SELECT语句中，也可以嵌套在UPDATE、DELETE和INSERT语句中。,什么是子查询？ 子查询是一个 SELECT 查询，它返回单个值且嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。 为什么使用子查询？ 把

32、一个复杂的查询分解成一些系列的逻辑步骤； 当一个查询依赖于另一个查询时，子查询会很有用。 子查询产生一个结果集，我们可以把它看成一个表（派生表），用来代替FROM子句中的表。,一、 使用比较运算符的子查询 子查询可由一个比较运算符(：、：、!或=)引入做为比较运算符的条件，子查询必须返回单个值做为外部查询中WHERE子句的比较参数。如果这样的子查询返回多个值，服务器将返回错误信息。,二、使用IN的子查询 使用IN(或NOT IN)关键字引入子查询时，允许子查询返回一列零值或多个结果值。它判断IN关键字前所指定的列值是否在子查询的结果中，IN是嵌套查询中最常用的关键字。 许多查询都可以通过执行一

33、次子查询并将结果值代入外部查询的 WHERE 子句进行评估。 在包括相关子查询的查询中，子查询依靠外部查询获得值。这意味着子查询是重复执行的，为外部查询可能选择的每一行均执行一次。 可以将某些嵌套查询转化为等价的连接查询，以提高查询效率。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。,三、使用EXISTS的子查询 使用EXISTS(NOT EXISTS)关键字引入一个子查询时，就相当于进行一次数据是否存在的测试。为了便于理解，我们可以把EXISTS想象为一个函数，而子查询是这个EXISTS函数的参数。它的作用是在WHERE子句中测试子查询返回的行是否存在。EXISTS子查询实际上不产生任何数据，它只返回TRUE或FALSE值。,注意： (1)EXISTS关键字前面没有列名、常量或其他表达式。 (2)EXISTS所采用的嵌套查询之间的连接不是列之间的关系，而是表之间的关系，它只是测试在子查询中是否存在符合子查询中指定条件的行，所以子查询的选择列表通常几乎都是由星号(*)组成，不必列出列名。 (3)事实上服务器在对外部查询的每一行数据处理时都需要进行一次嵌套查询，但这个嵌套查询不一定需要执行完，只要发现条件成立，就会退出子查询。因此从查询效率考虑，能使用EXISTS的查询就不要使用IN查询，能使用连接查询就不要使用嵌套查询。,四、用于替代表达式的子查询 附：子查询与连接