/*查询数据库课程成绩比“张凯迪”高的同学姓名*/
多表连接查询 正解:思路:五张表(由Student,Sc,Course连接的)依次拼在一起(两张大表),一张里面是所有同学的数据库成绩,一张表里是张凯迪的数据库成绩,进行比较大小
select S1.Sname from Student S1,Student S2,Course,Sc Sc1,Sc Sc2 where S1.S#=Sc1.S# and Sc1.C#=Course.C# and Sc2.C#=Course.C# and S2.S#=Sc2.S#and Cname='数据库原理与应用'and S2.Sname='张凯迪'and Sc1.Grade>Sc2.Grade
正解:非相关子查询:
思路:内部查询出张凯迪同学的成绩,传到外部查询进行比较
select S1.Sname from Student S1,Sc,Course where S1.S#=Sc.S# and Sc.C#=Course.C# and Cname='数据库原理与应用' and Grade>(select Grade from Student S1,Sc,Course where Sc.S#=S1.S# and Sc.C#=Course.C# and S1.Sname='张凯迪' and Cname='数据库原理与应用' )
正解:相关子查询:
外部限定内部课程为‘数据库’,语句变简洁,但是操作复杂,多次循环
select S1.Sname from Student S1,SC,Course where S1.S#=SC.S# and SC.C#=Course.C# and Cname='数据库原理与应用' and Grade>(select Grade from SC,Student where Student.S#=SC.S# and SC.C#=Course.C# and Student.Sname='张凯迪')
------------------------------------------------------ 错误:
select S1.Sname from Student S1,SC,Course where S1.S#=SC.S# and SC.C#=Course.C# and Cname='数据库原理与应用' and Grade>(select Grade from SC,Student where SC.C#=Course.C# and Student.Sname='张凯迪 ')
忘记写表的连接条件,导致表做笛卡尔积,张凯迪的数据库课成绩有多个
误区:--子查询返回的值不止一个。当子查询跟随在比较运算符 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。--Some/all()
正解:将所有的同学的数据库成绩抽出来,再将张凯迪同学的数据库成绩拿出来,两张表做不等值连接
select m1.Sname (注意:不是S1.Sname,也不是Sname) from (select S1.Sname ,Grade from Student S1 ,Sc,Course where S1.S#=SC.S# and SC.C#=Course.C# and Course.Cname='数据库原理与应用' ) as m1,(select S1.Sname ,Grade from Student S1 ,Sc,Course where S1.S#=SC.S# and SC.C#=Course.C# and S1.Sname='张凯迪'and Course.Cname='数据库原理与应用' ) as m2where m1.Grade>m2.Grade
错误:
select Sname from Student S1,Student S2,Course,Sc Sc1,Sc Sc2 where S1.S#=Sc1.S# and Sc1.C#=Course.C# and Sc2.C#=Course.C# and S2.S#=Sc2.S#and Cname='数据库原理与应用'and S2.Sname='张凯迪'and Sc1.Grade>Sc2.Grade
误区1:明确列名是哪一张表的,系统会警告