SQLserver创建表练习测验题

实验二创建数据库和表/*一、创建DB_student数据库*//*如果DB_student数据库存在，删除它*/UsemasterIfexists(selectnamefrommaster.dbo.sysdatabaseswherename='DB_student')b5E2RGbCAPDropdatabaseDB_studentGo/*以下创建DB_student数据库*/CreatedatabaseDB_studentOnprimary(name=DB_student_data,Filename='c:\programfiles\microsoftsqlserver\mssql\data\DB_student_data.mdf,P1EanqFDPwSize=5mb,Maxsize=50mb,Filegrowth=10%)Logon(name=DB_student_log,Filename='c:\programfiles\microsoftsqlserver\mssql\data\DB_student_data.ldf,DXDiTa9E3dSize=5mb,Maxsize=25mb,Filegrowth=10%)Go/*在DB_student数据库中创建student表*//*打开DB_student数据库*/useDB_studentGo/*在DB_student数据库中，如果student表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('student')RTCrpUDGiTandOBJECTPROPERTY(id,'IsUserTable')=1)droptablestudentGo/*以下创建student数据库表*/createtableStudent(snochar(9)ConstraintPK_student_snoprimarykey,5PCzVD7HxASnamechar(20),ssexchar(2),sagesmallint,sdeptchar(20))；Go/*在DB_student数据库中创建course表*//*在DB_student数据库中，如果course数据库表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('course')jLBHmALgandOBJECTPROPERTY(id,'IsUserTable')=1)droptablecourseGo/*以下创建course数据库表*/createtablecourse(cnochar(4)ConstraintPK_Course_cnoprimarykey,xHAQX74J0Xcnamechar(40),cpnochar(4),ccreditsmallint,"ForeignKeycpnoReferencesCourse(cno)*/);Go/*在DB_student数据库中创建SC表*//*在DB_student数据库中，如果SC数据库表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('sc')LDAYtRyKfEandOBJECTPROPERTY(id,'IsUserTable')=1)droptablescGo/*以下创建sc数据库表*/createtablesc(snochar(9),cnochar(4),gradesmallint,ConstraintPK_sc_sno_cnoprimarykey(sno,cno),ConstraintRFK_sc_snoForeignKey(sno)Referencesstudent(sno),Zzz6ZB2LtkConstraintRFK_sc_cnoForeignKey(cno)ReferencesCourse(cno)dvzfvkwMii)；Go/*为student数据表添加数据*/InsertintostudentValues('200215121',,李勇,,'男',20,'CS');InsertintostudentValues('200215122','刘晨','女',19,'CS');InsertintostudentValues('200215123','王敏','女',18,'MA');InsertintostudentValues('200215125','张立','男',19,'IS');/*为course数据表添加数据*/InsertintocourseValues('0001','数据库,,'0005',4);InsertintocourseValues('0002','数学',null,2);InsertintocourseValues('0003','信息系统','0001',4);InsertintocourseValues('0004','操作系统’,'0006',3);InsertintocourseValues('0005','数据结构’,'0007',4);InsertintocourseValues('0006','数据处理',null,2);InsertintocourseValues('0007','PASCAL语言','0006',4);Go/*为sc数据表添加数据*/InsertintoscValues('200215121','0001',92);InsertintoscValues('200215121','0002',85);InsertintoscValues('200215121','0003',88);InsertintoscValues('200215122','0002',90);InsertintoscValues('200215122','0003',80);Go/*二、创建jiaoxuedb(教学)数据库*//*如果jiaoxuedb数据库存在，删除它*/UsemasterIfexists(selectnamefrommaster.dbo.sysdatabaseswherename='jiaoxuedb')rqyn14ZNXIDropdatabasejiaoxuedbGo/*以下创建jiaoxuedb数据库*/CreatedatabasejiaoxuedbOnprimary(name=jiaoxuedb_data,Filename='c:\programfiles\microsoftsqlserver\mssql\data\jiaoxuedb_data.mdf,EmxvxOtOcoSize=5mb,Maxsize=50mb,Filegrowth=10%)Logon(name=jiaoxuedb_log,Filename='c:\programfiles\microsoftsqlserver\mssql\data\jiaoxuedb_data.ldf,SixE2yXPq5Size=5mb,Maxsize=25mb,Filegrowth=10%)Go/*在jiaoxuedb数据库中创建S(学生表)*//*打开jiaoxuedb数据库*/usejiaoxuedbGo/*在jiaoxuedb数据库中，如果S(学生表)存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('S')6ewMyirQFLandOBJECTPROPERTY(id,'IsUserTable')=1)droptableSGo/*以下创建S(学生表)数据库表*/CreatetableS(snochar(6)primarykey,Snamechar(8),Sexchar(2),Agetinyint,Deptchar(10));Go/*在jiaoxuedb数据库中创建C(课程表)*//*在jiaoxuedb数据库中，如果C数据库表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('C')kavU42VRUsandOBJECTPROPERTY(id,'IsUserTable')=1)droptableCGo/*以下创建C(课程表)数据库表*/createtableC(cnochar(5)primarykey,cnamechar(20),chourtinyint,ccredittinyint,)；Go/*在jiaoxuedb数据库中创建SC(选课表)*//*在jiaoxuedbt数据库中，如果sc数据库表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('sc')y6v3ALoS89andOBJECTPROPERTY(id,'IsUserTable')=1)droptablescGo/*以下创建SC数据库表*/createtablesc(snochar(6),cnochar(5),scoresmallint,primarykey(sno,cno))；Go/*在jiaoxuedb数据库中创建T(教师表)*//*在jiaoxuedbt数据库中，如果T(教师表)数据库表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('T')M2ub6vSTnPandOBJECTPROPERTY(id,'IsUserTable')=1)droptableTGo/*以下创建T(教师表)数据库表*/createtableT(tnochar(6)primarykey,Tnamechar(8),Sexchar(2),Agetinyint,Profchar(10),/*职称*/Salsmallint,/*工资*/Commsmallint,/*岗位津贴*/DeptVchar(10)/*所在系*/);Go/*在jiaoxuedb数据库中创建TC(任课表)*//*在jiaoxuedbt数据库中，如果TC数据库表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('tc')OYujCfmucwandOBJECTPROPERTY(id,'IsUserTable')=1)droptabletcGo/*以下创建TC数据库表*/createtabletc(tnochar(6),cnochar(5),primarykey(tno,cno))；Go/*为S(学生表)数据表添加数据*/InsertintosValues('001101','宋大方’,'男',19,'计算机');InsertintosValues('002102','李王','男',20,'信息');InsertintosValues('991101','张彬','男',18,'计算机');InsertintosValues（'991102','王蕾','女',19,'计算机'）;InsertintosValues（'991103','张建国’,'男',18,'计算机'）;InsertintosValues（'991104','李平方’,'男',18,'计算机'）;InsertintosValues（'991201','陈东辉’,'男',19,'计算机'）;InsertintosValues（'991202','葛鹏','男',21,'计算机'）;InsertintosValues（'991203','潘桃芝’,'女',19,'计算机'）;InsertintosValues（'991204','姚一峰’,'男',18,'计算机'）;InsertintosValues（'001102','许辉','女',22,'计算机'）;InsertintosValues（'001201','王一山’,'男',20,'计算机'）;InsertintosValues（'001202','牛莉','女',19,'计算机'）;InsertintosValues（'002101','李丽丽’,'女',19,'信息'）;Go/*为c（课程表）数据表添加数据*/InsertintocValues（'01001','计算机基础',60,3）;InsertintocValues（'01002','程序设计',80,5）;InsertintocValues（'02003','数据结构',60,6）;InsertintocValues（'02001','数据库',80,6）;InsertintocValues（'02002','计算机网络',60,6）;InsertintocValues（'01003','微机原理',60,8）;InsertintocValues（'02004','操作系统',60,6）;InsertintocValues（'03001','软件工程',60,3）;InsertintocValues（'03002','大型数据库',48,2）;InsertintocValues（'03003','图像处理',48,2）;/*为sc数据表添加数据*/InsertintoscValues（'991101','01001',88）;InsertintoscValues（'991102','01001',93）;InsertintoscValues（'991103','01001',90）;InsertintoscValues（'991101','01002',90）;InsertintoscValues（'991102','01002',98）;InsertintoscValues（'991103','01002',74）;InsertintoscValues（'991104','01002',85）;InsertintoscValues（'001201','01002',64）;InsertintoscValues（'991104','02001',33）;InsertintoscValues（'991104','01001',35）;InsertintoscValues（'991201','01001',76）;/*为T（教师表）数据表添加数据*/InsertintotValues（'000006','许红霞’,'女',39,'讲师',1100,1200,'计算机'）;eUts8ZQVRdInsertintotValues（'000007','许永军’,'男',57,'教授',2000,3000,'计算机'）;sQsAEJkW5TInsertintotValues（'000008','李桂青’,'女',65,'教授',2000,3000,'计算机'）;GMsIasNXkAInsertintotValues（'000009','王一凡’,'女',43,'讲师',1200,1200,'计算机'）;TIrRGchYzgInsertintotValues（'000010','田锋','男',33,'助教',500,800,'信息'）;InsertintotValues（'000001','李英’,'女',39,'副教授',1500,2000,'信息'）;7EqZcWLZNXInsertintotValues（'000002','张雪','女',51,'教授',1900,3000,'信息'）;InsertintotValues（'000003','张朋’,'男',30,'讲师',1000,1200,'计算机'）;lzq7IGf02EInsertintotValues（'000004','王平','女',28,'讲师',850,1200,'信息'）;InsertintotValues（'000005','李力’,'男',47,'教授',1800,3000,'计算机'）;zvpgeqJ1hk/*为tc（任课表）数据表添加数据*/InsertintotcValues('000001','02001');InsertintotcValues('000008','02002');InsertintotcValues('000003','02001');InsertintotcValues('000011','02003');InsertintotcValues('000001','01001');InsertintotcValues('000002','01002');InsertintotcValues('000002','01003');InsertintotcValues('000004','02002');InsertintotcValues('000005','01001');InsertintotcValues('000006','01002');InsertintotcValues('000003','01003');/*三、创建jxsk(教学)数据库*//*如果jxsk数据库存在，删除它*/UsemasterIfexists(selectnamefrommaster.dbo.sysdatabaseswherename='jxsk')NrpoJac3v1DropdatabasejxskGo/*以下创建jxsk数据库*/CreatedatabasejxskOnprimary(name=jxsk_data,Filename='c:\programfiles\microsoftsqlserver\mssql\data\jxsk_data.mdf,1nowfTG4KISize=5mb,Maxsize=50mb,Filegrowth=10%)Logon(name=jxsk_log,Filename='c:\programfiles\microsoftsqlserver\mssql\data\jxsk_data.ldf,fjnFLDa5ZoSize=5mb,Maxsize=25mb,Filegrowth=10%)Go/*在jxsk数据库中创建S1(学生表)*//*打开jxsk数据库*/usejxskGo/*在jxsk数据库中，如果S1(学生表)存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('S1')tfnNhnE6e5andOBJECTPROPERTY(id,'IsUserTable')=1)droptableS1Go/*以下创建S1(学生表)数据库表*/CreatetableS1(SNOchar(2)primarykey,SNchar(8),SEXchar(2),AGEtinyint,DEPTchar(10));Go/*在jxsk数据库中创建C1(课程表)*//*在jxsk数据库中，如果C1数据库表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('C1')HbmVN777sLandOBJECTPROPERTY(id,'IsUserTable')=1)droptableC1Go/*以下创建C1(课程表)数据库表*/createtableC1(CNOchar(2)primarykey,CNchar(10),/*课程名*/CTtinyint/*课时数*/);Go/*在jxsk数据库中创建SC1(选课表)*//*在jxskt数据库中，如果SC1数据库表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('sc1')V7l4jRB8HsandOBJECTPROPERTY(id,'IsUserTable')=1)droptablesc1Go/*以下创建SC1数据库表*/createtablesc1(SNOchar(2),CNOchar(2),SCOREsmallint,primarykey(SNO,CNO))；Go/*在jxsk数据库中创建T1(教师表)*//*PROF列与COMM列之间地取值关系地CHECK约束为：*//*(PROF='教授’ANDCOMM=4000)OR(PROF='副教授’ANDCOMM=2000)OR*/83lcPA59W9/*(PROF='讲师'ANDCOMM=1500)OR(PROF='助教'ANDCOMM=1000)*/mZkklkzaaP/*在jxsk数据库中，如果T1(教师表)数据库表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('T1')AVktR43bpwandOBJECTPROPERTY(id,'IsUserTable')=1)droptableT1Go/*以下创建T1(教师表)数据库表*/createtableT1(TNOchar(2)primarykey,TNchar(8),/*教师名*/SEXchar(2),AGEtinyint,PROFchar(10),/*职称*/SALsmallint,/*工资*/COMMsmallint,/*岗位津贴*/DEPTchar(10)/*所在系*/);Go/*在jxsk数据库中创建TC1(任课表)*//*在jxsk数据库中，如果TC1数据库表存在，就删除*/ifexists(select*fromdbo.sysobjectswhereid=object_id('tc1')ORjBnOwcEdandOBJECTPROPERTY(id,'IsUserTable')=1)droptabletciGo/*以下创建TC1数据库表*/createtabletcl(TNOchar(2),CNOchar(2),primarykey(TNO,CNO))；Go/*为S1(学生表)数据表添加数据*/InsertintosiValues('S2','钱尔','男',18,'信息');InsertintosiValues('s3','孙姗','女',20，'信息');InsertintosiValues（'s4','李思’,'男',21,'自动化'）;InsertintosiValues（'s5','周武','男',19,'计算机'）;InsertintosiValues（'s6','吴丽','女',20,'自动化'）;InsertintosiValues（'s7','李奇','男',18,'外语'）;InsertintosiValues（'s8','古名','男',null,'计算机'）;InsertintosiValues（'s9','赵亦','女',i7,'计算机'）;/*为cl（课程表）数据表添加数据*/InsertintociValues（'ci','程序设计',60）;InsertintociValues（'c2','微机原理',80）;InsertintociValues（'c3','数字逻辑',60）;InsertintociValues（'c4','数据结构',80）;InsertintociValues（'c5','数据库',60）;InsertintociValues（'c6','编译原理',60）;InsertintociValues（'c7','操作系统',60）;Go/*为sc1数据表添加数据*/InsertintosciValues（'s2','c5',57）;InsertintosciValues（'s2','c6',80）;InsertintosciValues（'s2','c7',null）;InsertintosciValues（'s3','ci',75）;InsertintosciValues（'s3','c2',70）;InsertintosciValues（'s3','c4',85）;InsertintosciValues（'s4','ci',93）;Insertintosc1Values('s4','c2',85);Insertintosc1Values('s4','c3',83);Insertintosc1Values('s5','c2',89);Insertintosc1Values('s9','c1',90);Insertintosc1Values('s9','c2',85);Go/*为T1(教师表)数据表添加数据*/Insertintot1Values('t5','张兰','女',39,'副教授’,1300,2000，'信息');Insertintot1Values('t4','张雪','女',51,'教授',1600,3000,'自动化');Insertintot1Values('t3','刘伟','男',30,'讲师',900,1200,'计算机');Insertintot1Values('t2','王平','女',28,'讲师',800,1200,'信息');Insertintot1Values('t1','李力','男',47,'教授',1500,3000,'计算机');Go/*为tc1(任课表)数据表添加数据*/Insertintotc1Values('t2','c5');Insertintotc1Values('t3','c1');Insertintotc1Values('t3','c5');Insertintotc1Values('t4','c2');Insertintotc1Values('t4','c3');Insertintotc1Values('t5','c5');Insertintotc1Values('t5','c7');Insertintotc1Values('t1','c1');Insertintotc1Values('t1','c4');GoCREATETRIGGERTRIGGER_SONStudentFORupdateASifupdate(sno)begindeclare@sno_newchar(9),@sno_oldchar(9)select@sno_new=snofrominsertedselect@sno_old=snofromdeletedupdateSCsetsno=@sno_newwheresno=@sno_oldendCREATETRIGGERgrade_scOnscforinsert,updateAsdeclare@grade_readsmallintSelect@grade_read=gradefrominsertedIf@grade_read>=0and@grade_read<=100BeginPrint"操作完成！”ReturnEndprint”成绩超出0-100之间！请重新输入.”Rollbacktransaction创建account表：createtableaccount(accountnumchar(11)primarykey,totalfloat);创建存储过程：P248Createproceduretransfer(@inaccountvarchar(11),@outaccountvarchar(11),@amountfloat)2MiJTy0dTTAsdeclare@totalDepositfloatBeginselect@totalDeposit=totalfromaccountwhereaccountnum=@outaccountgiiSpiue7Aif@totalDepositisnullbeginRollbackReturnendif@totalDeposit<@amountbeginRollbackReturnendupdateaccountsettotal=tot