《基于C＃的SQL Server应用技术》课件第3章

工作任务1查询数据工作任务2更新数据情境总结

练习题

子任务1简单查询

查询是对存储于SQLServer2008中的数据的请求，通过查询用户可以获得所需要的数据。查询可以通过执行SELECT语句实现，也可通过图形界面实现，但它们最后都要将每个查询转换成SELECT语句，然后发送到SQLServer服务窗口执行。本任务将介绍简单查询的一些基本用法。工作任务1查询数据

1．SELECT语句的基本语法

2．SELECT语句的执行方式

数据的查询可以在SQLServer管理平台中执行，也可以在查询分析器中执行，具体方法如下。

1)在SQLServer管理平台中执行SELECT语句

系统将打开如图3-1所示的窗口。图3-1查询分析器窗口

3．SELECT子句

4．FROM子句

FROM子句指定需要进行数据查询的表。只要SELECT子句中有要查询的列，就必须使用FROM子句。其最常用语法如下：

FROM{<源表>}[,…n]

源表：指明SELECT语句要用到的表、视图等数据源。

5．WHERE子句

WHERE子句是筛选条件，它定义了源表中的行要满足SELECT语句的要求所必须达到的条件。只有符合条件的行才向结果集提供数据，不符合条件的行中的数据不会被使用。

在WHERE子句使用的条件如表3-1所示。表3-1常用的查询条件

【任务1】在“学生信息表”中查询所有学生的学号，姓名。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,姓名

FROM学生信息表

②单击【执行】按钮，得到结果如图3-2所示。图3-2查询所有学生的学号、姓名

【任务2】为结果集内的列指定别名：在“班级管理系统”数据库中将“成绩信息表”中的成绩打四折。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,成绩,成绩*0.4AS平时成绩

FROM成绩信息表

②单击【执行】按钮，得到结果如图3-3所示。图3-3带有别名的查询

【任务3】

用DISTINCT消除结果集中重复的记录：查询“成绩信息表”中的“学号”、“成绩”的信息。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECTDISTINCT学号,成绩

FROM成绩信息表

②单击【执行】按钮，得到结果如图3-4所示。图3-4使用DISTINCT筛选的结果

【任务4】使用TOP显示前面有限条记录：查询“学生信息表”中的前5条记录，字段为学号和姓名。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECTTOP5学号,姓名

FROM学生信息表

②单击【执行】按钮，得到结果如图3-5所示。图3-5使用TOP语句

【任务5】FROM子句应用：在“班级管理系统”数据库中查询所有学生的学号，姓名，成绩相关信息。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT姓名,课程编号,成绩

FROM学生信息表XSJOIN成绩信息表CJONXS.学号=CJ.学号

②单击【执行】按钮，得到结果如图3-6所示。图3-6FROM子句应用

【任务6】比较大小：在“学生信息表”中查询“系别”为“计算机”的记录。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT 学号,姓名,系别

FROM 学生信息表

WHERE (系别=‘计算机系’)

②单击【执行】按钮，得到结果如图3-7所示。图3-7查询“系别”为“计算机”的记录

【任务7】在“成绩信息表”中，查询“成绩”在80～90之间的成绩，要求只显示课程编号和成绩字段。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT课程编号,成绩

FROM成绩信息表

WHERE成绩BETWEEN80AND90

②单击【执行】按钮，得到结果如图3-8所示。图3-8使用BETWEEN…AND

【任务8】在“成绩信息表”中，查询“成绩”不在80～90之间的成绩，要求只显示课程编号和成绩字段。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT 课程编号,成绩

FROM 成绩信息表

WHERE 成绩NOTBETWEEN80AND90

②单击【执行】按钮，得到结果如图3-9所示。图3-9使用NOTBETWEEN…AND

【任务9】确定集合：在“学生信息表”中，查询“系别”为“计算机系”和“会计系”的学生，字段包括“学号”、“姓名”、“系别”。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,姓名,系别

FROM学生信息表

WHERE系别IN(‘计算机系’,‘会计系’)

②单击【执行】按钮，得到结果如图3-10所示。图3-10使用IN筛选的结果

【任务10】

字符匹配：查询学生信息表中姓“张”的学生信息。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT 学号,姓名,系别

FROM 学生信息表

WHERE 姓名LIKE‘张%’

②单击【执行】按钮，得到结果如图3-11所示。图3-11模糊查询

【任务11】涉及空值的查询：

在“班级管理系统”数据库中查询所有电话为空的学生信息。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT*

FROM学生信息表

WHERE电话ISNULL

②单击【执行】按钮，得到结果如图3-12所示。图3-12查询空值

【任务12】

多重条件查询：在“班级管理系统信息表”中，查询机电系的姓张的学生信息。要求输出字段为学号、姓名、系别。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,姓名,系别

FROM学生信息表

WHERE系别=‘机电系’

AND姓名LIKE‘张%’

②单击【执行】按钮，得到结果如图3-13所示。图3-13多重条件查询

【任务13】ORDERBY子句：将“成绩信息表”按成绩排序，输出有关字段。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,成绩

FROM成绩信息表

ORDERBY成绩

②单击【执行】按钮，得到结果如图3-14所示。图3-14使用ORDERBY子句筛选的结果子任务2分类汇总

用户经常需要对结果集进行统计，例如，求和、平均值、最大值、最小值、个数等，这些统计可以通过集合函数、COMPUTE子句、GROUPBY子句来实现。本节将详细介绍这些常用函数、COMPUTE子句和GROUPBY子句的用法。

【任务1】SUM函数应用：计算“成绩信息表”中某个学生的成绩总和。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECTSUM(成绩)AS成绩总和

FROM成绩信息表

WHERE学号=‘2007110101’

②单击【执行】按钮，得到结果如图3-15所示。图3-15使用SUM()函数

【任务2】AVG函数应用：计算“成绩信息表”中某个学生的平均成绩。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECTAVG(成绩)AS平均成绩

FROM成绩信息表

WHERE学号=‘2007110101’

②单击【执行】按钮，得到结果如图3-16所示。图3-16使用AVG()函数

【任务3】COUNT函数应用：统计“学生信息表”中的学生总数。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECTCOUNT(*)AS学生总数

FROM学生信息表

②单击【执行】按钮，得到结果如图3-17所示。图3-17使用COUNT()函数

【任务4】

用GROUPBY分组：查询“学生信息表”中各系学生的个数。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT系别，COUNT(*)AS数量

FROM学生信息表

GROUPBY系别

②单击【执行】按钮，得到结果如图3-18所示。图3-18分组统计筛选结果

【任务5】在GROUPBY中使用HAVING子句：在“成绩信息表”中，找出所有学生的平均成绩大于80的信息。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,AVG(成绩)AS平均成绩

FROM成绩信息表

GROUPBY学号

HAVINGAVG(成绩)>80

②单击【执行】按钮，得到结果如图3-19所示。图3-19分组统计筛选结果

【任务6】COMPUTE子句应用：在“成绩信息表”中，检索“学号”为“2007110101”的记录，并求出平均成绩、最低成绩、最高成绩。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,课程编号，成绩

FROM成绩信息表

WHERE学号=‘2007110101’

COMPUTEAVG(成绩),MAX(成绩),MIN(成绩)

②单击【执行】按钮，得到结果如图3-20所示。图3-20分组统计筛选结果

【任务7】COMPUTEBY子句应用：从“成绩信息表”中检索数据，列出每个学生的成绩以及每个学生的平均成绩、最低成绩、最高成绩。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,课程编号,成绩

FROM成绩信息表

ORDERBY学号

COMPUTEAVG(成绩),MAX(成绩),MIN(成绩)

BY学号

②单击【执行】按钮，得到结果如图3-21所示。图3-21分组统计筛选结果子任务3联接

若一个查询同时涉及多个表，则称之为联接查询。联接查询是关系数据库中最重要的查询，它包括内联接、外联接、交叉联接、自联接、多表联接等。1．内联接

2．外联接

1)左外联接

2)右外联接

3)全联接

3．交叉联接

4．自联接

【任务1】

内联接：查询所有课程信息和选课学生的成绩。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECTCJ.*,KC.*

FROM成绩信息表CJJOIN课程信息表KC

ONCJ.课程编号=KC.课程编号

②单击【执行】按钮，得到结果如图3-22所示。图3-22使用SQL语句创建视图

【任务2】左外联接：查询所有学生的信息和选课学生的情况。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT姓名,性别,课程编号,成绩

FROM学生信息表XSLEFTJOIN成绩信息表CJ

ONXS.学号=CJ.学号

②单击【执行】按钮，得到结果如图3-23所示。图3-23左外联接

【任务3】交叉联接：查找所有学生选课的可能情况；得到的结果集的行数是两个表的行数的乘积。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT姓名,性别,课程编号,课程名称

FROM学生信息表CROSSJOIN课程信息表

ORDERBY课程编号

②单击【执行】按钮，得到结果如图3-24所示。图3-24交叉联接

【任务4】

自联接：查找不同课程成绩相同的学生的学号、课程编号和成绩。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECTCJ1.学号,CJ1.课程编号,CJ1.成绩

FROM成绩信息表CJ1JOIN成绩信息表CJ2

ONCJ1.成绩=CJ2.成绩

WHERECJ1.课程编号!=CJ2.课程编号

ANDCJ1.学号!=CJ2.学号

②单击【执行】按钮，得到结果如图3-25所示。图3-25自身联接查询子任务4实现嵌套查询

所谓子查询，是指包含在某一个SELECT、INSERT、UPDATE或DELETE命令中的SELECT查询。在SELECT、INSERT、UPDATE或DELETE命令中允许是一个表达式的地方均可以使用子查询。当从表中选取数据行的条件依赖于该表本身或其他表的联合信息时，需要使用子查询来实现。子查询也称为内部查询，而包含子查询的语句称为外部查询。

1．子查询基础

2．非相关子查询(不依赖于外部查询的子查询)

ANY或ALL与比较运算符一起使用的语义如表3-2所示。表3-2ANY或ALL与比较运算符一起使用的语义

3．相关子查询

在相关子查询中，子查询的执行依赖于外部查询，多数情况下是在子查询的WHERE子句中引用了外部查询的表。

4．带EXISTS测试的子查询

【任务1】返回单个值：查询“成绩信息表”中，所有成绩低于平均成绩的学生。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,课程编号,成绩

FROM成绩信息表

WHERE成绩<(SELECTAVG(成绩)

AS平均成绩FROM成绩信息表)

②单击【执行】按钮，得到结果如图3-26所示。图3-26返回单个值

【任务2】返回一组值：在“成绩信息表”中，找出某个学生的成绩比另外一个学生的最高成绩还高的学生成绩信息。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,课程编号,成绩

FROM成绩信息表

WHERE学号=‘2007110101’AND成绩>ALL

(SELECT成绩FROM成绩信息表

WHERE学号=‘2007110102’)

②单击【执行】按钮，得到结果如图3-27所示。图3-27返回一组值

【任务3】相关子查询：查询“成绩信息表”中大于该课程平均值的学生成绩信息。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,课程编号,成绩

FROM成绩信息表ASCJ1

WHERE成绩>

(SELECTAVG(成绩)

FROM成绩信息表ASCJ2

WHERECJ1.课程编号=CJ2.课程编号)

②单击【执行】按钮，得到结果如图3-28所示。图3-28相关子查询

【任务4】带EXISTS测试的子查询：利用EXISTS查询所有成绩记载的信息。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,课程编号,成绩

FROM成绩信息表

WHEREEXISTS

(SELECT学号

FROM学生信息表

WHERE成绩信息表.学号=学生信息表.学号AND系别=‘计算机系’)

②单击【执行】按钮，得到结果如图3-29所示。图3-29带EXISTS测试的子查询子任务1增加记录

一个表建立以后，就可以向表中添加数据。在前面的学习情境中实现了通过管理平台向表中添加数据。在本工作任务中将使用INSERT语句插入数据以及用INSERT…SELECT语句添加来自另外一个表中的数据。工作任务2更新数据1．使用INSERT语句插入新记录

1)给插入记录的所有字段添加数据

2)给插入记录的部分字段添加数据

3)给插入的记录使用默认值添加数据

2．使用INSERT…SELECT语句插入新记录

【任务1】给插入记录的所有字段添加数据：在“班级管理系统”数据库的“学生信息表”中添加一条记录，其中，学号：2009430102，姓名：张子瑶，性别：女，出生年月日：1990-6-3，班级：094301，电话：2754635，入学时间：2009-9-1，系别：机电系。添加新记录之后，显示“学生信息表”的全部记录。

操作步骤如下：

①在查询窗口中输入以下命令文本：

INSERTINTO学生信息表

VALUES(‘2009430102’,‘张子瑶’,

‘女’,‘1990-6-3’,‘094301’,‘2754635’,‘2009-9-1’,‘机电系’,‘’)

SELECT*FROM学生信息表

②单击【执行】按钮，得到结果如图3-30所示。图3-30给插入记录的所有字段添加数据

【任务2】

给插入记录的部分字段添加数据：在“学生信息表”中添加一条记录，其中，学号：2008310204,姓名：张凯,性别：男，系别：计算机系。

操作步骤如下：

①在查询窗口中输入以下命令文本：

INSERTINTO学生信息表(学号,姓名,性别,系别)

VALUES(‘2008310204’,‘张凯’,‘男’,‘计算机系’)

SELECT*FROM学生信息表

②单击【执行】按钮，得到结果如图3-31所示。图3-31给插入记录的部分字段添加数据

【任务3】用INSERT…SELECT语句插入新记录：利用已有表“学生信息表”创建一个表，表名为A，包括字段：学号、姓名、性别、系别。再将“学生信息表”中“系别”为计算机系的学生信息插入A表中，并显示A表内容。

操作步骤如下：

①在查询窗口中输入以下命令文本：

SELECT学号,姓名,性别,系别

INTOA

FROM学生信息表

WHERE系别=‘计算机系’

SELECT*

FROMA

②单击【执行】按钮，得到结果如图3-32所示。图3-32利用已有表创建一个表子任务2修改记录

在查询分析器窗口中掌握用命令方式修改数据，可以使用UPDATE语句对表中的数据进行修改，也可以使用FROM子句对UPDATE语句进行扩展，以便从一个或多个已经存在的表中获取修改时需要用到的数据。1．使用UPDATE语句修改记录

2．使用FROM子句扩展UPDATE语句

【任务1】

修改记录：将“成绩信息表”中的“课程编号”为“303”的学生成绩在原成绩的基础上增加5分。

操作步骤如下：

①在查询窗口中输入以下命令文本：

UPDATE成绩信息表

SET成绩=成绩+5

WHERE课程编号=‘303’

SELECT*FROM成绩信息表

WHERE课程编号=‘303’

②单击【执行】按钮，得到结果如图3-33所示。图3-33插入新记录

【任务2】用FROM子句扩展UPDATE语句：将“成绩信息表”中是“计算机系”的学生的成绩增加5分。

操作步骤如下：

①在查询窗口中输入以下命令文本：

UPDATE成绩信息表

SET成绩=成绩+5

FROM成绩信息表ASa,学生信息表ASb

WHEREa.学号=b.学号AND系别=‘计算机系’

②单击【执行】按钮，得到结果如图3-34所示。图3-34扩展UPDATE语句子任务3删除记录

1．使用DELETE语句删除表中的指定记录

2．使用TRUNCATETABLE语句删除表中所有记录

【任务1】

用DELETE语句删除表中的指定记录：从“学生信息表”中删除会计系的学生，并显示结果。

操作步骤如下：

①在查询窗口中输入以下命令文本：

USE班级管理系统

DELETE学生信息表

WHERE系别=‘会计’

②单击【执行】按钮，得到结果如图3-35所示。图3-35用DELETE语句删除表中的指定记录

【任务2】

使用TRUNCATE语句删除数据