VFP第3章.pptx

1、主编 杜春敏,Microsoft,Visual FoxPro 程序设计, http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- http:/www.ort-,第3章 结构化查询语言, http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- http:/www.ort-,了解SQL语言功能 理解SQL的表定义功能 掌握SQL的基本查询以及多表

2、联接查询和嵌套查询相关功能, http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- http:/www.ort-,3.1 SQL 概述,SQL语言，是用户操作关系数据库的通用语言，几乎所有的关系型数据库管理系统都支持SQL语言，Visual FoxPro也不例外，SQL已然成为业界的标准。 SQL主要特点： 综合统一 、高度非过程化、面向集合的操作方式 、同一种语法结构，两种使用方式 、语言简洁易用, http:/www.zg- http:/www.skf- htt

3、p:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- http:/www.ort-,SQL主要功能,SQL语言主要包括数据定义、数据操作和数据查询以及数据控制等功能，本章将从数据定义、数据操作和数据查询三个方面介绍Visual FoxPro所支持的SQL语言。, http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- http:/www.ort-, http:/www.zg- http:/www.skf

4、- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- http:/www.ort-, http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- http:/www.ort-,3.2 数据定义功能,完整的SQL语言数据定义功能包括数据库定义、表定义、视图定义、存储过程定义、触发器定义和规则定义等。本节主要介绍Visual FoxPro所支持的SQL语言表定义功能：创建表、修改表结构和撤销表的操作。

5、,3.2.1 创建表CREATE TABLE,命令格式： CREATE TABLEDBF (,),(), http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- http:/www.ort-,【例3.1】 用SQL语句创建学生表“XS.DBF”。,CREATE TABLE XS (学号 C(10),姓名 C(8),性别 C(2),出生日期 D,籍贯 C(12),民族 C(20);专业 C(20),班级 C(20),院系代码 C(10),是否党员 L,个人简历 M,照片

6、 G), http:/www.zg- http:/www.skf- http:/www.nsk- http:/www.fag- http:/www.ntn- http:/www.xunchi- http:/www.ort-,【例3.2】用SQL语句创建课程表“KC.DBF”。,CREATE TABLE KC (课程号 C(4)，课程名 C(20)，学时数 N( 3,0),3.2.2 修改表ALTER TABLE,命令格式1： ALTER TABLE ADD |ALTER COLUMN (,) 功能：用于添加或修改字段，ADD用于添加，ALTER用于修改字段。,【例3.3】用SQL语句向学生表“

7、XS.DBF”中添加家庭住址字段。,ALTER TABLE XS ADD 家庭住址 C（30）,命令格式2：ALTER TABLEDROPCOLUMN功能：用于删除字段。 【例3.4】用SQL语句删除“XS.DBF”家庭住址字段。 ALTER TABLE XS DROP 家庭住址,命令格式3： ALTER TABLERENAME COLUMNTO 功能：用于更改字段名称。 【例3.5】用SQL语句将“XS.DBF”表中的“班级”字段改名为“CLASS”。 ALTER TABLE XS RENAME 班级 TO CLASS,3.2.3 撤销表DROP TABLE,命令格式：DROP TABLE

8、【例3.6】撤销院系表“YX.DBF”。 DROP TABLE YX,3.3 数据操作功能,SQL数据操作功能主要包含表（或视图）中数据的添加（INSERT）、更新（UPDATE）和删除（DELETE）功能。,3.3.1 数据添加INSERT,命令格式1： INSERT INTO ( ， ) VALUES （ ，） 功能：在指定表的末尾添加一条新记录。新记录字段1的值为表达式1，字段2的值为表达式2，。各表达式的值必须与对应字段的数据类型一致。如果所添加的新记录全部字段均赋值，则可以省略字段名列表,【例3.7】向成绩表“CJ.DBF”中添加一条记录。 INSERT INTO CJ VALUES

9、(09714046,1006,76) 【例3.8】向课程表“KC.DBF”中添加一条记录。 INSERT INTO KC （课程号，课程名） VALUES (1008,汇编语言),3.3.2 数据更新UPDATE,命令格式如下： UPDATE SET = ,=,WHERE 功能：更新满足条件的记录中指定字段的属性值。其中，WHERE子句指定要更新的记录，缺省时表示针对全部记录。,【例3.9】将课程表“KC.DBF”中“汇编语言”课程的课时数修改为81课时。 UPDATE KC SET 学时数=81 WHERE 课程名=汇编语言“ 【例3.10】将学生表“XS.DBF”中牛芳菲的出生日期改为19

10、90年2月3日，专业改为中医。 UPDATE XS SET 出生日期=1990-02-03,专业=中医 WHERE 姓名=牛芳菲,3.3.3 数据删除DELETE,命令格式如下： DELETE FROMWHERE 功能：对指定表中符合条件的记录进行逻辑删除，缺省WHERE子句时，表示删除所有记录。,【例3.11】逻辑删除“CJ.DBF”中“1002”号课程的成绩记录。 DELETE FROM CJ WHERE 课程号=1002 【例3.12】逻辑删除“XS.DBF”中的所有男生记录。 DELETE FROM XS WHERE 性别=男,3.4 数据查询功能,数据查询，是数据库的核心操作。SQL

11、语言具有强大的数据查询功能，能够进行单表查询、多表联接查询和嵌套查询。,命令格式： SELECT ALLDISTINCT .AS ,.AS. FROM !| INNERLEFTOUTERRIGHTOUTERFULLOUTER JOIN ,!| ON INTO TO FILEADDITIVE TO PRINTER PROMPT TO SCREEN WHERE GROUP BY , HAVING UNION ALL ORDER BY ASCDESC,ASCDESC TOPPERCENT,整个SELECT-SQL语句的含义是，根据WHERE子句的条件表达式，从FROM子句指定的表或视图中找出满足条件

12、的元组，再按照SELECT子句中的目标列表达式，选出元组中的对应字段列表，形成输出结果。整个语句命令格式看起来非常复杂，不容易理解，但只要分解命令格式，理解其中的各个子句的功能和用法，再综合应用就很容易掌握了。,3.4.2 简单查询,1字段的选取 【例3.13】查询“XS.DBF”中所有学生的学号、姓名、籍贯和专业。 SELECT 学号,姓名,籍贯,专业 FROM XS 查询结果如图所示：,【例3.14】查询“KC.DBF”中所有课程的信息。 SELECT 课程号,课程名,学时数 FROM KC 或者 SELECT * FROM KC 当查询表中所有字段时，可以用“*”代表全体字段。查询结果同

13、表“KC.DBF”。,2使用选择谓词ALL/DISTINCT设定是否允许记录重复,如果在SELECT中没有使用任何一个选择谓词，或使用了选择谓词ALL，查询将返回符合条件的全部记录，而且允许在查询结果中包含重复记录。而在字段列表使用DISTINCT，查询结果中若有重复记录则只保留一条。,【例3.15】查询“CJ.DBF”中成绩不低于80分的课程的课程号。 SELECT ALL 课程号 FROM CJ WHERE 成绩=80 【例3.16】查询“CJ.DBF”中成绩不低于80分的课程的课程号，并过滤重复记录。 SELECT DISTINCT 课程号 FROM CJ WHERE 成绩=80,3使用

14、WHERE子句查询满足条件的元组,【例3.17】查询“CJ.DBF”中“2001”号课程高于80分的成绩记录。 SELECT * FROM CJ WHERE 课程号=”2001” AND 成绩80,【例3.18】查询“CJ.DBF”中“1003”号课程成绩在60分到80分范围内的记录。 SELECT * FROM CJ WHERE 课程号=”1003” AND 成绩 BETWEEN 60 AND 80,【例3.19】查询”XS.DBF”中籍贯为合肥、南京或南昌的学生的学号、姓名、性别、出生日期、籍贯和专业。 SELECT 学号,姓名,性别,出生日期,籍贯,专业 FROM XS WHERE 籍贯

15、 IN (合肥,南京,南昌),【例3.20】查询“KC.DBF”中，课程名称包含“程序设计”的课程的相关记录。 SELECT * FROM KC WHERE 课程名 LIKE %程序设计% ,4使用ORDER BY排序,【例3.21】查询“CJ.DBF”中“1002”号课程的考试成绩，并按成绩降序排列显示结果。 SELECT * FROM CJ WHERE 课程号=1002 ORDER BY 成绩 DESC,【例3.22】查询“CJ.DBF”中“1002”号课程成绩位于前三名的学生成绩记录。 SELECT * FROM CJ WHERE课程号=1002”; ORDER BY 3 DESC TO

16、P 3,3.4.3 联接查询,1内联接 【例3.23】查询医药贸易专业学生的选课情况，要求列出学生的学号、姓名、专业和课程号、成绩。 SELECT XS.学号,姓名,专业,课程号,成绩 FROM XS JOIN CJ ON XS.学号=CJ. 学号; WHERE 专业=”医药贸易”,2外联接,【例3.24】左联接，即除满足联接条件的记录出现在查询结果中外，第一个表中不满足联接条件的记录也出现在查询结果中。如查询所有课程的选课情况，包括已被选课程和未被选课程，列出课程的课程号、课程名、学生的学号和成绩。 SELECT KC.课程号,课程名,学号,成绩 FROM KC LEFT JOIN CJ;

17、ON KC.课程号=CJ.课程号,【例3.25】右联接，即除满足联接条件的记录出现在查询结果中外，第二个表中不满足联接条件的记录也出现在查询结果中。如上例查询所有课程的选课情况。可以由右联接实现，命令如下： SELECT KC.课程号,课程名,学号,成绩 FROM CJ RIGHT JOIN KC; ON CJ.课程号=KC.课程号,3全联接,【例3.26】对CJ表与KC表进行全联接查询。 SELECT KC.课程号,课程名,学号,成绩 FROM CJ FULL JOIN KC ON CJ.课程号=KC.课程号;,3.4.4 分组与统计查询,SQL提供的聚合函数主要有: COUNT(DISTI

18、NCT *)：统计表中元组的个数。 COUNT(DISTINCT)：统计字段值个数。 SUM(DISTINCT)：计算字段值总和（必须是数值型字段）。 AVG(DISTINCT)：计算字段平均值（必须是数值型字段）。 MAX(DISTINCT)：求字段最大值。 MIN(DISTINCT)：求字段最小值。,1简单计算查询,【例3.27】查询所有学生的学号、姓名与年龄。 SELECT 学号,姓名,2011-YEAR(出生日期) AS 年龄 FROM XS,2使用聚合函数查询,【例3.28】统计“XS.DBF”中的学生总人数。 SELECT COUNT(*) FROM XS 【例3.29】统计选修了

19、课程的学生人数。 SELECT COUNT( DISTINCT 学号) FROM CJ,【例3.30】查询学号为“10811023”的学生考试总成绩和平均成绩。 SELECT 学号,SUM(成绩),AVG(成绩) FROM CJ WHERE 学号=”10811023” 【例3.31】查询“VFP程序设计”课程的最高分和最低分。 SELECT 课程名,MAX(成绩),MIN(成绩) FROM CJ JOIN KC ON CJ.课程号=KC.课程号; WHERE 课程名= VFP程序设计,3分组统计查询,命令格式是： GROUP BY , HAVING ,【例3.32】查询每个学生的考试总分和平均

20、分。 SELECT 学号, SUM(成绩), AVG(成绩) FROM CJ GROUP BY 学号 【例3.33】统计每门课程的选课人数和平均成绩。 SELECT 课程号,COUNT(学号) AS选课人数, AVG(成绩) FROM CJ GROUP BY 课程号,【例3.34】查询选课人数超过5人的课程的课程号和选课人数。 SELECT 课程号,COUNT(学号) AS 选课人数 FROM CJ; GROUP BY 课程号 HAVING COUNT(学号)5,3.4.5 嵌套查询,在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件表达式中的查询称为嵌套查询。被包含的查询块称为子查询或内层查询，而包含子查询的语句称为主查询或外层查询，为了与外层查询有所区别，总是把子查询写在圆括号内。 嵌套查询常见的命令格式是： SELECT FROM WHERE (),【例3.35】查询成绩高于85分的学生的学号和姓名。 SELECT 学号,姓名 FROM XS WHERE 学号 IN (SELECT 学号 FROM CJ WHERE 成绩85) 【例3.36】查询与王维胜同籍贯的学生的记录。 S