VisualFoxPro中SQL语言的应用.ppt

数据库应用 经管类,第五章 Visual FoxPro中SQL语言的应用,2,江西财经大学信息管理学院数据库应用课程组,第5章 VFP中SQL语言的应用,SQL语言概述 SQL语言的特点和分类 基本表与视图 SQL数据查询语句 SELECT 单表查询 SELECT 多表查询 视图创建与使用（自学） SQL数据操纵语句 插入INSERT；更新UPDATE；删除DELETE SQL数据定义语句(选讲) 创建表 Create；更改表 Alter；删除表Drop,3,江西财经大学信息管理学院数据库应用课程组,5.1 SQL语言概述（1）,问题：不同关系数据库使用不同查询语言带来的问题。,解决方法：标准化-SQL,4,江西财经大学信息管理学院数据库应用课程组,5.1 SQL语言概述（2）,SQL全称是结构化查询语言(Structured Query Language) SQL的历史和标准 SQL89 、SQL99 SQL的优点 所有用户使用的统一语言 所有关系数据库的公共语言 非过程化语言,5,江西财经大学信息管理学院数据库应用课程组,过程化语言示例,煮饭 买米 淘米 开始煮 等待 饭好,6,江西财经大学信息管理学院数据库应用课程组,非过程化语言示例,饭店吃饭,点菜,7,江西财经大学信息管理学院数据库应用课程组,5.1 SQL语言概述（3）,SQL语言结构简洁，功能强大，简单易学。 SQL语言是关系数据库管理系统的标准语言。 SQL分成3类：,以记录为对象,以基本表为对象,8,江西财经大学信息管理学院数据库应用课程组,5.1 SQL语言概述（4）,基本表和视图 基本表 (Base Table)是独立存在的表。在Visual FoxPro中，一个基本表对应一个.DBF文件，一个表可以带若干索引。 视图(view):从一个或几个基本表中根据用户需要而导出的一个虚表 1:视图是虚表,它在存储时只存储视图的定义,而没有存储对应的数据 2:视图只在刚刚打开的一瞬间,通过定义从基表中搜集数据,并展现给用户,9,江西财经大学信息管理学院数据库应用课程组,什么是视图,基本表,视图定义 Create view as select,执行显示,10,江西财经大学信息管理学院数据库应用课程组,5.1 SQL语言概述（5）,基本表和视图 实际的应用中，由于安全控制的原因，在数据库中，一般用户通常不能看到所有的基本表，基本表通常只有数据库管理员DBA可以看到。用户看到的是与他们自己业务相关的视图(View)。 数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。,11,江西财经大学信息管理学院数据库应用课程组,5.2 数据查询,SELECT语句 单表查询 多表查询 创建和使用视图,12,江西财经大学信息管理学院数据库应用课程组,SELECT语句,一般格式： SELECT ALL | DISTINCT , FROM , WHERE GROUP BY HAVING ORDER BY ASC | DESC 简单格式： select 字段列表 from 数据来源列表 where 条件,13,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（1）,1. 单表基本查询 单表查询的含义：from子句只有一个数据表。 （1） 查询所有列 例5-1：返回“学生表”中的所有行和所有列。 open database “d:我的数据库项目数据库教学管理数据库.dbc“ select *; from 教学管理数据库!学生表 例5-2：查询成绩表中的所有记录。 open database “d:我的数据库项目数据库教学管理数据库.dbc“ select *; from 成绩表,14,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（2）,（2） 查询指定的列（投影操作） 例5-3：检索“学生表”中的学号、姓名、性别和籍贯字段。 open database “d:我的数据库项目数据库教学管理数据库.dbc“ select 学号，姓名，性别，籍贯； from 学生表 （3）消除取值重复的行 例5-6：显示学生表中的学生来自全国那些地方。 open database “d:我的数据库项目数据库教学管理数据库.dbc“ select distinct 籍贯； from 学生表,15,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（3）,（4） 查询经过计算的值，更改查询结果中的列标题名 例5-4：对“学生表”的“入学成绩”除以600，求相对成绩，其显示的字段名为“相对成绩”。 open database “d:我的数据库项目数据库教学管理数据库.dbc“ select 学号，姓名，入学成绩/600 as 相对成绩； from 学生表 例5-5：求所有学生在2004年的年纪。 open database “d:我的数据库项目数据库教学管理数据库.dbc“ select 学号，姓名，2004-year(出生日期) as 年龄； from 学生表,16,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（4）,2. 选择表中的若干元组 选择一个表中的若干元组(或记录)操作，是对该表实行选择操作。其方法是使用SELECT语句的WHERE子句中的条件。,17,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（5）,（1）查询满足条件的元组 常用的的查询条件如右表。,18,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（6）,1）大小比较 例5-8：查找入学成绩大于等于600的同学的学号、姓名和入学成绩。 select 学号，姓名，入学成绩； from 学生表； where 入学成绩=600 例5-9：查找学生表中的少数民族学生。 select 学号，姓名； from 学生表； where 少数民族否=.T.,19,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（7）,2）多重条件查询 例5-11：查找入学成绩在500分以上的少数民族学生的学号、姓名和少数民族否。 select 学号，姓名，少数民族否； from 学生表； where 入学成绩=500 and 少数民族否=.T.,20,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（8）,3）确定范围（数值、日期、时间等可比较的数据类型） 例5-13：求入学成绩在500到600间的学生学号、姓名和入学成绩。 select 学号，姓名，入学成绩； from 学生表； where 入学成绩 between 500 and 600 ( where 入学成绩=500 and入学成绩=600),21,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（9）,4）确定集合 例5-14：求籍贯为“江西南昌”或“吉林”的学生学号、姓名和籍贯。 select 学号，姓名，性别，籍贯； from 学生表; where 籍贯 in (“江西南昌”,“吉林”) 例5-15：求籍贯不为“江西南昌”和“吉林”的学生学号、姓名和籍贯。 select 学号，姓名，性别，籍贯； from 学生表; where 籍贯 not in (“江西南昌”,“吉林”) IN后面的集合可以是一个记录集(详见子查询),22,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（10）,5）字符匹配 例5-16：查找以姓“李”开头的学生学号和姓名。 select 学号，姓名； from 学生表; where 姓名 like “李%” 例5-18：查找第二个字符为“阳”字的学生学号和姓名。 select 学号，姓名； from 学生表; where 姓名 like “_阳%”,23,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（11）,3. 对查询结果排序 用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，缺省值为升序。 例5-23：查询所有学生的入学成绩，查询结果按入学成绩的降序排列。 select 学号，姓名，入学成绩； from 学生表； Order by 入学成绩 desc,24,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（12）,4. 对查询结果使用聚集函数,25,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（13）,4. 使用聚集函数 例5-26：求学生表中入学成绩在600分以上的人数。 select count(*)； from 学生表； where 入学成绩=600 例5-27：求学生表中入学成绩的平均成绩。 select avg(入学成绩)； from 学生表；,26,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（14）,5. 对查询结果分组 例5-28：按课程类别号，求各种类别课程的门数。 Select 课程类别号，count(*)； from 课程表； group by 课程类别号 注意：使用GROUP BY子句要注意，SELECT的输出字段列表中，凡没有出现在聚集函数中的字段，必须出现在GROUP BY子句中。例如“年级”字段出现在字段列表中，但它没有出现在聚集函数中，故字段“年级”必须出现在GROUP BY子句中。,27,江西财经大学信息管理学院数据库应用课程组,5.2.1 单表查询（15）,5. 对查询结果分组，只显示满足给定条件的结果 select 字段列表 from 单表 where 条件 group by 分组条件 having 子句 having 子句中出现聚集函数。 例5-29：求各个年级的班数，要求只输出3个班以上的年级。 select 年级,count(*); from 班级表; group by 年级; having count(*)=3,28,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（1）,问题： 已经学习一元关系操作（投影、选择），如何实现二元关系操作（自然连接）？,29,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（2）,1. 多表查询的工作原理 所谓多表查询是指from子句包括多个数据表，即数据表列表。 知识补允：笛卡尔乘积 A=a1,a2,a3,an B=b1,b2,bm AB = (a1,b1),(a1,b2),(a1,bm), (a2,b1),(a2,b2),(a2,bm), (an,b1),(an,b2),(an,bm) 例A： select *; from stu2,ach2 注意：这里没有where子句,30,江西财经大学信息管理学院数据库应用课程组,基本表：stu2,8个字段 6条记录,31,江西财经大学信息管理学院数据库应用课程组,基本表：ach2,3个字段 7条记录,32,江西财经大学信息管理学院数据库应用课程组,例子结果,33,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（3）,结果分析： 语句“select * from stu2,ach2”的结果非常庞大有11个字段，42条记录。 “表stu2”的8个字段+“表ach2”的3个字段=11个字段 “表stu2”(共6条记录)“表ach2”（共7条记录）=42条记录。 如果SELECT从两表检索结果，且不带WHERE子句时， 首先从前 “学生表”中取一条记录，然后与 “成绩表”中的每条记录进行逐一匹配后送入输出结果中。 再在学生表中取下一条记录，重复上面过程。 结果为在字段数（横向上）是两表的字段数相加； 在记录数（纵向上）是两表的记录数相乘。 结果存在记录组合爆炸的问题，这会产生很多无用的垃圾数据。,34,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（4）,自然连接操作复习 前提条件 这二个表必须存在一个公共的属性（称为连接属性），在一个表（称为一表）中它是主键，而在另一个表（称为多表）中它是外键。 如 “表stu2”（一表）中主键是学号，而学号在“表ach2”（多表）中是外键。 自然连接操作： 在字段上是二个参与运算的关系中的字段相加； 在记录上是在多表记录的基础上，扩展公共属性匹配时的一表对应记录。 结果只含匹配的记录项,35,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（5）,自然连接 前提条件：二个表间存在主、外键约束关系。 参见：参照完整性。,36,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（6）,自然的等值连接的结果集仍是一个关系 该关系的属性（字段）是两个关系的叠加； 该关系的元组（记录）是以多表为准。,37,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（7）,结论：select语句实现自然连接的约束条件 必须二个表之间必须存间主外键约束关系，即二个表中有共同的字段（或字段集），这个字段或字段集在一个表中为主键，在另一个表中为外键。 SELECT从两表检索结果时，要得到有效的数据必须带WHERE子句 如果二个实体是多对多的关系，也可以转化为二个一对多的关系。我们使用自然连接对二表进行查询。 例如：学生表，课程表 通过成绩表转化,38,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（8）,例B：将表stu2和表ach2进行自然连接，求结果集。 select * ; from stu2,ach2; where stu2.sno = ach2.sno 或 select * ; from stu2 inner join ach2; on stu2.sno = ach2.sno,39,江西财经大学信息管理学院数据库应用课程组,40,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（9）,结果,41,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（10）,分析： 子句“WHERE stu2.sno = ach2.sno” 表示的是表stu2和表ach2进行自然连接操作。它的含义是“stu2”与“ach2”的笛卡尔乘积中，只有sno_a和sno_b匹配的记录才是最终结果。 自然连接操作是数据库SQL检索语句中最常用的操作。 一对多的自然连接的结果是一个关系表，关系表的结果为： 由select挑选两表中字段作出输出目标字段； 由where条件过滤的记录作为最终结果。,42,江西财经大学信息管理学院数据库应用课程组,示例用关系模型,43,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询示例,例C-1：基于成绩表，结合学生表，输出一个含学生学号、姓名、性别、班级号、课程号、成绩的学生课程成绩单 ? ? ? 求课程成绩在85分以上同学的学号、姓名和成绩 ? 例C-2：基于成绩表，结合学生表、课程表，输出一个含学生学号、姓名、性别、班级号、课程名称、成绩的学生课程成绩单 ? ? ?,44,江西财经大学信息管理学院数据库应用课程组,多表查询示例,例C-3：基于成绩表，结合学生表、课程表、班级表，输出一个含学生专业名称、学号、姓名、性别、课程名称、成绩的各班学生考试成绩单 ? ? ?,45,江西财经大学信息管理学院数据库应用课程组,例C-3,select 专业名称, 学生表.学号 as 学号, 姓名, 性别, 课程名,成绩; from 学生表,成绩表,课程表,班级表; where 学生表.学号 = 成绩表.学号; and 课程表.课程号 = 成绩表.课程号; and 班级表.班级号 = 学生表.班级号,46,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（11）,问题：select语句的通用构造方法？如果from子句中有多个数据表怎么办？ 答：分段分层填空方式 select 目标字段列表 from 数据表列表（二个以上数据表） where 条件,47,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（12）,分层填空方法 1. 查询目标出现在“目标字段列表”中 2. “数据表列表”的构成： 首先将所有具备输出字段的数据表加入到“数据表列表”。 然后将与已知条件对应的数据表加入“数据表列表”（如果前面没有加入） 数据表列表间必须存在主、外键约束关系如果以上选择的数据列表在给定的数据库模式中不存在关联，则必须将关联这些数据表的中间数据表也加入到“数据表列表”，即使它们不需要输出字段值。,48,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（13）,分层填空方法 3. 二表间的自然连接 “表名.主键 =表名.外键” 多表间的自然连接 “表名.主键 =表名.外键 and 表名.主键 =表名.外键” and 已知条件”。 此处体现数据库导航的概念 即：从已知求未知,49,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（14）,例D：求计算机科学技术07-02班所有学生的成绩单。 分析：已知条件为“班级表”中的“专业名称”，待求为“学生表”的“姓名”；“课程”表的“课程号、课程名”；“成绩表”的“成绩”。,50,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（15）,例D：求计算机科学技术07-02班所有学生的成绩单。 select子句应包括： 姓名,课程表.课程号,课程名,成绩 from子句包括： 学生表,成绩表,课程表,班级表 Where 子句包括： 学生表.学号 = 成绩表.学号; and 课程表.课程号 = 成绩表.课程号; and 班级表.班级号 = 学生表.班级号; and 专业名称 = “计算机科学技术07-02班“,51,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（16）,例D：求计算机科学技术07-02班所有学生的成绩单。 select 姓名,课程表.课程号,课程名,成绩; from 学生表,成绩表,课程表,班级表; where 学生表.学号 = 成绩表.学号; and 课程表.课程号 = 成绩表.课程号; and 班级表.班级号 = 学生表.班级号; and 专业名称 = “计算机科学技术07-02班“,52,江西财经大学信息管理学院数据库应用课程组,5.2.2 多表查询（17）,习题：数据库模式相同，求“计算机科学技术07-02班”的数据库应用成绩单。 select 姓名,课程表.课程号, 课程名,成绩; from 学生表,成绩表,课程表,班级表; where 学生表.学号 = 成绩表.学号; and 课程表.课程号 = 成绩表.课程号; and 班级表.班级号 = 学生表.班级号; and 专业名称 = “计算机科学技术07-02班“; and 课程名 = “数据库应用“,53,江西财经大学信息管理学院数据库应用课程组,5.2.3 创建和使用视图,命令方式语法： Create sql view 视图名; as; sqlselect语句 例如：”各班各同学考试成绩”的视图 Create sql view 考试成绩; as; select 专业名称, 学生表.学号 as 学号, 姓名, 性别, 课程名, 成绩; from 课程表, 成绩表, 学生表, 班级表; where 学生表.学号 = 成绩表.学号; and 课程表.课程号 = 成绩表.课程号; and 班级表.班级号 = 学生表.班级号,54,江西财经大学信息管理学院数据库应用课程组,5.2.3 创建和使用视图 cont.,基于视图”考试成绩”进行数据分析 例：分析各班考试成绩 Select 专业名称, count(*), avg(成绩) as 平均成绩, max(成绩), min(成绩); from 考试成绩; group by 专业名称; order by 平均成绩 DESC,55,江西财经大学信息管理学院数据库应用课程组,5.3 数据操纵,插入记录 insert 删除记录 delete 更新记录 update,56,江西财经大学信息管理学院数据库应用课程组,5.3.1 插入记录,例5-42：向学生表插入一条记录，不包括简历和照片两字段。 insert into 学生表; (学号,姓名,性别,出生日期,少数民族否,班级号,籍贯,入学成绩); values (“0043023“,; “张强“,; “男“,; 1989/08/01,; .F.,; “CPA0403“,; “辽宁大连“,; 592),57,江西财经大学信息管理学院数据库应用课程组,5.3.2 删除记录,例5-43：删除学号为0043022的学生。 delete from 学生表; where 学号 = “0043022“ pack,58,江西财经大学信息管理学院数据库应用课程组,5.3.3 更新记录,例5-44：将学号为0043023的学生入学成绩改为612分。 update 学生表; set 入学成绩 = 612; where 学号 = 0043023 注意：由于UPDATE语句对数据表更新是不可逆的。所以，UPDATE语句中的WHERE条件必须仔细写好。,59,江西财经大学信息管理学院数据库应用课程组,5.4 数据定义,详细内容见书5.4节 (自修),60,江西财经大学信息管理学院数据库应用课程组,5.5 VFP查询和视图设计器,手工编写要求对数据库关系模式清晰 图形化的SQL查询语句编写 无需手工编写 机房实验课时讲解,61,江西财经大学信息管理学院数据库应用课程组,第5章 小结,SQL查询语言 SELECT 输出目标 FROM 来源 WHERE 条件 group by 分组依据 order by 排序依据 SQL数据操纵语言 Insert Delete Update SQL数据定义语言 Create/alter/drop,62,江西财经大学信息管理学院数据库应用课程组,知识补充：子查询,Select 查询结果为数据集 利用 Where in (数据集) 实现多表查询 示例1：查找”注册会计师专业”的同学名单 Select 姓名 from 学生表 where 班级编号 in () Select 姓名 from 学生表 where 班级