数据库学习总结.docx

1、数据库学习总结 学习总结聚集函数：程序一：select cno,count(Sno)/*count(*)或者count(cno),count(grade)等都是一样的结果，可以思考下就明白了*/from scgroup by cno连接：程序一：select */*select student.*,sc.*/from student,scwhere student.sno=sc.sno/*where sc.sno=student.sno结果是一样的*/如果是from sc, student则sc表的东西会在前面如果把上面的作为自然连接，只要把列名依次列出来，不列举同名的而已，笑尿了。麻烦死了，

2、为了省去少列举一列程序一：select Student.sno,sname,gradefrom student,scwhere student.sno=sc.sno and o=2 and grade 80程序二：select student.sno,sname,gradefrom student,( select * from sc where cno=2 and grade 80) cou/*把这个查询结果命名为cou表，然后进行连接查询*/where student.sno = cou.sno程序三：select sno,snamefrom studentwhere sno in( se

3、lect sc.sno from sc where cno=2 and grade 80)这个怎么改也达不到上面的效果Like的用法问题：程序一：/*select Sdeptfrom studentwhere Sname like 刘晨;*/select Sdeptfrom studentwhere Sname = 刘晨;Like可能还是与字符集有关，若改成sname like 刘%可以，若改成sname like 刘_不行自身连接中的一个问题：对于这样一个表，用自连接解决：查询与“刘晨”在同一个系学习的学生正确的做法是为何这样写就会是这样的结果以下是论证过程，看完，体会后就懂得两张表连接的过

4、程是第一张表的第一行与第二张第一行比较，接着第一张第二行与第二张第一行进行比较，依次，直到第一张表比较完，才转到第二张第二行比较查询所有选修了2号课程的学生姓名程序一：select Snamefrom Studentwhere exists( select * from Sc where Sc.Sno= Student.Sno and Cno=2)程序二：select Snamefrom studentwhere Sno in( select Sno from SC where Cno = 2)程序三：select Snamefrom Student,Scwhere Student.Sno =

5、 Sc.Sno and Sc.Cno=2程序四：select distinct Sname/*可以把distinct去掉，想想为何每个重复的是5个*/from Student,Scwhere Student.Sno in( select Sno from Sc where Cno=2)查询没有选修2号课程的学生姓名程序一：select Snamefrom studentwhere Sno in( select Sno from sc where Cno != 2)/*运行结果与上一题相同，经思考明白，问题来了，然后怎么办呢？*/程序二：select Snamefrom studentwhere

6、 Sno not in( select Sno from sc where Cno = 2)程序三：select Snamefrom Studentwhere not exists( select * from Sc where Sc.Sno= Student.Sno and Cno=2)程序四：select distinct Sname/*可以把distinct去掉，想想为何每个重复的是个*/from Student,Scwhere Student.Sno not in( select Sno from Sc where Cno=2)程序五：另一个连接没法写，如果仿照上题的程序三。因为SC表

7、的原因select *from Student,Scwhere not(Student.Sno = Sc.Sno and Sc.Cno=2)这样写也不对，可以想想例3.62查询选修了全部课程的学生姓名程序一：select Snamefrom Studentwhere not exists( select * from Sc where not exists ( select * from sc where Student.Sno = Sc.Sno ) 结果是写成了查询选课的学生。而不是全部课程正确程序：select Snamefrom Studentwhere not exists( sele

8、ct * from Course where not exists ( select * from sc where Course.Cno=Sc.Cno and Student.Sno = Sc.Sno ) 例3.63查询至少选修了学生95002选修的全部课程的学生号码程序一：最初的程序是这样的select x3.Snofrom Sc x3where not exists( select * from Sc x1 where not exists ( select * from Sc x2 where x2.Sno = 95002 and x1.Cno=X2.Cno )/*查询一个学生，没有没

9、选95002选的课，*/而实际结果是，该程序段：select * from Sc x1 where exists ( select * from Sc x2 where x2.Sno = 95002 and x1.Cno=X2.Cno )查询结果是：95001 2 8595001 3 8895002 2 9095002 3 8095004 3 76(95004为增加的测试数据)翻译过来，其实是 查询一个选了95002选的课(不是全部)所以程序段： select * from Sc x1 where not exists ( select * from Sc x2 where x2.Sno =

10、95002 and x1.Cno=X2.Cno )结果不对：95001 1 92程序二：/*书上例程*/select distinct Snofrom Sc xwhere not exists( select * from Sc y where y.Sno = 95002 and not exists ( select * from Sc z where z.Sno = x.Sno and z.Cno = y.Cno )程序三：/*借助于例3.62的方法*/select distinct x1.Snofrom Sc x1where not exists( select * from ( select Cno from Sc where Sno=95002 ) temp where not exists ( select * from Sc x2 where x2.Sno = X1.Sno and x2.Cno = temp.Cno )这题真的很头疼。需要记啊 By YueTengFei13计科卓越二班