《第9章 查询和视图优秀课件.ppt》由会员分享，可在线阅读，更多相关《第9章 查询和视图优秀课件.ppt（45页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第9章查询和视图第1页，本讲稿共45页第九章第九章 查询和视图查询和视图在使用数据库的过程中，最常用的操作之一就是查询和视图。当数据库的数据相当大，而我们要浏览所需的记录时，最好的方法就是通过查询或视图返回所需的结果。通过进行查询或视图操作，我们可以快速地返回所需的记录，进行浏览，修改等。本章主要介绍如何在SQLServer2000中运用SQL查询分析器进行查询，及创建视图，修改视图等操作。本章结合本书的“图书销售系统”进行讲述，例子都是以该数据库为基础。第2页，本讲稿共45页9.1查询9.1.1简单查询简单的Transact-SQL查询包括选择列表、FROM子句和WHERE子句，它们分别说明
2、所查询的列、查询的表和视图、以及搜索条件。不区分大小写。1select_list（选择列表）选择列表指出所查询的列，它由一组列名、列表、星号、表达式及变量等组成（变量包括局部变量和全局变量）。（1）选择所有列，例如下面的语句返回operator表中所有列的数据：【例9.1】步骤：打开SQL企业管理器，选择“图书销售系统”数据库，点击菜单“工具”，选择“SQL查询分析器（&Q）”，SQLServer2000将运行“SQL查询分析器”，在查询文本框内输入如下语句。（以下的例子将省略此介绍）SELECT*FROMoperator然后点击运行按钮（图中上的三角），运行结果如图9.1：第3页，本讲稿共4
3、5页图9.1查询结果（2）选择部分列并指定它们的显示次序，集中数据的排列顺序与选择列表中所指定的列名排列顺序相同。第4页，本讲稿共45页【例9.2】步骤：打开SQL查询分析器，在查询文本框内输入如下语句，点击运行。结果如图9.2所示。SELECTAccount,Name,PasswordFROMOperator图9.2查询结果第5页，本讲稿共45页（3）更改列标题。有时因为语言原因，需要在返回数据时改变列标题，就用到此方法。有二种方法：列标题列名；列名as列标题。例9.3的二种方法返回operator表的account、name的所有数据，并以中文“帐号”、“姓名”显示列标题。两种方法的结果一
4、致。如图9.1，图9.2所示。【例9.3】方法一方法一步骤：打开SQL查询分析器，在查询文本框内输入如下语句，点击运行。结果如图9.3所示。SELECT帐户=Account,姓名=NameFROMoperator方法二方法二步骤：打开SQL查询分析器，在查询文本框内输入如下语句，点击运行。结果如图9.4所示。selectaccountas帐户,nameas姓名fromoperator第6页，本讲稿共45页图9.3更改列标题图9.4更改列标题第7页，本讲稿共45页（4）删除重复行。SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使
5、用DISTINCT选项时，所有重复的数据行在SELECT返回的结果集合中只保留一行。如例9.4中，将返回BookSale表的account列、Operator表的name列的所有的数据。条件是两个表的Account列相等的数据，用WHERE关键字筛选，不使用DISTINCT关键字。【例9.4】在SQL查询分析器的文本框内输入如下语句，点击运行。结果如图9.5所示，我们可以看到，返回的数据都是重复的数据，共有26行。selectbooksale.account,operator.namefromoperator,booksalewhereoperator.account=booksale.acc
6、ount第8页，本讲稿共45页图9.5重复的数据行第9页，本讲稿共45页使用DISTINCT关键字后，结果如图9.6所示。可以看出，结果只有三条记录。selectdistinct(booksale.account),operator.namefromoperator,booksalewhereoperator.account=booksale.account图9.6删除重复行第10页，本讲稿共45页（5）限制返回的行数。使用TOPnPERCENT选项限制返回的数据行数，注意:若要返回指定的列名，应在n的后面指定列名，若要返回所有列，则使用*。TOPn说明返回n行。如例9.5中，将返回BookS
7、ale表的四条记录，指定为所有列。结果如图9.7所示。【例9.5】SELECTTOP4*FROMbookrecord而TOPnPERCENT时说明n时表示百分数，指定返回的行数等于总行数的百分之几。如例9.6中，将返回占BookRecord表所有记录的20%行记录，指定返回所有列。结果如图9.8所示。【例9.6】SELECTTOP20PERCENT*FROMbookrecord第11页，本讲稿共45页图9.7返回BookSale表的四条记录图9.8返回20%行记录第12页，本讲稿共45页2FROM子句指定SELECT语句查询及查询相关的表或视图，在FROM子句中最多可指定256个表或视图，它们
8、之间用逗号分隔。在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，应使用对象名限定这些列所属的表或视图。如例9.7中，返回Inrecord表的Account字段、Operator表的Name字段。使用关键字DISTINCT去掉重复记录。（二个表有相同的Account字段，并且存在一对多的关系）【例9.7】SELECTdistinct(inrecord.account),operator.nameFROMinrecord,operatorWHEREoperator.Account=inrecord.Account结果如图9.9：第13页，本讲稿共45页图9.9返回Inrecord表
9、的Account字段、Operator表的Name字段第14页，本讲稿共45页3使用WHERE子句设置查询条件过滤掉不需要的数据行。指定的数据应用符号括起来。如例9.8中，返回Bookrecord表的BooknNO，BookName列的所有数据，并指定Publisher的数据为“广西”，使用LIKE进行模糊查询。结果如图9.10所示。【例9.8】selectbookno,booknamefrombookrecordwherepublisherlike广西%第15页，本讲稿共45页图9.10使用LIKE进行模糊查询第16页，本讲稿共45页4使用ORDERBY子句对查询返回的结果按一列或多列排序。
10、（可省略）ORDERBY子句的语法格式为：如例9.9中将返回Bookrecord表的Bookno,BookName字段的记录，并指定Publisher的数据为“广西”，进行模糊查询，以BookName字段进行升序排序。结果如图9.11所示。【例9.9】selectbookno,booknamefrombookrecordwherepublisherlike广西%orderbybookname第17页，本讲稿共45页图9.11第18页，本讲稿共45页 9.1.2 联合查询联合查询是使用UNION运算符将两个或两个以上SELECT语句的查询结果合并成一个结果集合显示。UNION的语法格式如下：Se
11、lect_statementUNIONALLselectstatementUNIONALLselectstatement.n其中select_statement为待联合的SELECT查询语句。ALL选项表示将所有行合并到结果集合中，不指定该项时联合查询结果集合中的重复行将只保留一行。联合查询时，查询结果的列标题为第一个查询语句中的列标题。因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排序时也必须使用第一查询语句中的列名、列标题或者列序号。第19页，本讲稿共45页在使用UNION运算符时，应保证每个联合查询语句的选择列表中有相同数量的表达式，并且每个查询选择表达式应具有相同的数据
12、类型，或是可以自动将它们转换成相同的数据类型。在自动转换时，对于数值类型，系统将低精度的数据类型转换成高精度的数据类型。在包括多个查询的UNION语句中，其执行顺序是自左至右，使用括号可以改变这一执行顺序。如9.10中将返回BookRecord表和Inrecord表中BookNO与BookName的所有数据，并以中文标题显示。图9.12：【例9.10】select书号=bookno,书名=booknamefrombookrecordunionselectbookno,booknamefrominrecord第20页，本讲稿共45页图9.12返回所有数据并以中文标题显示第21页，本讲稿共45页
13、9.1.3 连接1内连接使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。（1）等值连接：如 例 9.11中 使 用 等 值 连 接 返 回 BookRecord表 和InRecord表中Account列相等的记录。结果有重复记录。【例9.11】SELECT*FromOperatorINNERJOINInRecordONOperator.Account=InRecord.Account第22页，本讲稿共45页（2）自然连接：例 如 9.12，运 用 自 然 连 接 在 选 择 列 表 中
14、删 除 Opertor和InRecord表中重复列（Account）【例9.12】SELECTOperator.name,Operator.password,Operator.Isdeleted,InRecord.*FROMOperatorINNERJOINInRecordONoperator.account=InRecord.account第23页，本讲稿共45页（3）不等值连接：例913中，使用不等值连接，返回BookRecord表中价格大于40的书名。结果如图9.13所示。【例9.13】SelectBookNameFROMBookRecordWHERESalesprice40图9.13不
15、等值连接第24页，本讲稿共45页2外连接外连接分为左外连接(LEFTOUTERJOIN或LEFTJOIN)、右外连接(RIGHTOUTERJOIN或RIGHTJOIN)和全外连接(FULLOUTERJOIN或FULLJOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。（1）左连接例9.14使用左连接，介绍二种方法，将Operator和InRecord表的信息连接起来。其结果是返回二个表的列Account相等的记录及左表Operator的所有记录。共返回14行记录。【例9.14】方法
16、一，使用上面的语法规则：SELECTa.*,b.*FROMOperatorasaLEFTJOINInrecordasbONA.account=b.Account第25页，本讲稿共45页方法二，使用第四章4.3.2节介绍的方法：SELECTa.*,b.*FROMOperatorasa,InrecordasbwhereA.account*=b.Account二种方法返回的结果如图9.14、图9.15。图9.14左连接图9.15左连接第26页，本讲稿共45页右连接例9.15使用右连接，将Operator和InRecord表的信息连接起来，其结果是返回二个表的列Account相等的记录及右表Inrec
17、ord的记录。结果只有12行记录。【例9.15】SELECTa.*,b.*FROMOperatorasa,InrecordasbwhereA.account=*b.Account第27页，本讲稿共45页（3）交叉连接（CROSSJOIN）没有WHERE子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例9.16，BookRecord表中有15条记录，InRecord表中有9条记录，则下列交叉连接检索到的记录数将等于15*9=135行。如图9.16。【例9.16】SELECTbookrecord.BookNO
18、,bookrecord.BookNameFROMBookRecordCROSSJOINinrecordORDERBYBookRecord.BookNO第28页，本讲稿共45页图9.16交叉连接第29页，本讲稿共45页 9.1.4 子查询子查询与我们之前遇到的查询不同，它要求服务器在处理当前查询语句之前预先在生成一个结果，再根据当前的查询结果进行进一步的查询。在实际运用中，子查询有利于简化查询结果，筛选结果层次清晰。子查询的进行顺序是先执行小括号中的子查询并返回结果，然后服务器再开始外围的查询并返回正确的结果。需要注意的是子查询只能放在小括号中。当子查询返回的结果作为查询条件等号（）右边的值存在
19、时只允许子查询返回一行结果，否则系统出错。所以使用时应尽量在子查询前面少使用等号（），除非非常明确子查询才会返回一行结果。如例9.17：【例9.17】selectbookno,booknamefrombookrecordwherebooknoin(selectbooknofrominrecordwherequantity20)第30页，本讲稿共45页首先在表Inrecord中查询出quantity（单价）大于20的BookNO字段，再根据子查询结果从BookRecord表中查询出BookNO和BookName字段。结果如图9.17：图9.17子查询第31页，本讲稿共45页图9.17子查询使用E
20、XISTS关键字在使用子查询时，除了使用IN关键字外，还可以使用EXISTS关键字。EXISTS关键字是将子查询作为一个参数来执行，当子查询返回一行或多行，EXISTS函数为真，如果返回零行则EXISTS为假。例如，例9.18中所查询的结果和上面使用IN关键字的例子相同。【例9.18】Selectbookno,booknamefrombookrecordwhereexists(select*frominrecordwhere bookrecord.Bookno=inrecord.Bookno andquantity20)结果如图9.18：第32页，本讲稿共45页图9.18使用EXISTS关键字
21、的子查询灵活使用子查询子查询可以在列清单中使用以直接获得查询结果而无需使用多次联接查询。如果在搜索的匹配条件中使用统计函数也可以使用子查询来代替。因为在WHERE子句中不允许直接对本SELECT列表的数据使用统计函数，通过在WHERE子句中使用子查询代替可以解决这个问题。第33页，本讲稿共45页9.2视图 9.2.1 视图简介关于视图的概念，请读者参阅第四章4.5.1节。9.2.2 创建视图创建视图要遵循以下原则：只能在当前数据库中创建视图。但是，如果使用分布式查询定义视图，则新视图所引用的表和视图可以存在于其它数据库中，甚至其它服务器上视图名称必须遵循标识符的规则，且对每个用户必须为唯一。此
22、外，该名称不得与该用户拥有的任何表的名称相同。可以在其它视图和引用视图的过程之上建立视图。MicrosoftSQLServer2000允许嵌套多达32级视图。不能将规则或DEFAULT定义与视图相关联。不能将AFTER触发器与视图相关联，只有INSTEADOF触发器可以与之相关联。第34页，本讲稿共45页定 义 视 图 的 查 询 不 可 以 包 含 ORDER BY、COMPUTE或COMPUTEBY子句或INTO关键字。不能在视图上定义全文索引定义。不能创建临时视图，也不能在临时表上创建视图。不能除去参与到用SCHEMABINDING子句创建的视图中的表或视图，除非该视图已被除去或更改而不
23、再具有架构绑定。另外，如果参与具有架构绑定的视图的表执行ALTERTABLE语句影响视图定义，则这些语句将失败。下列情况下必须在视图中指定每列的名称：视图中有任何从算术表达式、内置函数或常量派生出的列。视图中两列或多列具有相同名称（通常由于视图定义包含联接，而来自两个或多个不同表的列具有相同的名称）。第35页，本讲稿共45页希望使视图中的列名与它的源列名不同。（也可以在视图中重命名列。）无论重命名与否，视图列都会继承其源列的数据类型。【注意】本规则在视图基于含有外部联接查询的情况下不适用，因为列可能从不支持空值转而支持空值。其它情况下，无须在创建视图时指定列名。SQLServer使视图中的列与
24、定义视图的查询所引用的列具有相同的名称和数据类型。选择列表可以是基表中列名的完整或部分列表。默认情况下，由于行通过视图进行添加或更新，当其不再符合定义视图的查询的条件时，它们即从视图范围中消失。创建视图方法：第36页，本讲稿共45页1使用菜单方式（1）打开SQLServer企业管理器中，选择“图书销售系统”数据库，右击视图节点，然后从弹出的快捷菜单中选择新建视图命令。（2）然后在弹出的新建视图窗口的文本区域内输入要从哪个表返回字段。本次操作中，可以使用两种方法创建视图。【例9.19】createviewview2asselectbooknoas书号,booknameas书名frombookre
25、cord第37页，本讲稿共45页图9.19利用语句创建视图图9.20右击弹出快捷菜单第38页，本讲稿共45页方法二：利用从数据库中选择表字段的方式，选择要添加到视图的字段。方法是在关系图窗格右击，弹出快捷菜单，选择“添加表（B）”。如图9.20。弹出“添加表”窗体，如图9.21。在窗体中选择要添加的表，如选择“BookRecord”表，主窗体中将弹如图9.22的窗体，然后在表中选择要添加的字段，点击运行后，如果无误，底下的表区域内即返回结果。当关闭创建视图窗体时，系统将提示是否保存，选择保存后，如无重名等问题，即生成新的视图。第39页，本讲稿共45页图9.21添加表图9.22选择字段第40页，
26、本讲稿共45页 9.2.3 更新视图定义更新视图，实际上就是对某个视图进行修改等操作。现在我们以修改上节创建的view2视图为例，更新该视图。【例9.20】更新视图定义步骤如下：1打开SQL企业管理器，选择“图书销售系统”数据库，双击“视图”节点，在视图列表中选择“view2”，右击，选择“设计视图”，即弹出图9.23。图9.23更新视图定义第41页，本讲稿共45页2若要在视图中添加附加表或视图，右击关系图窗格，然后单击添加表命令。（在表或视图选项卡中，单击要添加到新视图中的表或视图，然后单击添加按钮。对每一个要添加到新视图中的表或视图重复以上操作。）3若要从视图中删除整个表或视图，在关系图窗
27、格中，右击表的标题栏，然后单击删除按钮。4在网格窗格的列框中，选择要在视图中引用的列。5若要在视图的结果集中显示此列，选择输出命令。6要按列分组，右击此列，然后单击分组依据命令。7在准则列中，输入指定要检索行的准则。该准则决定了WHERE 子句。若指定了分组依据，则该准则决定HAVING子句。8在或列中，输入任何指定要检索行的附加准则。第42页，本讲稿共45页右击网格窗格的任意处，然后单击属性命令。选择：（可选）（1）输出所有列命令，在视图的结果集中显示所有列。（2）DISTINCT值，以筛选出结果集中的重复值。（3）加密视图命令，加密视图的定义。9在顶层中，输入要在结果集中返回的行数。在行号之后输入词PERCENT，以按行数的百分比将行返回到结果集中。（可选）10右击关系图窗格的任意处，然后单击运行命令（查看结果集），或保存命令（保存视图）。第43页，本讲稿共45页9.2.4 查询视图查询视图，就是返回视图内的数据。步骤如下：1展开服务器组，然后展开服务器。2展开数据库文件夹，展开该视图所属的数据库，然后单击视图。3在详细信息窗格中，右击要查询的视图，选择“打开视图”下的命令。如图9.24：(查询完毕关闭视图窗口)。第44页，本讲稿共45页图9.24查询视图第45页，本讲稿共45页