《2023年数据库第四次实验报告视图tsql语句.pdf》由会员分享，可在线阅读，更多相关《2023年数据库第四次实验报告视图tsql语句.pdf（28页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、实验十报告创建视图兰州大学数据库实验报告实验目的1.掌握创建视图的SQL语句的用法。2.掌握使用公司管管理器创建视图的方法。3.掌握使用创建视图向导创建视图的方法。4.掌握查看视图的系统存储过程的用法。5.掌握修改视图的方法。一.实验准备1.了解创建视图方法。2.了解修改视图的SQL语句的语法格式。3.了解视图更名的系统存储过程的用法。4.了解删除视图的SQL语句的用法。二.实验规定1.用不同的方法创建视图。2.提交实验报告，并验收实验结果。三.实验内容1.创建视图(1)使用公司管理器创建视图 在EDUC库中以“s t u d e n t”表为基础，建立一个名为“V_计算机系学生”的视图。在使
2、用该视图时,将显示“s tu d e n t”表中的所有字段.流加表I J 公XX34.EDUC-dbo.View_l视图 的数 同义词student僚有列)snosnamesexnative二1coursecourse_pcoursestudentstudent_courseteacherteacher_course列 别名刷新(R)ELECTR.OM dbo.student国 student冯i-JUUI/V A O J V U“3 U 3 0c列 别名 表 输出 排序类空 排序喉序 筛选器 或 或或native student 回birthday student,dno student
3、团=CS*DSELECT sno,sname,sex,native,birthday,dno,spno,dassno,entime,home,tel,sageFROM dbo.studentWHERE(dno=CS)XX34.EDUC-dbo.View 1*studentM*僚有珈Misname叵|sexI*/native，sname,cname,scoreFROM studentr course,student_courseWHERE student.sno=studenc_course.sno-AND o=student course.eno;nrJ消息命令已成功完嬴刻有eefrnamx加
4、wsncsnsena00一国 studentIB口国 student_cou.一|曰cna色列 别名 表 输出 排序类型 排序顺序 筛:snostudent0snamestudentcnamecoursescorestudent_c.rn视图如下:XX34.EDUC-dbo.S_C_GRADE XX34.EDUC-dbo.S_C_GRADE SQLQuerysnosnameonamescore32020张三数据库6032013王安石C+NULL32018孙中山数据库5532014马周军数据库9532011刘洋数据库9232015刘翔数据库6732015刘翔Java9532015刘翔C+5532
5、015刘翔英语6732015刘翔高等数学AUZ32012李万数据库5932012李万Java9932012李万英语7932012李万高等数学7732016李敏浩英语98NULLNULLNULL建立一个所有计算机系学生的学号、选修课程号以及平均成绩的视图C O M P UT E J W G_ G R A D E;Cr e a te VIEW COMPUTE_AVG_GRADEASSELECT s t u d e n t.sno, o,A VG(s c o r e)平均成绩FROM stu d e n t c o u rse,s t u de n t_co u r s eWHERE stu d e
6、 n t.sn o=student_ c o u rse.snoAN D COU r S e.C no=s t ud e nt_ c ourse.eno AND s tu d e n t.dn 0=*C SGROUP BY stu d e n t.s n o,course,cn o;SQLQuery3.sql-x)u.(XX35lenovo(53)*Create VIEW COMPUTE_AVG_GRADEASSELECT scudenc.snor oz AVG(score)平均成绩FROM studentz course,student_courseWHERE student.sno=stu
7、dent_course.snoAND o=studenc_course.eno AND student.dno=CS,-GROUP BY student.sno,course.eno;出 消息命令已成功完成。I视图如下：/XX35.EDUC-dbo.COMPUTE_AVG_GRADE SQLQuery3.ssnoeno平均成绩刎,同C3NULL32016C498米AUZNULLMJLL2.修改视图(1)使用公司管理器修改视图在公司管理器中将视图C O M P UT E _ A V G _ G R A DE中改成建立在数学系的学生学号、选修课程号以及平均成绩的视图。(I S 设为数学系)XX35
8、.EDUC dbo固 student.MPUTE_AVG_GRADE*XX35.EDUC-dbo.COMPUTE.AVG.GRADE SQLQuery3,sql-xx.(XX35lenovo(53)*岳口日SELECT dbo.student.sno,cfco.course.ox),AVG(dbo.student_coirse.score)AS 平均成绩FROM dbo.student INNER JOINcfoo.student_course ON cfix).student.sno=dbo.student_course.sno INb JOINdbo.course ON dbo.stude
9、nt_o cfoo.course.ax)WHS汇(dbo.student.dno=IS*)GROUP BY dbo.student.sno,dbo.course.eno列别名表输出排序类型排序顺序分组依据筛选器 或snostudent团分组依据enocourse分组依据score平均student_c.回Avgdnostudent0Where 二方.Jin1X35.EDUC-dbo.COMPUTE AVG GRADE SQLQuery3snoeno平均成绩32011C l9232012C l8832014C l9532015C l8732018C l5532020C l6032012C2993
10、2015C29532015C35532012C47932015C48732012C57732015C5NHLAAJLLNULL 使 用SQL语句修改视图在查询分析器中使用更改视图的命令将上面建立的视图“V_计算机系学生”更名为“V_计算机系男生”。SP_RENAME V计算机系学生J V计算机系男生；n y jy 4 u iy，w -八I U I I U V U 3)SP_RENAME W_计算机系学生亡讦算机系男生丁4 I_1”备 消息注意：更改对象名的任一部分都可能会破坏脚本和存储过程。3.删除视图(1)使用公司管理器删除视图用公司管理器删除视图“V计算机系学生”X眦除对象迭泽页 常 规国
11、 脚 本，必 藉助要删除的对象9)对象名称 对象类型 所有者状态 消息 1 V_计算机系学生 视图 dbo(2)使用SQL语句删除视图用SQL语句删除视图COMPUTE_AVG GRADE;DR O P V I E W C O MPUT E_A V G_ GR A D E；-A A.A A J3ic n u v uDROP VIEW COMPUTE AVG GRADE;J消息命令已成功完成。实验十一使用视图实验目的1 .熟悉和掌握对数据表中视图的查询操作和S Q L命令的使用；2 .熟悉和掌握对数据表中视图的更新操作和S Q L 命令的使用，并注意视图更新与基本表更新的区别与联系3 .学习灵活
12、纯熟的进行视图的操作，结识视图的作用实验准备1 .熟悉S Q L S E R V E R 工作环境。2 .能连接到E D UC 数据库。3 .复习有关视图操作的$Q L 语言命令。三.实 验 规 定1 .在实验开始之前做好准备工作。2 .实验之后提交实验报告，思考视图和基本表的区别四.实 验 内 容定义视图在 E D UC 数据库中，己S t u d e n t C ou r s e和 S t u d e n t _c ou r s e 表为基础完毕一下视图定义1 .定义计算机系学生基本情况视图V C om p u t e r；CREATE VIEW V_Compute rASSE L EC
13、T s tu d e n t.*FROM s t u dentWHERE stu d en t,d n o=,C S 1：l y j y ja w a iw v w、1/E CREATE VIEW V_ConuterASSELECT student.*FROM student-WHERE 3tudent.dno=CS,:|T力 消息命令已成功完成。视图如下:人，-u w w.v _ v w in p u ic i n y j y j u t y j”i-AA./WKj j IC Iiw w 1八snosnamesexnativebirthdaydnospnodassno32013王安石男河南1
14、992-02-02 00:.CscpNULL32016李敏浩勇韩国19948-08 00:.CsBNULL32019周意女甘南1993-04)4 00:.CscpNULL*NULLNULLNULLNULLNULLNULLNUUNULL2.将 S t u de n tCo u r s e和St u d e nt c ou r s e表中学生的学号,姓名，课程号,课程名，成 绩定义为视图V_S C_GCREATE VIEW V_S_C_GASS E LECT s tu dent.snor s n am e z cours e.c n o,c o u ame,s c oreFROM stu d en
15、 t,cou r s e,s t u d ent_cours eWHERE stud e n t.s n o=s tude n t_course.sn o AND course,eno s tu den t_ c o u r s e.eno;CREATE VIEW V_S_C_GASSELECT stu d e n t.sn o,snam e,course.en o,c o u ame,scoreFROM sru d e n t,c o u rse,stu d e n t_ c o u rse-WHERE s tu d e n t.sno=student c o u rse.sno AND c
16、 o u o=student c o u rse.eno;nr3消息命令已成功完成。视图如下:XX35.EDUC-dbo.V S C G SQLQuery5.sql-xx.(XX35lenovo(53)*snosnameenocnamescore32020张三C l数据库6032013王安石C3C+AAJLL32018孙中山C l数据库5532014马周军C l数据库9532011刘洋C l数据库9232015划翔C l数据库8732015划翔C2Java9532015刘翔C3C+5532015刘翔C4英语8732015划翔C5高等数学AUZ32012李万C l数据库8832012李万C2Ja
17、va9932012李万C4英语7932012李万C5高等数学7732016李敏浩C4英语98NLALAAJLLNLILAAJLL3 .将各系学生人数，平均年龄定义为视图V_NUM_AVGCRE ATE V I EW V _N UM_A VGASS E L E C T COUNT(d n o)各系人数 I A VG(sa g e)平均年龄，F R O M s t u d e n tGROUP BY d n o；-、J r 一 -(E CREATE VIEW V_NUM_AVGASSELECT COUNT(d n o)，各系人数 IAVG(s a g e)，平均年龄，FROM s tu d e n
18、 t-GROUP BY d n o;4|加命令 岂成功完成。T视图如下：XX35.EDUC-dbo.V_NUM.AVG SQLQu,各系人数平均年龄*4 定义-日216 20NLIL MJLL一个反映学生出生年份的视图V_YEARCREATE V IEW V_YEARASS E L ECT snam e,20 2 3-s a g e，出生年份，F ROM s t u d e n t；看 CREATE-VIEWASSELECT snamez2014-sage 出生年份.-FROM student;.消息命令已成功完成。视图如下：XX35.EDUC dbo.V.YEAR SQLQuesname出生
19、年份刘洋1993李万1994王安石1992马周军1993刘翔1994李敏浩1993孙中山1994周鑫1993张三1993米NHLMJLL5.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_GCREA T E VIEW V_AVG_S_GASSELECT s t U d en t.S n O，学号 Is n a m e ，姓名COUNT(*),选修门数，AVG(s cor e)平 均 成 绩，FROM stu d e n t,stu d e n t _ courseWHER E s tu d e n t.sno=st u dent_ c o urs e.snoG ROUP BY s
20、t u den t.s n o,sn a m e；/u X O J.E U W e -U U U.V _M V U _i_k3-A A.V,A A 33 ltfllU V U CREATE VIEW V_AVG_S_GASSELECT s tu d e n t.s n o，学号L s n a m e，姓名ICOUNT(*)，选修门数I AVG(score),平均成绩，FROM stu d e n t,stu d e n t_ c o u rseWHERE s tu d e n t.sn o=stu d enc_ co u rse.sno-GROUP BY s tu d e n t.sno,sn
21、am e;T节肖息命令已成功完成。视 图 如 下：V_AVG_S_G.平均成绩；/SQLQueryS.sql-xx.(XX35lenovo(53)*SELECT，XA A5AAsI FROM V S C G,V AVG S GWHERE AND口 结 果 二 消息snosnamecnamescore1|32012 j李万数据库88232012李万Java99332015刘翔数据库87432015刘翔Java95532015刘翔英语874.按系记录各系平均成绩在8 0分以上的人数,结果按降序排列;1.先创建学生学号-系别视图V_STUDENTCREATE V IEw V_STUDENTASSEL
22、EC T S n OZ dn OFROM S t u d e n t;2.进行查询S E LECT V _STU D EN T.dn o 系别 3 C OUNT(V_S TUDENT.sn o)，人数，F ROM V_STU D ENT,V_AVG_S_GWHERE V_STUDENT.sno=V _A V G _S_G.学号 AND V_AVG_S_G.平均成绩80GROUP BY V STUDENT,d n o；E SELECT V STUDENT.dno 系 另,COUNT(V STUDENT.s n o)人数FROM V STUDENTrV AVG S GAAAA/WWWWVWSAA
23、_ _ _WHERE V STUDENT.sno=V AVG S G 学号 AND V AVG S G,平均成绩80A/SAAAAAi5VWv*/VSAAA*一 一 J J _ I Q 人人GROUP-ORDERBY V STUDENT.dnoBY COUNT(V STUDENT.sno)DESC;二1结 果3消息凝 厂 人数1 I is j 42 Cs 1三.修改视图1.通过视图v _ l S,分别将学号为“为”和“S4”的学生姓名更改为S4_MMM”并查询结果;UPDATE V _ I S SET s n am e=S1_MMMZ WHERE SNO=3202 3,；UPDATE V _
24、I S SET snam e=1 S4_MM M W H E R E SN O=3 2 0 2 3SEL E CT*FROM V_ ISWHERE sno=3202 3 OR s n o=3 2 023,;sn a ir.e-S l MMM，WHERE SMO=,320117;I UPDATE V IS SET snam e=*S4 MMM*WHERE S N O132014,;电 SELECT*FROM 丫 口-WHERE sn o=,32011*OR AsAnA AoA*=3 2 0 1 4,;4|iii口 结 果 消息：snosnamesexnativebirthdaydnospnoda
25、ssnoentimehometdsage1 32011 IS1.MMM里重庆1993-11-11 00:00:00IsecNULL2 0 1 2 g o 6 00:00:00重庆110212 32014S4_MMM男海南199344)5 00:00:00IsecNULL2012-094)6 00:00:00海南120212.通过视图V _ IS,新增长一个学生记录(S12,Y A N X I,19,I S),并查询结果工N S E RT INTO V_I S(snoz sname,se x z dn O f s age)VALUES(S 1 2 J YAN X工，J 女 ，I Sf z,1 9
26、,)s ELECT*FROM V _ isWH ERE sno=*S12;3 INSERT INTO V IS i sno.snarr.e,se x,dno,s a g e)k VALUES(,S 1 2 Z YAN X工 女 IS ,19.)5 SELECT*FROM V IS YZVAAAzW-WHERE sn o=,S12,;|iZtAA/SAA|rrr口 结 果.J消息snosnamesexnativebirthdaydnospnodassnoentimehome telsage1j S12 j YANXI女NULLNULLISNULLNULLNULLNULL NULL193.要 通
27、过 视 图V _ A V G _ S _ G,将 学 号 为“$1”的 平 均 成 绩 改 为9 0分,是否可以实现？并说明因素答：不能实现，由于视图或函数V _A V G _ S _ G,包含聚合、D I S T I N C T或GRO UP BY子句或者P I V O T或U N P I V O T运算符，所以无法进行更新。UPDATE V _ A V G _ S _ G SET 学号=，3 2 0 2 3 W H ER E 平均成绩=,9 0，_ .IUPDATE V_AVG_S_G SET 学号=32011，WHERE 平均成绩=9。；消息消息4403,级别1 6,状态1,第I f f
28、图为视图或函效V_AVG_S_G1应含藜合、DISTINCT或GROUP BY子句或者PIVOT或WPIVOT运算符，所以无法进行更新。实验十二更新语句实验目的1.熟悉使用U PD A T E/I N S E R T/D E L E T E 语句进行表操作；2.能将这些更新操作应用于实际操作中去；二.实验准备1.了解这些更新语句的基本语法和用法；三.实验规定1.完毕下面的实验内容，并提交实验报告；2.在实验报告中附上相应的代码；四.实验内容1.对于s t u de n t 表，将所有专业号为 001 的，并且入学年份为202 3 的学生，或是专业号 为 003，并且年龄小于20岁的学生的班级号
29、改为 0 0 1。U PD A T E s t u de nt S E T c l a s s n o=0 0 1 W H E R E (m no=001 A N D Y EAR (S C t i m e)=2023)O R(m no=0 0 3,A N D(ye a r (g e t da t e ()-ye a r (s d a t e )2 0)：-yj yj j f y iw a iw v wS UPDATE stu d e n t SET c la s s n o=001,I-WHERE(mno=001,AND YEAR(sctim e)2006)OR(mno=003,AND(yea
30、r(g e td a te ()-y e a r(sdace)2 0);F s e le c t*L FROM stu d e n t;|snosnamesexsdatednomnodassnosctime1;2006110011I小刘男1993X)65 00:00:00,0000010010012006501 00:00:00,00022006110012王三女1992-043 00:00:00.0000010010012 0 0 6 0 1 00:00:00.00032006110013小李男19954)7-08 00:00:00.0000010030012 0 0 6 0 1 00:00
31、:00.00042007110001张二里19954)8-08 00:00:00.0000020030012007-0)1 00:00:00.00052007110002大四女1994-08)6 00:00:00.0000020030022007-09)1 00:00:00.00062007110003阿娇女1993084)2 00:00:00.0000020010022007-0901 00:00:00,0002.对于s t u d e nt 表,删掉所有年龄小于2 0岁,并且专业号为 0 0 3 的学生的记录。DELETE FROM stu d e n tWHERE m no=r 0 0
32、3*AND(ye a r(g e td a te ()-y e a r(sd a te)2 0B DELETE FROM s tu d e n t1-WHERE mno=,0 0 31 AND(y e a r(g e c d a c e ()-y e a r (s d a te)=A L L (s el ect s ct i m e F R O M s t u den t)O R y e ar (s d a t e)=A L L (s el ec t y e a r (s d at e)fr o m s t u den t)UPDATE student SET tej,=nullWHERE s
33、ctiiEe=ALL(select sctim e FROM student)-OR y e a r(sdate)=ALL(select y e a r(sdate)from student)se le c t*L FROM student;!I ，二 二目结 果 上 消 息snosnamesexsdatednomnodassnosctimetel1 2066110611 i 小刘男199306)5 00:00:00.0000010010012 0 0 6*)1 00:00:00,00012345622006110012王三女1992-04-03 00:00:00.00000100100120
34、 060 1 00:00:00.00012345632007110002大四女1994-084)6(X):00:00.0000020030022007-09)1 00:00:00.000NULL42007110003阿娇女19934)82 00:00:00.0000020010022007 g)1 00:00:00.000NULL52007110011张三里1988X)8-08 00:00:00.0000010010012007-09)1 00:00:00,000NULL5.对于s tu d e n t表，将平均年龄最小的一个院系的院系编号改为0 0 8、UPDATE s tu d e n t
35、 SET dn 0=008wh e r e dno in(SELECT top 1 d n o F ROM s t udentGROUP BY d n oORDER BY AVG(y e a r (ge t d a t e()y e a r(s d a te)UPDATE student SET dno=,008,where dno i n(SELECT top 1 dno FROM scudentGROUP BY dno-ORDER BY AVG(year(getdate()-y ear(sd ate)n se le c t*L FROM student;Iin3结果1消息snosnames
36、exsdatednomnodassnosctimetd1I 2006110011小刘男19934)64)5 00:00:00.0000010010012006301 00:00:00.00012345622006110012王三女1992-04-03 00:00:00.00000100100120064)94)1 00:00:00.00012345632007110002大四女1 为4於06 00:00:00.0000080030022007-094)1 00:00:00.000NULL42007110003阿娇女19934)8-02 00:00:00,0000080010022007-034
37、)1 00:00:00,000NULL52007110011张三男1984)8-08 00:00:00.0000010010012007-09)1 00:00:00.000NULL实验十三T-SQL编程一、【实验目的】1、掌握T _SQ L编程的基本语法2,常用函数的使用方法二、【实验规定】1、使用查询分析器练习T-S Q L 编程方法2、练习函数的使用三、【实验准备】1、复习与本次实验内容相关知识2、预习相关函数（请预查阅CONVE RT、D A TEN A M E,G FT DA T E 函数的用法）四、【实验内容】1、条件结构自己编写一段程序判断一个年份（比如1920 2 3）是否是闰年
38、，是则显示19202 3 为闺年,否则显示192 0 23不是闰年.DECLARE y e a r I NTSELECT year=2 0 2 3i fye a r%4=0 an d y ear%1000 p r in t CONVERT(C H A R(4)rye a r)+是闰年e 1 s e p r i n t C O N V E R T (C HA R (4),y e a r )+，不是闰年，-A A.V A A S/i e i i u v u 9 4 DECLARE gyear INTI SELECT year=20140 ifyear%4=0 and year%1000 print
39、 CONVERT(CHAR(4),yaar)+是闰年-else print CONVERT(CHAR(4),year)+，不是闰年，丁 Lnr囱消息2014不是闰年2、循环结构(I)下面是计算1 1 0 0 和的循环结构，执行之，体会循环结构程序，注意语句块标志BEGIN.ENDDECLA RESUM INT,I INTSELECT I=LSUM=0WHILE I=10 0BEG I NSELECT SUM=SUM+ISELECT I=I+1ENDPR I NT 1 1 .10 0 的和为：=CONVE RT(CHAR(4),SUM)DECLARE SUM IN T,QI INTI SELEC
40、T I=1,SUM=OjWHILE I=100J BEGINSELECT SUM=SUM+ISELECT I=I+1-ENDPRINT 1 10。的和为：+COIT7ERT(CHAR(4),SUM)I口 消 息 _ _ _ _ _ _ _ _1-TOO的和为:SOSO（3）编写一个程序用于计算10的阶乘D ECLARE SUM I NT,I IN TSELECT I=1,SUM=1W HILE I =I OB E G INSELECT SUM=SUM*IS E L E C T I=I+1ENDPR I NT 邛介乘！的结果为：+CONVERT(CHAR(10),SUM)sqLqueryLsqi
41、-xx.(xxd/ienovo(j厂E DECLARE SUM IN T,1 INTI SELECT 1=1,SUM=1电WHILE I计算机应用机械工业出版社张建平定价2 0.0 0,可以作教材数据库技术电子工业出版社王徽定价15.0 0,可以作教材1c语言电子工业出版社遭浩强定价2 5.0 0,可以作教材)应用文写作中国人民大学出版社张锦芯定价2 5.0 0,可以作教材)管理学高等教育出版社R ob i s on定价15.0 0,可以作教材1工业管理机械工业出版社F a y ol定价太高，不适合作教材线性代数机械工业出版社李平定价50.0 0,可以作教材公司的概念机械工业出版社D u r
42、a r k定价14.0 0,可以作教材10统计学机械工北出版社徐新国定价15.0 0,可以作教材(2)请自己编程实现各位同学的成绩以等级分显示即：9 0分及以上为优,8 0分及以上到9 0以下为良,7 0分 及 以 上 到8 0分以下为中,6 0分 及 以 上 到7 0分以下为及格,其余为不及格.SELECT s t u d e n t.sno,s n a me,c name,CASEWHEN S tud e nt_cours e.s c o re=9 0 T HEN 优when s tud e nt_ c o u rse.sco r e=8 0 a n d s tu d e nt_cou r
43、 se.sc or e:7 0 a n d s t udent_c o urse.sco r e=6 0 a n d s t uden t _ c ou r s e.s core=90when studenr_ co u rse.score=80when stu d en t_ co u rse.score=70when stu d en t_ co u rse.sc o re e oe l s e，不及格，END GRADETHEN，优*and stu d e n co u rse.score=90 THEN 良and stu d en t_ co u rse.score=80 THEN 中
44、,and stu d en t_ co u rse.score=70 THEN 及格FROM stu d en t co u rse,co u rse,stu d en t-WHERE s tu d e n t co u o=course.|zno AND studenc co u rse.sno=student.sno 结果J消息snosnamecnameGRADE1|32020 张三数据库及格232013王安石C+不及格332018孙中山数据库不及格432015刘翔数据库良532015刘翔Java优632015刘翔C+不及格732015刻翔英语良832015刘翔高等数学不及格932012李
45、万数据库良1032012李万Java优1132012李万英语中1232012李万高等数学中1332016李敏浩英语优1432014S4_MMM数据库优1532011S1.MMM数据库优4、函数使用下面查询，显示当前日期，显示格式为:今天是X X X X 年 X X 月 X X 日，星期X”，执行之,体会系统函数的用法.SELECT，今天是+DATENAME(YEA R,GETD AT E()+年，+DATENAME(MONTH,GETD ATE()+月+DATE N AME(DAY,GETDATE()+,+DATENAME(WEEKDAY,GET DAT E()SELECT 今天是+DATENAME(YEAR,GETDATE()+，年,+DATENAME(MONTH,GETDATE()+月，+DATENAME(DAY,GETDATE()+，日，-+DATENAME(WEEKDAY,GETDATE()|口 结 果J消息比列名）1 岑夫票赤驿耘前苗;星地元