第四章VF关系数据库标准语言SQL教案

第四章关系数据库标准语言SQLSQL：是结构化查询语言，它包括数据查询、数据定义、数据操纵、数据控制四部分。但在VF中只绍介查询、定义、操纵三个方面。SQL命令动词：SQL功能命令动词数据查询SELECT数据定义CREATE、DROP、ALTER数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKE一、查询功能1、SQL语言的核心是查询。查询的基本形式：SELECT字段名/表达式FROM表名/视图名WHERE条件由于表是由行和列组成就像数学中所学的坐标由X轴和Y轴构成，所需确定某个点需要说明X为多少Y为多少。而表中需要查询某些数据就必需指明行和列，所以在查询时：1FROM表名/视图名：说明查询的数据来自于所需要的表或视图。若某于多个表时，各个表之间用逗号隔开。2SELECT字段名/表达式：说明从表或视图中所查询结果是哪些字段名或表达式的值（各字段名或表达式之间用逗号隔开）即针对表的列来操作（限定列）。3WHERE条件：说明从表或视图中所查询结果是哪些行即记录针对表的行来操作（限定行）。关于其它短语的说明：短语说明表名.*来自于某（些）表的所有字段（属性）DISTINCT去掉查询结果中的重复记录（元组）IN相当于数学集合“∈”只需属于其中一个元素（值）就是满足条件；NOTIN则不属于其中任意一个元素（值）就满足条件BETWEEN…AND表示…和…之间，注意和第二章BETWEEN（）函数的区别LIKE字符串匹配运算符，注意通配符是“%”和“_”与第二章LIKE（）函数区别ORDERBY对指定某个（些）字段排序，只能对最终的查询结果排序，不能用于子查询GROUPBY用于分组计算查询（指定分组依据）HAVING对GROUPBY分组计算查询的结果进一步的加以限定即限定分组（对分组之后的限定），必须跟GROUP连用，不能单独使用。但与WHERE条件无关，因WHERE条件是对分组之前限定某些元组（记录）是否来参加分组。（注：HAVING与WHERE使用的区别）ISNULL查询某字段为空值的记录（元组），但不能用“=NULL”表示ISNOTNULL查询某字段不为空值的记录（元组），但不能用“#NULL”或“NOTISNULL”ANY/SOME子查询中只需一行为.T.则外层就满足条件ALL子查询中所有的行都为.T.则外层就满足条件EXISTS子查询中只需有结果（无论多少），则外层就满足条件NOTEXISTS子查询中只需有结果，则外层就满足条件COUNT（）统计查询结果的记录（元组）条数这五个函数只能跟在后面，不能直接跟在其它短语后面MAX（）对某列（数值类型）求最大值MIN（）对某列（数值类型）求最小值AVG（）对某列（数值类型）求平均值SUM（）对某列（数值类型）求和[INNER]JOIN普通（内部）连接即只有满足条件的记录才出现在查询结果当中LEFTJOIN左连接即除满足条件的记录外，第一个表不满足条件的记录也出现在查询结果当中RIGHTJOIN右连接即除满足条件的记录外，第二个表不满足条件的记录都会出现在查询结果当中FULLJOIN全连接即除满足条件的记录外，第一个表和第二个表中不满足条件的记录都会出现在查询结果当中UNIONSQL查询中唯一的一个并运算，将两个SELECT查询语句的查询的结果合并成一个查询结果TOP显示前面的部分记录，必须跟ORDERBY连用。注意TOP必须书写在查询结果第一个字段的前面如：SELECTTOP2姓名，出生日期FROMXSINTOARRAY把查询结果存储到某个数组当中INTOCURSOR把查询结果存储到某个临时表当中INTOTABLE/DBF把查询结果存储到某个永久表（自由表）中TOFILE把查询结果存储到某个文本文件当中TOPRINTER把查询结果输出到打印机可以在3个区域中创建SELECT命令。1在命令窗口中；2在VFP程序中3在查询设计器中注：当FROM后面来自于多个表时，必须写连接条件（通常根据表的公共字段作为连接条件），此时在书写公共字段之前必须加以别名限定。定义表的别名方法见教材P137，超连接的格式见教材P140FROM书写不要写成=FORM二、查询方法：1、简单查询：例：从职工表中检索所有的工资值。SELECT工资FROM职工表或者SELECT*FROM职工表如果要去掉重复值用：DISTINCT例：检索哪些仓库有工资多于1260元的职工号SELECTDISTICT仓库号FROM职工表WHERE工资12602、单击的连接查询：类似于多个表的查询，与嵌套查询的区别是要查询的结果呆以出自多个表中。而嵌套查询的结果是基于一个表中。例：找出工资多于1250元的职工号与他们所在的城市。这里所要求检索的住处分别出自职工表和仓库表两个关系，这样的检索肯定是基于多个关系的，这类查询一般用连接查询来实现。SELECT职工号，城市FORM职工表，仓库表WHERE（工资1250）AND（职工表.仓库号=仓库表.仓库号）WHERE地址！=“北京”SELECT*FROM仓库表WHERENOT（地址=“北京”）四、排序：命令格式为：ORDERBY字段名asc/desc例：按职工表中的工资值升序查找出所有职工信息SELECT*FROM职工表ORDERBY工资注：ORDERBY是对最终结果进行排序，不能在子查询中使用。五、简单的计算查询：例：查询供应商表中所在地的数目；SELECTCOUNT（DISTINCT地址）FROM供应商表六、分组计算查询：格式为：GROUPBY字段名[HAVING条件]例：求每个仓库的职工的平均工资。SELECT仓库号，AVG（工资）FROM职工表GROUPBY仓库号注：GROUPBY短语一般跟在WHERE短语后面，若没有WHERE短语，就跟在FROM短语后面。另外，GROUPBY还可以根据多个属性进行分组，在分组查询时，有时要求查询满足条件的记录时，可以用HAVING短语来限定分组。七、利用空值查询：SQL支持NULL，所以也可以利用空值进行查询。NULL值具有以下特点：1不等价于没有任何值；2与0、空格和空字符串不同3排序时具有最大的优先权。4可以用于计算和大多数的函数中。5NULL值不改变变量或字段的数据类型。6NULL值会影响命令、函数、表达式的执行。例：查询已经确定供应商号的供应商信息：SELECT*FROM供应商表WHERE供应商号ISNOTNULL注：查询空值时使用ISNULL=NULL是无效的，空值是一个不确定的值，所以不能用等于号进行比较。八、自连接与别名查询：注：别名并不是必需的，在关系的自连接操作中，别名是必不可少的，SQL不仅可以以对多个表进行连接操作，还可以将同一关系与其自身进行连接，这种连接就称为自连接，可以进行自连接的关系，实际上它们之间存在着一种特殊的递归联系。1内外层相互关联的嵌套查询2使用量词和谓词的查询：3超连接查询：4集合并运算；并运算的格式：UNION[ALL]SELCTECommand一、VFP中SQLSELECT的几个特殊选项；1、显示部分结果：命令格式为：TOPnExpr[PERCENT]Top30注：（1）符号查询条件的所有记录中，选择指定数量或百分比的记录；（2）TOP子句必须与ORDERBY子句同时使用；当不使用PERCENT时，nExpr可以是1-32767之间的整数，显示查询结果的前几个记录；（3）当使用PERCENT时，nExpr是0.01-99.99之间的实数，显示查询结果的前百分之几个记录。例：显示工资最高的4位职工的信息。SELECT*TOP4FROM职工表ORDER工资DESC二、将查询结果放到数组中：命令格式为：INTOARRAYArrayName（数组变量名）可以将查询结果存放到二维数组中，每行对应一条记录。例：将查询到的AA信息存放在数组ABC中。SELECT*FROMAAINTOARRAYABC三、将查询结果放到临时文件中：命令格式为：INTOCURSORCursorName（临时文件名）例：将查询到的职工信息存放到临时文件ABC中。SELECT*FROM职工表INTOCURSORABC注：INTOCURSOR短语一般用于存放一些临时结果，当使用完后这些临时文件会自动删除。四、将查询结果存放到永久表中：命令格式为：INTODBF|TABLE表名例：将工资最高的那30%的职工信息存放到一个永久表中。SELECT*TOP30PERCENTFROM职工表INTOTABLEABCORDERBYDESC。五、将查询结果存放到文本文件中：命令格式为：TOFILE文件名例：将工资最高的那30%的职工信息存放到一文本文件AA中。SELECT*TOP30PERCENTFROM职工表ORDERBY工资DESCTOFILEABC数据的操作SQL数据操作：包括数据插入、更新和删除。一、插入记录：1、格式1：INSERTINTO表名[字段名表]VALUES（表达式1,表达式2，……）功能：在表中插入新记录：例：在供应商表关系中插入元组：（E4，S3，OR02，2002/01/22,13000）INSERTINTO供应商表VALUES(“E4”,”S3”,”OR02”,{^2002-01-22},13000)2、格式2：INSERTINTO表名FROMARRAY数组名功能：在表中插入新记录，数据来自于指定的数组名二、修改记录：命令格式：UPDATE表名SET被修改的字段名=表达式[WHERE条件]功能：用新的值去更新表中的记录例：将“选课”表中“学生号”为001号的学生成绩增加10分UPDATE选课SET成绩=成绩+10WHERE学生号=“001”注：一般使用WHERE子句指定条件，以更新满足条件的一些记录的字段值，并且一次可以更新多个字段；如果不使用WHERE子句，则更新全部记录，UPDATE命令只能用来更新单个表中的记录。三、删除记录：命令格式：DELETEFROM表名[WHERE条件]功能：逻辑删除表中记录例：要删除“选课”表中成绩小于60分的记录DELECTFROM选课WHERE成绩60注：FROM指定从哪个表中删除数据，WHERE指定被删除的记录所满足的条件，如果不使用WHERE子句，则删除该表中的全部记录。带有删除标记的记录在PACK命令之前并不从表中做物理删除，可以用RECALLFOR命令恢复带有删除标记的记录。注：这三个SQL语句对表操作的命令与第三章VF中对表操作命令的区别。四、数据定义：表的定义——创建表：命令格式：CREATETABLE|DBF表名（字段名类型(字段宽度),……）说明：1理解数据的说明；2理解创建方法；3撑握如下短语：PRIMARYKEY（建立主索引）；CHECK（设置规则）；ERROR（设置错误信息）；DEFAULT（默认值）；FREE（建立自由表；UNIQUE（建立候选索引）；NULL或NOTNULL（是否允许为NULL值）等例：用命令创建学生表。该表分别由字段学生号、姓名、性别、年龄构成，并且以学生为主索引，为年龄字段指定了有效性规则（年龄≥19）和显示的错误信息；CREATETABLE学生（学生号N（3）PRIMARYKEY，姓名C（3），性别L（1），年龄N（2）CHECK（年龄=19）ERROR“年龄必须满19岁”）五、表的删除：命令格式为：DROPTABLE表名功能：从磁盘上删除指定表文件注：执行了DROPTABLE语句之后，所有与被删除表有关的主索引、默认值、验证规则都将丢失。当前数据库中的其它表若与被删除的表有关联，这些规则和关系也都将无效。六、表结构的修改：命令格式为：ALTERTABLE有三种格式：注：第三种命令格式是对前两种命令格式的补充，可以更改字段名，也可以删除字段还可以定义，修改和删除表级别的有效性规则。1.增加字段Altertable表名add字段名类型（宽度）[check表达式[error’出错信息’]]Altertable表名add字