数据库原理及应用5.ppt

04 03 2020 1 数据库原理及应用第五章查询 查询的概念与设计查询设计器与查询条件各种查询的设计SQL应用 2 5 1查询的概念与设计 5 1 1查询的概念 查询提供了对数据浏览 数据重组 统计分析 编辑修改 输入输出等操作 为了减少数据的冗余 不同类别数据放到不同的表中 查询 的字段来自很多互相之间有 关系 的表 这些字段组合成一个新的数据表视图 但它并不存储任何的数据 视图 优点 1 节省空间2 同步更新 查询得到的是当前表中的实际数据 3 5 1 2查询的种类 1 选择查询从一个或者多个表中抽取数据 最常见2 交叉表查询以行列形式 二维表格 分组汇总数据 类似于电子表格 3 参数查询在查寻时弹出对话框 输入必要的参数 动态地进行查询 4 操作查询允许在一个查询操作中更改许多表中的数据 删除查询 更新查询 追加查询和生成表查询 5 SQL查询使用SQL语句 进行查询 4 5 1 3创建查询 1 查询向导 使用向导创建查询 选择表 字段 可以从不同的表 字段中生成查询 数据来源 不同的表和已有的查询 2 查询的数据表视图 查询的数据表视图看起来像表在查询数据表中无法加入或删除列 而且不能修改查询字段的字段名 这是因为由查询所生成的数据值并不是真正存在的值 而是动态地从表对象中调来的 是表中数据的一个镜像 可更改数据 5 5 2使用向导创建查询 5 2 1使用简单查询向导 数据来源 已有的表和查询 6 5 2 2创建子查询 创建子查询 统计与汇总分组 7 5 2 3创建基于多表的选择查询 5 2 4创建交叉表查询 将多个表 查询 中的数据集合在一起 抽取数据的方式取决于表与表之间的关系 例 学号 姓名 课程名称 分数 将一个表或一个查询中的某些字段中的数据作为新的字段 按照另外一种方式察看数据的查询 类似电子表格的格式中查看计算值 在行与列的交叉处可以对数据进行各种计算 包括求和 平均值 最大 最小值 记数等 交叉表查询向导 新的输出格式 8 5 2 4交叉表查询设计 1 选择含有交叉表数据的一个表 或查询 的名称 例如 成绩查询2 选择在交叉表中哪些字段的值用来做行标题 最多只能选择三个字段 例如 姓名3 选择在交叉表中哪些字段的值用来做列标题 例如 课程名4 选择在表中的交叉点计算出什么数值 例如 求平均Avg5 为新建的查询取名 并单击 完成 按钮 可以改变显示字段的属性 如 总计分数 分数 字段 9 5 2 4交叉表查询设计 如果多表 先建立多表查询 交叉表查询功能方便数据查看核算汇总值 10 5 3在查询设计器中创建查询 5 2 1查询设计器 表 查询显示窗口显示查询所用到的数据来源 示例查询设计窗口显示查询中所用到的查询字段和查询准则 11 示例查询设计窗口 查询设计视图中的工具条 进入查询设计视图后 菜单 工具栏都会发生了变化 1 字段 查询工作表中所使用的字段名称2 表 该字段所属的数据表 可以不同的表3 排序 该字段是否排序 按什么方式排序4 显示 该字段是否在查询表中显示5 条件 指定该字段的查询条件 限制条件6 或 提供多个查询条件 12 5 3 2查询的3种视图 1 设计视图2 数据表视图3 SQL视图用来显示和编辑SQL语句 13 5 3 3在查询设计器中创建选择查询 例 不及格成绩查询 14 5 3 4参数查询设计 一般查询中的准则是不变的 如果希望每次查询都要改变准则 参数查询可以在运行查询的过程中自动修改查询的参数用户在执行参数查询时会显示一个输入对话框以提示用户输入信息设计 当需要对某个字段进行参数查询时 首先切换到这个查询的设计视图 然后在作为参数使用的字段下的 准则 单元格中 在方括号内键入相应的提示文本 例如 在分数字段的准则中输入 分数从 And 到 或者 Between 分数从 And 到 15 5 4查询中的条件 1 使用表达式生成器 准则 在创建查询时所添加的限制条件 制定规则的时候 有时会用到很多函数或表中的字段名 表达式编辑框 操作符的快捷按钮 在查询的规则行中单击鼠标右键 在弹出的菜单中可以看到一个 生成器 的命令 表达式生成器 可以在ACCESS中任何需要表达式的位置上使用 表达式 用运算符连接结果为 真 假 1 非零 0 零 16 2 表达式生成器中的几种常用符号 代表数学运算中的 加 减 乘 除 四种运算符号 符号可以使两个表达式强制连接在一起 17 分别表示 等于 大于 小于 不等于 用来判断条件例 34 表示当某个值等于34时才算满足这个条件 北京 表示当某个值不等于字符串 北京 And Or Not 逻辑运算符是用来连接上面的这些条件表达式 如 100And300 Not 100 逻辑运算符 关系运算符 18 Like 用在对一个字符型的值进行逻辑判断 是否这个值满足某种格式类型 例 Like 中国 则字符串 中国人 中国字 都满足这个条件 Like 中国 则字符串 中国 中国人 中国人民银行 这些都满足这个条件 Like 表 则字符串 表1 表2 都满足这个条件 Like 北京 上海 广州 则只有字符串 北京 上海 广州 可以满足条件 Like 北京 上海 广州 则只有字符串 北京 上海 广州 不能满足条件 字串模糊查询 满足条件为真 19 3 使用准则来执行特定任务 1 检索其中值介于 之间指定值的记录通过使用Between And运算符或比较运算符 来识别范围 20 2 检索指定值范围以外的记录使用Not运算符的表达式 例如 可以在 公司名称 字段的 准则 单元格输入表达式NotT 以查找名称不是以T开始的全部客户 或在 运货商ID 字段输入Not2查找ID不是2的运货商 3 检索不包含Null值的记录输入 NotNull 或 IsNotNull 表达式 例如 要从包含供应商信息的表中查看有传真号码的供应商列表 请在 传真 字段的 准则 单元格中输入 NotNull 或 IsNotNull 4 检索不包含值的记录输入 IsNull 表达式 例如 要从包含供应商信息的表中查看没有传真号码的供应商列表 请 传真号码 字段的 准则 单元格输入 IsNull 21 5 检索包含当前日期的记录 当前日期是从计算机系统时钟取得的系统日期Date 括号中没有空格 当月的加班合计 22 6 检索包括值列表中一个值的记录输入使用In运算符的表达式 例如 查找在法国 德国或日本的供应商 在供应商表的国家字段的 准则 单元格中输入下列表达式 In 法国 德国 日本 或者 法国Or德国Or日本 23 7 抽取已有文本值的一部分使用Left Right或Mid函数 Left stringexpr n Right stringexpr n Mid stringexpr start n stringexpr参数可以是字段名 在方括号中 或文本表达式 n是要抽取的字符数 start是要抽取的第一个字符的位置 例 从学号 A10120201 中取出班级代码 24 3 表达式的例子 25 5 5查询中的计算 5 5 1创建总计字段 查询还具有计算功能 单击查询设计工具栏的 总计按钮 增加一个总计行 包含总和 平均值 计数 最大 最小值 标准偏差 方差等 计算能力有限 26 5 5查询中的计算 预定义计算 汇总计算函数 27 First函数 返回表中第一个记录的字段值Last函数 返回表中最后一个记录的字段值 28 注意事项 汇总函数是否有效取决与数据类型 例如文本字段不能 求和 平均 等 但可以 计数 汇总函数不能对空的值 NULL 进行运算 分组GroupBy汇总计算经常要进行分组操作 对某个字段分组 相同的值进行统计 29 5 5 3创建计算字段 用一个或多个字段的值 进行数值 日期和文字的计算创建计算字段的方法 将表达式直接输入到 字段 格中例如 工资表中的加班合计 加班合计 Sum 加班工资表 加班时数 加班工资表 单位工时报酬 按月份的加班合计2月份的加班合计例 工资表中的实际发放工资 注意 总计中的 分组 总计 平均 最小 最大 计数 标准差 方差 第一条记录 最后一条记录 表达式 条件的使用方法 30 5 6创建操作查询 操作查询能够改变已有表中的数据 或者创建一个新表 一般同时加上参数选择 1 创建删除查询 要求表的关系有 实施参照完整性 和 级联删除相关记录 保证删除一个表的记录时 相关联的其它表中的相应记录也会同步删除 例 创建 毕业生删除查询 调出 查询设计器 选择表 然后在查询工具中选择 删除查询 从已有表中删除符合准则的所有记录 31 加入 学号 字段 字段的准则中输入 输入要删除的学号起始2位 Left 学号 2 表示当记录的学号起始2位与输入的数字相等时 删除该条记录 DELETE学生简况 学号FROM学生简况WHERE 输入要删除的学号起始2位 Left 学号 2 32 2 创建追加查询 将一个或多个表中的一组记录 追加到一个或多个表的尾部 例如将毕业生的记录追加到专门存放毕业生的 毕业生 表中 l先创建 毕业生 表 学号 姓名 性别 照片 简历 l调出 查询设计器 设计视图 选择表 然后在查询工具中选择 追加查询 l加入追加的字段 学号 姓名 性别 照片 简历 l选择要追加的目标的表 在学号字段的准则中 输入要追加的学号起始2位 Left 学号 2 INSERTINTO毕业生 学号 姓名 性别 照片 简历 SELECT学生简况 学号 学生简况 姓名 学生简况 性别 学生简况 照片 学生简况 简历FROM学生简况WHERE 输入要追加的学号起始2位 Left 学号 2 33 3 创建更新查询 调出 查询设计器 设计视图 选择表 然后在查询工具中选择 更新查询 例如 对学生成绩表的所有不及格分数更改成60分 用于更新 替换已有的记录 能够改变一组记录 UPDATE学生成绩SET学生成绩 分数 60WHERE 学生成绩 分数 60 34 4 创建生成表查询 l调出 查询设计器 设计视图 选择表 然后在查询工具中选择 生成表查询 l输入要生成的表名 l在相应字段输入准则 从查询的结果中生成一个新的表 例如 生成不及格表 学号 姓名 课程 成绩 从学生简况 课程名称 学生成绩表 SELECT学生简况 姓名 课程名称 课程名称 学生成绩 分数INTO不及格FROM学生简况INNERJOIN 课程名称INNERJOIN学生成绩ON课程名称 课程编号 学生成绩 课程编号 ON学生简况 学号 学生成绩 学号WHERE 学生成绩 分数 60 35 5 7使用SQL语句创建查询 Access所有的查询都可以认为是一个SQL查询Access查询就是以SQL语句为基础来实现查询的功能ACCESS中所有的数据库操作都是由SQL语言构成的 ACCESS只是在其上增加了更加方便的操作向导和可视化设计 复习 36 5 7 1数据查询select 1 SELECT语句 SELECT语句构成了SQL数据库语言的核心 主要被用来对数据库进行查询并返回符合用户查询标准的结果数据 它的语法包括5个主要子句 分别是FORM WHERE GROUPBY HAVING ORDERBY子句 SELECT语句的结构 SELECT ALL DISTINCT 字段列表 字段列表FROM 表列表 表列表 WHERE 条件表达式 条件 GROUPBY 字段名 HAVING 条件表达式 分组 ORDERBY 字段名 DESC 排序 表示可选项 37 5 7 2 多表联合查询 例selectstudent SC FORMstudent SCWhereStudent 学号 SC 学号 如果需要从多个表中获得数据表名前缀 字段名 连接条件不能省 38 使用SQL语句联接表JOIN 将几个表或查询中的信息联接起来 并将结果显示为单个逻辑记录集有INNER LEFT RIGHT三种JOIN操作 INNERJOIN用来组合两个表中的记录 只要公共字段之中有相符的值即可 ROM 表名1 INNERJOIN 表名2 ON 表名1 字段A 关系运算符 表名2 字段B 例如 SELECT学生简况 姓名 学生成绩 课程编号 学生成绩 分数FROM学生简况INNERJOIN学生成绩ON学生简况 学号 学生成绩 学号 将学生简况和学生成绩表 通过相等的学号字段联接起来 39 使用SQL语句联接表JOIN 如果将几个表联接起来 在JOIN操作中可以进行嵌套操作 如有三个表 表1 表2 表3 现在将三个表联接起来 FROM 表1INNERJOIN表2ON表1 序号 表2 序号 INNERJOIN表3ON表1 序号 表3 序号这样这三个表就联接起来了 例如 SELECT学生简况 姓名 课程名称 课程名称 学生成绩 分数FROM学生简况INNERJOIN 课程名称INNERJOIN学生成绩ON课程名称 课程编号 学生成绩 课程编号 ON学生简况 学号 学生成绩 学号 40 使用SQL语句联接表JOIN LEFT RIGHTJOIN FROM 表名1 LEFT RIGHTJOIN 表名2 ON 表名1 字段A 关系运算符 表名2 字段B LEFTJOIN将LEFT左边的表名1中的所有记录全部保留 而将右边的表名2中的字段B与表名1 字段A相对应的记录显示出来 SELECT学生简况 姓名 学生成绩 课程编号 学生成绩 分数FROM学生简况LEFTJOIN学生成绩ON学生简况 学号 学生成绩 学号 而RIGHTJOIN和LEFTJOIN相反 SELECT学生简况 姓名 学生成绩 课程编号 学生成绩 分数FROM学生简况LEFTJOIN学生成绩ON学生简况 学号 学生成绩 学号 41 5 7 3 定义新的字段名 别名 字段别名可以帮助我们有效的组织查询的输出结果 相当于查询设计视图中字段属性的 标题 在SQL中可以用 AS 操作来实现定义新的字段别名 例在新建的查询中将 表1 中的 字段1 的内容对应到新字段 新字段1 中 SELECT表1 字段1AS新字段1FROM表1 别名都直接放置在各自对应名称的后面 中间用空格分开 SELECT学生成绩 学号 Sum 学生成绩 分数 AS总分FROM学生成绩GROUPBY学生成绩 学号ORDERBYSum 学生成绩 分数 DESC 42 5 7 4向表格中插入数据insertinto 使用insert语句向数据库表格中插入或添加新的数据行 INSERTINTOtablename first column last column VALUES first value last value 例如 INSERTINTO毕业生 学号 姓名 性别 简历 VALUES 22000102 李扬 0 Beijing INSERTINTO毕业生 学号 姓名 性别 照片 简历 SELECT 学生简况 学号 学生简况 姓名 学生简况 性别 学生简况 照片 学生简况 简历 FROM学生简况WHERE 输入要追加的学号起始2位 Left 学号 2 如果把不及格的加到不及格表中 43 更新记录update update语句更新或修改满足规定条件的现有记录 UPDATEtablenameSETcolumnname newvalue nextcolumn newvalue2 WHEREcolumnnameOPERATORvalue and orcolumnOPERATORvalue 例如 UPDATE学生成绩SET学生成绩 分数 60WHERE 学生成绩 分数 60 使用update语句时 关键一点就是要设定好用于进行判断的where条件从句 如果要求2003年之前进厂 工资不到800的加到800 44 删除记录deletefrom delete语句删除数据库表格中的行或记录 DELETEFROMtablenameWHEREcolumnnameOPERATORvalue and orcolumnOPERATORvalue 例如 DELETE学生简况 学号FROM学生简况WHERE 输入要删除的学号起始2位 Left 学号 2 当需要删除某一行或某个记录时 在deletefrom关键词之后输入表格名称 然后在where从句中设定删除记录的判断条件 注意 如果用户在使用delete语句时不设定where从句 则表格中的所有记录将全部被删除 45 创建表格createtable createtable语句用来建立新的数据库表格 CREATTABLEtablename column1datatype column2datatype column3datatype 创建新表格时 在关键词createtable后面加入所要建立的表格的名称 然后在括号内顺次设定各列的名称 数据类型 以及可选的限制条件等 CREATTABLEemployee firstnamevarchar 15 lastnamevarchar 20 agenumber 3 addressvarchar 30 cityvarchar 20 46 删除数据库表格droptable droptable命令删除某个表格以及该表格中的所有记录 DROPTABLEtablename 例如 droptableemployee Droptable命令的作用与删除表格中的所有记录不同 删除表格中的全部记录之后 该表格仍然存在 而且表格中列的信息不会改变 而使用droptable命令则会将整个数据库表格的所有信息全部删除 47 SQL的应用 除了在数据库中使用SQL命令之外 还可以通过编程语言例如VB VBA C VC ASP PHP来实现SQL操作 完成精细 复杂的查询任务 ADO ActiveXDataObjects 微软的一种新的数据访问模型 主要的ADO对象有三个 Connection Command和Recordset Connection对象能够建立数据与应用程序之间的联系 在建立联系后 Command和Recordset对象用来查询 存储或更新数据 Command对象主要担任数据库查询 DatabaseQuery 的角色 Recordset对象则是三者之中最为特别 功能亦最为强大 由数据取得的数据集合就存储于Recordset中 编程 48 例如 ASP中增加一个新注册账号Setconn Server CreateObject ADODB Connection conn Open driver MicrosoftAccessDriver mdb dbq MyDB MDB 在 guestbook 数据表中利用SQL语句增加一个数据记录 然后把其中的各字段 姓名 电话 Email 主题 留言 分别设置成从留言表单上取下的对应的数据 INSERTINTOtablename first column last column VALUES first value last value sql InsertIntoGuestBook 姓名 电话 Email 主题 留言 Values sql sql SqlStr Name sql sql SqlStr Tel sql sql SqlStr Email sql sql SqlStr Subject sql sql SqlStr Memo 执行sql指令 写入数据库conn Executesql 49 作业 7 在教学管理数据库中 创建以下查询 1 教师任课查询 包括字段 姓名 性别 课程名 2 电信学院教师任课查询 包括姓名 电信学院 性别 课程名 12 在教学管理数据库中 创建 各班学生平均成绩 查询 其中包括学号 班级和平均成绩 14 用查询设计器修改例5 4 教学管理数据库中 创建的成绩查询 交叉表 查询 要求添加计算每个学生的 平均分 的字段 13 在罗斯文示例数据库中 创建 产品库存 查询 其中包括产品ID 产品名称 单价 库存量 金额 其中 金额 字段为 单价 和 库存量 两个字段的乘积 15 在教学管理数据库中 创建一个查询 删除 学生 表中指定班级的学生的记录及他们在 成绩