《gaozhi第4章关系型数据.ppt》由会员分享,可在线阅读,更多相关《gaozhi第4章关系型数据.ppt(37页珍藏版)》请在金锄头文库上搜索。
1、第第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可以直接以同一语法结构使用不同语言,以命令方式交互使用,也可以
2、嵌入到程序设计语言中以程序方式 使用。面向集合的操作方式。表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、入、删除和修改等3种操作;数据查询包括单表查询、多表查询、嵌套查询和连接查询等;数据控制是对基本表和视图的安全表授权、完整性规则的设定、事务并发控制等内容。数据定义语言SQL对应关系数据库模式的三级模式结构,模式、外模式和内模式的基本对象有基本表、视图和索引。SQL数据定义功能包括定义基本表、定义视图和定义索引。定义基本表(CREATE TABLE语句)CREATE TABLE ( 列级完整性约束条件 , 列级完整性约束条件 , )SQL支持的数据类型定义表的各个属性,需要指明其数据类型及长度。 SQL支持以下数据类型:SMALLINT :半字长二进制整数INTEGER:全字长二进制整数DEC
4、IMAL:压缩十进制数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子句用于增加新列和新的
5、完整性约束条件,DROP子句用于删除指定的完整性约束条件,MODIFY子句用于修改原有的列定义,包括列名和数据类型。删除基本表(DROP TABLE语句)DROP TABLE索引的建立和删除建立索引节省查询操作的时间,回忆查询速度的有效手段,使用CREATE INDEX语句。CREATE UNIQUECLUSTER INDEX ON ( , );其中, 是要建立索引的基本表的名称,索引可以建立在该表的一列或多列上,各列名之间用逗号分隔。列名后面可用指定索引值的排列次序,缺省值为ASC(升序); UNIQUE表明此索引的每一个索引值只对应唯一的数据记录; CLUSTER表示要建立的索引是聚簇索引
6、。索引的建立和删除例:按照学号升序建立唯一索引(次序升序,省略)CREATE UNIQUE INDEX STUSNO ON STUDENT (SNO);删除索引,数据增加删改频繁,系统会花费许多时间来维护索引,可以删除一些不必要的索引,使用DROP INDEX语句。格式 DROP INDEX例: DROP INDEX Stusname;删除索引,系统同时从数据字典中删去有关该索引的描述。视图-外模式视图是从一个或几个基本表导出的表,是虚表(不实际存储在数据库中)格式: CREATE VIEW, AS 子查询 WITH CHECK OPTION4.2.2 简单查询简单查询是针对单个表的查询,由S
7、ELECT和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 工资 FR
8、OM 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 F
9、ROM 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的
10、嵌套查询功能将非常方便。其格式为: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有如下几个特殊运
11、算符:BETWEENAND:在“和之间”,并且包括两个边界值。LIKE:字符串匹配运算符,通配符“%”表示0个或多个字符,另外还有一个通配符“_”(下划线)表示一个字符。!=:在SQL中“不等于”用“!=”表示,另外还可以用否定运算符NOT写出等价的命令。例例4.7 查询工资在12201270元之间的职工所有信息。SELECT *FROM zg WHERE 工资BETWEEN 1220 AND12704.2.6 排序 SQL SELECT命令可以将查询结果排序,实现该排序的短语是ORDER BY。具体格式如下:ORDER BY Order_Item ASC | DESC,Order_Item
12、ASC |DESC 根据列的数据对查询结果进行排序,每个Order_Item都必须对应查询结果中的一列。 可以按升序(ASC)或降序(DESC)排序,可以按一列或多列排序。 例例4.9 按zg表中的工资值升序查找出所有职工信息。 SELECT*FROM zg ORDER BY工资4.2.7 简单的计算查询 事实上,SQL不仅具有一般的检索能力,而且还有计算方式的检索;SQL不仅可以查询表中数据,而且还可以计算表中的数据。用于计算检索的函数有如下几个。COUNT:计算列中选定项的数目,计算查询输出的行数。SUM:计算列中数值的和。AVG:计算列中数值的平均值。MAX:确定列中的最大值。MIN:确
13、定列中的最小值。这些函数可以在SELECT短语中对查询结果进行计算。例例4.10 查询gys表中所在地的数目。SELECT COUNT(DISTINCT 地址) FROM gys4.2.9 利用空值查询由于SQL支持NULL,所以可以利用空值进行查询。NULL值具有以下特点。等价于没有任何值。与0、空格和空字符串不同。排序时具有最大的优先权。可以用于计算和大多数的函数中。NULL值不改变变量或字段的数据类型。NULL值会影响命令、函数、表达式的执行。例例4.16 查询已经确定供应商号的dgd信息。SELECT* FROM dgd WHERE 供应商号 IS NOT NULL4.2.15 Vis
14、ual FoxPro中SQL SELECT的几个特殊选项 在符合查询条件的所有记录中,选择指定数量或百分比的记录,只显示前几项记录。(1) 使用TOP nExpr PERCENT 短语非常有用,其中nExpr是数字表达式,当不使用PERCENT时,nExpr是132767间的整数,说明显示前几条记录;当使用PERCENT时,nExpr是0.0199.99间的实数,说明显示结果中前百分之几的记录。需要注意的是,TOP短语要与ORDER BY短语同时使用才有效。(2) 将查询结果存放到数组中 可以使用INTO ARRAY ArrayName短语将查询结果存放到数组中,ArrayName可以是任意的
15、数组变量名。一般将存放查询结果的数组作为二维数组来使用,每行一条记录,每列对应于查询结果的一列。查询结果存放在数组中,可以非常方便地在程序中使用。(3) 将查询结果存放在临时文件夹中 使用短语INTO CURSOR CursorName可以将查询结果存放到临时数据库文件中,其中CursorName是临时文件名,该短语产生的临时文件是一个只读的.dbf文件,当查询结束后该临时文件是当前文件,可以像一般的.dbf文件一样使用(当然是只读),当关闭文件时该文件将自动删除。 一般利用INTO CURSOR短语存放一些临时结果,比如一些复杂的汇总可能需要分段完成,需要根据几个中间结果再汇总等,这时利用该
16、短语存放中间结果就非常合适。使用完后这些临时文件会自动删除。(4) 将查询结果存放到永久表中 使用短语INTO DBF | TABLE TableName可以将查询结果存放到永久表中(.dbf文件)。(5) 将查询结果存放到文本文件中 使用短语TO FILE FileName ADDITIVE 可以将查询结果存放到文本文件中,其中FileName给出了文本文件名(默认是.TXT),如果使用ADDITIVE则结果将追加在原文件的尾部,否则将覆盖原有文件。(6) 将查询结果直接输出到打印机 使用短语TO PRINTER PROMPT 可以直接将查询结果输出到打印机,如果使用了PROMPT选型,在开
17、始打印之前会弹出打印机设置对话框。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(eEx
18、pression1,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=eE
19、xpression2 WHERE Condition命令说明:TableName指定要更新记录的表名。SET Column_Name1=eExpression1 ,Column_Name1=eExpression2 指定要更新的列及这些列的新值。WHERE Condition指定要更新记录的条件。 UPDATE命令的功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法,即用的值取代相应的属性列值。如果省略WHERE子句,则表示要修改表中的所有元组。例如,将“选课”表中学生号为003号的学生成绩增加15分,应使用如下命令:UPDATE 选课 SET 成绩=成绩+15 W
20、HERE 学生号=003“4.3.3 数据删除 SQL从表中删除DELETE数据的命令格式如下: DELETE FROM TableName WHERE Condition 这里FROM指定从哪个表中删除数据,WHERE指定被删除的记录所满足的条件,如果不使用WHERE子句,则删除该表中的全部记录。 使用DELETE命令删除的记录并不是物理删除,只是在记录上打上一个删除标记。如果要真正地从数据库中把相应的记录清除掉,还必须用PACK命令。打上删除标记的记录可以通过RECALL命令恢复。SQL中删除记录的格式如下:DELETE FROM TableName WHERE Condition命令说明
21、: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 NULLCHECK lExpression1ERROR cMessageText1DEFAULT eExpression1PR
22、IMARY KEY | UNIQUEREFERENCES TableName2 TAG TagName1NOCPTRANS,FieldName2,PRIMARY KEY eExpression2 TAG TagName2|,UNIQUE eExpression3 TAG TagName3,FOREIGN KEY eExpression4 TAG TagName4 NODUPREFERENCES TableName3 TAG TagName5,CHECK lExpression2 ERROR cMessageText2)| FROM ARRAY ArrayName4.4.2 表的删除Visual
23、 FoxPro可以通过SQL的DROP TABLE命令来删除表。命令格式:DROP TABLE TableName 命令说明:执行DROP TABLE命令应在当前数据库下,否则将从磁盘上删除表文件,但该表在数据库文件中的信息并没有删除,以后会出现错误提示。若要删除数据库中的表,最好先打开该数据库,在当前数据库中进行操作。TableName指定要移出并删除的表的名称。DROP TABLE直接从磁盘上删除table_name所对应的.dbf文件。如果table_name是数据库中的表并且相应的数据库是当前数据库,则从数据库中删除了表;否则虽然从磁盘上删除了.dbf文件,但是在数据库中(记录在.db
24、c文件中)的信息却没有删除,此后会出现错误提示。所以要输出数据库中的表时,最好在数据库中进行操作,即数据库是当前打开的数据库。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 KEY
25、| UNIQUE REFERENCE TableName2 TAG TagName1 l命令说明:该格式可以添加(ADD)新的字段或修改(ALTER)已有的字段,它的句法基本可以与CREATE TABLE的句法相对应。l从命令格式可以看出,该格式可以修改字段的类型、宽度、有效性规则、错误信息、默认值,定义主关键字和联系等。但是不能删除字段名,不能删除字段,也不能删除已经定义的规则等。4.5 数据控制SQL中数据控制功能包括事务管理功能和数据保护功能,即数据库的恢复、并发控制;数据库的安全性和完整性控制。DBMS是保证某个用户对某类数据具有何种操作权利及保证权力的执行,其功能如下:把授权的决定告
26、知系统,有GRANT和REVOKE语句完成。把授权的结果存入数据字典。当用户提出操作请求时,根据授权情况进行检查,以决定是否执行操作请求。数据控制SQL语言GRANT语句向用户授予操作权限,一般格式:GRANT , ON TO WITH GRANT OPTION例: GRANT SELECT ON TABLE STUDENT TO U1数据控制SQL语言用REVOKE语句收回向用户授予操作权限,一般格式:REVOKE , ON FROM , ;例: REVOKE SELECT ON TABLE STUDENT FROM U14.7 本 章 小 结 本章介绍了关系型数据库标准语言本章介绍了关系型
27、数据库标准语言SQL,知识点如下知识点如下(1) SQL的数据定义功能的数据定义功能SQL定义、表的定义、表的删除、表结构的修改、视图的定义、表的定义、表的删除、表结构的修改、视图的定义。定义。(2) SQL的数据操作功能的数据操作功能数据插入、数据删除、数据更新。数据插入、数据删除、数据更新。(3) SQL的数据查询功能的数据查询功能简单查询、简单的联接查询、嵌套查询、几个特殊运算符、简单查询、简单的联接查询、嵌套查询、几个特殊运算符、简单的计算查询、简单的计算查询、分组与计算查询、别名与自联接查询、分组与计算查询、别名与自联接查询、内外层互相关嵌套查询、使用量词和谓词的查询、超联接内外层互相关嵌套查询、使用量词和谓词的查询、超联接查询、集合的并运算。查询、集合的并运算。(4) SQL的数据控制的数据控制