SQL数据库期末复习备考必备

数据库的创建：createDATABASEstuManage表的创建：usestuManagegocreatetabledepartment数据的插入：usestuManageinsertintodepartment(dNO,dName,dTel)values('115500','金融系','02085560371')数据修改：usestuManageupdateteachersettRank='副教授'wheretBirth>'1979/12/31'数据删除：usestuManagedeletedepartmentwheredName='金融系'数据库的备份：BackupdatabasestuManageTOdisk='F:\DATA\HOMEWORK1\Manage.bak'withformat,name='数据的完整备份'查询：1、查询100165专业的学生的学号、姓名、性别和出生年月信息2、查询职称不是教授的所有教师的全部信息3、查询不姓陈的学生学号和姓名信息4、查询100165专业中1991年之前出生的学生的学号、姓名、性别及出生年月信息5、查询所有学生的序号、姓名及年龄信息，其中年龄由出生年月转换得到6、查询所有学生的学号、姓名、性别及出生年月信息，按照专业号升序、出生年月降序排列7、查询091650102同学的所有课程的平均分、最高分和最低分3、查询姓陈而且姓名是三个字的同学的学号和姓名SELECTsno,snameSELECTsno,snameFROMstudentWHEREsname_like’陈%’_and_len(sname)_=3__________5、查询开课号为0901120001的成绩排名前三名的学号及成绩信息SELECTSELECTtop3sno,gradeFROMskcWHEREkno=‘0901120001’ORDERBY__grade___desc_____7、查询出姓张的学生或者姓名中带有“秋”的学生select*fromstudentwheresnamelike'%秋%'orsnamelike'张%'8、查询出每门课程的平均分、最低分、最高分Selectkno,max(grade),min(grade),avg(grade)Fromskcgroupbykno9、查询出平均分大于80分，且至少选修了2门课程的学生学号。selectstudent.sno,AVG(grade)as平均分fromstudent,skcwherestudent.sno=skc.snogroupbystudent.snoHavingAVG(grade)>80andcount(*)>=2多表查询：1、查询每个学生的学号、姓名、性别、专业名称等信息2、查询每个学生的学号、姓名、性别、专业名称等信息3、查询每个学生的学号、姓名及不及格课程门数4、查询学生的学号、姓名及不及格课程名称3、使用SQL语句修改视图ALTERVIEWteacher1_viewASSELECTtno,tname,sex,birthday,tRankFROMteacherWHEREdno=‘060606’然后使用SELECT*FROMiec_teacher_view4、使用SQL语句删除视图DROPVIEWteacher1_view1、使用SQL语句为stuManag数据库的skc表的“grade”字段创建一个非聚集索引，命名为grade_index。CREATEINDEXgrade_indexONskc(grade)2、为skc表的“sno”和“kno”字段创建一个复合唯一索引，命名为sno_kno_index。CREATEUNIQUEINDEXsno_kno_indexONskc(sno,kno)3、使用系统存储过程sp_helpindex查看skc表上的索引信息。sp_helpindexskc4、使用系统存储过程sp_rename将索引grade_index更名为skc_grade_index。sp_rename'skc.grade_index','skc_grade_index','INDEX'5、使用SDROPINDEX删除索引skc_grade_index。DROPINDEXskc.skc_grade_index1、建立一个名为view_stu_info的视图，使视图显示学生姓名、性别和年龄。createviewview_stu_info(sname,ssex,sbirth)asselectsname,ssex,sbirthfromstudent2、建议一个名为view_avg_grade的视图，显示学生的学号和课程平均分。createviewview_avg_grade(sno,gavg)asselectsno,avg(grade)fromskcgroupbysno3、创建一个视图，包括计算机科学与技术系的所有学生的学号、姓名、专业。 createviewview_avg_all(sno,sname,mname)asselectsno,s.sname,mnamefromstudents,majormwherem.dno='100003'4、创建一个视图，包括所有学生名称、课程名称、教师名称及总评成绩。createviewview_avg_all1(sname,cname,tname,grade)asselectsname,cname,tname,gradefromskc,student,teacher,course1、在视图view_stu_info上查询出年龄小于20的学生，并分析所出现的结果。selectsnamefromview_stu_infowheresbirth<'1993/01/01'2、在view_avg_grade视图上查询平均成绩为80分以上的学生的学号和成绩selectsno,gavgfromview_avg_gradewheregavg>803、通过视图view_stu_info将学号为’091651101’的学生姓名改为吴明，并分析所出现的结果。updateview_stu_infosetsname='吴明'wheresno='091651101'4、在view_avg_grade视图上将学号为S1的学生的平均成绩改为90分，并分析所出现的结果。updateview_avg_gradesetgavg='90'wheresno='s1'1、请分析数据库各个表及字段的使用情况，然后为经常查询和分组的字段创建索引。createclusteredindexmycolumn_indexonmytable2、为一些表中不允许重复的字段创建唯一性索引。createuniqueclusteredindexmycolumn_indexonmytable创建存储过程和触发器：T-SQL创建存储过程的基本语法格式：CREATEPROC[EDURE]存储过程名称参数定义ASSQL语句语法格式：CREATETRIGGER触发器名称ON表或视图{FOR|AFTER|INSTEADOF}{[DELETE]，[INSERT]，[UPDATE]}ASSQL语句（一）存储过程的创建和执行1、创建下面的存储过程并执行，然后分析其功能1）创建CREATEPROCEDURElist_stu_courseASSELECTs.sno,s.sname,s.ssex,o,ame,skc.grade,k.kcreditFROMstudents,coursec,kaikek,skcWHEREs.sno=skc.snoANDskc.kno=k.knoANDo=oGO2)执行EXEClist_stu_course或者list_stu_course3）功能分析创建一个叫list_stu_course存储过程，定义学号，姓名，性别，开课号，开课名，课程成绩kcredit等参数，并以sno,cno,kno来建立连接，查询所建立的存储过程2、带参数存储过程并执行1）创建CREATEPROClist_student_department(@departmentchar(20))ASBEGINSELECTsno,sname,ssex,snative,mname,dnameFROMstudent,major,departmentWHEREstudent.mno=major.mnoANDmajor.dno=department.dnoANDdname=@departmentEND2)执行EXEClist_student_department@department=‘计算机’3）功能分析创建一个叫list_student_department存储过程，定义sno,sname,ssex,snative,mname,dname等参数，并以mno,dno来建立连接，查询存储过程中计算机系的学生的信息3、根据功能要求完成下面的任务在stuManag数据库中创建一个名为proc_1的存储过程，产生某门课程的选课学生情况列表，其中包括课程号、课程名、学号、姓名、成绩。要求输入某门课程的课程号，得到上述信息。1）创建：CREATEPROCEDUREproc_1_(@cnochar(10))___ASSELECTo,ame,s.sno,s.sname,skc.gradeFROMstudents,coursec,skc,kaikekWHEREs.sno=skc.snoANDskc.kno=k.knoANDo=oANDo=@cnoGO2）执行：EXECproc_1‘010101’或EXECproc_1_’010102’____4、根据功能要求，完成下面任务在stuManag数据库中，定义存储过程proc_2，要求实现输入学生学号，根据该学生所选课程的平均成绩显示提示信息，即如果平均成绩在60分以上，显示“此学生综合成绩合格！”，否则显示“此学生综合成绩不合格！”。1）创建：CREATEPROCEDUREproc_2@snochar(10),@savgintASSELECT@savg=avg(grade)FROMskcWHEREskc.sno=@snoIF@savg>=60PRINT‘此学生综合成绩合格！’ELSEPRINT‘此学生综合成绩不合格！’GO（二）管理存储过程1、查看存储过程代码EXECsp_helptext‘list_student_department’EXECsp_helptext@objname=‘list_student_department’2、修改存储过程ALTERPROClist_student_department@departmentchar(20)ASBEGINSELECTdname,mname, COUNT(*)‘学生人数’FROMstudent,major,departmentWHEREstudent.mno=major.mnoANDmajor.dno=department.dnoANDdname=@departmentGROUPBYdname,mnameEND3、删除存储过程DROPPROCEDURElist_stu_course

（三）触发器操作1、触发器创建及验证新建一个Test数据库，然后在Test数据库中完成下面内容。在查询分析器中输入下面的代码，思考其功能，运行后，查看输出结果。并描述每一部分的功能或者输出结果。1）创建触发器CREATETABLETestTable1(---创建表TestTable1)c1INT,c2CHAR(30))CREATETRIGGERtrig1ONTestTable1FORINSERT,UPDATE,DELETEasSELECT*FROMTestTable1--下面两个语句分开执行INSERTTestTable1VALUES(1,’林冲’)UPDATETestTable1SETc2=‘宋江’WHEREc1=12）inserted表和deleted表的使用CREATETRIGGERtrig1ONTestTable1FORINSERT,UPDATE,DELETEASPRINT‘inserted表：’SELECT*FROMINSERTEDPRINT‘deleted表：’SELECT*FROMDELETEDGO--下面三个语句分开执行INSERTTestTable1VALUES(2,’吴用’)UPDATETestTable1SETc2=‘关胜’WHEREc1=2DELETETestTable1WHEREc1=23）insert和update触发器CREATETABLETestTable2( c1INT, c2CHAR(10))GOCREATETRIGGERtrig2ONTestTable2FORINSERT,UPDATEASDECLARE@c1_1intSELECT@c1_1=c1FROMINSERTEDIF@c1_1<1OR@c1_1>108BEGINROLLBACKRAISERROR(‘c1值必须在1到108之间！’,16,1)ENDGO--下面三个语句分开执行INSERTTestTable2VALUES(200,’武松’)INSERTTestTable2VALUES(15,’武松’)UPDATETestTable2SETc1=300WHEREc2=‘武松’4）delete触发器INSERTstuVALUES(‘03170356’,’陈功’,’男’,17,’广东’)INSERTstudyVALUES(‘03170356’,1,88)INSERTstudyVALUES(‘03170356’,2,99)GOCREATETRIGGERtrig3ONstuFORDELETEASDELETEstudyWHEREstudy.sno=(SELECTsnoFROMDELETED)go--执行下面语句DELETEFROMstuWHEREsno=‘03170356’5）insteadof触发器CREATETRIGGERaaONTestTable2insteadofINSERT,UPDATE,DELETEASINSERTTestTable1VALUES(100,’1234’)--执行下面语句，然后查询TestTable2表INSERTINTOTestTable2VALUES(10,’宋江’)2、基于stuManag数据，根据功能要求，完成T-SQL语句1）创建触发器trigger_1，实现当修改学生表（student）中的数据时，显示提示信息“学生表被修改了”。CREATETRIGGERtrigger_1ONstudentFORupdateASPRINT‘学生课程表被修改了’GO2）创建触发器trigger_2，实现当删除课程表中某门学生的记录时，对应学生成绩表中所有有关该学生的记录均删除。CREATETRIGGERtrigger_2ONstudentFORDELETEASDELETEFROMskcWHEREskc.sno=(delete*fromcourse)GO3）创建触发器trigger_3，实现当修改学生课程表（course）中的某门课的课程号时，对应学生成绩表（skc）中的课程号也作修改。当修改学生课程表中记录时，相当于删除一条旧记录并插入一条新记录，删除的旧记录在deleted临时表中，插入的新记录在inserted临时表中。因此，可以通过在查询分析器的查询窗口中输入如下语句创建触发器：CREATETRIGGERtrigger_3ONcourseFORUPDATEASBEGINUPDATEskcSETkno=(SELECTknoFROM_skc________)WHEREkno.sno=(SELECTknofrom__course_______)ENDGO3、删除触发器trigger_1____________trigger_11、创建一个存储过程：当任意输入一个学生的姓名时，将返回该学生的学号、选修的课程名称和课程成绩。并执行该存储过程，查询“刘永强”的学号、选修课程和课程成绩。CREATEPROClist_student_a1(@a1char(20))ASBEGINSELECTs.sno,sname,mname,gradeFROMstudents,major,skcWHEREs.mno=major.mnoANDs.sno=skc.snoANDsname=@a1END执行EXEClist_student_a1@a1='刘永强'2、创建一个存储过程，要求实现如下功能：输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分<9，则显示“此学生所选总学分为XXX，学分不足！”，否则显示“此学生所选总学分为XXX，学分已足！”。并调用修改后的存储过程，显示“02020101”学生的总学分情况。CREATEPROCEDURElist_stu_a2@a21char(20),@a2intASselect@a2=k.kcreditfromkaikek,skcswheres.sno=@a21if@a2>9print'此学生所选总