数据查询
select distinct cno from sc //distinct只能出现一次
where子句常用的查询条件
1.通配符
% 表示任意长度 a%b 如abc,adjfhjb
_表示任意单个字符 a_b 如abb,acb
escape‘<换码字符>’
2.like, not like 等关键字进行字符串匹配
3.多重条件查询:AND优先级高于OR,可以用括号改变优先级
4.例1:
例2:
例3:
例4:
例5:查询每门课的间接先修课(即先修课的先修课)
select FIRST.Cno,SECOND.Cpno
from Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;
例6:查询选修2号课程且成绩在90分以上的所有学生
select Student.Sno,Sname
from Student,SC
whereStudent.Sno=SC.Sno
AND SC.Cno='2'AND SC.Grade>90;
例7:
select Sname from Student where Sno in
(select Sno from sc where Cno='2')
例8:找到每个学生超出他选修课程平均成绩的课程号
select sno,cno from SC x
WHERE Grade>=
(SELECT AVE(Grade)FROM SC Y)
from SC y where y.Sno=x.Sno)
例9:查询所有选修了1号课程的学生姓名
select Sname FROM Student
WHERE EXISTS (SELECT * from SC
where Sno=Student.Sno AND Cno='1')
5.集合查询–union
查询选修了课程1或者选修了课程2的学生
select Sno from SC where Cno='1'
union select Sno from SC where Cno='2'
6.集合查询–intersect
查询计算机科学系的学生与年龄不大于19岁的学生的交集
select * from Student where Stept='CS'
intersect select * from student where Sage<=19
7.集合查询–minus
查询计算机学院的学生与年龄不大于19岁的学生的差集
select * from student where stept='CS'
minus select * from student where sage<=19