数据库的查询、视图和游标

最新推荐文章于 2024-04-02 09:38:24 发布

tjjingpan

最新推荐文章于 2024-04-02 09:38:24 发布

阅读量2.5k

点赞数 5

本文链接：https://blog.csdn.net/tjjingpan/article/details/104358855

版权

创建数据库和保存数据的主要目的是使用这些数据，使用的主要方法就是查询。查询的数据源可以来自一个或多个基本表及由此形成的数据集。

视图是由一个或多个基本表导出的数据信息，是组织信息的逻辑表，对于管理和查询数据非常方便。

游标在数据库与应用程序之间提供了数据处理单位的变换机制。

本章介绍数据库的查询、视图和游标。

4.1 数据库的查询：SELECT

说明从这个主体语法可以看出，SELECT语句返回一个表的结果集，通常该结果集称为表值表达式。

**4.1.1 选择查询结果输出列：* /列表下面分不同情况说明选择查询结果输出列。**

1. 选择所有列

【例4.1】查询xscj数据库中xsb表的所有数据。

SELECT *  FROM xsb

2. 选择一个表中指定的列

SELECT 学号,姓名,总学分
	FROM xsb
	WHERE 专业 = '计算机'

3. 定义列别名

当希望查询结果中的某些列或所有列显示时使用自己定义的列标题时，可以在列名之后使用AS子句来更改查询结果的列标题名。

【例4.3】查询xsb表中计算机系同学的学号、姓名和总学分，结果中各列的标题分别指定为number、name和mark。代码如下，执行结果如图4.2所示。

SELECT 学号 AS number,姓名 AS name,总学分 AS mark
	FROM xsb
	WHERE 专业 = '计算机'

更改查询结果中的列标题也可以使用“列别名=表达式”的形式。例如：

SELECT number = 学号,name = 姓名 ,mark = 总学分
	FROM xsb
	WHERE 专业 = '计算机'

当自定义的列标题中含有空格时，必须使用引号将标题括起来。例如：

SELECT 'Student number' = 学号,姓名 AS 'Student name' ,mark = 总学分, 性别
	FROM xsb
	WHERE 专业 = '计算机'

说明不允许在WHERE子句中使用列别名。这是因为，执行WHERE代码时，可能尚未确定列值。例如，下述查询是非法的：

SELECT 性别 AS SEX FROM xsb WHERE SEX = 0

插入成绩表数据cjb

INSERT INTO cjb
VALUES
('191301','102',78),
('191301','206',76),
('191303','101',62),
('191303','102',70),
('191303','206',81),
('191304','101',90),
('191304','102',84),
('191304','206',65),
('191302','102',78),
('191302','206',78),
('191306','101',65),
('191306','102',71),
('191306','206',80),
('191307','101',78),
('191307','102',80),
('191307','206',68),
('191308','101',85),
('191308','102',64),
('191308','206',87),
('191309','101',66),
('191309','102',83),
('191309','206',70),
('191310','101',95),
('191310','102',90),
('191310','206',89),
('191311','101',91),
('191311','102',70),
('191311','206',76),
('191313','101',63),
('191313','102',79),
('191313','206',60),
('221301','101',80),
('221302','101',65),
('221303','101',87),
('221304','101',91),
('221310','101',76),
('221316','101',81),
('221318','101',70),
('221320','101',82),
('221321','101',76),
('221341','101',90)

4. 替换查询结果中的数据

在对表进行查询时，有时希望对所查询的某些列得到的是一种概念而不是具体的数据。例如，查询xsb表的总学分，希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。

要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：

【例4.4】查询xsb表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，则替换为“尚未选课”；若总学分小于50，则替换为“不及格”；若总学分在50与52之间，则替换为“合格”；若总学分大于52，则替换为“优秀”。列标题更改为“等级”。

SELECT 学号,姓名,等级=
	CASE 
		WHEN 总学分 IS NULL THEN '尚未选课'
		WHEN 总学分 < 50 THEN '不及格'
		WHEN 总学分 >=50 and 总学分<=52  THEN '合格'
		ELSE '优秀'
	END
FROM xsb
WHERE 专业='计算机'

5. 计算列值

使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：

【例4.5】按120分计算成绩并显示学号为“191301”的学生的成绩情况。

代码如下，执行结果如图4.4所示。

SELECT 学号,课程号,成绩120=成绩*1.20
	FROM cjb
	WHERE 学号='191301'

计算列值使用算术运算符：+（加）、−（减）、*（乘）、/（除）和%（取余）。其中，算术运算符（+、−、*、/）可以用于任何数字类型的列，包括int、smallint、tinyint、decimal、numeric、float、real、money和smallmoney；%可以用于上述除money和smallmoney以外的数字类型。

6. 消除结果集中的重复行

对表只选择某些列时，可能会出现重复行。例如，若对xscj数据库的xsb表只选择专业和总学分，则出现多行重复的情况。可以使用DISTINCT关键字消除结果集中的重复行，其格式是：

关键字DISTINCT的含义是：对结果集中的重复行只选择一个，保证行的唯一性。

【例4.6】对xscj数据库的xsb表只选择专业和总学分，消除结果集中的重复行。

SELECT DISTINCT 专业,总学分 FROM xsb

与DISTINCT相反，当使用关键字ALL时，将保留结果集中的所有行。当SELECT语句中省略ALL与DISTINCT时，默认值为ALL。

7. 限制结果集返回行数

如果SELECT语句返回的结果集的行数非常多，那么可以使用TOP选项限制其返回的行数。TOP选项的基本格式为：

指示只能从查询结果集返回指定的第一组行或指定的百分比数目的行。“表达式”可以是指定数目或百分比数目的行。若带PERCENT关键字，则表示返回结果集的前（表达式值）%行。

【例4.7】对xscj数据库的xsb表选择姓名、专业和总学分，返回结果集的前6行。

SELECT TOP 6 姓名, 专业,总学分 FROM xsb

8. 选择用户定义数据类型列

有关用户定义数据类型的内容将在后面章节详细讨论。

9. 聚合函数

SELECT子句的表达式中还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。聚合函数通常与GROUP BY子句一起使用。如果一个SELECT语句中有一个GROUP BY子句，则这个聚合函数对所有列起作用；如果没有，则SELECT语句只产生一行作为结果。SQL Server所提供的聚合函数如表4.1所示。

下面对常用的聚合函数进行介绍。

（1）SUM和AVG

SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：

其中，“表达式”可以是常量、列、函数或表达式，其数据类型只能是int、smallint、tinyint、bigint、decimal、numeric、float、real、money和smallmoney。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。SUM / AVG忽略NULL值。

SELECT SUM(学分)  AS '总学分'  FROM kcb

执行结果如图4.6所示。

使用聚合函数作为SELECT的选择列时，若不为其指定列标题，则系统将对该列输出标题“无列名”。

SELECT AVG(成绩) AS '选修101课程学生平均成绩' 
FROM cjb WHERE 课程号=101

（2）MAX和MIN

MAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为：

其中，“表达式”可以是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期类型。ALL、DISTINCT的含义及默认值与SUM/AVG函数相同。MAX/MIN忽略NULL值。

【例4.9】求选修101课程的学生的最高分和最低分。

SELECT MAX(成绩) AS '计处机基础最高分',MIN(成绩) AS '计算机基础最低分'
FROM cjb
WHERE 课程号= '101'

（3）COUNT

COUNT用于统计组中满足条件的行数或总行数，格式为：

其中，“表达式”的数据类型是除text、image或ntext之外的任何类型。ALL、DISTINCT的含义及默认值与SUM/AVG函数相同，COUNT忽略NULL值。

【例4.10】求学生的总数、总学分在50分以上的人数和专业个数。

SELECT  COUNT(*) AS '学生总数',COUNT (DISTINCT 专业) AS '专业个数'
FROM xsb;
GO
SELECT  COUNT(总学分) AS '总学分>50人数'
FROM xsb
WHERE 总学分>50
GO

查询结果如下:

使用COUNT(*)时将返回检索行的总数目，不论其是否包含NULL值。COUNT_BIG函数的格式、功能与COUNT函数相同，区别仅在于COUNT_BIG返回bigint类型值。

4.1.2 选择查询条件：WHERE

下面分不同情况说明选择查询条件。

1. 表达式比较

比较运算符用于比较两个表达式值，共有9个，分别是=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<>（不等于）、!=（不等于）、!<（不小于）、!>（不大于）。比较运算的格式为：

其中，“表达式”是除text、ntext和image以外类型的表达式。

当两个表达式值均不为空值（NULL）时，比较运算返回逻辑值TRUE（真）或FALSE（假）。而当两个表达式值中有一个为空值或都为空值时，比较运算将返回UNKNOWN。

可以将多个判定运算的结果通过逻辑运算符（NOT、AND和OR）再组成更为复杂的查询条件。

【例4.11】查询xsb表中通信工程专业总学分大于等于42的同学的情况。

SELECT * 
	FROM xsb
	WHERE 专业= '通信工程' AND 总学分 >=42

2. 模式匹配

LIKE谓词用于指出字符串是否与指定的字符串相匹配，返回逻辑值TRUE或FALSE。LIKE谓词表达式的格式为：

说明（1）表达式：一般为字符串表达式，在查询语句中可以是列名。

（2）模式串：可以使用通配符，表4.2列出了LIKE谓词可以使用的通配符及其说明。

（3）转义符：应为有效的SQL Server字符，没有默认值，且必须为单个字符。当模式串中含有与通配符相同的字符时，应通过该字符前的转义符指明其为模式串中的一个匹配字符。使用ESCAPE可指定转义符。

（4）NOT LIKE：NOT LIKE与LIKE的作用相反。

使用带%通配符的LIKE时，模式字符串中的所有字符都有意义，包括起始或尾随空格。

【例4.12】查询xsb表中姓“王”且单名的学生情况。

SELECT *
FROM xsb
WHERE 姓名 LIKE '王_'

【例4.13】查询xsb表中学号倒数第5个数字为9，最后1个数在1～5的学生学号、姓名及专业。

SELECT 学号,姓名,专业
FROM xsb
WHERE 学号 LIKE '%9___[1-5]'

如果需要查找一个通配符，则必须使用一个转义字符。

3. 范围比较

用于范围比较的关键字有两个：BETWEEN和IN。

（1）当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，格式为：

当不使用NOT时，若“表达式”的值在“表达式1”与“表达式2”之间（包括这两个值），则返回TRUE，否则返回FALSE；当使用NOT时，返回值刚好相反。

注意“表达式1”的值不能大于“表达式2”的值。

【例4.14】查询xsb表中不在1995年出生的学生情况。

SELECT 学号,姓名,专业,出生时间
FROM xsb
WHERE 出生时间 NOT BETWEEN '1995-1-1' and '1995-12-31'

（2）使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任何一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：

【例4.15】查询xsb表中专业为“软件工程”或“通信工程”学生的情况。

SELECT  *
FROM  xsb
WHERE 专业 IN ('软件工程','通信工程')

也下面查询语句相同:

SELECT  *
FROM  xsb
WHERE 专业 ='软件工程' OR 专业='通信工程'

4. 空值比较

当需要判定一个表达式的值是否为空值时，使用IS NULL关键字，格式为：

当不使用NOT时，若表达式的值为空值，则返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。

【例4.16】查询备注为空（没有备注内容）的学生。

SELECT *
FROM xsb
WHERE 备注 IS NULL

5. 子查询

在查询条件中，可以使用另一个查询的结果作为条件的一部分，例如，判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。

T-SQL允许SELECT多层嵌套使用，用来表示复杂的查询。子查询除了可以用在SELECT语句中，还可以用在INSERT、UPDATE及DELETE语句中。子查询通常与IN、EXIST谓词及比较运算符结合使用。

（1）IN子查询

IN子查询用于判断一个给定值是否在子查询结果集中，格式为：

当表达式与子查询的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。

【例4.17】查找选修了课程号为206的学生的情况。

在“查询分析器”窗口中输入并执行如下查询脚本：

SELECT * 
	FROM xsb
	WHERE 学号 IN 
	(SELECT 学号
		FROM cjb
		WHERE 课程号='206'
	)

本例中，先执行子查询：

得到一个只含有学号列的表，cjb表中每个课程名列值为206的行在结果表中都有一行。再执行外查询，若xsb表中某行的学号列值等于子查询结果表中的任一个值，该行就被选择。

IN和NOT IN子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套的子查询。

例4.18】查找未选修离散数学的学生情况。

SELECT * 
	FROM xsb
	WHERE 学号 NOT IN
	(
		SELECT 学号
			FROM cjb
			WHERE 课程号 IN
			(
				SELECT 课程号
				FROM kcb
				WHERE 课程名 = '离散数学' 
			)
	)

（2）比较子查询

这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式为：

其中，ALL、SOME和ANY说明对比较运算的限制。

ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE。

SOME或ANY表示表达式只要与子查询结果集中的某个值满足比较的关系，就返回TRUE，否则返回FALSE。

【例4.19】查找选修了离散数学的学生学号。

SELECT 学号
	FROM cjb
	WHERE 课程号 = 
	(
		SELECT 课程号
			FROM kcb
			WHERE 课程名= '离散数学'
	)

【例4.20】查找比所有计算机系的学生年龄都大的学生。

SELECT *
	FROM xsb
	WHERE 出生时间 < ALL
	(
		SELECT 出生时间
		FROM xsb
		WHERE 专业 = '计算机'
	)

【例4.21】查找206号课程成绩不低于101号课程最低成绩的学生学号。

SELECT 学号
 FROM cjb
 WHERE 课程号 = '206' AND 成绩 !< ANY
 (
	SELECT 成绩
	 FROM cjb
	 WHERE 课程号 ='101'
 )

【例4.22】查找选修206号课程的学生姓名。

SELECT 姓名
	FROM  xsb
	WHERE EXISTS
	(
		SELECT * 
		FROM cjb
		WHERE 学号 = xsb.学号 AND 课程号 = '206'
	)

分析：

① 子查询的条件中使用了限定形式的列名引用“xsb.学号”，表示这里的“学号”列出自表xsb。

② 与前面的子查询例子的不同点是，前面的例子中内层查询只处理一次，得到一个结果集，再依次处理外层查询；而本例的内层查询要处理多次，因为内层查询与xsb.学号有关，外层查询中xsb表的不同行有不同的学号值。这类子查询称为相关子查询，因为子查询的条件依赖于外层查询中的某些值。

其处理过程如下：

首先查找外层查询中xsb表的第一行，根据该行的学号列值处理内层查询，若结果不为空，则WHERE条件为真，就把该行的姓名值取出作为结果集的一行；然后再找xsb表的第2、第3、…、第n行，重复上述处理过程直到xsb表的所有行都查找完为止。

【例4.23】查找选修了全部课程的同学的姓名。

SELECT 姓名
FROM xsb
WHERE NOT EXISTS
(
	SELECT * 
		FROM kcb
		WHERE NOT EXISTS
		(
			SELECT * 
			FROM cjb
			WHERE 学号 = xsb.学号 AND 课程号 = kcb.课程号
	)
)

说明由于没有人选全部课程，所以结果为空。

另外，子查询还可以用在SELECT语句的其他子句中，如FROM子句。SELECT关键字后面也可以定义子查询。

【例4.24】从xsb表中查找所有女学生的姓名、学号及其与“191301”号学生的年龄差距。

SELECT  学号,姓名,YEAR(出生时间) - 
		YEAR(( SELECT 出生时间
				FROM xsb
				WHERE 学号='191301'
				)
			)
FROM xsb
WHERE 性别 = 0

说明YEAR函数用于取出日期类型数据的年份。

4.1.3 指定查询对象：FROM

SELECT的查询对象由FROM子句指定，查询对象主要包括表或视图，称为表源。

1. 表或视图名

表或视图名指定SELECT语句要查询的表或视图，表和视图可以是一个或多个，有关视图的内容在下一节中介绍。

【例4.25】查找kcb表中101号课程的开课学期。

USE sxcj
GO
SELECT 开课学期
	FROM kcb
	WHERE 课程号 = '101'

查询结果为1。

【例4.26】查找191301号学生计算机基础课的成绩。

SELECT 成绩
	FROM cjb,kcb
	WHERE cjb.课程号=kcb.课程号 AND 学号='191301'

可以使用AS选项为表指定别名，AS关键字也可以省略，直接给出别名即可。别名主要用在相关子查询及连接查询中。如果FROM子句指定了表别名，则这条SELECT语句中的其他子句都必须使用表别名来代替原始的表名。

【例4.27】查找选修了与学号为191302的同学所选修的课程完全相同的同学的学号。


--查找选修了与学号为191302的同学所选修的课程完全相同的同学的学号。
SELECT DISTINCT
	学号 
FROM
	cjb AS CJ1 
WHERE
	NOT EXISTS (   --结果集为空返回True,有结果集返回False
	SELECT
		* 
	FROM
		cjb AS CJ2 
	WHERE
		CJ2.学号 = '191302' 
	AND NOT EXISTS ( SELECT * FROM cjb AS CJ3 WHERE CJ3.学号 = CJ1.学号 AND CJ3.课程号 = CJ2.课程号 ) 
	)

/*
主查询 第1行 191301 第二查询第1行 102 AND NOT EXISTS (SELECT * FROM cjb AS CJ3 WHERE CJ3.学号 = 191301 AND CJ3.课程号 =102) False  主查询条件为True
主查询 第1行 191301 第二查询第2行 206 AND NOT EXISTS (SELECT * FROM cjb AS CJ3 WHERE CJ3.学号 = 191301 AND CJ3.课程号 =206) False  主查询条件为True
.
.
.
主查询 第12行 221301 第二查询第一行 102 AND NOT EXISTS	(SELECT * FROM cjb AS CJ3 WHERE CJ3.学号 = 221301 AND CJ3.课程号 =102) True		主查询条件为False
主查询 第12行 221301 第二查询第一行 102 AND NOT EXISTS	(SELECT * FROM cjb AS CJ3 WHERE CJ3.学号 = 221301 AND CJ3.课程号 =206)	True	主查询条件为False
.
.
.
*/

2. 导出表

导出表表示由子查询中SELECT语句的执行而返回的表，但必须使用AS关键字为子查询产生的中间表定义一个别名。

【例4.28】从xsb表中查找总学分大于50的男同学的姓名和学号。

SELECT 姓名,学号,总学分
FROM ( SELECT 姓名,学号,性别,总学分
		FROM xsb
		WHERE 总学分 >=50
	)	AS student
WHERE 性别=1

说明在这个例子中，首先处理FROM子句中的子查询，将结果放到一个中间表中并为表定义一个名称student，然后再根据外部查询条件从student表中查询出数据。另外，子查询还可以嵌套使用。

子查询用于FROM子句时，也可以为列指定别名。

【例4.29】在xsb表中查找1995年1月1日以前出生的学生的姓名和专业。

SELECT student.name,student.speciality
	FROM	(SELECT * FROM xsb WHERE 出生时间<'19950101')
	AS student (num,name,sex,birthday,speciality,score,mem)

注意若要为列指定别名，则必须为所有列指定别名。

4.1.4 连接：=/JOIN

连接是两元运算，可以对两个或多个表进行查询，结果通常是含有参加连接运算的两个表（或多个表）的指定列的表。例如，在xscj数据库中要查找选修了离散数学课程的学生的姓名和成绩，就需要将xsb、kcb和cjb 3个表进行连接，才能查找到结果。

在实际的应用中，多数情况下，用户查询的列都来自多个表。例如，在学生成绩数据库中查询选修了某个课程号的课程的学生姓名、该课课程名和成绩，所需要的列来自xsb、kcb和cjb3个表。把涉及多个表的查询称为连接查询。

在T-SQL中，连接查询有两类表示形式：一是符合SQL标准的连接谓词表示形式；另一是T-SQL扩展的使用关键字JOIN的表示形式。

1. 连接谓词

可以在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行连接，这种表示形式称为连接谓词表示形式。

【例4.30】查找xscj数据库每个学生的情况以及选修的课程情况。

SELECT xsb.*, cjb.*
	FROM xsb,cjb
	WHERE xsb.学号 = cjb.学号

结果表将包含xsb表和cjb表的所有列。

注意连接谓词中的两个列（即字段）称为连接字段，它们必须是可比的。如本例连接谓词中的两个字段分别是xsb和cjb表中的学号字段。不同表中的字段名，需要在字段名之前加上表名以示区别。

连接谓词中的比较符可以是<、<=、=、>、>=、!=、<>、!<和 !>，当比较符为“=”时，就是等值连接。

（1）自然连接

它在目标列中去除相同的字段名。

【例4.31】自然连接查询。


SELECT xsb.*, cjb.课程号,cjb.成绩
	FROM xsb,cjb
	WHERE xsb.学号 = cjb.学号

本例所得的结果表包含以下字段：学号、姓名、性别、出生时间、专业、总学分、备注、课程号、成绩。若选择的字段名在各个表中是唯一的，则可以省略字段名前的表名。

如本例的SELECT语句也可写为：

SELECT xsb.*, 课程号,成绩
	FROM xsb,cjb
	WHERE xsb.学号 = cjb.学号

【例4.32】查找选修了206号课程且成绩在80分以上的学生姓名及成绩。


SELECT 姓名,成绩
	FROM xsb,cjb
	WHERE xsb.学号 = cjb.学号 AND 课程号 ='206' AND 成绩 >= 80

（2）多表连接

有时用户所需要的字段来自两个以上的表，那么就要对两个以上的表进行连接，称之为多表连接。

例4.33】查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。

SELECT xsb.学号,姓名, 课程名,成绩
	FROM xsb,kcb,cjb
	WHERE xsb.学号 = cjb.学号
		AND kcb.课程号=cjb.课程号
		AND 课程名='计算机基础'
		AND  成绩 >=80

连接和子查询可能都要涉及两个或多个表，要注意连接与子查询的区别：连接可以合并两个或多个表中的数据，而带子查询的SELECT语句的结果只能来自一个表，子查询的结果是用来作为选择结果数据时进行参照的。

2. 以JOIN关键字指定的连接

T-SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有所增强。FROM子句的<连接表>表示将多个表连接起来。

以JOIN关键字指定的连接有3种类型：内连接、外连接、交叉连接。

① 内连接。

指定了INNER关键字的连接是内连接，内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。

【例4.34】查找xscj数据库中每个学生的情况以及选修的课程情况。

SELECT *
	FROM xsb  INNER  JOIN cjb
		ON xsb.学号 = cjb.学号

执行的结果将包含xsb表和cjb表的所有字段（不去除重复字段——学号）。

内连接是系统默认的，可以省略INNER关键字。使用内连接后仍可使用WHERE子句指定条件。

【例4.35】查询选修了206课程且成绩大于80的学生姓名和成绩

SELECT 姓名,成绩
	FROM xsb  INNER  JOIN cjb
		ON xsb.学号 = cjb.学号
	WHERE 课程号 = '206' AND 成绩 >=80

内连接还可以用于多个表的连接。

【例4.36】用FROM子句的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。

SELECT xsb.学号,姓名,课程名,成绩
	FROM xsb  JOIN cjb JOIN kcb
		ON cjb.课程号 = kcb.课程号
		ON xsb.学号 = cjb.学号
	WHERE 课程名 = '计算机基础' AND 成绩 >=80

作为一种特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。

【例4.37】查找不同课程成绩相同的学生的学号、课程号和成绩。

SELECT  a.学号,b.课程号,b.课程号,a.成绩
	FROM cjb a JOIN cjb b 
	ON a.成绩=b.成绩 AND a.学号= b.学号 AND a.课程号!=b.课程号

② 外连接。

指定了OUTER关键字的连接为外连接，外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括以下3种。

• 左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行，还包括左表的所有行。

• 右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行，还包括右表的所有行。

• 完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行，还包括两个表的所有行。

其中的OUTER关键字均可省略。

【例4.38】查找所有学生情况，以及他们选修的课程号，若学生未选修任何课，也要包括其情况。

SELECT * FROM xsb
SELECT * FROM cjb
SELECT xsb.* ,课程号
	FROM xsb LEFT OUTER JOIN cjb
	ON xsb.学号=cjb.学号

本例执行时，若有学生未选任何课程，则结果表中相应行的课程号字段值为NULL。

【例4.39】查找被选修了的课程的情况和所有开设的课程名。


SELECT cjb.*,课程名
FROM cjb RIGHT JOIN kcb
ON cjb.课程号=kcb.课程号

本例执行时，若某课程未被选修，则结果表中相应行的学号、课程号和成绩字段值均为NULL。

③ 交叉连接。

交叉连接实际上是将两个表进行笛卡儿积运算，结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表的行数之积。

例4.40】列出学生所有可能的选课情况。

SELECT 学号,姓名,课程号,课程名
FROM xsb CROSS JOIN kcb

交叉连接也可以使用WHERE子句进行条件限定。

4.1.5 指定查询结果分组方法：GROUP BY

GROUP BY子句主要用于根据字段对行分组。例如，根据学生所学的专业对xsb表中的所有行分组，结果是每个专业的学生成为一组。GROUP BY子句有ISO标准和非ISO标准两种语法格式可用。这里介绍ISO标准的GROUP BY子句。

语法格式如下：

【例4.41】在xscj数据库上产生一个结果集，包括每个专业的男生、女生人数，总人数及学生总人数。

代码如下，执行结果如图4.16所示。

SELECT 专业,性别,COUNT(*) AS '人数'
FROM xsb
GROUP BY ROLLUP(专业,性别)

【例4.42】在xscj数据库上产生一个结果集，包括每个专业的男生、女生人数，总人数，以及男生总数、女生总数、学生总人数。

SELECT 专业,性别,COUNT(*) AS '人数'
FROM xsb
GROUP BY CUBE(专业,性别)

【例4.43】生成一个结果集，分别根据专业和性别对人数进行聚合。

SELECT 专业,性别,COUNT(*) AS '人数'
FROM xsb
GROUP BY GROUPING SETS(专业,性别)

4.1.6 指定查询结果分组后筛选条件：HAVING

使用GROUP BY子句和聚合函数对数据进行分组后，还可以使用HAVING子句对分组数据进行进一步的筛选。

例如，查找xscj数据库中平均成绩在85分以上的学生，就是在cjb表上按学号分组后筛选出符合平均成绩大于等于85的学生。

【例4.44】查找平均成绩在85分以上的学生的学号和平均成绩。

SELECT 学号,AVG(成绩) AS '平均成绩'
FROM cjb
GROUP BY 学号
HAVING AVG(成绩) >=85

说明在SELECT语句中，当WHERE、GROUP BY与HAVING子句都被使用时，要注意它们的作用和执行顺序。WHERE用于筛选由FROM子句指定的数据对象，GROUP BY用于对WHERE的结果进行分组，HAVING则是对GROUP BY以后的分组数据进行过滤。

【例4.45】查找选修课程超过两门且成绩都在80分以上的学生的学号。

SELECT 学号,AVG(成绩) AS '平均成绩'
	FROM cjb
	WHERE 成绩 > 80
	GROUP BY 学号
	HAVING COUNT(*) > 2

说明本查询将cjb表中成绩大于80的记录按学号分组，对每一组记录计数，选出记录数大于2的各组的学号值并形成结果表。

【例4.46】查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。

SELECT 学号,AVG(成绩) AS '平均成绩'
FROM cjb
WHERE 学号 IN
	(
		SELECT 学号 
			FROM xsb
			WHERE 专业= '通信工程'
	)
GROUP BY 学号 HAVING AVG(成绩)>=85

说明（1）先执行WHERE查询条件中的子查询，得到通信工程专业所有学生的学号集。然后对cjb表中的每一条记录，判断其“学号”字段值是否在前面所求得的学号集中。若否，则跳过该记录，继续处理下一条记录；若是，则加入WHERE的结果集。

（2）对cjb表均筛选完后，按学号进行分组，再在各分组记录中选出平均成绩值大于等于85的记录并形成最后的结果集。

4.1.7 指定查询结果排序顺序：ORDER BY

在应用中经常要对查询的结果排序输出，如将学生成绩由高到低排序。在SELECT语句中，使用ORDER BY子句对查询结果进行排序。ORDER BY子句的格式为：

其中，“排序表达式”可以是列名、表达式或一个正整数，当它是一个正整数时，表示按表中该位置上的列排序。“排序名”是Windows排序规则名称或SQL排序规则名称。关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC。

1. 对查询的结果排序输出

【例4.47】将通信工程专业的学生按出生时间先后顺序排序。

SELECT * 
FROM xsb
WHERE 专业='通信工程'
ORDER BY 出生时间

例4.48】将计算机专业学生的“计算机基础”课程成绩按降序排列。

SELECT 姓名,课程名,成绩
FROM xsb,kcb,cjb
WHERE xsb.学号= cjb.学号
	AND cjb.课程号=kcb.课程号
	AND 课程名='计算机基础'
	AND 专业= '计算机'
ORDER BY 成绩 DESC

tjjingpan

关注

5
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
数据库的查询、视图和游标

创建数据库和保存数据的主要目的是使用这些数据，使用的主要方法就是查询。查询的数据源可以来自一个或多个基本表及由此形成的数据集。视图是由一个或多个基本表导出的数据信息，是组织信息的逻辑表，对于管理和查询数据非常方便。游标在数据库与应用程序之间提供了数据处理单位的变换机制。本章介绍数据库的查询、视图和游标。4.1 数据库的查询：SELECT说明从这个主体语法可以看出，SELEC...
复制链接

扫一扫