VF中SQL语言的应用1.ppt

1检索学生表中的学号、姓名、性别和籍贯 2求所有学生在2004年的年龄 3求1987年以后出生的学生学号和姓名 4查找入学成绩大于等于600的同学的学号、姓名和入学成绩 5查找入学成绩在570分以上的女性学生，显示学号、姓名、性别和入学成绩 6入学成绩在500到600间的学生学号、姓名和入学成绩 7求籍贯为“江西南昌”或“吉林”的学生学号、姓名和籍贯 8查找江西籍的男性学生的学号和姓名 9求学生表中入学成绩在600分以上的人数 10求学生表中入学成绩的平均成绩 11按课程类别号，求各种类别课程的门数,14:05:42,查询语句的一般格式：,SELECT ALL | DISTINCT field1,field2,. FROM table1,table2,. WHERE “conditions“ GROUP BY “field“ HAVING “conditions “ ORDER BY “field“ ASC | DESC ,select where 姓名 like “李%” select .where at(“李”，姓名）0 select where like(“李*”,姓名) 三个是等价的。 Like是SQL语言中的，因此，通配符是:%和_. 说明： _ 替代一个字符， %替代多个字符。 Like()是VFP自带的函数，通配符是：*和?. 说明： ？ 替代一个字符， * 替代多个字符。,简单的计算查询: 计算平均值avg(cj) as 平均成绩 计算总分:sum(cj) as 总成绩 求最高值:max(cj) as 最高分 求最小值:min(cj) as 最低分,4.2.2 多表查询,一、多表查询的工作原理 多表查询：from子句包括多个数据表 例： select * ; from 学生表,成绩表 结果：如下图 结果集在横向上（字段数）是两表的字段数相加；在纵向上（记录数）是两表的记录数相乘。 即如果SELECT从两表检索结果，且不带WHERE子句的关键字相等约束时，结果存在组合爆炸的问题，这会产生很多无用的垃圾数据。,select * from 学生表,成绩表的结果,带Where子句的多表查询,SELECT从两表检索结果时，要得到有效的数据必须带WHERE子句。 通常二个表之间必须有主外键的约束，即二个表中有共同的字段（或字段集），这个字段或字段集在一个表中为主键，在另一个表中为外键。如果二表是多对多的关系，也可以转化为二个一对多的关系。 我们最常使用自然联接对两表进行查询。 自然连接实际上指定了结果集必须满足的条件。,例4-31：将学生表和课程表进行自然联接，求结果集。 select * from 学生表,成绩表; where 学生表.学号 = 成绩表.学号,4.2.2 多表查询(工作原理),理解：例4-31中的子句“WHERE 学生表.学号 = 成绩表.学号”(或from 学生表 inner join 成绩表on 学生表.学号 = 成绩表.学号)表示的是“学生表”和“成绩表”进行自然联接操作。它的含义是判断“学生表”中的学号与“成绩表”中的学号，当它们相等时，这条记录才加入最终的输出结果集中。 从概念上来说，相当于先作不带WHERE子句的运算，得到一个大的结果集后，再判断“学号_a”是否等于“学号_b”，如果相等才认为是所要的结果。,上述SELECT语句的等价SQL语句如下： select * ; from 学生表 inner join 成绩表 ; on 学生表.学号 = 成绩表.学号 inner join(内联接)：这是最普通的联接类型。只输出满足连接条件的元组 outer join(外联接)：分为left outer join(左外联接)和right outer join(右外联接)，以指定表为连接主体，将主体表中不满足连接条件的元组一并输出 full join：完整外部联接，返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。,4.2.2 多表查询(工作原理),4.2.2 多表查询(工作原理),一对多的自然连接 在数据库中，使用最多的是一对多的关联。这里关联有一端为参照表（也称为多表），另一端为被参照表（也称为一表），二个表之间必须有主外键的约束，即二个表中有共同的字段（或字段集），这个字段或字段集在一个表（一表）中为主键，在另一个表（多表）中为外键。 自然连接的WHERE子句中的条件为：两个表在公共字段（集）上的值对应相等。 如：WHERE 学生表.学号 = 成绩表.学号 一对多的自然联接的结果是一个关系表，关系表的结果为：横向上（字段数）为两表的字段相叠加；纵向上（记录数）以多表的记录为最终结果。,4.2.2 多表查询(工作原理),例4-32：求课程成绩在85分以上人的学号、姓名和成绩 select 学生表.学号,姓名,成绩; from 学生表,成绩表; where 学生表.学号 = 成绩表.学号; and 成绩=85 注：为简化SELECT的书写，SQL中允许使用表的别名，例如上例可以改写为 select a.学号,姓名,成绩; from 学生表 a,成绩表 b; where a.学号 = b.学号; and 成绩=85,这里的a和b分别是学生表和成绩表的别名,例4-33：将“学生表”、“成绩表”和“课程表”进行自然联接，求结果 select *; from 学生表,成绩表,课程表; where 学生表.学号 = 成绩表.学号; and 课程表.课程号 = 成绩表.课程号 分析：将三个表进行自然连接，需要两个连接条件。先将“学生表”和“成绩表”自然联接，得到一个关系表，其中包含字段是“学生表”和“成绩表”字段的叠加。再将得到的关系表与“课程表”进行自然联接。最终的结果是将三个表的字段叠加，但记录是在“成绩表”的基础上扩展相关字段得到。 当然也可以理解为先进行“课程表”与“成绩表”的自然联接，然后再用得到的关系与“学生表”进行自然联接。,4.2.2 多表查询(工作原理),4.2.2 多表查询(工作原理),三个表关联的结果，结果记录以多表为准,4.2.2 多表查询(工作原理),问题： 什么时候要用到多表自然连接查询？ 如何书写自然连接查询？ 数据库导航：就是根据给出的已知条件，求需要的数据。这里已知条件是在一个表中的某个字段取值，所求数据是我们感兴趣的字段。那么我们要从已知的数据表出发，通过表间的关联到达目的表，最后根据题目要求筛选相关的字段和记录。,4.2.2 多表查询(工作原理),如何导航：依据数据库模式,4.2.2 多表查询(举例),二、多表的自然联接查询例子 例4-34：求“江华”的各科成绩 分析：已知条件为“学生表”的“姓名”字段，其内容等于“江华”，待求的是“课程表”中的“课程号、课程名”和“成绩表”中的“成绩”。 select 课程表.课程号,课程名,成绩; from 学生表,成绩表,课程表; where 学生表.学号 = 成绩表.学号; and 课程表.课程号 = 成绩表.课程号; and 姓名 = “江华“,例4-35：求计算机科学技术2003-01班的所有成绩单 分析：已知条件为“班级表”中的“专业名称”，待求为“学生表”的“姓名”；“课程表”的“课程号、课程名”；“成绩表”的“成绩”。 select 姓名,课程表.课程号,课程名,成绩; from 学生表,成绩表,课程表,班级表; where 学生表.学号 = 成绩表.学号; and 课程表.课程号 = 成绩表.课程号; and 班级表.班级号 = 学生表.班级号; and 专业名称 = “计算机科学技术2003-01班“,4.2.2 多表查询(举例),例4-36：求“计算机科学技术2003-01班”的微机操作成绩单。 select 姓名,课程表.课程号,课程名,成绩; from 学生表,成绩表,课程表,班级表; where 学生表.学号 = 成绩表.学号; and 课程表.课程号 = 成绩表.课程号; and 班级表.班级号 = 学生表.班级号; and 专业名称 = “计算机科学技术2003-01班“; and 课程名 = “微机操作“,4.2.2 多表查询(举例),下面我们给出多表查询中使用GROUP BY子句的例子。 例4-37：求每个学生所修的总学分数。 select 姓名,sum(学分); from 学生表,成绩表,课程表; where 学生表.学号 = 成绩表.学号; and 课程表.课程号 = 成绩表.课程号; group by 姓名,4.2.2 多表查询(举例),例4-38：求总学分在6分以上的学生姓名。 select 姓名,sum(学分) ; from 学生表,成绩表,课程表 ; where 学生表.学号 = 成绩表.学号 ; and 课程表.课程号 = 成绩表.课程号; group by 姓名 ; having sum(学分)= 6 HAVING子句的作用：可以过滤GROUP BY子句的结果，只有满足HAVING短语指定条件的组才输出,4.2.2 多表查询,HAVING短语与WHERE子句的区别：作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组。 HAVING短语作用于组，从中选择满足条件的组。,SELECT语句的完整格式,SELECT ALL | DISTINCT TOP PERCENT . AS , . AS . FROM FORCE ! AS INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER JOIN ! AS ON INTO | TO FILE ADDITIVE | TO PRINTER PROMPT | TO SCREEN PREFERENCE PreferenceName NOCONSOLE PLAIN NOWAIT WHERE AND .AND | OR AND | OR . GROUP BY , . HAVING UNION ALL ORDER BY ASC | DESC , ASC | DESC .,第四章 VF中SQL语言的应用,4.1 SQL语言概述 4.2 数据查询 4.3 数据操纵 4.4 数据定义 4.5 VF查询设计器,4.3 数据操纵,数据操纵语句包括： 插入(INSERT)、删除(DELETE)和更新(UPDATE)三种。 注意：这里的DELETE语句是SQL语句，它不是我们前面所学的Visual FoxPro的记录删除语句。SQL中的DELETE语句是SQL中的标准语句，用它编写的数据库操纵语句有很好的可移植性，而Visual FoxPro的记录删除语句DELETE是Visual FoxPro特有的语句。,4.3.1 插入记录,语句格式： INSERT INTO ( , , .) VALUES ( , , .) 功能：向表中插入一条记录。,INTO子句 指定要插入数据的表名及属性列 属性列的顺序可与表定义中的顺序不一致 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致 指定部分属性列：插入的元组在其余属性列上取空值 VALUES子句 提供的值必须与INTO子句匹配 值的个数 值的类型,4.3.1 插入记录,例4-41：向学生表中插入一条记录。 use 学生表 insert into 学生表; values (“0043022“,; “李莉“,; “女“,; 1989/06/01,; .F.,; “CPA0403“,; “北京“,; 588,; ,; ) 使用该语句时，如果违法数据表的约束，即表中要求不能为空的或主键冲突的数据将不能插入数据表中。,4.3.1 插入记录,例4-42：向学生表插入一条记录，不包括简历和照片两字段。 insert into 学生表; (学号,姓名,性别,出生日期,少数民族否,班级号,籍贯,入学成绩); values (“0043023“,; “张强“,; “男“,; 1989/08/01,; .F.,; “CPA0403“,; “辽宁大连“,; 592),4.3.1 插入记录,4.3.2 删除记录,语句格式： DELETE FROM ! WHERE AND | OR . 功能：从表中删除满足条件的记录，注意该语句只是标上删除标记，要物理删除必须使用PACK语句。如果没有指定条件将删除所有记录，此时如果希望恢复请使用RECALL ALL