《无线自组织网络开发与设计综合实训项目》SQL语句部分(补充内容)

目》SQL语句部分(补充内容)

实训一熟悉SQLSERVER，数据定义实训一、实训目的1、利用查询分析器创建数据库2、利用查询分析器用SQL语言定义基本表、索引并进行相关操作二、实训步骤及内容在以下实训中，使用学生-课程数据库，它描述了学生的基本信息、课程的基本信息及学生选修课程的信息。1．创建学生课程数据库2．创建学生关系表S：学号姓名Sno课程号Cno学号Sno6、在表S上增加“出生日期”属性列。7、删除表S的“年龄”属性列。8、删除S姓名列的唯一值约束。9、修改SC表成绩属性列为精确数字型。10、在表S上，按“Sno”属性列的唯一值方式建立索引。11、删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。三、实训结果：1．创建学生课程数据库createdatabaseStudend;性别Ssex先行课Cpno课程号Cno年龄Sage学分ccredit成绩grade所在系sdeptSname课程名Cname3．创建课程关系表C：4．创建课程关系表SC：5．将以上创建表S、C、SC的SQL命令以.SQL文件的形式保存在磁盘上。2．创建学生关系表S：学号姓名SnoSnamecreatetableS(SnoCHAR(9)PRIMARYKEY,SnameCHAR(20)UNIQUE,SsexCHAR(2),Sagesmallint,Sdeptchar(20),);3．创建课程关系表C：课程号CnocreatetableC课程名Cname性别Ssex年龄Sage所在系sdept先行课Cpno学分ccredit(Cnochar(4)primarykey,CnameCHAR(40),Cpnochar(4),CcreditSMALLINT,);4．创建课程关系表SC：学号SnocreatetableSC(Snochar(9),Cnochar(4),Gradesmallint,);课程号Cno成绩grade5．将以上创建表S、C、SC的SQL命令以.SQL文件的形式保存在磁盘上。6、在表S上增加“出生日期”属性列。altertableSaddSbirthdaydatetime;7、删除表S的“年龄”属性列。altertableSdropcolumnSage;8、删除S姓名列的唯一值约束。altertableSdropUQ__S__7D78A4E7;9、修改SC表成绩属性列为精确数字型。altertableSCaltercolumnGradeint;10、在表S上，按“Sno”属性列的唯一值方式建立索引。createuniqueindexStusnoonS(Sno);11、删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。droptableSC;实训二数据更新实训

一、实训目的1、熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用2、加深理解表的定义对数据更新的作用二、实训步骤及内容（一）利用查询分析器在表中插入数据打开在实训一中建立的“学生课程”数据库；在“SCDB”数据库中的student、course、sc表中插入数据；student表的内容：SNOSNAMESSEXSAGESDEPT95001李勇男20CS95002刘晨女19IS95003王敏女18MAcourse表的内容CNOCNAMECPNOCREDIT1数据库2数学NULL23信息系统144操作系统NULL35数据处理NULL2sc表的内容SNOCNOGRADE95001192950012659500138500229095002380（二）修改表中的数据在插入数据后的三张表S、C、SC中进行相关的更新操作：1、将student表中‘李勇’的名字改为‘李明’；2、将student表中‘王敏’的年龄改为20岁；3、将所有学生的年龄增加1岁；4、修改学生的学号‘95002’为‘95005’，注意表之间的关系，保持一致性；5、把选修了“2”号课程，且成绩低于70分的成绩提高5%；6、将“CS”系全体学生的成绩置零；（三）删除表中的数据1、把选修了“2”号课程，且成绩低于70分的学生的成绩删除掉；2、删除学号为“95003”的相关信息；3、删除\"IS\"系选修了\"2\"号课程的选课记录；4、删除“CS”系的全体学生的选课记录；5、删除整张表的数据，注意表之间的关系，保持一致性。三、实训结果：（一）利用查询分析器在表中插入数据打开在实训一中建立的“学生课程”数据库；在“SCDB”数据库中的student、course、sc表中插入数据；student表的内容：SNOSNAMESSEXSAGESDEPT95001李勇男20CS95002刘晨女19IS95003王敏女18MAcourse表的内容CNOCNAMECPNOCREDIT1数据库2数学NULL23信息系统144操作系统NULL35数据处理NULL2sc表的内容SNOCNOGRADE95001192950012659500138500229095002380insertintoS(Sno,Sname,Ssex,Sage,Sdept)values('95001','李勇','男',20,'CS');insertintoS(Sno,Sname,Ssex,Sage,Sdept)values('95002','刘晨','女',19,'IS');insertintoS(Sno,Sname,Ssex,Sage,Sdept)values('95003','王敏','男',20,'CS');select*fromS;insertintoC(Cno,Cname,Cpno,Ccredit)values('1','数据库','5',4);insertintoC(Cno,Cname,Cpno,Ccredit)values('2','数学',NULL,2);insertintoC(Cno,Cname,Cpno,Ccredit)values('3','信息系统','1',4);insertintoC(Cno,Cname,Cpno,Ccredit)values('4','操作系统',NULL,3);insertintoC(Cno,Cname,Cpno,Ccredit)values('5','数据处理',NULL,2);insertintoSC(Sno,Cno,Grade)values('95001','1',92);insertintoSC(Sno,Cno,Grade)values('95001','2',65);insertintoSC(Sno,Cno,Grade)values('95001','3',88);insertintoSC(Sno,Cno,Grade)values('95002','2',90);insertintoSC(Sno,Cno,Grade)values('95002','3',80);（二）修改表中的数据在插入数据后的三张表S、C、SC中进行相关的更新操作：1、将student表中‘李勇’的名字改为‘李明’；updateSsetSname='李明'whereSno='95001';2、将student表中‘王敏’的年龄改为20岁；updateSsetSage=20whereSname='王敏';3、将所有学生的年龄增加1岁；updateSsetSage=Sage+1;4、修改学生的学号‘95002’为‘95005’，注意表之间的关系，保持一致性；updateSsetSno='95005'whereSno='95002';5、把选修了“2”号课程，且成绩低于70分的成绩提高5%；updateSCsetGrade=Grade*1.05whereCno='2'andGrade<70;6、将“CS”系全体学生的成绩置零；updateSCsetGrade=0where'CS'=(selectSdeptfromSwhereS.Sno=SC.Sno);（三）删除表中的数据1、把选修了“2”号课程，且成绩低于70分的学生的成绩删除掉；deletefromSCwhereCno='2'andGrade<70;2、删除学号为“95003”的相关信息；deletefromSwhereSno='95003';3、删除\"IS\"系选修了\"2\"号课程的选课记录；deletefromSCwhereCno='2'andSnoin(selectSnofromSwhereSdept='IS');4、删除“CS”系的全体学生的选课记录；deletefromSCwhere'CS'=(selectSdeptfromSwhereS.Sno=SC.Sno);5、删除整张表的数据，注意表之间的关系，保持一致性。deletefromSC;实训三数据查询实训一、实训目的熟悉和掌握对数据表中数据的查询操作和SQL命令的使用，学会灵活熟练的使用SQL语句的各种形式；加深理解关系运算的各种操作（尤其的关系的选择，投影，连接和除运算）二、实训方法及步骤１．在表student，course，sc上进行简单查询；２．在表student，course，sc上进行连接查询；３．在表student，course，sc上进行嵌套查询；４．使用聚合函数的查询；５．对数据的分组查询；６．对数据的排序查询。三、实训内容在表student，course，sc上完成以下查询：1、查询学生的基本信息；2、查询“CS”系学生的基本信息；3、查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；4、找出最大年龄；5、找出“CS”系年龄最大的学生，显示其学号、姓名；6、统计“CS”系学生的人数；7、统计各系学生的人数，结果按升序排列；8、按系统计各系学生的平均年龄，结果按降序排列；9、查询每门课程的课程名；10、统计无先修课的课程的学分总数；11、查询选修了“1”或“2”号课程的学生学号和姓名；12、查询选修了“1”和“2”号课程的学生学号和姓名；13、查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；14、查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；15、查询没有选修课程的学生的基本信息；16、查询选修了3门以上课程的学生学号；17、查询选修课程成绩至少有一门在80分以上的学生学号；18、查询选修课程平均成绩在80分以上的学生学号；19、（选做）统计每位学生选修课程的门数、学分及其平均成绩；20、（选做）找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列。四、实训结果：1、查询学生的基本信息；select*fromS;2、查询“CS”系学生的基本信息；select*fromSwhereSdept='CS';3、查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；selectSno,SnamefromSwheresdept='CS'ANDSagenotbetween19and21;4、找出最大年龄；selectmax(Sage)fromS;5、找出“CS”系年龄最大的学生，显示其学号、姓名；selectSno,SnamefromSwhereSage=(selectmax(Sage)fromSwhereSdept='CS');7、统计“CS”系学生的人数；SELECTSdept,count(*)fromSwhereSdept='CS'GROUPBYSdept;8、统计各系学生的人数，结果按升序排列；SELECTSdept,COUNT(*)FROMSgroupbysdept;9、按系统计各系学生的平均年龄，结果按降序排列；selectSdept,avg(Sage)fromSgroupbySdeptorderbyavg(Sage)desc;10、查询每门课程的课程名；selectCnamefromCgroupbyCname;10、统计无先修课的课程的学分总数；selectsum(Ccredit)fromCwhereCpnoisnull;11、查询选修了“1”或“2”号课程的学生学号和姓名；selectS.Sno,SnamefromS,SCWHERES.Sno=SC.SnoAND(Cno='1'orCno='2');12、查询选修了“1”和“2”号课程的学生学号和姓名；selectS.Sno,SnamefromSC,SwhereCno='1'ANDS.Sno=SC.SnoandSC.SnoIN(selectSnofromSCwhereCno='2');13、查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；selectS.Sno,Sname,GradefromS,SC,CwhereSC.Sno=S.SnoandC.Cno=SC.CnoandCname='数据库'andGrade<60;14、查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；selectS.Sno,Sname,C.Cno,Cname,GradefromSC,C,SWHERES.Sno=SC.SnoandC.Cno=SC.Cno;15、查询没有选修课程的学生的基本信息；select*fromSWHEREnotexists(select*fromSCWHERES.Sno=Sno);16、查询选修了3门以上课程的学生学号；selectSnofromSCgroupbySnohavingcount(*)>3;17、查询选修课程成绩至少有一门在80分以上的学生学号；selectdistinctS.SnofromS,SCWHERES.Sno=SC.SnoandGrade>80;18、查询选修课程平均成绩在80分以上的学生学号；selectdistinctS.SnofromS,SCwhereSC.Sno=S.SnoandSC.Snoin(selectSnofromSCGROUPBYSnohavingavg(Grade)>80);19、找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列。实训四视图的定义、使用实训

一、实训目的熟悉和掌握对数据表中视图的定义操作和SQL命令的使用；熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系；学习灵活熟练的进行视图的操作，认识视图的作用；二、实训方法及步骤１．视图的定义２．视图的查询３．视图的修改三、实训内容：以students，courses，sc表为基础完成以下视图定义（使用SQL和企业管理器两种方法）及使用1.定义“IS”系学生基本情况视图V_IS并查询结果；2.将students，courses，sc表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询结果;3.将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果;4.定义一个反映学生出生年份的视图V_YEAR并查询结果;5.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;6.建立视图V_AVG平均成绩为90分以上的学生学号；7.通过视图V_IS，分别将学号为“95001”和“95004”的学生姓名更改为“S0001”,”S0004”并查询结果;8.通过视图V_IS，新增加一个学生记录(’S0012’,’YANXI’,19,’IS’)，并查询结果；9.通过视图V_IS，新增加一个学生记录(’S0013’,’YANXI’,19,’MA’)，并查询结果；10.通过视图V_IS，删除学号为“S0012”和“95003”的学生信息，并查询结果；四、实训结果以students，courses，sc表为基础完成以下视图定义（使用SQL和企业管理器两种方法）及使用1.定义“IS”系学生基本情况视图V_IS并查询结果；createviewV_ISasselect*fromSwhereSdept='IS';2.将students，courses，sc表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询结果;createviewV_S_C_GasselectS.Sno,Sname,C.Cno,Cname,GradefromS,SC,CwhereS.Sno=SC.SnoandSC.Cno=C.Cno;3.将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果;createviewV_NUM_AVG(Sdept,Sdept_count,Sage_avg)asselectSdept,count(Sdept),avg(Sage)fromSgroupbySdept;4.定义一个反映学生出生年份的视图V_YEAR并查询结果;createviewV_YEAR(Sname,Sbirthday)asselectSname,2013-SagefromS;5.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;createviewV_AVG_S_G(Sno,Cno_count,Grade_avg)asselectSno,count(Cno),avg(Grade)fromSCgroupbySno;6.建立视图V_AVG平均成绩为90分以上的学生学号；createviewV_AVG(Grade_over90_Sno)asselectS.SnofromS,SCwhereSC.Sno=S.SnoandSC.Snoin(selectSnofromSCgroupbySnohavingavg(Grade)>90);7.通过视图V_IS，分别将学号为“95001”和“95004”的学生姓名更改为“S0001”,”S0004”并查询结果;updateV_ISsetSname='王明'whereSno='95001';updateV_ISsetSname='王强'whereSno='95002';8.通过视图V_IS，新增加一个学生记录(’S0012’,’YANXI’,19,’IS’)，并查询结果；insertintoV_IS(Sno,Sname,Sage,Sdept)VALUES('95005','宋明','19','IS');9.通过视图V_IS，新增加一个学生记录(’S0013’,’YANXI’,19,’MA’)，并查询结果；insertintoV_IS(Sno,Sname,Sage,Sdept)VALUES('95006','王芳','19','MA');10.通过视图V_IS，删除学号为“S0012”和“95003”的学生信息，并查询结果；deletefromV_ISwhereSno='95005'orSno='95006';