《sql 数据库上机实验三》.doc

实目的： 掌握数据查询操作。 内容：1）2) 对各表中的数据进行不同条件的查询； 附录：语句清单 --sql 查询实验三代码 --------1 select student.*,sc.cno,sc.grade ----左外连接(显性连接) from student left join sc on student.sno=sc.sno /* select student.*,sc.cno,sc.grade from student inner join sc ----内连接法（显性连接） on student.sno=sc.sno */ /* select student.*,sc.cno,sc.grade ----------隐性连接 from student,sc where student.sno=sc.sno */ select first.cname,first.cpno,second.cno from course first,course second ----------2 where first.cpno=second.cno select student.*,sc.* ---------------3 from student right join sc on student.sno=sc.sno select student.sno,sname from student,sc,(select student.sno from student,sc -----4 导出表的使用 where sc.sno=student.sno and cno=2 group by student.sno)as result(sno) where student.sno=sc.sno and cno=3 group by student.sno,student.sname select student.sno,sname from student,sc as x --------4法二 where student.sno=x.sno and cno=2 and exists(select student.sno from student,sc as y where cno=3 and exists(select * from student,sc as z where z.sno=y.sno and z.sno=x.sno)) select student.sno,sname from student,sc as x,sc as y ------4法三 where student.sno=x.sno and x.sno=y.sno and x.cno=2 and y.cno=3 select * from student as x,student y --------5 where x.sname=刘晨 and x.sage=y.sage select sname,sage from student,sc,course -----------6 where student.sno=sc.sno and sc.cno=course.cno and course.cname=数据库 select sname from student where sageany(select sage from student ----------7 where sdept=is ) and sdept is select sname from student where sageall(select sage ------8 from student where sdept=is) and sdept is select sname from student ------------9 where sno in(select sno from sc group by sno having COUNT(*)=7) select * --------------10 from stu