VisualFoxPro中SQL语言的应用.ppt

数据库应用经管类,第五章VisualFoxPro中SQL语言的应用,2,江西财经大学信息管理学院数据库应用课程组,第5章VFP中SQL语言的应用,SQL语言概述SQL语言的特点和分类基本表与视图SQL数据查询语句SELECT单表查询SELECT多表查询视图创建与使用（自学）SQL数据操纵语句插入INSERT；更新UPDATE；删除DELETESQL数据定义语句(选讲)创建表Create；更改表Alter；删除表Drop,3,江西财经大学信息管理学院数据库应用课程组,5.1SQL语言概述（1）,问题：不同关系数据库使用不同查询语言带来的问题。,解决方法：标准化-SQL,4,江西财经大学信息管理学院数据库应用课程组,5.1SQL语言概述（2）,SQL全称是结构化查询语言(StructuredQueryLanguage)SQL的历史和标准SQL89、SQL99SQL的优点所有用户使用的统一语言所有关系数据库的公共语言非过程化语言,5,江西财经大学信息管理学院数据库应用课程组,过程化语言示例,煮饭买米淘米开始煮等待饭好,6,江西财经大学信息管理学院数据库应用课程组,非过程化语言示例,饭店吃饭,点菜,7,江西财经大学信息管理学院数据库应用课程组,5.1SQL语言概述（3）,SQL语言结构简洁，功能强大，简单易学。SQL语言是关系数据库管理系统的标准语言。SQL分成3类：,以记录为对象,以基本表为对象,8,江西财经大学信息管理学院数据库应用课程组,5.1SQL语言概述（4）,基本表和视图基本表(BaseTable)是独立存在的表。在VisualFoxPro中，一个基本表对应一个.DBF文件，一个表可以带若干索引。视图(view):从一个或几个基本表中根据用户需要而导出的一个虚表1:视图是虚表,它在存储时只存储视图的定义,而没有存储对应的数据2:视图只在刚刚打开的一瞬间,通过定义从基表中搜集数据,并展现给用户,9,江西财经大学信息管理学院数据库应用课程组,什么是视图,基本表,视图定义Createviewasselect,执行显示,10,江西财经大学信息管理学院数据库应用课程组,5.1SQL语言概述（5）,基本表和视图实际的应用中，由于安全控制的原因，在数据库中，一般用户通常不能看到所有的基本表，基本表通常只有数据库管理员DBA可以看到。用户看到的是与他们自己业务相关的视图(View)。数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。,11,江西财经大学信息管理学院数据库应用课程组,5.2数据查询,SELECT语句单表查询多表查询创建和使用视图,12,江西财经大学信息管理学院数据库应用课程组,SELECT语句,一般格式：SELECTALL|DISTINCT,FROM,WHEREGROUPBYHAVINGORDERBYASC|DESC简单格式：select字段列表from数据来源列表where条件,13,江西财经大学信息管理学院数据库应用课程组,5.2.1单表查询（1）,1.单表基本查询单表查询的含义：from子句只有一个数据表。（1）查询所有列例5-1：返回“学生表”中的所有行和所有列。opendatabase“d:我的数据库项目数据库教学管理数据库.dbc“select*;from教学管理数据库!学生表例5-2：查询成绩表中的所有记录。opendatabase“d:我的数据库项目数据库教学管理数据库.dbc“select*;from成绩表,14,江西财经大学信息管理学院数据库应用课程组,5.2.1单表查询（2）,（2）查询指定的列（投影操作）例5-3：检索“学生表”中的学号、姓名、性别和籍贯字段。opendatabase“d:我的数据库项目数据库教学管理数据库.dbc“select学号，姓名，性别，籍贯；from学生表（3）消除取值重复的行例5-6：显示学生表中的学生来自全国那些地方。opendatabase“d:我的数据库项目数据库教学管理数据库.dbc“selectdistinct籍贯；from学生表,15,江西财经大学信息管理学院数据库应用课程组,5.2.1单表查询（3）,（4）查询经过计算的值，更改查询结果中的列标题名例5-4：对“学生表”的“入学成绩”除以600，求相对成绩，其显示的字段名为“相对成绩”。opendatabase“d:我的数据库项目数据库教学管理数据库.dbc“select学号，姓名，入学成绩/600as相对成绩；from学生表例5-5：求所有学生在2004年的年纪。opendatabase“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入学成绩=500and少数民族否=.T.,20,江西财经大学信息管理学院数据库应用课程组,5.2.1单表查询（8）,3）确定范围（数值、日期、时间等可比较的数据类型）例5-13：求入学成绩在500到600间的学生学号、姓名和入学成绩。select学号，姓名，入学成绩；from学生表；where入学成绩between500and600(where入学成绩=500and入学成绩=600例5-27：求学生表中入学成绩的平均成绩。selectavg(入学成绩)；from学生表；,26,江西财经大学信息管理学院数据库应用课程组,5.2.1单表查询（14）,5.对查询结果分组例5-28：按课程类别号，求各种类别课程的门数。Select课程类别号，count(*)；from课程表；groupby课程类别号注意：使用GROUPBY子句要注意，SELECT的输出字段列表中，凡没有出现在聚集函数中的字段，必须出现在GROUPBY子句中。例如“年级”字段出现在字段列表中，但它没有出现在聚集函数中，故字段“年级”必须出现在GROUPBY子句中。,27,江西财经大学信息管理学院数据库应用课程组,5.2.1单表查询（15）,5.对查询结果分组，只显示满足给定条件的结果select字段列表from单表where条件groupby分组条件having子句having子句中出现聚集函数。例5-29：求各个年级的班数，要求只输出3个班以上的年级。select年级,count(*);from班级表;groupby年级;havingcount(*)=3,28,江西财经大学信息管理学院数据库应用课程组,5.2.2多表查询（1）,问题：已经学习一元关系操作（投影、选择），如何实现二元关系操作（自然连接）？,29,江西财经大学信息管理学院数据库应用课程组,5.2.2多表查询（2）,1.多表查询的工作原理所谓多表查询是指from子句包括多个数据表，即数据表列表。知识补允：笛卡尔乘积A=a1,a2,a3,anB=b1,b2,bmAB=(a1,b1),(a1,b2),(a1,bm),(a2,b1),(a2,b2),(a2,bm),(an,b1),(an,b2),(an,bm)例A：select*;fromstu2,ach2注意：这里没有where子句,30,江西财经大学信息管理学院数据库应用课程组,基本表：stu2,8个字段6条记录,31,江西财经大学信息管理学院数据库应用课程组,基本表：ach2,3个字段7条记录,32,江西财经大学信息管理学院数据库应用课程组,例子结果,33,江西财经大学信息管理学院数据库应用课程组,5.2.2多表查询（3）,结果分析：语句“select*fromstu2,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*;fromstu2,ach2;wherestu2.sno=ach2.sno或select*;fromstu2innerjoinach2;onstu2.sno=ach2.sno,39,江西财经大学信息管理学院数据库应用课程组,40,江西财经大学信息管理学院数据库应用课程组,5.2.2多表查询（9）,结果,41,江西财经大学信息管理学院数据库应用课程组,5.2.2多表查询（10）,分析：子句“WHEREstu2.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创建和使用视图,命令方式语法：Createsqlview视图名;as;sqlselect语句例如：”各班各同学考试成绩”的视图Createsqlview考试成绩;as;select专业名称,学生表.学号as学号,姓名,性别,课程名,成绩;from课程表,成绩表,学生表,班级表;where学生表.学号=成绩表.学号;and课程表.课程号=成绩表.课程号;and班级表.班级号=学生表.班级号,54,江西财经大学信息管理学院数据库应用课程组,5.2.3创建和使用视图cont.,基于视图”考试成绩”进行数据分析例：分析各班考试成绩Select专业名称,count(*),avg(成绩)as平均成绩,max(成绩),min(成绩);from考试成绩;groupby专业名称;orderby平均成绩DESC,55,江西财经大学信息管理学院数据库应用课程组,5.3数据操纵,插入记录insert删除记录delete更新记录update,56,江西财经大学信息管理学院数据库应用课程组,5.3.1插入记录,例5-42：向学生表插入一条记录，不包括简历和照片两字段。insertinto学生表;(学号,姓名,性别,出生日期,少数民族否,班级号,籍贯,入学成绩);values(0043023,;张强,;男,;1989/08/01,;.F.,;CPA0403,;辽宁大连,;592),57,江西财经大学信息管理学院数据库应用课程组,5.3.2删除记录,例5-43：删除学号为0043022的学生。deletefrom学生表;where学号=0043022pack,58,江西财经大学信息管理学院数据库应用课程组,5.3.3更新记录,例5-44：将学号为0043023的学生入学成绩改为612分。update学生表;set入学成绩=612;where学号=0043023注意：由于UPDATE语句对数据表更新是不可逆的。所以，UPDATE语句中的WHERE条件必须仔细写好。,59,江西财经大学信息管理学院数据库应用课程组,5.4数据定义,详细内容见书5.4节(自修),60,江西财经大学信息管理学院数据库应用课程组,5.5VFP查询和视图设计器,手工编写要求对数据库关系模式清晰图形化的SQL查询语句编写无需手工编写机房实验课时讲解,61,江西财经大学信息管理学院数据库应用课程组,第5章小结,SQL查询语言SELECT输出目标FROM来源WHERE条件groupby分组依据orderby排序依据SQL数据操纵语言InsertDeleteUpdateSQL数据定义语言Create/alter/drop,62,江西财经大学信息管理学院数据库应用课程组,知识补充：子查询,Select查询结果为数据集利用Wherein(数据集)实现多表查询示例1：查找”注册会计师专业”的同学名单Select姓名from学生表where班级编号in()Select姓名from学生表where班级编号in(Select班级编号from班级表where班级名称like“注册会计师%”)等价于？示例2：教学管理数据库studentdb中求与欧阳思思同一学院的同学名单,Select班级编号from班级表where