第08章+数据查询课件.ppt

《第08章+数据查询课件.ppt》由会员分享，可在线阅读，更多相关《第08章+数据查询课件.ppt（54页珍藏版）》请在三一办公上搜索。

1、第八章 数据查询,计算机基础教学部,本章内容,8.1 查询分析器简介8.2 SELECT语句简介8.3 SELECT基本查询8.4 SELECT嵌套查询8.5 连接查询8.6 在INSERT、UPDATE和DELETE语句中使用子查询,8.1 查询分析器简介,通过开始菜单启动,通过企业管理器启动,启动查询分析器,8.1 查询分析器简介,查询分析器的工作界面,对象浏览区域,选择数据库,输入SQL区域,显示查询结果,8.1 查询分析器简介,查询分析器的常用操作执行SQL语句在【输入SQL区域】中输入SQL语句或打开一个SQL文件，单击工具栏执行按钮 或按F5。查看和修改表中的数据,8.1 查询分析

2、器简介,控制查询结果的显示方式,以文本方式显示,以表格显示结果,将结果保存为文件,菜单,工具栏,8.1 查询分析器简介,新建查询窗口 单击工具栏【新建查询】按钮。选择菜单【文件】|【新建】保存在查询窗口中输入的SQL语句将光标置于输入SQL区域 单击工具栏保存按钮 选择菜单【文件】|【保存】保存查询结果将光标置于查询结果显示区域 单击工具栏保存按钮 选择菜单【文件】|【保存】,8.1 查询分析器简介,对象搜索单击工具栏对象搜索按钮 或选择菜单【工具】|【对象搜索】|【新建】。,8.2 SELECT语句简介,数据查询是数据库最重要也是最主要的功能，在数据库中，数据查询是通过SELECT语句来完成

3、的。SELECT查询语句只有一条语句，却能实现各种需求的查询。功能非常强大，依赖于其子句。SELECT语句的框架：,8.2 SELECT语句简介,SELECT ALL|DISTINCT;TOP n PERCENT;.AS,AS;FROM,;INNER/LEFT/RIGHT/FULL JOIN;ON;INTO;WHERE;GROUP BY,HAVING;ORDER BY ASC/DESC,),集合函数(),BY,提示：SELECT和FROM是必备的，其他子句根据实际情况设定。,8.3 SELECT基本查询,无条件简单查询SELECT ALL|DISTINCT TOP n PERCENT sele

4、ct_list FROM table_name查询结果：从table_name中返回数据。各选项含义：ALL：表示输出所有记录，包括重复记录。DISTINCT：输出无重复结果的记录。TOP n：返回查询结果的前n行数据。TOP n PERCENT：返回查询结果前n%行数据。select_list：所要查询的选项（列）的集合，多个选项之间用逗号分开。table_name：要查询的表名。,8.3 SELECT基本查询,例8-1：查询课程表中的所有数据。select 课程号,课程名,学时 from 课程表提示：如果查询某表的所有列时，可用*代替各列名。select*from 课程表,例8-2：查询课

5、程表中前两行数据。select top 2 课程号,课程名,学时 from 课程表,8.3 SELECT基本查询,例8-3：查询课程表中前一半数据。select top 50 percent*from 课程表例8-4：查询课程表中学时列，不显示重复记录。select distinct 学时 from 课程表,和原数据比较：Select 学时 from 课程表,8.3 SELECT基本查询,提问：下列SELECT语句含义 select*from 学生 select top 3 学号,姓名,性别 from 学生 select top 20 percent 学号,姓名,性别from 学生 selec

6、t distinct 性别 from 学生,8.3 SELECT基本查询,使用别名在输出结果中，如果不希望使用字段名作为各列的标题，可以根据需求设置列标题。格式：column_name1 AS column_title1,例8-5：查询课程表所有数据，字段学时显示为课时。select 课程号,课程名,学时 as 课时 from 课程表,提示：AS可以省略。select 课程号,课程名,学时 课时 from 课程表,8.3 SELECT基本查询,使用集合函数进行统计可以通过集合函数对表数据进行统计计算，并返回计算结果。比如查询表中记录的个数、查询学生的平均成绩等。AVG()：求一列数据的平均值。

7、SUM()：求一列数据的和。COUNT(*)：统计查询的行数。MIN()：求列中的最小值。MAX()：求列中的最大值。,8.3 SELECT基本查询,例8-6：查询学生表中的记录数。select count(*)from 学生表例8-7：查询选课表中最高分和最低分。select max(成绩),min(成绩)from 选课表例8-8：查询选课表中的平均成绩。select avg(成绩)from 选课表提示：在使用集合函数时，常需重新命令列标题。,改为：select max(成绩)as 最高分,min(成绩)as 最低分 from 选课表,8.3 SELECT基本查询,带条件查询经常会查询符合某

8、条件的记录，比如查询学生表中女同学的信息，查询成绩在90分以上的记录等。通过WHERE子句来指定查询条件。格式：WHERE 条件表达式：通过比较操作符和逻辑操作符构成的逻辑判断的式子。比较操作符：=、!=（或）、=、=90；not(成绩=90)课程号=c01 and 成绩=80,8.3 SELECT基本查询,例8-9：查询学生表中女同学的个人信息。select 学号,姓名,性别 from 学生表 where 性别=女例8-10：查询课程表中c01和c03这两门课程情况。select*from 课程表 where 课程号=c01 or 课程号=c03提示：书写表达式时需要根据数据类型来加定界符。

9、表达多个条件时使用AND和OR，注意其逻辑含义。,8.3 SELECT基本查询,查询结果处理排序输出ORDER BY语法格式：ORDER BY order_by_expression1ASC|DESC,order_by_expression2ASC|DESC,ASC升序（默认）；DESC降序order_by_expression1通常为字段，字段与字段之间用逗号分隔。例如：order by 性别（以性别来排序）order by 性别,姓名（先以性别升序来排，性别相同的再以姓名升序来排）,8.3 SELECT基本查询,例8-11：将选课表的成绩按从低到高的顺序显示。select*from 选课表

10、 order by 成绩 ASC例8-12：将选课表的成绩按从高到低的顺序显示。select*from 选课表 order by 成绩 DESC,提示：字母排序AZ为升序；ZA为降序。汉字排序取汉字的汉语拼音，再以字母序来排。,课堂练习,在“学生表”中实现以下查询：所有学生的学号、姓名、性别信息，并按学号排序。所有女生的信息。所有女生的人数。在“选课表”中实现以下查询：查找学号为s2008003的课程号、成绩。查找成绩在80分以上的学生学号、成绩。查找课程号为c01的最高分、最低分、平均分。计算成绩在90分以上的记录数。表的基本操作在课程表中插入一条记录：课程号c06,课程名为photosho

11、p,学时为40修改课程号为c06记录的学时数为60删除课程号为c06记录,8.3 SELECT基本查询,重定向输入INTO把查询结果存放到一个新表中。语法：INTO new_table例8-12：查询出学生表中女同学的个人信息，并将结果存入女同学表中。select*into 女同学表 from 学生表 where 性别=女Select*from 女同学表,8.3 SELECT基本查询,输出合并UNION把来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。语法：UNION ALLALL结果全部合并，若没有ALL，则重复的记录将被自动去掉。,8.3 SELECT基本查询,例8-13：将男

12、同学表和女同学表的记录合并。select 学号,姓名,性别 from 女同学表unionselect 学号,姓名,性别 from 男同学表提示：两个SELECT语句必须输出同样的列数。两个表各相应列的数据类型必须相同。只有最后一个SELECT语句中可以用ORDER BY 子句。,8.3 SELECT基本查询,分组统计GROUP BY通过GROUP BY子句可以将查询结果按指定列进行分组，该列值相等的记录为一组。语法：GROUP BY group_by_expression1,group_by_expression2,例8-14：统计学生表中男女生人数。select 性别,count(*)as

13、人数 from 学生表 group by 性别提示：group_by_expression1通常为字段名（列名）。通常和集合函数一起使用。课堂练习：统计选课表中每位学生的平均成绩。,8.3 SELECT基本查询,筛选HAVING若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。语法：HAVING searching_condition 例8-15：查询选课表中平均成绩在90分以上的学生学号和平均成绩。select 学号,avg(成绩)平均成绩 from 选课表group by 学号having avg(成绩)=90,8.3 SELECT基本查询,提示：HAVING子句通常需要使用

14、集合函数来限定查询结果。HAVING子句和WHERE子句都是条件限定子句，但其作用对象不同。,8.3 SELECT基本查询,在多表连接查询中使用分组统计举例：统计各门课程的平均成绩，并显示课程名。select 课程表.课程号,max(课程名)课程名,avg(成绩)平均成绩from 选课表 inner join 课程表 on 选课表.课程号=课程表.课程号group by 课程表.课程号,思考：统计平均分在90分以上的课程号、课程名和平均成绩。查询学生的平均成绩，并显示学生姓名。,课堂练习,完成对如下三个表的查询：职工(*职工号 int,姓名 char(8),性别 char(2),出生日期 da

15、tetime,党员否 bit,参加工作 datetime,部门号 int)部门(*部门号 int,部门名 char(10)工资(*职工号 int,*工资日期 datetime,工资 decimal(6,1)计算各部门员工数。显示所有职工的平均工资。显示所有职工的职工号、姓名和平均工资。,8.3 SELECT基本查询,使用COMPUTE和COMPUTE BY子句汇总使用COMPUTE子句可以在查询的结果集中生成汇总行的同时，还生成明细行。语法：COMPUTE row_aggregate(column_name),row_aggregate(colornn_name).BY column_name

16、,column_name.例8-16：查询选课表中学号为s2008001的平均成绩及各科成绩。select 学号,课程号,成绩from 选课表where 学号=s2008001compute avg(成绩),8.3 SELECT基本查询,COMPUTE子句生成集合函数结果附加在结果集的最后，当与BY一起使用时，COMPUTE子句在结果集内对指定列进行分组汇总。例8-17：查询选课表中学生的各科成绩及平均成绩。select 学号,课程号,成绩from 选课表 order by 学号compute avg(成绩)by 学号,提示：COMPUTE子句中指定的列必须是SELECT子句中已有的。COMP

17、UTE BY必须与ORDER BY子句一起使用，且两个子句指定的列及顺序相同。不能与SELECT INTO子句一起使用。,8.4 SELECT嵌套查询,有时一个SELECT语句无法完成查询任务，而需要一个子SELECT的结果作为查询的条件，即需要在一个SELECT语句的WHERE子句中出现另一个SELECT语句，这种查询称为嵌套查询。通常把仅嵌入一层子查询的SELECT语句称为单层嵌套查询，把嵌入子查询多于一层的查询称为多层嵌套查询。SQL Server允许多层嵌套查询，嵌套查询一般的查询方法是由里向外进行处理。,8.4 SELECT嵌套查询,单值嵌套查询子查询的返回结果是一个值的嵌套查询。例

18、8-18：查询选课表中英语考试成绩。select 学号,成绩,课程号 from 选课表 where 课程号=(select 课程号 from 课程表 where 课程名=英语),8.4 SELECT嵌套查询,多值嵌套查询子查询的返回结果是一列值的嵌套查询。如果子查询的返回值不止一个，则必须指明在WHERE子句中怎样使用这些值。通常会使用条件运算符ANY（或SOME）、ALL和IN。ANY运算符：满足子查询中任意一个值的记录。ALL运算符：满足子查询中所有值的记录。IN运算符：字段内容是结果集合或者子查询中的内容。,8.4 SELECT嵌套查询,例8-19：查询选课表中成绩高于学号为s20080

19、05的最低分的学号、课程号及考试成绩。select*from 选课表 where 成绩any(select 成绩 from 选课表 where 学号=s2008005)例8-20：查询选课表中成绩高于学号为s2008005的最高分的学号、课程号及考试成绩。select*from 选课表 where 成绩ALL(select 成绩 from 选课表 where 学号=s2008005)附学号为s2008005 的考试成绩：,8.4 SELECT嵌套查询,例8-21：查询选c02课程的学生信息。select*from 学生表 where 学号 in(select 学号 from 选课表 where

20、 课程号=c02)提示：IN是属于的意思，等价于“=ANY”,8.5 连接查询,连接概述通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其他类型数据库管理系统的一个标志。通过连接运算符可以实现多个表查询。建立连接的方法where 子句例8-22：查询学生考试成绩，要求给出学号、课程名和成绩。select 学号,课程名,成绩 from 选课表,课程表 where 选课表.课程号=课程表.课程号,8.5 连接查询,from子句 语法：FROM 表1 连接类型 JOIN 表2 ON 连接条件如上题（例8-22）用from子句来实现查询：select 学号,课程名,成绩

21、from 选课表 inner join 课程表 on 选课表.课程号=课程表.课程号 课堂练习：查询学生考试成绩，要求显示课程号、姓名、成绩（分别用where子句和from子句实现）。,8.5 连接查询,使用表别名由于查询来源于多个表，如果多个表有相同列，需要在列名前附加表名，形如.。可以是原表名，也可以用别名。对比以下两段SQL语句，学会使用表别名,select 学号,课程名,成绩,选课表.课程号 from 选课表,课程表 where 选课表.课程号=课程表.课程号,select 学号,课程名,成绩,a.课程号 from 选课表 a,课程表 b where a.课程号=b.课程号,课堂练习：

22、查询学生考试成绩，要求显示学生学号、姓名、成绩。,8.5 连接查询,连接类型内连接（INNER JOIN）：使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。外连接（OUTER JOIN）：不只列出与连接条件相匹配的数据行，而是列出左表（左外连接）、右表（右外连接）或两个表（全外连接）中所有符合条件的数据行。交叉连接（CROSS JOIN）：没有where子句，返回连接表中所有数据行的笛卡尔乘积。连接条件由被连接表中的列和比较运算符、逻辑运算符等构成。如：选课表.课程号=课程表.课程号,8.5 连接查询,内连接列出与连接条件匹配的数据行，分为种：等值连接：

23、在连接条件中使用等号（=）比较被连接的列值，按对应列的共同值将一个表中的记录与另一个表中的记录相连接，包括重复列。例8-23：查询选课表和课程表的等值连接。select*from 选课表 inner join 课程表 on 选课表.课程号=课程表.课程号,8.5 连接查询,不等值连接：在连接条件中使用除等于（=）运算符以外的其他运算符比较被连接的列值。运算符：、=、!。例8-24：查询课程表中与英语课时不同的课程。select a.课程号,a.课程名 from 课程表 a inner join 课程表 b on a.学时b.学时 where b.课程名=英语,课程表数据：,查询结果：,提示：连

24、接对象是同一个表时，必须使用表别名。,8.5 连接查询,自然连接：连接条件中使用等于（=）运算符比较被连接列的列值，它使用选择列表方式来指出查询结果集合中所包括的列，并删除重复列。例8-25：查询选课表和课程表的自然连接。select 课程表.课程名,选课表.*from 选课表 inner join 课程表 on 选课表.课程号=课程表.课程号,8.5 连接查询,外连接左外连接：引用左表的所有行。例8-26：选课表左外连接学生表。select a.*,b.课程号,b.成绩from 学生表 a left outer join 选课表 b on a.学号=b.学号,说明：左外连接限制选课表中的行，

25、而不限制学生表中的行，即在左连接中，学生表中不满足条件的行也显示出来。,8.5 连接查询,右外连接：引用右表的所有行。例8-27：选课表右外连接课程表。select a.*,b.课程名from 选课表 a right outer join 课程表 b on a.课程号=b.课程号全外连接：返回两个表的所有行（FULL OUTER JOIN）。,8.5 连接查询,学生表,选课表,select 学生表.学号,姓名,课程号,成绩from 学生表 inner join 选课表 on学生表.学号=选课表.学号,select 学生表.学号,姓名,课程号,成绩from 学生表 left join 选课表 o

26、n学生表.学号=选课表.学号,内连接,左外连接,交叉连接不带WHERE子句。返回被连接的两个表所有数据行的笛卡尔积。结果数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例8-28：学生表和课程表的交叉连接。select 学号,姓名,课程名from 学生表 cross join 课程表 问题：学生表中有名学生，课程表中有门课程，交叉连接结果有多少条记录？,8.5 连接查询,课堂练习,完成对如下三个表的查询：职工(*职工号 int,姓名 char(8),性别 char(2),出生日期 datetime,党员否 bit,参加工作 datetime,部门号 int)部

27、门(*部门号 int,部门名 char(10)工资(*职工号 int,*工资日期 datetime,工资 decimal(6,1)显示所有职工的职工号、姓名、部门名，并按部门名升序排列。显示在2008年7月1日以前参加工作的职工号、姓名、性别、出生日期、部门名。显示部门号为1的职工号、姓名、工资日期、工资。,8.5 连接查询,将子查询作为来源表或输出列例8-29：查询男同学的考试成绩，并显示学生姓名。select a.学号,b.姓名,a.课程号,a.成绩 from 选课表 a inner join(select 学号,姓名 from 学生表 where 性别=男)b on a.学号=b.学号,

28、8.6 在INSERT、UPDATE和DELETE语句中使用子查询,在INSERT语句中使用子查询新增的数据可以来源于子查询。格式：INSERT SELECT 语句例8-30：将选课表中90分以上的记录添加到高分表。（高分表的数据模式与选课表相同）insert 高分表select 学号,课程号,成绩 from 选课表 where 成绩=90提示：新增表的列数和数据类型一定要和子查询返回结果一致。,8.6 在INSERT、UPDATE和DELETE语句中使用子查询,在UPDATE语句中使用子查询例8-31：如果学生平均成绩在90分以上，则将学生表考核列的值改为优。update 学生表set 考核=优from 学生表,(select 学号 from 选课表 group by 学号 having avg(成绩)=90)awhere 学生表.学号=a.学号,8.6 在INSERT、UPDATE和DELETE语句中使用子查询,在DELETE语句中使用子查询删除条件来自一个子查询。例8-32：将男同学的记录从高分表中删除。delete from 高分表 where 学号 in(select 学号 from 学生表 where 性别=男),小结与提问,数据查询使用查询分析器SELECT语句简单查询嵌套查询连接查询在新增、修改和删除语句中使用子查询,