《网络数据库应用教程》全套PPT电子课件教案-第4章　查询及其应用.ppt

第4章 查询及其应用,4.1 认 识 查 询 4.2 查询的基本操作 4.3 sql查询,4.1 认 识 查 询,4.1.1 查询的目的 查询是按照一定的条件或要求对数据库中的数据进行检索。,4.1.2 查询的种类 1选择查询 选择查询是最常见的查询类型，它可以从当前数据库中的一个或多个表中按照一定的条件检索数据，也可以使用选择查询对记录进行分组，并对记录作总计、计数、平均值以及其他类型的统计计算。,2参数查询 参数查询在执行时显示对话框以提示用户输入相关信息，然后按用户输入内容执行相应的查询操作。 3交叉表查询 交叉表查询把一个表或查询作为数据源，将表或查询中的某个字段的统计值（合计、计算以及平均）作为查询结果，并将它们分组，一组列在数据表的左侧，一组列在数据表的上部。,4操作查询 操作查询是在一个查询中更改多条记录的查询，共有4种类型： （1）删除查询 删除查询是从一个或多个表中删除满足条件的一组记录。 （2）更新查询 更新查询是对一个或多个表中的一组记录做全局的更改。,（3）追加查询 追加查询是从一个或多个表中将满足条件的一组记录追加到一个表的尾部。 （4）生成表查询 生成表查询是使用一个或多个表中的全部或部分数据新建表。,5sql查询 sql 查询是用户使用sql语句创建的查询。除使用符合sql语法规定的sql命令外，还支持联合查询、传递查询、数据定义查询和子查询等特殊查询。 （1）联合查询 联合查询是将多个查询结果合并起来，系统会自动去掉重复的记录。,（2）传递查询 传递查询可直接将查询命令发送到odbc数据库服务器。 （3）数据定义查询 数据定义查询用于创建、删除、更改表或创建数据库中的索引。 （4）子查询 子查询是包含了另一个选择查询或操作查询中的sql select语句的查询。,4.2 查询的基本操作,access 2000为查询对象提供了3种视图：设计视图、sql视图和数据表视图。查询的设计视图主要用于创建和修改查询，sql视图用于查看和修改sql语句，数据表视图以行列方式查看查询结果中的数据。从查询的设计视图切换到另外两种视图的方法是，单击“查询设计”工具栏上“视图”按钮，打开“视图”列表，选择“sql视图”或“数据表视图”。,4.2.1 建立查询的方式 创建查询有两种方式，一种是使用向导创建查询，另一种是使用查询的设计视图创建查询。,1使用向导创建查询 access 2000提供了4种查询向导：简单查询向导、交叉表查询向导、查找重复项查询向导和查找不匹配项查询向导。启动查询向导的方法是：在数据库窗口的“对象”栏中单击“查询”，然后单击数据库窗口上的“新建”按钮或者选择access 主窗口的菜单栏中的“插入”“查询”命令，可打开图4-2所示的“新建查询”对话框。,2使用查询的设计视图创建查询 利用查询的设计视图创建查询的方法如下。 （1）在数据库窗口中打开 “新建查询”对话框。 （2）在“新建查询”对话框中选择“设计视图”选项，然后单击“确定”按钮，出现 “显示表”对话框。,（3）在“显示表”对话框中，选择查询所需要的表名或查询名。 （4）在查询的设计视图窗口中，设置查询的条件。 “字段”设置查询结果中要显示的字段。“排序”指定在查询结果中记录按哪个字段中的数据进行排序，它有两种类型：升序和降序。“准则”中的每一列准则指定了筛选记录的限制条件。,“显示”决定了选定的字段是否显示在查询结果中。 （5）保存查询的定义 。 （6）如果要查看查询结果，单击access主窗口工具栏上的“运行”按钮。,4.2.2 创建选择查询 例4.1 在“课程管理”数据库中，创建一个查询“高数成绩查询”，查询结果中包含student表中的snumber和sname字段，scores表中的grade字段，schedule表中的ctitle字段，并且要求只查询高等数学课程的成绩信息。,4.2.3 创建交叉表查询 在使用交叉表查询向导创建交叉表时，查询所依据的字段只能出自同一个表或查询。如果从多个表中创建交叉表查询，需要先创建一个查询，该查询中包含交叉表查询中所需的所有字段，然后再使用交叉表查询向导。,例4.2 在“课程管理”数据库创建一个交叉表查询，用来查找每个学生各门课程的成绩。 （1）打开查询的设计视图，将student表、schedule表和scores表添加到查询的设计视图窗口中。 （2）在菜单栏上选择“查询”“交叉表查询”命令，查询的设计视图窗口中的查询设计网格部分增添了“总计”行和“交叉表”行。,（3）首先指定一个或多个行标题。然后指定一个列标题。最后指定需要进行统计的字段。 （4）保存。 （5）运行 。,4.2.4 创建参数查询 例4.3 在“课程管理”数据库中的student表上创建一个参数查询，根据输入的学生政治面目，将所有与输入政治面目相同的学生的snumber、sname、ssex字段的信息显示出来。,4.2.5 创建操作查询 1生成表查询 例4.4 从“课程管理”数据库中的schedule表中选取courseid、ctitle和coursehour字段，从textbook表中选取tbtitle字段，创建一个新表“课程简要信息”。,2删除查询 例4.5 从“课程管理”数据库的student表中删除年龄大于35岁的学生。,3追加查询 创建追加查询的步骤如下。 （1）在数据库窗口中，打开查询的设计视图 。 （2）单击工具栏上的“查询类型”按钮，打开“查询类型”列表，选择其中的“追加查询”选项，打开“追加”对话框。在“追加”对话框中的“表名称”下拉列表框中输入表名 。,（3）在查询的设计视图窗口中，将所需的字段拖动到查询设计网格的“字段”行中。 （4）access 2000会自动在“追加到”行中填上与“字段”行中相同的字段名，表示将查询到的记录追加到相同的字段中。 （5）单击access主窗口工具栏上的“执行”按钮，这时出现一个确认对话框，确认追加操作后，access就将满足条件的记录追加到指定的表中。,4更新查询 例4.6 在“课程管理”数据库中，将schedule表中coursehour为64的课程改为48。,在使用操作查询时需要注意如下内容。 操作查询执行之后不能撤消，因此在使用删除查询和更新查询之前，应先备份表中的数据，这样当删除或更新操作出现错误后，可以从备份数据中恢复被删除或更改的数据。 如果被删除数据的表与其他表之间建立的关系，并且在设定表的关系时选定了“级联删除相关记录”，执行删除查询时会同时删除相关表中的记录。,4.3 sql查询,4.3.1 sql的数据定义功能 sql的数据定义功能包括定义基本表和定义索引。,1定义基本表 定义基本表的语句是create，语法格式为： create table （列级完整性约束条件 ，列级完整性约束条件 ，）,2修改基本表 修改基本表的语句是alter，语法格式为： alter tableadd列级完整性约束条件； 其中，add子句用于增加新列和新的完整性约束条件。,例4.8 向student表中增加szip（邮编），其数据类型为文本类型，且字段大小为6。 alter table student add szip char（6）,3删除表 删除一个表是将表结构和表中记录一起删除，如果这个表上建有索引，则将索引一起删除。 删除基本表的语句是drop，语法格式为： drop table 例4.9 删除student表。 drop table student,4建立索引 建立索引是加快查询速度的有效手段。用户可根据需要在基本表上建立一个或多个索引（23个索引）。 格式为：create uniqueclusterindex on （ ，）；,例4.10 为student表建立索引，按snumber升序建立惟一索引。 create unique index snumber on student（snumber） 5删除索引 格式为： drop index on； 例4.11 删除snumber索引。 drop index snumber on student,4.3.2 sql的数据查询功能 select命令的语法如下。 select all|distinct , from ， where group byhaving| order byasc|desc；,1select子句 select子句指出所要查找的列。其中，all表示检索符合条件的所有记录，为缺省值；distinct表示去掉重复记录；目标列表达式指定要查询的列，可以是列名、表达式或函数。select后如果没有指定目标列表达式，而用“*”表示，则表示要指定表中的所有列。,2from子句 from子句指定要查询的数据出自哪张表，可以是一个表，也可以是多个表。 例4.12 查询全体学生的详细信息。 select * from student,若查询涉及两个以上的表，则称为连接查询。 例4.15 查找学生信息以及所选修课程的courseid及grade select * from student, scores where student.snumber= scores.snumber,3where子句 where子句指定查询所需的条件。如果没有where子句，表示选择全部记录。,例4.17 在scores表中查找courseid号为1101且成绩在80分到90分之间的学生的学号。 select snumber, courseid, grade from scores where （courseid=“1101“） and （grade between 80 and 90）,例4.20 查找没有指定教材的课程名称。 select ctitle from schedule where tbookid is null 测试字段的值是否为空值的一般形式是：列名 is not null。 需要注意的是，不能写成：列名=null。,例4.21 求选修高等数学的学生的学号和姓名。 select snumber, sname from student where snumber in ( select snumber from scores where courseid = ( select courseid from schedule where ctitle=高等数学 ),本例中描述的查询是一个嵌套查询，嵌套查询又称为子查询。嵌套查询是指一个select-from-where查询块中可以嵌入另一个select-from-where查询块。sql中允许多层嵌套。嵌套是由内向外处理的，外层查询可以利用内层查询的结果。,4group by子句 group by子句用于对查询结果按指定的列进行分组，并且可以利用函数进行统计，如求平均值、最大值、最小值和计数等。,例4.23 求选修courseid号为1101的学生的平均分数 select avg(grade) from scores where courseid=1101,例4.25 求每门课程的平均成绩。 select courseid,avg(grade) from scores group by courseid 5having子句 having必须跟随group by使用，它用来限定分组必须满足的条件。,例4.26 查找选修课程超过三门课程的学生学号。 select snumber from scores group by snumber having count(*)3,6order by子句 order by用来对查询结果按指定的列进行排序。,例4.27 求选修courseid为1101的学生学号和成绩，且按成绩降序排列。 select snumber, grade from scores where courseid=1101 order by grade desc,例4.28 查找选修courseid为1102课程的学生学号及成绩在80分以上的学生学号。 select * from scores where courseid=1102 union select * from scores where grade80,该查询是一个联合查询。联合查询使用union将多个查询结果合并起来，系统会自动去掉重复的记录。,4.3.3 sql数据更新 sql的数据更新包括对表中记录的添加、删除和修改。 1插入数据 插入数据分为两种格式，一种是插入单个记录，另一种是插入一个子查询的结果。,（1）插入单个记录 语法格式： insert into (, ) values(,)；,例4.29 将一个新学生记录（学号：99080220；姓名：王小兵；性别：男；政治面目：团员；出生日期：1982年2月5日；住址：德外125号；邮政编码：100083）插入到学生表中。 insert into student values（99080220,王小兵,男,团员, #1982-2-5# , 德外125号 , 100083）,（2）插入子查询结果 语法格式： insert into (,) 子查询；,例4.30 求每一门课程的平均成绩，并把结果存入数据库。 首先在数据库中建立一个新表，其中一列存放courseid，另一列存放相