数据库系统管理与维护(5)课件

数据库系统管理与维护ACCESS第5章数据查询和SQL命令学习要点了解查询的基本概念、基本功能及查询的种类掌握利用简单查询向导和查询设计器创建查询的方法掌握利用SQL语句创建查询的方法创建和修改查询查询概述查询查询是一个独立的、功能强大的、具有计算功能和条件检索功能的数据库对象。数据查询是数据库管理数据的一个最基本的工具。使用查询可以选择记录、更新表和向表中添加新记录。最常用的是使用查询选择一组满足指定准则的特定记录。可对多个数据表做复杂的查询。与表不同，查询不保存数据，只保存查询的命令。可以用查询向导、查询设计器或直接使用SQL命令创建查询。创建和修改查询查询的基本功能以一个表或多个不同的表为基础，创建一个新的数据集。通过指定查询的准则，使符合条件的数据汇总到结果集中。对某些字段进行计算，显示计算结果，完成数据的统计分析作为窗体或报表的数据源利用动作查询，实现对表格数据的修改、删除和更新。创建和修改查询查询种类选择查询指定条件浏览、统计表中的数据参数查询交叉表查询动作查询删除查询更新查询追加查询生成表查询SQL查询创建和修改查询创建简单查询使用简单查询向导创建查询Access提供简单查询向导、交叉表查询向导、查找重复项查询向导和查找不匹配项查询向导。其中简单查询向导可以创建一个简单的选择查询。只用于生成一些小的选择查询，将数据表中记录的全部或部分字段输出。简单查询向导例5.1使用简单查询向导创建一个选择查询，查询学生信息表中除照片字段之外的所有数据。操作步骤打开学籍管理数据库，选择“查询”对象，单击“新建”按钮，打开“新建查询”对话框选择“简单查询向导”，单击“确定”按钮，打开“简单查询向导”对话框简单查询向导在“表/查询”组合框中选择“表：学生信息表”，在“可用字段”列表框中列出学生信息表的所有可用字段。在“可用字段”列表框中选择要查询的字段，如“学生编号”，单击按钮将选定的字段移到左边的“选定的字段”列表框中。也可在“选定的字段”列表框选择某个已选的字段，单击按钮将该字段从“选定的字段”列表框移出。简单查询向导单击下一步打开对话框选择默认（明细查询），单击下一步简单查询向导保持默认查询标题“学生信息表查询”，单击“完成”简单查询向导得到查询的结果查询设计器使用查询设计器创建查询查询设计器功能比查询向导强大，除了可以创建查询，还可以编辑和修改查询。例5.2使用查询设计器创建一个查询，查询学生信息。操作步骤打开学籍管理数据库，选择“查询”对象，单击“新建”按钮，打开“新建查询”对话框查询设计器选择“设计视图”选项，单击“确定”按钮，打开查询设计器，出现“显示表”对话框。选择“学生信息表”，单击“添加”按钮，将选定的表添加到查询设计器中，关闭“显示表”对话框。查询设计器在查询设计器中，将表中相关字段逐个拖到设计网格相应的位置，“显示”复选框为选中，表示该字段的数据将在查询结果集中显示出来。查询设计器单击工具栏“保存”按钮，打开“另存为”对话框，输入该查询的名称“学生信息查询”，单击“确定”按钮保存。单击工具栏“运行”按钮，运行查询。查询设计器说明查询设计器分为上下两个部分，上半部分是查询的数据源，即数据表/查询对象显示区，用来显示该查询所使用的基本表或查询以及它们之间的关系；下半部分是设计区域。设计区域的网格的每一列对应查询结果集工作表的一个字段，网格的行标题表明了其字段的属性和要求。字段：指定查询工作表中所使用的字段的名称。表：指定查询字段所来自的数据表。排序：确定是否按该字段排序以及按何种方式排序。显示：确定该字段是否在查询结果中可见。条件：指定该字段的查询条件。或：用来提供多个查询准则，使查询结果中只包含满足条件的数据记录。修改简单查询修改简单查询在实际使用查询中，通常先用查询向导建一个简单查询，然后用查询设计器进行修改、细化，并加入查询条件，以达到实现复杂查询的目的。例5.3修改学生信息查询操作步骤打开数据库，选择需要修改的查询“学生信息查询”，单击“设计”按钮，打开查询设计器。删除字段选中要删除的字段，按Delete键取消该字段的“显示”复选框修改简单查询修改简单查询添加字段双击查询的数据源中的相关字段，将其自动添加到查询设计区的最后的字段的后面。选中查询的数据源中的相关字段，将其拖到查询设计区的网格中。修改简单查询插入字段选中查询的数据源中的相关字段，将其拖到要插入的位置，原先字段往后（右）移。修改简单查询改变排序方式在查询设计区中，要改变排序方式的字段下的“排序”下拉列表框中选择排序方式。修改简单查询增加查询条件在查询设计区中，在条件所包含的字段下的“条件”文本框中输入查询条件表达式。按Ctrl+F2组合键，打开表达式生成器，输入查询条件。修改简单查询修改单表查询为多表查询单击“查询设计”工具栏上的“显示表”按钮或选择“查询”-〉“显示表”命令，打开“显示表”对话框。“显示表”对话框的“表”选项卡中，选择要添加的表，添加到查询设计器中。修改字段以及相应的内容。保存查询。其他查询选择查询：是从一个或多个表中查找出符合条件的数据。动作查询：可以对数据库进行动态的修改。是在选择查询基础上创建的，具有选择查询的特性。可对数据源中的数据进行追加、删除和更新，并可在选择查询基础上生成新的表。删除查询更新查询追加查询生成表查询动作查询更新查询可对一个表或多个表中的记录进行批量修改。只能对已有的记录进行修改，不能添加或删除记录。例5.4创建更新查询，计算学生成绩表中的每个学生的总分和平均分操作步骤打开查询设计器，将学生成绩表的总分和平均分字段添加到查询设计区选择“查询”->”更新查询”命令，指定创建更新查询。在总分字段的“更新到”文本框输入计算公式：[大学语文]+[高等数学]+[大学英语]+[计算机基础]+[思想道德修养]在平均分字段的“更新到”文本框输入计算公式：

Round(([大学语文]+[高等数学]+[大学英语]+[计算机基础]+[思想道德修养])/5,2)注：Round为四舍五入函数保存查询执行更新查询时，由于更新操作是不可恢复的，所以会打开两个对话框要求用户对更新进行确认。动作查询参数查询通过输入参数可使得查询条件更加灵活。利用参数查询可提高查询的通用性。例5.5创建参数查询，查询班级中同学的信息。添加学生信息表中相应的字段到查询设计器的设计区中。在需要输入参数的字段所对应的“条件”文本框中输入带括号的文本[班级编号]动作查询选择“查询”->“参数”命令，打开“查询参数”对话框，在“参数”文本框内输入“班级编号”，在“数据类型”下拉列表框中选择“文本”选项。保存查询动作查询生成表查询生成表查询是利用已有的数据创建一个新表，将查询出的动态集合以表的形式保存。例5.6创建生成表查询，查询入学成绩大于等于600分的同学的信息，并生成一个新表为“优秀生”操作步骤打开查询设计器，将所有字段添加到查询设计区中。在查询设计区的网格中选择所需的字段和选择条件，选择“查询”->”生成表查询”命令，打开“生成表”对话框，在“表名称”下拉列表框中输入“优秀生‘。以“生成表查询”名保存查询。动作查询追加查询利用追加查询可以对数据表进行追加记录的操作。通常使用追加查询实现记录的批量追加。在追加查询时，只有匹配的字段才能被追加。例5.7创建追加查询。将学生表的入学成绩大于等于500的学生信息追加到优秀生表中。操作步骤打开查询设计器，将所有字段添加到查询设计区中，并设置入学成绩字段的条件为[入学成绩]>=500And[入学成绩]<600动作查询选择“查询”->”追加查询”命令，打开”追加”下拉列表框，在表名称的下拉列表框输入“优秀生”以“追加查询”为名称保存。动作查询删除查询使用删除查询可以从一个或多个数据表中删除符合指定条件的记录。例5.8将优秀表中入学成绩大于550分的学生的记录删除。操作步骤打开查询设计器，将优秀生表中的所有字段添加到查询设计区中选择“查询”->“删除查询”命令，在入学成绩字段“条件”文本框中输入删除记录的条件表达式“<550”动作查询以“删除查询”保存查询。SQL命令SQL（StructuredQueryLanguage）结构化查询语言，是关系数据库的标准语言SQL语言的主要功能是与各种数据库建立联系，进行沟通。可以用来执行数据查询、数据更新、数据提取等操作。SQL是一个通用的、功能极强的关系数据库语言，目前绝大多数关系数据库都采用SQL语言标准。SQL的产生与发展SQL标准的进展过程标准大致页数发布日期SQL/861986.10SQL/89(FIPS127-1)120页1989年SQL/92622页1992年SQL991700页1999年SQL20032003年SQL的特点1.综合统一集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。可以独立完成数据库生命周期中的全部活动：定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护数据库安全性、完整性控制等用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。数据操作符统一SQL的特点2.高度非过程化非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径SQL只要提出“做什么”，无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。SQL的特点3.面向集合的操作方式非关系数据模型采用面向记录的操作方式，操作对象是一条记录SQL采用集合操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合SQL的特点4.以同一种语法结构提供多种使用方式SQL是独立的语言能够独立地用于联机交互的使用方式SQL又是嵌入式语言SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用SQL的特点5.语言简洁，易学易用SQL功能极强，完成核心功能只用了9个动词。SQL功能动词数据查询SELECT数据定义CREATE，DROP，ALTER数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKESQL查询命令SQL查询就是用SQL语句创建的查询，是使用最为灵活的一种查询方式。用户可以通过SQL语句创建出复杂的查询条件。三种特有的查询形式：联合查询、传递查询和数据定义查询。使用最多的是SELECT语句。是SQL数据库语言的核心。５个主要子句：FROM,WHERE,GROUPBY,HAVING和ORDERBYSQL查询命令SELECT语句的语法SELECT[ALL|*|DISTINCT]查询项１[查询项２…]FROM数据源[WHERE条件][GROUPBY分组表达式][HAVING条件][ORDERBY排序项|[ASC]|[DESC]][]可选，｜多个选一个查询项：只要输出的查询项目（列、字段、表达式等）数据源：表或查询。排序项：指定的排序的关键字，一个或多个字段SQL查询命令例5.9创建查询１，查询学生信息表中的学号、姓名等字段。打开学籍管理数据库，选择对象“查询”，打开查询设计器，关闭“显示表”窗口。使用“查询”->“SQL特定查询”->“联合”命令，打开联合查询窗口，输入查询的SQL命令：SELECTALL学生编号,姓名,性别,出生日期,入学成绩FROM学生信息表单击工具栏保存按钮，打开另存为对话框，以“SQL查询１”为名保存查询，运行查询SQL查询命令各项说明ALL返回查询到的所有记录，包括重复记录，可省略。＊返回数据源中所有字段的信息。DISTINCT对于重复的数据行，在SELECT中返回一行。例：

SELECTDISTINCT性别FROM学生信息表SQL查询命令FROM子句用于指定SELECT查询的数据源，后面可以跟一个或多个表名，查询名或链接表由INNERJOIN、LEFTJOIN或RIGHTJOIN得到的复合结果）。SELECT*FROM学生信息表SELECT*FROM查询１SELECT学生编号,姓名,班级名称FROM学生信息表

INNERJOIN班级表

ON[学生信息表].班级编号

=[班级表].班级编号如果选择的列存在同名的列，应使用对象限定符SQL查询命令WHERE子句WHERE子句是一个行选择说明子句，用于指定查询条件，以对数据源中进行行限定。当WHERE后面的行选择为真时才将这些行作为查询的行。在WHERE中可以有多个条件，通过AND和OR逻辑运算符进行连接。如查询男同学的信息：SELECT*FROM学生信息表WHERE性别=‘男’SQL查询命令WHERE子句可以包含各种条件运算符：比较运算符：>（大于）,>=（大于等于）,=（等于）,<=（小于等于）,<（小于）,<>（不等于）,!>（不大于）,!<（不小于）SELECT*FROM学生信息表WHERE出生日期>#1987-01-01#逻辑运算符：AND（逻辑与），OR（逻辑或）使用AND时：只有当所有条件都为True(真)时,表达式为True,其它都为False(假)。SELECT*FROM学生信息表WHERE性别=‘女’AND住校否使用OR时：只有当所有条件都为False(假)时,表达式为Fasle,其它都为True(真)。SELECT*FROM学生信息表WHERE性别=‘男’OR政治面貌=‘团员’SQL查询命令范围运算符（BETWEEN…AND…和NOTBETWEEN…AND…）SELECT*FROM学生信息表WHERE出生日期BETWEEN#1987-1-1#AND#1987-12-31#列表运算符（IN和NOTIN）SELECT*FROM学生信息表WHERE入学成绩IN(530,550,578)SQL查询命令模式匹配（LIKE）常用于模糊查找，LIKE运算符可判断值，是否与指定的字符通配格式相符。LIKE用到的通配字符：＊表示该位置可以匹配零个和多个任意字符。如SELECT*FROM学生信息表WHERE姓名LIKE‘张*’?表示该位置可以匹配1个任意字符。如SELECT*FROM学生信息表WHERE姓名LIKE‘张?’#表示该位置可以匹配1个任意数字字符。如SELECT*FROM学生信息表WHERE姓名LIKE‘张＃’［字符列表］用于一个字符、字符串或范围，要求所匹配的对象为任意一个字符列表中的单一字符。如LIKE［A，B，C］［!字符列表］表示不在字符列表中的任何单一字符。如LIKE［！A，B，C］SQL查询命令GROUPBY子句GROUPBY子句用于指定查询结果分组条件例5.10创建SQL查询2，以班级编号分类统计学生信息表中学生的平均入学成绩。SELECT班级编号，AVG(入学成绩)AS平均入学成绩FROM学生信息表GROUPBY

班级编号操作过程：先以班级编号把返回记录分成多个组，分完组后，用聚合函数AVG对每个组中的入学成绩字段进行计算。SQL查询命令聚合函数：是SQL语言中的一种特殊函数，一般作用在多条记录中。COUNT():用于计算满足某一特定条件的记录数目。SUM():用于计算满足某一特定条件的集合中某列的总和。AVG():用于计算满足某一特定条件的集合中某列的平均值。MAX():用于计算满足某一特定条件的集合中某列的最大值。SQL查询命令HAVING子句HAVING子句用于指定返回的集合必须满足的条件。在使用GROUPBY子句对表或查询中的记录进行分组时，有时会要求对所选的记录进行限制。HAVING子句允许为每一组指定条件，只有符合HAVING子句条件的组才显示在查询结果集中。使用HAVING子句，处于GROUPBY子句之后。如：以班级编号分类统计学生信息表中学生的平均入学成绩大于等于500分的班级编号和平均入学成绩。SELECT班级编号,AVG(入学成绩)AS平均入学成绩FROM学生信息表GROUPBY班级编号HAVING

AVG(入学成绩)>=500SQL查询命令ORDERBY子句使用ORDERBY子句可将查询返回的结果按一列或多列排序。ASC表示升序(默认)，DESC表示降序。如在学生成绩表中查询学生编号和大学语文成绩，查询结果按大学语文降序排序.SELECT学生编号,大学语文FROM学生成绩表ORDERBY大学语文DESC其他SQL命令创建表的SQL命令CREATETABLE<表名>

(<字段名1>

<数据类型>［(长度)］,<字段名2>

<数据类型>［(长度)］,……)其中：表名：只要创建的表的名称。字段名1、字段名２…:指所要创建新表的字段名，字段名和数据类型之间必须有空格，各字段之间用逗号分割。长度:指字段的长度。例5.11创建SQL查询３,创建一个学生表，结构为学生编号CHAR(10),姓名CHAR(4),出生日期DATE,班级编号CHAR(8)CREATETABLE学生表(学生编号CHAR(10),姓名CHAR(4),出生日期DATE,班级编号CHAR(8))其他SQL命令修改表结构的SQL命令修改字段的SQL命令格式ALTERTABLE<表名>ALTER

<字段名1>

<数据类型>［(长度)］[,<字段名2>

<数据类型>［(长度)］,……]其中：表名：只要创建的表的名称。字段名1、字段名２…:指所要修改的字段名该命令用于修改已有的字段，修改字段数据类型，长度，不能修改字段名。ALTERTABLE学生信息表

ALTER姓名TEXT(6)其他SQL命令增加字段的SQL命令格式ALTERTABLE<表名>ADD<字段名1>

<数据类型>［(长度)］[,<字段名2>

<数据类型>［(长度)］,……]其中：表名：只要增加字段所在的表的名称。字段名1、字段名２…:指所要增加的字段名数据类型(长度):指要增加的字段的数据类型和长度。ALTERTABLE学生信息表ADD电话号码TEXT(12)其他SQL命令删除字段的SQL命令格式ALTERTABLE学生信息表DROP<字段名1>

<数据类型>［(长度)］[,<字段名2>

<数据类型>［(长度)］,……]其中：表名：指要删除字段所在的表的名称。字段名1、字段名２…:指所要删除的字段名ALTERTABLE学生信息表DROP电话号码其他SQL命令插入记录的SQL命令INSERTINTO表名(<字段名1>

[,<字段名2>,……])VALUES(值1[,值2…])其中：该命令在指定的表的末尾添加一条新记录。当需要插入表中所有字段的数据时，表名后面的