SQL集锦.docVIP

select cno,count(sno) from sc t group by cno having count(sno)1 select sno from sc t group by sno having count(*)1 select student.*,sc.* from student,sc where student.sno=sc.sno 若在等值连接中把目标列中重复的属性列去掉则为自然连接 select student.sno, sname, ssex, sage, sdept, cno, grade from student, sc where student.sno = sc.sno 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为自身连接。 查询一门课的间接先修课（即先修课的先修课） 在Course表中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，找到它的先修课程。这就要讲Course表与其自身连接。 为此，要为Course表取两个别名，一个是first，一个是second select first.cno, second.cpno from course first, course second where first.cpno = second.cno; select student.sno, sname, ssex, sage, sdept, cno, grade from student left join sc on (student.sno = sc.sno); 复合条件连接：查询选修2号课程的且成绩在60分以上的所有学生 select student.sno, sname from student, sc where student.sno = sc.sno and sc.cno = 2 and sc.grade 60; 连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。 查询每个学生的学号、姓名、选修的课程名及成绩。 select student.sno, sname, cname, grade from student, sc, course where student.sno = sc.sno and sc.cno = course.cno 嵌套查询 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 sno, sname, sdept from student where sdept in (CS) 本例中的查询也可以用自身连接来完成： select s1.sno, s1.sname, s1.sdept from student s1, student s2 where s1.sdept = s2.sdept and s2.sname = 刘晨select sno, sname from student where sno in (select sno from sc where cno in (select cno from course where cname = 信息系统));select student.sno, sname from student, sc, course where student.sno = sc.sno and sc.cno = course.cno and course.cname = 信息系统;select sno, cno from sc x where grade =(select avg(grade) from sc y where y.sno = x.sno) 带有any（some）或all谓词的子查询 子查询返回单值时可以用比较运算符，但返回多值时要用any（有的系统用some）或all谓词修饰符。而是用any或all谓词时则必须同时使用比较运算符。其语义为： select sname, sage from student where sage any (select sage from student wher