第5章〓查询的创建与使用 数据查找是数据库管理系统中的一个基本功能。使用查询可以将不同表中的信息结合起来，提供一个相关数据项的统一视图。使用查询还可以选择记录、更新表中数据、向表中添加新记录。 5.1查询简介 5.1.1查询和表的区别 在使用数据库中的数据时，并不是简单地使用某一个表中的数据，而常常是将有“关系”的多张表中的数据关联起来使用，有时还可能要对这些数据进行一定的计算。对于这样的要求，建立“查询”对象可以很轻松地解决，查询就是依据一定的查询条件，对数据库中的数据信息进行查找。查询的字段来自互相之间有“关系”的表，这些字段组合成一个新的数据表视图，但它并不存储任何的数据。当改变表中的数据时，查询得到的数据也随之发生改变。 在运行查询时，查询的结果动态地来源于表对象，是表中数据的一个镜像。所以在查询数据表中无法加入或删除字段，也不能修改查询字段的字段名。 5.1.2常见的查询 使用查询，可以按照不同的方式查看、更改和分析数据。同时，查询也可以作为窗体、报表的数据源。Access 中常见的查询有： 选择查询、参数查询、聚合查询、交叉表查询、操作查询、SQL查询。 1. 选择查询 选择查询是最常见的一种查询，它从一个或多个有“关系”的表中将满足要求的数据提取出来，并把这些数据显示在新的查询数据表中，并能对记录进行分组、总计、计数、求平均值，以及其他类型的计算。 选择查询的查询条件在创建查询时给定、除非修改，否则不变。 2. 参数查询 如果用户查询时需要通过在对话框中输入要查询的数据，就要创建参数查询。参数查询可以在运行查询的过程中修改查询的规则，并且执行参数查询时会显示一个输入对话框以提示用户输入信息。 Access的参数查询是建立在选择查询或交叉表查询的基础之上的，在运行选择查询或交叉表查询之前，为用户提供了一个设置条件的参数对话框，可以很方便地更改查询限制或对象。当然不仅可以建立单个参数的查询，还可以建立多字段参数查询。例如，可以设计用它提示输入两个日期，然后通过Access检索在这两个日期之间的数据。 3. 聚合查询 聚合查询有时也称为分组查询，创建聚合查询有助于快速分组和汇总数据。因为使用传统的选择查询只能检索显示在数据源中的记录，而使用聚合查询则可以对检索数据进行诸如总计、平均值、计数等汇总操作。 4. 交叉表查询 Access支持一种特殊类型的总计查询，称为交叉表查询，交叉表查询允许用户精确确定汇总数据如何在屏幕上显示。利用交叉表查询，可以在类似电子表格的格式中查看计算值，也能够计算数据的总计、平均值、计数或其他类型的操作。交叉表查询以传统的行列电子数据表形式显示汇总数据并且与Excel数据透视表密切相关。 5. 操作查询 使用操作查询只需进行一次操作就可对许多记录进行更改和移动。操作查询有四种： 删除查询、更新查询、追加查询、生成表查询。 1) 删除查询 从一个或多个表中删除一组记录。例如，可以使用删除查询来删除已经离校的学生信息。使用删除查询，会删除整个记录。 2) 更新查询 对一个或多个表中的一组记录做全局的更改。使用更新查询，可以更改已有表中的数据。 例如，给所有职工的工资增加200元。 3) 追加查询 将一个或多个表中的一组记录添加到另一个表的末尾。例如，新入校学生的信息存放在新生表中，可以通过追加查询将其追加到总表中。 4) 生成表查询 生成表查询可以根据一定的准则来新建表，然后再将所生成的表导出到其他数据库中，或者在窗体和报表中加以利用。 6. SQL查询 SQL查询是用户使用SQL语句直接创建的一种查询。实际上，Access所有的查询都可以认为是一个SQL查询，因为Access就是以SQL语句为基础来实现查询功能的。 1) SQL查询的分类 SQL查询可以分为四类： 联合查询、传递查询、数据定义查询和子查询。 联合查询： 该查询使用UNION运算符来合并两个或更多选择查询的结果。 传递查询： SQL 特定查询，可以用于直接向ODBC数据库服务器发送命令。通过传递查询，可以直接使用服务器上的表，而不是由Access数据库引擎处理后的数据。 数据定义查询： 包含数据定义语言(DDL)语句的SQL特有查询，这些语句可用来创建或更改数据库中的对象。 子查询： 嵌在另一个选择查询或动作查询内的SQL语句。 2) SQL语句的使用场合 SQL语句可以在Access中的很多场合使用，只要这些场合能够输入表、查询或字段的名称即可。某些情况下，Access会自动填入SQL语句。例如，当使用向导创建窗体或报表以便从多个表中获得数据时，Access会自动创建一个SQL语句，并将该语句用作窗体或报表的“记录源”属性。在通过向导创建列表框或组合框时，Access 会创建一个SQL语句，并将该语句用作列表框或组合框的“行来源”属性设置。 如果不使用向导，也可以为“记录源”或“行来源”属性生成一个SQL语句，方法是单击这些属性旁的任意一个“生成”按钮，然后在查询“设计”视图中创建查询。 5.2选择查询的创建 5.2.1利用查询设计视图建立查询 直接使用查询设计视图建立查询有利于更好地理解数据库中表之间的关系，这对建立一个优秀的数据库非常有用。 建立一个“学生成绩查询”，通过这个查询显示学生的学习成绩，包括“学号”“姓名”“课程名称”“任课教师”“成绩”等字段，步骤如下。 图5.1“显示表”对话框 (1) 首先打开xsgl数据库，然后单击“创建”菜单中的“查询/查询设计”项，弹出“显示表”对话框，如图5.1所示。 (2) 在“显示表”对话框中，“表”选项卡中列出了所有的表，“查询”选项卡中列出了所有的查询，而“两者都有”可以把数据库中所有“表”和“查询”对象都显示出来，这样有助于从选择的表或查询中选取新建查询的字段。 单击“显示表”对话框上的“两者都有”标签，在列表栏中选择需要的表或查询。然后单击“添加”按钮，这样就可以将表添加到查询窗口中。 (3) 关闭“显示表”对话框，回到“查询设计”窗口，如图5.2所示。 图5.2“查询设计”窗口 若从查询设计器中删除表非常简单，只需要在查询设计器中右击相应的表，然后从快捷菜单中选择“删除表”命令即可。 “查询设计”窗口中有三个主要的视图，通过左上角的视图切换按钮可以在不同的视图之间切换。 设计视图： 在该视图中可以创建查询。 数据表视图： 显示查询结果。 SQL视图： 显示查询对应的SQL语句。 查询设计器由表查询窗格和按示例查询(QBE)设计网格两部分构成。这两个窗格通过一个窗格大小调整滚动条水平分隔。可使用该滚动条将设计网格向左或向右移动，使用鼠标单击并向上或向下拖动该滚动条，可以更改上部窗格和下部窗格的相对大小。 查询设计器窗口的上部是表查询窗格。 向查询的设计中添加表或查询及其对应的字段列表时，将添加到该位置。每个要添加的对象都有一个对应的字段列表。每个字段列表包含对应表或查询中所有字段的名称。 查询设计器窗口的下部是按示例查询(QBE)设计网格。 QBE窗格保存查询中涉及的字段名以及用于选择记录的条件。QBE窗格中的每一列包含上部窗格中表或查询包含的单个字段的信息。将字段从上部窗格拖动到QBE窗格可设计查询的构成字段。 QBE窗格包含以下6个行标题。 ① 字段： 查询中所使用的字段名。 ② 表： 该字段来自于数据表。 ③ 排序： 是否按该字段排序。 ④ 显示： 该字段是否在结果集工作表中显示。 ⑤ 条件： 查询条件。 ⑥ 或： 用来提供多个查询条件。 (4) 在查询中添加或删除目标字段。 在查询设计表格中添加的字段称为“目标字段”，添加目标字段有两种方法。 第一种方法： 在“示例查询设计”窗口的表格中选择一个空白的列，单击第一行对应的格子，格子的右边出现一个带下箭头的按钮，单击这个按钮出现下拉框，在下拉框中就可以选择相应的目标字段。 第二种方法： 选中目标字段所在的表，在它的列表框中找到需要添加的字段，将鼠标移动到列表框中标有这个字段的选项上，按住鼠标左键，这时鼠标光标变成一个长方块，拖动鼠标将长方块拖到下方查询表格中的一个空白列，放开鼠标左键，这样就可以将目标字段添加到查询表格中。 如果要删除一个目标字段，将鼠标移动到要删除的目标字段所在列的选择条上，光标会变成一个向下的箭头，单击鼠标左键将这一列都选中，按Delete键，选中的目标字段将被删除。 现在加入“学号”“姓名”“课程名”“成绩”字段，结果如图5.3所示。 图5.3设置结果 (5) 切换到查询的数据表视图查看查询结果。 “查询”可以在“设计”视图和“数据表”视图中切换。在Access中，视图之间切换非常简单，在视图切换按钮中可以选择“设计”视图和“数据表”视图。选择“数据表视图”，结果如图5.4所示。 图5.4查询运行结果 (6) 保存查询。 查询已经基本建立成功，现在需要进行查询的保存。单击“文件”菜单，选择“保存”命令，然后输入查询的名称“学生成绩查询”，单击“确定”按钮即可保存查询。 5.2.2管理查询字段 在设计查询时，有时需要对字段进行特定操作。例如，重新排序、插入新字段或者删除现有字段，或者将某个字段添加到QBE窗格但不显示它，等等。 1. 更改字段顺序 字段在QBE窗格中从左到右的显示顺序决定了它们在数据表视图中的显示顺序。移动QBE窗格中的字段，可以在查询结果中实现新的字段序列，在选中字段的情况下，只需要将字段拖动到新位置，即可在QBE设计中移动字段。 更改字段显示顺序的过程如下。 (1) 单击某个字段的选择器栏，所选字段变为黑色，如图5.5所示。 图5.5更改字段顺序 (2) 然后在按住鼠标左键的同时，将该字段拖动到QBE窗格中的新位置。 注意： 查询中的字段顺序与数据在窗体或报表中的显示方式无关。 2. 删除字段 要从QBE窗格中删除某个字段，可以选择字段并按 Delete键。也可以右击某个字段的选择器栏，然后从显示的快捷菜单中选择“剪切”命令即可。 3. 插入字段 要在QBE窗格中插入新字段，可以将表查询窗格中某个字段列表窗口中的字段拖动到QBE窗格的某一列上，新列将插入到该列的左侧。 4. 隐藏字段 执行查询时，有时可能希望仅显示QBE窗格中的某些字段，这时可以将不需要显示的字段隐藏。一般情况下，在查询中隐藏某个字段的常见原因是该字段用于作为排序或选择条件，但在查询结果中并不需要显示其内容。 例如，显示物理学院所有学生的成绩。这时，物理学院是选择条件，不需要显示。只需要在QBE窗格中“院系”栏取消选中该字段的“显示”复选框，如图5.6所示。 图5.6隐藏字段 5.2.3运算符简介 运算符和操作数的组合称为表达式。对于表达式Access会对其进行求值。表达式可用于在表的字段属性中指定数据验证规则、在窗体和报表中创建计算字段，以及在查询中指定条件。 运算符根据运算对象不同可分为算术运算符、比较运算符、字符串运算符、逻辑运算符和其他运算符。 1. 算术运算符 算术运算符用于执行数值计算。当使用算术运算符时，参与运算的可以是任意数值数据类型，可以是常量、变量或者字段。运算时可以单独使用这些数值，也可以组合使用以创建复杂的表达式。算术运算符有7种，如表5.1所示。 表5.1算术运算符 运算符名称功能举例结果 +加两个数字型字段值相加，两个文本字符串连接100+20120 -减两个数字型字段值相减100-2080 *乘两个数字型字段值相乘100*202000 /除两个数字型字段值相除100/616.667 ＼整除两个数字型字段值相除四舍五入取整100＼616 ^幂A的B次幂2^2.14.28709385014517 Mod模取余，A除以B的余数22.52mod 43 2. 比较运算符 比较运算符用于比较两个值，基于比较运算符生成的表达式的运算结果为True(用-1表示)、 False(用0表示)或Null。关系成立结果为True，关系不成立结果为False，当无法计算表达式时就返回Null。一般情况下Access在比较大小时不区分大小写。比较运算符有6种，如表5.2所示。 表5.2比较运算符 运算符名称功能举例结果 =等于两个表达式的值相等返回True，若一侧为Null，则返回Null100=20False < >不等于两个表达式的值不相等返回True100< >20True <小于左侧小于右侧返回True “man” > “women”False <=小于或等于左侧小于或等于右侧返回True10<=10True >大于左侧大于右侧返回True［成绩］>60若成绩字段的值大于60则结果为True >=大于或等于左侧大于或等于右侧返回True［成绩］>=60若成绩字段的值大于或等于60则结果为True 3. 字符串运算符 与算术运算符和逻辑运算符不同，字符串运算符是专门用于处理字符串数据类型的。字符串运算符有3种，如表5.3所示。 表5.3字符串运算符 运算符名称功能举例结果 &连接将两个字符串连接为一个字符串“姓名:”& “张明”姓名:张明 Like操作数类似确定一个字符串是否与另一个字符串的模式相匹配［xm］ Like “周强” 若xm字段的值是周强则为True Not Like操作数不 类似确定一个字符串是否与另一个字符串的模式不匹配［xm］ Not Like “周强” 若xm字段的值不是周强则为True 注意： Like运算符及Not Like运算符用于比较两个字符串表达式。这些运算符确定一个字符串是否与另一个字符串的模式相匹配，返回值为True、False或Null(如果Like运算中有操作数为Null，则结果为Null)。 Like和 Not Like运算符运算时不区分大小写。 Like运算符使用语法如下： 表达式 Like 模式字符串 其功能是在模式字符串中查找表达式，如果存在返回True。 Like和 Not Like运算符为字符串比较提供了强大且灵活的工具，而通配符则可以增强Like运算符的灵活性。表5.4显示了5个可与Like运算符结合使用的通配符。 表5.4可与Like运算符结合使用的通配符 通配符功能 ?单个字符(0～9，A(a)～Z(z)) *任意字符串 #任意单个数字 ［list］列表中的任意单个字符 ［!list］不在列表中的任意单个字符 ［list］和［!list］都可在两个字符之间使用连字符来表示范围。表5.5列举了一些通配符示例。 表5.5通配符使用示例 示例含义 ［Answer］ Like “［AD］” 若Answer的值为A、B、C、D、a、b、c、d，返回True，否则返回False ［xm］ Like “???” xm长度刚好是3的返回True，否则返回False ［code］ Like “610103*” code以610103开头的返回True，否则返回False ［xm］ Not Like “［周,李,张］*” xm不是周姓、李姓、张姓返回True，否则返回False 4. 逻辑运算符 逻辑运算符用于在表达式中连接多个条件。与比较运算符类似，这些运算符也是始终返回True、False或Null。常见的逻辑运算符有5种，如表5.6所示。 表5.6逻辑运算符 运算符名称示例运 算 规 则 And与表达式1 And表达式2 当表达式1和表达式2都为True时返回True Or或表达式1 Or表达式2 当表达式1或表达式2中的任何一个为True时返回True Not非Not表达式表达式为True时返回False，否则返回True Xor异或表达式1 Xor表达式2 当表达式1和表达式2的值相同时，返回False，否则返回True Eqv逻辑相等表达式1 Eqv表达式2 当表达式1和表达式2的值相同时，返回True，否则返回False 5. 其他运算符 除了以上运算符外，Access还有3个常用的运算符Between…And、In、Is，其运算规则如表5.7所示。 表5.73个常用运算符的运算规则 运算符功能举例 Between…And指定范围操作Between“A”And“B” In指定枚举范围In (“A, B, C”) Is判定是Expression Is Null 注意： 通常情况下Is运算符与关键字Null结合使用以确定对象的值是否为Null。在VBA环境中Is运算符可用于比较各种对象，以确定它们是否代表相同的实体。也就是说，Is运算符仅适用于对象和对象变量，例如表中的字段。Is运算符不能用于简单变量(例如字符串或数字)。 6. 运算符优先顺序 在使用具有多个运算符的复杂表达式时，Access必须确定运算符计算的先后次序。优先级高的先计算，优先级低的后计算。Access将始终遵循运算符的优先级，除非使用括号改写其默认优先级顺序。括号内的运算会先于括号外的运算执行。在括号中，Access会遵循默认的运算符优先顺序进行计算。 不同类别运算符的计算优先顺序如下： 算术运算符→比较运算符→逻辑运算符 每个类别都包含自己的优先顺序。 (1) 算术运算符的优先级： 指数→取否→乘法与除法(从左到右)→整除→求模→加法和减法(从左到右)→字符串的连接。 (2) 比较运算符的优先级： 等于→不等于→小于→大于→小于或等于→ 大于或等于→Like运算符 (3) 逻辑运算符的优先级： Not→And→Or→Xor→ Eqv 5.2.4为查询添加准则 运算符和表达式的一种基本应用是生成复杂的查询条件。在使用选择查询时，可能需要指定一个或多个条件以限制显示信息的范围。 1. 查询中常用运算符 在查询中所用到的运算符有算术运算符、比较运算符、逻辑运算符和字符串运算符等。表5.8显示了用于选择查询的最常见运算符。 表5.8查询中常用运算符 算术运算符关系运算符逻辑运算符字符串运算符其他运算符 *=And&Between…And /< >Or LikeIn +>NotNot LikeIs Null - ON 语法是： DROP INDEX
, DISTINCTROW支持(允许选择单个记录)不支持 OWNERACCESS支持(在执行时控制许可权)不支持 TABLE in UNION支持(允许使用下列语法指定表： TABLE ) 不支持 ORDER BY in UNIONS支持。允许通过联合查询中的子句实现多种排序 支持。允许通过语句末尾的子句实现一种排序 TRANSFORM支持。用于交叉表查询不支持 PARAMETERS支持(在SQL中记录)不支持 5.7.2数据定义 SQL的数据定义功能非常广泛，一般包括数据库的定义、表的定义、视图的定义、存储过程的定义、规则的定义和索引的定义等多个部分。 SQL中数据定义的基本语句有如下四个。 (1) 建立新表： CREATE TABLE … (2) 添加字段： ALTER TABLE … ADD … (3) 删除字段： ALTER TABLE … DROP … (4) 基本表删除： DROP TABLE … 1. 基本数据类型 在Access中使用SQL语句时，可以使用的基本数据类型及其特点如表5.13所示。 表5.13基本数据类型及其特点 数 据 类 型存 储 大 小说明 BINARY每字符1B任何类型的数据都可存储在这种类型的字段中 BIT1BYes/No(True/False, ON/OFF,-1/0)只包含两值之一的字段 BYTE1B介于0～255的整数 MONEY8B介于-922,337,203,685,477.5808～922,337,203,685,477.5807 DATETIME8字节介于100～9999年的日期或时间数值 UNIQUEIDENTIFIER128b用于远程过程调用的唯一识别数字 REAL4B单精度浮点数 FLOAT8B双精度浮点数 SMALLINT2B介于-32,768～32,767的短整型数 INTEGER4B介于-2,147,483,648～2,147,483,647的长整型数 DECIMAL17B可以定义精度(1～28)和符号(0～定义精度)。默认精度和符号分别是18和0 TEXT每字符2B从0到最大2GB IMAGE视实际需要而定从0到最大2GB用于OLE对象 CHAR每字符2B长度从0到255字符 说明： (1) BINARY： 二进制型，可以指定长度。 (2) BIT： 位型，可用格式(Yes/No, True/False, OH/OFF)。 (3) BYTE： 数字字节，不要指定长度和精度，否则会报错。 (4) MONEY，CURRENCY： 货币型，不要指定长度和精度，否则会报错。 (5) DATETIME： 日期时间型，不要指定长度，否则会报错。 (6) UNIQUEIDENTIFIER： 用于远程过程调用的唯一识别数字。 (7) REAL，SINGLE： 数字单精度型，不要指定长度和精度，否则会报错。 (8) FLOAT，DOUBLE，NUMBER： 数字双精度型，不要指定长度和精度，否则会报错。 (9) SMALLINT，SHORT： 数字整型，不要指定长度和精度，否则会报错。 (10) INTEGER，INT，LONG： 数字长整型，不要指定长度和精度，否则会报错。 (11) DECIMAL： 数字小数，可以指定长度和精度，如只指定长度，那么精度默认为0，如都不指定，那么默认长度为18，默认精度为0。 (12) TEXT： 文本型(指定长度时)。 (13) IMAGE，OLEOBJECT： OLE OBJECT型，不要指定长度，否则会报错。 (14) CHAR，NCHAR，VARCHAR，NVARCHAR： 文本型，可以指定长度，否则默认值为255。 (15) MEMO： 备注型，不要指定长度，否则会报错。 2. 在Access中使用SQL 在数据库窗口中，选择“创建”选项卡，单击“查询”中的“查询设计”按钮，弹出“显示表”对话框，单击“关闭”按钮，系统将建立一个名为“查询1”的空查询。单击功能区的SQL视图按钮，切换到SQL视图，如图5.33所示。 图5.33SQL视图 在图5.33中，系统默认为SELECT命令。删除该命令，输入完成特定功能的SQL命令，然后单击功能区的“运行”按钮，便可运行对应的SQL命令。 3. 表的建立 SQL中的CREATE TABLE语句用来建立新表。 CREATE TABLE语句的使用格式如下。 CREATE TABLE ［数据库名.］表名 (列名1类型 ［(宽度，［小数位数］)］ ［NULL/NOT NULL］， 列名2类型 ［(宽度，［小数位数］)］ ［NULL/NOT NULL］， … …) 说明： ［数据库名.］： 用于指明所建立的表隶属于哪个数据库。 表名： 所创建表的名称。 列名： 所建立表的字段名。 类型： 指明对应字段的数据类型。常见的基本类型及其符号表示如表5.13所示。 ［(宽度，［小数位数］)］： 指明对应字段的宽度。如有小数部分，还需指出小数的位数。 ［NULL/NOT NULL］： 指明字段是否可以取空值。 【例5.1】用SQL在xsgl数据库中建立基本表JBQK、CJ和KC。 (1) 创建基本情况表JBQK。 通过查询设计视图创建查询，并切换到SQL视图，输入如下命令，结果如图5.34所示。 图5.34SQL视图 CREATETABLEJBQK (SNOCHAR(4)NOT NULL, SNAME CHAR(8)NOT NULL, AGEBYTE, GENDER CHAR(2), DEPTCHAR(12)) 其中： JBQK为表名，SNO,SNAME,AGE,SEX为字段名，NOT NULL用于说明列值不能为空。 单击“运行”按钮，系统运行SQL命令，便会生成基本表JBQK。后面的SQL命令都按此法创建并运行。 (2) 创建成绩表CJ。 CREATETABLECJ (SNOCHAR(4)NOT NULL, KNO CHAR(4) NOT NULL, ACHIEVEMENTSINGLE) (3) 创建课程表KC。 CREATETABLEKC (KNOCHAR(4),KNAME CHAR(10),GRADE SHORT) 注意： (1) 使用CREATE TABLE语句创建基本表时，最初得到的只是一个空的框架(表结构)，用户可以使用INSERT命令插入内容。 (2) 使用SQL语句创建表时，表名以及表中字段名称必须以字母开头，后面可以使用字母、数字或下画线。 4. 修改表结构 基本表创建以后，经过一段时间的使用，表的结构可能会无法满足实际的要求，这时就需要对表的结构进行修改，例如，增加新字段或者删除无用字段。 (1) 增加新字段。 语句格式： ALTER TABLE表名ADD字段名 类型 ［(宽度［,小数位数］)］ 【例5.2】在基本情况表JBQK中增加ADDRESS字段。 ALTER TABLEJBQKADD ADDRESS CHAR (40) (2) 删除无用字段。 语句格式： ALTER TABLE表名 DROP 字段名 【例5.3】删除基本情况表JBQK中的ADDRESS字段。 ALTER TABLEJBQKDROP ADDRESS (3) 修改字段的类型宽度等。 语句格式： ALTER TABLE表名 ALTER 字段名 新类型 ［(新宽度［,小数位数］)］ 【例5.4】将成绩表CJ中的ACHIEVEMENT字段的类型改为双精度型。 ALTER TABLECJALTERACHIEVEMENT FLOAT 5. 删除基本表 在SQL中使用DROP TABLE命令删除某个表格及该表格中的所有记录。DROP TABLE命令的使用格式为： DROP TABLE表名 【例5.5】删除基本情况表CJ。 DROPTABLE CJ 5.7.3数据更新 Access SQL的数据更新包括插入数据、删除数据、修改数据三种基本操作。 1. 插入数据 Access SQL使用INSERT语句向表中插入或添加新的数据行。INSERT语句的基本格式为： INSERT INTO基本表名(列名表) VALUES(元组值) 【例5.6】给课程表KC添加一条记录。 INSERT INTO KC VALUES ("1-06","C语言",4) 简单来说，当向数据库表中添加新记录时，在关键词INSERT INTO后面输入所要添加的表名称，然后在括号中列出将要添加新值的列的名称。若省略了列名表，则按表结构中列的顺序输入相应的内容。 2. 删除数据 SQL使用DELETE语句删除数据库表格中的行或记录。DELETE语句的格式为： DELETE FROM基本表名［WHERE条件表达式］ 从基本表中删除满足条件的表达式的元组，该语句每次只能从一个基本表中删除元组。当无条件时，表示删除所有的元组。 【例5.7】在课程表KC中，删除课程编号KNO为106的课程。 DELETE FROM KC WHERE KNO="1-06" 【例5.8】在成绩表CJ中，将课程编码为“0033”的课程成绩小于该科平均成绩的元组从成绩表CJ中删除。 DELETE * FROM CJ WHERE KNO="0033" AND ACHIEVEMENT<(SELECT AVG(ACHIEVEMENT) FROMCJ WHERE KNO="0033") 简单来说，当需要删除某一行或某个记录时，在DELETEFROM关键词之后输入表格名称，然后在WHERE从句中设定删除记录的判断条件。注意，如果用户在使用DELETE语句时省略WHERE从句，则表格中的所有记录将全部被删除。 3. 修改数据 SQL使用UPDATE语句更新或修改满足规定条件的记录。UPDATE语句的格式为： UPDATE基本表名 SET列名=值表达式［,SET 列名=值表达式…］ ［WHERE条件表达式］ 命令功能： 修改基本表中满足条件表达式的那些元组指定列的值，所需修改的列由SET子句指出。 【例5.9】在JBQK表中将学号(SNO)为2014119091的记录的性别( GENDER)改为“女”。 UPDATE JBQK SET GENDER = "女" WHERE SNO=" 2014119091" 【例5.10】将所有男同学的成绩提高15%。 UPDATE CJSETACHIEVEMENT= ACHIEVEMENT *1.15 WHERE SNO IN(SELECT SNOFROMJBQKWHEREGENDER="男") 使用UPDATE语句时，关键一点就是要设定好用于进行判断的WHERE条件从句。 5.7.4数据查询 数据查询是对数据库进行的最基本的操作，查询效率的高低对软件有着重要的影响。在SQL中提供了SELECT查询语句，其功能强大且内容丰富。 1. SELECT的语法格式 SELECT的语法格式如下。 SELECT目标表的列名序列 FROM基本表视图序列 ［INTO目标位置］ ［WHERE行条件表达式］ ［GROP BY列名序列］ ［HAVING组条件表达式］ ［ORDER BY列名［ASC|DESC］…］ ［ ］： 表示可选项，用户根据实际需要进行选择。 各子句的含义及功能如下。 (1) 目标表的列名序列： 指明查询结果的字段构成，可以是字段名、表达式、常量等。 (2) 基本表视图序列： 用于指明查询信息的数据来源。 (3) ［INTO目标位置］： 指明查询结果的输出位置，输出位置包括：  ARRAY数组名： 存放到指定的数组中。  CURSOR临时表名： 存放到一个临时表中。  TO FILE文件名： 存放到一个文件中。  TO PRINTER： 打印查询结果。  TO SCREEN： 将结果在屏幕上显示(默认方式)。 (4) ［WHERE 行条件表达式］： 用于在连接结果中选择满足条件的元组。 (5) ［GROUP BY 列名序列］： 用于对结果进行分组。分组记录的字段可以有多个，这些字段的顺序决定最高到最低的分组层次。 (6) ［HAVING 组条件表达式］： 用于选择满足条件的组。 (7) ［ORDER BY 列名［ASC|DESC］…］ ： 用于设置查询结果的排序方式。ASC表示升序，DESC表示降序。使用的目的是将查询的结果依照指定字段加以排序。若没有ORDER BY，查询出的数据集将不会排序。 2. 语句的执行过程 SELECT语句的执行过程如下。 (1) 首先读取FROM子句中的基本表和视图，然后对其进行笛卡儿积运算。 (2) 根据WHERE子句，选出满足条件表达式的元组。 (3) 按照GROUP子句中指定字段的值进行分组，并从这些分组中选择满足HAVING子句中条件的分组。 (4) 按照SELECT子句给出的字段求值得到目标表。 (5) 用ORDER子句对目标表进行排序。 【例5.11】在CJ表中查询出所有成绩大于或等于60分的学生。 SELECT SNO,KNO, ACHIEVEMENT FROM CJ WHERE ACHIEVEMENT >=60 3. WHERE子句中的运算符 SQL是完备的，也就是说，只要数据是按关系方式存入数据库的，就能构造合适的SQL命令把它检索出来。事实上，SQL不仅具有一般的检索能力，还可以通过在WHERE子句中加入运算符进行计算方式的检索。在WHERE子句中使用的一些运算符如表5.14所示。 表5.14WHERE子句中的运算符 运 算 符 号运算符 算术比较运算符 =(等于)、>(大于)、<(小于)、>=(大于或等于)、<=(小于或等于)、< >(不等于) 逻辑运算符AND、OR、NOT 集合运算符UNION(并)、INTERSECT(交)、EXCEPT(差) 集合成员资格运算符IN、NOT IN 谓词EXISTS(存在)、ALL、SOME、UNIQUE 数学函数AVG、MIN、MAX、SUM、COUNT，FIRST、LAST 其他LIKE、BETWEEN…AND… 通过使用LIKE运算符可以只选择与用户规定格式相同的记录。可以将一字符串与另一特定字符串样式比较，并将符合该字符串样式的记录过滤出来。若要查询出所有姓“李”的人，可以利用下面的语句。 LIKE "李*" 【例5.12】计算学生成绩高于或等于60分的平均分。 SELECT AVG(ACHIEVEMENT) AS平均分 FROM CJ WHERE ACHIEVEMENT>=60 【例5.13】统计出成绩表CJ中选课程号码KNO为“102”的学生人数。 SELECT COUNT(SNO) AS人数 FROM CJ WHERE KNO="1-02" 【例5.14】在基本情况表JBQK中找出姓名字段SNAME的第一条数据和学号字段SNO的最后一条数据。 SELECT FIRST(SNAME), LAST(SNO) FROMJBQK 【例5.15】在成绩表CJ中计算出总成绩。 SELECT SUM(ACHIEVEMENT) AS总成绩 FROMCJ 4. 简单查询 所谓简单查询是指查询仅涉及数据库中的一个表。 【例5.16】显示JBQK表中所有字段的数据。 SELECT* FROMJBQK 选择表中部分字段并指定它们的显示次序，查询结果集合中字段的排列顺序与命令中所指定的字段名排列顺序相同。 【例5.17】显示JBQK表中指定字段SNAME,AGE的数据。 SELECT SNAME,AGE FROM JBQK 在选择列表中，可重新指定列标题。定义格式为： 字段名 AS 列标题 【例5.18】使用列标题别名。 SELECT SNAME AS姓名, AGE AS 年龄 FROM JBQK 【例5.19】TOP n指令示例。 SELECT TOP 4 SNO,SNAMEFROM JBQK SELECT TOP 50 PERCENT*FROM JBQK 使用TOP n［PERCENT］选项限制返回的数据行数，TOP n说明返回n行，而TOP n PERCENT时，说明n是表示一个百分数，指定返回的行数等于总行数的百分之几。 【例5.20】查询名字的第二个汉字为“志”的学生的姓名与学号。 SELECT SNAME, SNO FROM JBQK WHERE SNAME LIKE "?志*" 【例5.21】查询姓“马”的学生的姓名与学号。 SELECT SNAME, SNO FROM JBQK WHERESNAMELIKE"马*" 说明： “？”代表任意一个字符，“*”表任意长的字符串。 【例5.22】查询JBQK表中全体学生情况，查询结果按所在系的系号升序排列，同一系的学生按年龄的降序排列。 SELECT* FROMJBQK ORDER BY(DEPT, AGE DESC) 【例5.23】查询JBQK表中“信息学院”和“新闻学院”同学的学号、姓名和年龄字段。 SELECT SNO, SNAME, AGE FROMJBQK WHEREDEPTIN ("信息学院","新闻学院") 5. 嵌套查询 嵌套查询也称为子查询，是指一个SELECT…FROM…WHERE查询可以嵌套在另外一个查询中。SQL允许多层嵌套。每个子查询在上一级查询处理之前求解，即嵌套查询是由里向外处理的，这样外查询可以利用内查询的结果。 当查询涉及多个关系时用嵌套查询逐次求解，层次分明，易于理解，易于书写，具有结构化程序设计的优点。 【例5.24】在JBQK表中查询和李娜在同一系的学生的学号、姓名。 (1) 在JBQK表中查询李娜所在的系。 SELECT DEPT FROM JBQK WHERESNAME="李娜" (2) 查询和张志刚在同一系的学生的学号、姓名。 SELECT SNO, SNAMEFROMJBQK WHERE DEPT=( SELECT DEPT FROM JBQK WHERESNAME="张志刚") 【例5.25】在成绩表CJ中查询选修了101课或选修了103课的学生学号。 SELECTSNOFROM CJ WHEREKNO="1-01" UNION SELECTSNOFROM CJ WHEREKNO="1-03" 6. 连接查询 连接查询涉及两个以上的表，连接查询是关系数据库中最主要的查询，包括等值连接、自然连接、非等值连接、自身连接、外连接和复合连接查询等。 【例5.26】查询每个学生及其选修情况。 SELECT JBQK.SNO, SNAME, KNAME, CJ.ACHIEVEMENT FROM JBQK, CJ, KC WHERE JBQK.SNO=CJ.SNO AND CJ.KNO=KC.KNO 【例5.27】对JBQK, CJ, KC中的数据进行以下3种查询操作。 (1) 统计每一年龄组选修课程的学生人数。 SELECTAGE, COUNT(*) FROMJBQK, CJ WHERE JBQK.SNO=CJ.SNO GROUP BY AGE (2) 求基本情况表JBQK中女同学的每一年龄组(超过1人)的人数，要求查询结果按人数的升序排列，人数相同按年龄的降序排列。 SELECT AGE, COUNT(*) FROM JBQK WHERE GENDER="女" GROUP BY AGE HAVING COUNT(*)>=2 ORDER BY AGE DESC (3) 检索女同学选修的课程的课程号。 SELECT DISTINCT KNOFROM JBQK,CJ WHERE JBQK.SNO=CJ.SNO AND SEX="女" 习题 一、 填空题 1. 查询就是依据一定的查询条件，对中的数据信息进行查找。 2. 聚合查询有时也称为，创建聚合查询有助于快速分组和汇总数据。 3. 操作查询包括更新查询、追加查询、和生成表查询等。 4. Access数据库中，SQL查询的GROUP BY语句用于。 5. 条件语句“WHERE 工资额>1000”的意思是。 6. 利用对话框提示用户输入查询条件，这样的查询属于。 7. SQL是在数据库系统中应用广泛的数据库查询语言，它包括数据定义、数据查询、和4种功能。 8. 使用查询向导创建交叉表查询的数据源必须来自个表或查询。 二、 选择题 1. Access数据库中，在创建交叉表查询时，用户需要指定三种字段，下面()不是交叉表查询所需要指定的字段。 A. 格式字段B. 列标题字段C. 行标题字段D. 总计类型字段 2. 将Access数据库中C语言课程不及格的学生从“学生表”中删除，要用()。 A. 追加查询B. 生成表查询C. 更新查询D. 删除查询 3. 在Access中，可以把()作为创建查询的数据源。 A. 查询B. 报表C. 窗体D. 外部数据表 4. 在Access数据库中，对数据表进行删除的是()。 A. 汇总查询B. 操作查询C. 选择查询D. SQL查询 5. 在Access数据库中，从数据表找到符合特定准则的数据信息的是()。 A. 汇总查询B. 动作查询C. 选择查询D. SQL查询 6. 若在学生表中查找所有出生日期在“200011”和“20001231”之间的记录，可以在查询设计视图的准则行中输入()。 A. Between#200011#And #20001231# B. Between"200011"And"20001231" C. "200011" And "20001231" D. #200011#And#20001231# 7. 在SQL的SELECT语句中，用于实现选择运算的子句是()。 A. FORB. IFC. WHILED. WHERE 8. 条件中“性别="女"AND工资额>2000”的意思是()。 A. 性别为"女"并且工资额大于2000的记录 B. 性别为"女"或者工资额大于2000的记录 C. 性别为"女"并非工资额大于2000的记录 D. 性别为"女"或者工资额大于2000，且二者择一的记录 9. 条件“NOT工资额>2000”的意思是()。 A. 除了工资额大于2000之外的工资额的记录 B. 工资额大于2000的记录 C. 并非工资额大于2000的记录 D. 字段工资额大于2000，且二者择一的记录 10. 已知“借阅表”中有“借阅编号”“学号”和“借阅图书编号”等字段，每个学生每借阅一本书生成一条记录，要求按学生学号统计出每个学生的借阅次数，下列SQL语句中，正确的是()。 A. SELECT学号COUNT(学号)FROM借阅 B. SELECT学号COUNT(学号)FROM借阅GROUP BY学号 C. SELECT学号SUM(学号)FROM借阅 D. SELECT学号SUM(学号)FROM借阅ORDER BY学号 11. 将信息系1999年以前参加工作的教师的职称改为副教授，合适的查询为()。 A. 生成表查询B. 更新查询C. 删除查询D. 追加查询 12. 以下不属于操作查询的是()。 A. 交叉表查询B. 生成表查询C. 更新查询D. 追加查询 13. 下面对查询功能的叙述中正确的是()。 A. 查询和表基本类似，都可用于数据的存储 B. 在查询中，编辑记录主要包括添加记录、修改记录、删除记录和导入导出记录 C. 在查询中，查询不仅可以找到满足条件的记录，而且还可以在建立查询的过程中进行各种统计计算 D. 以上说法均不对 14. 用SQL描述“在教师表中查找男教师的全部信息”，以下描述正确的是()。 A. SELECT FROM 教师表IF(性别="男") B. SELECT 性别FROM教师表IF(性别="男") C. SELECT * FROM教师表WHERE(性别="男") D. SELECT * FROM性别WHERE(性别="男") 15. ()会在执行时弹出对话框，提示用户输入必要的信息，再按照这些信息进行查询。 A. 选择查询B. 参数查询C. 交叉表查询D. 操作查询 16. 查询能实现的功能有()。 A. 选择字段，选择记录，编辑记录，实现计算，建立新表，建立数据库 B. 选择字段，选择记录，编辑记录，实现计算，建立新表，更新表 C. 选择字段，选择记录，编辑记录，实现计算，建立新表，设置格式 D. 选择字段，选择记录，编辑记录，实现计算，建立新表，建立基于查询的报表和窗体 17. 在Access数据库中使用向导创建查询，其数据可以来自()。 A. 多个表 B. 一个表C. 一个表的一部分D. 表或查询 18. 在Access数据库中，下列查询的计算表达式中，求两门课的平均分数，正确的是()。 A. ［语文］+［数学］ /2B. "(［语文］+［数学］)/2" C. (［语文］+［数学］)/2D. "［语文］"+"［数学］"/2 19. 在Access数据库中，要查询的条件是语文成绩处在60分数段的记录，则在语文字段的准则中应当输入()。 A. >60 and <70B. >=60 and <70 C. >60 or <70 D. >=60 or <70 20. 在Access数据库中，查询姓名字段中所有姓张的同学记录时，在姓名准则中应输入()。 A. 张B. 张*C. *张D. *张* 21. 下列SELECT语句正确的是()。 A. SELECT * FROM"学生表"WHERE 姓名="张三" B. SELECT * FROM"学生表"WHERE 姓名=张三 C. SELECT * FROM学生表WHERE 姓名="张三" D. SELECT * FROM学生表WHERE 姓名=张三 三、 简答题 1. 举例说明在什么情况下，需要设计生成表查询。 2. 举例说明在什么情况下，需要设计追加查询。 3. 现在已知某单位“职工工资表”中的性别用汉字“男”或“女”表示，已有字段为“姓名，性别，标准工资，加班费，标准工资”，在查询中除已知字段外，现在要求自动求出“妇女保健补贴”字段中的数据，即每位妇女月补贴25元，如何自动求出所有职工的工资总额？ 4. 设有下列关系模型的样本数据： 书店表： 由书店号、书店名、地址组成，书店号为主码。 图书馆表： 由图书馆号、图书馆名、城市、电话组成，图书馆号为主码。 图书表： 由ISBN号、书名、定价组成，ISBN号为主码。 图书发行表： 由图书馆号、图书ISBN号、书店号、册数组成，图书馆号、图书ISBN号、书店号为主码。 试用SQL写出以下查询。 (1) 查找馆名为“A馆”的图书馆从书店“B店”购买的图书书名及其册数。 (2) 取出馆址在“西安”的馆名及电话号码。 (3) 取出书店“太白路店”发行的图书书名和数量。 5. 设有一个销售管理数据库系统，其关系模式如下： 销售员(工号，姓名，柜台，部门) 商品(商品号，商品名，价格) 业绩(工号，商品号，销售额) 用SQL表示下列数据查询操作。 (1) 查询“服装”部门女装柜台销售员的工号和姓名。 (2) 查询销售以“霓裳”开头的服装的销售员的工号、姓名和该类产品的销售额。 (3) 查询每种商品的商品号、各销售员销售该商品的最高销售额、最低销售额和所有员工的平均销售额。 四、 操作题 1. 创建一个数据表结构如下： 序号(自动编号)，学号(文本，8，必填，非空)，姓名(文本，8)，性别(文本，2)，出生日期(日期)，党员否(是/否)，入学成绩(整型，一位小数)，籍贯(文本，10)，简历(备注)，照片(OLE)。 字段属性定义： 定义“学号”为主键，“入学成绩”定义有效性规则为大于500且小于750。 查询要求： 创建一个籍贯包含“西安”和“北京”学生的查询，显示“姓名”“籍贯”； 创建一个入学成绩大于600的男生的查询，查询结果按成绩降序排列，显示“学号”“入学成绩”。 2. 创建“学生成绩.accdb”数据库，数据库包括学生表(学号，姓名，系名，性别，出生日期，爱好，照片，简历)、课程表(课程编号，课程名称，学分，开课时间)和成绩表(学号，课程编号，成绩)，按下列要求进行操作。 (1) 创建表间关系。 (2) 录入数据。 (3) 写出满足如下条件的SQL语句。 ① 选出语文、数学、计算机的各科成绩在90分以上的学生姓名。 ② 选出“计算机系”的男学生的学号和姓名。 ③ 选出姓“刘”的学生的姓名、性别。 (4) 创建一个查询，查询每位学生的总分，要求输出学号、姓名、总分，查询保存为“总分查询”。 (5) 创建一个选择查询，查找并显示简历信息为空的学生的“学号”“姓名”“性别”和“出生年月”四个字段内容，所建查询命名为“基本信息查询”。 (6) 创建一个选择查询，按系别统计各自男女学生的平均年龄，显示字段标题为“所属院系”“性别”，所建查询命名为“按系统计查询”。 (7) 创建一个操作查询，将没有书法爱好的学生的“学号”“姓名”和“出生年月”三个字段内容追加到目标表“临时表”的对应字段内，所建查询命名为“追加查询”。 (8) 创建一个查询，当运行该查询时，应显示参数提示信息“请输入爱好”，输入爱好后，在简历字段中查找具有指定爱好的学生，显示“学号”“姓名”“性别”“年龄”“照片”和“简历”字段的内容，所建查询命名为“按爱好查询”。 (9) 创建一个查询，查找学生的课程成绩大于或等于80且小于或等于100的学生情况，显示“姓名”“课程名称”和“成绩”三个字段的内容，所建查询名为“成绩查询”。 (10) 创建一个查询，按“课程编号”分类统计最高分成绩与最低分成绩的差，并显示“课程名称”“最高分与最低分的差”等内容。其中，最高分与最低分的差由计算得到，所建查询名为“高低分差别查询”。 3. 创建“库存管理系统”数据库，在库中创建“产品定额储备表”和“库存情况表”。表结构要求根据自己对实际的情况理解构造。按要求创建如下查询。 (1) 以“库存管理系统”数据库中的“产品定额储备”和“库存情况”两张表为数据源创建一个查询，查找并显示库存量超过1000只的产品名称和库存数量，查询名为“数量查询”。 (2) 以“库存管理系统”数据库中的“产品定额储备”和“库存情况”两张表为数据源，创建一个查询，按出厂价计算每种库存产品的总金额，并显示其产品名称和总金额。总金额的计算方法为： 总金额=出厂价×库存数量。查询名为“资金查询”。