《查询的创建和使用.pptx》由会员分享，可在线阅读，更多相关《查询的创建和使用.pptx（77页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第4章查询的创建和使用4.1查询概述4.1.1查询的概念根据给定的条件从数据库的一个或多个表中筛选出符合条件的记录，构成一个数据集合，供使用者查看、更改和分析使用。查询从中获取数据的表称为查询的数据源。查询的结果是一个动态的数据集合，会随着数据源的变化而变化。查询的主要功能:1.提取数据n可以从一个或多个表或查询中重新组合、检索出用户需要的数据。n可以从一个或多个表中选择部分或全部字段，也可以从一个或多个表中将符合条件的记录选取出来。2.数据更新更新操作包括添加、修改、删除记录。查询的主要功能:3.实现计算n在查询中对相关数据进行统计、计算，n还可以建立新的字段来保存计算的结果，这样可以简化处
2、理工作，用户不必每次都在原始数据上进行检索，从而提高了整个数据库的性能。4.产生新的表并可作为其他对象的数据源可以建立一个新表来保存查询的结果。查询的结果还可作为窗体、报表等对象的数据源。4.1.2查询的类型1.选择查询最常用、最基本的一种查询。它可以根据用户提供的条件，从一个或多个表中检索数据，并且在数据表中显示结果。2.参数查询是一种交互式查询，在建立查询时为用户留有动态参数接口。在执行参数查询时会显示一个对话框，提示用户根据需要输入查询条件，然后再检索相应的记录。参数查询具有很强的灵活性。3.交叉表查询可以计算并重新组织数据的结构，更加方便地分析数据。交叉表查询可以计算数据的平均值、总计
3、、最大值、最小值或其他类型的总和。4.1.2查询的类型4.操作查询操作查询用于添加、更改或删除数据。操作查询有以下4种类型：删除查询：删除查询可以从一个或多个表中删除一组记录。追加查询：追加查询可将一组记录添加到一个或多个表的尾部。运行追加查询的结果是向相关表中自动添加记录，增加表的记录数。更新查询：更新查询可根据指定条件对一个或多个表中的记录进行更改。生成表查询：生成表查询是利用一个或多个表中的全部或部分数据创建新表。4.1.2查询的类型5.SQL查询SQL是一种结构化查询语言，是数据库操作的工业化标准语言，所有的数据库管理系统都支持这种语言。SQL查询就是使用SQL语句创建的查询。4.1.
4、3查询视图1.设计视图设计视图用于对查询设计进行编辑，通过设计视图可以创建SQL之外的各种类型查询。2.数据表视图数据表视图用于查看查询运行的结果，是查询的数据浏览器。3.SQL视图SQL视图是查看和编辑SQL语句的窗口，可以查看用设计视图创建的查询所产生的SQL语句，也可以对SQL语句进行编辑和修改。4.1.3查询视图4.数据透视表和数据透视图视图在数据透视表和数据透视图视图中，可以根据需要生成数据透视表和数据透视图，从而对数据进行分析，得到直观的分析结果。4.2选择查询的创建选择查询的概念：根据用户提供的条件，从一个或多个表中检索数据，并且在数据表中显示结果。1.建立单表查询【例4-1】查
5、询学生的基本信息，要求显示学生的学号、姓名、性别、出生日期和院系等信息，所建查询命名为“学生基本信息查询”。4.2.1使用简单查询向导2.建立多表查询当所需要查询的信息来自两个或两个以上的表或查询时就需要建立多表查询。建立多表查询的各个表必须要有关联字段，并且事先应通过这些关联字段建立了表间关系。【例4-2】查询学生的课程成绩，要求显示“学号”、“姓名”、“课程号”、“课程名称”和“成绩”。4.2.2使用查询设计视图n打开查询设计视图的方法n查询设计视图的结构【例4-3】查询院系为“物流学院”的学生信息，要求显示“学号”、“姓名”、“出生日期”及“籍贯”并按出生日期升序显示。【例4-4】查询选
6、修了“大学计算机基础”课程的学生信息，要求显示“学号”、“姓名”、“院系”及“课程名称”和“成绩”，并按成绩降序显示。4.2.3查询条件n打开表达式生成器的方法n表达式生成器的结构表达式就是运算符、常量、函数和字段名称、控件和属性的任意组合，计算结果为单个值。查询条件是指在创建查询时为了查询到所需要的记录，通过对字段添加限制条件，使查询结果中只包含满足条件的记录。1.常量（1）数字型常量：如分数“650”、“590”等。（2）文本型常量：如“信息学院”、“物流学院”等，可使用“*”和“？”通配符。（3）日期型常量：如出生日期#1992-7-1#。（4）是否型常量：Yes、No、True、Fal
7、se。（5）空字段值：“IsNull”表示字段值为空，“IsNotNull”表示字段值不为空。表达式中常用的运算符有算术运算符、关系运算符、逻辑运算符、标识符和特殊操作符。算术运算符：如+、-、*、/等。关系运算符：如、=、=、=、。逻辑运算符：如AND、OR、NOT。标识符：通常是一个对象的名字，一般指的是字段的名字。引用字段名称时需要用“”将其名称括起来。2.表达式中的运算符（1）用逻辑运算符组合条件And表示当两个操作数的值都为TRUE时，表达式的值才为TRUE。Or表示两个操作数的值有一个为TRUE时，表达式的值就为TRUE。用Or运算符可将条件进行扩展。Not则生成操作数的相反值。（
8、2）用关系运算符组合条件（3）用Between运算符组合条件Between运算符也可用于指定字段的取值范围，范围之间用And连接。（4）用like组合条件Like运算符用于查找文本型字段的部分值，格式为：Like字符模式在所定义的字符模式中，可用“？”表示该位置可匹配任何一个字符；用“*”表示该位置可匹配零个或多个字符；用“#”表示该位置可匹配一个数字；用“”描述一个范围。（5）用In组合条件In运算符用于指定字段的一系列的值，与like不同，in运算中不允许使用通配符。3.函数常用的数值函数函数功能Abs（数值表达式）返回数值表达式值的绝对值Int（数值表达式）返回数值表达式值的整数部分Sq
9、r（数值表达式）返回数值表达式值的平方根Sgn（数值表达式）返回数值表达式值的符号值常用的文本函数函数功能Left（字符串表达式，n）从字符串表达式左侧第1个字符开始截取n个字符Right（字符串表达式，n）从字符串表达式右侧第1个字符开始截取n个字符Len（字符串表达式）返回字符串表达式中字符的个数Mid（字 符 串 表 达 式，n1,n2）从字符串表达式左边n1位置开始，截取连续n2个字符常用的日期时间函数函数功能Now（）返回系统当前的日期时间Date（）返回系统当前的日期Time（）返回系统当前的时间Day（日期表达式）返回日期中的日Month（日期表达式）返回日期中的月份Year（日
10、期表达式）返回日期中的年份4.2.4编辑查询1.增加字段2.删除字段3.修改字段4.移动字段5.调整设计网格的列宽4.3在查询中进行计算4.3.1查询中的计算功能预定义计算自定义计算查询中的常用计算计算名功能合计计算一组记录中某个字段值的总和平均值计算一组记录中某个字段值的平均值最大值计算一组记录中某个字段值的最大值最小值计算一组记录中某个字段值的最小值计数计算一组记录中记录的个数First一组记录中某个字段的第一个值Last一组记录中某个字段的最后一个值Expression创建一个由表达式产生的计算字段Where设定分组条件以便选择记录4.3.2总计查询总计查询是通过对查询设计视图窗口中的总
11、计行进行设置实现的，用于对查询中的全部记录进行总和、平均值、最大值、最小值的计算。【例4-5】查询全校学生的入学成绩情况：平均入学成绩、最高成绩和最低成绩，并定义查询字段依次为平均分、最高分、最低分。4.3.3分组总计查询分组是指在查询设计窗口中指定某一字段为分组字段，将该字段值相同的所有记录组合在一起，并对分组进行统计计算。【例4-6】查询全校各院系学生的入学成绩情况：即查询各院系的平均入学成绩、最高成绩和最低成绩，定义查询字段依次为平均分、最高分、最低分，并要求平均分保留小数点后1位。4.3.4添加计算字段当需要统计的数据在表中没有相应的字段，或者用于计算的数据值来源于多个字段时，应在设计
12、网格中添加一个计算字段，计算字段是指根据一个或多个表中的一个或多个字段并使用表达式建立的新字段。【例4-7】计算所有学生的年龄，要求显示每个学生的“学号”、“姓名”和“年龄”。【例4-8】查询每个学生的奖学金级别，要求显示的字段有：“学号”、“姓名”、“院系”、“入学成绩”和“奖学金等级”。奖学金等级的划分标准是：入学成绩=650的为“一级”，620=入学成绩650的为“二级”，590=入学成绩=650,一级,IIf(入学成绩=620,二级,IIf(入学成绩=590,三级)4.4其他查询的设计4.4.1交叉表查询交叉表查询以一种紧凑的、类似于电子表格的形式显示查询字段，并且可以计算该字段的合计
13、或平均值等。使用交叉表查询计算和重构数据，可以简化数据分析。1.交叉表查询向导【例4-9】建立学生与课程成绩交叉表查询，统计每名学生选修的课程及成绩。2.设计视图中的交叉表查询【例4-10】利用交叉表查询，统计每名学生选修课程情况。要求显示学生“姓名”、选课数量、平均成绩以及所学课程的成绩。4.4.2参数查询创建这种参数的查询只要在查询设计窗口中，在要定义为参数的字段的条件行中输入用“”括起来的提示信息，在运行查询时这些提示信息出现在“输入参数值”输入框的上方，然后保存查询文件。【例4-11】创建一个带有参数的查询，要求先输入学生的学号，之后显示该学生的“学号”、“姓名”、“性别”、“课程名称
14、”及“成绩”。练习:设计一带参数查询：要求输入某一院系名称，显示该院系所有学生的基本信息,包括学号、姓名、性别、出生日期和籍贯。三实验内容1创建一个查询，计算每位读者已办理借书证的年限。要求显示“读者编号”、“姓名”、“性别”、“类别”、“部门”和已办理年限，所建查询命名为“读者借书证的时间”。已办理年限:year(date()-year(办证日期)实验3查询中的计算2利用总计查询统计每类读者，即“教工”、“本科”和“研究生”的平均借书数量，所建查询命名为“各类读者的平均借书量”。以“类别”字段为分类字段，对“借阅日期”字段进行计数。3利用总计查询统计每个部门的平均借书数量及所借图书的总价格，
15、所建查询命名为“各部门平均借书量及价格”。4利用总计查询统计每个部门的罚款总额，所建查询命名为“各部门的罚款额”。实验4创建参数查询1设计一个参数查询，要求输入读者姓名，可显示该读者的借书记录，包括“读者编号”、“姓名”、“性别”、“部门”及所借阅的图书的“书名”、“出版社”、“作者”和“借阅日期”、“还书日期”，所建查询命名为“按读者姓名查询”。2设计一个参数查询，要求输入图书的ISBN，可显示该图书的“书名”、“出版社”、“作者”及借阅过该图书的“读者编号”、“姓名”、“性别”、“部门”及“借阅日期”、“还书日期”，所建查询命名为“按书号查询”。3设计一个多参数查询，要求输入一个开始日期和
16、结束日期，显示在该时间段内借书的读者信息，包括“读者编号”、“姓名”、“性别”、“部门”及所借阅的图书的“书名”、“出版社”、“作者”和“借阅日期”、“还书日期”，所建查询命名为“按日期查询”。在“借阅日期”字段的条件行中输入：=开始日期and=结束日期4.4.3操作查询Access2010的操作查询包括以下几种查询：生成表查询。利用从一个或多个表获得的数据创建一个新的表。更新查询。对一个或多个表中的多个记录的某些字段的值进行修改。追加查询。为一个或多个表添加从一个或多个表中获得的一组记录。删除查询。对一个或多个表中满足条件的一组记录进行删除操作。1.生成表查询【例4-12】在“新教务管理数据
17、库”中创建一个“不及格学生名单”的表。要求该表中要有：“学号”、“姓名”、“性别”、“院系”、“课程名称”和“成绩”等字段。【例4-13】将“学生”表中院系为“信息学院”的学生记录保存到当前数据库名为“信息学院学生名单”的新表中。2.更新查询【例4-14】在“不及格学生名单”表中添加一个“补考日期”字段，并将“2013/10/10”输入到“补考日期”字段中。3.追加查询【例4-15】将“学生”表中院系为物流学院的所有学生追加到“信息学院学生名单”表中。4.删除查询【例4-16】将“信息学院学生名单”表中院系字段为“物流学院”的所有学生删除。4.4.4重复项、不匹配项查询1.查找重复项查询根据查
18、询结果可以确定在表中某个字段的值是否有重复，或确定记录在表中是否共享相同的值。【例4-17】使用重复项查询向导，在“学生”表中查询同名学生，要求显示学生“学号”、“姓名”、“性别”和“院系”等字段。练习：查找入学成绩相同的学生信息，要求显示学生学号、姓名、性别、院系和籍贯。2.查找不匹配项查询在关系数据库中，当建立了一对多的关系后，通常在“一方”表中的每一条记录，与“多方”表中的多条记录相匹配。但是也有可能存在“多方”表中没有记录与之匹配。因此要执行查找不匹配查询至少需要两个表，并且这两个表要在同一个数据库中。【例4-18】利用不匹配项查询，查找出没有考试成绩的学生名单，要求显示学生“学号”、
19、“姓名”、“性别”和“院系”等字段。练习：查询在“课程”表中列出的但是没有被学生选修过的课程。要求显示：课程编号、课程名称、学分、开课院系。4.5SQL查询4.5.1查询与SQL视图Access2010中的查询是以SQL语句为基础来实现查询功能的，因此，Access2010中所有的查询都可认为是SQL查询。各种查询操作，系统自动地将操作命令转换为SQL语句。只要单击“SQL视图”就可以看到系统所生成的SQL代码。4.5SQL查询4.5.2SQL简介SQL语言是1974年由Boyce和Chamberlin公司提出，并首先在IBM公司研制的关系数据库原型系统SystemR上实现。由于它具有功能丰富
20、、使用灵活、语言简捷易学等特点，被业界广泛采用。经过不断修改、扩充和完善，1986年被美国国家标准局批准成为关系型数据库语言的标准。SQL语言的功能包括以下4个方面：数据定义语言DDL：定义数据库的逻辑结构，包括定义表、视图和索引等。数据操纵语言DML：包括数据检索和数据更新两大操作，其中数据更新包括插入、删除和修改操作。数据控制语言：包括基本表和视图等的授权、完整性描述以及事务开始和结束等控制语句等。嵌入式SQL语言：SQL语言既可以作为独立的语言供终端用户联机使用，也可以作为宿主型语言嵌入到某种高级程序设计语言中使用。规定了SQL语言在宿主语言程序中的使用规则。SQL语言的应用有以下几个方
21、面：在查询“设计”视图中创建查询时，Access将在后台构造等效的SQL语句。某些查询不能在设计网格中创建。对于传递查询、数据定义查询和联合查询，必须直接在SQL视图中创建SQL语句。在Access中，某些情况下，Access会帮助用户填入SQL语句。SQL语言的功能SQL功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOTE1.CREATE语句创建表的一般格式为：CREATETABLE()NOTNULLPRIMARYKEY|UNIQUE,()NOTNULLPRIMARYKEY|UNIQUE,)；说
22、明：其中的是所要定义的基本表的名字，它可以由一个或若干个字段组成。各个字段的数据类型必须用字符表示，如字符型用CHAR或TEXT，日期型用DATE，货币型用MONEY，备注型用MEMO，整型用INTEGER，单精度用SINGLE表示等等。定义单个字段为主键时，可以直接在该字段名后加上PRIMARYKEY或UNIQUE关键字。如果要将多个字段定义为主键或唯一键，应使用PRIMARYKEY或UNIQUE子句。【例4-19】使用SQL语句创建一个名为STUDENT的表，其表结构为：学号（文本型，10字符，主键）、姓名（文本型，8字符，不允许为空值）、性别（文本型，1字符）、出生日期（日期/时间型）、
23、贷款否（是/否型）、简历（备注型）、照片（OLE对象型）。CREATETABLESTUDENT(学号TEXT(10)PRIMARYKEY,姓名TEXT(8)NOTNULL,性别TEXT(1),出生日期DATE,贷款否LOGICAL,简历MEMO,照片OLEOBJECT);【例4-20】使用SQL语句创建一个名为COURSE表，其表结构为：课程号（文本型，3字符，主键）、课程名称（文本型，50字符）、学分（单精度型）、授课院系（文本型，50字符）。CREATETABLECOURSE(课程号TEXT(3)PRIMARYKEY,课程名称TEXT(50),学分SINGLE,授课院系TEXT(50);【
24、例4-21】使用SQL语句创建一个名为SC表，其表结构为：学号（文本型，10字符）、课程号（文本型，3字符）、成绩（单精度型），主键由学号和课程号组成。同时使SC表与STUDENT表之间通过“学号”建立关系、SC表与COURSE表之间通过“课程号”字段建立关系。CREATETABLESC(学号TEXT(10)REFERENCESSTUDENT(学号),课程号TEXT(3)REFERENCESCOURSE(课程号),成绩SINGLE,PRIMARYKEY(学号,课程号);2.ALTER（1）在表中添加字段，其一般格式为：ALTERTABLEADD();【例4-22】在STUDENT表中增加一个“
25、电话号码”字段，类型为“长整型”。ALTERTABLESTUDENTADD电话号码INTEGER;（2）修改字字段的数据类型，其一般格式为：ALTERTABLEALTER();注意：使用该命令只能修改字段的数据类型而不能修改字段名。【例4-23】将STUDENT表中新增加的“电话号码”字段修改为文本型、大小为8字符。ALTERTABLESTUDENTALTER电话号码TEXT(8);（3）删除字段，其一般格式为：ALTERTABLEDROP【例4-24】在STUDENT表中将新增加的“电话号码”字段删除。ALTERTABLESTUDENTDROP电话号码;3.DROP（1）删除索引，其一般格式
26、为：DROPINDEXON;（2）删除表，其一般格式为：DROPTABLE;删除表后，在表上定义的索引也一起被删除。4.INSERT其一般格式为：INSERTINTO(,，)VALUES(,)；说明：该语句的功能是将新记录插入到指定表中，新记录的字段名1的值为常量1、字段名2的值为常量2，如果INTO后缺省字段名，则必须为新记录中的每个字段都赋值，且插入的数据类型和顺序要与表中定义的字段一一对应。【例4-25】向STUDENT表中插入2个学生记录。第一条记录“学号”为“2010011123”，“姓名”为“李荣辉”，“性别”为“男”。第二条记录“学号”为“2010010148”，“姓名”为“黄娟
27、”，“性别”为“女”，“出生日期”为“1990-5-26”，“贷款否”为“是”，其他字段为空值。INSERTINTOSTUDENT(学号,姓名,性别)VALUES(“2010011123”,”李荣辉”,“男”);INSERTINTOSTUDENTVALUES(“2010010148”,”黄娟”,”女”，#1990-5-26#,YES,NULL,NULL);5.UPDATE其一般格式为：UPDATESET=,=，WHERE；说明：如果不带WHERE子句，则更新表中所有的记录。如果带WHERE子句，则只更新表中满足条件的记录。5.UPDATE【例4-26】将STUDENT表中姓名为“李荣辉”的出生
28、日期改为“1991-5-20”。UPDATESTUDENTSET出生日期=#1991/5/20#where姓名=“李荣辉”;【例4-27】将STUDENT表中所有女生的“贷款否”字段改为“否”。UPDATESTUDENTSET贷款否=NOWHERE性别=“女”;6.DELETE其一般格式为：DELETEFROMWHERE；说明：如果不带WHERE子句，则删除表中所有的记录，但表对象仍保留在数据库中。如果带WHERE子句，则只删除表中满足条件的记录。【例4-28】删除STUDENT表中学号为“2010011123”的学生记录。DELETEFROMSTUDENTWHERE学号=“201001112
29、3”;4.5.3SQL查询的创建SELECT语句的一般格式：SELECTALL|DISTINCT，FROM,WHEREGROUPBYHAVINGORDERBYASC|DESC说明：根据WHERE子句的条件表达式，从FROM子句指定的基本表或查询中找出满足条件的记录，再按SELECT子句中的目标列找出元组中的属性值形成结果表。ALL为默认值，表示所有满足条件的记录，DISTINCT用于忽略重复数据的记录，即在基本表中重复记录只出现一次。GROUP子句则表示将结果按表中的某一字段名（分组项）的值进行分组，该属性列值相同的元组为一个组，每个组产生结果表中的一条记录。GROUP子句还可以带HAVING
30、短语，表示只有满足指定条件的组才输出。ORDER子句将结果按表中的某一字段名（排序项）的值升序或降序排列。1.单表查询（1）选择查询【例4-29】从“学生”中查询信息学院的所有学生的“学号”、“姓名”、“性别”和年龄。SELECT学号,姓名,性别,YEAR(DATE()-YEAR(出生日期)AS年龄FROM学生WHERE院系=“信息学院”;（2）排序查询ORDERBY子句的格式为：ORDERBYASC|DESC【例4-30】在“成绩”表中查询8090分的记录，同一门课程按成绩降序排。SELECT*FROM成绩WHERE成绩BETWEEN80AND90ORDERBY课程号,成绩DESC;思考：查
31、询“学生”表中入学成绩在580650分的学生记录，要求显示学生的“学号”、“姓名”、“性别”，“院系”和“入学成绩”，同一院系按入学成绩降序排列。TOP短语的格式为：TOP或TOPPERCENT【例4-31】查询“成绩”表中成绩排在前5名的记录。SELECTTOP5*FROM成绩ORDERBY成绩DESC;（3）分组查询使用Groupby子句可以对查询结果按照某一字段的值进行分组。分组查询通常与SQL的统计函数一起使用。常用统计函数有：COUNT、AVG、SUM、MIN、MAX。【例4-32】在“学生”表中统计各院系的学生人数。SELECT院系,COUNT(*)AS各院系人数FROM学生GRO
32、UPBY院系;思考：在“学生”表中查询院系人数在3人以上的院系及人数。【例4-33】查询选修了3门以上课程的学生学号。SELECT学号,COUNT(课程号)AS选修课程门数FROM成绩GROUPBY学号HAVINGCOUNT(*)=3;2.多表查询在进行多表查询时，通常需要指定两个表的联接条件，该条件放在WHERE子句中，格式为：SELECTFROM，WHERE.=.；联接条件中的联接字段一般是两个表中的公共字段或语义相同的字段。在SELECT命令中还可以使用表的别名。格式为：SELECTFROM,WHERE.=.；【例4-34】查询所有学生的“学号”、“姓名”、选修的“课程名称”和“成绩”。
33、SELECT学生.学号,姓名,课程名称,成绩FROM学生,成绩,课程WHERE学生.学号=成绩.学号AND课程.课程号=成绩.课程号;或使用表的别名：SELECTXS.学号,姓名,课程名称,成绩FROM学生XS,成绩CJ,课程KCWHEREXS.学号=CJ.学号ANDKC.课程号=CJ.课程号;【例4-35】查询85分以上学生的“学号”、“姓名”、选修的“课程名称”和“成绩”。SELECTXS.学号,姓名,课程名称,成绩FROM学生XS,成绩CJ,课程KCWHEREXS.学号=CJ.学号ANDKC.课程号=CJ.课程号AND成绩85;3.嵌套查询【例4-36】查询姓名为“姚一萌”的同学所选修的
34、“课程名称”和“成绩”。SELECT学号,课程名称,成绩FROM成绩,课程WHERE学号=(SELECT学号FROM学生WHERE姓名=“姚一萌”)AND成绩.课程号=课程.课程号将一个SELECT语句包含在另一个SELECT语句的WHERE子句中，嵌套查询也称为子查询。子查询（内层查询）的结果用作建立其父查询（外层查询）的条件，因此，子查询的结果必须有确定的值。思考:查询选修了“大学计算机基础”课程的学生的“学号”、“姓名”、“院系”及“成绩”。4.合并查询所谓合并查询是指将两个SELECT语句的查询结果通过并运算（UNION）合并为一个查询结果。进行合并查询时，要求两个查询结果应具有相同的字段个数，并且对应字段的数据类型也必须相同。【例4-37】查询经济学院和商学院的学生的“学号”、“姓名”和“性别”。SELECT学号,姓名,性别,院系FROM学生WHERE院系=“经济学院”UNIONSELECT学号,姓名,性别,院系FROM学生WHERE院系=“商学院”;