第4章 数据库操作与SQL语言

2020/5/27,1,第4章数据库操作与SQL语言,讲述数据库的概念和相关操作以及SQL结构化查询语言,2020/5/27,2,数据库（DataBase）就是按一定的组织结构存储在计算机内可共享使用的相关数据的集合。它以文件的形式组织管理一个或多个数据文件，并被多个用户所共享，它是数据库管理系统的重要组成部分。在VisualFoxPro中，数据库包含有数据库表、视图等数据实体，又提供了数据字典、各种数据保护和数据管理功能，可将数据库看成是个收集表的“容器”。数据库文件的扩展名是.dbc，在建立数据库时，系统还会建立一个扩展名是.dct的数据库备注文件和一个扩展名是.dcx的数据库索引文件，因此VisualFoxPro数据库包括这3个文件。,数据库基本概念,2020/5/27,3,4.1数据库基本操作,4.1.1数据库建立数据库的建立有两种方法：使用“数据库设计器”和使用建立数据库的命令。1.用数据库设计器建立数据库菜单“文件”|“新建”，打开“新建”对话框，选定“文件类型”为“数据库”选项按钮，单击“新建文件”，打开“创建”对话框，在“数据库名”文本框中输入所建的数据库名，单击“保存”按钮建立数据库，同时打开“数据库设计器”窗口。利用“数据库设计器”提供的工具或菜单，可以方便的建立数据库表或建立视图，也可以将自由表添加到数据库中，成为数据库表，以及建立数据库表间的永久关联关系等数据库操作。,2020/5/27,4,利用“数据库设计器”，新建数据库rsgzk.dbc，并将表rsb.dbf、gzb.dbf、bmdm.dbf和zz.dbf添加到新数据库中，成为数据库表。建立数据库：菜单“文件”|“新建”命令，选“文件类型”为“数据库”选项按钮，单击“新建文件”，在“数据库名”文本框中输入rsgzk.dbc，单击“保存”按钮，此时建立数据库rsgzk.dbc，同时打开“数据库设计器”窗口。添加数据表：在“数据库设计器”窗口，单击“数据库设计器”工具中的“添加表”按钮，在“打开”对话框中选定rsb.dbf，单击“确定”按钮，rsb.dbf就添加到了rsgzk数据库中，用同样的方法添加表gzb.dbf、bmdm.dbf和zz.dbf。,例4-1,2020/5/27,5,命令格式：CREATEDATABASE功能：建立一个数据库同时打开它。例如建立gzk.dbc的命令如下：CREATEDATABASEgzk,2.用命令方式建立数据库,2020/5/27,6,数据库表：归属于某个数据库的表称为数据库表。自由表：独立存在、不与任何数据库相关联的表称为自由表。数据库表与自由表是VisualFoxPro中表的两种存在状态，这两种表是可以相互转化的，需要注意的是当数据库表转化成自由表时，其数据库表特有的属性将会丢失。数据库表的优点：可以使用长表名，可以使用长字段名；可以为字段制定标题和添加注释；可以为字段制定默认值和输入掩码；字段有默认的控件类；可以为字段设定字段级规则和记录级规则；支持插入、更新和删除事件的触发器；支持主关键字，表间永久关系和参照完整性规则等。,3.数据库表,2020/5/27,7,4.1.2数据库操作命令,1.打开数据库命令命令格式：OPENDATABASE功能：打开一个数据库。例如打开数据库gzk.dbc的命令如下：OPENDATABASEgzk2.关闭数据库命令命令格式：CLOSEDATABASEALL功能：关闭当前的数据库和它的表。选择ALL表示关闭所有打开的数据库和它们的表、所有的自由表以及索引文件，返回1号工作区。例如关闭当前数据库gzk.dbc的命令如下：CLOSEDATABASE,2020/5/27,8,命令格式：MODIFYDATABASE功能：打开数据库设计器，以交互方式查看和修改当前数据库。例如查看和修改数据库gzk.dbc的命令如下：MODIFYDATABASEgzk.dbc4.向数据库中添加表命令命令格式：ADDTABLE功能：向当前数据库添加一个自由表。例如向数据库gzk.dbc添加自由表gzb1.dbf（它为gzb.dbf的复制表）和zz1.dbf（zz.dbf的复制表）。OPENDATABASEgzkADDTABLEgzb1ADDTABLEzz1MODIFYDATABASE职称,基本工资FROMrsb例4-12查询bmdm.dbf中的全部字段以及所有记录，并将结果输出在屏幕上。SELECT*FROMbmdmTOSCREEN例4-13查询表rsb.dbf中基本工资字段的最高值、最低值、平均值，合计值以及参与统计的人数。SELECTMAX(基本工资)AS最高,MIN(基本工资)AS最低,;AVG(基本工资)AS平均,SUM(基本工资)AS合计,;COUNT(*)AS统计人数;FROMrsb,（1）查询全部或者部分字段内容,2020/5/27,38,条件表达式可以使用运算符（=,!=,#,=,=,、=、等比较运算符来生成父查询的查询条件。例如查询奖励工资大于平均奖励工资的职工，输出姓名、职称和奖励工资，命令如下：SELECT姓名,职称,奖励;FROMrsb,gzb;WHERErsb.编号=gzb.编号.AND.奖励;(SELECTAVG(奖励)FROMgzb)说明：子查询的结果AVG(奖励)是一个单值，可以使用比较运算符号。,带有比较运算符号的子查询,2020/5/27,43,在嵌套查询中，当子查询的返回结果是一个集合时，常常用IN谓词来作为父查询的条件，来判断条件值是否在子查询的结果中。IN谓词的使用格式是：父查询WHERE字段IN（子查询）。例4-22列出部门代码是“A01”的职工的工资详细清单。分析：由于在gzb.dbf中没有部门代码字段，所以无法直接从gzb.dbf查找到。因而首先需要在rsb.dbf中查找“A01”的职工编号，再根据找到的编号，在gzb.dbf中找出对应的记录。SELECT*FROMgzbWHERE编号IN(SELECT编号;FROMrsbWHERE部门代码=A01),带IN谓词的子查询,2020/5/27,44,在嵌套查询中，常用EXISTS或NOTEXISTS来检查在子查询中是否有结果返回。使用EXISTS谓词，若子查询结果为非空，则父查询的条件返回值为“真”，否则返回值为“假”；使用NOTEXISTS谓词，则结果跟EXISTS谓词正好相反。其使用格式为：父查询WHERENOTEXISTS(子查询)例如查询计算机系的职工的姓名和职称，命令如下：SELECT姓名,职称FROMrsbWHEREEXISTS;(SELECT*FROMbmdmWHEREbmdm.代码=rsb.部门代码;.AND.部门名称=计算机系)说明：首先,含EXIST谓词的查询总是相关查询：即,子查询必需引用父查询的字段(如例中的“部门代码”字段)。这使得存在谓词的子查询相当慢,因为对于父查询的每一记录,子查询都要执行一遍。父查询中要处理的记录数越多,则查询执行时间越长。其次,EXIST谓词的子查询输出一般使用通配符“*”。这是由于结果只是真或假,对输出什么字段无关紧要，因此可用通配符。,带EXISTS谓词的子查询,2020/5/27,45,ALL、ANY或SOME是量词，其中ANY或SOME是相同的，都表示子查询结果中某个记录值，ALL表示子查询结果中所有记录值。其使用格式为：父查询WHERE(子查询)例如查询基本工资大于所有未婚职工基本工资的职工信息，命令如下：SELECT*FROMrsbWHERE基本工资;ALL(SELECT基本工资FROMrsbWHERE婚否=.F.),带有ALL、ANY或SOME量词的子查询,2020/5/27,46,SELECT-SQL命令除了将查询结果输出到屏幕和浏览窗口外，还可以将查询结果保存为一个表、文本文件和数组，或输出到打印机。例4-23查询所有“讲师”的工资情况，结果按“实发工资”降序排列，分别保存到临时表js、表js1.dbf和文本文件js2.txt。*将结果输出到临时表SELECT*FROMgzbWHERE编号IN;(SELECT编号FROMrsbWHERE职称=讲师);ORDERBY实发工资DESCINTOCURSORjsBROWSE(SELECT编号FROMrsbWHERE职称=讲师);ORDERBY实发工资DESCINTOTABLEjs1BROWSE*将查询结果保存到文本文件js2.txt中，命令如下：SELECT*FROMgzbWHERE编号IN;(SELECT编号FROMrsbWHERE职称=讲师);ORDERBY实发工资DESCTOFILEjs2TYPEjs2.txtFROMrsbGROUPBY职称USEzcstBROWSE,2020/5/27,55,在数据库rsgzk.dbc中建立一个包含表rsb.dbf和表gzb.dbf的所有字段视图，视图取名rsgzst。代码如下：OPENDATABASErsgzkCREATESQLVIEWrsgzst;ASSELECT*FROMrsb,gzbWHERErsb.编号=gzb.编号USErsgzstBROWSE,例4-28,2020/5/27,56,4.4.2界面操作创建视图,视图设计器类似于查询设计器，只是比查询设计器多出一个“更新条件”选项卡，用于设置更新的条件，如图4-36所示。打开“视图设计器”的方法：打开相关的数据库，选择菜单“文件”“新建”命令，选择新建“视图”选项按钮，单击“新建文件”按钮。使用“项目管理器”和使用命令CREATESQLVIEW来打开“视图设计器”窗口。,2020/5/27,57,(1)“表”下拉列表框,（2）“重置关键字”按钮,（3）“全部更新”按钮,（4）“发送SQL更新”复选框,（5）“字段名”窗格,关键字段列,可更新字段列,字段名列,(6)“SQLWHERE子句包括”区,(7)“使用更新”区,视图“更新条件”选项卡,2020/5/27,58,（1）“表”下拉列表框：指定视图所使用的哪些表可以修改。此列表中所显示的表都包含了“字段”选项卡“选定字段”列表中的字段。（2）“重置关键字”按钮：从每个表中选择主关键字字段作为视图的关键字字段，对于“字段名”列表中的每个主关键字字段，在钥匙符号下面打一个“对号”。关键字字段可用来使视图中的修改与表中的原始记录相匹配。（3）“全部更新”按钮：选择除了关键字字段以外的所有字段来进行更新，并在“字段名”列表的铅笔符号下打一个“对号”。（4）“发送SQL更新”复选框：指定是否将视图记录中的修改传送给原始表。,1.“更新条件”选项卡的各项功能,2020/5/27,59,（5）“字段名”窗格：显示所选的、用来输出（因此也是可更新的）的字段。关键字段（使用钥匙符号作标记）：指定该字段是否为关键字段。可更新字段（使用铅笔符号作标记）：指定该字段是否为可更新字段。字段名：显示可标志为关键字字段或可更新字段的输出字段名。（6）“SQLWHERE子句包括”区的选项用来检查更新冲突，在多用户环境下，由系统检查在用视图更新前原始表的数据是否被别的用户修改过，如果这些记录被修改，就不允许更新操作。“关键字段”选项：如果在原始表中有一个关键字字段被改变，设置WHERE子句来检测冲突，使更新失败。对于由另一用户对表中原始记录的其它字段所做修改，不进行比较。“关键字和可更新字段”选项：如果另一用户修改了原始表中标记关键字字段或任何可更新的字段，设置WHERE子句来检测冲突，使更新失败。“关键字和已修改字段”选项：如果从视图首次检索后，本地改变的任一字段在源表中已被改变时，设置WHERE子句来检测冲突，使更新失败。“关键字段和时间戳”选项：如果自原始表记录的时间戳首次检索以后，它被修改过，设置WHERE子句来检测冲突。只有当远程表有时间戳列时，此选项才有效。,“更新条件”选项卡的各项功能（续）,2020/5/27,60,(7)“使用更新”区的选项用来设置更新方式。“SQLDELETE然后INSERT”选项：指先删除原始表中的旧记录，再由视图中被修改的记录在原始表末尾创建一个新的记录。“SQLUPDATE”选项：用视图字段中的变化来修改原始表记录的字段。例4-29在数据库rsgzk.dbc中利用视图设计器建立一个包含有编号、姓名、职称、和包含gzb所有字段的视图，视图取名st，并要求在gzb中除编号之外所有字段设置为可更新源表字段。操作步骤：（1）打开数据库rsgzk.dbc（2）打开“视图设计器”窗口（3）设置输出字段（4）设置更新条件,例4-29,2020/5/27,61,（5）浏览视图和更新数据USEstBROWSE&在浏览器中将5号记录的岗位津贴从25修改为30,例题中“更新条件”对话框设置,2020/5/27,62,视图虽然是虚拟表，但用户还是可以像数据库表一样对视图中的字段设置显示格式、有效性规则等属性。操作方法是：在“视图设计器”的“字段”选项卡中，单击“属性”按钮，出现如下图所示的“视图字段属性”对话框。,2.设置视图字段属性,2020/5/27,63,参数化视图用来限定视图的作用范围，参数化视图在视图的SQLSELECT语句中加一条WHERE子句，从而仅下载那些符合WHERE子句条件的记录，其中的子句是根据所提供的参数值建立的，参数值可以在运行时传递，也可通过编程方式传递。创建参数化视图的步骤：（1）打开“视图设计器”，然后从“查询”菜单中选择“视图参数”项，设定参数名和类型，如设定参数名：职称，类型：字符型。（2）在“视图设计器”对话框的“筛选”选项卡中设置筛选条件。如rsb.职称=?职称，其中？后“职称”是在步骤1中设定的视图参数名。,3.创建参数化视图,2020/5/27,64,将上例4-29中视图st修改成能按职称来显示修改的带参数视图，并以csst视图名保存。操作步骤：（1）打开数据库rsgzk.dbc。（2）打开视图st，（3）从“查询”菜单