数据查询
语句格式
SELECT [ALL|DISTINCT] <目标列表达式>
[,<目标列表达式>] …
FROM <表名或视图名>[, <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
消除取值重复的行
如果没有指定DISTINCT关键词,则缺省为ALL
查询满足条件的元组
常用的查询条件
查 询 条 件 | 谓 词 |
比 较 | =,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符 |
确定范围 | BETWEEN AND,NOT BETWEEN AND |
确定集合 | IN,NOT IN |
字符匹配 | LIKE,NOT LIKE |
空 值 | IS NULL,IS NOT NULL |
多重条件(逻辑运算) | AND,OR,NOT |
(1) 比较大小
查询计算机科学系全体学生的名单。
SELECT Sname
FROM Student
WHERE Sdept=‘CS’;
(2)确定范围
查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
(3) 确定集合
IN <值表>, NOT IN <值表>
[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,Ssex
FROM Student
WHERESdept IN ( 'IS','MA','CS' );
[例13]查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN ( 'IS','MA','CS' );
(4)字符匹配
[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
1) 匹配串为固定字符串
查询学号为200215121的学生的详细情况。
SELECT *
FROM Student
WHERE Sno LIKE ‘200215121';
等价于:
SELECT *
FROM Student
WHERE Sno = ' 200215121 ';
匹配串为含通配符的字符串
[例15] 查询所有姓刘学生的姓名、学号和性别。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE ‘刘%’;
[例16] 查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__';
查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE ‘__阳%’;
查询所有不姓刘的学生姓名。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';
使用换码字符将通配符转义为普通字符
[例19] 查询DB_Design课程的课程号和学分。
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\‘;
[例20] 查询以"DB_"开头,且倒数第3个字符为 i的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i_ _' ESCAPE ' \ ‘;
ESCAPE'\' 表示“ \” 为换码字符
(5) 涉及空值的查询
IS NULL 或 IS NOT NULL
“IS” 不能用“=” 代替
某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL
查所有有成绩的学生学号和课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
(6) 多重条件查询
逻辑运算符:AND和 OR来联结多个查询条件
AND的优先级高于OR
可以用括号改变优先级
可用来实现多种其他谓词
[NOT] IN
[NOT] BETWEEN … AND …
查询计算机系年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept= 'CS' AND Sage<20;
查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ( 'IS','MA','CS' )
可改写为:
SELECT Sname,Ssex
FROM Student
WHERE Sdept= ' IS ' OR Sdept= ' MA' OR Sdept= ' CS ';
ORDER BY子句
可以按一个或多个属性列排序
升序:ASC;降序:DESC;缺省值为升序
当排序列含空值时
ASC:排序列为空值的元组最后显示
DESC:排序列为空值的元组最先显示
查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECT Sno,Grade
FROM SC
WHERE Cno= ' 3 '
ORDER BY Grade DESC;
查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;
聚集函数
计数
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 ';
GROUP BY子句
求各个课程号及相应的选课人数。
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
查询结果:
Cno COUNT(Sno)
1 22
2 34
3 44
4 33
5 48
HAVING短语与WHERE子句的区别:
§ 作用对象不同
§ WHERE子句作用于基表或视图,从中选择满足条件的元组
§ HAVING短语作用于组,从中选择满足条件的组。
一、等值与非等值连接查询
v 等值连接:连接运算符为=
[例33] 查询每个学生及其选修课程的情况
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno = SC.Sno;
Student.Sno | Sname | Ssex | Sage | Sdept | SC.Sno | Cno | Grade |
200215121 | 李勇 | 男 | 20 | CS | 200215121 | 1 | 92 |
200215121 | 李勇 | 男 | 20 | CS | 200215121 | 2 | 85 |
200215121 | 李勇 | 男 | 20 | CS | 200215121 | 3 | 88 |
200215122 | 刘晨 | 女 | 19 | CS | 200215122 | 2 | 90 |
200215122 | 刘晨 | 女 | 19 | CS | 200215122 | 3 | 80 |
复合条件连接
WHERE子句中含多个连接条件
[例37]查询选修2号课程且成绩在90分以上的所有学生
SELECTStudent.Sno, Sname
FROM Student, SC
WHEREStudent.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;
嵌套查询
一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
SELECT Sname /*外层查询/父查询*/
FROM Student
WHERE Sno IN
(SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno= ' 2 ');
带有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 = '刘晨';
查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno,Sname ③ 最后在Student关系中取出Sno和Sname
FROM Student
WHERE Sno IN
(SELECT Sno ② 然后在SC关系中找出选修了3号课程的学生学号
FROM SC
WHERE Cno IN
(SELECT Cno ① 首先在Course关系中找出
FROM Course “信息系统”的课程号,为3号
WHERE Cname= ‘信息系统’
)
);
集合查询
查询计算机科学系的学生及年龄不大于19岁的学生。
方法一:
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
UNION:将多个查询结果合并起来时,系统自动去掉重复元组。
UNION ALL:将多个查询结果合并起来时,保留重复元组