gaozhi第4章关系型数据.ppt

第4章关系型数据库标准语言SQL,内容提要,4.1SQL概述4.2查询功能4.3操作功能4.4定义功能4.5数据控制4.6小型案例实训4.7本章小结4.8习题,4.1SQL概述,SQL具有以下特点:SQL是一种一体化的语言，它包含数据定义、数据查询、数据操纵和数据控制等方面的功能，可以完成数据库活动中的全部工作。SQL是一种高度非过程化的语言，它没有必要一步步地告诉计算机“如何”去做，只需清楚用户要“做什么”，SQL就可以将要求交给系统，自动完成全部工作。SQL非常简洁,灵活易用。SQL可以直接以同一语法结构使用不同语言，以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。面向集合的操作方式。,表4-1SQL命令动词,4.2查询功能,4.2.1创建SELECT命令可以在以下3个区域中创建SELECT命令。在命令窗口中。在VFP程序中。在查询设计器中。,界面,连接到SQLServer对话框,查询分析器工作界面,SQL语言的组成,SQL语言集数据定义、数据操纵、数据查询和数据控制功能于一体。数据定义是定义SQL模式、基本表、视图、索引等结构；数据操纵是数据插入、删除和修改等3种操作；数据查询包括单表查询、多表查询、嵌套查询和连接查询等；数据控制是对基本表和视图的安全表授权、完整性规则的设定、事务并发控制等内容。,数据定义语言,SQL对应关系数据库模式的三级模式结构，模式、外模式和内模式的基本对象有基本表、视图和索引。SQL数据定义功能包括定义基本表、定义视图和定义索引。定义基本表（CREATETABLE语句）CREATETABLE（列级完整性约束条件，列级完整性约束条件，）,SQL支持的数据类型,定义表的各个属性，需要指明其数据类型及长度。SQL支持以下数据类型：SMALLINT：半字长二进制整数INTEGER：全字长二进制整数DECIMAL：压缩十进制数FLOAT：双字长浮点数CHAR（n）：长度为n的定长字符串VARCHAR（n）：最大长度为n的变长字符串,例子：定义STUDENT（学生）基本表,CREATETABLESTUDENT（SNOCHAR（7）NOTNULL，SNAMEVARCHAR（8）NOTNULL，SEXCHAR（2）NOTNULL，PRIMARYKEY（SNO）,修改基本表（ALTERTABLE语句）,修改基本表（ALTERTABLE语句）ALTERTABLEADDDROPMODIFY；其中ALTERTABLE语句指定修改哪个基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义，包括列名和数据类型。,删除基本表（DROPTABLE语句）,DROPTABLE,索引的建立和删除,建立索引节省查询操作的时间，回忆查询速度的有效手段，使用CREATEINDEX语句。CREATEUNIQUECLUSTERINDEXON（,）；其中，是要建立索引的基本表的名称，索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。列名后面可用指定索引值的排列次序，缺省值为ASC（升序）；UNIQUE表明此索引的每一个索引值只对应唯一的数据记录；CLUSTER表示要建立的索引是聚簇索引。,索引的建立和删除,例：按照学号升序建立唯一索引（次序升序，省略）CREATEUNIQUEINDEXSTUSNOONSTUDENT（SNO）；删除索引，数据增加删改频繁，系统会花费许多时间来维护索引，可以删除一些不必要的索引，使用DROPINDEX语句。格式DROPINDEX例：DROPINDEXStusname；删除索引，系统同时从数据字典中删去有关该索引的描述。,视图-外模式,视图是从一个或几个基本表导出的表，是虚表（不实际存储在数据库中）格式：CREATEVIEW,AS子查询WITHCHECKOPTION,4.2.2简单查询,简单查询是针对单个表的查询，由SELECT和FROM短语构成无条件查询或由SELECT、FROM和WHERE短语构成条件查询。其基本格式是：SELECTselect_ItemFROMDatabaseName如果要去掉查询结果中的重复值，只需要指定DISTINCT短语：SELECTDISTINCTselect_ItemFROMDatabaseName此外还可以用WHERE短语指定查询条件，查询条件可以是任意复杂的逻辑表达式：SELECTselect_ItemFROMDatabaseNameWHEREFilterCondition例4.1从zg关系中检索所有的工资值。SELECT工资FROMzg结果显示如图4-2所示。,图4-2检索所有的工资值,例4.2检索工资多于1220元的职工号。SELECT职工号FROMzgWHERE工资1220结果显示如图4-3所示。,4.2.3简单的连接查询,简单的连接查询是一类基于多个表的查询，与嵌套查询的区别是要查询的结果可以出自多个表，而嵌套查询的结果是基于一个表。一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。如果一个查询同时涉及两个以上的表，则称为连接查询。其基本格式是：SELECTselect_Item1,select_Item2FROMDatabaseName1,DatabaseName2;WHEREJoinCondition当FROM之后的多个关系中含有相同的属性名时，必须用关系前缀指明属性所属的关系，“.”前面的是关系名，后面是属性名。例4.4找出工作在面积大于340的仓库的职工号和职工所在的城市。SELECT职工号,城市FROMck,zg;WHERE(面积340)AND(ck.仓库号=zg.仓库号)结果显示如图4-5所示。,4.2.4嵌套查询,嵌套查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。例如，当检索关系X中的元组时，它的条件依赖于相关的关系Y中的元组的属性值，这时使用SQL的嵌套查询功能将非常方便。其格式为：SELECTselect_Item1FROMDatabaseName1WHEREFilterCondition1IN;SELECTselect_Item2FROMDatabaseName2WHEREFilterCondition2)例4.5哪些城市至少有一个仓库的职工工资为1220元？这个查询可以使用如下的嵌套查询：SELECT城市FROMckWHERE仓库号IN;(SELECT仓库号FROMzgWHERE工资=1220)结果显示如图4-6所示。,4.2.5几个特殊运算符,SQLSELECT有如下几个特殊运算符：BETWEENAND：在“和之间”，并且包括两个边界值。LIKE：字符串匹配运算符，通配符“%”表示0个或多个字符，另外还有一个通配符“_”(下划线)表示一个字符。!=：在SQL中“不等于”用“!=”表示，另外还可以用否定运算符NOT写出等价的命令。例4.7查询工资在12201270元之间的职工所有信息。SELECT*FROMzgWHERE工资BETWEEN1220AND1270,4.2.6排序,SQLSELECT命令可以将查询结果排序，实现该排序的短语是ORDERBY。具体格式如下：ORDERBYOrder_ItemASC|DESC,Order_ItemASC|DESC根据列的数据对查询结果进行排序，每个Order_Item都必须对应查询结果中的一列。可以按升序(ASC)或降序(DESC)排序，可以按一列或多列排序。例4.9按zg表中的工资值升序查找出所有职工信息。SELECT*FROMzgORDERBY工资,4.2.7简单的计算查询,事实上，SQL不仅具有一般的检索能力，而且还有计算方式的检索；SQL不仅可以查询表中数据，而且还可以计算表中的数据。用于计算检索的函数有如下几个。COUNT：计算列中选定项的数目，计算查询输出的行数。SUM：计算列中数值的和。AVG：计算列中数值的平均值。MAX：确定列中的最大值。MIN：确定列中的最小值。这些函数可以在SELECT短语中对查询结果进行计算。例4.10查询gys表中所在地的数目。SELECTCOUNT(DISTINCT地址)FROMgys,4.2.9利用空值查询,由于SQL支持NULL，所以可以利用空值进行查询。NULL值具有以下特点。等价于没有任何值。与0、空格和空字符串不同。排序时具有最大的优先权。可以用于计算和大多数的函数中。NULL值不改变变量或字段的数据类型。NULL值会影响命令、函数、表达式的执行。例4.16查询已经确定供应商号的dgd信息。SELECT*FROMdgdWHERE供应商号ISNOTNULL,4.2.15VisualFoxPro中SQLSELECT的几个特殊选项,在符合查询条件的所有记录中，选择指定数量或百分比的记录，只显示前几项记录。(1)使用TOPnExprPERCENT短语非常有用，其中nExpr是数字表达式，当不使用PERCENT时，nExpr是132767间的整数，说明显示前几条记录；当使用PERCENT时，nExpr是0.0199.99间的实数，说明显示结果中前百分之几的记录。需要注意的是，TOP短语要与ORDERBY短语同时使用才有效。(2)将查询结果存放到数组中可以使用INTOARRAYArrayName短语将查询结果存放到数组中，ArrayName可以是任意的数组变量名。一般将存放查询结果的数组作为二维数组来使用，每行一条记录，每列对应于查询结果的一列。查询结果存放在数组中，可以非常方便地在程序中使用。(3)将查询结果存放在临时文件夹中使用短语INTOCURSORCursorName可以将查询结果存放到临时数据库文件中，其中CursorName是临时文件名，该短语产生的临时文件是一个只读的.dbf文件，当查询结束后该临时文件是当前文件，可以像一般的.dbf文件一样使用(当然是只读)，当关闭文件时该文件将自动删除。,一般利用INTOCURSOR短语存放一些临时结果，比如一些复杂的汇总可能需要分段完成，需要根据几个中间结果再汇总等，这时利用该短语存放中间结果就非常合适。使用完后这些临时文件会自动删除。(4)将查询结果存放到永久表中使用短语INTODBF|TABLETableName可以将查询结果存放到永久表中(.dbf文件)。(5)将查询结果存放到文本文件中使用短语TOFILEFileNameADDITIVE可以将查询结果存放到文本文件中，其中FileName给出了文本文件名(默认是.TXT)，如果使用ADDITIVE则结果将追加在原文件的尾部，否则将覆盖原有文件。(6)将查询结果直接输出到打印机使用短语TOPRINTERPROMPT可以直接将查询结果输出到打印机，如果使用了PROMPT选型，在开始打印之前会弹出打印机设置对话框。,4.3操作功能,4.3.1数据插入VisualFoxPro支持两种SQL插入命令的格式，可以说第一种格式是标准格式，第二种格式是VisualFoxPro的特殊格式。第一种格式是：INSERTINTOdbf_name(fname1,fname2,)VALUES(eExpression1,eExpression2,)命令说明：INSERTINTOdbf_name指定要追加记录的表名，dbf_name中可以包含路径，也可以是一个名称表达式。也可以用fname1,fname2,来指定新记录的值所对应的字段名。VALUES(eExpression1,eExpression2,)指定新插入记录的字段值。第二种格式是：INSERTINTOdbf_nameFROMARRAYArrayName|FROMMEMVAR命令说明：FROMARRAYArrayName指定一个数组，数组中的数据将被插入到新记录中。FROMMEMVAR把内存变量的内容插入到与其同名的字段中。插入新记录后，指针指向新记录。,4.3.2数据更新,SQL的数据更新命令UPDATE用于更新表中的记录。其格式如下：UPDATETableNameSETColumn_Name1=eExpression1,Column_Name2=eExpression2WHERECondition命令说明：TableName指定要更新记录的表名。SETColumn_Name1=eExpression1,Column_Name1=eExpression2指定要更新的列及这些列的新值。WHERECondition指定要更新记录的条件。UPDATE命令的功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。例如，将“选课”表中学生号为003号的学生成绩增加15分，应使用如下命令：UPDATE选课SET成绩=成绩+15WHERE学生号=003“,4.3.3数据删除,SQL从表中删除DELETE数据的命令格式如下：DELETEFROMTableNameWHERECondition这里FROM指定从哪个表中删除数据，WHERE指定被删除的记录所满足的条件，如果不使用WHERE子句，则删除该表中的全部记录。使用DELETE命令删除的记录并不是物理删除，只是在记录上打上一个删除标记。如果要真正地从数据库中把相应的记录清除掉，还必须用PACK命令。打上删除标记的记录可以通过RECALL命令恢复。SQL中删除记录的格式如下：DELETEFROMTableNameWHERECondition命令说明：TableName指定要给其中的记录加删除标记的表。WHERECondition指定给某些记录做删除标记的条件。,4.4定义功能,4.4.1表的定义在VisualFoxPro中可通过SQL语句的CREATETABLE命令来建立表。命令格式：CREATETABLE|DBFTableName1NAMELongTableNameFREE(FieldName1FieldType(nFieldWidth,nPrecision)NULL|NOTNULLCHECKlExpression1ERRORcMessageText1DEFAULTeExpression1PRIMARYKEY|UNIQUEREFERENCESTableName2TAGTagName1NOCPTRANS,FieldName2,PRIMARYKEYeExpression2TAGTagName2|,UNIQUEeExpression3TAGTagName3,FOREIGNKEYeExpression4TAGTagName4NODUPREFERENCESTableName3TAGTagName5,CHECKlExpression2ERRORcMessageText2)|FROMARRAYArrayName,4.4.2表的删除,VisualFoxPro可以通过SQL的DROPTABLE命令来删除表。命令格式：DROPTABLETableName命令说明：执行DROPTABLE命令应在当前数据库下，否则将从磁盘上删除表文件，但该表在数据库文件中的信息并没有删除，以后会出现错误提示。若要删除数据库中的表，最好先打开该数据库，在当前数据库中进行操作。TableName指定要移出并删除的表的名称。DROPTABLE直接从磁盘上删除table_name所对应的.dbf文件。如果table_name是数据库中的表并且相应的数据库是当前数据库，则从数据库中删除了表；否则虽然从磁盘上删除了.dbf文件，但是在数据库中(记录在.dbc文件中)的信息却没有删除，此后会出现错误提示。所以要输出数据库中的表时，最好在数据库中进行操作，即数据库是当前打开的数据库。,4.4.3表结构的修改,在VisualFoxPro中可通过SQL的ALTERTABLE命令来修改表。格式1：ALTERTABLETableName1ADD|ALTERCOLUMNFieldName1FieldType(nFieldWidth,nPrecision)NULL|NOTNULLCHECKlExpression1ERRORcMessageText1DEFAULTeExpression1PRIMARYKEY|UNIQUEREFERENCETableName2TAGTagName1命令说明：该格式可以添加(ADD)新的字段或修改(ALTER)已有的字段，它的句法基本可以与CREATETABLE的句法相对应。从命令格式可以看出，该格式可以修改字段的类型、宽度、有效性规则、错误信息、默认值，定义主关键字和联系等。但是不能删除字段名，不能删除字段，也不能删除已经定义的规则等。,4.5数据控制,SQL中数据控制功能包括事务管理功能和数据保护功能，即数据库的恢复、并发控制；数据库的安全性和完整性控制。DBMS是保证某个用户对某类数据具有何种操作权利及保证权力的执行，其功能如下：把授权的决定告知系统，有GRANT和REVOKE语句完成。把授权的结果存入数据字典。当用户提出操作请求时，根据授权情况进行检查，以决定是否执行操作请求。,数据控制,SQL语言GRANT语句向用户授予操作权限，一般格式：GRANT,ONTOWITHGRANTOPTION例：GRANTSELECTONTABLESTUDENTTOU1,数据控制,SQL语言用REVOKE语句收回向用户授予操作权限，一般格式：REVOKE,ONFROM,；例：REVOKESELECTONTABLESTUDENTFROMU1,4.7本章小结,本章介绍了关系型数据库标准语言SQL,知识点如下(1)SQL的数据定义功能SQL定义、表的定义、表的删除、表结构的修改、视图的定义。(2)SQL的数据操作功能数据插入、数据删除、数据更新。(3)SQL的数据查询功能简单查询、简单的联接查询、嵌套查询、几个特殊运算符、简单的计算查询、分组与计算查询、别名与自联接查询、内外层互相关嵌套查询、使用量词和谓词的查询、超联接查询、集合的并运算。(4)SQL的数据控制,