实验三SQL查询与更新实验（SQL3学时）

来源：刀刀网

实验三 SQL查询与更新实验（SQL 3学时） §3.1实验目的与要求

（1）熟悉SQL DML语言，能够完成基本的表操作；（2）掌握SELECT语句进行单表查询的方法; （3）掌握SELECT语句进行多表连接查询的方法; （4）了解嵌套查询的方法；（5）了解外连接查询的方法；（6）了解集合函数的使用方法；（7）了解表更新操作的使用方法。 §3.2 实验系统及软件要求： 1、WindowsXP操作系统； 2、SQL SERVER 2005中文版软件； §3.3 实验内容

§3.3.1简单SELECT语句 1. SELECT的语法格式

SELECT 语句的基本语法格式如下： SELECT <字段列表> [INTO 新表名]

FROM <表名/视图名列表> [WHERE 条件表达式]

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

[ORDER BY列名1[ASC|DESC]，列名2 [ASC|DESC]，．．．，列名n[ASC|DESC]]

[COMPUTE 行聚合函数名（统计表达式）[ ,…n] [BY 分类表达式[,…n]]]

其中各子名说明如下：

（1）字段列表用于指出要查询的字段，也就是查询结果中包含的字段的名称。

（2）INTO子句用于创建一个新表，并将查询结果保存到这个新表中。

（3）FROM子句用于指出所要进行查询的数据来源，即来源于哪些表或视图的名称。（4）WHERE子句用于指出查询数据时要满足的检索条件。

（5）GROUP BY子句用于对查询结果分组。（6）ORDER BY子句用于对查询结果排序。 SELECT语句的功能如下：

从FROM列出的数据源表中，找出满足WHERE检索条件的记录，按SELECT子句的字段列表输出查询结果表，在查询结果表中可进行分组与排序。

说明：在SELECT语句中SELECT子句与FROM子句是不可少的，其余的是可选的。

2. 基本的SELECT语句 SELECT语句的基本形式如下：

SELECT <字段列表>FROM <表名列表> [WHERE 查询条件] （1）选择表中若干列【练习3.1】 USE JXGL GO

SELECT 学号,姓名 FROM xs

（2）查询表中全部列（*）

将表中所有属性都选出来，可以有两种方法。一种方法是在SELECT命令后面列出所有列名。如果列的显示顺序与其在基表中的顺序相同，也可以简单地将<字段列表>简写为“*”。

【练习3.2】 USE JXGL GO

SELECT * FROM kc

3．设置字段别名

T-SQL提供了在SELECT语句中操作别名的方法。用户可以根据实际需要对查询数据的列标题进行修改，或者为没有标题的列加上临时标题。其语法格式为：

①列表达式AS 别名 ②列表达式别名③别名=列表达式

【练习3.3】查询JXGL数据库的kc表，列出表中的所有记录，每个记录名称依次为课程编号，课程名称，课程学分及课程时数。

USE JXGL GO

SELECT 课程号AS 课程编号,课程名课程名称,学分课程学分,学时数AS 课程时数FROM kc

4．查询经过计算的值

SELECT子句的<字段列表>不仅可以是表中的属性列，也可以是表达式，包括字符串常量、函数等。其语法格式为：计算字段名=表达式

【练习3.4】查询所有学生的学号，姓名及年龄。 USE JXGL GO

SELECT 学号,姓名,年龄=DATEDIFF(YY,出生时间,GETDATE()) FROM xs GO

5．返回全部记录（ALL）

要返回所有记录可在SELECT后使用ALL，ALL是默认设置，因此也可以省略。

【练习3.5】查询XS表中所有学生专业。 USE JXGL GO

SELECT 专业 FROM xs

6．过滤重复记录（DISTINCT）

在练习3.5的执行结果集中显示重复行。如果让重复行只显示一次，需在SELECT子句中用DISTINCT指定在结果集中只能显示唯一行。

【练习3.6】查询xs表中的学生所在专业有哪些。（重复专业只显示一次）。

USE JXGL GO

SELECT DISTINCT 专业 FROM xs GO

7．仅返回前面若干条记录其语法格式如下：

SELECT [TOP n ︱TOP n PERCENT] 列名l [，．．．n] FROM 表名

其中：TOP n表示返回最前面的n行，n表示返回的行数；TOP n PERCENT表示返回的最前面的n％行。

【练习3.7】查询xs表中前5条记录 USE JXGL GO

SELECT TOP 5 * FROM xs GO

【练习3.8】查询XS表中前面10％行记录 USE JXGL GO

SELECT TOP 10 PERCENT * FROM xs GO

8. 使用INTO子句

使用INTO子句允许用户定义一个新表，并且把SELECT子句的数据插入到新表中，其语法格式如下：

SELECT <字段列表> INTO 新表名 FROM <表名列表> WHERE 查询条件

使用INTO子句插入数据时，应注意以下几点：（1）新表不能存在，否则会产生错误信息。（2）新表中的列和行是基于查询结果集的。

（3）使用该子句必须在目的数据库中具有CREATE TABLE权限。（4）如果新表名称的开头为“#”，则生成的是临时表。注意：使用INTO子句，通过在WHERE子句中FALSE条件，可以创建一个和源表结构相同的空表。

【练习3.9】创建一个和xs表结构相同的xs_new表。 USE JXGL GO

SELECT * INTO xs_new FROM xs WHERE 6>8

设置“WHERE 6>8”这样一个明显为逻辑否的条件的目的是为了只保留xs表的结构，而不返回任何记录。

【练习3.10】查询所有女生的信息并将结果保存在名为“女生表”的数据表中。

USE JXGL GO

SELECT * INTO 女生表 FROM xs WHERE 性别='女'

【练习3.11】查询所有男生的信息并将结果存入临时表中。 USE JXGL

SELECT * INTO #TEMPDB FROM xs WHERE 性别='男'

要查看临时表的内容可用下面的语句：SELECT * FROM #TEMPDB

9. 使用WHERE子句其语法格式如下： SELECT 列名1[,…列名n] FROM 表名 WHERE 条件表达式

使用WHERE子句可以查询的记录范围。在使用时，WHERE子句必须紧跟在FROM 子句后面。WHERE子句中的条件是—个逻辑表达式。

（1）比较表达式作查询条件

比较表达式是逻辑表达式的一种，使用比较表达式作为查询条件的一般表达形式是：表达式比较运算符表达式

其中，表达式为常量、变量和列表达式的任意有效组合。比较运算符包括：=（等于）、<（小于）、>（大于）、<>（不等于）、!>（不大于）、!<（不小于）、>=（大于等于）、<=（小于等于）、!=（不等于）。

【练习3.12】查询年龄在23岁以下的学生。 USE JXGL GO

SELECT 姓名,性别,年龄=DATEDIFF(YEAR,出生时间,GETDATE()) FROM xs

WHERE DATEDIFF(YEAR,出生时间,GETDATE())<=23 GO

（2）逻辑表达式作查询条件

使用逻辑表达式作为查询条件的一般表达形式是：

表达式1 AND|OR表达式2，或NOT 表达式

【练习3.13】查询年龄为23岁且性别为“女”的学生。 USE JXGL GO

SELECT 姓名,性别,年龄=DATEDIFF(YEAR,出生时间,GETDATE()) FROM xs

WHERE DATEDIFF(YEAR,出生时间,GETDATE())=23 AND 性别='女'

（3）使用（NOT）BETWEEN…AND关键字其语法格式为：

表达式[NOT] BETWEEN 表达式l AND 表达式2

谓词可以用来查找属性值在（或不在）指定范围内的元组，其中BETWEEN后是范围的下限（即低值），AND后是范围的上限（即高值）。使用BETWEEN查询数据范围时同时包括了边界值，而使用NOT BETWEEN进行查询时没有包括边界值。

【练习3.14】查询年龄在20到22之间的女学生的学号，姓名，年龄。

USE JXGL GO

SELECT 姓名,性别,年龄=DATEDIFF(YEAR,出生时间,GETDATE()) FROM xs

WHERE DATEDIFF(YEAR,出生时间,GETDATE())BETWEEN 20 AND 22 AND 性别='女'

（4）使用IN关键字

同BETWEEN关键字一样，IN的引入也是为了更方便地检索数据的范围，灵活使用IN关键字，可以用简洁的语句实现结构复杂的查询。语法格式为：

表达式[NOT] IN （表达式1,表达式2[,…表达式n]）

如果“表达式”的值是谓词IN后面括号中列出的表达式1，表达式2,…表达式n的值之一，则条件为真。

【练习3.15】查询选修了“a003”和“a008”的学生的学号。 USE JXGL GO

SELECT DISTINCT 学号 FROM cj

WHERE 课程号IN ('a003', 'a008') GO

（5）使用LIKE关键字

语法格式：表达式[NOT] LIKE <匹配串>

其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符。SQL Server提供了以下4种通配符供用户灵活实现复杂的查询条件。％（百分号）：表示从0到n个任意字符。_（下划线）：表示单个的任意字符。[ ]（封闭方括号）：表示方括号里列出的任意一个字符。[^]：任意一个没有在方括号里列出的字符。

需要注意的是，以上所有通配符都只有在LIKE子句中才有意义，否则通配符会被当作普通字符处理。

【练习3.16】查询姓“张”学生的学号及姓名 USE JXGL GO

SELECT 学号,姓名 FROM xs

WHERE 姓名LIKE '张%' GO

注意：通配符和字符串必须括在单引号中。要查找通配符本身时，需将它们用方括号括起来。例如：LIKE ' [[]'表示要匹配“[”。

（6）涉及空值的查询

对于空值（NULL）要用IS 进行连接，不能用“=”代替。【练习3.17】查询选修了课程却没有成绩的学生的学号。 USE JXGL

SELECT 学号 FROM cj

WHERE 成绩IS NULL GO

10. 使用ORDER BY子句

对查询的结果进行排序，通过使用ORDER BY子句实现。语法格式如下：ORDER BY表达式1 [ASC︱DESC][,…n]] 其中，表达式给出排序依据，即按照表达式的值升序（ASC）或降序（DESC）排列查询结果。在默认的情况下，ORDER BY按升序进行排列，即默认使用的是ASC关键字。

不能按ntext、text或image类型的列排序，因此ntext、text或image类型的列不允许出现在ORDER BY子句中。

【练习3.18】按年龄从小到大的顺序显示女学生的姓名，性别及出生时间。

USE JXGL GO

SELECT 姓名,性别,出生时间 FROM xs WHERE 性别='女' ORDER BY 出生时间DESC GO

3.3.2 SELECT语句的集合函数

为了进一步方便用户，增强检索功能，SELECT语句中的统计功能可以对查询结果集进行求和、求平均值、求最大最小值等操作。统计的方法是通过集合函数和GROUP BY子句、COMPUTE子句进行组合来实现。

1. 使用集合函数

汇总查询是把存储在数据库中的数据作为一个整体，对查询结果得到的数据集合进行汇总或求平均值等各种运算。SQL Server提供了

一系列统计函数，用于实现汇总查询。

【练习3.19】查询学生总人数 USE JXGL GO

SELECT 学生总人数=COUNT(*) FROM xs GO

2. 使用DISTINCT短语

如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL为缺省值），则表示不取消重复值。

【练习3.20】查询选修了“a003”课程的学生人数。 USE JXGL GO

SELECT 选课人数=COUNT(DISTINCT 学号) FROM cj

WHERE 课程号='a003' GO

3. 使用MAX()函数

【练习3.21】查询选修了“a003”号课程的学生最高分数。 USE JXGL GO

SELECT a003课程最高分=MAX(成绩) FROM cj

WHERE 课程号='a003' GO

3.3.3 使用GROUP BY子句其语法格式如下。 GROUP BY 列名 [HAVING 条件表达式]

HAVING 条件表达式选项是对生成的组进行筛选。

【练习3.22】在xs表中分专业统计出男生和女生的平均年龄及人数，结果按性别排序。

USE JXGL GO

SELECT 专业,性别,

AVG(DATEDIFF(YEAR,出生时间,GETDATE())) AS平均年龄, COUNT(*) AS 人数 FROM xs

GROUP BY 专业,性别 ORDER BY 性别 GO

【练习3.23】查询cj表中平均成绩大于80分的学生的学号、平均分，并按分数由高到低排序。

USE JXGL GO

SELECT 学号, AVG(成绩) AS平均成绩 FROM cj GROUP BY 学号

HAVING AVG(成绩)>=80 ORDER BY AVG(成绩) DESC GO

注意：WHERE子句是对表中的记录进行筛选，而HAVING子句是对组内的记录进行筛选，在HAVING子句中可以使用集合函数，并且其统计运算的集合是组内的所有列值，而WHERE 子句中不能使用集合函数。

3.3.4* 使用COMPUTE子句

使用COMPUTE BY子句，它对BY后面给出的列进行分组显示，并计算该列的分组小计。

语法格式如下：COMPUTE 集合函数[BY 分类表达式]

注意：

（1）COMPUTE或COMPUTE BY子句中的表达式，必须出现在选择列表中，并且必须将其指定为与选择列表中的某个表达式完全一样，不能使用在选择列表中指定的列的别名。（2）在COMPUTE或COMPUTE BY子句中，不能指定为ntext、text和image数据类型。（3）如果使用COMPUTE BY，则必须也使用ORDER BY子句。表达式必须与在ORDER BY 后列出的子句相同或是其子集，并且必须按相同的序列。例如，如果ORDER BY子句是：ORDER BY a,b,c 则COMPUTE子句可以是下面的任意一个（或全部），即：

COMPUTE BY a,b,c COMPUTE BY a,b COMPUTE BY a

（4）在SELECT INTO语句中不能使用COMPUTE。因此，任何由COMPUTE生成的计算结果不出现在用SELECT INTO语句创建的新表内。

【练习3.24】在cj表中求每门课程成绩大于80分的人数，并显示统计的明细。

USE JXGL GO SELECT * FROM cj

WHERE 成绩>=80 ORDER BY 课程号

COMPUTE COUNT(课程号) BY 课程号 GO

3.3.5 SELECT语句中的多表连接

在SQL Server中连接查询类型分为交叉连接、内连接、外连接、自连接。连接查询就是关系运算的连接运算，它是从多个数据源间（FORM）查询满足—定条件的记录。

1. 交叉连接

交叉连接也叫非连接，它是将两个表不加任何约束地组合起来。也就是将第一个表的所有行分别与第二个表的每行形成一条新的记录，连接后该结果集的行数等于两个表的行数积，列数等于两个表的列数和。在数学上，就是两个表的笛卡尔积，在实际应用中一般是没有意义的，但在数据库的数学模式上有重要的作用。

其语法结构如下：SELECT 列名列表FROM 表名1 CROSS JOIN 表名2 或者SELECT 列名列表FROM 表名1, 表名2

【练习3.25】在xs、cj表中实现交叉连接。 USE JXGL GO

SELECT xs.学号,姓名, 课程号,成绩 FROM xs CROSS JOIN cj 2. 内连接

内连接也叫自然连接，它是组合两个表的常用方法。内连接就是只包含满足连接条件的数据行，是将交叉连接结果集按照连接条件进行过滤的结果。连接条件通常采用“主键= 外键”的形式。内连接有以下两种语法格式。

其语法结构如下：SELECT 列名列表FROM 表名1 [INNER] JOIN 表名2 ON 表名1.列名=表名2.列名或SELECT 列名列表FROM 表名1, 表名2 WHERE 表名1.列名=表名2.列名【练习3.25】查xs、cj表内连接值。

USE JXGL GO

SELECT xs.学号,姓名, 课程号,成绩

FROM xs INNER JOIN cj ON xs.学号= cj.学号 3. 自连接

连接操作不仅可以在不同的表上进行，也可以在同一张表内进行自身连接，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。表名在FROM 子句中出现两次，必须对表指定不同的别名，在SELECT 子句中引用的列名也要使用表的别名进行

限定，使之在逻辑上成为两张表。

【练习3.25】在 USE JXGL GO

SELECT c2.课程号,c2.成绩

FROM cj as c1, cj as c2 where c1.学号= c2.学号 and c1.课程号= 'a008'

4. 合并结果集

合并查询也称联合查询是将两个或两个以上的查询结果合并，形成一个具有综合信息的查询结果。使用UNION语句可以把两个或两个以上的查询结果集合并为一个结果集。

其语法格式如下：查询语句 UNION [ALL] 查询语句注意:

（1）联合查询是将两个表（结果集）顺序连接。

（2）UNION中的每一个查询所涉及到的列必须具有相同的列数、相同位置的列的数据类型要相同。若长度不同，以最长的字段作为输出字段的长度。

（3）最后结果集中的列名来自第一个SELECT语句。

（4）最后一个SELECT查询可以带ORDER BY子句，对整个UNION操作

结果集起作用。且只能用第一个SELECT查询中的字段作排序列。（5）系统自动删除结果集中重复的记录，除非使用ALL关键字。 3.3.6子查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为子查询。子查询总是写在圆括号中，可以用在使用表达式的任何地方。上层的查询块称为外层查询或父查询，下层

查询块称为内查询或子查询。SQL语言允许多层嵌套查询。即一个子查询中还可以嵌套其他子查询。

注意：子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。

3.3.7 嵌套子查询

嵌套子查询的执行不依赖于外部嵌套。其一般的求解方法是由里向外处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

1．比较测试中的子查询

比较测试中的子查询是指父查询与子查询之间用比较运算符进行连接。但是用户必须确切地知道子查询返回的是一个单值，否则数据库服务器将报错。返回的单个值被外部查询的比较操作（如：=、!=、<、<=、>、>=）使用，该值可以是子查询中使用集合函数得到的值。

【练习3.26】求选修了数字信号处理课程的学生的学号及姓名。 USE JXGL GO

SELECT xs.学号,姓名,专业 FROM xs,cj

WHERE xs.学号=cj.学号AND cj.课程号= (SELECT 课程号 FROM kc

WHERE 课程名='数字信号处理') GO

【练习3.27】在xs表中查询和“张永强”在同一专业的所有男同学信息。

USE JXGL GO SELECT * FROM xs

WHERE 性别='男' AND 专业=

(SELECT 专业 FROM xs

WHERE 姓名='张永强') AND 姓名<>'张永强' GO

2．集合成员测试中的子查询

集合成员测试中的子查询是指父查询与子查询之间用IN或NOT IN进行连接，判断某个属性列值是否在子查询的结果中，通常子查询的结果是一个集合。IN表示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等；NOT IN表示不属于。【练习3.28】求选修了学分为4分的课程的学生的学号及姓名。

USE JXGL GO

SELECT DISTINCT xs.学号,姓名,专业 FROM xs,cj

WHERE xs.学号=cj.学号AND cj.课程号IN (SELECT 课程号FROM kc WHERE 学分=4) 3．批量比较测试中的子查询 (1) 使用ANY关键字的比较测试

通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的结果为TRUE，则ANY测试返回TRUE。

(2)使用ALL关键字的比较测试

通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的结果为FALSE，则ALL 测试返回FALSE。

ANY和ALL都用于一个值与一组值的比较，以“>”为例，ANY表示大于一组值中的任意一个，ALL表示大于一组值中的每一个。比如，>ANY（1,2,3）表示大于1；而>ALL（1,2,3）表示大于3。

【练习3.29】在kc表中查询学分大于最小学分的课程信息。

USE JXGL GO SELECT * FROM kc

WHERE 学分>ANY (SELECT 学分FROM kc) GO

3.3.8* 相关子查询

所谓相关子查询，是指在子查询中，子查询的查询条件中引用了外层查询表中的字段值。相关子查询的结果集取决于外部查询当前的数据行，这一点是与嵌套子查询不同。