VFP课件第10讲.ppt

1 第10讲结构化查询语言SQL 2 返回 10 1连接查询 10 3集合查询 10 2嵌套查询 10 4视图的定义 2 如果查询涉及两个或两个以上的表 则需要将相关的表按某个字段连接起来 这种查询称为连接查询 连接查询包括等值连接 自然连接 自连接 外连接和复合连接等多种形式的查询 10 1 1等值连接查询连接查询中用来连接两个表的条件称为连接条件 等值连接的格式为 其中 引用不同表中的字段需在字段名前加表名和 例10 1 从学生表 xsb 和成绩表 cjb 两个表中 检索所有成绩多于85分的学号 姓名 课程号 学期和成绩 解 SELECTxsb 学号 姓名 课程号 学期 成绩FROMxsb cjb WHERE 成绩 85 AND xsb 学号 cjb 学号 注意 本例中 既有查询条件 成绩 85 又有联接条件 xsb 学号 cjb 学号 引用两个表都有的字段时 必须前面加表的名称和 10 1连接查询 3 例10 2 从学生表 xsb 和成绩表 cjb 两个表中 检索所有同学各们课程考试成绩的最高分 最低分 总分和平均分 并分专业按照平均分从高到低排序 解 SELECTxsb 学号 专业 max 成绩 as最高分 min 成绩 as最低分 sum 成绩 as总分 AVG CJ as平均分 FROMXSB CJB WHEREXSB 学号 CJB 学号 GROUPBYXSB 学号ORDERBY专业 6DESC注意 双重排序的表示格式 4 10 1 2超连接查询超连接查询包括内部连接 左连接 右连接和全连接等几种情况的查询 在ViualFoxPro中 与超连接运算有关的命令格式如下 FROM INNER LEFT RIGHT FULL JOINON 可以看出 超连接查询是通过FROM短语给出连接类型 用ON短语给出连接条件进行的 1 内部连接 INNERJOIN 在内部连接查询中 只有满足连接条件的记录才会出现在查询结果中 例10 3 从XSB和CJB表中查询所有有考试成绩的同学的学号 姓名 专业 课程号 学期和成绩 按学号排序 解 SELECTXSB 学号 姓名 专业 课程号 学期 成绩FROMXSB INNERJOINCJBONXSB 学号 CJB 学号 ORDERBYXSB 学号查询结果如图10 2 其中INNER可以省略 5 2 左连接 LEFTJOIN 在左连接查询中 除满足连接条件的记录出现在查询结果中外 LEFTJOIN左侧表中不满足连接条件的记录也会出现在查询结果中 右侧表对应的字段值为 NULL 例10 4 从XSB和CJB表中查询所有的同学的学号 姓名 专业 课程号 学期和成绩 包括还没有考试成绩的同学 解 SELECTXSB 学号 姓名 专业 课程号 学期 成绩FROMXSB LEFTJOINCJBONXSB 学号 CJB 学号查询结果如图10 3 在查询窗口可以看到 目前还没有课程考试成绩的同学 对应的课程号 学期 成绩值都是NULL 6 3 右连接 RIGHTJOIN 在右连接查询中 除满足连接条件的记录出现在查询结果中外 RIGHTJOIN右侧表中不满足连接条件的记录也会出现在查询结果中 左侧表对应的字段值为 NULL 例10 5 从CJB和KCB表中查询所有的同学的学号 课程号 课程名 学分 学期和成绩 包括还没有考试成绩的课程 解 SELECT学号 KCB 课程号 课程名 学分 学期 成绩FROMCJB RIGHTJOINKCBONKCB 课程号 CJB 课程号查询结果如图10 4 在查询窗口可以看到 目前还没有考试成绩的课程 对应的学号 学期 成绩值都是NULL 4 全连接 FULLJOIN 在全连接查询中 除满足连接条件的记录出现在查询结果中外 两个表中不满足条件的记录也会出现在查询结果中 7 10 1 3多表连接多表连接指两个以上的表连接 利用WHERE子句或超级连接的方式都可以实现 多表连接需要注意表连接的顺序 相邻的两个表必须可以建立关联 例10 6 从Xsb Cjb和Kcb表中查询所有的同学的学号 姓名 专业 课程名 学分 学期和成绩 并按学号排序 解 分别用两种连接方式解答方法1 用等值连接SELECTXsb 学号 姓名 专业 课程名 学分 学期 成绩FROMXsb Cjb Kcb WHERECjb 课程号 Kcb 课程号 AND Xsb 学号 Cjb 学号 ORDERBYXsb 学号方法2 用超连接 内连接 SELECTXsb 学号 姓名 专业 课程名 学分 学期 成绩 FROMXsbJOINCjbJOINKcb ONCjb 课程号 Kcb 课程号ONXsb 学号 Cjb 学号 ORDERBYXsb 学号 8 10 1 4自连接查询表的自连接是一个表与其自身进行的连接 进行这种连接运算时 需要在FROM短语中为表定义别名 其方法是 可以进行自连接查询的表必须有两个值域完全相同的字段 且它们之间存在逻辑联系 如KCB 课程表 就符合自连接查询条件 因为它的先修课号是某门课程的课程号 就形成了自连接关系 如图10 6所示 9 例10 7 在课程表中列出有先修课的课程及其先修课名单 解 这是一个典型的自连接查询 本身的两个字段相互关联 1 N 因此在查询中把一个表当作两个表使用 临时给两个别名 F S SELECTF 课程名 的先修课是 S 课程名FROMkcbF kcbS WHEREF 先修课号 S 课程号查询结果如图10 7所示 10 所谓嵌套查询 是指在WHERE子句条件中涉及字段与另一个SELECT查询结果的比较 也就是说 嵌套查询是指在一个SELECT语句中包含另一个SELECT语句的查询 SQL支持嵌套查询 这正是SQL结构化的具体体现 处于内层的查询称为子查询 处于外层的查询称为父查询 在子查询的SELECT语句不能使用ORDERBY子句 即ORDERBY子句只能对最终查询结果排序 10 2 1带有谓词IN的子查询由于子查询的结果是记录的集合 故常使用谓词IN来实现 例10 8 检索出那些至少有一门课程不及格学生的学号 姓名和专业 解 学生的信息在XSB DBF表中 而成绩在CJB DBF表中 所以先在CJB DBF表中检索出有不及格的学号 再据此到学生表检索相关学生的信息 SELECT学号 姓名 专业FROMxsbWHERE学号IN SELECT学号FROMcjbWHERE成绩 60 10 2嵌套查询 11 例10 9 检索尚无考试成绩的同学信息 解 SELECT FROMxsbWHERE学号NOTIN SELECTDIST学号FROMcjb 10 2 2带有比较运算符的子查询带有比较运算符的子查询 是指在父查询WHERE子句中 使用比较运算符进行比较的查询 当用户能确切知道子查询返回的是单值时 可以使用 等比较运算符进行查询 例10 10 查询汪远东同学的考试成绩信息 解 SELECT FROMCJBWHERE学号 SELECT学号FROMXSBWHERE姓名 汪远东 因一位学生只能有一个学号 即内查询结果是单值 因此可以用 代替IN 12 10 2 3带有ANY SOME或ALL谓词的子查询使用谓词ANY SOME ALL的WHERE子句中的条件书写格式为 ANY ALL SOME 子查询 其中 ANY和SOME是作用相同的谓词 ANY代表某个值 ALL代表所有值 从上述格式可以看出 使用谓词ANY或ALL时必须同时使用比较运算符 例如 ANY的语义为 大于子查询结果中的某个值 ALL的语义为 大于子查询结果中的所有值 例10 11 查询其他专业中比 信息管理 专业任一同学年龄都小的学生学号 姓名 专业和出生日期 解 SELECT学号 姓名 专业 出生日期fromxsbWHERE出生日期 all SELECT出生日期FROMXSBWHERE专业 信息管理 执行结果如图10 10 13 例10 12 查询 信息管理 专业年龄最小的学生的专业和出生日期 解 SELECT专业 max 出生日期 fromxsbWHERE专业 信息管理 执行结果如图10 11 对比两个查询结果 可以证实例题10 11的结果是正确的 例10 13 从学生表和成绩表中 检索出已经考过 1002 和 1003 两门课程的学生的学号和姓名 解 SELECT学号 姓名FROMxsbWHERE学号IN SELECT学号FROMcjbWHERE课程号 1002 AND学号IN SELE学号FROMcjbWHERE课程号 1003 上例说明 一个父查询可以有两个并列的子查询 14 10 2 4带有EXISTS谓词的子查询EXISTS也称为存在量词 WHERE子句中使用量词EXISTS表示当子查询的结果非空时 条件为真 反之 则为假 EXISTS前也可以加NOT 表示检测条件为 不存在 例10 14 检索至少有一门考试成绩的同学的信息 解 Select fromxsbwhereexists select fromcjbwhere学号 xsb 学号 由EXISTS引出的子查询 其目标列表达式通常都用 表示 原因是带EXISTS的子查询仅测试子查询的返回值是否为空 也就是说 给出列名将毫无意义 15 10 2 5内外层互相关嵌套查询即内层查询的条件需要需要外层查询提供值 而外层查询的条件需要内层查询的结果 此时需要给内外层的表指定别名 例10 15 在成绩表中查询每个同学取得最好成绩的课程及成绩 解 selectout 学号 out 课程号 out 学期 out 成绩fromcjbout where成绩 selectmax 成绩 fromcjbinwhereout 学号 in 学号 或可以更简洁 select fromcjboutwhere成绩 selectmax 成绩 fromcjbinwhereout 学号 in 学号 可以看出 内层查询的结果 MAX 成绩 是外层查询的条件 而外层查询的结果 OUT 学号 是内外层查询的相关条件 执行结果如图10 12 16 SQL支持集合的并 UNION 运算 需要注意的是 参加并运算操作的两个查询语句 其结果应具有相同的字段个数且对应字段的数据类型也应相同 例10 16 查询计算机科学专业的男同学和市场营销专业女同学的名单 显示学号 姓名 专业 性别和出生日期 解 SELECT学号 姓名 专业 性别 出生日期fromxsbwhere 专业 计算机科学 and 性别 男 Union SELECT学号 姓名 专业 性别 出生日期fromxsbwhere 专业 市场营销 and 性别 女 执行结果如图10 13 10 3集合查询 17 前面我们学习了用向导或设计器创建视图 实际上 用SQL命令也可以直接定义视图 格式 CREATEVIEW AS命令中的SELECT语句 就是本讲前面介绍的