（三）数据库查询

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表的式>]……
FROM <表名或视图名>[,<表明或视图名>]……
[WHERE <条件表达式>]
[GROUP BY<列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]];

一、单表查询

1.选择表中的若干列

——查询指定列

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

SELECT Sno ,Sname
FROM Student;

 ——查询全部列

查询全体学生的详细记录

SELECT * 
FROM Student;

等价于

SELECT Sno,Sname,Ssex,Sage,Sdept   /*学生全部属性*/
FROM Student;

——查询经过计算的值

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

SELECT Sname ,2017-Sage
FROM Student;

输出的结果为

Sname	2017-Sage
李勇	1984
刘晨	1985
王明	1995
张立	1988

可以发现第2列不是列名而是一个表达式，使用当年的年份减去学生的年龄。

若想把列表中第二列列名显示为“Birthday"可以用如下方法：

SELECT Sname ,2017-Sage Birthday
FROM Student;

输出的结果为

Sname	Birthday
李勇	1984
刘晨	1985
王明	1995
张立	1988

相当于给2017-Sage起了一个别名。

2、选择表中的若干元组

——消除取值重复的行

查询选修了课程的学生学号

SELECT Sno
FROM SC;        /*选课表*/

输出结果为：

Sno

14042401

14042401

14042402

14042403

14042403

该查询结果里包含了多个重复的行，若想去掉这些重复行必须指定关键词DISTINCT：

SELECT DISTINCT Sno
FROM SC;        /*选课表*/

输出结果为：

Sno

14042401

14042402

14024203

——查询满足条件的元组

1.大小比较

查询计算机科学系的全体学生的姓名

SELECT Sname
FROM Student
WHERE Sdept='CS';

2.确定范围

查询年龄在20-30岁之间的学生姓名

SELECT Sname
FROM Student
WHERE Sage BETWEEN 20 AND 23;

查询年龄不在20-30岁之间的学生姓名

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

3.确定集合

查询计算机科学系、数学系和信息系学生的姓名和性别

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

4.字符匹配

谓词LIKE可以用来进行字符串的匹配，其一般语法为：

[NOT ]LIKE '<匹配串>'[ESCAPE'<换码字符>']

其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以是含有通配符%和_。

.%(百分号)代表任意长度的字符串

._(下划线)代表任意单个字符

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

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

注意：数据库中字符集为ASCII时一个汉字需要两个_，当字符集为GBK时只需要一个_。

5.涉及空值的查询

某些学生选修了课程后没有参加考试，所以有选课记录，但是没有考试成绩。查询缺少成绩的学生的学号和相应的课程号，

SELECT Sno ,Cno
FROM SC
WHERE Grade IS NULL;

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

6.多重条件查询

查询计算机系年龄在20岁以下的学生姓名

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

3、ORDER BY 子句

可以用ORDER BY 子句对查询结果按照一个或多个属性列的升序（ASC)或降序(DESC)排序，缺省值时为升序

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

SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;

4、聚集函数

SQL提供了许多聚集函数，主要有：

COUNT([DISTINCT|ALL] *)         /*统计元组个数*/
COUNT([DISTINCT|ALL] <列名>)	/*统计一列中值个数*/
SUM([DISTINCT|ALL] <列名>)	/*计算一列值的总和（此列必须为数值型）*/
AVG([DISTINCT|ALL] <列名>)	/*计算一列值的平均值（此列必须为数值型）*/
MAX([DISTINCT|ALL] <列名>)	/*求一列值中的最大值*/
MIN([DISTINCT|ALL] <列名>)	/*求一列值中的最小值*/

查询学生总数

SELECT COUNT(*)
FROM Student;

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

SELECT COUNT(DISTINCT Sno)
FROM SC;

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

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

5、GROUP BY 子句

GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。

求各个课程及相应的选课人数

SELECT Cno,COUNT(Cno)
FROM SC
GROUP BY Cno;

该语句对查询结果按Cno的值分组，所有具有相同Cno值得元组为一组，然后对每一组作用聚集函数COUNT计算，以求得改组的学生人数

结果为：

Cno	COUNT(Sno)
1	22
2	23
3	34
4	48

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

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;

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

二、连接查询

A	B	C
a1	b1	5
a1	b2	6
a2	b3	8
a2	b4	12

                    （a）关系R

B	E
b1	3
b2	7
b3	10
b3	2
b5	2

                      （b）关系

A	R.B	C	S.B	E
a1	b1	5	b2	7
a1	b1	5	b2	10
a1	b2	6	b2	7
a1	b2	6	b3	10
a2	b3	8	b3	10

                   (c）一般连接

A	R.B	C	S.B	E
a1	b1	5	b1	3
a1	b2	6	b2	7
a2	b3	8	b3	10
a2	b3	8	b3	2

                    （d)等值连接

A	B	C	E
a1	b1	5	3
a1	b2	6	7
a2	b3	8	10
a2	b3	8	2

                  (e)自然连接

A	B	C	E
a1	b1	5	3
a1	b2	6	7
a2	b3	8	10
a2	b3	8	2
a2	b4	12	NULL
NULL	NULL	NULL	2

                          (f)外连接

A	B	C	E
a1	b1	5	3
a1	b2	6	7
a2	b3	8	10
a2	b3	8	2
a2	b4	12	NULL

                        (g)左外连接

A	B	C	E
a1	b1	5	3
a1	b2	6	7
a2	b3	8	10
a2	b3	8	2
NULL	b5	NUll	2

                      （h)右外连接

1、等值与非等值连接查询

连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：

[<表名1>.]<列名1><比较运算符>[<表名2>/]<列名2>

其中比较运算符主要有：=、>、<、>=、<=、!= 等。

此外连接谓词还可以使用下面形式：

[<表名1>.]<列名1>BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

当连接运算符为=时，称为 等值连接。使用其它运算符称为 非等值连接。

查询每个学生及其选修课程的情况。

SELECT Student.* ,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;

查询结果如下：

Student.Sno	Sname	Ssex	Sage	Sdept	SC.Sno	Cno	Grade
14042401	李晨	男	20	CS	14042401	1	92
14042402	李晨	男	20	CS	14042401	2	87
14042401	李晨	男	20	CS	14042401	3	89
14042402	李勇	男	21	CS	14042402	2	80

DBMS执行该连接操作的可能过程是：

        首先在表Student中找到第一个元组，然后从头开始扫描SC表，逐一查找与Student第一个元组的Sno相等的SC元组，找到后就将Student中的第一个元组与该元组拼接起来，形成结果表中的一个元组。SC全部查找完后，再找Student中第二个元组，然后再从头开始扫描SC,逐一查找满足连接条件的元组，找到后就将Student中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直至Student中的全部元组都处理完毕为止。

若把等值连接中把目标列中重复的属性列去掉则为 自然连接 。

 对上一例子用自然连接完成

  

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

例中，由于Sno,Sname,Ssex,Sage,Sdept,Cno和Grade属性列在Student和SC表中是唯一的，因此引用是可以去掉表明前缀。而Sno在两个表都出现了，因此引用是必须加上表明前缀。

2、自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为 表的自身连接。

查询每门课的间接先修课。

               FIRST表

Cno	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	4
5	数据结构	7	4
6	数据处理		2
7	PASCAL	6	4

          SECOND 表

Cno	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	4
5	数据结构	7	4
6	数据处理		2
7	PASCAL	6	4

SELECT FIRST.Cno,SECOND.Cpno
WHERE Course FIRST ,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

结果为：

Cno	Pcno
1	7
3	5
5	6

3、外连接

如果把舍弃的元组保存在结果关系中，而在其他属性上填空值（NULL),那么这种连接就叫做外连接（outer jion);

如果只把左边关系中要舍弃的元组保存就叫做左外连接（LEFT OUTER JION 或LEFT JION)；

如果只把右边关系中要舍弃的元组保存就叫做右外连接 （RIGHT OUTER JION 或RIGHT JION)。

SELECT Student.Sno,Sname,Ssex,Sdept,Cno,Grade
FROM Student LEFT OUT JOIN ON(Student.Sno=SC.Sno);
/*也可以使用USING来去掉结果中的重复值：
FROM Student LEFT OUT JOIN SC USING (Sno);*/

4、复合条件连接

 WHERE子句中可以有多个连接条件，称为 复合条件连接。

   查询选修2号课程且成绩在90分以上的所有学生的学号和名字。

SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

多表连接

查询每个学生的学号、姓名、选修课程名及成绩

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

三、嵌套查询

1、带有IN谓词的子查询

查询与“刘晨”在同一个系学习的学生

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
             (SELECT Sdept
              FROM Student
              WHERE Sname='刘晨‘）

本题也可以用自身连接完成

SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname=’刘晨‘

2、带有比较运算符的子查询

找出每个学生超过他选修课程平均成绩的课程号。

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);

3、带有ANY(SOME）或ALL谓词的子查询

查询其它系中比计算机科学系某一学生年龄小的学生姓名和年龄。

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
               FROM Student
               WHERE Sdept='CS')
AND Sdept!='CS';         	/*注意这是父查询块中的条件*/

DSMS执行此查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合A，然后处理父查询，找所有不是CS系且年龄小于A中某个值的学生。

本例也可以用聚集函数来实现

SELECT Sname,Sage
FROM Student
WHERE Sage<
          (WHERE MAX(Sage)
           FROM Student
           WHERE Sdept='CS')
AND Sdept<>'CS';

查询其它系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

SELECT Sname,Sage
FROM Student
WHERE Sage<ALL
           (SELECT Sage
 	    FROM Student
            WHERE Sdept='CS')
AND Sdept<>'CS'; 

4、带有EXISTS谓词的子查询

EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true"或逻辑假值"false"。

查询所有选修了1号课程的学生姓名。

SELECT Sname,Sage
FROM Student
WHERE EXISTS
      (SELECT *
       FROM SC
       WHERE Sno=Student.Sno AND Cno='1');

由EXISTS引出的子查询，其目标列表达式通常都用*,因为带有EXISTS的子查询只返回真值或假值，给出列明无实际意义。

四、集合查询

查询计算机科学系的学生及年龄不大于19岁的学生

SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;

 本查询实际上是求计算机科学系的所有学生与年龄不大于19岁的学生的并集。使用UNION将多个查询结果合并起来时，系统就会自动去掉重复元组。如果要保留重复元组则用UNION ALL操作符。

查询即选修了课程1又选修了课程2的学生。就是查询选修课程1的学生集合与选修课程2的学生集合的交集。

SELECT Sno
FROM SC
WHERE Cno='1'
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='2';

本例也可以表示为：

SELECT Sno
FROM SC
WHERE Cno='1' AND Sno IN
			(SELECT Sno
			FROM SC
			WHERE Cno='2');