第 5 章数据查询功能

第5章数据查询功能 • 创建和使用查询 • 在关系数据库中，查询（Query）的作用是从（多个）表中提取面向业务分析的数据，并对业务数据进行汇总性的分析。 • 查询实现的是DBMS的数据查询功能，而查询的基本工作原理又是关系运算。

5.1 关系运算 • 在关系数据库中，数据分别存储在不同的二维表中，因此二维表具有一定的独立性。同时，数据库又是具有逻辑关系的数据集合，不同二维表中的数据又是相互联系的。 • 为了从二维表中提取数据、生成面向业务分析的信息，可以借助于关系运算。 • 关系运算又可分为两类。一类是传统的关系运算，包括并、差、交、笛卡儿积；另一类是专门的关系运算，包括选择、投影和连接。

5.1.1 传统的关系运算 • 并（Union）运算：设R和S是n元关系，且两者对应属性的数据类型相同。定义R和S的并运算为 R ⋃ S = { t | t ∈ R ⋁ t ∈ S } ⋁ 代表或者的含义即：或属于关系R、或属于关系S的记录的集合。 • 差（Difference）运算：设R和S是n元关系，且两者对应属性的数据类型相同。定义R和S的差运算为 R − S = { t | t ∈ R ⋀ t ∉ S } ⋀ 代表并且的含义即：属于关系R、但不属于关系S的记录的集合。 • 交（Intersection）运算：设R和S是n元关系，且两者对应属性的数据类型相同。定义R和S的交运算为 R ⋂ S = { t | t ∈ R ⋀ t ∈ S } = R − ( R − S ) 即：既属于关系R、又属于关系S的记录的集合。 • 并、差、交运算的前提：关系R和关系S有相同的属性结构。

5.1.1 传统的关系运算

5.1.1 传统的关系运算 • 笛卡尔积（Cartesian Product）运算：设R是一个n元关系，S是一个m元关系，定义R和S的笛卡尔积运算为 • R☓S={(r1,r2,┈,rn,s1,s2,┈,sm)|(r1,r2,┈,rn)∈R ⋀ (s1,s2,┈,sm)∈S}

5.1.2 专门的关系运算 • 选择（Selection）：设R是一个n元关系，F是一个形如riθc的公式，其中θ ∈ {=,≠,>,<,≤,≥}，riθc表示属性值ri和指定值c的比较关系（实际上隐含着一个条件，称θ为关系运算符）。定义R的选择运算为 σF(R) = { (r1,r2,┈,ri,┈,rn) | (r1,r2,┈,ri,┈,rn) ∈ R ⋀ riθc } 即：在关系R中选择某一属性值满足一定条件（与指定值的比较关系成立）的记录。

5.1.2 专门的关系运算 • 例1，在如下表Student中，若要找出所有女学生的元组，就可以使用选择运算来实现，其中的条件riθc是：Sex=“女”。 σ Sex=“女”(Student) = { t | t ∈ Student ⋀ t.Sex=“女”} • 选择运算是对单个关系进行水平分解，是从行的角度进行的运算。

5.1.2 专门的关系运算 • 例2，在如下表Student中，若要找出所有性别为女且入学成绩在500分以上（包括500分）的学生记录，也可以使用选择运算来实现，其中的条件是：（Sex=“女”）⋀（Entrancescore≥500）。 σ（Sex=“女”）⋀（Entrancescore≥ 500）(Student) = { t | t ∈ Student ⋀ t.Sex=“女” ⋀ t.Entrancescore≥ 500 } • 选择运算中的条件可以是多个简单条件的逻辑组合。

5.1.2 专门的关系运算 • 投影（Projection）：设R是一个n元关系，定义R的投影运算为 Πi1,i2,┈,im(R) = { (ri1,ri2,┈,rim) | (r1,r2,┈,ri1,ri2,┈,rim,┈,rn) ∈ R } 其中i1,i2,┈,im表示所选取属性列的编号，且m < n。即：从关系R中选取若干属性列组成新的关系。

5.1.2 专门的关系运算 • 例如，在如下表Student中，若要仅显示所有学生的StudentID（学号）、Name（姓名）和Sex（性别），那么可以使用投影运算来实现。 ΠStudentID, Name, Sex (Student) • 投影运算是对单个关系进行垂直分解，是从列的角度进行的运算。

5.1.2 专门的关系运算 • 连接（Join)运算：设R是n元关系，S是m元关系，A是R的属性，B是S的属性，A和B的值域具有相同的数据类型，θ ∈ {=,≠,>,<,≤,≥}。定义R和S的连接（Jion)运算为 R⋈AθB S = { rs | r∈R ⋀ s∈S ⋀ (r[A] θ s[B]) } 其中，r[A]表示元组r在属性A上的值，s[B]表示元组s在属性B上的值。r=(r1,r2, ┈r[A], ┈, rn)，s=(s1,s2,┈, s[B]┈, sm)，rs形如(r1,r2, ┈r[A], ┈, rn, s1,s2,┈, s[B]┈, sm)。 • 并称A和B是连接属性。

5.1.2 专门的关系运算 • 需要注意的是，连接属性可以有不同的名称，但必须表示相同的含义并有相同的数据类型，以保证属性值的可比性。

5.1.2 专门的关系运算 • 需要特别注意的是，等值连接与一对多表间关系有着密切的联系：一对多表间关系为等值连接运算提供了基础，通过一对多表间关系中的关联字段可以进行等值连接运算。实际上，等值连接运算中的连接属性就是一对多表间关系中的关联字段。 • StudentID是关系Student的主键，StudentNO是关系CourseGrade相对于关系Student的外键（关系CourseGrade的主键是StudentID和Course的组合）；关系Student和关系CourseGrade是父表和子表的一对多表间关系，关联字段在关系Student和关系CourseGrade中分别是StudentID和StudentNO，同时也是等值连接运算中的连接属性。

5.1.2 专门的关系运算 • 由此可见，在一对多表间关系的基础上，将关联字段作为连接属性可以进行等值连接运算，从而将父表与子表中的数据有机地联系在一起。 • 在上述等值连接运算的结果关系中，学生姓名（Name）与课程成绩（Grade）的匹配正是通过等值连接运算得到的。

5.1.2 专门的关系运算 等值连接和笛卡儿积的比较

5.1.2 专门的关系运算 • 若在等值连接的结果关系中去掉重复的属性，或者说，连接属性在结果关系中只出现一次，则此连接称为自然连接。

5.1.2 专门的关系运算 • 通过4.3.3和4.3.4两节的介绍，我们已经知道：在关系数据库中，为了减少数据冗余，业务数据是分散地存储在多个二维表中的，每个二维表具有相对的独立性。 • 但二维表之间又存在着相互联系，二维表之间的联系主要是指父表与子表之间的一对多联系——通过关联字段将父表与子表中的数据联系起来。 • 连接运算就具体实现了相关表中的数据联系。

5.1.3 关系运算的总结 • 关系运算是对关系进行的闭合运算，因此，运算对象是关系，并且运算的结果仍为关系。 • 并、差、交、笛卡儿积为二元关系运算，即是对两个关系进行的运算，并且并、差、交运算要求两个关系的对应属性有相同的含义和数据类型。 • 选择、投影为一元关系运算，即对单个关系进行的运算；选择运算是从行的角度进行的运算，投影运算是从列的角度进行的运算。 • 连接为二元关系运算，其中的两个关系是通过连接属性完成运算的，且连接属性必须有相同的数据类型并表示相同的含义。在关系数据库中，等值连接运算是最常见的连接运算。 • 在RDBMS中，数据查询功能就是通过选择、投影和等值连接运算完成的。换句话说，从数据库中的若干表提取所需要的数据，都要借助于选择、投影和等值连接运算才能实现。在Access数据库中，“查询”对象中主要体现和包含了关系的选择、投影和等值连接运算。

5.2 查询的设计与创建 • 在Access数据库中，查询（Query）可以从多个表中提取数据、并对数据进行组合和分析。 • 使用查询，可以按照不同的准则或方式对表中的数据进行抽取，以得到用户需要的数据和信息。

5.2.1 查询、关系运算及SELECT命令 • 在Access数据库中，大部分查询可以使用数据查询语言（Data Query Language，DQL）中的SELECT命令来实现数据查询功能。 SELECT命令的基本语法格式为 SELECT字段名1[，字段名2…] FROM表名 [WHERE条件表达式] [GROUP BY字段列表] [ODRDE BY字段名 ASC|DESC] • 关系的选择、投影和等值连接运算都可以通过SELECT命令来体现和实现。

5.2.1 查询、关系运算及SELECT命令 选择运算 σ Sex=“女”(Student) = { t | t ∈ Student ⋀ t.Sex=“女”} • 可以使用如下SELECT命令来实现 • SELECT StudentID, Name, Sex, Birthday, Score FROM Student WHERE Sex=“女” 投影运算 ΠStudentID, Name, Sex (Student) • 可以使用如下SELECT命令来实现 SELECT StudentID, Name, Sex FROM Student • 多数情况下，一个查询对象对应着一个SELECT命令

5.2.2 查询的视图 在Access数据库中，查询对象有五种视图。这五种视图及其作用分别如下： 1．设计视图：用于创建新的查询对象，或者修改已有的查询对象； 2．数据表视图：可以以二维表的形式显示查询结果； 3．SQL视图：用于查看查询对象所对应的SELECT命令，该命令属于SQL语句； 4．数据透视表视图：以表格形式对查询结果进行进一步的多维分析； 5．数据透视图视图：以图形方式显示、对比查询结果。

5.2.2 查询的视图 查询的设计视图 • 查询的含义是：从“供应商”和“产品”表中查询单价在10与20之间（包括10和20）的产品信息，并检索出其中的公司名称、产品名称、单价等三个字段上的数据，而且将查询结果按照“公司名称”的降序排列。

5.2.3 在设计视图中创建查询 在Access数据库中，创建查询有两种方法。 1．在设计视图中创建查询。 2．使用向导创建查询。其中，“在设计视图中创建查询”的方法最为灵活、有效和实用，本章将详细介绍。【练习5-1（P92）】查询单价在10～20元（包括10元和20元）的产品数据，要求在查询结果中列出每个产品的供应商的公司名称以及相应的产品名称，并将查询结果按照公司名称的降序排列。

5.2.3 在设计视图中创建查询 “罗斯文”示例数据库中的表间关系及对应的关联字段

5.2.3 在设计视图中创建查询 SELECT 供应商.公司名称, 产品.产品名称, 产品.单价 ③ FROM 供应商 INNER JOIN 产品 ON 供应商.供应商ID = 产品.供应商ID ① WHERE (((产品.单价)>=10 And (产品.单价)<=20)) ② ORDER BY 供应商.公司名称 DESC; ④

5.2.3 在设计视图中创建查询

5.2.4 在查询中使用条件来检索特定记录 • 条件是查询中用来识别所需特定记录的限制准则。使用查询条件，可以从数据来源中检索出具有指定特征的记录。 • 在查询的设计视图中指定条件，就是在设计网格中某字段对应的“条件”单元格中输入相应的表达式，表达式描述了所查询的记录在该字段上的取值具有什么样的特征。 • 查询条件中的表达式可以是常量、变量、运算符、字段名和函数等元素按照一定规则的组合，代表着一定的准则。

5.2.4 在查询中使用条件来检索特定记录 在设计视图中创建查询条件的常见办法有： 1．检索字段值为指定常量。例如，如果只查询“产品名称”为“鸭肉”的产品记录，只需要在“产品名称”字段下方对应的“条件”单元格中输入“鸭肉”即可。 2．检索字段值介于指定值之间的记录，即通过使用Between...And 运算符或比较运算符（=、<、>、<>、<= 和 >=）来创建条件，具体例子如表5-2-2所示。表 5-2-2 字段值介于指定值之间的查询条件

5.2.4 在查询中使用条件来检索特定记录 3．使用通配符和Like运算符检索部分或完全匹配的内容。所谓通配符（Wildcard Character），就是可用于替代一个或多个字符的特殊键盘字符。常见的通配符如下表所示。例如，为了查找“产品名称”字段包括“X…X鱼”的产品记录，则可以在该字段对应的“条件”单元格中输入Like “*鱼”，这样就可能找出“产品名称”为“雪鱼”、“黄鱼”、“金枪鱼”的产品记录；而如果在“条件”单元格中输入Like “?鱼”，则只可能找出“产品名称”为“雪鱼”、“黄鱼”的产品记录，而找不出“产品名称”为“金枪鱼”的产品记录。

5.2.4 在查询中使用条件来检索特定记录 4．检索指定值范围以外的记录。例如，可以在“客户ID”字段对应的“条件”单元格中输入 Not Like “A*”，以查找“客户ID”不是以字母A开头的客户记录。又例如，在“单价”字段对应的“条件”单元格输入 Not 10（此条件等价于 <>10 ），以查找“单价”不为10的产品记录。 5．用In运算符检索包括值列表中一个值的记录。例如，在“供应商”表中查找北京、上海或沈阳等城市的供应商记录，可以在“城市”字段对应的“条件”单元格中输入下列表达式：In ("北京","上海","沈阳")。

5.2.4 在查询中使用条件来检索特定记录 6．构造复合的条件。 • 当需要检索必须同时满足多个条件的数据时，可以使用And逻辑运算符将这些条件连接起来。例如，为了表示“单价在10元和20元之间（包括10元和20元）”的条件，既可以在“单价”字段对应的“条件”单元格中输入表达式“Between 10 And 20”，也可以输入表达式“>=10 And <=20”。 • 当需要检索只需符合多个条件之一的数据时，则可以使用Or逻辑运算符将这些条件连接起来。例如，在“产品名称”字段对应的“条件”单元格中输入表达式："鸭肉" Or "鸡肉"，则表示检索“产品名称”为“鸭肉”或“鸡肉”的产品记录。

5.2.4 在查询中使用条件来检索特定记录 6．构造复合的条件。 • 在相同字段或不同字段对应的“条件”或“或”单元格中输入表达式，也可以构造出复合的条件。

5.2.4 在查询中使用条件来检索特定记录 6．构造复合的条件。（(产品ID Between 1 And 10) AND (单价>30)） Or (（产品ID Between 20 And 30) AND (单价<10)）

5.3 选择查询 • 在Access数据库中，根据对数据来源的操作方式以及对查询结果组织形式的不同，可以将查询分为选择查询、交叉表查询、操作查询、参数查询和SQL查询五大类。本节首先介绍选择查询。 • 在Access数据库中，选择查询是最常见的查询类型，它从一个或多个表中检索数据，并且以二维表的形式显示查询结果。 • 【练习5-1】中所创建的查询就是一个选择查询，该查询从“供应商”和“产品”表中检索有关供应商的公司名称、产品名称和单价等产品数据，并且这些数据都直接来自于相关表中的对应字段。 • 此外，在Access查询中，还可以利用Access内置函数从字段数据中进一步提取信息。

5.3.1 利用Access内置函数从字段数据中提取信息 【练习5-2（P99）】 “雇员”表根据“出生日期”或“身份证号码”推算雇员的“年龄”；将雇员的“姓氏”和“名字”组合成“姓名”。

5.3.1 利用Access内置函数从字段数据中提取信息

5.3.1 利用Access内置函数从字段数据中提取信息 • 在一个具体的RDBMS产品中，大都提供了专门的内置函数和运算符，利用这些内置函数和运算符可以对表中的数据进行专门的处理，以便从数据中提取更细的或特定的数据。 • 例如，通过如下形式的表达式 "第" & DatePart("q",[订购日期]) & "季度“ 可以得到类似于“第1季度”、“第2季度”、“第3季度”和“第4季度”这样的文本串数据。 • 根据所处理数据的类型不同，可以将Access内置函数划分为算术函数、文本函数、日期/时间函数等。每种函数只能处理特定类型的数据。

5.3.1 利用Access内置函数从字段数据中提取信息 常用的Access内置函数及其相关用法

5.3.1 利用Access内置函数从字段数据中提取信息

5.3.2 基于记录分组的选择查询 • 在对数据库中的数据进行分析时，有时需要依据一定的标准将有关数据（记录）进行分组，并在此基础上对记录作总计、计数、平均值以及其他类型的聚合计算。 • 又例如，计算并对比每个季度的销售额，就可以根据交易时间将同一季度的交易记录归入同一组并计算每笔交易的销售额，然后将同一季度中每笔交易的销售额加起来，就可以得到该季度的销售额。 • Access数据库中的选择查询就可以实现这种基于记录分组的聚合计算。

5.3.2 基于记录分组的选择查询 • 【练习5-3（P103）】创建选择查询，统计每个供应商提供的产品种类数，并显示供应商的公司名称和对应的产品种类数。 “产品”表

5.3.2 基于记录分组的选择查询 SELECT 供应商.公司名称, Count(产品.产品ID) AS 产品ID之计数 FROM 供应商 INNER JOIN 产品 ON 供应商.供应商ID = 产品.供应商ID GROUP BY 供应商.公司名称;

5.3.2 基于记录分组的选择查询 基于“分组”的聚合函数和其他选项

5.3.2 基于记录分组的选择查询 【练习5-4（P106）】采用记录分组的查询方法，对每张订单的销售额小计（不考虑折扣情况）进行统计查询，要求显示订单ID、客户的公司名称和每张订单的销售额小计。

5.3.2 基于记录分组的选择查询

5.4 交叉表查询 【练习5-5（P109）】根据查询结果设计分组选择查询（由学生自己完成）。下图是一个分组选择查询的数据表视图，其中，“公司名称”和“类别名称”为分组基准字段，第3列为某供应商提供的某大类别的产品种类数。

5.4 交叉表查询 【练习5-6（P109）】创建查询结果（数据表视图）如下图所示的交叉表查询。

第 5 章数据查询功能