第08章+数据查询课件

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

以文本方式显示

以表格显示结果

将结果保存为文件菜单工具栏8.1查询分析器简介新建查询窗口单击工具栏【新建查询】按钮。

选择菜单【文件】|【新建】保存在查询窗口中输入的SQL语句将光标置于输入SQL区域单击工具栏保存按钮

选择菜单【文件】|【保存】保存查询结果将光标置于查询结果显示区域单击工具栏保存按钮

选择菜单【文件】|【保存】8.1查询分析器简介对象搜索单击工具栏对象搜索按钮或选择菜单【工具】|【对象搜索】|【新建】。8.2SELECT语句简介数据查询是数据库最重要也是最主要的功能，在数据库中，数据查询是通过SELECT语句来完成的。SELECT查询语句只有一条语句，却能实现各种需求的查询。功能非常强大，依赖于其子句。SELECT语句的框架：8.2SELECT语句简介SELECT[ALL|DISTINCT];[TOPn[PERCENT];[<表名|表别名>.]<列表达式>[AS<栏名>][,[<表名|表别名.>]<列表达式>[AS<栏名>]…];FROM[<数据库名!>]<表名>[,[<数据库名!>]<表名>…];[INNER/LEFT/RIGHT/FULLJOIN[<数据库名!>]<表名>;[ON<连接条件>…]];[INTO

<新表名>];[WHERE<条件>;[GROUPBY<列名1>[,<列名2>…][HAVING<筛选条件>];[ORDERBY<列名1>[ASC/DESC][,<列名2[ASC/DESC]…]][COMPUTE

集合函数(<列名1>)[,[集合函数(<列名2>)],…]

BY

<列名１>[,[<列名2>],…]提示：SELECT和FROM是必备的，其他子句根据实际情况设定。8.3SELECT基本查询无条件简单查询SELECT

[ALL|DISTINCT][TOPn[PERCENT]]

select_list

FROM

table_name查询结果：从table_name中返回数据。各选项含义：ALL：表示输出所有记录，包括重复记录。DISTINCT：输出无重复结果的记录。TOPn：返回查询结果的前n行数据。TOPnPERCENT：返回查询结果前n%行数据。select_list：所要查询的选项（列）的集合，多个选项之间用逗号分开。table_name：要查询的表名。8.3SELECT基本查询例8-1：查询课程表中的所有数据。

select课程号,课程名,学时from课程表

提示：如果查询某表的所有列时，可用*代替各列名。

select*from课程表例8-2：查询课程表中前两行数据。

selecttop2课程号,课程名,学时from课程表8.3SELECT基本查询例8-3：查询课程表中前一半数据。

selecttop50percent*from课程表例8-4：查询课程表中学时列，不显示重复记录。

selectdistinct学时

from课程表和原数据比较：Select学时from课程表8.3SELECT基本查询提问：下列SELECT语句含义

select*from学生

selecttop3学号,姓名,性别from学生

selecttop20percent学号,姓名,性别

from学生

selectdistinct性别from学生8.3SELECT基本查询使用别名在输出结果中，如果不希望使用字段名作为各列的标题，可以根据需求设置列标题。格式：column_name1[[AS]column_title1][,…]例8-5：查询课程表所有数据，字段学时显示为课时。

select课程号,课程名,学时as课时

from课程表提示：AS可以省略。

select课程号,课程名,学时

课时from课程表8.3SELECT基本查询使用集合函数进行统计可以通过集合函数对表数据进行统计计算，并返回计算结果。比如查询表中记录的个数、查询学生的平均成绩等。AVG(<字段名>)：求一列数据的平均值。SUM(<字段名>)：求一列数据的和。COUNT(*)：统计查询的行数。MIN(<字段名>)：求列中的最小值。MAX(<字段名>)：求列中的最大值。8.3SELECT基本查询例8-6：查询学生表中的记录数。

selectcount(*)from学生表例8-7：查询选课表中最高分和最低分。

selectmax(成绩),min(成绩)

from选课表例8-8：查询选课表中的平均成绩。

selectavg(成绩)from选课表提示：在使用集合函数时，常需重新命令列标题。改为：selectmax(成绩)as

最高分, min(成绩)as

最低分

from选课表8.3SELECT基本查询带条件查询经常会查询符合某条件的记录，比如查询学生表中女同学的信息，查询成绩在90分以上的记录等。通过WHERE子句来指定查询条件。格式：WHERE<条件表达式>条件表达式：通过比较操作符和逻辑操作符构成的逻辑判断的式子。比较操作符：=、!=（或<>）、>、>=、<、<=逻辑操作符：AND、OR、NOT例如：性别='女'；成绩>=90；not(成绩>=90)

课程号='c01'and成绩>=808.3SELECT基本查询例8-9：查询学生表中女同学的个人信息。

select学号,姓名,性别

from学生表

where性别='女'例8-10：查询课程表中c01和c03这两门课程情况。

select*from课程表

where课程号='c01'or

课程号='c03'提示：书写表达式时需要根据数据类型来加定界符。表达多个条件时使用AND和OR，注意其逻辑含义。8.3SELECT基本查询查询结果处理排序输出ORDERBY语法格式：ORDERBYorder_by_expression1[ASC|DESC][,order_by_expression2[ASC|DESC]][,…]]ASC——升序（默认）；DESC——降序order_by_expression1——通常为字段，字段与字段之间用逗号分隔。例如：orderby性别（以性别来排序）

orderby性别,姓名（先以性别升序来排，性别相同的再以姓名升序来排）8.3SELECT基本查询例8-11：将选课表的成绩按从低到高的顺序显示。select*from选课表orderby成绩ASC例8-12：将选课表的成绩按从高到低的顺序显示。select*from选课表orderby成绩DESC提示：字母排序——A~Z为升序；Z~A为降序。汉字排序——取汉字的汉语拼音，再以字母序来排。课堂练习在“学生表”中实现以下查询：所有学生的学号、姓名、性别信息，并按学号排序。所有女生的信息。所有女生的人数。在“选课表”中实现以下查询：查找学号为s2008003的课程号、成绩。查找成绩在80分以上的学生学号、成绩。查找课程号为c01的最高分、最低分、平均分。计算成绩在90分以上的记录数。表的基本操作在课程表中插入一条记录：课程号c06,课程名为photoshop,学时为40修改课程号为c06记录的学时数为60删除课程号为c06记录8.3SELECT基本查询重定向输入INTO

把查询结果存放到一个新表中。 语法：INTO

new_table例8-12：查询出学生表中女同学的个人信息，并将结果存入女同学表中。select*into

女同学表

from学生表where性别='女'Select*from女同学表8.3SELECT基本查询输出合并UNION把来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。 语法：[UNION[ALL]<SELECT语句>]ALL——结果全部合并，若没有ALL，则重复的记录将被自动去掉。8.3SELECT基本查询例8-13：将男同学表和女同学表的记录合并。

select学号,姓名,性别from女同学表

union select学号,姓名,性别from男同学表提示：两个SELECT语句必须输出同样的列数。两个表各相应列的数据类型必须相同。只有最后一个SELECT语句中可以用ORDERBY子句。8.3SELECT基本查询分组统计GROUPBY通过GROUPBY子句可以将查询结果按指定列进行分组，该列值相等的记录为一组。语法：GROUPBYgroup_by_expression1 [,group_by_expression2][,…]

例8-14：统计学生表中男女生人数。

select性别,count(*)as人数

from学生表groupby

性别

提示：group_by_expression1通常为字段名（列名）。通常和集合函数一起使用。 课堂练习：统计选课表中每位学生的平均成绩。8.3SELECT基本查询筛选HAVING若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。语法：HAVING

searching_condition

例8-15：查询选课表中平均成绩在90分以上的学生学号和平均成绩。select学号,avg(成绩)平均成绩from选课表groupby学号having

avg(成绩)>=908.3SELECT基本查询提示：HAVING子句通常需要使用集合函数来限定查询结果。HAVING子句和WHERE子句都是条件限定子句，但其作用对象不同。WHERE子句HAVING子句作用对象表字段计算字段与分组的关系无关只能用于分组查询中，必须在GROUPBY之后。8.3SELECT基本查询在多表连接查询中使用分组统计举例：统计各门课程的平均成绩，并显示课程名。

select课程表.课程号,max(课程名)课程名,

avg(成绩)平均成绩

from选课表innerjoin课程表

on选课表.课程号=课程表.课程号

groupby课程表.课程号思考：统计平均分在90分以上的课程号、课程名和平均成绩。查询学生的平均成绩，并显示学生姓名。课堂练习完成对如下三个表的查询：职工(*职工号int,姓名char(8),性别char(2),出生日期datetime,党员否bit,参加工作datetime,部门号int)部门(*部门号int,部门名char(10))工资(*职工号int,*工资日期datetime,工资decimal(6,1))计算各部门员工数。显示所有职工的平均工资。显示所有职工的职工号、姓名和平均工资。8.3SELECT基本查询使用COMPUTE和COMPUTEBY子句汇总使用COMPUTE子句可以在查询的结果集中生成汇总行的同时，还生成明细行。

语法：COMPUTE

row_aggregate(column_name)

[,row_aggregate(colornn_name)...] [BY

column_name[,column_name...]]例8-16：查询选课表中学号为s2008001的平均成绩及各科成绩。select学号,课程号,成绩from选课表where学号='s2008001'compute

avg(成绩)

8.3SELECT基本查询COMPUTE子句生成集合函数结果附加在结果集的最后，当与BY一起使用时，COMPUTE子句在结果集内对指定列进行分组汇总。例8-17：查询选课表中学生的各科成绩及平均成绩。select学号,课程号,成绩

from选课表orderby学号

compute

avg(成绩)

by

学号提示：

COMPUTE子句中指定的列必须是SELECT子句中已有的。

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

例8-18：查询选课表中英语考试成绩。

select学号,成绩,课程号from选课表

where课程号=(select课程号from课程表

where课程名='英语')8.4SELECT嵌套查询多值嵌套查询子查询的返回结果是一列值的嵌套查询。如果子查询的返回值不止一个，则必须指明在WHERE子句中怎样使用这些值。通常会使用条件运算符ANY（或SOME）、ALL和IN。

ANY运算符：满足子查询中任意一个值的记录。

ALL运算符：满足子查询中所有值的记录。

IN运算符：字段内容是结果集合或者子查询中的内容。8.4SELECT嵌套查询例8-19：查询选课表中成绩高于学号为s2008005的最低分的学号、课程号及考试成绩。select*from选课表where成绩>any(select

成绩from选课表

where学号='s2008005')例8-20：查询选课表中成绩高于学号为s2008005的最高分的学号、课程号及考试成绩。select*from选课表where成绩>ALL(select

成绩from选课表

where学号='s2008005')附学号为s2008005的考试成绩：8.4SELECT嵌套查询例8-21：查询选c02课程的学生信息。select*from学生表where学号in(select学号from选课表

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

select学号,课程名,成绩

from选课表,课程表

where选课表.课程号=课程表.课程号8.5连接查询from子句

语法：FROM表1[连接类型]JOIN表2ON连接条件 如上题（例8-22）用from子句来实现查询：

select学号,课程名,成绩

from选课表innerjoin

课程表

on

选课表.课程号=课程表.课程号

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

选课表.课程号

from选课表,课程表

where选课表.课程号=课程表.课程号select学号,课程名,成绩,

a.课程号

from选课表a,课程表bwherea.课程号=b.课程号课堂练习：查询学生考试成绩，要求显示学生学号、姓名、成绩。8.5连接查询连接类型内连接（INNERJOIN）：使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。外连接（OUTERJOIN）：不只列出与连接条件相匹配的数据行，而是列出左表（左外连接）、右表（右外连接）或两个表（全外连接）中所有符合条件的数据行。交叉连接（CROSSJOIN）：没有where子句，返回连接表中所有数据行的笛卡尔乘积。连接条件由被连接表中的列和比较运算符、逻辑运算符等构成。如：选课表.课程号=课程表.课程号8.5连接查询内连接 列出与连接条件匹配的数据行，分为３种：等值连接：在连接条件中使用等号（=）比较被连接的列值，按对应列的共同值将一个表中的记录与另一个表中的记录相连接，包括重复列。例8-23：查询选课表和课程表的等值连接。

select*from选课表innerjoin

课程表

on

选课表.课程号=课程表.课程号8.5连接查询不等值连接：在连接条件中使用除等于（=）运算符以外的其他运算符比较被连接的列值。运算符：>、<、>=、<=、<、!>、!<和<>。例8-24：查询课程表中与英语课时不同的课程。selecta.课程号,a.课程名from课程表ainnerjoin

课程表b

ona.学时<>b.学时whereb.课程名='英语'课程表数据：查询结果：提示：连接对象是同一个表时，必须使用表别名。8.5连接查询自然连接：连接条件中使用等于（=）运算符比较被连接列的列值，它使用选择列表方式来指出查询结果集合中所包括的列，并删除重复列。例8-25：查询选课表和课程表的自然连接。select课程表.课程名,选课表.*from选课表innerjoin

课程表

on

选课表.课程号=课程表.课程号8.5连接查询外连接左外连接：引用左表的所有行。例8-26：选课表左外连接学生表。

selecta.*,b.课程号,b.成绩

from学生表aleftouterjoin

选课表b

ona.学号=b.学号说明：左外连接限制选课表中的行，而不限制学生表中的行，即在左连接中，学生表中不满足条件的行也显示出来。8.5连接查询右外连接：引用右表的所有行。例8-27：选课表右外连接课程表。selecta.*,b.课程名from选课表arightouterjoin

课程表b ona.课程号=b.课程号全外连接：返回两个表的所有行（FULLOUTERJOIN）。8.5连接查询学生表选课表select学生表.学号,姓名,课程号,成绩from学生表innerjoin选课表on学生表.学号=选课表.学号select学生表.学号,姓名,课程号,成绩from学生表

leftjoin选课表on学生表.学号=选课表.学号内连接左外连接交叉连接不带WHERE子句。返回被连接的两个表所有数据行的笛卡尔积。结果数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例8-28：学生表和课程表的交叉连接。select学号,姓名,课程名from学生表

crossjoin课程表问题：学生表中有８名学生，课程表中有５门课程，交叉连接结果有多少条记录？8.5连接查询……课堂练习完成对如下三个表的查询：职工(*职工号int,姓名char(8),性别char(2),出生日期datetime,党员否bit,参加工作datetime,