VisualFoxPro中SQL语言的应用.ppt

数据库应用经管类,第五章 Visual FoxPro中SQL语言的应用,2,江西财经大学信息管理学院数据库应用课程组,第5章 VFP中SQL语言的应用,SQL语言概述SQL语言的特点和分类基本表与视图SQL数据查询语句SELECT 单表查询SELECT 多表查询视图创建与使用（自学）SQL数据操纵语句插入INSERT；更新UPDATE；删除DELETESQL数据定义语句(选讲)创建表 Create；更改表 Alter；删除表Drop,3,江西财经大学信息管理学院数据库应用课程组,5.1 SQL语言概述（1）,问题：不同关系数据库使用不同查询语言带来的问题。,解决方法：标准化-SQL,4,江西财经大学信息管理学院数据库应用课程组,5.1 SQL语言概述（2）,SQL全称是结构化查询语言(Structured Query Language)SQL的历史和标准SQL89、SQL99SQL的优点所有用户使用的统一语言所有关系数据库的公共语言非过程化语言,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,bmAB=(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数据操纵语言InsertDeleteUpdateSQL数据定义语言Create/alter/drop,62,江西财经大学信息管理学院数据库应用课程组,知识补充：子查询,Select 查询结果为数据集利用 Where in(数据集)实现多表查询示例1：查找”注册会计师专业”的同学名单Select 姓名 from 学生表 where 班级编号 in()Select 姓名 from 学生表 where 班级编号 in(Select 班级编号 from 班级表 where 班级名称 like“注册会计师%”)等价于？示例2：教学管理数据库studentdb中求与欧阳思思同一学院的同学名单,Select 班级编号 from 班级表 where 班级名称 like“注册会计师%”,63,江西财经大学信息管理学院数据库应用课程组,多表查询 练习题,图书管理习题：现有一个“图书管理”数据库，其四个数据表结构如下：读者.DBF：读者编号(字符型8),姓名(字符型8),性别(字符型2),身份证号(字符型18),工作单位(字符型50)图书分类.DBF：分类号(字符型3),分类名称(字符型20)图书.DBF：图书编号(字符型10),分类号(字符型3),图书名称(字符型50),作者姓名(字符型8),出版社名(字符型20),出版号(字符型17),单价(数值型7.2),出版时间(日期型8),入库时间(日期型8),库存数量(整型4),借阅.DBF：读者编号(字符型8),图书编号(字符型10),借阅日期(日期型8),归还日期(日期型8),是否归还(逻辑型1),64,江西财经大学信息管理学院数据库应用课程组,图书管理数据库关系模式,65,江西财经大学信息管理学院数据库应用课程组,多表查询 练习题,请写出完成下列操作的FOXPRO命令序列和SQL语句：单表查询显示作者为“王蒙”的所有图书名称、出版社名、出版时间和单价。显示单价大于60元的所有图书名称、出版社名、出版时间和单价。显示所有姓“李”读者的读者编号、姓名和身份证号。显示入库时间在2006年10月的所有图书编号和图书名称。,66,江西财经大学信息管理学院数据库应用课程组,多表查询 练习题,SQL多表查询求读者“李华”所借阅图书的所属分类名称（去除重复的记录）。求在2006年10月间所有借阅图书名称和单价。求所有包含“财经”二字的图书名称、出版社名和单价。求读者“王平”未归还的所有图书名称、出版社名和单价。按分类名称，求各类图书数量，并按降序排列。求读者“王平”借阅的图书被其它读者借阅的情况(输出姓名、图书名称、借阅时间）求作者“萨克”的图书为哪些读者和工作单位所借阅。求图书“计算机科学技术手册”为哪些读者借阅。求分类名称为“自动化”的所有图书为哪些单位的读者借阅（去除重复的记录）。求借阅日期为2006年6月17日，且尚未归还的图书编号和图书名称。,