10操作查询和SQL特定查询.ppt

数据库应用基础 第10章操作查询和SQL特定查询 第10章操作查询和SQL特定查询 10 1操作查询 操作查询也叫动作查询 其作用是针对表中的一条记录或多条记录和数据项进行操作处理 在标准SQL语言中的操作查询包括 追加 更新 删除等三种 在Access中的操作查询 除前三种外还包括生成表查询 10 1 1生成表查询 生成表查询对查询结果的处理方式与选择查询不同 它将创建一个指定名称的新表 并把检索出的结果数据存放在该表中 该表的结构由SELECT后的字段列表所决定 表中的记录就是查询结果集中的那些数据行 10 1 1生成表查询 按班级编号统计各班学生人数 并生成表AAA SELECT班级编号 Count AS班级人数INTOAAAFROM学生GROUPBY班级编号本例将生成一个名为AAA的新表 其中包含班级编号和班级人数两个字段 10 1 2追加查询 追加查询由INSERT语句实现 可将一个或多个新记录添加到表中 INSERT语句的基本格式分为单记录追加和多记录追加两种 单记录追加查询 如需将一组已知数据追加到某个表中形成一条新记录 使用单记录追加查询 其语法格式为 INSERTINTO 字段1 字段2 VALUES 值1 值2 在按照格式书写实际语句时 格式中直接出现的单词是必写的 由一对尖括号括起的项目要求由用户填写 方括号中的项目是可选的 省略号表示该处可以出现更多相应内容 其他符号表示应该按要求填写对应的内容 单记录追加查询 例2 将编号为 21010504 姓名为 孙琳 的教师数据添加到 教师 表中 INSERTINTO教师 教师编号 姓名 VALUES 21010504 孙琳 在表名后的括号中列举的是将接受数据的字段名 这些字段名不要加引号 在VALUES后的括号中列举的是一组具体值 这些值将依次存入字段列表中对应的各字段 这组值的个数应与指定的字段名个数一致 且类型一致 在VALUES子句中列出的值如果是文本类型数据应该用双引号 括起 如果是日期 时间类型数据应用井号 括起 多记录追加查询 如果希望将查询的结果追加到某个表中形成一条或多条新记录时 使用多记录追加查询 其语法格式为 INSERTINTO 字段1 字段2 SELECT 源表名 字段1 字段2 FROM与单记录追加查询语句格式不同的是 在本语句格式中VALUES部分被一个选择查询所取代 我们知道 选择查询的结果集是由若干行 记录 列 字段 数据所组成的 如果把这些数据添加到指定表中 结果集中的行将成为表中的记录 各列数据也将被填入表中相应的字段 多记录追加查询 例3 从学生表中查询出08级学生数据 并将这些数据添加到 08级学生 表中 INSERTINTO08级学生 学号 姓名 性别 SELECT学号 姓名 性别FROM学生WHERE学号LIKE 2008 本例使用了一个作为子查询的选择查询 该子查询从 学生 表中检索出数千行08级学生数据 并将这些数据添加到 08级学生 表中 应该注意的是 在被添加的目标表名之后给出了字段列表时 子查询SELECT后的字段列表也应该与之对应 字段的名称可以不相同 各字段值按出现的顺序添加到目标表中 多记录追加查询 INSERTINTO08级学生SELECT姓名 性别 学号FROM学生WHERE学号LIKE 2008 如果未指出将数据写入 08级学生 表中的哪些字段 系统就会自动进行字段匹配处理 按照SELECT后的字段列表中出现的字段名称 依次检查 08级学生 表中是否有同名字段 如果发现 08级学生 表中不存在某个字段 则不能执行本次追加查询 如果SELECT后的字段列表中的每个字段都可以在目标表中找到相应字段 就可以执行追加查询 并按字段名相同的匹配方式 将子查询的结果数据记录添加到目标表中 更新查询 使用更新查询可以更改一条或多条现有记录中的数据 更新查询语句的基本格式如下 UPDATESETWHERE在此格式中 为需要更新数据的表 为一组描述如何更新数据的表达式 用于筛选将被更新数据的记录 更新查询 例4 将学号为 200711043 的学生的出生日期改为1990年7月15日 并将其民族改为 满族 UPDATE学生SET出生日期 1990 7 15 民族 满族 WHERE学号 200711043 本例 更新了学生表中的出生日期和民族字段 当然被更新的只是学号为 200711043 的记录 更新查询 在更新查询语句中 一次可以更新若干个字段的值 每个字段如何更新用一个赋值表达式来表示 在赋值表达式中等号的左面为要更新的字段的名称 等号的右面为用来更新的值或表达式 当需更新的字段多于一个时 各赋值表达式间用逗号分隔 更新查询中的条件直接影响更新的范围 在描述条件时一定要小心 因为设置不当时将导致发生一些不期望的更新 而这些更新是无法撤销的 在需要更新数据前 通常应先写个选择查询来验证条件是否正确 然后将正确的条件用于更新查询 或者 先将要更新的表做个备份 以便在更新失误时做恢复之用 更新查询 例5 在 学生 表中有一个 缓考 字段 该字段为 是 否 类型 现根据本次学生考试情况更新该项数据 未参加任何考试者填True 或 1 参加考试者填False 或0 为了保证数据正确 在进行本次更新前 需要把该字段原有的数据全部设置为True 然后再在判断出参加了考试的情况下 将相应值改为False 相应的两个更新语句如下 UPDATE学生SET缓考 True由于本语句没有设置条件 那么全部记录都将被更新 更新查询 UPDATE学生SET缓考 FalseWHEREEXISTS SELECT FROM学生考试WHERE学生考试 学号 学生 学号 在本句中使用了子查询 其目的是检索当前学生在 学生考试 表中是否有相应记录存在 如果有 就表示该学生参加了考试 于是就将其缓考字段值更新为False 那些没有参加考试的学生的缓考字段值没有被更新 仍然是True 更新查询 例6 统计并更新各班学生人数 当 学生 表中学生记录出现增减时 将引起学生人数的变动 现在的要求是更新 班级 表中各班级相应的人数 Access支持使用另一个表 查询中的数据来更新当前表的操作 因此 可以先按 班级 对学生人数进行分组计数 生成一个临时表 然后 用该临时表来更新班级表 更新查询 相应的两个语句如下 SELECT班级编号 Count AS班级人数INTOAAAFROM学生GROUPBY班级编号本例的新表名为AAA 其中包含班级编号和班级人数两个字段 UPDATE班级 AAASET班级 班级人数 AAA 班级人数WHERE班级 班级编号 AAA 班级编号在本句中两个相关表的名称都放在UPDATE后 在SET部分指出用AAA 班级人数来更新班级 班级人数 并且更新是在两个表中具有相同班级编号值的记录间进行的 删除查询 删除查询使用DELETE语句 该语句用于从FROM子句中所列出的一个或多个表中删除满足指定条件的记录行 其一般格式为 DELETEFROMWHERE删除查询将删除整个记录 并且执行删除后 不能取消该操作 如果希望知道将要删除哪些记录 可以先检查使用相同条件的选择查询的结果 然后再执行删除查询 删除查询 例7 从学生表中删除学号为 200715006 的学生记录 DELETEFROM学生WHERE学号 200715006 在执行时 系统将提示有多少记录将被删除 确认后完成删除操作 千万注意 如果不使用WHERE子句 或者WHERE中的条件总是成立 就会把表中全部记录删除掉 删除查询 例8 从 场次 表中删除那些没有安排考试的场次 有时为了操作方便 先预设了若干场次 以备安排考试时选用 在完成实际考试安排后 有部分场次并没有被选用 想删去 此时 应该根据 学生考试 安排的具体数据确定确实没有安排该场次考试时 才能从 场次 表中删除 DELETEFROM场次WHERENOTEXISTS SELECT FROM学生考试WHERE场次 场次 场次 本例使用子查询来判断是否有学生在那个场次考试 如果没有就把该场次删除 10 2SQL特定查询 联合查询可合并两个或多个表中的数据 但具体方式与其他查询不同 大多数查询通过联接行来合并数据 而联合查询通过追加行来合并数据 传递查询不是由Access附带的数据库引擎处理的 它们被直接传递到远程数据库服务器并由该服务器执行处理 然后将结果传递回Access 数据定义查询是一种特殊类型的查询 它不处理数据 而是创建 删除或修改其他数据库对象 子查询 在查询的设计视图中 无法进行SQL特定查询设计 只能在SQL视图中打开或运行它们 在Access中还提供了交叉表查询 10 2 1联合查询 联合查询与联接查询在构成结果集时所采用的方式完全不同 联接查询从多个表或查询中获取并合并数据列 生成查询结果集 联合查询则将合并两个或更多独立查询或表的结果 这时的合并是按行进行的 联合查询 联合查询的语法 TABLE query1UNION ALL TABLE query2 UNION ALL TABLE queryn 其中query1 n表示SELECT语句 或者查询的名称 或前面有TABLE关键字的表的名称 可以在一个UNION操作中以任意组合合并两个或更多查询 表和SELECT语句的结果 默认情况下 使用UNION操作时不会返回重复的记录 但可以包括ALL谓词以确保返回所有记录 联合查询 例9 假设已毕业学生的信息是保存在结构与 学生 表相同的 毕业学生 表中 现需查询出商务学院历年来自湖南的学生名单 SELECT学生 学号 学生 姓名FROM学生WHERE地区 湖南 UNIONSELECT学号 姓名FROM毕业学生WHERE地区 湖南 ORDERBY学生 学号本例使用UNION将两个类似的查询结果联合在一起 使用这些查询时 应注意所有的查询必须具有相同数目的字段 但这些字段的大小或数据类型不必相同 联合查询 仅在第一个SELECT语句中使用别名 因为别名在任何其他语句中将被忽略 可以在最后一个query参数的末尾使用ORDERBY子句以指定的顺序显示返回的数据 在ORDERBY子句中 指的是在第一个SELECT语句中调用别名所依据的字段 10 2 2数据定义查询 数据定义查询是一种特殊类型的查询 它不处理数据 而是创建 删除或修改其他数据库对象 下面主要介绍其中的 CREATETABLEALTERTABLEDROPTABLECREATEINDEXDROPINDEX 创建新表 CREATETABLE语句用以创建新表 在Access中 每个表最少应该有1个字段 最多可以有1024个字段 在CREATETABLE语句中 应该对每个字段按一定的要求分别进行定义 各字段定义间用逗号分隔 创建新表 定义字段的主要成分 字段名 要在新表中创建的字段的名称 数据类型 字段所具有的数据类型 在CREATETABLE语句中常用的数据类型如下 Char文本类型 用于保存字符 Integer整型 Single单精度实数 Double双精度实数 Money货币型 Datetime日期 时间型 Bit是 否 逻辑 型 OleObjecOLE对象 创建新表 定义字段的主要成分 字段大小 以字符为单位的字段大小 主要用于文本字段 因为其它类型字段的大小基本上是固定的 NULL NOTNULL 可选项目 定义相应字段是否允许为空值 PRIMARYKEY 可选项目 定义相应字段是否为主键 创建新表 例10 创建 教师情况 表 其中包括 教师编号 姓名 性别 出生日期 婚否 学院编号 职称 职务 政治面貌 账号 基本工资 岗位津贴等字段 相应语句如下 CREATETABLE教师情况 教师编号CHAR 8 PRIMARYKEY 姓名CHAR 10 NOTNULL 性别CHAR 1 NOTNULL 出生日期DATETIME 婚否Bit 学院编号CHAR 2 职称CHAR 10 职务CHAR 2 政治面貌CHAR 10 账号CHAR 16 NOTNULL 基本工资MONEY 岗位津贴MONEY 修改表的结构 ALTERTABLE语句针对指定的表可以进行多种与字段相关的操作 各种操作通过可选子句来指定 这些子句如 ADDCOLUMN向表中增加一个新字段ALTERCOLUMN修改指定字段的类型及大小DROPCOLUMN从表中删除一个字段ADDCONSTRAINT可添加一个多字段索引DROPCONSTRAINT可删除多字段索引在使用ALTERTABLE语句时 应注意一次只能涉及一个字段或索引 不能一次添加或删除多个字段或索引 修改表的结构 例11 在 教师情况 表中增加一个 相片 字段 该字段类型为OLE对象 可以用来保存图像 相应的SQL语句如下 ALTERTABLE教师情况ADDCOLUMN相片OleObjec在ALTERTABLE语句使用ADDCOLUMN子句增加新字段时 对新字段的定义要求与CREATETABLE语句相同 例12 将 教师情况 表中的职务字段的大小改为10 相应的SQL语句如下 ALTERTABLE教师情况ALTERCOLUMN职务CHAR 10 注意 当使用ALTERTABLE修改字段类型或大小时 可能出现由于类型转换而产生的原有数据的变化 或者由于大小变化丢失部分数据的问题 修改表的结构 例13 删除 教师情况 表中的岗位津贴字段 相应的SQL语句如下 ALTERTABLE教师情况DROPCOLUMN岗位津贴删除一个字段前应该确定该字段中的数据确实不需要了 因为删除字段的操作将使该字段连同相应的数据都被删除掉 并且是不可恢复的 创建索引 建立索引是加快查询速度的有效手段 用户可以根据应用的需要 在基本表上建立一个或多个索引 以加快对表中数据的查找速度 索引可以在创建新表时在CREATETABLE语句中建立 也可以利用CREATEINDEX语句来建立 创建索引 CREATEINDEX语句的完整格式为 CREATE UNIQUE INDEX索引的名称ON表的名称 字段名1 ASC DESC 字段名2 ASC DESC 语句中的 UNIQUE 属于可选项 如果在INDEX这个词之前加上保留字 UNIQUE 表示创建的是唯一性索引 即此索引的每一个索引值只对应唯一的数据记录 ASC DESC 也是可选项 ASC表示升序 DESC表示降序 当ASC或DESC在某字段名后出现时 表示对该字段按升序或降序创建索引 如果不指明ASC或DESC 则表示默认为ASC 如果需要按多个字段建立索引 可以在字段列表中给出多个字段名 以及排序规则 创建索引 例14 为 教师情况 表按 账号 建索引 索引名称为 教师账号 CREATEINDEX教师账号ON教师情况 账号 本例语句执行后 打开 教师情况 表 选择打开 索引 对话框 在该对话框中 可以看到基于该表所建的两个索引 一个是按 教师编号 建立的主索引 另一个就是新建的名为 教师账号 的索引 创建索引 例15 为 学生考试 表按学号 升序 和课程编号 降序 建名为 学号课程 的索引 CREATEINDEX学号课程ON学生考