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 |
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计算,以求得改组的学生人数
结果为:
查询选修了3门以上课程的学生学号
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 |
B | E |
b1 | 3 |
b2 | 7 |
b3 | 10 |
b3 | 2 |
b5 | 2 |
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 |
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 |
A | B | C | E |
a1 | b1 | 5 | 3 |
a1 | b2 | 6 | 7 |
a2 | b3 | 8 | 10 |
a2 | b3 | 8 | 2 |
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 |
A | B | C | E |
a1 | b1 | 5 | 3 |
a1 | b2 | 6 | 7 |
a2 | b3 | 8 | 10 |
a2 | b3 | 8 | 2 |
a2 | b4 | 12 | NULL |
A | B | C | E |
a1 | b1 | 5 | 3 |
a1 | b2 | 6 | 7 |
a2 | b3 | 8 | 10 |
a2 | b3 | 8 | 2 |
NULL | b5 | NUll | 2 |
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 |
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子句中可以有多个连接条件,称为
复合条件连接。
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');