大型数据库资料打印.docx

【例】创立一个students数据库，该数据库有1个10MB>1个20MB的数据文件和2个IoMB的日志文件。数据文件的逻辑名称为：Studentl和student2;物理名称为：StudentLmdf和student2.ndf;主文件是student由PRIMARY指定；两个数据文件的最大尺寸分别为无限大和IOoMB,增长速度分别为10%和1MB。事务日志文件逻辑名称为StIldelItlOgl和StUdeIltlOg2,物理文件名为StlldentlOgI.Idf和StiIdelltlog2.ldf,最大尺寸分别为50MB,增长速度分别为IMBe数据文件存放在D:data下,日志文件存放在E:data。createdatabasestudentsONPRIMARY(NAME=tSTUDENTl1,FILENAME=DAdataVSTUDENTLMDFt,SIZE=10MB,Maxsize=Unlimited,FILEGROVVTH=10%),(NAME=SrUDENT2FILENAME=,DdataSTUDENT2.NDF,SIZE=20MB,Maxsize=100MB,Filegrovvth=IMB)logON(NAME=,STUDENTLOG,FILENAMETeAdataVSTUDENTLOGLLDF',SIZE=10MB,MAXSIZE=50MB,Filegrovvth=IMB),(NAME=tSTUDENTLOGZ',FlLENAME='e:dataSTUDENTLOG2.LDF',SIZE=10MB,MAXSIZE=50MB,Filegrowth=IMB)go工程实训创立名为“goods"的数据库，要求：1、主数据文件名为goods_data.MDF,存放在e：目录下，初始值大小为5MB,增长方式为按照10%的比例增长;2、次数据文件名为goods_datal.NDF和goods_data2.NDF,都存放在D：目录下，初始大小为IMB,增长方式为按2MB的增量增长，文件组为aa；3、日志文件名goodsog.LDF,都存放在E：目录下，初始大小为3MB,增长方式为按照IMB的增量增长。要求分别使用企业管理器和Transact-SQL语句，并记录完整语句。4、对上述数据库分别进展查看、别离、附加、脱机、联机、收缩、删除等操作。CREATEDATABASEGOODSONPRIMARY(NAME=,goods-data.MDF,FlLENAME='C:goods_data.MDF、SIZE=5,FILEGROWTH=10%),FILEGROUPaa(NAME=,good-data2.NDE,FILENAME='D:good_data2.NDF',SIZE=I,FILEGROVVTH=2),(NAME=,good-datal.NDFFILENAME='D:good_datal.NDF',SIZE=I,FILEGROWTH=2)1.OGON(NAME=,goodsJog.LDF,FlLENAME=tEAgoodsJogXDF',SIZE=3,Filegrovvth=I)在数据库XSCj中的表“学生基本信息表"中定义学生性别列只能是“男"或"女"，如果用户输入其它值，系统均提示用户输入无效。usexscjALTERTABLE学生基本信息表addCoNSTRAlNTck_studJnfoCHECK(性别=N'男'or性别二N'女')/*删除约束名为aCK-StudJnfo*的约束的语句是：*/ALTERTABLE学生基本信息表DROPCONSTRAlNTcMtudJnfo例：在Employees表中给EmployeeID字段添加Check约束，使EmpIoyeeII)>100ALTERTABLEEmployeesWITHNOCHECKADDCONSTRAINTID_checkCHECK(EmployeeID>100)WITHNOCHECK防止根据现有记录验证该约束default约束的创立、查看、删除/*为表StudJnfo创立一个约束名为de-gender*的DEFAULT约束，要求性别(gender)的默认值为“男"。其TfQL语句*/ALTERTABLEstudJnfoADDCoNSTRAINTde_genderDEFAULTN男FORgender/*删除DEFAULT约束的语句：*/ALTERTABLEstudJnfoDROPCONSTRAINTde,genderPrimarykey约束的创立、查看、删除/*为表ShKLiIIfo中的学号(StluLid)定义主键。其TfQL语句如下：*/ALTERTABLEstudJnfoADDCONSTRA1NTPK_XHPRIMARYKEYCLUSTERED(StudJd)*删除该主键的TSQL语句*/ALTERTABLEstudJnfoDROPCONSTRAINTPK_XH例：在EmPloyeeS表中，给字段Name增加PRlMARYKEY约束，并设为非聚集索引(默认主键列为聚集索引)ALTERTABLEEmployeesADDCONSTRAINTPK.NamePRIMARYKEYNONCLUSTERED(Name)外键约束的创立、查看、删除/*为学生成绩表StlKLgrade与课程信息表IeSSOli_info创立外键约束，其TSQL语句如下：*/-为IessonJnfo表中的CourseJd列建设主键约束ALTERTABLEIessonJnfbADDCONSTRAlNTpk_courseJdPRIMARYKEYCLUSTERED(course.id)-为stud_grade表中的CourseJd列建设外键约束ALTERTABLEstudqradeADDCONSTRAINTfk_course_idFOREIGNKEY(CourseJd)REFERENCESlesson_info(course_id)UNIQUE约束的创立、查看、删除/*针对学生基本信息表的“号码”创立UNIQUE约束。具体语句如下：*/ALTERTABLEstudJnfoADDCONSTRAINTUN.telcodeUNIQUE(telcode)*删除UNlQUE约束的TfQL语句如下：*/ALTERTABLEstudJnfoDROPCONSTRAINTUN.telcode查询少数民族学生的基本情况。USEXSCJSELECT*FROM学生基本信息表WHERE族别。，汉族4、 ToP关键字的使用用于提取前几条，或者前百分比数据。语法：ToP(EXPRESSIoN)PERCENT例：查询前三名同学的所有信息SELECTTOP(3)*FROMstudent5、DISTINCT关键字的使用例：查询所有学生所在的城市，要求没有重复信息SELECTDISTINCTCityFROMstudent例：查询所有学生的姓名、学号以及出生年并按姓名升序和出生年降序排列SELECTName,Numb,YearFROMstudentORDERBYName,YearDESC例查询所有男生学号、姓名和年龄，并按出生日期进展排列(升序)的语句：USEstudentGOSELECTstud_id学号，name姓名,year(getdate()-year(birthday)年龄,birthday出生日期FROMStudJnfoWHEREgender=N'男'ORDERBYbirthdayASC例、统计计算机工程系各个专业的学生的平均入学成绩的语句：USEstudentGOSELECTsubstring(stud_id,5,2)专业编号，avg(mark)平均入学成绩FROMstudJnfbWHERESUbString(SnKUd,3,2)='0GROUPBYsubstring(studJd,5,2)-1、在学生基本信息表中查询所有姓，王，的学生的学号、姓名、家庭住址。select学号,姓名,家庭住址from学生基本信息表where姓名like'王'-2、查询所有出生日期在1985-03-10室W986-03-l(之间学生的学号和姓名。select学号,姓名,出生日期from学生基本信息表where出生日期between'03/10/1985'and'03/10/1986'-3、查询课程编号为，007号课程成绩高于80分的学生学号,姓名,课程编号,成绩。selecta.学号,a.姓名,b.课程编号,b.成绩from学生基本信息表a,成绩表bWherea.学号=b.学号and课程编号='002,and成绩>80-4、从学生基本信息表中查找所有新疆学生的记录。select*from学生基本信息表Where家庭住址like，%新疆，-5、从学生基本信息表中检索学号末位数字是奇数的学生。select*from学生基本信息表whereright(rtrim(学号),l)%2<>0-6、查询学生的学号,姓名，课程编号,成绩，并按成绩降序、姓名升序排列selecta.学号,a.姓名b课程编号,b.成绩from学生基本信息表a,成绩表bwherea.学号=b.学号orderby成绩desc,姓名-7、统计各民族男女生的学生总数，输出结果包含：性别、族别、人数。select性别,族别,count(*)as人数from学生基本信息表groupby族别，性别-8、在成绩表中统计每个学生的学号,平均成绩及总成绩。select学号,avg(成绩)平均成绩,sum(成绩)总成绩from成绩表groupby学号-9、查询总成绩小于500分的学生学号、姓名、总成绩及所在班级selecta.学号,a.姓名,c.班级名称,sum(成绩)总成绩from学生基本信息表a,成绩表b,班级表Cwherea.学号=b.学号anda.班级编号=c.班级编号groupbya.学号,a.姓名,c.班级名称havingSUm(成绩)<500-10、查询C语言程序设计课程成绩大于等于60分的学生姓名、课程名称及成绩。selecta.姓名,b.成绩C课程名称from学生基本信息表a,成绩表b,课程信息表Cwherea.学号=b.学号andb.课程编号=c.课程编号andc.课程名称=C语言程序设计，andb.成绩=60-11、列出成绩表中各门课程的学生学号、课程编号和成绩以及该门课程的平均分、最高分和最低分。select学号,成绩,课程编号from成绩表orderby课程编号computeavg（成绩）,max（成绩）,min（成绩）by课程编号-12、列出C语言程序设计课程成绩高于该门课程平均分的学生信息。selecta.*from学生基本信息表a,成绩表b,课程信息表Cwherea.学号=b.学号andb.课程编号二c.课程编号andc.课程名称=C语言程序设计'andb成绩（selectavg（成绩）from成绩表a,课程信息表bwherea.课程编号二b.课程编号andb.课程名称=C语言程序设计'）-13、查询选修了课程名称为C语言程序设计课程的学生基本信息,select*from学生基本信息表where学号in（SeIeCta.学号from成绩表a,课程信息表bwherea.课程编号二b.课程编号andb课程名称=C语言程序设计）-14、查询总成绩高于500分的学生基本信息select*from学生基本信息表where学号in（select学号from成绩表groupby学号havingsum（成绩）500）-15、查询未选修课程编号为0031的课程的学生情况。select*from学生基本信息表where学号notin（select学号from成绩表Where课程编号='003'）-16、查询比所有回族的学生年龄都大的学生记录。select*from学生基本信息表Whereyear（getdate（）-year（出生日期）（selectmax（year（getdate（）-year（出生日期）from学生基本信息表where族别='回族）-17、查询课程编号为，004，且成绩不低于课程编号为窗）3,的最低成绩的学生的姓名，系部名称和班级名称selecta.姓名,b.班级名称,c.系部名称from学生基本信息表a,班级表b,系部表Cwherea.班级编号=b.班级编号andb.系部编号=c.系部编号anda.学号in（select学号from成绩表where课程编号='004'and成绩=（selectmax（成绩）from成绩表where课程编号='003'）-18、对每个学生的选修的所有课程的成绩求平均值，并显示详细记录。select*from成绩表orderby学号computeavg（成绩）by学号-19、查询选修课程超过门且成绩都在分以上的学生的学号。select学号,count(*)as人数from成绩表groupby学号havingmin(成绩)>60andcount(*)>2-20、查询信息与计算机科学系学生的学号、姓名、家庭住址，并产生一个学生总人数行。selecta.学号,a.姓名,a.家庭住址from学生基本信息表a,班级表b,系部表Cwherea.班级编号=b.班级编号andb系部编号=c.系部编号and系部名称与信息与计算机科学，computeCOUnt(学号)使用INSERT语句插入数据,例I:在表StlI(Liilfo中插入一条记录(0401040125,张琳，女)。具体语句如下： INSERTINTOstudJnfo(StiId_id,name,gender) VALUES('0401040125',N'张琳'，N'女')将查询结果插入到表中 INSERTTABLE_NAME SELECTCoLUMN_NAME FROMTABLE.LIST WHERESEARCH.CONDITIONS例5：student,假设现在有newStudent,该表用于统计03001班学生的信息：Name、Numb、Year、City、Class.,求若何从student表中选取记录插入到newtable表中。CREATETABLEnewStudent(Namenchar(20),Numbnchar(20)notnull,Classnchar(30),Citynchar(25),Yearint)INSERTnewStudentSELECTName,Numb,Class,City,YearFROMstudentWHEREClass=,0300清空people表中的数据 TRUNCATETABLEPeoP加在数据库student中的stud_grade表中studjd列上创立名为StudJdJndex的聚集索引。USEstudentGOCREATECLUSTEREDINDEXStudJdJndexONstud_gradc(studjd)在数据库student中的stud_grade表中coursejd列上创立名为Courseindex的非聚集索引USEstudentGOCREATENONCLUSTEREDINDEXCourscIndcxONStiKLgrade(coursejd)-1、创立一个能向学生表中插入一条记录的存储过程InSerLStUdent,该过程需要5个参数，分别用来传递学号、姓名、性别、班级编号、出生日期5个值。usexscjgocreateprocinsert_studentxuehaochar(8),nameChar(12),Xingbchar(2),bjbhchar(8),birthdaydatetimeasbegininsert学生基本信息表(学号,姓名,性别,班级编号,出生日期)values(xuehao,name,xingb,bjbh,birthday)end-2、写出执行存储过程InSert.student的SQL语句，向学生表中插入一个新同学，并提供相应的实参值(实参值自定)。execinsert_student2210001郑佩佩',女,'20051003:2012/01/02'-3、创立一个向课程表中插入一门新课程的存储过程InSert.course,该存储过程需要四个参数，分别用来传递课程号、课程名、学分、系部编号，-但允许参数“学分”的默认值为2,即当执行存储过程InSerLeoUrSe时，未给参数“学分”提供实参值时，存储过程将按默认值2进展运算。gocreateprocinsert_coursekchchar(4),kcmchar(30),xbbhchar(2),xfdecimal(3,0)=2asinsert课程信息表(课程编号,课程名称,系部编号,学分)va1ues(kch,kcm,Xbbh,Xf)-4、执行存储过程Insert-course,向课程表Course中插入一门新课程。分两种情况写出相应的SQL命令：-第一种情况：提供四个实参值执行存储过程Insert-courseexecinsert_course'1104?手机编程？0,3-第二种情况：只提供三个实参值执行存储过程InSert.course,即：不提供与参数“学分”对应的实参值。execinsert_course'1105','嵌入式开发','01'-5、创立一个名为QUery.student的存储过程，该存储过程的功能是根据学号查询学生表中某一学生的姓名、班级编号、性别及出生日期。gocreateprocquery.studentxuehaochar(8)asselect姓名,班级编号,性别,出生日期from学生基本信息表where学号=xiIehaO-6、执行存储过程Query-student,查询学号为(X)OOO3”的学生的学号、班级号、性别及出生日期。写出完成此功能的SQL命令。execquery_student'000003,一7、问题：请创立存储过程，查看001课程考试平均分以及未通过考试的学员名单gocreateproc成绩查询asbeginSeleCtaVg(成绩)as平均分from成绩表where课程编号='001'SeIeCta.学号,姓名威绩from学生基本信息表a,成绩表bWherea.学号=b.学号andb.课程编号='001'and成绩60end-执行存储过程exec成绩查询-8、问题：修改上例：由于每次考试的难易程度不一样，每次的及格线可能随时变化(不再是60分)，这导致考试的评判结果也相应变化。- 分析：上述存储过程添加1个输入参数WrittenPaSS及格线gocreateproc成绩查询2WrittenPaSSintasbeginSeleCtaVg(成绩)as平均分from成绩表where课程编号='001'SeleCta.学号,姓名威绩from学生基本信息表a,成绩表bWherea.学号=b.学号andb.课程编号='001'and成绩WriltenPaSSend- 执行存储过程exec成绩查询270- 9、若何修改上例程序，根据每次统考指定的及格分数线，显示通过考试的学员名单并返回及格人数。(提示：用输出参数)gocreateproc成绩查询3WritIenPaSSint,PaSSCOUntintoutputasbeginSeIeCta.学号,姓名威绩from学生基本信息表a,成绩表bWherea.学号=b.学号andb.课程编号='001'and成绩=WrittenPaSSselectPaSSCOUnt=CoUnt(*)from学生基本信息表a,成绩表bWherea.学号=b.学号andb.课程编号='001'and成绩WritIenPaSSend-执行存储过程declare人数intexec成绩查询370,人数outputselect人数-10、思考：若何返回及格率(提示：用输出参数存储过程中用查询赋值语句分别求出及格人数与总人数，再求出及格率。)gocreateproc成绩查询4writtenPassdecimal(5,2),passcountintoutput,passratedecimal(6,2)outputasdeclarezongrenshudecimal(5,0)beginselectZongrenShU=COUnt(*)from学生基本信息表a,成绩表bwherea.学号=b.学号andb.课程编号='001'selectPaSSCOUnt=CoUnt(*)from学生基本信息表a,成绩表bWherea.学号=b.学号andb.课程编号='001'and成绩WritIenPaSSsetPaSSrate=PaSSCOUnt/zOngrenShUprintPaSSCoUnt/ZongrenShUend-执行存储过程declare及格人数加,及格比率decimal(6,2)exec成绩查询470,及格人数OUlPUt,及格比率outputprint'001课程及格人数为'+cast(及格人数asvarchar(50)+''+'及格比率为'+cast(及格比率*10OaSvarchar(50)+,%,-触发器题目-I.创立一个当学生表中插入一个新同学信息时能自动列出全部同学学生信息的触发器Display_triggergoCreatetriggerdisplay.triggerOn学生基本信息表ForinsertAsSelect*from学生基本信息表-2.调用上次实验创立的存储过程InSertstudent,向学生表中插入一新同学，看触发器DiSPIay_trigger是否被执行。execinsert_student'22100009,三Mffl,',20051003'2012-01-0209:06:18'-3.练习和管理触发器。-(1)建设数据库testdb,并在数据库中建设两个表：Txl(IDint,Namechar(10),Ageint)Person_counts(Person_countint)createdatabasetextdbusetextdbcreatetabletxl(idint,namechar(10),ageint)createtableperson_counts(person_countint)-(2)使用T-SQL编写一个触发器tr_person_ins,每当txl表中插入一行数据时，表Person,counts中对应的数量也相应地发生变化。一因为表PerSon_counts中无记录，所以先向里面插入一条记录，PerSon_count值为0insertperson_countsvalues(0)-创立触发器createtriggertr_person_insontxlforinsertasdeclarejiIushuintifexists(select*frominserted)beginSELECTjilushu=COUNT(*)fromtxlupdateperson_countssetperson_count=ji1ushuendelserollbacktran-验证触发器inserttxlvalues(2,'sandy2',40)goselect*fromperson_counts-(3)使用企业管理器创立一个触发器tr_person_deL每当txl表中删除记录时，Person,counts中对应的数量也相应地发生变化。gocreatetriggertr_person_delontxlfordeleteasdeclarejilushuintifexists(select*fromdeleted)beginSELECTjilushu=COUNT(*)fromtxlupdateperson_countssetperson_count=ji1ushuendelserollbacktran-验证触发器deletetxIwhereid=2goselect*fromperson_counts-(4)使用企业管理器查看触发器tjperSOLdel的内容，并将该触发器的内容加密。execsp_helptexttr_person_delgoaltertriggertr_person_delontxlwithencryptionfordeleteasdeclarejilushuintifexists(select*fromdeleted)beginSELECTjilushu=COUNT(*)fromtxlupdateperson_countssetperson_count=ji1ushuendelserollbacktran