关于SQL关系型数据库查询语言(ppt 60页).ppt

第5章关系型数据库查询语言SQL,在数据库中的目的之一是能够对数据进行快速分析，提取有用的信息，在VFP中，数据分析是通过查询（Query）实现的。利用VFP的查询功能，可以从一个或多个表中选择所需的数据。其中，基于多表进行查询更有意义。本章讨论VFP的SELECT-SQL语言及其界面接口语言RQBE。,5.1SQL概述,VFP中的SELECT-SQL是从SQL语言移植过来的查询命令，具有强大的单表与多表查询功能。SQL是结构化查询语言（StructuredQueryLanguage）的缩写，其标准是由美国国家标准化组织ANSI（AmericanNationalStandardsInstitute）于1986年10月公布，并由国际标准化组织ISO（InternationalStandardsOrganization）认证。SQL语言具有以下主要特点：1.SQL是一种一体化语言。它包括了数据定义、数据查询、数据操纵和数据控制等功能，可以完成数据库的全部操作。2.SQL是一种高度非过程化的语言。它没有必要告诉计算机如何去做，而只需要告诉计算机要做什么。3.SQL功能强大，语言简洁。4.SQL命令既可以在交互方式下使用，也可以在程序方式中使用。SQL语言的命令动词如表5.1所示。,5.1SQL概述,SQL语言的命令动词如表5.1所示。SQL语言是一种面向懂英语的人编写的语言，所以，英语文化圈的人比较容易接受这种语言。但是这种语言比较形式化，即便是懂英语的人，也不容易记住使用的若干细节，所以，就又提供了一种RQBE(RelationalQueryByExample)语言，这种语言实际上是SQL语言的一个界面接口语言,从而完成相应的查询任务。,5.2SQL查询,本节的例子用表5.2和表5.3作为背景材料。表5.2产品销售中(CPSOLD.DBC)有关的表文件,表5.2中的数据表PRODUCTBM.DBF的产品编码的前两位为类别编码。,5.2SQL查询,表5.3教学管理(STUGL.DBC)有关的表文件,5.2SQL查询,表5.3教学管理(STUGL.DBC)有关的表文件,5.2SQL查询5.2.1SELECT-SQL的命令格式,【命令格式】SELECTALL|DISTINCTTOP数值表达式PERCENT别名.AS,别名.ASFROMFORCEINNER|LEFTOUTER|RIGHTOUTER|FULLOUTERJOIN!ON联接条件INTO|TOFILEADDITIVE|TOPRINTERPROMPT|TOSCREENNOCONSOLEPLAINNOWAITWHEREANDAND|ORAND|ORGROUPBY,HAVINGUNIONALLSELECT-SQL命令ORDERBYASC|DESC,ASC|DESC【功能】在一个或多个表中查询数据。,5.2SQL查询5.2.1SELECT-SQL的命令【说明】,（1）SELECTALL|DISTINCT：SELECT说明要查询的数据，ALL表示选出的记录中包括重复的记录，是缺省值，DISTINCT则选出的记录中不包括重复的记录。.AS：别名是字段所在的表名，选择表达式可以是字段名，也可以是自定义函数或如右表5.4所示的SQL函数。选择表达式可用一个*表示，此时指定所有的字段。列名用于指定输出时使用的列标题,5.2SQL查询5.2.1SELECT-SQL的命令【说明】,(2)FROM数据库名！表名INNER|LEFT|RIGHT|FULLJOIN数据库名！表名ON联接条件：用于指定查询的表与联接类型。FROM说明要查询的表。JOIN用于联接两个表，ON用于指定联接条件。INNER|LEFT|RIGHT|FULL：指定联接类型。联接类型有四种，分别为：内部联接、左联接、右联接和完全联接。若数据表表1中有字段D1，数据表表2中有字段D2，联接条件为：表1.D1=表2.D2，则联接类型与查询结果如表5.5所示。其中的选项OUTER表示外部联接，即允许满足联接条件的纪录，又允许不满足联接条件的纪录。若省略OUTER选项，效果不变。FORCE字句：严格按照指定的联接条件来联接表，避免VFP因进行联接优化而降低查询速度。,表5.5,5.2SQL查询5.2.1SELECT-SQL的命令【说明】,（3）INTO目标|TOFILE文件名ADDITIVE|TOPRINTERPROMPT|TOSCREEN：用于指定查询结果的输出去向。目标有三种选项：ARRAY（数组）、CURSOR（临时表名）、DBF（表名）。TOFILE文件名指输出到指定的文本文件，并取代原文件内容。ADDITIVE表示只添加新数据，不清除原文件的内容。TOPRINTER指打印输出，PROMPT表示打印前先显示打印确认对话框。TOSCREEN指输出到屏幕，这是缺省值。,5.2SQL查询5.2.1SELECT-SQL的命令【说明】,（4）WHERE联接条件AND联接条件AND|OR筛选条件AND|OR筛选条件：用于联接和筛选条件。若已用ON字句指定了联接条件，WHERE字句中只能指定筛选条件。也可以省去JOIN字句，一次性地在WHERE字句中指定联接条件和筛选条件。筛选条件用的比较符为=(等于)，,!=,#(不等于),=(恒等于),(大于),=(大于等于),(小于),SOME;(SELECT分数表.成绩FROM分数表;WHERE分数表.学号=”2005032104”AND分数表.课程编码=”0202”)本例中SOME也可以改为ALL或ANY，甚至没有关键词SOME,ALL,ANY也可以，因为子查询集合中只有一个元素。,5.3定义功能5.3.1表结构的建立,本节我们给出在程序方式下表结构的建立、修改和表的删除命令的简要操作方法，深入的讨论请参考VFP编程手册。用CREATE表名建立表结构，只能在交互方式下使用。如果要在程序方式下建立表结构，需要用CREATETABLE命令。此命令也可在交互方式下使用。【格式】CREATETABLE|DBF表名（字段名1类型（宽度，小数位），字段名2）【例5.15】建立学生档案表STUDENT.DBF。CREATETABLESTUDENT(学号C(10),姓名C(8),性别L,;班级C(10),出生年月D,通讯地址C(20),简历M)LISTSTRUCTURE,5.3定义功能5.3.2表结构的修改,用MODIFYSTRUCTURE修改表结构，只能在交互方式下使用。如果要在程序方式下修改表结构，需要用ALTERTABLE命令。【命令格式】ALTERTABLE表名ADD|ALTER字段名类型（宽度，小数位）|DROP字段名|RENAMECOLUMNTO【功能】修改表结构。【说明】(1)ADD字段名子句用于添加字段。(2)ALTER字段名子句用于修改已有字段。(3)DROP字段名子句用于删除字段。(4)RENAMECOLUMNTO子句用于更改字段名。【例5.16】已知上例中的STUDENT.DBF表，要求新增一个体重N(3）字段。USESTUDENTLISTSTRUCTUREALTERTABLESTUDENTADD体重N(3)LISTSTRUCTURE,5.3定义功能5.3.2表结构的修改,【例5.17】将STUDENT表中的学号改为数值型，宽度变为10。ALTERTABLESTUDENTALTER学号N(10)LISTSTRUCTURE,5.3定义功能5.3.3表的删除,【格式】DROPTABLE【功能】删除指定的表。【例5.18】删除STUDENT表。DROPTABLESTUDENT,5.4操作功能5.4.1插入记录,【格式1】INSERTINTO表名（字段名1,字段名2,）VALUES(表达式1,表达式2,)【功能】在未打开的表尾直接馈入记录数据。【格式2】INSERTINTO表名FROMARRAY数组名|FROMMEMVAR【功能】在未打开的表尾馈入数组或同名的内存变量值。【例5.19】在STUDENT表尾插入一条记录。INSERTINTOSTUDENT(学号,姓名,性别,班级,出生年月,身高,通讯地址);VALUES(”2005032105”,”李伟”,.T.,”信息管理”,;CTOD(”08/15/90”),1.88,”河南省郑州市”)LIST,5.4操作功能5.4.2更新记录,【命令格式】UPDATE表名SET=,=WHERE【功能】用表达式的值替换字段值。【例5.20】将TEACHER表中讲师的月收入增加10。USETEACHERLISTUPDATETEACHERSET月收入=月收入*1.1;WHERE职称=”讲师”LIST,5.4操作功能5.4.3删除记录,【命令格式】DELETEFROM表名WHERE【功能】删除指定表中的记录。若缺省条件，则删除所有记录。【例5.21】删除TEACHER表中数学教研室的记录。DELEFROMTEACHERWHERE教研室=”数学”PACKUSETEACHERLIST,5.5查询与视图设计器（RQBE）,前面提到SQL语言比较形式化，即便是懂英语的人，也不容易记住使用的若干细节，所以，就又提供了一种RQBE(RelationalQueryByExample)语言，这种语言实际上是SQL语言的一个界面接口语言,从而完成相应的查询任务。这一节我们详细讨论RQBE语言，即查询和视图设计器。它可以创建、修改查询和视图。查询设计器(QueryDesigner)用于建立基于数据表的查询；视图设计器(ViewDesigner)用于建立基于数据表的视图，二者使用十分相似。在用查询和视图设计器创建查询和视图时，我们同时给出其SELECT-SQL语句。本节以表5.2和表5.3中的数据表为数据源给出查询及视图建立的方法。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,1查询的概念(1)查询：就是向一个数据库发出检索信息的请求，从中提取符合特定条件的记录。(2)查询文件：即保存实现SELECT-SQL命令的查询文件。查询文件保存时，系统自动给出扩展名.qpr；查询被运行后，系统还会生成一个编译后的查询文件，扩展名为.qpx。(3)查询结果：通过运行查询文件得到的一个基于表和视图的动态的数据集合。查询结果可以用不同的形式来保存。查询中的数据是只读的。(4)查询的数据源：可以是一张或多张相关的自由表、数据库表、视图。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,2利用“查询向导”创建查询【例5.22】参考例5.6中的要求说明查询建立的过程，并说明这些步骤。基本步骤：(1)在项目管理器的数据选项卡中，选中查询，点击“新建”，进入“新建查询”对话框（也可以在“文件”菜单中，选择“新建”命令创建查询）。(2)在“新建查询”对话框中，单击“查询向导”按钮，进入“向导选择”窗口，如图5.4所示。(3)在“向导选择”窗口中，有3种形式的查询可以使用，即：Cross-TabWizard:以电子数据表的格式显示数据。GraphWizard：以图形的方式显示查询结果。QueryWizard：创建一个标准的查询。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,根据需要，选择其中的一种。我们选择“查询向导(QueryWizard)”，单击“确定”按钮，进入“查询向导”窗口，如图5.5所示。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(4)在“查询向导”窗口中，打开所需要的表，选择好数据表的字段，单击“Next”按钮，如图5.6所示。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(5)在“查询向导”窗口中，添加数据表之间的关联关系，按“Next”按钮,如图5.7所示(左）。选定4个单选项（从上到下分别表示内联接、左联接、右联接和完全联接）中的任一项，单击“Next”按钮，如图5.8所示（右）。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(6)在“查询向导”窗口中，可选择参数对记录进行筛选，然后可单击“完成”按钮结束操作；或单击“Next”按钮，如图5.9所示。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(7)在“查询向导”窗口中，可选择参数对记录进行排序，然后可单击“完成”按钮结束操作；或单击“Next”按钮，如图5.10所示（左）。在此窗口中，可选择参数对记录范围进行限制，然后单击“Finish”按钮结束操作；或单击“Next”按钮，如图5.11所示（右）。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(8)在“查询向导”窗口中，选定任意一个单选项，单击“完成”按钮，进入“另存为”窗口。在此窗口中，输入查询文件的名称，单击“保存”按钮，结束查询文件的创建操作。其中的SELECT-SQL语句为：SELECTProductbm.产品编码,Productbm.产品名称,；Productbm.计量单位,Productsold.数量,Productsold.单价,；Productsold.金额;FROMCPSOLD!PRODUCTBM;LEFTOUTERJOINCPSOLD!PRODUCTSOLD;ONProductbm.产品编码=Productsold.产品编码;ORDERBYProductbm.产品编码,Productsold.数量,5.5查询与视图设计器（RQBE）5.5.1查询设计器,3用查询设计器建立查询基本步骤：查询设计器添加创建查询所基于的数据表定义输出内容设置联接、筛选、排序、分组条件选择查询结果的输出形式保存查询文件运行查询。【例5.23】查询要求同例5.22。(1)在项目管理器的数据选项卡中，选中查询，点击“新建”，进入“新建查询”对话框，在“新建查询”对话框中，单击“新建查询”按钮，打开“查询设计器”窗口，如下图5.12所示。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(2)在“添加表或视图”对话框中，把要建立查询的数据表添加到查询设计器中。如果在所添加的数据表之间没有建立联接，将会出现“联接条件”窗口，如图5.13所示。(3)在“联接条件”窗口的联接类型中，有4种类型的联接，选择联接条件和所需的联接类型后，单击“OK”按钮，并关闭“添加表或视图”窗口。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(4)在“查询设计器”窗口的“字段”选项卡中，选择数据表可出现在查询中的字段或表达式，以及他们的排序表达式和分组表达式等内容（如果需要的话），然后退出“查询设计器”窗口，进入“MicrosoftVisualFoxPro”窗口,单击“是”按钮，进入“另存为”窗口。(5)在“另存为”窗口中，输入创建查询的名称，单击“保存”按钮，结束操作。其中的SELECT-SQL语句同例5.22。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,说明：这个例子也可以按类别分组求和，方法为：将类别添加至已选择字段,金额在“函数和表达式”中设置为“SUM(PRODUCTSOLD.金额)”,选中它，单击“添加”按钮，添加至“选定字段”中。在分组选项卡中选择类别作为分组字段。其中的SELECT-SQL语句为：SELECTProductbm.类别,SUM(Productsold.金额);FROMcpsold!productbm;INNERJOINcpsold!productsold;ONProductbm.产品编码=Productsold.产品编码;GROUPBYProductbm.类别,5.5查询与视图设计器（RQBE）5.5.1查询设计器,4交叉表查询在“查询向导”的“向导选择”窗口中，有一个“交叉表向导（Cross-TabWizard）”选项，它以电子数据表的格式显示数据。但我们通过查询来获取某些数据时，交叉表往往具有很重要的作用。我们以“教学管理(STUGL.DBC)”数据库中的一个视图为例（有关视图的内容参见5.5.2中的例5.24），来说明交叉表的使用。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(1)在“向导选择”窗口中,选定“交叉表向导（Cross-TabWizard）”，单击“确定”按钮，将会出现“交叉表向导（Cross-TabWizard）”窗口，如图5.14所示（上），窗口中的内容和形式与在“查询向导”窗口基本相同。在这里，需要选定将要使用的数据库或数据表（选定“学生成绩单”视图），并选定所需字段，然后单击“Next”按钮，进入如图5.15所示（下）的“交叉表向导”窗口。(2)在“交叉表向导”窗口中，需要定义交叉表的布局。在本例中，我们将“姓名”放在“行”的位置，“课程名称”放在“列”的位置，“成绩”放在“数据”区域，用鼠标将上述三个字段拖到相应的位置即可。设置好“交叉表”的布局之后，窗口下面的“Next”按钮被激活，单击此按钮，进入下一个窗口，如图5.16所示（下页）。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(3)在“交叉表向导”窗口中，需要在“总结”下面的单选项中选择每行所需的总计类型；在“分类汇总”下面的单选项中确定是否对数据添加分类汇总列。选定之后，单击“Next”按钮或单击“Finish”按钮，进入图5.17所示（下页）的窗口。本例中，选定“总结”下的“求和”单选项和“分类汇总”下面的“数据求和”单选项。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(4)在“交叉表向导”窗口中，根据需要选择结果的保存类型。在窗口下面有一个“Displaynullvalues”的复选框，如果选定此复选框，则在交叉表中没有赋值的字段将自动赋给“NULL”值，以后需要时，可以随时添加相应的内容；如果不选定此复选框，在交叉表中没有赋值的字段将没有任何显示。此外，在窗口的右下角还有一个“预览”按钮，单击此按钮，将会显示交叉表的结果，如果所作的交叉表不满足自己的需要，还可以退回到“交叉表向导”窗口中，重新设定交叉表。一切都设置好之后，单击“Finish”按钮，结束交叉表的制作。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,(5)单击“Finish”按钮后，将会出现“另存为”窗口，在这里，选择保存交叉表的文件夹，并为所作的交叉表确定名字“学生成绩单”。本例的交叉表结果如图5.18所示。其中的SELECT-SQL语句为：SELECT学生成绩单.姓名,学生成绩单.课程名称,SUM(学生成绩单.成绩);FROMSTUGL!学生成绩单;GROUPBY学生成绩单.姓名,学生成绩单.课程名称;ORDERBY学生成绩单.姓名,学生成绩单.课程名称;INTOCURSORSYS(2015)DO(_GENXTAB)WITHQuery,.t.,.t.,.t.,.t.,0,.t.BROWSENOMODIFY,5.5查询与视图设计器（RQBE）5.5.1查询设计器,5定向输出查询的结果定向输出结果可以确定查询结果的输出格式。其操作步骤如下：(1)在项目管理器中，选中某个查询文件，单击右边的“修改”按钮，进入“查询设计器”窗口。(2)选择系统菜单“查询”中的“查询去向”选项，打开“查询去向”窗口，如图5.19所示。在此窗口中，有4种输出格式：浏览（Browse）：将查询结果输出到浏览窗口。临时表（Cursor）：将查询结果存入一个临时的数据表中，关闭数据表时，查询结果丢失。表（Table）：将查询结果存入一个数据表中，关闭数据表时，查询结果保留。屏幕（Screen）：将查询结果输出到屏幕上。(3)选定所需格式，然后单击“OK”按钮。,5.5查询与视图设计器（RQBE）5.5.1查询设计器,6运行查询在完成查询设计并指定输出格式之后，可以通过运行查询来启动该查询。其操作步骤为：在项目管理器中，选中所要运行的查询，单击“运行”；或打开“查询设计器”窗口，然后选择系统菜单“查询”的“运行查询”选项，将结果以指定的格式输出。另外，也可以在命令窗口中输入命令：DO，查询结果也将以指定的格式输出。,5.5查询与视图设计器（RQBE）5.5.2视图设计器,前面介绍的查询可以很方便的从表中检索出所需的数据，但不能修改所查询的数据。如果既要查询又要修改数据，可以使用视图，也就是说将查询功能扩展为能够修改数据，则为视图；因此，视图的创建和查询的创建一样，只是视图增加了更新功能。视图是数据库的一部分，与数据库表有很多相似的地方。视图是一个虚表，其中存放的是数据库表的定义.在大多数场合下，视图的作用等同于表，数据库提供给表的一些特性，比如给字段设置标题等，同样适用于视图。在VFP中，有两种类型的视图：本地视图和远程视图。本地视图能够更新存放在本地计算机上的表，远程视图能够更新存放在远程服务器上的表。,5.5查询与视图设计器（RQBE）5.5.2视图设计器,1视图的创建(1)使用命令方式。【命令格式】CREATESQLVIEW视图名ASSELECT_SQL语句【例5.24】参考例5.7的SELECT_SQL语句创建视图“学生成绩单”。可使用以下的命令：OPENDATABASEstuglCREATESQLVIEW学生成绩单AS;SELECT学生表.学号,学生表.姓名,学生表.班级,分数表.成绩,课程表.课程名称;FROM学生表INNERJOIN(分数表INNERJOIN课程表;ON分数表.课程编码=课程表.课程编码)ON学生表.学号=分数表.学号也可以使用已有的SELECT_SQL语句来创建视图,只要把SELECT_SQL语句存入一个变量,然后用宏替换在CREATESQLVIEW命令中调用即可。例如,上面创建视图的命令,可以改成下列的格式:x=“SELECT学生表.学号,学生表.姓名,学生表.班级,分数表.成绩,课程表.课程名称;FROM学生表INNERJOIN(分数表INNERJOIN课程表;ON分数表.课程编码=课程表.课程编码)ON学生表.学号=分数表.学号CREATESQLVIEW学生成绩单ASSELECTProductbm.产品编码,Productbm.产品名称,;Productbm.计量单位,Productsold.数量,Productsold.单价,;Productsold.金额;FROMproductbmLEFTOUTERJOINproductsold;ONProductbm.产品编码=Productsold.产品编码以上的两个例子创建的视图可在项目管理器中浏览或修改。,5.5查询与视图设计器（RQBE）5.5.2视图设计器,（2）使用“视图设计器”创建视图。启动“视图设计器”添加表或视图建立表间的关联选择字段筛选记录排序记录设置更新条件.新建本地视图对话框中还包括一个视图向导按钮，用于引导用户快速创建视图。我们可以参考例5.24用视图向导创建“学生成绩单”视图（参见实训2）。“视图设计器”的窗口与“查询设计器”窗口相类似,但也有一些差别。“视图设计器”多提供了一个“更新条件”选项卡，用于对出现在视图中的字段进行修改规则的设置，下面我们介绍视图的更新，而其他内容不再赘述。,5.5查询与视图设计器（RQBE）5.5.2视图设计器,2视图的更新查询的结果只能阅读，不能修改。而视图则不仅仅具有查询的功能，还可修改记录数据并使源表随之更新。与查询设计器相比，在视图设计器中多了一个更新条件选项卡，该选项卡具有使修改过的记录更新源表的功能。,5.5查询与视图设计器（RQBE）5.5.