项目六 数据查询答案

项目六数据查询

项目要求：

在“学籍管理”数据库中的院部信息表、班级信息表、学生信息表、教师信息表、课程信息表、选课信息表中进行简单查询、模糊查询、分组查询、联接查询以及子查询的操作。

项目支撑知识：

?简单查询

查询是对存储在SQL Server2005中的数据的请求，用户可以通过查询来获得所需要的数据。查询可以通过执行SELECT语句实现，也可通过其他图形界面的程序实现，但它们最后都要将每个查询转换成SELECT语句，然后发送到SQL Server服务器执行。

SELECT语句的语法形式如下：

SELECT select_list

[ INTO new_table ] FROM table_source

[WHERE search_condition]

[GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

[COMPUTE 子句] [FOR 子句] [OPTION 子句]

其中：

●SELECT子句：用于指定所选择的要查询的特定表中的列，它可以是星

号（*）、表达式、列表、变量等。

●INTO子句：用于指定所要生成的新表的名称。

●FROM子句：用于指定要查询的表或者视图，最多可以指定16个表或

者视图，用逗号相互隔开。

●WHERE子句：用来限定查询的范围和条件。

●GROUP BY子句：是分组查询子句。

●HAVING子句：指定记录辅助过滤条件，从分组的结果中筛选行，即选取

满足条件的那些组。逻辑上讲，HAVING 子句从中间结果集（由SELECT 语

句中的 FROM、WHERE 或 GROUP BY 子句创建的）对行进行筛选。 HAVING

子句通常与 GROUP BY 子句一起使用，如果不使用 GROUP BY 子句，

HAVING 的行为与 WHERE 子句一样。

●ORDER BY子句：是对检索到数据进行排序；ASC 和 DESC 关键字用于指

定行是按升序还是按降序排序，默认升序。

●UNION 运算符：将两个或更多查询的结果组合为单个结果集，该结果集

包含联合查询中的所有查询的全部行。可以在查询之间使用 UNION 运

算符，以将查询的结果组合成单个结果集。

●COMPUTE 子句：生成合计作为附加的汇总行附加在结果集的最后。当与

BY 一起使用时，COMPUTE 子句在结果集内生成控制中断、明细和分类

汇总。可在同一查询内指定 COMPUTE BY 和 COMPUTE。

●FOR语句：指定数据的返回形式。

●OPTION 子句：指定应在整个查询中使用所指定的查询提示。

?查询结果的显示方式

在SQL Server2005中，使用SELECT语句查询得到的结果有3种显示方式。第一种是网格显示方式，第二种是文本显示方式，第三种是将结果保存成数据文件。默认状态下以网格形式显示查询结果。如果要改变默认显示方式，可以按照如下步骤进行设置：

1.在SSMS环境中，执行“工具”菜单中的“选项”命令，打开“选项”对

话框，如图6.1所示。

图 6.1 “选项”对话框

2.在“选项”对话框中，依次展开“查询结果”、“SQL Server”节点，打

开显示结果的默认方式下拉列表框，从3种显示方式中选择一种设置为默认方式。

另外，还可以通过单击SQL编辑器工具栏上的相应按钮来进行临时显示设置，SQL编辑器工具栏如图6.2所示。

图 6.2 SQL编辑器工具栏

?选择数据库的方式

在SSMS中执行SELECT语句需要选择数据库，选择数据库有两种方式：隐式和显式。

●隐式：使用当前数据库，可通过SSMS工具条上的“选择数据库”下拉

列表指定当前数据库，如图6.3所示。

图 6.3 选择数据库

●显式：使用“USE 数据库名”明确指定要操作的数据库。

?集合函数

为进一步增强检索功能，SQL Server提供了许多集合函数，主要有：

●COUNT([DISTINCT |ALL ]*)统计记录个数。

●COUNT([DISTINCT |ALL ]<列名>)统计一列中值的个数。

●SUM([DISTINCT |ALL ]<列名>)计算一列值的总和。

●A VG([DISTINCT |ALL ]<列名>)计算一列值的平均值。

●MAX([DISTINCT |ALL ]<列名>)求一列值中的最大值。

●MIN([DISTINCT |ALL ]<列名>)求一列值中的最小值。

在SELECT子句中集合函数用来对结果集进行统计计算。DISTINCT是去掉指定列中的重复值，ALL是不取消重复值，默认状态下是ALL。

?联接查询

通过联接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。

联接表示如何使用一个表中的数据来选择另一个表中的行。联接可以分为：内联接、外联接和交叉联接。

●内联接：仅显示两个联接表中的匹配行的联接,包括等值联接和自然联

接。

●外联接：包括在联接表中没有相关的行的联接,可分为以下3种：

1.左向外联接（LEFT OUTER JOIN）：包括第一个命名表（“左”表,出现

在JOIN子句的最左边）中的所有行,不包括右表中的不匹配行。如果左表中的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

2.右向外联接（RIGHT OUTER JOIN）：包括第二个命名表（“右”表,出现

在JOIN子句的最右边)中的所有行,不包括左表中的不匹配行。如果右表中的某行在左表中没有匹配行，则将为左表返回空值。

3.完整外部联接（FULL OUTER JOIN）：包括联接表中的所有行,不论它们

是否匹配。

●交叉联接:在这类联接的结果集内,两个表中每两个可能成对行占一行,

也称作笛卡尔积。

?子查询

子查询可以嵌套在外部SELECT、INSERT、UPDATE或DELETE 语句的WHERE或HAVING子句内，或者其他子查询中。尽管根据可用内存和查询中其他表达式的复杂程度不同，嵌套限制也有所不同，但嵌套到32层也是可能的。任何可以使用表达式的地方都可以使用子查询，只要它返回的是单个值。

项目技能：

?使用T-SQL语言在各个数据表中完成简单查询、模糊查询、分组查询、联接查询以及子查询的操作。

项目实现：

?【例6.1】使用T-SQL语言查询学生信息表中的姓名、性别和院部编号，并按院部编号降序排列。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select 姓名,性别,院部编号from 学生信息表order by 院部编号desc 【提示】用户可以使用ORDER BY 子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认状态下为升序。ORDER BY 子句的语法格式如下：

ORDER BY { 列名 [ASC/DESC]}

当按多列排序时，先按前面的列排序，如果值相同再按后面的列排序。

?【例6.2】使用T-SQL语言查询选课信息表中的学号、分数，该分数为将成绩折成10分制的值。（折成10分制函数为：floor(成绩/10)）

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select 学号,floor(成绩/10) as 分数from 选课信息表

【提示】在默认状态下，查询结果中的列标题可以是表中的列名或无列标题。

可以根据实际需要对列标题进行修改，也可以为没有标题的列添加标题。

修改列标题可以采用以下任何一种方法：

●SELECT 列名（表达式）列别名 FROM 数据源

●SELECT 列名（表达式） AS 列别名 FROM 数据源

●SELECT 列别名=列名（表达式） FROM 数据源

?【例6.3】使用T-SQL语言查询选课信息表中成绩在80-90分之间的学生的学号和课程编号。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select 学号,课程编号 from 选课信息表 where 成绩 between 80 and 90 【提示】范围运算符BETWEEN…AND… 和NOT BETWEEN…AND…可以查找属性值在指定的范围内的记录。其中BETWEEN后是范围的下限，AND后是范围的上限。语法格式如下：

列表达式 [NOT]BETWEEN 起始值 AND 终止值

?【例6.4】使用T-SQL语言查询学生信息表中院部编号分别为YB0003和YB0004的学生的姓名和生日。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select 姓名,生日from 学生信息表where 院部编号in('YB0003','YB0004')

【提示】确定集合运算符IN和NOT IN可以用来查找属性值属于（或不属于）指定集合的记录，运算符的语法格式如下：

列表达式 [NOT]IN （列值1，列值2，列值3）

?【例6.5】使用T-SQL语言查询学生信息表中姓“王”且名字为两个汉字的学生的全部信息。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select * from 学生信息表 where 姓名 like '王_'

【提示】在实际应用中，有时不能给出精确的查询条件。因此，需根据一些不确定的信息来查询。T-SQL提供了字符匹配运算符LIKE进行字符串的匹配运算，实现这类模糊查询。其一般语法格式如下：

[NOT] LIKE ‘<匹配串>’

注意:与LIKE一起使用的匹配串如下:

_(下划线)可匹配任意单个字符。

%(百分号)可匹配任意类型和长度的字符。

[ ]可匹配在指定范围内的任何单个字符。

[^]可匹配不在指定范围内的任何单个字符。

?【例6.6】使用T-SQL语言查询学生信息表中学号的最后一位数字不是2-4的学生的全部信息。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select * from 学生信息表 where 学号 like '_______[^2-4]'

?【例6.7】使用T-SQL语言查询学生信息表中Email字段不为空的学生的全部信息。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select * from 学生信息表 where EMAIL is NOT NULL

【提示】空值通常表示未知,不可用或将在以后添加的数据。若要测试查询中的空值，请在WHERE子句中使用IS NULL或IS NOT NULL。语法格式如下：

列表达式IS [NOT] NULL

?【例6.8】使用T-SQL语言统计选课信息表中选课的人数，而不是人次？

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select count(distinct 学号) from 选课信息表

【提示】count为集合函数，关于集合函数的用法参见本章项目支撑知识。?【例6.9】使用T-SQL语言查询选课信息表中平均成绩大于85分的学生的学号。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select 学号from 选课信息表group by 学号having avg(成绩)>85 【提示】AVG为集合函数，关于集合函数的用法参见本章项目支撑知识。

GROUP BY 子句将查询结果集按某一列或多列值分组，分组列值相等的为一组，并对每一组进行统计。GROUP BY子句的语法格式为：

GROUP BY 列名 [HAVING 筛选条件表达式]

其中：

●“BY 列名”是按列名指定的字段进行分组，将该字段相同的记录组成

一组，对每一组记录进行汇总计算并生成一条记录。

●“HAVING 筛选条件表达式”表示对生成的组筛选后再对满足条件的组

进行统计。

●SELECT 子句的列名必须是 GROUP BY子句已有的列名或是计算列。

?【例6.10】根据学生信息表和选课信息表，使用T-SQL语言查询学生的学号，姓名,课程编号和成绩。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select a.学号,姓名,课程编号,成绩

from 学生信息表 as a inner join 选课信息表 as b on a.学号=b.学号【提示】此联接为等值联接，它在连接的过程中只拼接满足连接条件的记录并存放到结果集中。该联接的语法格式为：

SELECT 列名列表

FROM 表名1 [INNER]JOIN 表名2

ON 表名1.列名=表名2.列名

其中：

●INNER为连接类型选项关键字，指定连接类型为内连接时可以省略。

●ON 表名1.列名=表名2.列名是连接的等值连接条件，通常为“ON 主

键=外键”的形式。

?【例6.11】根据学生信息表和选课信息表，使用T-SQL语言查询学生信息表中所包含的学生的所有信息及其相对应的该学生在选课信息表中的全部信息。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select * from 学生信息表 as a left outer join 选课信息表 as b on

a.学号=

b.学号

【提示】该程序中包含了“左向外联接”，相关用法参见本章项目支撑知识。左向外联接的语法如下：

SELECT 列名列表

FROM 表名1 LEFT[OUTER]JOIN 表名2

ON 表名1.列名=表名2.列名

?【例6.12】根据课程信息表和选课信息表，使用T-SQL语言查询课程信息表中所包含的课程的所有信息及其相对应的该门课程在选课信息表中的全部信息。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select * from 选课信息表 as a right outer join 课程信息表 as b on

a.课程编号=

b.课程编号

【提示】该程序中包含了“右向外联接”，相关用法参见本章项目支撑知识。右向外联接的语法如下：

SELECT 列名列表

FROM 表名1 RIGHT[OUTER]JOIN 表名2

ON 表名1.列名=表名2.列名

?【例6.13】使用T-SQL语言查询课程信息表中名称相同但课程编号不同的课程的相关信息（要求显示课程编号和名称）。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可

完成查询。

select a.课程编号,a.名称 from 课程信息表 as a

inner join 课程信息表 as b on a.名称=b.名称 where a.课程编号<>b.

课程编号

【提示】表可以通过自联接与自身联接。自联接中一个表被引用两次，必须在FROM子句中使用别名区分两个引用。自联接相当于两个内容完全一样的表的联接。

?【例6.14】根据学生信息表和选课信息表，使用T-SQL语言查询选过课的学生的全部信息。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成查询。

select * from 学生信息表 where 学号 in(select 学号 from 选课信息表)

【提示】该程序括号中的查询块称为子查询或内层查询，而包含子查询的查询块称为父查询或外层查询。在带有IN运算符的子查询中，子查询的结果是一个集合。父查询通过IN运算符将父查询中的一个表达式与子查询结果集中的每一个值进行比较，如果表达式的值与子查询结果集合中的任何一个值相等，父查询中的“表达式 IN（子查询）”条件表达式返回TRUE，否则返回FALSE。

?【例6.15】使用T-SQL语言创建学生信息表的副本。

在查询编辑器中输入以下代码，单击工具栏上的“执行”按钮，便可完成副本的创建。

select * into 学生信息表副本 from 学生信息表

【提示】在实际的数据库应用系统中，有时需要将查询结果保存成一个表。

这个功能可以通过SELECT语句中的INTO子句实现。INTO子句的语法格式如下：

INTO 新表名

其中：

●新表名是被创建的新表，查询的结果集中的记录将添加到此表中。

●新表的字段由结果集中的字段列表决定。

●如果表名前加#号则创建的表为临时表。

●用户必须拥有在数据库中创建表的权限。

●INTO子句不能与COMPUTER子句一起使用。

项目拓展（全部使用T-SQL语句)：

?【练习6.1】查询会员信息表Customers中会员的编号（c_ID）、用户名（c_Name）、真实姓名（c_TrueName）和密码（c_Password）。

?【练习6.2】查询会员信息表Customers中会员的编号（c_ID）、用户名（c_Name）、真实姓名（c_TrueName）、年龄和密码（c_Password）并以汉字标题显示列名。

?【练习6.3】查询会员信息表Customers中所有姓“刘”的会员信息，要求显示姓名（c_Name）、性别(c_Gender)、出生年月(c_Birth)和籍贯(c_Address)。

?【练习6.4】对会员信息表Customers按会员类型（c_Type）进行升序排列，类型相同的按年龄进行降序排列。

?【练习6.5】统计Customers表中男、女会员的总人数。

?【练习6.6】根据订单信息表Orders、支付方式表Payments和员工信息表Employees，查询每笔订单的基本信息（订单号、订单日期、总金额）以及付款方式（名称），和处理该订单的员工名称。

?【练习6.7】实现订单表（Orders）和订单详情表（OrderDetails）的左外连接。

效果评测：

?与LIKE一起使用的匹配字符串有哪几种？

?进行联接查询时应注意什么？

?外联接有几种，各自特点是什么，举例说明？

?SQL中常用的集合函数是什么？