受立信oss劉長海sql server的學數(shù)據(jù)庫習記錄

1、SQLSERVER一、建數(shù)據(jù)表格，并添加數(shù)1、建立Student表數(shù)據(jù)createtableStudent(S#varchar(10),Snamenvarchar(10),Sage datetime,Ssex nvarchar(10)SQLSERVER一、建數(shù)據(jù)表格，并添加數(shù)1、建立Student表數(shù)據(jù)createtableStudent(S#varchar(10),Snamenvarchar(10),Sage datetime,Ssex nvarchar(10)o,2、建立Course表數(shù)據(jù)tableCourse(C#nvarchar(10),T#oN語文N數(shù)學N英語,3、建立Teacher

2、表數(shù)據(jù)tableTeacher(T#varchar(10),Tnameo,4、建立sc表3、建立Teacher表數(shù)據(jù)tableTeacher(T#varchar(10),Tnameo,4、建立sc表tableSC(S#o,o,o,二、用這些創(chuàng)建好的表進行數(shù)1、查詢01課程比02課程成績高的學生的信息及課程selecta.*,b.score,c.scorefromstudenta,scb,sccwherea.s#=b.s#and a.s#=c.s# and b.c#=01 and c.c#=02 and b.scorec.score2、查詢平均成績大于等于 60 分的同學的學和學和平均成#學號,

3、cast(avg(b.score)asdecimal(8,2)平均fromStudenta,scbwheregroupbya.s#,a.snamehavingavg(b.score)=60 order by a.s#cast為強制轉(zhuǎn)換語句，decimal可以指定小數(shù)點前后的位數(shù)，avg求平數(shù)3、cast為強制轉(zhuǎn)換語句，decimal可以指定小數(shù)點前后的位數(shù)，avg求平數(shù)3、查詢所有同學的學、選課總數(shù)、所有課程總成績Studenta,SCbya.s#,a.sname by a.s#4、查詢李姓老師的count(Tname李姓老師的數(shù)量fromTeacherwhereTnameCOUNT(*fro

4、mTeacherwhereTnamelike李COUNT(*) 李姓老師數(shù)量fromTeacherwhere4、查詢學過老師授課的同學的信selecta.*from4、查詢學過老師授課的同學的信selecta.*fromStudenta,SC,Course,Teacherwherea.S#=SC.S#SC.C#=Course.C#andandselectdistinctStudent.*fromStudent,SC,Course,andTeacher.Tname=orderbydistinct 用于返回唯一不同的5、查詢沒學過老師授課的同selectm.*fromStudentmwhereS#

5、notin(selectdistinctSC.S#fromSC, Course,TeacherwhereSC.C#=Course.C#andCourse.T#=Teacher.T#Teacher.Tname = ) order by 6、查詢學為01并且6、查詢學為01并且也為02的課程的同學的信selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#= 01andexists(Select1fromSCSC_2whereSC_2.S#=SC.S#and= 02) order by selectStudent.*fromStudent

6、,SCwhereStudent.S#=SC.S#andSC.C#= 02andexists(Select1fromSCSC_2whereSC_2.S#=SC.S#and= 01) order by selectm.*fromStudentmwhereS#in selectS#from selectdistinctS#fromSCwhereC#=01 union allselect distinct S# from SC where C# = ) t group by S# having count(1) =)order by 7查詢?yōu)?1但是沒有7查詢?yōu)?1但是沒有學為02的課程的同學的信se

7、lectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#= 01andnotexists(Select1fromSCSC_2whereSC_2.S#=SC.S#and= 02) order by selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#= 01andStudent.S#notin(SelectSC_2.S#fromSCSC_2whereSC_2.S#= SC.S# and SC_2.C# = 02) order by Student.S#8、查詢沒有學全所有課程的

8、同學的信方法一：selectStudent.*fromStudentSCwhereStudent.SgroupbyStudent.S#,Student.Sname,Student.Sage,Student.Ssexhaving count(C#) (select count(C#) from Course)方法二：selectStudent.*fromStudentleftjoinSConStudent.SgroupbyStudent.S#,Student.Sname,Student.Sage,Student.Ssexhaving count(C#) (select count(C#) from Course)9、查詢至少有一門課與學號為01的同學9、查詢至少有一門課與學號為01的同學所學相同的同學的selectdistinctStudent.*fromStudent,SCwhereStudent.S#=SC.S#and SC.C# in (select C# from SC where S# = 01) and Student.S# 0110、查詢和01號的同學學習的課程完全相同的其他同學的whereS#01andSC.C#in(selectdistinctC#fromSCwhereS#=01) 11、查詢沒學過老師講授的任一門課程