当前位置 :| 主页>数据库工程师>结构化查询>

单表查询

来源： 作者： 时间：2008-03-16 点击：

一、

选择表中的若干列

查询指定列

例1

查询全体学生的学号与姓名

SELECT Sno,Sname

FROM Student;

详细信息…

<目标列表达式> 中各个列的先后顺序可以与表中的顺序不一致。也就是说，用户在查询时可以根据应用的需要改变列的显示顺序。

例2

查询全体学生的姓名、学号、所在系

SELECT Sname, Sno, Sdept

FROM Student;

详细信息…

这时结果表中的列的顺序与基表中不同，是按查询要求，先列出姓名属性，然后再列学号属性和所在系属性。

查询全部列

例3

查询全体学生的详细记录

SELECT *

FROM Student;

详细信息…

该SELECT语句实际上是无条件地把Student表的全部信息都查询出来，所以也称为全表查询，这是最简单的一种查询。

查询经过计算的值

SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。

例4

查全体学生的姓名及其出生年份

SELECT Sname, 1996-Sage

FROM Student;

详细信息…

本例中，<目标列表达式>中第二项不是通常的列名，而是一个计算表达式，是用当前的年份（假设为1996年）减去学生的年龄，这样，所得的即是学生的出生年份。输出的结果为：

Sname	1996-Sage
-------	---------
李勇	1976
刘晨	1977
王名	1978
张立	1978

不仅可以是算术表达式，还可以是字符串常量、函数等。

例5

查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名

SELECT Sname, 'Year of Birth:', 1996-Sage, ISLOWER(Sdept)

FROM Student;

详细信息…

结果为：

Sname	'Year of Birth:'	1996-Sage	ISLOWER(Sdept)
-------	----------------	---------	--------------
李勇	Year of Birth:	1976	cs
刘晨	Year of Birth:	1977	if
王名	Year of Birth:	1978	ma
张立	Year of Birth:	1978	if

用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如对于上例，可以如下定义列别名

SELECT	Sname NAME, 'Year of Birth:'BIRTH,1996-SageBIRTHDAY,
	ISLOWER(Sdept) DEPARTMENT
FROM	Student;

结果为：

NAME	BIRTH	BIRTHDAY	DEPARTMENT
-------	----------------	---------	--------------
李勇	Year of Birth:	1976	cs
刘晨	Year of Birth:	1977	if
王名	Year of Birth:	1978	ma
张立	Year of Birth:	1978	if

二、

选择表中的若干元组

消除取值重复的行

例6

查所有选修过课的学生的学号

SELECT Sno

FROM SC;

详细信息…

假设SC表中有下列数据
Sno      Cno    Grade
------- ------- -------
95001      1       92
95001      2       85
95001      3       88
95002      2       90
95002      3       80 　　

执行上面的SELECT语句后，结果为：

  Sno
-------
95001
95001
95001
95002
95002

该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定DISTINCT短语：
SELECT DISTINCT Sno
FROM SC; 　

执行结果为：

  Sno
-------
95001
95002

查询满足条件的元组

查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表3-3所示。
表3-3 常用的查询条件

查询条件	谓词
───────	───────────────
比较	上述比较运算符
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE
空值	IS NULL, IS NOT NULL
多重条件	AND, OR

(1)

比较

例7

查计算机系全体学生的名单

SELECT Sname
FROM Student WHERE Sdept = 'CS';

例8

查所有年龄在20岁以下的学生姓名及其年龄

SELECT Sname, Sage
FROM Student
WHERE Sage <20;
或
SELECT Sname, Sage
FROM Student
WHERE NOT Sage>= 20;

例9

查考试成绩有不及格的学生的学号

SELECT DISTINCT Sno
FROM Course
WHERE Grade <60;

这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。

(2)

确定范围

例10

查询年龄在20至23岁之间的学生的姓名、系别、和年龄

SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;

与BETWEEN...AND...相对的谓词是NOT BETWEEN...AND...。

例11

查询年龄不在20至23岁之间的学生姓名、系别和年龄。

SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;

(3)

确定集合

例12

查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别

SELECT Sname, Ssex 　
FROM Student 　
WHERE Sdept IN ('IS', 'MA', 'CS')

与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。

例13

查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别

SELECT Sname, Ssex 　
FROM Student 　
WHERE Sdept NOT IN ('IS', 'MA', 'CS')

(4)

字符匹配

谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：
[NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']

其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。
%(百分号) 代表任意长度（长度可以为0）的字符串。
_(下横线) 代表任意单个字符。

例14

查所有姓刘的学生的姓名、学号和性别

SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';

例15

查姓“欧阳”且全名为三个汉字的学生的姓名

SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__';

注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟２个_。

例16

查名字中第二字为“阳”字的学生的姓名和学号

SELECT Sname, Sno
FROM Student
WHERE Sname LIKE '__阳%';

例17

查所以不姓刘的学生姓名

SELECT Sname, Sno, Ssex

FROM Student

WHERE Sname NOT LIKE '刘%';

详细信息…

如果用户要查询的匹配字符串本身就含有%或_，比如要查名字为DB_Design的课程的学分，应如何实现呢？这时就要使用ESCAPE ''短语对通配符进行转义了。

例18

查DB_Design课程的课程号和学分

SELECT Cno, Ccredit

FROM Course

WHERE Cname LIKE ’DB\_Design’ ESCAPE ’\’

详细信息…

ESCAPE ’\’短语表示\为换码字符，这样匹配串中紧跟在\后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。

例19

查以”DB_”开头，且倒数第三个字符为i的课程的详细情况

SELECT *

FROM Course

WHERE Cname LIKE ’DB\_%i__’ ESCAPE ’\’;

详细信息…

注意这里的匹配字符串’DB\_%i__’。第一个_前面有换码字符\，所以它被转义为普通的_字符。而%、第二个_和第三个_前面均没有换码字符\，所以它们仍作为通配符。其执行结果为：

Cno	Cname	Ccredit
------	------------	---------
8	DB_Design	4
10	DB_Programing	2
13	DB_DBMS Design	4

(5)

涉及空值的查询

例20

某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号

SELECT Sno, Cno 　
FROM SC
WHERE Grade IS NULL;

注意这里的'IS'不能用等号（'='）代替。

例21

查所有有成绩的记录的学生学号和课程号

SELECT Sno, Cno 　
FROM SC
WHERE Grade IS NOT NULL;

(6)

多重条件查询

逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算符同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但用户可以用括号改变优先级。

例22

查CS系年龄在20岁以下的学生姓名

SSELECT Sname
FROM Student
WHERE Sdept='CS' AND Sage<20;

例12 中的IN谓词实际上是多个OR运算符的缩写，因此例12中的查询也可以用OR运算符写成如下等价形式：

SELECT Sname, Ssex 　
FROM Student 　
WHERE Sdept='IS' OR Sdept='MA' OR Sdept='CS';

三、

对查询结果排序

如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。

例23

查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

SELECT Sno, Grade

FROM SC

WHERE Cno='1' GROUP BY Grade DESC;

详细信息…

前面已经提到，可能有些学生选修了3号课程后没有参加考试，即成绩列为空值。用ORDER BY子句对查询结果按成绩排序时，若按升序排，成绩为空值的元组将最后显示，若按降序排，成绩为空值的元组将最先显示。例如上述查询可以得到如下结果表：

例24

查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列

SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;

四、

使用集函数

为了进一步方便用户，增强检索功能，SQL提供了许多集函数，主要包括：
COUNT([DISTINCT|ALL] *) 统计元组个数
COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数
SUM([DISTINCT|ALL] <列名>) 计算一列值的总和（此列必须是数值型）
AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值（此列必须是数值型）
MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值
MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值
如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL为缺省值），则表示不取消重复值。

例25

查询学生总人数

SELECT COUNT(*)
FROM Student;

例26

查询选修了课程的学生人数

SELECT COUNT(DISTINCT Sno)

FROM SC;

详细信息…

学生每选修一门课，在SC中都有一条相应的记录，而一个学生一般都要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。

例27

计算1号课程的学生平均成绩

SELECT AVG(Grade)
FROM SC WHERE Cno='1';

例28

查询学习1号课程的学生最高分数

SELECT MAX(Grade)
FROM SC WHERE Cno='1';

五、

对查询结果分组

GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。　　
对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。

例29

查询各个课程号与相应的选课人数

SELECT Cno, COUNT(Sno)

FROM SC

GROUP BY Cno;

详细信息…

该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。查询结果为：
Cno　 COUNT(Sno)
------ ----------
1       22
2       34
3       44
4       33
5       48

如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。

例30

查询信息系选修了3门以上课程的学生的学号

SELECT Sno

FROM SC

WHERE Sdept='IS;

GROUP BY Sno

HAVING COUNT(*)>3;

详细信息…

查选修课程超过3门的信息系学生的学号，首先需要通过WHERE子句从基本表中求出信息系的学生。然后求其中每个学生选修了几门课，为此需要用GROUP BY子句按Sno进行分组，再用集函数COUNT对每一组计数。如果某一组的元组数目大于3，则表示此学生选修的课超过3门，应将他的学生号选出来。HAVING短语指定选择组的条件，只有满足条件（即元组个数>3）的组才会被选出来。　　

WHERE子句与HAVING短语的根本区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

[收藏] [推荐] [评论] [打印] [关闭]

顶一下

最新评论共有 0 位网友发表了评论

查看所有评论

发表评论