VisualFoxPro查询,视图与SQL上课完美教案.ppt

第五章 查询、视图与SQL,5.1 创建查询,5.1.1 用查询设计器创建查询（1）用菜单方式打开查询设计器 选择【文件】菜单的【新建】命令，或单击【常用】工具栏的【新建】按钮，打开【新建】对话框，在该对话框中选择【查询】，再单击【新建文件】按钮，即可进入【查询设计器】窗口。（2）用命令方式打开查询设计器 格式：CREATE QUERY查询文件名 功能：打开查询设计器创建查询。说明：查询文件名可以不加扩展名，系统会自动为该文件加上扩展名.qpr。注：打开查询设计器时会打开表或视图。,5.1 创建查询,添加表或视图如果选择的是多个表或视图，则表之间必须是有联系的。,例子,【例5.1】创建一个对单表dab.dbf的查询文件dabcx.qpr，查询男生的学号、姓名、专业班级，并按“学号”字段排序。操作步骤如下：启动查询设计器。打开【查询设计器】窗口并选择需要使用的数据表dab.dbf 选择查询输出字段。在【字段】选项卡页面的【可用字段】中，选择字段：学号、姓名、专业班级，单击【添加】按钮将所选字段添加到【选定字段】列表框中，或双击上述字段，结果如下图所示。,图示：,图示,保存查询文件。,例子,【例5.2】利用上一章已经建立的“学生学籍管理系统.dbc”数据库创建一个多表查询dbcx1.qpr，查询“陈兴华”同学的各科成绩，要求给出学号、姓名、课程名和成绩，并按照“成绩”进行排序。操作步骤如下：启动查询设计器。打开“学生学籍管理.dbc”数据库。启动查询设计器的同时依次选择dab.dbf,xscjb.dbf和kcb.dbf,5.1 创建查询,选择查询输出字段。在【字段】页面中选择输出字段：dab.学号、dab.姓名、kcb.课程名和xkcjb.成绩。设置查询条件。设定筛选条件为：dab.姓名=“陈兴华”。设置查询输出排序依据。选择【排序依据】选项卡，双击字段xkcjb.成绩，添加到排序条件中。保存查询文件。选择【文件】菜单下【保存】或【另存为】命令，进入【另存为】对话框，将查询文件保存为bcx1.qpr。,5.1.2 用查询向导创建查询,操作方法举例说明如下：【例5.3】利用查询向导创建数据表kcb.dbf和xkcjb.dbf的多表查询dbcx2.qpr，查询选修大学计算机基础且成绩在60分及以上的学生学号、课程名、成绩。操作步骤如下：启动查询向导。选择【文件】菜单下【新建】命令，打开【新建】对话框，选择【查询】，单击【向导】按钮，弹出【向导选取】对话框,5.1.2 用查询向导创建查询,输出字段的选取。在【选择要使用的向导：】列表框中选择“查询向导”，然后单击【确定】按钮，即出现【查询向导】的“步骤1-字段选取”对话框，如下图所示。,5.1.2 用查询向导创建查询,建立表间关系。,5.1.2 用查询向导创建查询,设置查询条件。,5.1.2 用查询向导创建查询,预览查询效果。单击【预览】按钮，进入【预览】窗口，结果如下图所示。排序查询结果。步骤4-排序记录”对话框。这一步设置输出字段的排序顺序。将【可用字段】栏中的“xkcjb.成绩”以降序添加到“选定字段”栏。,5.1.2 用查询向导创建查询,保存查询文件,5.1.3 查询文件的运行,运行查询文件通常有3种方法：（）在查询设计器中直接运行 在查询设计器中设计查询时，每设计好一部分，都可以运行查询，查看结果。其方法是：在【查询设计器】窗口中选择【查询】菜单中的【运行查询】选项，或单击常用工具栏的【运行】按钮，即可运行查询。（）利用菜单选项运行 当查询文件设计完成并保存后，可以通过选择【程序】菜单中的【运行】命令，打开【运行】对话框。选择要运行的查询文件，再单击【运行】按钮，即可运行。,5.1.3 查询文件的运行,（）用命令运行 格式：查询文件名 功能：运行查询文件。说明：查询文件名中的扩展名不能缺省。例如，我们要运行查询文件dbcx1.qpr,则可以在命令窗口中输入命令：DO dbcx1.qpr,其运行结果如下图所示。,5.1.4 查询文件的修改,修改查询文件的方法通常有2种：（）用菜单打开查询文件进行修改 选择【文件】菜单中的【打开】命令，指定文件类型为“查询”，选择相应的查询文件，单击【确定】，打开该查询文件的查询设计器，即可对该查询文件进行修改。（）用命令打开查询文件进行修改 格式：MODIFY QUERY查询文件名 功能：打开指定查询文件的查询设计器，以便修改该查询文件。,5.1.5 查询结果的输出,输出：浏览：将查询结果输出到浏览窗口（默认）。临时表：将查询结果保存于一个临时表（只读）中。表：将查询结果保存于一个数据表中，用户可随意处理这个表。图形：将查询结果用图形方式输出。屏幕：将查询结果输出到屏幕上。报表：将查询结果输出到一个报表文件（.frx）标签：将查询结果输出到一个标签文件（.lbx）,5.1.5 查询结果的输出,选择【查询】菜单下【查询去向】，进入【查询去向】对话框，如下图所示。,5.1.5 查询结果的输出,【例5.4】为【例5.3】所创建的查询文件dbcx2.qpr，定制查询结果的输出格式。操作步骤如下：打开查询文件dbcx2.qpr，如下图所示,5.2 创建视图,视图可以从一个或多个相关联的表中提取信息，视图却是以读写方式访问表文件。视图可以看成是从一个或多个数据表中导出的一张“虚拟表”。视图是数据库的一部分，只有打开或创建包含视图的数据库后，才能创建视图5.2.1 利用视图设计器创建视图 通过例子理解：,5.2 创建视图,【例5.5】为数据库文件“学生学籍管理系统.dbc”创建一个本地视图文件v1.vue，要求显示学号、姓名、课程名称、成绩，并且要求只能更新“成绩”字段。操作步骤如下：打开数据库。打开数据库文件“学生学籍管理系统.dbc”，进入数据库设计窗口。启动视图设计器。选择【文件】菜单下【新建】命令，打开“新建”窗口。选择“视图”，单击【新建文件】按钮，进入【视图设计器】窗口，并同时打开“添加表或视图”对话框,5.2 创建视图,添加表或视图将表dab.dbf、xkcjb.dbf和kcb.dbf添加到视图设计器在【视图设计器】窗口中，进行如下设置：在“字段”选项卡选择字段：dab.学号、dab.姓名、kcb.课程名和xkcjb.成绩。在“更新条件”选项卡中的“表”中选择可更新表：xkcjb，在“字段名”中选择xkcjb的更新关键字和字段：xkcjb.成绩，并将字段名前面的关键字标识（小钥匙）、字段更新标识（小粉笔）均加上“”。在“SQL WHERE 子句包括”中勾选“关键字和可更新字段”，在“使用更新”中勾选“SQL UPDATE”，然后勾选“发送SQL更新”，如下图所示。,5.2 创建视图,5.2 创建视图,保存视图。选择【文件】菜单下【保存】命令，输入视图文件名v1。此时在数据库“学生学籍管理系统.dbc”中会添加一个视图文件v1.vue，如下图所示。,5.2 创建视图,浏览视图，打开数据库。在【数据库设计器】窗口中双击视图对象，可以在【浏览】窗口中显示视图内容，如下图所示。,5.2.2 利用视图向导创建视图,打开【数据库设计器】，选择【数据库】菜单中的【新建本地视图】命令，进入【新建本地视图】对话框，然后单击【视图向导】按钮。选择【文件】菜单的【新建】命令，在出现的【新建】对话框中选择【视图】，然后单击【向导】按钮。视图还可以用命令CREATE VIEW视图文件名进行创建，可以用命令DROP VIEW视图文件名进行删除。,5.3 SQL语言,结构化查询语言SQL（Structured Query Language）SQL语言的功能主要包括数据定义、数据控制、数据操作和数据查询等。SQL语言具有以下的特点：SQL是一种一体化的语言。SQL语言是一种高度非过程化的语言。SQL语言非常简洁。,5.3 SQL语言,SQL命令动词,5.3.2 使用SQL语句定义表,1）创建表 格式：CREATE TABLE|DBF FREE(（宽度，小数位数）,（宽度，小数位数）nullnot nulldefault primary key 功能：生成一个由表名所标识的表。,5.3.2 使用SQL语句定义表,数据类型及说明。,5.3.2 使用SQL语句定义表,【例5.6】建立表dab2.dbf（结构和表dab.dbf类似）。CREATE TABLE dab2(学号 C(8)NOT NULL PRIMARY KEY，姓名 C(8),性别 C(2),出生日期 D,专业班级 C(20),照片 G,简历 M)用MODIFYS STRUCTURE显示，结果如下图所示。,5.3.2 使用SQL语句定义表,2)修改表结构(1)修改字段属性 格式：ALTER TABLE ALTER(宽度，小数位数)ALTER(宽度，小数位数)功能：修改指定表的结构。【例5.8】把表dab2.dbf中的学号字段宽度改为10，专业班级字段宽度改为30。ALTER TABLE dab2 ALTER 学号 C(10)ALTER 专业班级 C(30),5.3.2 使用SQL语句定义表,(2)增加字段 格式：ALTER TABLE ADD(宽度，小数位数)ADD（宽度，小数位数）功能：为指定表增加部分字段。【例5.9】给表dab2.dbf增加字段入学总分C(5,1)及身高N(4,2)。ALTER TABLE dab2 ADD 入学总分 N(5,1)ADD 身高 N(4,2),5.3.2 使用SQL语句定义表,（3）字段改名 格式：ALTER TABLE 表名 RENAME TO 新字段名1；RENAME TO 新字段名2 功能：为指定表中的部分字段改名。【例5.10】将表dab2.dbf中“入学总分”字段改名为“入学成绩”。ALTER TABLE dab2 RENAME 入学总分 TO 入学成绩,5.3.2 使用SQL语句定义表,（4）删除字段 格式：ALTER TABLE 表名 DROP DROP 功能：删除指定表中的部分字段。【例5.11】删除表dab2.dbf中身高字段。ALTER TABLE dab2 DROP身高,5.3.2 使用SQL语句定义表,3)删除表格式：DROP TABLE 表名【例5.12】删除自由表dab2.dbf。DROP TABLE dab2【例5.13】删除数据库表xkcjb2.dbf。OPEN DATABASE XSXJGLXTDROP TABLE xkcjb2注意：DROP TABLE直接从磁盘上删除“表名”所对应的.dbf文件。如果“表名”是数据库中的表并且相应的数据库是当前数据库，则从数据库中删除了表。,5.3.3 使用SQL语句查询表数据,SQL语句最主要的功能是查询。1)基本的SELECT语句 格式：SELECT,FROM 数据库名称！功能：从指定表或视图中查询全部记录的指定字段值【例5.14】查询dab中所有学生。SELECT*FROM dab该命令等价于：SELECT 学号,姓名,性别,出生日期,专业班级 FROM dab,5.3.3 使用SQL语句查询表数据,2)唯一性查询 唯一性查询指对于要显示的结果，若有重复出现时，则只显示重复行中的一行。这可通过在基本SELECT语句中加入DISTINCT关键字的方法实现。若不加此关键字，则显示全部行。【例5.16】查询dab中“专业班级”字段，要求数据重复时，仅显示相重复的一条记录。SELECT 专业班级 FROM dab DISTINCT结果如图所示。,5.3.3 使用SQL语句查询表数据,3)带条件的查询WHERE子句的格式：WHERE AND 功能：查询指定表或视图中满足查询条件的记录。,5.3.3 使用SQL语句查询表数据,【例5.17】查询dab中的男生学号、姓名、性别。SELECT 学号,姓名,性别 FROM dab WHERE 性别=男结果如下图所示。【例5.18】查询dab中计算机1班的男生姓名。SELECT 姓名 FROM dab WHERE 性别=男 AND 专业班级=计算机1班结果是：张广田,5.3.3 使用SQL语句查询表数据,【例5.19】查询xkcjb中成绩在7090分范围内的学生。SELECT*FROM xkcjb WHERE 成绩 BETWEEN 70 AND 90 等价于：SELECT*FROM xkcjb WHERE 成绩=70 AND 成绩=90结果如图所示。【例5.21】查询xkcjb中选修课程号为“001”的学生。SELECT*FROM xkcjb WHERE 课程号 IN(001)结果有3条记录，学号分别为“20050010”、“20050020”和“20045643”。,5.3.3 使用SQL语句查询表数据,【例5.20】查询dab中所有姓“张”的学生。SELECT*FROM dab WHERE 姓名 LIKE 张%结果如下图所示。LIKE子句中可以用通配符：下划线“_”，表示任何一个字符；百分号符“%”，表示一串字符。,5.3.3 使用SQL语句查询表数据,4)排序查询格式：ORDER BY ASC|DESC,ASC|DESC说明：ASC表示升序排序（缺省方式），DESC表示降序排序。【例5.22】按课程的学分升序查询出全部课程信息。SELECT*FROM kcb ORDER BY 学分,5.3.3 使用SQL语句查询表数据,【例5.23】按学号升序，相同学号再按成绩降序查询出全部学生成绩情况。SELECT*FROM xkcjb ORDER BY 学号,成绩 DESC 结果如下图所示。,5.3.3 使用SQL语句查询表数据,5)带计算函数的查询基本的查询计算函数的格式及功能如下表所示。,5.3.3 使用SQL语句查询表数据,【例5.24】求所有课程的总学分。SELECT SUM(学分)FROM kcb 结果是：Sum_学分31【例5.25】求学生总人数。SELECT COUNT(*)FROM dab 结果是：Cnt12,5.3.3 使用SQL语句查询表数据,6)用别名输出列标题（字段名）将列名用含义更明确的别名输出，便于理解，可以通过AS关键字实现。格式：AS 功能：将查询输出的列名以列别名输出。其中，列别名若为常字串，则无须加定界符【例5.28】求选修课程的最高成绩和最低成绩。SELECT MAX(成绩)AS 最高成绩,MIN(成绩)AS 最低成绩 FROM xkcjb结果是：最高成绩 最低成绩100.0 38.0,5.3.3 使用SQL语句查询表数据,7)分组查询 格式：GROUP BY,HAVING 其中，可以是表的字段名、字段函数名或标识列的数值型表达式；HAVING 子句进一步限定分组的条件。注意：若只有HAVING子句而无GROUP BY子句，则HAVING子句相当于WHERE子句。,5.3.3 使用SQL语句查询表数据,【例5.29】求各门课程的平均成绩。SELECT 课程号,AVG(成绩)；FROM xkcjb GROUP BY 课程号结果如图所示。【例5.30】求至少选修了2门课程的学生的信息SELECT dab.学号,姓名,课程号 FROM dab,xkcjb WHERE dab.学号=xkcjb.学号;GROUP BY dab.学号 HAVING COUNT(*)=2结果如图所示。,5.3.3 使用SQL语句查询表数据,8)多表联接查询4种联接类型的查询：内部联接（Inner Join):只有2个表的字段都满足联接条件时，才将此记录选入查询结果中。左联接(Left Outer Join)：联接条件左边表中的记录都包含在查询结果中，而右边表中的记录只有满足联接条件时，才选入查询结果中。右联接（Right Outer Join）：与左联接正好相反。完全联接（Full Join）：无论2个表中的记录是否满足联接条件，都将全部记录选入查询结果中。,5.3.3 使用SQL语句查询表数据,方法：一是在查询命令中显示地指定联接类型；二是在查询命令中使用WHERE子句；三是使用嵌套查询。（1）指定联接类型实现多表间的联接查询 格式：SELECT FROM INNER|LEFT|RIGHT|FULL JOIN ON WHERE 功能：通过指定的联接类型建立多表间的联接 注意：若SELECT后要查询的列名（字段名）在2个表中都有，则必须采用“表名.字段名”，若字段名唯一，则可仅写出字段名。,5.3.3 使用SQL语句查询表数据,【例5.31】显示dab和xkcjb中成绩不及格的学生姓名、学号、课程号及成绩。SELECT dab.学号,姓名,课程号,成绩 FROM xkcjb;INNER JOIN dab ON dab.学号=xkcjb.学号;WHERE 成绩60结果如图所示。,5.3.3 使用SQL语句查询表数据,（2）用WHERE子句实现多表间的联接查询：用WHERE子句实现多表间的联接查询时，无须直接指明联接类型（隐含是内部联接），只需把联接条件直接写入WHERE子句即可。【例5.32】用WHERE子句完成例5.31的功能。SELECT dab.学号,姓名,课程号,成绩 FROM xkcjb,dab WHERE dab.学号=xkcjb.学号 AND 成绩60（3）使用嵌套查询实现多表间的联接查询：嵌套查询就是将第2个查询块放在第1个查询块的WHERE条件中，形成外层(第1个)查询包含内层(第2个)查询的嵌套查询。,5.3.3 使用SQL语句查询表数据,【例5.33】查询成绩在90分及以上的学生姓名。SELECT 姓名 FROM dab WHERE 学号 IN；(SELECT 学号 FROM xkcjb WHERE 成绩=90)结果是蜂雪燕和张伟。,5.3.3 使用SQL语句查询表数据,9)查询中的几个特殊选项(1)使用TOP PERCENT短语显示部分结果【例5.34】显示学分较高的2门课程的信息。SELECT*TOP 2 FROM KCB ORDER BY 学分 DESC注意：TOP短语要与ORDER BY短语同时使用才有效。(2)使用INTO ARRAY 将查询结果存放到数组中。【例5.36】查询学生档案信息并将结果放入数组TEMP中。SELECT*FROM DAB INTO ARRAY TEMP,5.3.3 使用SQL语句查询表数据,(3)使用INTO CURSOR 将查询结果存放到临时表中。【例5.37】查询课程表信息并将结果放入临时表TEMP中。SELECT*FROM KCB INTO CURSOR TEMP4)使用INTO DBF|TABLE 将查询结果存放到永久表中。【例5.38】查询课程表信息并将结果放入永久表TEMP中。SELECT*FROM KCB INTO DBF TEMP,5.3.4 使用SQL语句更新表,1)插入记录 格式：INSERT INTO(，)VALUES(，)功能：在指定表末尾添加一条新记录。【例5.39】向表dab添加一条记录。INSERT INTO dab(学号,姓名,性别,出生日期,专业班级)VALUES(20060002,张三,男,19871010,计算机1班),5.3.4 使用SQL语句更新表,2)更新记录 格式：UPDATE SET=,=WHERE 功能：更新表中满足条件记录的字段值，即用的值替换对应的的值。【例5.40】将dab表中的姓名“张三”改为“李四”。UPDATE dab SET 姓名=李四 WHERE 姓名=张三结果如下图所示。,5.3.4 使用SQL语句更新表,3)删除记录格式：DELETE FROM WHERE 功能：逻辑删除表中满足条件的记录。注意：SQL语句的删除记录语句是逻辑删除，如果要物理删除记录需要继续使用PACK命令。【例5.41】逻辑删除表dab中姓名为“李四”的记录。DELETE FROM dab WHERE 姓名=李四,