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 江西财经大学信息管理学院 数据库应用 课程组 什么是视图 基本表 视图定义Createview asselect 执行显示 10 江西财经大学信息管理学院 数据库应用 课程组 5 1SQL语言概述 5 基本表和视图实际的应用中 由于安全控制的原因 在数据库中 一般用户通常不能看到所有的基本表 基本表通常只有数据库管理员DBA可以看到 用户看到的是与他们自己业务相关的视图 View 数据库中只存放视图的定义而不存放视图对应的数据 这些数据仍存放在导出视图的基本表中 因此视图是一个虚表 视图在概念上与基本表等同 用户可以在视图上再定义视图 11 江西财经大学信息管理学院 数据库应用 课程组 5 2数据查询 SELECT语句单表查询多表查询创建和使用视图 12 江西财经大学信息管理学院 数据库应用 课程组 SELECT语句 一般格式 SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC 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 21 江西财经大学信息管理学院 数据库应用 课程组 5 2 1单表查询 9 4 确定集合例5 14 求籍贯为 江西南昌 或 吉林 的学生学号 姓名和籍贯 select学号 姓名 性别 籍贯 from学生表 where籍贯in 江西南昌 吉林 例5 15 求籍贯不为 江西南昌 和 吉林 的学生学号 姓名和籍贯 select学号 姓名 性别 籍贯 from学生表 where籍贯notin 江西南昌 吉林 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 对查询结果排序用户可以用ORDERBY子句对查询结果按照一个或多个属性列的升序 ASC 或降序 DESC 排列 缺省值为升序 例5 23 查询所有学生的入学成绩 查询结果按入学成绩的降序排列 select学号 姓名 入学成绩 from学生表 Orderby入学成绩desc 24 江西财经大学信息管理学院 数据库应用 课程组 5 2 1单表查询 12 4 对查询结果使用聚集函数 25 江西财经大学信息管理学院 数据库应用 课程组 5 2 1单表查询 13 4 使用聚集函数例5 26 求学生表中入学成绩在600分以上的人数 selectcount from学生表 where入学成绩 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 an B b1 b2 bm A B 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学号 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 5VFP查询和视图设计器 手工编写要求对数据库关系模式清晰图形化的SQL查询语句编写无需手工编写机房实验课时讲解 61 江西财经大学信息管理学院 数据库应用 课程组 第5章小结 SQL查询语言SELECT输出目标FROM来源WHERE条件groupby分组依据orderby排序依据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日 且尚未归还的图书编号和图书名称