VF关系数据库标准语言SQL

1、注：本课件授课内容中标注的页码与教程一致。“习题”来自教程，“练习”来自一本通第5章 结构化查询语言（SQL）SQL概述 P86同学们：SQL的内容在笔试和上机考试中均占到大约30%的比例，此外它还是查询和视图的基础，因此是学习的重点也是难点。在讲解本部分内容时，以VFP程序设计教程第5章为主，要求大家课后一定要结合例题解析进行复习。虽然SQL对大家来说有一定的难度，但是熟能生巧，大家把书中所涉及的题目和课件中的例题都理解并且操作了，相信SQL这个难关一定能攻克。SQL是结构化查询语言Structure Query Language的缩写。SQL包含了查询功能、数据定义、数据操纵和数据控制功能

2、，在VFP中没有提供数据控制功能。SQL主要特点：P861 SQL是一种一体化语言。2 SQL是一种高度非过程化的语言。3 SQL语言非常简洁。4 SQL语言可直接以命令方式交互使用，也可嵌入到程序设计语言中以程序方式使用。第四节 数据查询功能P94SELECT命令的特点：P941 可以自动打开数据库、表文件加以查询，而不需要事先用OPEN DATABASE或USE命令打开。2 可以直接选取数据表中的数据，而不需要事先用SET RELATION命令建立关联。3 当需要的索引文件不存在时，会自动建立暂存索引文件，以支持快速搜索技术（Rushmore）来查询。4 其查询结果可输出到文件、表、屏幕或

3、报表上，还可以转换成统计图表。命令格式：SELECT FROM WHERE P94可与 LIST FIELDS FOR 对照学习。关系操作：投影，选择，联接。说明：功能强大，语法灵活；要处理的数据表无须事先打开，通过FROM子句指明并打开。1 SELECT 短语：说明要查询的数据；对应的关系操作为投影，类似于FIELDS子句。2 FROM 短语：说明要查询的数据来自哪个或哪些表，可对单个表或多个表进行查询；3 WHERE 短语：说明查询条件；对应的关系操作为选择，类似于FOR子句。如是多表查询还可能过该子句指明联接条件，进行联接。4 GROUP BY 短语：用于对查询结果进行分组，可利用它进行

4、分组汇总；类似于TOTAL命令。5 HAVING 短语：跟随GROUP BY 使用，它用来限定分组必须满足的条件；6 ORDER BY 短语：用于对查询的结果进行排序；类似于SORT命令。示例数据库：学生管理，包括三个表：学生，课程，选课（通过该表反映“学生”与“课程”之间“多对多”的联系。）问题：请同学们分别指出三个表的主关键字。一、 简单查询 P95简单查询基于单个表。例：基本查询：1、列出学生信息SELE * FROM 学生 &&“*”号代表所有列。类似于：USE 学生LIST2、列出学生的学号，姓名条件查询：常用运算符P951、列出男生学生的学号、姓名及生日SELE 学

5、号,姓名,生日 FROM 学生 WHERE 性别=”男”类似于USE 学生LIST学号,姓名,生日 FOR性别=”男”2、列出学分大于5的课程的信息SELE * FROM 课程 WHERE 学分>5类似于USE 学生LIST FOR 学分>5习题：P95 例5-11，5-12练习：P92 11、13特殊运算符 P951 BETWEEN AND 意为“和之间”例：查询成绩在80分到90分之间的选课信息。SELE * FROM 选课 WHERE 成绩 BETWEEN 80 AND 90等价于：SELE * FROM 选课 WHERE 成绩>=80 AND 成绩<=90例：查

6、询成绩不在80分到90分之间的选课信息。SELE * FROM 选课 WHERE 成绩 NOT BETWEEN 80 AND 902 LIKE 是字符串匹配运算符，通配符“%”表示0个或多个字符，“_”表示一个字符。注意：不是“*”和“？”，不正确！例：从学生关系中查询姓“黄”的学生信息。SELE * FROM 学生 WHERE 姓名 LIKE “黄%”不能写成：SELE * FROM 学生 WHERE 姓名="黄%" 例：从学生关系中查询不姓“黄”的学生信息。SELE * FROM 学生 WHERE 姓名 NOT LIKE “黄%”例：SELE * FROM 学生 WHE

7、RE 姓名 LIKE "黄_"SELE * FROM 学生 WHERE 姓名 LIKE "黄_"3、IN：相当于集合运算符。例：列出湖南和湖北的学生SELE * FROM 学生 WHERE 籍贯 IN(“湖南”,”湖北”)练习：P92 9、10使用空值进行查询 空值的概念：空值就是缺值或还没有确定值，不能把它理解为任何意义的数据。比如表示价格的一个字段值，空值表示没有定价，而数值0可能表示免费。空值与空（或空白）字符串、数值0等具有不同的含义。例：假设在选课中有些学生某门课程还没有考试，则成绩为空。试找出尚未考试的选课信息。SELE * FROM 选课

8、WHERE 成绩 IS NULL注：不能写成“=NULL”例：试找出成绩不为空的选课信息。SELE * FROM 选课 WHERE 成绩 IS NOT NULL二、 联接查询 P97联接是关系的基本操作之一，联接查询基于多个关系的查询，有等值联接和非等值联接。例：查询男生学生的选课信息，包括姓名，学号及成绩。分析：本例的查询结果包括两个表“学生”与“选课”的属性，适用于联接查询。SELE 姓名,选课.学号,成绩; FROM 学生,选课;WHERE 学生.学号=选课.学号 AND 性别=”男” 说明：如果命令太长一行写不下可在行末加分号“：”表续行选课.学号： 因为两个表中均有“学号”字段，所以

9、必须指明所属表。学生.学号=选课.学号：连接条件。还可使用如下SQL语句：SELE 姓名,选课.学号,成绩;FROM 学生 JOIN 选课 ON 学生.学号=选课.学号;WHERE 性别=”男”说明：JOIN ON ：建立表与表之间的联接。 超联接查询 P99在两个表的超联接查询中，首先保证一个表中满足条件的元组都在结果中，然后将满足联接条件的元组与另一个表中的元组进行联接，不满足联接条件的则将应来自另一表的属性置为空值。语法：SELE FROM Talbel INNER|LEFT|RIGHT|FULL JOIN Table ON Join ConditionWHERE 说明：1 INNER

10、JOIN：等价于JOIN，为普通联接。2 ON Join Condition：用于指定联接条件。其余说明请同学们看书。例：比较几种联接方式。SELE 姓名,课程号,成绩 FROM 学生 JOIN 选课 ON 学生.学号=选课.学号SELE 姓名,课程号,成绩 FROM 学生 LEFT JOIN 选课 ON 学生.学号=选课.学号SELE 姓名,课程号,成绩 FROM 学生 RIGHT JOIN 选课 ON 学生.学号=选课.学号SELE 姓名,课程号,成绩 FROM 学生 FULL JOIN 选课 ON 学生.学号=选课.学号习题:P98 例5-14 5-15 5-16 5-17三、 嵌套查询

11、P98嵌套查询是另一类基于多个关系的查询，此类查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。例：查询女生学生选课信息，包括学号，课程号，成绩。SELE *;FROM 选课;WHERE 学号 IN (SELE 学号 FROM 学生 WHERE 性别=”女”)还可使用如下SQL语句：简单联接查询SELE 选课.学号,课程号,成绩;FROM 选课 JOIN 学生 ON 选课.学号=学生.学号;WHERE 性别=”女” 用量词和谓词的嵌套查询 P101前面已学过NOT IN运算符，此处还有两种与子查询相关的运算符。格式1：<表达式><比较运算符>ANY|ALL|SO

12、ME(子查询)格式2：NOT EXISTS (子查询)说明：1ANY、ALL和SOME为量词，ANY和SOME是同义词，在进行比较运算时只要子查询中有一行能使结果为真，则结果为真；ALL则要求子查询中的所有行都使结果为真时，结果才为真。2EXISTS为谓词，EXISTS和NOT EXISTS是用来检查在子查询中是否有结果返回，也就是存在元组或不存在元组。例：查询选修有课程的学生的学号，姓名，性别及生日。SELE * FROM 学生 WHERE EXIST (SELE * FROM 选课 WHERE 学生.学号=选课.学号)注：本例中内层查询引用了外层查询的表，只有这样使用谓EXISTS或NOT

13、 EXISTS才有意义。例：查询学分最高的课程信息。SELE * FROM 课程 WHERE 学分>=ALL (SELE 学分 FROM 课程)SELE * FROM 课程 WHERE NOT 学分<ANY (SELE 学分 FROM 课程)SELE * FROM 课程 WHERE NOT 学分<SOME (SELE 学分 FROM 课程)SELE * FROM 课程 WHERE 学分=(SELE MAX(学分) FROM 课程)例：查询学分不是最高的课程信息。SELE * FROM 课程 WHERE 学分<ANY (SELE 学分 FROM 课程)SELE * FRO

14、M 课程 WHERE 学分<SOME (SELE 学分 FROM 课程)习题：P100 例5-19 5-20练习：P91 6/P94 19四、 排序 P99SQL SELECT 可将查询结果排序。排序的短语格式如下：ORDER BY Order_Item ASC|DESC, Order_Item ASC|DESC说明：可按升序或降序排序，允许按一列或多列排序。例：按学分进行升序查询课程信息。SELE * FROM 课程 ORDER BY 学分 ASC例：先按学分进行升序排序，学分相同的再按课程名进行降序排序并输出全部课程信息。SELE * FROM 课程 ORDER BY 学分,课程名

15、DESC注：ORDER BY 对最终的查询结果进行排序，不能在子查询中使用此短语。习题：P102 例5-21 5-22练习：P94 20五、 简单计算查询 P101SQL不但具有一般的检索能力，而且还有计算方式的检索。用于计算检索的函数有：1 COUNT（*）：计数 自变量通常为数值型2 SUM（）：求和3 AVG（）：算平均值4 MAX（）：求最大值5 MIN（）：求最小值上面的函数可用在SELECT短语中对查询结果进行计算。例：查询学号为“03”的学生选修课程的考试成绩总分和平均分。SELE SUM(成绩),AVG(成绩) FROM 选课 WHERE 学号=”03”SELE SUM(成绩)

16、 总分,AVG(成绩) 平均分 FROM 选课 WHERE 学号=”03”SELE 姓名,SUM(成绩) AS 总分,AVG(成绩) AS 平均分;FROM 学生 JOIN 选课 ON 学生.学号=选课.学号;WHERE 学生.学号=”03”习题：P101 例5-23 5-24练习：P91 5、6 /P94 18六、 分组与计算查询 利用GROUP BY 子句进行分组计算查询使用得更加广泛。GROUP BY短语格式如下：GROUP BY GroupColumn,GroupColunmHAVING FilterCondition说明：1 可按一列或多列分组，还可以用HAVING进一步限定分组的条

17、件。2 GROUP BY子句一般跟在WHERE子句之后，没有WHERE子句时，跟在FROM子句之后；HAVING子句必须跟在GROUP BY 之后，不能单独使用。在查询中是先用WHERE子句限定元组，然后进行分组，最后再用HAVING子句限定分组。例：求每个学生选课的考试成绩平均分。SELE 学号,AVG(成绩) FROM 选课 GROUP BY 学号说明：在此查询中，选按学号属性进行分组，然后再计算每个学号的平均成绩。例：在选课表中求每个选课门数为4门的学生的总分和平均分。SELE 学号,SUM(成绩) AS 总分,AVG(成绩) AS 平均分;FROM 选课;GROUP BY 学号 HAV

18、ING COUNT(*)=4SELE 姓名,SUM(成绩) AS 总分,AVG(成绩) AS 平均分;FROM 学生 JOIN 选课 ON 学生.学号=选课.学号;WHERE 性别=”女”;GROUP BY 选课.学号 HAVING COUNT(*)=4例：求平均成绩在80分以上的各课程的课程号与平均成绩。SELE 课程号,AVG(成绩) FROM 选课; GROUP BY 课程号 HAVING AVG(成绩)>80习题：P102 例5-25练习：P91 7、12、14、15、16七、集合并运算 P103可将两个SELECT语句的查询结果通过并运算合并成一个查询结果。为进行并运算，要求两

19、个查询结果具有相同的字段个数，并且对应字段的值要出自同一值域，也就是具有相同的数据类型和取值范围。例：查询学号为01与02的选课信息。SELE * FROM 选课 WHERE 学号=”01”;UNION;SELE * FROM 选课 WHERE 学号=”02”习题：P103 例5-26八、VFP中有关SQL SELECT的几个特殊选项 P1031 显示部分结果格式：TOP n PERCENT功能：只需要满足条件的前几个记录。说明：（1） n是1至32767之间的整数，说明显示前几个记录。（2） 当使用PERCENT时，说明显示结果中前百分之几的记录。例：显示学分最低的前2项课程记录。SELE

20、* TOP 2 FROM 课程 ORDER BY 学分 DESC &&通常与ORDER BY子句连用习题：P145/46 P152/122 将结果存放在数组中 格式：INTO ARRAY ArrayName3 将结果存放在临时文件中格式：INTO CURSOR CursorName说明：临时表中一个只读的DBF文件，当查询结束后该临时文件是当前文件，可像一般的DBF文件一样使用，当关闭文件时该文件将自动删除。4 将结果存放在永久表中，通过该子句可实现表的复制。格式：INTO TABLE|DBF TableName5 将结果存放到文本文件中格式：TO ADDITIVE6 将结果直

21、接输出到打印机格式：TO PRINTER PROMPT习题：P103 例5-27SELECT语句的工作流程： 首先根据联接条件把几个表联接成一个临时表，然后根据WHERE中的查询条件进行过滤，过滤出来的结果根据分组条件分成一组一组，然后分别对这些组进行计算，最后又得出一个临时表，然后又根据HAVING中的条件对这个临时表再一次过滤，最后按指定的次序输出到指定的地方。它中间生成的临时表完全由系统自己创建、使用、删除，完全不受用户控制。上机练习：1、完成课件中所有实例，操作数据库：学生成绩管理2、完成教程上所有实例，操作数据库：图书管理说明：以上练习要求既会直接在命令窗口通过SQL语句完成，也要尝

22、试使用查询设计器来实现。如果在上机课上不能完成，可以在计算机课上完成，或者其他业余时间完成。请一定在下周上课前完成所有练习！第三节 数据操作功能 P92一、 插入 命令格式1：INSERT INTO 数据表名 (字段名1,字段名2,.) VALUES (表达式1,表达式2.)功能：在指定表尾添加一条新记录，其值为VALUES后面的表达式的值。命令格式2：INSERT INTO 数据表名 FROM ARRAY ArrayName|FROM MEMVAR功能：向指定表中添加一条新记录，其值来自于数组或对应的同名内存变量。例：向课程表中插入记录：（”05”,”数理逻辑”,”6”,”03”）INSER

23、T INTO 课程 VALUES(”05”,”数理逻辑”,6,”03”)说明：1 如未使用字段名指明，则VALUES中的表达式数目必须与表中的字段数相同，而且相应的数据类型必须一致。2 如果表中设定了主索引，则插入记录时只能用该SQL INSERT 命令而不能使用APPEND或INSERT命令。3 注意插入数据的数据类型必须与表设计时的类型一致。习题：P92 例5-7，5-8练习：P89 例9 P91 1，3，4二、 更新 命令格式：UPDATE TableName SET 字段名1=表达式1，字段名2=表达式2. WHERE Condition类似于REPLACE命令。一次只能在单一的表中更

24、新记录，通常嵌入在循环结构内，用另一个表的数据来修改本表。例：将课程关系中课程号为“01”的课程的学分加1分。UPDATE 课程 SET 学分=学分+1 WHERE 课程号=”01”习题：P93 例5-10练习：P88 例7，例15 P91 2操作：第三套 二（2）/三三、删除 P93命令格式：DELETE FROM TableName WHERE Condition说明：类似于VFP的DELE . FOR . 命令。1 若无WHERE子句会删除表中的全部记录。2 此命令为逻辑删除。用PACK命令进行物理删除。例：删除课程关系中课程号为“05”的元组。DELE FROM课程 WHERE 课程号

25、=”05”习题：P93 例5-9练习：P89 例10，例11，例12，例13，例14第二节 数据定义功能 P87一、表的定义 P87命令格式：CREATE TABLE 数据表名(字段名1 字段类型(字段宽度,小数位数) NULL|NOT NULLCHECK ERRORDEFAULTPRIMARY KEY|UNIQUE字段名2. .)说明：例：用SQL CREATE命令建立学生2数据表CREATE TABLE 学生2(学号C(2) NOT NULL PRIMARY KEY ,姓名 C(8),;性别 C(2) CHECK 性别=”男” OR 性别=”女” ERROR “性别只能是男或女” DEFA

26、ULT “男”,生日 D)习题：P88 例5-1，例5-2练习：P87 例4，例5，例6，例8例：第十套 一（3）（4）二（1）crea table golden(国家代码 C(3),金牌数 int,银牌数 int,铜牌数 int) &&答案有错insert into golden values("011",9,7,11)*可使用查询设计器来完成SELECT 国家.国家名称, count(获奖牌情况.名次) AS 金牌数; FROM 国家 INNER JOIN 获奖牌情况 ; ON 国家.国家代码 = 获奖牌情况.国家代码; WHERE 获奖牌情况.名次 =

27、 1; GROUP BY 国家.国家名称; ORDER BY 2 DESC, 国家.国家名称 DESC; INTO TABLE temp.dbf二、表的删除 P91命令格式：DROP TABLE TableName功能：直接从磁盘上删除指定的表，执行该命令前必须先打开相应的数据库。例：删除上例中的表“学生2”DROP TABLE 学生2习题：P91 例5-6三、 表结构的修改 一本通 P79命令格式1：ALTER TABLE TableName ADD|ALTER COLUMN 字段名1. 功能：添加（ADD）新的字段并同时设置数据表字段的有关属性；修改（ALTER）已有的字段类型、宽度、有效

28、性规则、错误信息、默认值、定义主关键字和联系等属性。但不能修改字段名，不能删除字段，也不能删除已经定义的规则等；它的句法基本与CREATE TABLE的句法相对应。例：为课程关系增加长度为6的字符型的先修课号属性。ALTER TABLE 课程 ADD 课程号 C(6)例：课程中的课程号属性的长度由6改为8。ALTER TABLE 课程 ALTER 课程号 C(8)习题：P90 例5-3 练习：P87 例1，例2，例3，命令格式2：ALTER TABLE 表名 ALTER COLUMN 字段名1 NULL|NOT NULLSET DEFAULT SET CHECK . ERROR.DROP DE

29、FAULTDROP CHECK说明：此格式主要用于定义、修改和删除字段一级的有效性规则和默认值定义。例：为课程关系增加学分大于2的有效性规则ALTER TABLE 课程 ALTER 学分 SET CHECK 学分>2习题：P90 例5-4命令格式3：ALTER TABLE 表名 DROP COLUMN字段名SET CHECK.ERROR.DROP CHECKRENAME COLUMN 字段名 TO 新字段名. . 说明：此格式可删除字段，可修改字段名，可定义、修改和删除表一级的有效性规则等。例：将课程表的先修课号字段名改为先修课。ALTER TABLE 课程 RENAME 先修课号 TO

30、 先修课例：删除课程表中的先修课字段 。ALTER TABLE 课程 DROP 先修课习题：P91 例5-5四、视图操作1定义视图命令格式：CREATE VIEW 视图名 AS select查询语句说明：视图是一个虚表，它一旦建立使用方法跟表十分类似，它不会形成单独的文件，它的内容保存在数据库中，所以自由表不能建立视图。这是视图与查询的一个区别。例：CREATE VIEW 学生成绩 AS ;SELE 姓名,SUM(成绩) AS 总分,AVG(成绩) AS 平均分;FROM 学生 JOIN 选课 ON 学生.学号=选课.学号;GROUP BY 选课.学号 USE 学生成绩BROWSELE * FROM 学生成绩2视图的删除命令格式：DROP VIE