|
1.常用的比较运算符 2.算术比较操作 (1)算术比较运算符 <,<=,>,>=,=,!= BETWEEN…AND… (2)查询学习了课程C2的学生 SELECT * FROM Sc WHERE Cno='C2' (3)查询年龄大于19的学生 SELECT * FROM Student WHERE Age>19 SELECT * FROM Student WHERE NOT Age<=19 (4)查询课程号为C2且成绩高于85分以上的学生 SELECT * FROM Sc WHERE Cno='C2' AND G>85 (5)查询选修C1或C2且不低于70分的学生 SELECT * FROM Sc WHERE (Cno='C1' OR Cno='C2') AND G>=70 (6)查询成绩在60至75之间的学生 SELECT * FROM Sc WHERE G>=60 AND G<=75 SELECT * FROM Sc WHERE G BETWEEN 60 AND 75 |
3.字符串的匹配操作 (1)两个通配符 百分号(%):与零个或多个字符组成的字符串匹配 下划线(_):与单个字符匹配 (2)查询计算机系的学生 SELECT * FROM Student WHERE Dept = 'CS' SELECT * FROM Student WHERE Dept LIKE 'CS' (3)查询不以P开头的课程 SELECT * FROM Course WHERE Cn NOT LIKE 'P%' (4)查询第二个字符为o的课程 SELECT * FROM Course WHERE Cn LIKE '_o%' (5)查询姓赵的学生(汉字?) SELECT * FROM Student WHERE Sn LIKE '赵%' SELECT * FROM Student WHERE Sn LIKE '赵_' (6)转义字符 为了使字符串中包含特殊字符(即%和_),SQL允许定义转义字符。转义字符紧靠特殊字符并放在它前面,表示该特殊字符将被当成普通字符 在LIKE比较中使用ESCAPE关键字来定义转义符 LIKE 'ab\%cd%' ESCAPE '\' 匹配所有以“ab%cd”开头的字符串 LIKE 'ab\\cd%' ESCAPE '\' 匹配所有以“ad\cd”开头的字符串 | |
4.空值的比较操作 (1)说明 SQL中允许列值为空,空值用保留字NULL表示 用IS NULL测试列值是否为空值 如果要测试非空值,可用短语IS NOT NULL 空值的存在增加了算术操作和比较操作的复杂性。SQL中规定,涉及到+、-、*、/的算术表达式中有一个值是空值时,表达式的值也是空值。涉及到空值的比较操作的结果认为是false 在聚合函数中遇到空值时,除了COUNT(*)外,都跳过空值而去处理非空值 (2)查询没填的课程名 SELECT * FROM Course WHERE Cn IS NULL SELECT * FROM Course WHERE LEN(ALLTRIM(Cn))=0 |
5.集合成员资格的比较 (1)格式 <元组> [NOT] IN(<集合>) (2)说明 判断元组是否在子查询的结果(即集合)中的操作,称为“集合成员资格比较” 这里的集合可以是一个SELECT查询语句,或者是元组的集合,但其结构应与前面元组的结构相同 IN操作符表示:如果元组在集合内,那么其逻辑值为TRUE,否则为FALSE (3)查询选修了课程C1、C2或C3的学号(下面是2种等价方法) SELECT DISTINCT Sno FROM Sc WHERE Cno='C1' OR Cno='C2' OR Cno='C3' | SELECT DISTINCT Sno FROM Sc WHERE Cno IN ('C1', 'C2', 'C3') | (4)查询既不是计算机系,也不是数学系的学生 SELECT * FROM Student WHERE Dept NOT IN ('CS','MA') SELECT * FROM Student WHERE NOT Dept IN ('CS','MA') (5)查询没有选修C2课程的学生姓名 SELECT Sname FROM Student WHERE Sno NOT IN (SELECT Sno FROM Sc WHERE Cno='C2') (6)查询每门课程的成绩均在80分以上的姓名 SELECT Sname FROM Student WHERE Sno NOT In (SELECT * FROM Sc WHERE Grade<80) |
6.集合成员的算术比较 (1)形式 <元组> θ ALL | SOME | ANY(<集合>) (2)说明 这里要求“元组”与集合中“元组”的结构一致 θ是算术比较运算符,“θ ALL”操作表示左边那个元组与右边集合中每一个元组满足θ运算,“θ SOME”操作表示左边那个元组与右边集合中至少一个元组满足θ运算。ANY和SOME是同义词,早期的SQL标准用ANY,为避免与英语中ANY意思混淆,后来的标准都改为SOME (3)查询选修了C2的学生姓名(每段代码最后是查询结果:学生的编号集合) SELECT Sname FROM Student,Sc WHERE Student.Sno=Sc.Sno AND Cno='C2' 查询结果{16345} | SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM Sc WHERE Cno='C2') 查询结果{13456} | SELECT Sname FROM Student WHERE Sno=ANY (SELECT Sno FROM Sc WHERE Cno='C2') 查询结果{13456} | SELECT Sname FROM Student WHERE Sno=SOME (SELECT Sno FROM Sc WHERE Cno='C2') 查询结果{13456} | (4)查询选修了C2的学生中成绩最高的学号(除错误的2种方法外,其余2种为正确的等价方法) SELECT Sno,MAX(Grade) FROM Sc WHERE Sno IN (SELECT Sno FROM Sc WHERE Cno='C2') | | 错误原因:“该语句语义为:选C2的学生,在所有课程中获得最高分数的学生” | | SELECT Sno FROM Sc WHERE Cno='C2' AND Grade >= (SELECT MAX(Grade) FROM Sc WHERE Cno='C2') | | | SELECT Sno FROM Sc WHERE Cno='C2' AND Grade>= ALL (SELECT Grade FROM Sc WHERE Cno='C2') | | | SELECT X.Sno,MAX(X.Grade) FROM Sc X,Sc Y WHERE X.Sno=Y.Sno AND Y.Cno='C2' | | 错误原因:“该语句语义为:所有选课的学生,在所有课程中获得最高分数的学生” | | (5)查询没有选修C6课程的学生姓名(下面是2种等价方法) SELECT Sname FROM Student WHERE Sno NOT in (SELECT Sno FROM Sc WHERE Cno='C6') | SELECT Sname FROM Student WHERE Sno != all (SELECT Sno FROM Sc WHERE Cno='C6') | (6)检索平均成绩最高的学生学号 SELECT Sno FROM SC GROUP BY Sno HAVING AVG(Grade) >= ALL (SELECT AVG(Grade) FROM SC GROUP BY Sno) | |
7.集合空否的测试 (1)形式 [NOT] EXISTS (<集合>) (2)说明 不带NOT的操作,当集合非空时(即至少存在一个元组),其逻辑值为true,否则为false 带NOT的操作,当集合为空时,其值为true,否则为false (3)查询选修了C6的学生姓名(相关子查询)(除了第4个,其余是等价方法) SELECT Sname FROM Student,Sc WHERE Student.Sno=Sc.Sno AND Cno='C6' | SELECT Sname FROM Student WHERE Sno=ANY (SELECT Sno FROM Sc WHERE Cno='C6') | SELECT Sname FROM Student WHERE Sno in (SELECT Sno FROM Sc WHERE Cno='C6') | SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM Sc WHERE Cno='C6') | | 错误原因:“该语句语义为:所有选课的学生” | | SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM Sc WHERE Cno='C6' AND Sno=Student.Sno) | (4)查询没有选修C6课程的学生姓名(下面是3种等价方法) SELECT Sname FROM Student WHERE Sno NOT in (SELECT Sno FROM Sc WHERE Cno='C6') | SELECT Sname FROM Student WHERE Sno!=all (SELECT Sno FROM Sc WHERE Cno='C6') | SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Sc WHERE Cno='C6' AND Sno=Student.Sno) | (5)查询所有学生都选修的课程名称(查询这样的课程名称,这门课程是没有一个学生不选的) SELECT Cname FROM Course WHERE NOT EXISTS (SELECT * FROM Student WHERE NOT EXISTS (SELECT * FROM Sc WHERE Student.Sno=Sc.Sno And Sc.Cno=Course.Cno)) | (6)查询至少选修了与S4学生选修的全部课程相同的姓名 SELECT sname FROM student WHERE NOT EXISTS (SELECT * FROM sc scx WHERE Scx.Sno='S4' and NOT EXISTS (SELECT * FROM Sc Scy WHERE Scy.Sno=Student.Sno and Scy.Cno =Scx.Cno)) | (7)查询至少选修了学生S4选修的全部课程的学号(不存在这样的课程,学生S4选修了,而其他学生没有选) SELECT Distinct Sno FROM Sc A WHERE NOT EXISTS (SELECT * FROM Sc B WHERE B.Sno='S4' And NOT EXISTS (SELECT * FROM Sc C WHERE C.Sno=A.Sno And C.Cno=B.Cno)) | |
8.集合中重复元组存在否的测试 (1)形式 [NOT] UNIQUE(<集合>) (2)说明 不带NOT的操作,当集合中不存在重复元组时,其逻辑值为true,否则为false 带NOT的操作,当集合中存在重复元组时,其逻辑值为true,否则为false (3)在基本表T和C中检索只开设了一门课程的教师工号和姓名 SELECT Tno, Tname FROM Teacher WHERE UNIQUE (SELECT Tno FROM Course WHERE Course.Tno = Teacher.Tno) | |