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