visual foxpro程序设计案例教程vf按章(1)

1、第第4 4章章 关系数据库标准语言关系数据库标准语言SQLSQL 教学目标：教学目标：学会使用学会使用SQL语言。语言。教学要求：教学要求：了解了解SQL特点，主要功能，掌特点，主要功能，掌握数据定义、查询、操纵功能。握数据定义、查询、操纵功能。教学重点与难点：教学重点与难点：查询、嵌套查询。查询、嵌套查询。4.1 SQL4.1 SQL概述概述 SQLSQL是是19741974年由年由Boyce ChamberlinBoyce Chamberlin提出。提出。19791979年年IBMIBM公司首次在其关系数据库系统公司首次在其关系数据库系统System RSystem R上实现了上实现了SQ

2、LSQL。19861986年年1010月由美国国家标准局（简称月由美国国家标准局（简称ANSIANSI）的数的数据委员会据委员会X3H2X3H2批准了批准了SQLSQL作为关系数据库语言的美作为关系数据库语言的美国国家标准，同年公布了国国家标准，同年公布了SQLSQL标准文本（简称标准文本（简称SQLSQL8686）。）。19871987年国际标准化组织（简称年国际标准化组织（简称ISOISO）也通也通过了这一标准。此后过了这一标准。此后ANSIANSI不断修改和完善不断修改和完善SQLSQL标准，标准，并于并于19891989年公布了年公布了SQLSQL8989标准，标准，19921992年

3、又公布了年又公布了SQLSQL9292标准。标准。SQLSQL9292标准将其内容分为三个级标准将其内容分为三个级别，即基本级、标准级和完全级。尽管如此，别，即基本级、标准级和完全级。尽管如此，SQL-92SQL-92标准也不够完备，且正在进行修改。标准也不够完备，且正在进行修改。 4.1.1 SQL4.1.1 SQL的特点的特点 1 1、一体化语言。、一体化语言。2 2、高度非过程化。、高度非过程化。3 3、语言简洁，易学易用。、语言简洁，易学易用。4 4、统一的语法结构对待不同的工作方式。、统一的语法结构对待不同的工作方式。SQL功能功能命令动词命令动词数据查询数据查询SELECT数据定义

4、数据定义CREATE、DROP、ALTER数据操作数据操作INSERT、UPDATE、DELETE数据控制数据控制GRANT、REVOKE4.1.2 SQL4.1.2 SQL的主要功能的主要功能 SQLSQL是一种介于关系代数与关系演算之间的结构化是一种介于关系代数与关系演算之间的结构化查询语言，其功能并不仅仅是查询，还包括数据查询语言，其功能并不仅仅是查询，还包括数据定义、数据操纵和数据控制。定义、数据操纵和数据控制。SQLSQL语言是一个通用语言是一个通用的、功能极强的关系数据库语言。的、功能极强的关系数据库语言。1 1、数据定义功能、数据定义功能2 2、数据操纵功能、数据操纵功能3 3、

5、数据控制功能、数据控制功能VFPVFP在在SQLSQL方面支持数据定义、数据查询和数据操方面支持数据定义、数据查询和数据操纵功能，但在具体实现方面也存在一些差异。另纵功能，但在具体实现方面也存在一些差异。另外，由于外，由于VFPVFP自身在安全控制方面的缺陷，所以它自身在安全控制方面的缺陷，所以它没有提供数据控制功能。没有提供数据控制功能。 4.2 SQL4.2 SQL的数据定义功能的数据定义功能 标准标准SQLSQL的数据定义功能非常广泛，一般包括数据库的定义、的数据定义功能非常广泛，一般包括数据库的定义、表的定义、视图的定义、存储过程的定义、规则的定义和索表的定义、视图的定义、存储过程的定

6、义、规则的定义和索引的定义等若干部分。引的定义等若干部分。 5.2.1 表的定义及维护表的定义及维护1 1、表结构的定义、表结构的定义语句格式：语句格式：CREATE TABLECREATE TABLE ( ( (,小数位数小数位数)列级完整性的列级完整性的约束条件约束条件 ,列级完整性的约束条件列级完整性的约束条件,nn ， ,n );n );说说 明：明：(1)(1)SQLSQL支持的数据类型支持的数据类型 不同的数据库系统支持的数据类型不完全相同。不同的数据库系统支持的数据类型不完全相同。使用最多的是字符型数据和数值数据。使用最多的是字符型数据和数值数据。(2)(2) 省略为自由表，否则

7、为数据库中表。省略为自由表，否则为数据库中表。(3)(3) 为新建表的表名。为新建表的表名。(4)(4) 数据类型数据类型( (字段宽度字段宽度, ,小小数位数数位数) )指定了字段名、字段类型、字段宽指定了字段名、字段类型、字段宽度及小数位数。字段类型可以用一个字符表示。度及小数位数。字段类型可以用一个字符表示。(5)(5)列级完整性约束条件列级完整性约束条件NOT NULLNOT NULL或或NULLNULL约束：约束：NULLNULL允许该字段值为空；允许该字段值为空；NOT NULLNOT NULL不允许字段值为空。缺省值为不允许字段值为空。缺省值为NOT NULLNOT NULL。U

8、NIQUEUNIQUE约束：是唯一性约束。约束：是唯一性约束。DEFAULTDEFAULT约束：是缺省值约束。约束：是缺省值约束。格式为：格式为：DEFAULT DEFAULT FOR FOR CHECKCHECK约束：是检查约束。约束：是检查约束。格式为格式为: :CONSTRANT CONSTRANT CHECK CHECK ERRORERROR约束：输入的值不符合约束：输入的值不符合CHECKCHECK子句的合法值子句的合法值时，显示的错误信息。时，显示的错误信息。格式为：格式为：ERRORERROR字符型文本信息字符型文本信息(6)(6)表级完整性约束条件表级完整性约束条件PRIMAR

9、Y KEYPRIMARY KEY约束：是实体完整性约束。用于定义约束：是实体完整性约束。用于定义主码，它能保证主码的唯一性和非空性。主码，它能保证主码的唯一性和非空性。PRIMARY KEYPRIMARY KEY约束格式为：约束格式为：CONSTRAINT CONSTRAINT PRIMARY KEYPRIMARY KEY（ ）FOREIGN KEYFOREIGN KEY约束：是外码和参照表约束，它用于约束：是外码和参照表约束，它用于定义参照完整性。定义参照完整性。FOREIGN KEYFOREIGN KEY约束格式为：约束格式为：CONSTRANT CONSTRANT FOREIGN KEY

10、(FOREIGN KEY() ) REFERENCES(REFERENCES()()()例如：用例如：用SQLSQL命令建立下列三个基本表，基表结构为：命令建立下列三个基本表，基表结构为： 学生（学号，姓名，年龄，性别，所在系）；学生（学号，姓名，年龄，性别，所在系）； 课程（课程号，课程名，先行课）；课程（课程号，课程名，先行课）； 选课（学号，课程号，成绩）；选课（学号，课程号，成绩）；CREATE TABLE CREATE TABLE 学生学生( (学号学号 CHAR(5) NOT NULL UNIQUE,CHAR(5) NOT NULL UNIQUE, 姓名姓名 CHAR(8) NOT

11、 NULL,CHAR(8) NOT NULL, 性别性别 SMALLINT,SMALLINT, 所在系所在系 CHAR(20),DEFAULT C1 20 FOR CHAR(20),DEFAULT C1 20 FOR 年龄年龄, , CONSTRAINT C2 CHECK(CONSTRAINT C2 CHECK(性别性别 IN(IN(男男, , 女女););CREATE TABLE CREATE TABLE 课程课程( (课程号课程号 CHAR(5) PRIMARY KEY,CHAR(5) PRIMARY KEY, 课程名课程名 CHAR(20),CHAR(20), 先行课先行课 CHAR(5

12、);CHAR(5);CREATE TABLE CREATE TABLE 选课选课( (学号学号 CHAR(5),CHAR(5), 课程号课程号 CHAR(5),CHAR(5), 成绩成绩 SMALLINT,SMALLINT, CONSTRAINT C3 CHECK( CONSTRAINT C3 CHECK(成绩成绩 BETEWWN 0 AND 100),BETEWWN 0 AND 100), CONSTRAINT C4 PRIMARY KEY( CONSTRAINT C4 PRIMARY KEY(学号，课程号学号，课程号) )， CONSTRAINT C5 FOREIGN KEY(CONSTR

13、AINT C5 FOREIGN KEY(学号学号) ) REFERENCES REFERENCES 学生学生( (学号学号) ) CONSTRAINT C6 FOREIGN KEY(CONSTRAINT C6 FOREIGN KEY(课程号课程号) ) REFERENCES REFERENCES 课程课程( (课程号课程号););例题说明：例题说明：（1 1）在学生表中定义的列组约束条件是：学号不能）在学生表中定义的列组约束条件是：学号不能为空且不能出现重复值；姓名不能为空；年龄的为空且不能出现重复值；姓名不能为空；年龄的缺省值为缺省值为2020；性别必须为；性别必须为“男男”或或“女女”。（

14、2 2）在课程表中定义了课程号为主码的表级约束条）在课程表中定义了课程号为主码的表级约束条件。件。（3 3）在选课表中定义了学号和课程号为主码的约束；）在选课表中定义了学号和课程号为主码的约束；定义了成绩值在定义了成绩值在0 0到到100100之间的约束；定义了学号之间的约束；定义了学号为外码，其参照表为学生表，该外码对应学生表为外码，其参照表为学生表，该外码对应学生表中的学号的约束；还定义了课程号为外码，其参中的学号的约束；还定义了课程号为外码，其参照表为课程表，该外码对应课程表中的课程号的照表为课程表，该外码对应课程表中的课程号的约束。约束。2 2、表结构的修改、表结构的修改 语句格式：语

15、句格式：ALTER TABLEALTER TABLE ADD ADD 完整性约束完整性约束,nn DROPDROP MODIFYMODIFY ,n; n; 语句功能：该语句可以添加（语句功能：该语句可以添加（ADDADD）新字段、新字段、删除（删除（DROPDROP）字段或修改（字段或修改（MODIFYMODIFY）原字段的原字段的定义。定义。例如：向课程表中增加例如：向课程表中增加“学时学时”字段。字段。ALTER TABLE ALTER TABLE 课程课程 ADD ADD 学时学时 SMALLINT; SMALLINT; 例如：将选课表中课程号的宽度改为例如：将选课表中课程号的宽度改为6

16、 6。ALTER TABLE ALTER TABLE 选课选课 MODIFY MODIFY 课程号课程号 CHAR(6);CHAR(6);例如：删除学生表中对年龄的默认值的定义。例如：删除学生表中对年龄的默认值的定义。ALTER TABLE ALTER TABLE 学生学生 DROP C1;DROP C1;3 3、删除基本表删除基本表语句格式：语句格式：DROP TABLEDROP TABLE ;例如：删除学生表。例如：删除学生表。 DROP TABLE DROP TABLE 学生学生; ;如果表是数据库中的表并且相应的数据库是当前数如果表是数据库中的表并且相应的数据库是当前数据库，则从数据库

17、中删除了表；否则虽然从磁盘上据库，则从数据库中删除了表；否则虽然从磁盘上删除了表文件，但是记录在数据库文件中的信息却删除了表文件，但是记录在数据库文件中的信息却没有删除，此后会出现错误提示。所以要删除数据没有删除，此后会出现错误提示。所以要删除数据库中的表时，最好应该使数据库是当前数据库。库中的表时，最好应该使数据库是当前数据库。基本表一旦被删除，表中的数据及在此表基础上建基本表一旦被删除，表中的数据及在此表基础上建立的索引、视图将自动地全部被删除。立的索引、视图将自动地全部被删除。4.2.2 索引的定义及维护索引的定义及维护1 1、建立索引、建立索引语句格式：语句格式：CREATE UNIQ

18、UECLUSTER INDEXCREATE UNIQUECLUSTER INDEX ON ON (,次序次序)；说说 明：明：（1 1）指定）指定UNIQUEUNIQUE项，则该索引的每一个索引值只对应项，则该索引的每一个索引值只对应唯一的记录。唯一的记录。（2 2）指定）指定CLUSTERCLUSTER项，则建立的索引是聚簇索引。项，则建立的索引是聚簇索引。（3 3） 是要建索引的基本表的名称。是要建索引的基本表的名称。（4 4） 是要按其建立索引的列名。是要按其建立索引的列名。（5 5） 指定了索引值的排列顺序，有指定了索引值的排列顺序，有ASC(ASC(升序升序) )和和DESC(DES

19、C(降序降序) )两个值，缺省为两个值，缺省为ASCASC。例如：为学生表按学号升序建立索引例如：为学生表按学号升序建立索引STUSNOSTUSNO。CREATE UNIQUE INDEX Stusno ON CREATE UNIQUE INDEX Stusno ON 学生学生( (学号学号););例如：为课程表按课程号升序建立唯一索引例如：为课程表按课程号升序建立唯一索引COUCNOCOUCNO。CREATE UNIQUE INDEX Coucno ON CREATE UNIQUE INDEX Coucno ON 课程课程( (课程号课程号););例如：为选课表按学号升序和课程号降序建立唯一

20、例如：为选课表按学号升序和课程号降序建立唯一索引索引SCNOSCNO。CREATE UNIQUE INDEX SCno ON CREATE UNIQUE INDEX SCno ON 选课选课( (学号学号 ASCASC，课程号课程号 DESC);DESC);4.2.3 视图的定义及维护视图的定义及维护1 1、视图的定义、视图的定义语句格式：语句格式：CREATE VIEWCREATE VIEW视图名视图名,)2)ASAS查询语句查询语句;说说 明：明：（1 1）组成视图的属性列名全部省略或者全部指定。）组成视图的属性列名全部省略或者全部指定。若省略了视图的各个属性列名，则该视图的属性若省略了视

21、图的各个属性列名，则该视图的属性为查询语句中指定的列名。为查询语句中指定的列名。（2 2）查询语句可以是任意的）查询语句可以是任意的SELECTSELECT查询语句，查询语句，它说明并限制了视图中的数据。它说明并限制了视图中的数据。（3 3）必须明确指定组成视图的各列名的情况。）必须明确指定组成视图的各列名的情况。例如：建立计算机系学生的视图。例如：建立计算机系学生的视图。CREATE VIEW COMPUTER ASCREATE VIEW COMPUTER AS SELECT SELECT 学号学号, ,姓名，年龄姓名，年龄 FROM FROM 学生学生 WHERE WHERE 所在系所在系

22、=“=“计算机计算机”；（上例为从单个表派生出的视图，视图还可以由多（上例为从单个表派生出的视图，视图还可以由多个表导出。）个表导出。）例如：由学生、课程和选课三个表，定义包括学号、例如：由学生、课程和选课三个表，定义包括学号、姓名、课程名和成绩属性的数学系学生成绩视图。姓名、课程名和成绩属性的数学系学生成绩视图。 CREATE VIEW CREATE VIEW 成绩成绩( (学号学号, ,姓名姓名, ,课程名课程名, ,成绩成绩) ) ASAS SELECT SELECT 学生学生. .学号学号, ,学生学生. .姓名，课程姓名，课程. .课程课程名名, ,选课选课. .成绩成绩 FROM

23、FROM 学生学生, ,课程课程, ,选课选课 WHERE WHERE 学生学生. .学号学号= =选课选课. .学号学号 AND AND 课程课程. .课课程名程名= =选课选课. .课程号课程号 AND AND 学生学生. .所在系所在系=“=“数学数学”；建立视图时，用一个查询来建立视图的建立视图时，用一个查询来建立视图的SELECTSELECT子句可以包子句可以包含算术表达式或集函数，这些表达式或集函数与视图的其他含算术表达式或集函数，这些表达式或集函数与视图的其他字段一样对待，由于它们是计算得来的，并不存储在表内，字段一样对待，由于它们是计算得来的，并不存储在表内，所以称为虚字段。所

24、以称为虚字段。 例如：定义一个视图，它包含学号、总成绩、平均成绩三例如：定义一个视图，它包含学号、总成绩、平均成绩三个属性。个属性。CREATE VIEW CREATE VIEW 成绩统计成绩统计( (学号学号, ,总成绩总成绩, ,平均成绩平均成绩) ) ASASSELECT SELECT 学号学号, ,SUM(SUM(成绩成绩) )，AVG(AVG(成绩成绩) )FROM FROM 选课；选课；2 2、视图的删除、视图的删除语句格式：语句格式：DROP VIEWDROP VIEW视图名视图名说说 明：上述命令与明：上述命令与VFPVFP中删除视图的命令中删除视图的命令DELETE VIEW

25、 DELETE VIEW 视图名等价。视图名等价。视图删除后，视图的定义将从数据字典中删除，视图删除后，视图的定义将从数据字典中删除，而由该视图导出的其他视图的定义却仍存在数而由该视图导出的其他视图的定义却仍存在数据字典中，但这些视图已失效。为了防止用户据字典中，但这些视图已失效。为了防止用户在使用时出错，要用视图删除语句把那些失效在使用时出错，要用视图删除语句把那些失效的视图一一删除。同样，在某个基本表被删除的视图一一删除。同样，在某个基本表被删除后，由该基本表导出的所有视图定义虽然没有后，由该基本表导出的所有视图定义虽然没有被删除，但均已无法使用了。被删除，但均已无法使用了。3 3、关于视

26、图的说明、关于视图的说明在在VFPVFP中视图是可更新的，但是这种更新是否反映中视图是可更新的，但是这种更新是否反映在基本表中则取决于视图更新属性的设置。在在基本表中则取决于视图更新属性的设置。在VFPVFP中视图有它特殊的概念和用途，在关系数据库中，中视图有它特殊的概念和用途，在关系数据库中，视图始终不真正含有数据，它总是原来表的一个视图始终不真正含有数据，它总是原来表的一个窗口。所以，虽然视图可以像表一样进行各种查窗口。所以，虽然视图可以像表一样进行各种查询，但是插入、更新和删除操作在视图上却有一询，但是插入、更新和删除操作在视图上却有一定的限制。定的限制。在一般情况下，当一个视图是由单个

27、表导出时可在一般情况下，当一个视图是由单个表导出时可以进行插入和更新操作，但不能进行删除操作以进行插入和更新操作，但不能进行删除操作; ;当当视图是从多个表导出时，插入、更新和删除操作视图是从多个表导出时，插入、更新和删除操作都不允许进行。这种限制是很必要的，它可以避都不允许进行。这种限制是很必要的，它可以避免一些潜在问题的发生。免一些潜在问题的发生。5.3 SQL的数据更新功能的数据更新功能数据更新是指对数据的操作功能，主要包括数据数据更新是指对数据的操作功能，主要包括数据的插入、更新、删除三个方面的操作。的插入、更新、删除三个方面的操作。5.3.1 数据插入数据插入SQLSQL的数据插入语

28、句有两种使用形式：一种是使用的数据插入语句有两种使用形式：一种是使用常量，一次插入一条记录；另一种是插入子查询常量，一次插入一条记录；另一种是插入子查询的结果，一次插入多条记录。的结果，一次插入多条记录。1 1、使用常量插入一条记录、使用常量插入一条记录2 2、在表中插入子查询的结果在表中插入子查询的结果5.3 SQL的数据更新功能的数据更新功能数据更新是指对数据的操作功能，主要包括数据数据更新是指对数据的操作功能，主要包括数据的插入、更新、删除三个方面的操作。的插入、更新、删除三个方面的操作。5.3.1 数据插入数据插入SQLSQL的数据插入语句有两种使用形式：一种是使用的数据插入语句有两种

29、使用形式：一种是使用常量，一次插入一条记录；另一种是插入子查询常量，一次插入一条记录；另一种是插入子查询的结果，一次插入多条记录。的结果，一次插入多条记录。1 1、使用常量插入一条记录、使用常量插入一条记录2 2、在表中插入子查询的结果在表中插入子查询的结果1 1、使用常量插入一条记录、使用常量插入一条记录语句格式：语句格式：INSERT INTOINSERT INTO (,)2,)VALUESVALUES( (常量常量1 1) ), , 常量常量2 2，) )语句功能：将新记录插入指定表中。语句功能：将新记录插入指定表中。说说 明：明：（1 1）新记录）新记录 n的值为的值为 n的值。的值。

30、（2 2）如果）如果INTOINTO子句中有属性列选项，则没有出现子句中有属性列选项，则没有出现在子句中的属性将取空值。在子句中的属性将取空值。（3 3）如果）如果INTOINTO子句中没有指明任何列名，则新插子句中没有指明任何列名，则新插入的记录必须在每个列上均有值。入的记录必须在每个列上均有值。例如：将一新生记录例如：将一新生记录( (学号学号: :20050110,姓名姓名: 张亮张亮,年龄年龄: 20，所在系，所在系: 数学数学) )插入到学生插入到学生表中。表中。 INSERT INTO INSERT INTO 学生学生 VALUES( VALUES(20050110, 张亮张亮,

31、20, 数学数学) )；例如：在选课表中插入一条记录例如：在选课表中插入一条记录( (学号学号: : 20030211, ,课程号课程号: : C10, ,成绩不详成绩不详) )。 INSERT INTO INSERT INTO 选课选课 VALUES(VALUES(20030211, C10) )；2 2、在表中插入子查询的结果在表中插入子查询的结果语句格式：语句格式：INSERT INTO INSERT INTO (,2,) ) ；例如：求每个系学生的平均年龄，并把结果存入数据表中。例如：求每个系学生的平均年龄，并把结果存入数据表中。CREATE TABLE CREATE TABLE 系平均年龄系平均年龄( (系名系名 CHAR(20),CHAR(20),平均年龄平均年龄 SMALLINT)SMALLINT)；INSERT INTO INSERT INTO 系平均年龄系平均年龄SELECT SELECT 所在系所