gaozhi第4章关系型数据.ppt

第4章 关系型数据库标准语言 SQL 内容提要 4.1 SQL概述 4.2 查询功能 4.3 操作功能 4.4 定义功能 4.5 数据控制 4.6 小型案例实训 4.7 本章小结 4.8 习题 4.1 SQL概述 SQL具有以下特点: SQL是一种一体化的语言，它包含数据定义、数据查询、数 据操纵和数据控制等方面的功能，可以完成数据库活动中的 全部工作。 SQL是一种高度非过程化的语言，它没有必要一步步地告诉 计算机“如何”去做，只需清楚用户要“做什么”，SQL就可以将 要求交给系统，自动完成全部 工作。 SQL非常简洁,灵活易用。 SQL可以直接以同一语法结构使用不同语言，以命令方式交 互使用，也可以嵌入到程序设计语言中以程序方式 使用。 面向集合的操作方式。 表4-1 SQL命令动词 SQL功能命令动词 数据查询 SELECT 数据定义 CREATE、DROP 、ALTER 数据操纵 INSERT、UPDATE、DELETE 数据控制 GRANT、REVOKE 4.2 查 询 功 能 4.2.1 创建SELECT命令 可以在以下3个区域中创建SELECT命令。 在命令窗口中。 在VFP程序中。 在查询设计器中。 界面 连接到SQLServer对话框 查询分析器工作界面 SQL语言的组成 SQL语言集数据定义、数据操纵、数据查询和数据 控制功能于一体。 数据定义是定义SQL模式、基本表、视图、索引等 结构； 数据操纵是数据插入、删除和修改等3种操作； 数据查询包括单表查询、多表查询、嵌套查询和连 接查询等； 数据控制是对基本表和视图的安全表授权、完整性 规则的设定、事务并发控制等内容。 数据定义语言 SQL对应关系数据库模式的三级模式结构，模式 、外模式和内模式的基本对象有基本表、视图和 索引。 SQL数据定义功能包括定义基本表、定义视图和 定义索引。 定义基本表（CREATE TABLE语句） CREATE TABLE （ 列级完整性约束条件 ， 列级完整性约束条件 ， ） SQL支持的数据类型 定义表的各个属性，需要指明其数据类型及长度 。 SQL支持以下数据类型： SMALLINT ：半字长二进制整数 INTEGER：全字长二进制整数 DECIMAL：压缩十进制数 FLOAT：双字长浮点数 CHAR（n）：长度为n的定长字符串 VARCHAR（n）：最大长度为n的变长字符串 例子： 定义STUDENT（学生） 基本表 CREATE TABLE STUDENT （SNO CHAR（7）NOT NULL， SNAME VARCHAR（8） NOT NULL ， SEX CHAR（2）NOT NULL， PRIMARY KEY（SNO） 修改基本表（ALTER TABLE语句 ） 修改基本表（ALTER TABLE语句） ALTER TABLE ADD DROP MODIFY ； 其中ALTER TABLE语句指定修改哪个基本表， ADD子句用于增加新列和新的完整性约束条件， DROP子句用于删除指定的完整性约束条件， MODIFY子句用于修改原有的列定义，包括列名 和数据类型。 删除基本表（DROP TABLE语句） DROP TABLE 索引的建立和删除 建立索引节省查询操作的时间，回忆查询速度的 有效手段，使用CREATE INDEX语句。 CREATE UNIQUECLUSTER INDEX ON （ , ）； 其中， 是要建立索引的基本表的名称， 索引可以建立在该表的一列或多列上，各列名之 间用逗号分隔。列名后面可用指定索引值 的排列次序，缺省值为ASC（升序）； UNIQUE 表明此索引的每一个索引值只对应唯一的数据记 录； CLUSTER表示要建立的索引是聚簇索引。 索引的建立和删除 例：按照学号升序建立唯一索引（次序升序，省 略） CREATE UNIQUE INDEX STUSNO ON STUDENT （SNO）； 删除索引，数据增加删改频繁，系统会花费许多 时间来维护索引，可以删除一些不必要的索引， 使用DROP INDEX语句。 格式 DROP INDEX 例： DROP INDEX Stusname； 删除索引，系统同时从数据字典中删去有关该索 引的描述。 视图-外模式 视图是从一个或几个基本表导出的表，是虚表 （不实际存储在数据库中） 格式： CREATE VIEW, AS 子查询 WITH CHECK OPTION 4.2.2 简单查询 简单查询是针对单个表的查询，由SELECT和FROM短语构成无条件查 询或由SELECT、FROM和WHERE短语构成条件查询。其基本格式 是： SELECT select_Item FROM DatabaseName 如果要去掉查询结果中的重复值，只需要指定DISTINCT短语： SELECT DISTINCT select_Item FROM DatabaseName 此外还可以用WHERE短语指定查询条件，查询条件可以是任意复杂 的逻辑表达式： SELECT select_Item FROM DatabaseName WHERE FilterCondition 例4.1 从zg关系中检索所有的工资值。 SELECT 工资 FROM zg 结果显示如图4-2所示。 图4-2 检索所有的工资值 例4.2 检索工资多于1220元的职工号。 SELECT 职工号 FROM zg WHERE 工资1220 结果显示如图4-3所示。 图4-3 检索工资结果 4.2.3 简单的连接查询 简单的连接查询是一类基于多个表的查询，与嵌套查询的区别是要查询的 结果可以出自多个表，而嵌套查询的结果是基于一个表。 一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有 用的信息。前面的查询都是针对一个表进行的。如果一个查询同时涉及两个 以上的表，则称为连接查询。其基本格式是： SELECT select_Item 1,select_Item 2 FROM DatabaseName 1,DatabaseName 2; WHERE JoinCondition 当FROM之后的多个关系中含有相同的属性名时，必须用关系前缀指明属性 所属的关系，“.”前面的是关系名，后面是属性名。 例4.4 找出工作在面积大于340的仓库的职工号和职工所在的城市。 SELECT 职工号,城市FROM ck,zg; WHERE(面积340)AND (ck.仓库号=zg.仓库号) 结果显示如图4-5所示。 4.2.4 嵌套查询 嵌套查询所要求的结果出自一个关系，但相关的条件却涉 及多个关系。例如，当检索关系X中的元组时，它的条件 依赖于相关的关系Y中的元组的属性值，这时使用SQL的 嵌套查询功能将非常方便。其格式为： SELECT select_Item 1 FROM DatabaseName 1 WHERE FilterCondition 1 IN; SELECT select_Item 2 FROM DatabaseName 2 WHERE FilterCondition 2) 例4.5 哪些城市至少有一个仓库的职工工资为1220元？ 这个查询可以使用如下的嵌套查询： SELECT 城市FROM ck WHERE 仓库号IN; (SELECT仓库号FROM zg WHERE工资=1220) 结果显示如图4-6所示。 4.2.5 几个特殊运算符 SQL SELECT有如下几个特殊运算符： BETWEENAND：在“和之间”，并且包括两 个边界值。 LIKE：字符串匹配运算符，通配符“%”表示0个或多 个字符，另外还有一个通配符“_”(下划线)表示一个 字符。 !=：在SQL中“不等于”用“!=”表示，另外还可以用否 定运算符NOT写出等价的命令。 例4.7 查询工资在12201270元之间的职工所有信息 。 SELECT *FROM zg WHERE 工资BETWEEN 1220 AND1270 4.2.6 排序 SQL SELECT命令可以将查询结果排序，实现该 排序的短语是ORDER BY。具体格式如下： ORDER BY Order_Item ASC | DESC,Order_Item ASC |DESC 根据列的数据对查询结果进行排序，每个Order_Item 都必须对应查询结果中的一列。 可以按升序(ASC)或降序(DESC)排序，可以按一列或 多列排序。 例4.9 按zg表中的工资值升序查找出所有职工信息。 SELECT*FROM zg ORDER BY工资 4.2.7 简单的计算查询 事实上，SQL不仅具有一般的检索能力，而且还有计算方 式的检索；SQL不仅可以查询表中数据，而且还可以计算 表中的数据。用于计算检索的函数有如下几个。 COUNT：计算列中选定项的数目，计算查询输出的行数 。 SUM：计算列中数值的和。 AVG：计算列中数值的平均值。 MAX：确定列中的最大值。 MIN：确定列中的最小值。 这些函数可以在SELECT短语中对查询结果进行计算。 例4.10 查询gys表中所在地的数目。 SELECT COUNT(DISTINCT 地址) FROM gys 4.2.9 利用空值查询 由于SQL支持NULL，所以可以利用空值进行查询。 NULL值具有以下特点。 等价于没有任何值。 与0、空格和空字符串不同。 排序时具有最大的优先权。 可以用于计算和大多数的函数中。 NULL值不改变变量或字段的数据类型。 NULL值会影响命令、函数、表达式的执行。 例4.16 查询已经确定供应商号的dgd信息。 SELECT* FROM dgd WHERE 供应商号 IS NOT NULL 4.2.15 Visual FoxPro中SQL SELECT的几个特殊选项 在符合查询条件的所有记录中，选择指定数量或百分比的记录，只显示前 几项记录。 (1) 使用TOP nExpr PERCENT 短语非常有用，其中nExpr是数字表达式， 当不使用PERCENT时，nExpr是132767间的整数，说明显示前几条记 录；当使用PERCENT时，nExpr是0.0199.99间的实数，说明显示结果 中前百分之几的记录。需要注意的是，TOP短语要与ORDER BY短语同时 使用才有效。 (2) 将查询结果存放到数组中 可以使用INTO ARRAY ArrayName短语将查询结果存放到数组中， ArrayName可以是任意的数组变量名。一般将存放查询结果的数组作为二 维数组来使用，每行一条记录，每列对应于查询结果的一列。查询结果存 放在数组中，可以非常方便地在程序中使用。 (3) 将查询结果存放在临时文件夹中 使用短语INTO CURSOR CursorName可以将查询结果存放到临时数据库 文件中，其中CursorName是临时文件名，该短语产生的临时文件是一个 只读的.dbf文件，当查询结束后该临时文件是当前文件，可以像一般的.dbf 文件一样使用(当然是只读)，当关闭文件时该文件将自动删除。 一般利用INTO CURSOR短语存放一些临时结果，比如一些 复杂的汇总可能需要分段完成，需要根据几个中间结果再汇 总等，这时利用该短语存放中间结果就非常合适。使用完后 这些临时文件会自动删除。 (4) 将查询结果存放到永久表中 使用短语INTO DBF | TABLE TableName可以将查询结果存 放到永久表中(.dbf文件)。 (5) 将查询结果存放到文本文件中 使用短语TO FILE FileName ADDITIVE 可以将查询结果存 放到文本文件中，其中FileName给出了文本文件名(默认是 .TXT)，如果使用ADDITIVE则结果将追加在原文件的尾部， 否则将覆盖原有文件。 (6) 将查询结果直接输出到打印机 使用短语TO PRINTER PROMPT 可以直接将查询结果输 出到打印机，如果使用了PROMPT选型，在开始打印之前会 弹出打印机设置对话框。 4.3 操 作 功 能 4.3.1 数据插入 Visual FoxPro支持两种SQL插入命令的格式，可以说第一种格式是标准 格式，第二种格式是Visual FoxPro的特殊格式。 第一种格式是： INSERT INTO dbf_name (fname1 , fname2, ) VALUES ( eExpression1 , eExpression2,) 命令说明： INSERT INTO dbf_name指定要追加记录的表名，dbf_name中可以包含 路径，也可以是一个名称表达式。 也可以用fname1,fname2,来指定新记录的值所对应的字段名。 VALUES(eExpression1,eExpression2,)指定新插入记录的字段值。 第二种格式是： INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR 命令说明： FROM ARRAY ArrayName指定一个数组，数组中的数据将被插入到新 记录中。 FROM MEMVAR把内存变量的内容插入到与其同名的字段中。插入新 记录后，指针指向新记录。 4.3.2 数据更新 SQL的数据更新命令UPDATE用于更新表中的记录。其格式如下： UPDATE TableName SET Column_Name1=eExpression1 , Column_Name2=eExpression2 WHERE Condition 命令说明： TableName指定要更新记录的表名。 SET Column_Name1=eExpression1 ,Column_Name1=eExpression2 指定要更新的列及这些列的新值。 WHERE Condition指定要更新记录的条件。 UPDATE命令的功能是修改指定表中满足WHERE子句条件的元组。其中 SET子句用于指定修改方法，即用的值取代相应的属性列值。 如果省略WHERE子句，则表示要修改表中的所有元组。 例如，将“选课”表中学生号为003号的学生成绩增加15分，应使用如下命令 ： UPDATE 选课 SET 成绩=成绩+15 WHERE 学生号=“003“ 4.3.3 数据删除 SQL从表中删除DELETE数据的命令格式如下： DELETE FROM TableName WHERE Condition 这里FROM指定从哪个表中删除数据，WHERE指定被删除的记录 所满足的条件，如果不使用WHERE子句，则删除该表中的全部记录 。 使用DELETE命令删除的记录并不是物理删除，只是在记录上打上 一个删除标记。如果要真正地从数据库中把相应的记录清除掉，还必 须用PACK命令。打上删除标记的记录可以通过RECALL命令恢复。 SQL中删除记录的格式如下： DELETE FROM TableName WHERE Condition 命令说明： TableName指定要给其中的记录加删除标记的表。 WHERE Condition指定给某些记录做删除标记的条件。 4.4 定 义 功 能 4.4.1 表的定义 在Visual FoxPro中可通过SQL语句的CREATE TABLE命令来建立表。 命令格式： CREATE TABLE | DBF TableName1 NAME LongTableName FREE (FieldName1 FieldType (nFieldWidth,nPrecision)NULL | NOT NULL CHECK lExpression1ERROR cMessageText1 DEFAULT eExpression1 PRIMARY KEY | UNIQUE REFERENCES TableName2 TAG TagName1 NOCPTRANS ,FieldName2 ,PRIMARY KEY eExpression2 TAG TagName2 |,UNIQUE eExpression3 TAG TagName3 ,FOREIGN KEY eExpression4 TAG TagName4 NODUP REFERENCES TableName3 TAG TagName5 ,CHECK lExpression2 ERROR cMessageText2) | FROM ARRAY ArrayName 4.4.2 表的删除 Visual FoxPro可以通过SQL的DROP TABLE命令来删除表。 命令格式： DROP TABLE TableName 命令说明： 执行DROP TABLE命令应在当前数据库下，否则将从磁盘上删除表文件 ，但该表在数据库文件中的信息并没有删除，以后会出现错误提示。若 要删除数据库中的表，最好先打开该数据库，在当前数据库中进行操作 。 TableName指定要移出并删除的表的名称。DROP TABLE直接从磁盘上 删除table_name所对应的.dbf文件。 如果table_name是数据库中的表并且相应的数据库是当前数据库，则从 数据库中删除了表；否则虽然从磁盘上删除了.dbf文件，但是在数据库 中(记录在.dbc文件中)的信息却没有删除，此后会出现错误提示。所以要 输出数据库中的表时，最好在数据库中进行操作，即数据库是当前打开 的数据库。 4.4.3 表结构的修改 在Visual FoxPro中可通过SQL的ALTER TABLE命令来修改表。 格式1： ALTER TABLE TableName1 ADD | ALTER COLUMN FieldName1 FieldType ( nFieldWidth , nPrecision ) NULL | NOT NULL CHECK lExpression1 ERROR cMessageText1 DEFAULT eExpression1 PRIMARY K