Access数据库4章节查询命令的功能与使用.ppt

第四章查询 使用数据库管理系统的最主要的目的就是希望非常系统且有组织的管理数据 并且能够以非常有效率的方式来存取所需的数据 因此 从数据库的一个或多个数据表中提取出所需的数据集合并针对该数据集合加以汇总 统计或分析这项最例行的工作而言 非 查询 莫属 查询 之前 查询虽然是一个储存在数据库中的数据库对象 不过查询中所存放的并不是查询所得的信息 而是如何去取得所需信息的方法与定义 SQL命令语句 即 当运行查询时 这些信息便会取得 但是这些通过查询所得的信息并不会储存在数据库中 换句话说 当关闭查询后 查询记录也随之清空 保存下来的只有查询方式 再次打开查询将重新从表中调入数据 因此 我们建立查询的目的就是在需要取得特定信息时立即运行特定的查询来获得所需的信息 4 1查询的功能 指定需要查看的数据表中的信息 可以是一个表或多个表 通过指定查询条件来查看表中符合条件的数据和字段 对表中的数据进行统计 包括汇总 排序等 将需要的数据信息提取出来建立一个新的数据表 在财务管理中通过查询结果可以创建财务报表 使用查询后的数据生成图表 直观地表述数据 查询与数据表的关系数据来源不可同名查询保存定义 不是数据所以 数据表负责保存记录 查询负责取出记录 二者在目的上完全相同 都可以将记录以表格的形式显示在屏幕上 这些记录的进一步处理是用来制作窗体 报表和数据访问页 4 2查询的类型 1 选择查询选择查询是最常见的查询类型 它是按照规则从一个或多个表 或其他查询中检索数据 并按照所需的排列顺序显示出来 包括 简单选择查询 重复项查询 汇总查询和不匹配查询 2 参数查询参数查询可以在执行时显示自己的对话框以提示用户输入信息 它不是一种独立的查询 只是在其他查询中设置了可变化的参数 3 交叉表查询使用一个数据表或查询作为数据源 通过交叉表查询返回字段的统计信息 如合计和平均值 并将它们分组 一组显示在电子表格的左侧 一组显示在上部 使用交叉表查询可以计算并重新组织数据的结构 这样可以更加方便地分析数据 4 操作查询使用操作查询只需进行一次操作 就可对许多记录进行更改和移动 有四种操作查询 如下所示 删除查询 可以从一个或多个表中删除一组记录 例如 可以使用删除查询来删除所有毕业学生的记录 使用删除查询 通常会删除整个记录 而不只是记录中所选择的字段 更新查询 可以对一个或多个表中的一组记录做全局的更改 例如 可以将所有学生的语文成绩提高10个百分点 或将某一工作类别的人员的工资提高5个百分点 使用更新查询 可以更改已有表中的数据 追加查询 可以将一个或多个表中的一组记录添加到一个或多个表的末尾 例如 假设用户获得了一些新的客户以及包含这些客户信息的数据表 若要避免在自己的数据库中键入所有这些信息的麻烦 最好将其追加到 客户 表中 生成表查询 可以根据一个或多个表中的的全部或部分数据新建表 生成表查询有助于创建表以导出到其他数据库中 5 SQL查询SQL StructureQueryLanguage 是一种结构化查询语言 是数据库操作的工业化标准语言 可以使用SQL来查询 更新和管理任何数据库系统 用户在设计视图中创建查询时 Access将在后台构造等效的SQL语句 有一些SQL查询 只能在SQL视图中创建 称为 特定查询 包括 传递查询 传递查询可以直接向ODBC数据库服务器发送命令 联合查询 联合查询可使用Union运算符来合并两个或更多选择查询结果 数据定义查询 使用SQL语句在查询的过程中对远程的数据库进行修改 删除 创建等操作 子查询 在SQL查询语句中嵌套查询 4 3创建查询 创建查询的方式1 查询设计方式在Access中创建查询有三种方式 查询向导 设计视图 SQL视图 注意 简单的选择查询 包括 查找重复项查询 和 查找不匹配项查询 交叉表查询一般使用向导创建查询 SQL查询 SQL程序 在SQL视图中创建 其他查询一般在设计视图中创建 2 三种视图在Access中查询有三种视图 设计视图 在 设计视图 中不仅可以创建各种查询 而且可以编辑已有查询 数据表视图 数据表视图 显示的是查询结果 是一个动态记录集 SQL视图 在 SQL视图 中创建SQL查询语句 生成查询程序 注意 通过选择 视图 菜单中的相应选项 或点击工具栏中的 视图 图标的下拉按钮 在下拉表中选择相应选项 可实现在三种视图间的转换 简单查询例1 使用向导 在 教学信息管理 数据库中查找并显示 教师 表中的 姓名 性别 职称 和 专业 4个字段 单个表 例2 使用简单查询向导 查找每个学生的选课成绩 并显示学号 姓名 课程和考分4个字段 多个表 学生表 课程名称表 成绩表 1 选择需要创建查询的表2 选择在查询中包括的字段3 显示已选择的字段 4 3 1使用向导创建查询 4 3 1使用向导创建查询 交叉表查询交叉表查询以水平和垂直方式对记录进行分组 并计算和重构数据 使查询后生成的数据显示得更清晰 结构更合理 紧凑 还可以对数据进行汇总 计数及求平均值等操作 交叉表查询是将来源于某个表中的字段进行分组 一组列在数据表的左侧 一组列在数据表的上部 然后在数据表行与列交叉处显示表中某个字段的各种计算值 例 使用交叉表查询向导 创建统计每班男女生人数的交叉表查询 重点思考行与列的选择 注意 交叉表查询向导 创建的查询 数据源必须是来源于一个表或查询 如果来自多个表 可以先建立一个查询 然后再以此查询作为数据源 当然 用查询的设计视图来做交叉表查询 数据源可以是多个表或查询 交叉表的实际工作应用 举例 各连锁店各种商品的进货量 销售量等不同部门薪资平均值各部门员工各个月份的出生人数或员工总人数每一位客户在每一年各季度的订购金额 各年份的订购总金额各类产品各年度每季的订货平均与总金额 4 3 1使用向导创建查询 查找重复项查询向导对某些具有相同字段值的记录进行统计计数 例 查找教师表中各种职称人数的统计查询 思考 可不可以用交叉表完成此任务 如果不能 为什么 查找不匹配项查询向导在一个表中查找与另一个中没有相关记录的记录 例 查找哪些在成绩表中没有他们的选课成绩的学生记录 运算符 函数与表达式 基础知识补充 要点 表达式的格式函数的类型和意义运算符的类型和意义创建复杂的表达式在工作中实际应用函数 表达式 表达式是一个或一个以上的字段 函数 运算符 内存变量或常量的组合 字段 工资 12常量运算符注意事项 将字段名包含在一对中括号中 将常量字符串包含在一对单或双引号中 引号必须是英文半角符号 例 姓名 先生 小姐 将日期时间包含在一对 号中 例 2006 09 02AM10 10 10 204 使用运算符 或 来连接 文本 类型字段或字符串 收件人地址 邮政编码 家庭地址 收件人地址 邮政编码 家庭地址 算术运算符 逻辑运算符和比较运算符 举例 使用逻辑运算符和比较运算符设置数据有效性规则单价不能比0小 数量不小于1全勤奖金不是0就是2000and 100and 200 其他运算符 例 名 Like 浩 如果用户输入的名字以 浩 开头 并且是两个汉字 则返回true ID Like AB 用户输入的ID字段的内容必须以 AB 字符开头 后面跟3个数字 LastName Willams Or LastName Johnson 如果LastName字段为Willams或Johnson 结果为trueNot 教授 表示查询的条件是职称除了教授以外的所有教师In 教授 副教授 职称为教授或副教授 函数 日期 时间CDate将字符串转化成为日期selectCDate 2005 4 5 Date返回当前日期DateAdd将指定日期加上某个日期selectdateAdd d 30 Date 将当前日期加上30天 其中d可以换为yyyy或H等DateDiff判断两个日期之间的间隔selectDateDiff d 2006 5 1 2006 6 1 返回31 其中d可以换为yyyy m H等DatePart返回日期的某个部分selectDatePart d 2006 5 1 返回1 即1号 d也可以换为yyyy或mDay返回日期的d部分 等同于datepart的d部分Hour返回日期的小时IsDate判断是否是日期 是日期返回 1 不是日期返回0Minute返回日期的分钟部分Month返回日期的月份部分Now返回当前时间 完整时间 包括年月日小时分秒 Second返回日期的秒部分Time返回当前的时间部分 即除去年 月 日的部分 Weekday返回某个日期的当前星期 星期天为1 星期一为2 星期二为3 例如selectweekday now Year返回某个日期的年份 检查IsEmpty检测是否为空 不过经测试 不管什么情况 都返回0 IsNull检测是否为Null值 null值返回0 非null值返回 1IsNumeric检测是否为数字 是数字返回 1 否则返回0 算术Abs绝对值Atn返正切值 Cos余弦值Exp返回e的给定次幂 Fix返回数字的整数部分 即小数部分完全截掉 Int将数字向下取整到最接近的整数 其实等同于Fix Log返回以E为底的对数值Rnd返回一个0到1之间的随机数值Sgn返回数字的正负符号 正数返回1 负数返回 1 0值返回0 Sin正弦值Sqr返回平方根值Tan正切值 程序流程Choose根据第一参数 返回后面字符串组的值 SelectChoose 1 a b c 返回a 将1改成2后 返回b 改成3后 返回c 第一个参数也可以是某个字段 IIF根据表达式返回特定的值SelectIIF 3 1 OK False 返回OK Sql合计函数Avg取字段平均值Count统计记录条数Max取字段最大值Min取字段最小值StDev估算样本的标准差 忽略样本中的逻辑值和文本 StDevP计算以参数形式 忽略逻辑值和文本 给出的整个样本总体的标准偏差 Sum计算字段的总合Var估算样本方差 忽略样本中的逻辑值和文本 VarP计算整个样本总体的方差 忽略样本总体中的逻辑值和文本 文本Asc返回字母的Acsii值 selectAsc A 返回65Chr将ascii值转换到字符selectchr 65 返回 A Format格式化字符串 SelectFormat now yyyy mm dd 返回类似于 2005 04 03 SelectFormat 3 9 0 00 返回0 33InStr查询子串在字符串中的位置selectInstr abc a 返回1 selectInstr abc f 返回0LCase返回字符串的小写形式Left左截取字符串Len返回字符串长度LTrim左截取空格Mid取得子字符串selectmid 123 1 2 asmidDemo返回12Right右截取字符串RTrim右截取空格Space产生空格selectSpace 4 返回4个空格StrComp比较两个字符串是否内容一致 不区分大小写 selectStrComp abc ABC 返回0 selectStrComp abc 123 返回 1Trim截取字符串两头的空格UCase将字符串转大写 4 3 2使用设计视图创建查询 建立查询的步骤打开查询设计视图加入所要查询的数据表与查询查询的对象不仅仅是数据表 也可以是另外一个查询即多层次查询 查询的对象也可以是链接数据表 Excel SQLServer 文本文件等 决定查询的类型选择要出现在查询结果中的字段或设定输出表达式视需要设定查询字段的属性排序查询结果 选择性的 指定查询的条件将查询分组 选择性的 指定分组条件运行并验证查询 例 查询学生的学号 姓名 课程和考分 在设计视图中创建查询在设计视图窗口中的操作 与表相似 插入新字段移出字段移动字段添加表或查询删除表和查询排序查询的结果 由左至右 创建过程 打开查询设计视图加入所要查询的数据表与查询 决定查询的类型 不选择的话默认是选择查询 选择要出现在查询结果中的字段或设定输出表达式 视需要设定查询字段的属性 运行并验证查询思考 如何判断查询结果是否正确 一般来说 查询使用的数据表越多 查询结果的记录数可能越少 因为交集会越来越少 若查询结果的记录数比原始数据表的记录数还多 说明查询设计错误 选择查询例1 创建带条件的查询查找1988年出生的1年级男生 并显示 姓名 性别 生日 字段查找 职称 为教授 副教授 或简历字段为空的教师的任课情况 4 4查询实例 例2 查询中函数的使用查看本月生日的学生 查询结果按 生日 的降序排序在教师表中 由性别字段的内容得到称呼字段 IIf 的使用 分析 本例中由于要统计的每个学生出生日期月分的数据在 学生 表中没有相应的字段 所以添加了一个计算字段 是系统自动产生的 即 表达式1 值引自Month 生日 该值与条件行中的Month Date 相等时 记录符合条件 例3 在查询中进行计算统计各类职称的教师人数统计2年级学生已修课程的总学分 参数查询使用参数查询可以在同一查询中根据输入的参数不同而得到不同的查询结果 其独特之处在于运行参数查询时它们会提示输入所需的数据 如要查找人的姓名 注意 使用参数查询不是输入实际值数据 而是提示查询用户输入的条件值 设置方法 在查询网格中输入提示文本 并用 将其括起来 运行时 该提示文本将显示出来 例 根据输入的优秀标准 统计多门优秀的学生记录无论是哪种查询 条件一栏是作为判断的 一般不用显示在最后的结果中 中括号在查询中的意义 字段名和参数 查询可多种设计 交叉表查询交叉表查询是以水平和垂直方式对记录进行分组 并计算和重构数据 使查询后生成的数据显示得更清晰 结构更紧凑 合理 还可以对数据进行汇总 计数及求平均值等操作 设计重点 用向导创建交叉表查询只可使用一个表或一个查询 如需使用的字段在多个表中 需要先将所需字段组合在一个查询内 再以此查询为数据源建立交叉表查询 一个列标题 只能是一个字段作为列标题 多个行标题 可以指定多个字段作为行标题 但最多3个 一个值 设置为 值 的字段是交叉表中行标题和列标题相交单元格内显示的内容 值 的字段也只能有一个 且其类型通常为 数字 在交叉表查询中使用参数查询 参数必须定义 使用设计视图创建交叉表实例 统计某个年级必修课的成绩说明 是否类型数据 1表示是 0表示否 在 查询 参数命令中定义 操作查询可以通过查询的运行对数据做出变动 可以大批量地更改和移动数据 操作查询是建立在选择查询的基础上 对原有的数据进行批量的更新 追加和删除 或者创建新的数据表 但其结果不像选择查询那样运行只显示查询结果 操作查询运行后改变了数据源的数据 所以需要再打开目的表 即被更新 追加 删除或生成的表 才能了解操作查询的结果 由于操作查询将改变数据表的内容 而且某些错误的操作查询可能会造成数据表中数据的丢失 因此用户在进行操作查询之前 应该先对数据库或表进行备份 生成表查询利用一个或多个表中的全部或部分数据创建新表 可以对一些特定的数据进行备份 例 将2门以上 含2门 不及格的学生记录生成一个新表 说明 本例中共使用了5个字段 其中的第5列 考分 字段是作为条件判断的字段 查询结果中不显示该字段 查询生成的数据表是4个字段 表中字段的数据来源于查询的结果 追加查询例 将1门不及格的学生添加到生成2门以上不及格学生表中 更新查询例 将云南学生的所有课程的考分加上2分 删除查询例 将成绩备份表中考分低于60分的记录删除 SQL查询 SQL语言包含4个部分功能 数据查询 SELECT语句 数据操纵 INSERT UPDATE DELETE语句 数据定义 CREATE DROP等语句 数据控制 COMMIT ROLLBACK等语句 SQL查询 CREATE命令CREATE命令用来创建表 视图或索引 其命令格式为 CreateTable 列完整性约束条件 列完整性约束条件 表完整性约束条件 例 创建一个教师信息表 包括 编号 姓名 职称 出生日期 简历等字段 其中 编号字段为主索引字段 不能为空 且值唯一 CreateTable教师信息 编号char 9 notnullunique 姓名char 9 职称char 10 出生日期date 简历memo DROP命令DROP命令用来删除表 视图或索引 其命令格式为 DropTable DropIndex DropView 例 删除教师信息表 DropTable教师信息 SELECT命令利用SELECT命令可以构造数据查询语句 其语法结构为 Select All Distinct From Where GroupBy Having OrderBy Asc Desc 意义 根据FROM子句中提供的表 按照WHERE子句中的条件 表间的联接条件和选择条件 表达式 从表中找出满足条件的记录 语句中各关键词的含义为 All 默认 返回全部记录 Distinct 略去选定字段中重复值的记录 From 指明字段的来源 即数据源表或查询 Where 定义查询条件 GroupBy 指明分组字段 Having 指明分组条件 OrderBy 指明排序字段 Asc Desc 排序方式 升序或降序 例 从学生成绩表中 查询出英语061班中全体学生的记录 结果按照高数成绩的升序排序 SelectAll学号 姓名 班级 高数成绩 政治成绩 英语成绩 计算机成绩From学生成绩Wherebj 英语061 OrderBy高数成绩Asc 如果本例的条件改为 查询出英语061班中高数成绩75分以上全体学生的记录 两个条件并列 则语句应为 SelectAll学号 姓名 班级 高数成绩 政治成绩 英语成绩 计算机成绩From学生成绩Where班级 英语061 and高数成绩 75OrderBy高数成绩Asc 如果本例的条件改为 查询出英语061班中全体学生的记录 显示出他们的学号 姓名 班级和入学成绩 则语句应为 SelectAll学生成绩 学号 学生成绩 班级 学生基本信息 往常成绩单From学生成绩 学生基本信息Where学生成绩 学号 学生基本信息 学号and班级 英语061 OrderBy高数成绩Asc 联接查询数据表1INNERJOIN数据表2ON数据表1 字段 数据表2 字段例 查找职称为教授或副教授 或简历字段内容为空的教师的任课情况SELCET姓名 职称 简历 课程idFROM教师INNERJOIN课程表ON教师 id 课程表 教师idW