数据库-第四次实验报告-视图-t-sql语句

实验十报告创建视图兰州大学数据库实验报告实验目的1．掌握创建视图的SQL语句的用法。2．掌握使用企业管管理器创建视图的方法。3．掌握使用创建视图向导创建视图的方法。4．掌握查看视图的系统存储过程的用法。5．掌握修改视图的方法。一．实验准备1．了解创建视图方法。2．了解修改视图的SQL语句的语法格式。3．了解视图更名的系统存储过程的用法。4．了解删除视图的SQL语句的用法。二．实验要求1.用不同的方法创建视图。2.提交实验报告，并验收实验结果。三．实验内容1.创建视图（1）使用企业管理器创建视图①在EDUC库中以“student”表为基础，建立一个名为“V_计算机系学生”的视图。在使用该视图时，将显示“student”表中的所有字段.视图如下：(2)使用SQL语句创建视图①在查询分析器中建立一个每个学生的学号、姓名、选修的课名及成绩的视图S_C_GRADE；CreateVIEWS_C_GRADEASSELECTstudent.sno,sname,cname,scoreFROMstudent,course,student_courseWHEREstudent.sno=student_course.snoANDcourse.cno=student_course.cno;视图如下：②建立一个所有计算机系学生的学号、选修课程号以及平均成绩的视图COMPUTE_AVG_GRADE;CreateVIEWCOMPUTE_AVG_GRADEASSELECTstudent.sno,course.cno,AVG(score)'平均成绩'FROMstudent,course,student_courseWHEREstudent.sno=student_course.snoANDcourse.cno=student_course.cnoANDstudent.dno='CS'GROUPBYstudent.sno,course.cno;视图如下：2.修改视图(1)使用企业管理器修改视图在企业管理器中将视图COMPUTE_AVG_GRADE中改成建立在数学系的学生学号、选修课程号以及平均成绩的视图。（IS设为数学系）(2)使用SQL语句修改视图①在查询分析器中使用更改视图的命令将上面建立的视图“V_计算机系学生”更名为“V_计算机系男生”。SP_RENAME'V_计算机系学生','V_计算机系男生';3.删除视图（1）使用企业管理器删除视图用企业管理器删除视图“V_计算机系学生”（2）使用SQL语句删除视图用SQL语句删除视图COMPUTE_AVG_GRADE;DROPVIEWCOMPUTE_AVG_GRADE;实验十一使用视图一．实验目的1.熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；2.熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系3.学习灵活熟练的进行视图的操作，认识视图的作用二．实验准备1．熟悉SQLSERVER工作环境。2．能连接到EDUC数据库。3．复习有关视图操作的SQL语言命令。三．实验要求1.在实验开始之前做好准备工作。2.实验之后提交实验报告，思考视图和基本表的区别四．实验内容一．定义视图在EDUC数据库中，已StudentCourse和Student_course表为基础完成一下视图定义１．定义计算机系学生基本情况视图V_Computer；CREATEVIEWV_ComputerASSELECTstudent.*FROMstudentWHEREstudent.dno='CS';视图如下：２．将StudentCourse和Student_course表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_GCREATEVIEWV_S_C_GASSELECTstudent.sno,sname,course.cno,course.cname,scoreFROMstudent,course,student_courseWHEREstudent.sno=student_course.snoANDcourse.cno=student_course.cno;视图如下：３．将各系学生人数，平均年龄定义为视图V_NUM_AVGCREATEVIEWV_NUM_AVGASSELECTCOUNT(dno)'各系人数',AVG(sage)'平均年龄'FROMstudentGROUPBYdno;视图如下：４．定义一个反映学生出生年份的视图V_YEARCREATEVIEWV_YEARASSELECTsname,2014-sage'出生年份'FROMstudent;视图如下：５．将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_GCREATEVIEWV_AVG_S_GASSELECTstudent.sno'学号',sname'姓名',COUNT(*)'选修门数',AVG(score)'平均成绩'FROMstudent,student_courseWHEREstudent.sno=student_course.snoGROUPBYstudent.sno,sname;视图如下：６．将各门课程的选修人数及平均成绩定义为视图V_AVG_C_GCREATEVIEWV_AVG_C_GASSELECTcno'课程号',COUNT(cno)'选修人数',AVG(score)'平均成绩'FROMstudent_courseGROUPBYcno;视图如下：二．使用视图1.查询以上所建的视图结果。所建视图结果如上题截图所示2.查询平均成绩为90分以上的学生学号、姓名和成绩；SELECT学号,姓名,平均成绩FROMV_AVG_S_GWHERE平均成绩90;3.查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；SELECTV_S_C_G.sno,V_S_C_G.sname,V_S_C_G.cname,V_S_C_G.scoreFROMV_S_C_G,V_AVG_S_GWHEREV_S_C_G.sno=V_AVG_S_G.学号ANDV_S_C_G.scoreV_AVG_S_G.平均成绩;4.按系统计各系平均成绩在80分以上的人数，结果按降序排列；1．先创建学生学号-系别视图V_STUDENTCREATEVIEWV_STUDENTASSELECTsno,dnoFROMstudent;2．进行查询SELECTV_STUDENT.dno'系别',COUNT(V_STUDENT.sno)'人数'FROMV_STUDENT,V_AVG_S_GWHEREV_STUDENT.sno=V_AVG_S_G.学号ANDV_AVG_S_G.平均成绩80GROUPBYV_STUDENT.dno;三．修改视图1.通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM”并查询结果;UPDATEV_ISSETsname='S1_MMM'WHERESNO='32011';UPDATEV_ISSETsname='S4_MMM'WHERESNO='32014';SELECT*FROMV_ISWHEREsno='32011'ORsno='32014';2.通过视图V_IS，新增加一个学生记录('S12','YANXI',19,'IS')，并查询结果INSERTINTOV_IS(sno,sname,sex,dno,sage)VALUES('S12','YANXI','女','IS','19')SELECT*FROMV_ISWHEREsno='S12';3.要通过视图V_AVG_S_G，将学号为“S1”的平均成绩改为90分，是否可以实现？并说明原因答：不能实现，因为视图或函数'V_AVG_S_G'包含聚合、DISTINCT或GROUPBY子句或者PIVOT或UNPIVOT运算符，所以无法进行更新。UPDATEV_AVG_S_GSET学号='32011'WHERE平均成绩='90';实验十二更新语句一．实验目的1．熟悉使用UPDATE/INSERT/DELETE语句进行表操作；2．能将这些更新操作应用于实际操作中去；二．实验准备1．了解这些更新语句的基本语法和用法；三．实验要求1．完成下面的实验内容，并提交实验报告；2．在实验报告中附上相应的代码；四．实验内容1．对于student表，将所有专业号为‘001’的，并且入学年份为2006的学生，或是专业号为‘003’，并且年龄小于20岁的学生的班级号改为‘001’。UPDATEstudentSETclassno='001'WHERE(mno='001'ANDYEAR(sctime)='2006')OR(mno='003'AND(year(getdate())-year(sdate))20);2．对于student表，删掉所有年龄小于20岁，并且专业号为‘003’的学生的记录。DELETEFROMstudentWHEREmno='003'AND(year(getdate())-year(sdate))203．对于student表，插入一条新记录，它的具体信息为，学号：2007110011、姓名：张三、性别：男、出生日期：19880808、院系编号：‘001’、专业编号：‘001’、班级号：‘001’、入学时间：20070901。INSERTINTOstudent(sno,sname,sex,sdate,dno,mno,classno,sctime)VALUES('2007110011','张三','男','1988-8-8','001','001','001','2007-9-1');4．对于student表，将入学时间最晚的学生和年龄最小的学生的联系方式去掉。UPDATEstudentSETtel=nullWHEREsctime=ALL(selectsctimeFROMstudent)ORyear(sdate)=ALL(selectyear(sdate)fromstudent)5．对于student表，将平均年龄最小的一个院系的院系编号改为‘008’。UPDATEstudentSETdno='008'wherednoin(SELECTtop1dnoFROMstudentGROUPBYdnoORDERBYAVG(year(getdate())-year(sdate)))实验十三T-SQL编程一、【实验目的】1、掌握T_SQL编程的基本语法2、常用函数的使用方法二、【实验要求】1、使用查询分析器练习T-SQL编程方法2、练习函数的使用三、【实验准备】1、复习与本次实验内容相关知识2、预习相关函数（请预查阅CONVERT、DATENAME、GFTDATE函数的用法）四、【实验内容】1、条件结构自己编写一段程序判断一个年份(比如1900年)是否是闰年,是则显示1900年为闰年,否则显示1900年不是闰年.DECLARE@yearINTSELECT@year=2014if@year%4=0and@year%1000printCONVERT(CHAR(4),@year)+'是闰年'elseprintCONVERT(CHAR(4),@year)+'不是闰年'2、循环结构（1）下面是计算1~100和的循环结构，执行之，体会循环结构程序，注意语句块标志BEGIN...ENDDECLARE@SUMINT,@IINTSELECT@I=1,@SUM=0WHILE@I=100BEGINSELECT@SUM=@SUM+@ISELECT@I=@I+1ENDPRINT'1...100的和为：'+CONVERT(CHAR(4),@SUM)（3）编写一个程序用于计算10的阶乘DECLARE@SUMINT,@IINTSELECT@I=1,@SUM=1WHILE@I=10BEGINSELECT@SUM=@SUM*@ISELECT@I=@I+1ENDPRINT'阶乘!的结果为：'+CONVERT(CHAR(10),@SUM)（4）自己编制一个程序,要求其能打印出100内的素