第6章SQL语言以及查询和视图设计.ppt

1、第6章 SQL语言以及查询和视图设计,6.1 概述 1SQL语言简介 SQL：Structured Query Language的缩写，即结构化查询语言，是关系数据库的标准语言。所有的关系数据库管理系统都支持SQL。 SQL语言的主要特点： SQL语言非常简洁，功能很强。 SQL是一种一体化的语言。 SQL语言是高度非过程化的语言。 SQL语言既可以直接以命令方式交互使用,也可以嵌入到程序设计语言中使用。 VFP支持SQL的数据定义、数据查询和数据操纵功能。,SQL命令动词,2本章用例说明 “教学管理” 数据库和 “职工培训”数据库。 “职工培训”数据库包含如下四个表： 单位（单位号 C(6)

2、，单位名 C(10)，学分指标 I） 职工（职工号 C(4)，单位号 C(6)，性别 C(2)，工龄 N(3,0)） 成绩1（课程号 C(4)，职工号 C(4)，成绩 N(4,0)，考核时间 D） 课程1（课程号 C(4)，课程名 C(16)，学分 I） 四个表相互之间的联系是：单位-职工(1：N) 、职工-成绩1 (1：N)、课程1-成绩1 (1：N) 各表主关键字。,6.2.1 表的定义 较完整的SQL CREATE TABLE命令格式是： CREATE TABLE | DBF NAME FREE ( 类型(字段宽度，小数位数)NULL|NOT NULL CHECK ERROR DEFAU

3、LT PRIMARY KEY | UNIQUE REFERENCES TAG NOCPTRANS ,字段名2 ,PRIMARY KEY TAG ,UNIQUE TAG ，FOREIGN KEY TAG NODUP REFERENCES TAG ，CHECK ERROR ) |FROM ARRAY ,6.2 SQL的数据定义功能,例6-1 用命令建立“职工培训”数据库,然后用SQL CREATE命令建立“单位”表。 CREATE DATABASE 职工培训 CREATE TABLE 单位(单位号 C(6) PRIMARY KEY,单位名 C(10),; 学分指标 I CHECK(学分指标0) E

4、RROR 学分必须大于0!) PRIMARY KEY：说明“单位号”是主关键字(主索引)； CHECK：说明“学分指标”字段的有效性规则(学分指标0)； ERROR：指定当违反该有效性规则时的出错提示信息。,例6-2 用SQL CREATE命令建立“职工”表。 CREATE TABLE 职工( 职工号 C(4) PRIMARY KEY,单位号 C(6),; 性别 C(2) CHECK(性别=男 OR 性别=女) ERROR 性别只能是男或女! DEFAULT 男,工龄 N(3,0) CHECK(工龄=0) ERROR 工龄必须大于0!,; FOREIGN KEY 单位号 TAG 单位号 REF

5、ERENCES 单位) 其中：FOREIGN KEY 单位号 TAG 单位号 REFERENCES 单位表示要建立本表（职工表）与单位表的联系；其中“FOREIGN KEY 单位号”表示在本表单位号字段上建立普通索引，同时说明该字段是联接字段，“TAG 单位号 REFERENCES 单位”表示引用单位表的主索引“单位号”与单位表建立联系。,例6-3 用SQL CREATE命令建立“课程1”表和“成绩1”表。 创建“课程1”表： CREATE TABLE 课程1(课程号 C(4) PRIMARY KEY,课程名 C(16),学分 I) 创建“成绩1”表： CREATE TABLE 成绩1(课程号

6、 C(4),职工号 C(4),; 成绩 N(4,0) NULL DEFAULT .NULL.,考核时间D NULL DEFAULT .NULL.,; FOREIGN KEY 职工号 TAG 职工号 REFERENCES 职工,; FOREIGN KEY 课程号 TAG 课程号 REFERENCES 课程1) 小结：通过SQL CREATE命令不仅可以建立表，也可以建立起表之间的联系。在此基础上，可以利用第5章介绍的方法进行参照完整性设置，从而进一步完善数据库的设计。,SQL命令格式：DROP TABLE 功能：该命令直接从磁盘上删除指定的dbf文件。 说明：要删除数据库表时,最好应使所属数据库

7、是当前打开的数据库。才能在从磁盘上删除dbf文件的同时，从数据库中删除表。如果所属数据库不是当前数据库，则虽然从磁盘上删除了dbf文件，但记录在数据库dbc文件中的信息没有删除，以后会出现错误提示。,6.2.2 表的删除,6.2.3 表结构的修改,ADD 新字段名及属性,ALTER旧字名段,新属性 Set(drop) check Set(drop) default,DROP 字段名,ADD(DROP) PRIMARY KEY 主关键字 TAG 标识名 ADD(DROP) UNIQUE 候选关键字 TAG 标识名 ADD(DROP) FOREIGN KEY 外部关键字 RENAME 旧字段名 T

8、O 新字段名,ALTER TABLE 表名,例6-4 为“职工”表增加一个年龄字段。 ALTER TABLE 职工 ADD 年龄 I ; CHECK 年龄18 AND 年龄16 AND 年龄=70 ERROR 年龄超范围（16年龄70）！ 例6-7 删除“职工”表年龄字段的有效性规则。 ALTER TABLE 职工 ALTER 年龄 DROP CHECK,表结构的修改举例,例6-8 将“成绩1”表中的成绩字段名改为分数。 ALTER TABLE 成绩1 RENAME COLUMN 成绩 TO 分数 例6-9 删除“职工”表中的年龄字段。 ALTER TABLE 职工 DROP COLUMN 年

9、龄 例6-10 将“成绩1”表的课程号和职工号定义为候选索引(候选关键字),并使用索引标识名“职工课程”。 ALTER TABLE 成绩1 ADD UNIQUE 职工号+课程号 TAG 职工课程 如果定义为主索引，只需要把UNIQUE 改为PRIMARY KEY即可。 例6-11 删除“成绩1”表的表的候选索引“职工课程”。 ALTER TABLE 成绩1 DROP UNIQUE TAG 职工课程,主要包括数据的插入、更新和删除三个方面的操作。 6.3.1 插入 VFP支持两种格式的插入命令，格式1是标准格式，格式2是VFP的专有格式。 格式1：INSERT INTO (,) VALUES(,

10、) 格式2：INSERT INTO FROM ARRAY |FROM MEMVAR 其中： ：指定要插入记录的表。VALUES(,)给出具体插入的记录值。VALUES（）是必须的。若插入的不是完整的记录,应在表名字后用（）指定要插入值的字段。 格式2中，FROM ARRAY 表示要插入的记录值来自指定的数组；FROM MEMVAR表示要插入的记录值来自于与表中字段同名的内存变量，如果同名的变量不存在，那么相应的字段为默认值或空。,6.3 SQL的操纵功能,例6-12 用SQL的INSERT命令，在“单位”表中插入以下两个元组：(”100001”,”物理学院”,300)，(”100002”,50

11、0) 命令如下： INSERT INTO 单位 VALUES (100001, 物理学院,300) INSERT INTO 单位(单位号,学分指标) VALUES (100002, 500) 其中，插入的第二条记录必须指定要插入的字段名。,SQL命令格式： UPDATE SET =,字段名2=WHERE 一般使用WHERE子句指定条件，即满足条件的记录才被更新。一次可以更新多个字段。如果不使用WHERE子句,则更新全部记录。 例6-15 给“职工”表中所有职工增加2年工龄。 命令如下：UPDATE 职工 SET 工龄=工龄+2 如果仅给单位号是“100001”的职工加2年工龄，则命令为： UP

12、DATE 职工 SET 工龄=工龄+2 WHERE 单位号=100001,6.3.2 更新,SQL命令格式： DELETE FROM WHERE 这里FROM指定从哪个表中删除记录，WHERE指定被删除的记录应满足的条件，如果不使用WHERE子句，则删除该表中的全部记录。 例6-16 用 SQL的DELETE命令删除“职工”表中职工号是1001的元组。 命令如下： DELETE FROM 职工 WHERE 职工号=1001 注意：在VFP中SQL DELETE命令同样是逻辑删除记录，如果要物理删除记录需要使用PACK命令。,6.3.3 删除,6.4.1 SQL SELECT命令的格式 查询功能

13、是SQL语言的核心功能，其基本形式由SELECT-FROM-WHERE查询块组成，多个查询块可以嵌套执行。 VFP中SQL SELECT命令的格式如下： SELECT ALL|DISTINCTTOP PERCENT 别名.AS 列名1,别名. AS 列名2 FROM FORCE数据库名!AS INNER|LEFTOUTER|RIGHTOUTER|FULLOUTER JOIN 数据库名!AS ON INTO |TO FILE ADDITIVE|TO PRINTER PROMPT|TO SCREEN PREFERENCE NOCONSOLEPLAINNOWAIT WHERE AND AND | O

14、R AND | OR GROUP BY , HAVING UNION ALL ORDER BY ASC|DESC, ASC|DESC,6.4 SQL的查询功能,SQL SELECT命令格式中主要短语的含义如下： SELECT短语：说明查询的结果中要包含的数据。用“*”表示所有字段或指定具体字段数据；用ALL选项表示结果中可包括重复值的记录，DISTINCT选项表示结果中不能包括重复值的记录。缺省为ALL。 FROM短语：FROM短语与SELECT短语要同时使用，用来指定提供数据的表的名称。可以指定一个或多个表，多个表时用逗号分隔。数据库名和表名之间用！分隔。 WHERE短语：说明查询条件,即选

15、择元组的条件。 GROUP BY短语：用于对查询结果进行分组, 通常利用它进行分组求和。 HAVING短语：必须跟随GROUP BY使用，用于限定分组必须满足的条件。 ORDER BY短语：用来对查询的结果进行排序。缺省ORDER BY短语则按升序。,简单查询基于单个表，仅由SELECT和FROM短语构成无条件查询，或由SELECT、FROM和WHERE短语构成条件查询。 例6-17 从“职工”表中检索全体职工的工龄情况有多少种。命令为： SELECT DISTINCT 工龄 FROM 职工 其中，DISTINCT短语的作用是去掉查询结果中的重复值。 例6-18 检索“单位”表中的所有元组。

16、SELECT * FROM 单位 其中“*”是通配符，表示所有字段。,6.4.2 简单查询,例6-19 检索工龄高于14年的职工号及其工龄。 SELECT 职工号, 工龄 FROM 职工 WHERE 工龄14 其中，WHERE短语指定了查询条件。 例6-20 找出在“100001”或“100002”单位工作，并且工龄低于15年的职工号。 SELECT 职工号,工龄 FROM 职工; WHERE 工龄15 AND (单位号=100001 OR 单位号=100002),在WHERE子句所表达的条件中，除了可以使用关系运算符和逻辑运算符之外，还可以使用一些特殊运算符号，如BETWEENAND 、IN

17、和LIKE等。 例6-21 检索出学分指标在260300分范围的单位信息。 要查询值在什么范围之内，可以使用BETWEENAND,命令如下： SELECT * FROM 单位 WHERE 学分指标 BETWEEN 260 AND 300 例6-22 从“单位”表中检索出所有系的信息，不要学院或其它单位的信息。 命令如下：SELECT * FROM 单位 WHERE 单位名 LIKE %系 这里的LIKE是字符串匹配运算符，通配符“%”表示0个或多个字符,通配符“_”表示一个任意字符。,6.4.3 带特殊运算符的条件查询,例6-23 检索出除“数学学院”以外的其它单位的职工信息。 命令如下：SE

18、LECT * FROM 职工 WHERE 单位号!=100002 在SQL中,“不等于”用“!=”表示。在VFP中还可以用“”和“#”表示。另外还可以用否定运算符NOT写出与上述命令等价的命令： SELECT * FROM 职工 WHERE NOT(单位号=100002) NOT的应用范围很广，比如，NOT IN、NOT BETWEEN等。例如检索出学分指标不在260300分范围的单位信息： SELECT * FROM 单位 WHERE 学分指标 NOT BETWEEN 260 AND 300,用于指定排序的短语ORDER BY的格式如下： ORDER BY ASC|DESC, ASC|DES

19、C 可以按一列或多列排序，用ASC指定升序，DESC指定降序。默认为升序(ASC)。 例6-24 按“职工”表的工龄值的降序检索出全部职工信息。 SELECT * FROM 职工 ORDER BY 工龄 DESC 例6-25 检索出全部职工的信息，先按工龄升序排序，工龄相同则按职工号降序排序。 SELECT * FROM 职工 ORDER BY 工龄 , 职工号 DESC 注意：ORDER BY是对最终的查询结果进行排序，嵌套查询中的子查询不能使用该短语。,6.4.4 排序,联接查询是基于多个表的查询。 例6-26 找出工龄大于等于15年的职工以及他们所在的单位。 分析：要检索的信息分别出自职

20、工表和单位表，这是基于多个关系的查询，可以用联接查询来实现。命令如下： SELECT 职工号,工龄,单位名 FROM 职工,单位; WHERE (工龄=15) AND (职工.单位号=单位.单位号) 其中“职工.单位号=单位.单位号”是联接条件。“工龄=15”是选择条件。 说明： 1)当FROM之后有两个关系或更多关系时，各个关系相互之间肯定有一种联系，否则无法构成检索表达式。单位表和职工表之间是一对多的联系。 2)当FORM之后的多个关系中含有相同的属性名时，这时必须加表名作为属性名的前缀，以区别属性所属的关系。例如 “职工.单位号”，“单位.单位号”。,6.4.5 简单的联接查询,3)在一

21、个SELECT查询命令中，这种表名前缀出现较多时就会显得很麻烦，因此，SQL允许在FORM短语中为每个关系名或表名定义一个别名。上述命令可以写为： SELECT 职工号,工龄,单位名 FROM 职工 AS A,单位 AS B; WHERE (工龄=15) AND (A.单位号=B.单位号) 其中AS可以省略。 例6-27 找出“数学学院”工龄高于15年的职工。 SELECT 职工号,工龄,单位.单位号,单位名 FROM 职工,单位; WHERE (工龄15)AND(单位名=数学学院)AND(职工.单位号=单位.单位号) 注意：这里WHERE子句中， “职工.单位号=单位.单位号”是联接条件，其

22、它是选择条件。,例6-28 检索出“物理学院”全部职工选修课程的情况。 分析：该检索涉及到单位表、职工表、成绩1表和课程1表四个关系，表之间相互的联系必须用联接条件来表达，命令如下： SELECT A.单位号,A.单位名,B.职工号,C.课程号,D.课程名,C.成绩 FROM 单位 A,职工 B,成绩1 C,课程1 D;WHERE A.单位名=物理学院 AND A.单位号=B.单位号; AND B.职工号=C.职工号 AND C.课程号=D.课程号 当查询涉及多个关系时，联接条件的表达最好按表之间的联系顺序来书写。这里，可以理解成以单位表的单位号联系确定职工表中的单位号，得到职工号；再以职工表

23、的职工号联系确定成绩1表的职工号，得到课程号和成绩；再以成绩1表的课程号联系确定课程1表的课程号，并得到课程名。,1简单的嵌套查询 在一个SELECT命令的WHERE子句中，如果又出现另一个SELECT命令，则这样的查询称为嵌套查询。嵌套查询也是基于多个关系的查询。其查询的结果出自一个关系，但查询相关的条件却可能涉及多个关系。 例6-29 检索哪些单位有工龄高于14年的职工。 分析：这里要求查询单位表中的单位信息，而查询条件是职工表的工龄字段值。命令如下： SELECT 单位名 FROM 单位 WHERE 单位号 IN; (SELECT 单位号 FROM 职工 WHERE 工龄14) 可以看到

24、,该命令中含有两个SELECT-FROM-WHERE查询块，即内层查询（或子查询）块和外层查询块，内层查询检索到的单位号是100002和100004，因此可以写出等价的命令： SELECT 单位名 FROM 单位 WHERE 单位号 IN(100002,100004) 本例也可以用联接查询实现，命令如下： SELECT DISTINCT 单位名 FROM 职工,单位; WHERE (工龄14) AND (职工.单位号=单位.单位号),6.4.6 嵌套查询,例6-30 检索哪些单位所有职工的工龄均高于14年。 分析：本例的检索要求与前一例稍有区别，即要找出没有一个职工的工龄是低于或等于14年的单

25、位。命令如下： SELECT * FROM 单位 WHERE 单位号 NOT IN; (SELECT 单位号 FROM 职工 WHERE 工龄=14) 这里，内层查询块从职工表中找出所有职工的工龄低于或等于14年的单位号集合；然后从单位表中检索元组的单位号属性值不在该集合中的所有元组。 但是，这里的检索出现了错误，尽管在职工表中还没有“经济管理系”（单位号100005）的职工，但结果中出现了“经济管理系”的信息。 如果要排除那些在职工表中还没有职工信息的单位，命令如下： SELECT * FROM 单位 WHERE 单位号 NOT IN; (SELECT 单位号 FROM 职工 WHERE 工

26、龄=14); AND 单位号 IN (SELECT 单位号 FROM 职工),前面几个嵌套查询的例子都是外层查询依赖于内层查询的结果，内层查询与外层查询无关。但有时，内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。这就是所谓内、外层互相关的查询。 例6-31 列出每个职工所选修课程的最好成绩。 即每个职工可能学习了多门课程，要把每个职工的最高成绩找出来。命令如下： SELECT out.职工号,out.课程号,out.成绩,out.考核时间 FROM 成绩1 out WHERE out.成绩=; (SELECT MAX(inside.成绩) FROM 成绩1 inside

27、; WHERE out.职工号=inside.职工号) 该查询中，外层查询和内层查询使用同一个表，为了区别，在FROM子句中给它们分别指定别名out和inside。外层查询提供out关系中每个元组的职工号值给内层查询使用；内层查询利用一个职工号值，确定该职工所选学课程的最好成绩分数；随后外层查询再将out关系的同一元组的成绩值与该最好成绩值进行比较，如果相等，则该元组被选择。 MAX()函数将在下一小节介绍。,2内、外层互相关的嵌套查询,6.4.7 使用函数计算与分组查询,SQL SELECT命令还有计算方式的检索，利用系统提供的计算检索函数，可以对满足条件的记录进行计算和统计汇总等。 用于计

28、算检索的常用函数： MAX(表达式)求最大值 MIN(表达式)求最小值 AVG(表达式)计算平均值 COUNT(表达式)计数 SUM(表达式)求和 1简单计算查询 例6-32 找出在“100002”单位工作的职工的最高工龄。 SELECT MAX(工龄) FROM 职工 WHERE 单位号=100002 如果找该单位职工的最低工龄，则命令为： SELECT MIN(工龄) FROM 职工 WHERE 单位号=100002 例6-33 找出在“100002”单位工作的职工的平均工龄。 SELECT AVG(工龄) FROM 职工 WHERE 单位号=100002,例6-34 检索显示教学管理数据

29、库的“成绩”表中所有同学全部课程的平时、期中和期末成绩总分的平均分以及所选修的课程数。 SELECT AVG(平时成绩+期中成绩+期末成绩),; COUNT(DISTINCT 课程代号) FROM 成绩 说明： 1)函数参数不一定是单个属性，也可以是复杂的表达式。 2)在SELECT语句中，不但可以为关系指定别名,还可以使用AS（或省略AS）为查询结果中的各列单独指定显示的标题，使得显示标题含义更明了。 例如:SELECT AVG(平时成绩+期中成绩+期末成绩) AS 总平均,; COUNT(DISTINCT 课程代号) AS 课程数 FROM 成绩,例6-35 求所有职工的工龄都高于12年的

30、单位的平均学分指标。 SELECT AVG(学分指标) 平均指标 FROM 单位 WHERE 单位号 NOT IN (SELECT 单位号 FROM 职工 WHERE 工龄=12) 其中，“平均指标”是为显示列指定的标题，省略了AS。另外，该命令的计算结果包含了在职工表中还没有职工信息的单位，因此结果不准确。要排除这些单位，命令应改为： SELECT AVG(学分指标) 平均指标 FROM 单位 WHERE 单位号 NOT IN (SELECT 单位号 FROM 职工 WHERE 工龄=12) AND 单位号 IN (SELECT 单位号 FROM 职工),例6-36 找出已经被选课程的门数和

31、选修课程的人数。 SELECT COUNT(DISTINCT 课程号) 课程数,COUNT(*) 人次数 FROM 成绩1 注意，除非对关系中的元组个数进行计数，一般COUNT函数应该使用DISTINCT。 例6-37 求选修“办公自动化”这一课程的职工的成绩之和。 SELECT SUM(成绩) FROM 成绩1 WHERE 课程号 IN; (SELECT 课程号 FROM 课程1 WHERE 课程名=办公自动化),2分组与计算查询,如果需要对查询结果进行分组，而分组又通常要求有计算，这时利用GROUP BY子句。 GROUP BY短语的格式如下： GROUP BY , HAVING 可以按一

32、列或多列分组，还可以用HAVING短语进一步限定查询结果中的分组必须满足的条件。,例6-38 求各单位职工的平均工龄。 命令：SELECT 单位号,AVG(工龄) FROM 职工 GROUP BY 单位号 GROUP BY子句一般放在WHERE子句 或 放在FROM子句之后。 如果要检索满足一定条件的分组，就应使用HAVING子句。 例6-39 求至少有两个职工的单位的平均工龄。 SELECT 单位号,COUNT(*),AVG(工龄) FROM 职工; GROUP BY 单位号 HAVING COUNT(*)=2 HAVING子句总是跟在GROUP BY子句之后,不可以单独使用。,6.4.8

33、使用量词和谓词的查询,除与子查询有关的IN和NOT IN等运算符外还有两类和子查询有关的运算符，ANY、SOME、ALL与EXISTS、NOT EXISTS。它们有以下两种形式： ANY|ALL|SOME(子查询) NOT EXISTS(子查询) 其中：ANY、SOME和ALL是量词。而ANY和SOME是同义词，表示在进行比较运算时只要子查询中有一行能使结果为真，则结果就为真；ALL则要求子查询中的所有行都使结果为真时，结果才为真。 EXISTS是谓词。EXISTS或NOT EXISTS是用来检查在子查询中是否有结果返回，即存在元组或不存在元组。,例6-40 检索出那些在职工表中还没有职工的单

34、位信息。 要查询的是没有职工或不存在职工的单位，所以可以使用谓词NOT EXISTS： SELECT * FROM 单位 WHERE NOT EXISTS; (SELECT * FROM 职工 WHERE 单位号=单位.单位号) 以上的查询等价于： SELECT * FROM 单位 WHERE 单位号 NOT IN(SELECT 单位号 FROM 职工) 例6-41 检索那些至少已经有一个职工的单位信息。 SELECT * FROM 单位 WHERE EXISTS; (SELECT * FROM 职工 WHERE 单位号=单位.单位号) 它等价于： SELECT * FROM 单位 WHERE

35、 单位号 IN(SELECT 单位号 FROM 职工) 注意：NOT EXISTS只是判断子查询中是否有或没有结果返回，它本身并没有任何运算或比较。,例6-42 找出有职工工龄大于或等于“100002”单位中任何一名职工工龄的单位。 这个查询可以使用ANY或SOME量词。 SELECT DISTINCT 单位号 FROM 职工 WHERE 工龄=ANY; (SELECT 工龄 FROM 职工 WHERE 单位号=”100002”) 它等价于： SELECT DISTINCT 单位号 FROM 职工 WHERE 工龄=; (SELECT MIN(工龄) FROM 职工 WHERE 单位号=100

36、002) 例6-43 检索有职工的工龄大于或等于“100002”单位中所有职工工龄的单位号。 这个查询使用ALL量词。 SELECT DISTINCT 单位号 FROM 职工 WHERE 工龄=ALL; (SELECT 工龄 FROM 职工 WHERE 单位号=100002) 它等价于： SELECT DISTINCT 单位号 FROM 职工 WHERE 工龄=; (SELECT MAX(工龄) FROM 职工 WHERE 单位号=100002),6.4.9 自联接和超联接查询（选学）,1自联接查询 SQL不仅可以对多个关系实行联接操作，也可以将单个关系与其自身进行联接，这种联接称为自联接。

37、在可以进行自联接操作的关系中，具有来自同一值域的两个不同的属性，使该关系中的一些元组与该关系中另外一些元组有对应关系，也就是实体集内部存在联系，这种关系通常是一对多的联系。 例如，假设学员关系：学员(学号,姓名,组长学号)，则存在一个组长管理多个学员；又例如，假设零部件关系：零部件(零件号，名称，所属部件号) ，则存在一个部件包含多个组成零件。以零部件关系为例，其中零件号和所属部件号两个属性出自同一个值域，这两个属性值是包含关系，其中一个所属部件号对应多个零件号。 例6-44 根据零部件关系列出部件所包含的零件清单。 SELECT A.名称,包含,B.名称 FROM 零部件 A,零部件 B;

38、WHERE A.零件号=B.所属部件,2超联接查询,超联接运算符有“*=”和“=*”。 “*=”称为左联接，左联接的含义是检索第一个表中的所有记录和第二个表中与之对应的记录。对于第一个表中的每一个记录，如果第二个表中没有与之相匹配的记录，则在查询结果中第二个表相应行的属性值以Null代之； “=*”称为右联接右联接的含义是检索第二个表中的所有记录和第一个表中与之对应的记录。对于第二个表中的每一个记录，如果第一个表中没有与之相匹配的记录，则在查询结果中第一个表相应行的属性值以Null代之。 注意：VFP虽然支持超联接运算，但不能使用运算符“*=”和“=*”。VFP支持超联接查询有专有的联接运算语

39、法格式。在SELECT语句格式中，VFP用于联接运算有关的语法格式为： SELECT . FROM INNER |LEFT|RIGHT|FULL JOIN ON WHERE . 说明： 和分别指定第一个表和第二个表。, FROM短语中给出联接类型。有四种联接类型： INNER JOIN等价于JOIN，为普通的等值联接，只有满足联接条件的记录才出现在查询结果中。在VFP中称为内部联接。 LEFT JOIN为左联接。 RIGHT JOIN为右联接。 FULL JOIN称为全联接。相当左联接与右联接的合成。即两个表中的记录不管是否满足联接条件将都在查询结果中出现，相互不满足联接条件的记录相应部分以N

40、ULL代之。 ON 短语指定联接条件。 WHERE短语给出筛选条件。 例6-45 检索出单位及其所属职工的职工号和工龄信息。 用内部联接实现，即只有满足联接条件的记录才出现在查询结果中。 SELECT 单位.单位号,单位名,职工号,工龄; FROM 单位 INNER JOIN 职工; ON 单位.单位号=职工.单位号 注意：在超联接中，联接条件在ON短语中给出。,6.4.10 用空值查询（自学）,所谓空值是指NULL值。SQL支持空值，允许利用空值进行查询。 例6-48 找出“成绩1”表中选修了课程但还没有参加考核或者没有成绩值的记录。 SELECT * FROM 成绩1 WHERE 成绩 I

41、S NULL 例6-49 找出“成绩1”表中有成绩的记录。命令如下： SELECT * FROM 成绩1 WHERE 成绩 IS NOT NULL 注意：利用空值查询时要使用“IS NULL”或“IS NOT NULL”，若使用“=NULL”和“NULL”是无效的,因为空值不是一个确定的值,所以不能用“=”，“”等运算符进行比较。 例6-50 找出那些所有课程都在65分以上的职工的平均工龄。 SELECT AVG(工龄) FROM 职工 WHERE 职工号 NOT IN; (SELECT 职工号 FROM 成绩1 WHERE 成绩65 OR 成绩 IS NULL),6.4.11 集合的并运算（

42、自学）,SQL支持集合的并(UNION)运算，可以将两个SELECT语句的查询结果用UNION合并成一个查询结果。要进行并运算，两个查询的结果必须具有相同的字段个数，并且对应字段的值要出自同一个值域，即具有相同的数据类型和取值范围。 例如，检索出单位表中物理学院和数学学院的信息： SELECT * FROM 单位 WHERE 单位名=物理学院; UNION; SELECT * FROM 单位 WHERE 单位名=数学学院 显然，对于出自同一个表的数据，用以下命令更简单，等价的命令为： SELECT * FROM 单位 WHERE 单位名=物理学院 OR 单位名=数学学院,6.4.12 VFP的

43、SQL SELECT命令中几个特殊选项,1.显示部分结果：TOP PERCENT 需要注意的是TOP短语要与ORDER BY短语同时使用才有效。 例6-51 找出工龄最高的三位职工的信息。 SELECT * TOP 3 FROM 职工 ORDER BY 工龄 DESC 例6-52 显示20%职工的信息，他们的工龄是最短的。 SELECT * TOP 20 PERCENT FROM 职工 ORDER BY 工龄 2.将查询结果存放到数组中：INTO ARRAY 例6-53 如果把例6-51的查询结果存放在数组temp中，命令为： SELECT * TOP 3 FROM 职工 ORDER BY 工

44、龄 DESC INTO ARRAY temp,3.将查询结果存放在临时文件中:INTO CURSOR 例6-54 将查询到的职工信息存放在临时dbf文件temp中，并用BROWSE命令查看。 命令如下： SELECT * FROM 职工 INTO CURSOR temp BROWSE 4.将查询结果存放到永久表中:INTO DBF | TABLE 例6-55 将例6-52的查询结果存放在表LOWER.DBF文件中，命令如下： SELECT * TOP 20 PERCENT FROM 职工 ORDER BY 工龄 INTO TABLE LOWER,5.将查询结果存放到文本文件中:TO FILE

45、ADDITIVE 文本文件名的默认扩展名是txt。若使用ADDITIVE，则如果指定的文本文件已经存在时，将查询结果追加在原文件的尾部，否则将覆盖原有文件。 6.将查询结果直接输出到打印机的短语格式:TO PRINTER PROMPT 7.将查询结果显示在VFP主窗口或活动的用户自定义窗口:TO SCREEN 注意：前面2、3、4三个短语都是INTO短语，5、6、7是TO短语。如果TO短语和INTO短语同时使用，则TO短语将会被忽略。,1视图的概念 视图（VIEW）是在数据库表（非自由表）的基础上定义的一种特殊表，是实际并不存在的虚拟表。所谓虚拟，是因为视图的数据是从表或其它视图中抽取得来的，

46、视图的数据并没有在数据库中单独集中存储，而只是在数据库中保存视图的定义。视图一经定义，就成为数据库的组成部分，可以像表一样接受用户的查询。另外，没有数据库就没有视图。 视图又分为本地视图和远程视图。 视图是可更新的，利用视图可以修改或更新检索到的记录，并把更新结果发送回到源表中，使源数据表中的记录也随之修改，这是查询所没有的功能。,6.5 视图及视图的定义,视图定义的命令格式如下： CREATE SQL VIEW (,) AS 说明： 视图定义的核心部分是查询。其中可以是任意的SELECT查询语句，该子句决定视图中的数据来源。 当没有为视图指定字段名(列名)时，视图的字段名将与中指定的字段名或

47、表中的字段同名。 SQL表示SQL可以省略。 由于视图的数据是从表派生出来的，常把视图的源表称作基本表。 例6-56 定义以下视图：CREATE VIEW V1 AS; SELECT 职工号, 单位号,性别 FROM 职工 视图一经定义，就可以和基本表一样进行各种查询。 例如：SELECT * FROM V1或 SELECT 职工号,单位号,性别 FROM V1,2SQL视图的定义或创建,例6-57 前面的例6-31，要列出每个职工所选修课程的最好成绩，如果要求一般工作人员也键入那么复杂的命令，实在有点太强求了，我们可以先定义好视图：CREATE VIEW V2 AS; SELECT out.

48、职工号,out.课程号,out.成绩,out.考核时间; FROM 成绩1 out WHERE out.成绩=; (SELECT MAX(inside.成绩) FROM 成绩1 inside; WHERE out.职工号=inside.职工号) 此后，再提出同样的查询要求，就只需要输入以下命令即可： SELECT * FROM V2 例6-58 以下视图向用户提供职工号、职工的性别和职工所在单位信息： CREATE VIEW V3 AS; SELECT 职工号,性别,单位名 FROM 职工,单位; WHERE 职工.单位号=单位.单位号 该结果对用户就好像有一个包含字段职工号、性别和单位名的表

49、。使用起来十分方便。,在视图中还可以使用虚字段 一个视图的SELECT子句可以包含算术表达式或函数，这些表达式或函数与视图的其他字段看起来一样，但并不存储在表内，所以称为视图的虚字段。（注：虚字段名就是在查询语句中为列指定的显示标题） 例6-59 定义一个视图，包含教学管理数据库中成绩表的学号、课程号、平时、期中和期末成绩之外，还包含总评成绩。总评成绩按平时、期中和期末成绩的平均计算。 CREATE VIEW V_ZP AS; SELECT *,(平时成绩+期中成绩+期末成绩)/3 AS 总评 FROM 成绩 其中“总评”称为虚字段。然后,可使用查询命令: SELECT * FROM V_ZP

50、 ORDER BY 学号 4.视图的删除 视图是从表中派生出来的，不存在修改结构的问题，但是视图可以删除。 删除视图的命令格式是：DROP VIEW 例如，删除视图 V3，键入命令：DROP VIEW V3,6.6.1查询和视图设计的概念 查询设计（或设计查询）是指预先定义好一个SQL SELECT语句，并保存为查询文件（.QPR文件），该命令文件可以被反复执行来提取数据。 生成查询文件（.QPR文件）的方法： 1)可使用文本编辑器直接输入和编辑SQL SELECT语句，并保存为.QPR文件。 2)使用“查询向导”和“查询设计器”来设计，让系统自动生成.QPR查询文件。,6.6 查询设计与视图

51、设计,视图设计（或设计视图）则是指使用VFP提供的“视图向导”和“视图设计器”来设计，让系统在数据库中自动生成视图的定义。 查询和视图的主要差别在于： 1)视图是可更新的，视图可以更新数据并将更新结果发送回源表，而查询则不行。 2)查询文件（QPR文件）独立存在，该程序文件可以反复执行；视图不是独立文件，只是保存在数据库中的一种定义，可以作为查询的对象。 3)对查询结果可以定向输出，而视图不可以定向输出。查询的结果可以在窗口浏览，生成数据表、图形和报表等，而视图的结果不能直接实现这样的功能。,6.6.2 查询设计,1. 用“查询向导”设计查询 1)“查询向导”的启动 单击“文件”菜单，选择“新

52、建”命令，文件类型选中“查询”，单击“向导”按钮； 打开“教学管理”项目。 单击“工具”菜单，选择“向导”子菜单中的“查询”命令。 2）创建标准查询的过程 例：设计一个查询，查找“学1”表中专业为“计算机”，性别为“女”的所有同学的记录。注意：在“步骤3筛选记录”中构成以下两个条件表达式： 学1.专业=计算机与 学1.性别=女 两个条件要求同时满足是“与”关系，因此选中“与”单选按钮。,3）运行查询,在“项目管理器”中运行查询 如果查询不在“项目管理器”中，可以利用“项目管理器”的“添加”按钮，将查询添加到“项目管理器”中，然后再运行它。 使用“程序”菜单的“运行”命令运行查询 用DO命令运行

53、查询文件 可以在命令窗口直接输入命令来运行查询文件，例如： DO 查询成绩.QPR 其中，扩展名.QPR不能省略。,2.用“查询设计器”设计查询,1）启动“查询设计器” 在“项目管理器”“数据”选项卡中选择“查询”，单击“新建”按钮。 在“文件”菜单中选“新建”命令 在命令窗口中，直接输入以下命令：CREATE QUERY 启动查询设计器时，用户应首先添加查询的对象，即表或视图。,查询设计器窗口组成：,由顶部窗格和下部的选项卡组成，顶部的窗格显示被查询的表和表间的联接。连线表示表间的联接。在数据库中定义的表与表之间的联系在“查询设计器”中将成为默认的联接。,2）利用“查询设计器”设计查询,“查

54、询设计器”窗口中的基本操作：添加表、移去表 、“查询设计器工具栏”的显示与隐藏。 例6-60：设计一个查询，用于查看计算机专业学生选修了哪些课程，并将选修的课程名称和期末成绩显示出来。 分析：根据表结构可知，本任务涉及3个表,即“学1”表、“成绩”表、“课程”表。在“学1”表中有学生的姓名及专业信息；在“成绩”表中有学生学号和对应的选修课程代码；在“课程”表中有课程代码对应的课程名称。3个表分别以学号和课程代号建立联接。,3）查询文件(SQL语句)内容的查看,在“查询设计器”中，用户设置好有关内容后，系统自动生成一条SELECT-SQL命令，并将该命令以查询文件（.QPR文件）形式存储起来。要查看SQL语句，方法如下： 在项目管理器“数据”选项卡中选中某个查询； 单击“修改”按钮，打开“查询设计器”； 从“查询”菜单中选择“查看S