南工大数据库实验+考试-上机实验指导-实验内容和思考参考答案

实验2：SQLServer数据库的管理二、实验内容及步骤4．单击“新建查询”打开查询设计器窗口，在查询设计器窗口中使用Transact-SQL语句CREATEDATABASE创建studb数据库。然后通过系统存储过程sp_helpdb查看系统中的数据库信息。CREATEDATABASEstudbON(NAME=studb_dat,FILENAME='C:\DataBase\studb.mdf')EXECsp_helpdb5．在查询设计器中使用Transact-SQL语句ALTERDATABASE修改studb数据库的设置，指定数据文件大小为5MB，最大文件大小为20MB，自动递增大小为1MB。ALTERDATABASEstudbMODIFYFILE(NAME=studb_data,SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=1MB)6．在查询设计器中为studb数据库增加一个日志文件，命名为studb_Log2，大小为5MB，最大文件大小为10MB。ALTERDATABASEstudbADDLOGFILE(NAME=studb_log2,FILENAME=studb_log2.ldf,SIZE=5MB,MAXSIZE=10MB)8．使用Transact-SQL语句DROPDATABASE删除student_db数据库。DROPDATABASEstudent_db实验3SQLServer数据表的管理二、实验内容及步骤5．使用Transact-SQL语句CREATETABLE在studentsdb数据库中创建grade表。CREATETABLEgrade( [学号][char](4)NULL, [课程编号][char](4)NULL, [分数][char](5)NULL)ON[PRIMARY]8．使用Transact-SQL语句INSERTINTO...VALUES向studentsdb数据库的grade表插入数据：学号为0004，课程编号为0001，分数为80。INSERTgradeVALUES('0004','0001','80')9．使用Transact-SQL语句ALTERTABLE修改curriculum表的“课程编号”列，使之为非空。ALTERTABLEcurriculumALTERCOLUMN课程编号NOTNULL10．使用Transact-SQL语句ALTERTABLE修改grade表的“分数”列，使其数据类型为real。ALTERTABLEgradeALTERCOLUMN分数real11．使用Transact-SQL语句ALTERTABLE修改student_info表的“姓名”列，使其列名为“学生姓名”，数据类型为vachar(10)，非空。altertablestudent_infoadd学生姓名varchar(10)NOTNULL;updatestudent_infoset学生姓名=姓名;altertablestudent_infodropcolumn姓名;12．使用Transact-SQL语句DELETE删除studentsdb数据库的grade表中学号为0004的成绩记录。DELETEgradeWHERE学号='0004'13．使用Transact-SQL语句UPDATE修改studentsdb数据库的grade表中学号为0003、课程编号为0005、分数为90的成绩记录。UPDATEgradeSET分数=90WHERE学号='0003'and课程编号='0005'14．使用Transact-SQL语句ALTER...ADD为studentsdb数据库的grade表添加一个名为“备注”的数据列，其数据类型为VARCHAR(20)。ALTERTABLEgradeADD备注VARCHAR(20)NULL15．分别使用SQLServer管理平台和Transact-SQL语句DROPTABLE删除studentsdb数据库中grade表。DROPTABLEstudentsdb.dbo.grade三、实验思考1．使用Transact-SQL语句删除在studentsdb数据库的grade表添加的“备注”数据列。ALTERTABLEgradeDROPCOLUMN备注实验4数据查询二、实验内容及步骤2．在studentsdb数据库中使用SELECT语句进行基本查询。（1）在student_info表中，查询每个学生的学号、姓名、出生日期信息。SELECT学号,姓名,出生日期FROMstudent_info（2）查询学号为0002的学生的姓名和家庭住址。SELECT姓名,家庭住址FROMstudent_infoWHERE学号='0002'（3）找出所有男同学的学号和姓名。SELECT学号,姓名FROMstudent_infoWHERE性别='男'3．使用SELECT语句进行条件查询（1）在grade表中查找分数在80~90范围内的学生的学号和分数。SELECT学号,分数FROMgradeWHERE(分数BETWEEN80AND90)（2）在grade表中查询课程编号为0003的学生的平均分。SELECTSUM(分数)/COUNT(*)AS平均分FROMgradeWHERE(课程编号='0003')（3）在grade表中查询学习各门课程的人数。SELECT课程编号,COUNT(*)AS人数FROMgradeGROUPBY课程编号（4）将学生按出生日期由大到小排序。SELECT*FROMstudent_infoORDERBY出生日期DESC（5）查询所有姓“张”的学生的学号和姓名。SELECT*FROMstudent_infoWHERE姓名LIKE'张%'4．对student_info表，按性别顺序列出学生的学号、姓名、性别、出生日期及家庭住址，性别相同的按学号由小到大排序。SELECT学号,姓名,性别,出生日期,家庭住址FROMstudent_infoORDERBY性别,学号5．使用GROUPBY查询子句列出各个学生的平均成绩。SELECT学号,SUM(分数)/COUNT(*)AS平均成绩FROMgradeGROUPBY学号6．使用UNION运算符将student_info表中姓“张”的学生的学号、姓名与curriculum表的课程编号、课程名称返回在一个表中，且列名为u_编号、u_名称，如图1-8所示。图1-8联合查询结果集SELECT学号ASu_编号,姓名ASu_名称FROMstudent_infoWHERE(姓名LIKE'张%')UNIONSELECT课程编号ASu_编号,课程名称ASu_名称FROMcurriculum8．连接查询（1）查询分数在80~90范围内的学生的学号、姓名、分数。SELECTstudent_info.学号，姓名，分数FROMstudent_info，gradeWHEREstudent_info.学号=grade.学号AND分数BETWEEN80AND90（2）查询学习“C语言程序设计”课程的学生的学号、姓名、分数。SELECTstudent_info.学号，姓名，分数FROMstudent_infoINNERJOINgradeONstudent_info.学号=grade.学号INNERJOINcurriculumON课程名称='C语言程序设计'（3）查询所有男同学的选课情况，要求列出学号、姓名、课程名称、分数。SELECTa.学号,a.姓名,c.课程名称,b.分数FROMstudent_infoaINNERJOINgradebONa.学号=b.学号INNERJOINcurriculumcONb.课程编号=c.课程编号WHERE(a.性别='男')实验5：索引与视图二、实验内容及操作步骤1．分别使用SQLServer管理平台和Transact-SQL语句为studentsdb数据库的student_info表和curriculum表创建主键索引。CREATEUNIQUECLUSTEREDINDEX[PK_curriculum]ON[curriculum]([课程编号])10．在studentsdb数据库中，使用Transact-SQL语句CREATEVIEW建立一个名为v_stu_c的视图，显示学生的学号、姓名、所学课程的课程编号，并利用视图查询学号为0003的学生情况。CREATEVIEWv_stu_cASSELECTa.学号,a.姓名,b.课程编号FROMstudent_infoaINNERJOINgradebONa.学号=b.学号SELECT*FROMv_stu_cWHERE(学号='0003')11．基于student_info表、curriculum表和grade表，建立一个名为v_stu_g的视图，视图中具有所有学生的学号、姓名、课程名称、分数。使用视图v_stu_g查询学号为0001的学生的所有课程与成绩，如图1-9所示。图1-9学号为0001的学生的视图信息CREATEVIEWv_stu_gASSELECTa.学号,a.姓名,c.课程名称,b.分数FROMstudent_infoaINNERJOINgradebONa.学号=b.学号INNERJOINcurriculumcONb.课程编号=c.课程编号SELECT*FROMv_stu_gWHERE学号=’0001重要12．使用Transact-SQL语句修改视图v_stu_c，使之显示学号、姓名、每个学生所学课程数目。ALTERVIEWv_stu_cASSELECTa.学号,a.姓名,COUNT(*)AS课程数目FROMstudent_infoaINNERJOINgradebONa.学号=b.学号GROUPBYa.学号,a.姓名13．使用Transact-SQL语句ALTERVIEW修改视图v_stu_i，使其具有列名学号、姓名、性别。ALTERVIEWv_stu_i(学号，姓名，性别)ASSELECT学号，姓名，性别FROMstudent_info14．使用系统存储过程sp_rename将视图v_stu_i更名为v_stu_info。sp_renamev_stu_i，v_stu_info21．利用视图v_stu_i为student_info表添加一行数据：学号为0015、姓名为陈婷、性别为女。INSERTINTOv_stu_iVALUES(‘0015’,’陈婷’,’女’)22．利用视图v_stu_i删除学号为0015的学生记录。DELETEFROMv_stu_iWHERE(学号='0015')23．利用视图v_stu_g修改姓名为刘卫平的学生的高等数学的分数为84。UPDATEv_stu_gSET分数=84WHERE姓名='刘卫平'AND课程名称='高等数学'24．使用Transact-SQL语句DROPVIEW删除视图v_stu_c和v_stu_g。DROPVIEWv_stu_cDROPVIEWv_stu_g实验8：存储过程和触发器二、实验内容及步骤3．使用studentsdb数据库中的student_info表、curriculum表、grade表。（1）创建一个存储过程stu_grade，查询学号为0001的学生的姓名、课程名称、分数。（2）执行存储过程stu_grade，查询0001学生的姓名、课程名称、分数。（3）使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。CREATEPROCEDUREstu_gradeASSELECTa.学号,c.课程名称,b.分数FROMstudent_infoaINNERJOINgradebONa.学号=b.学号INNERJOINcurriculumcONb.课程编号=c.课程编号WHERE(a.学号='0001')EXECstu_gradesp_renamestu_grade,stu_g4．使用student_info表、curriculum表、grade表。（1）创建一个带参数的存储过程stu_g_p，当任意输入一个学生的姓名时，将从3个表中返回该学生的学号、选修的课程名称和课程成绩。（2）执行存储过程stu_g_p，查询“刘卫平”的学号、选修课程和课程成绩。（3）使用系统存储过程sp_helptext，查看存储过程stu_g_p的文本信息。CREATEPROCEDUREstu_g_p@stu_namevarchar(8)ASSELECTa.学号,c.课程名称,b.分数FROMstudent_infoaINNERJOINgradebONa.学号=b.学号INNERJOINcurriculumcONb.课程编号=c.课程编号WHERE(a.姓名=@stu_name)EXECstu_g_p‘刘卫平’sp_helptextstu_g_p5．使用student_info表。（1）创建一个加密的存储过程stu_en，查询所有男学生的信息。（2）执行存储过程stu_en，查看返回学生的情况。（3）使用Transact-SQL语句DROPPROCEDURE删除存储过程stu_en。CREATEPROCEDUREstu_enWITHENCRYPTIONASSELECT*FROMstudent_infoWHERE性别='男'EXECstu_enDROPPROCEDUREstu_en6．使用grade表。（1）创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生各门课程的平均成绩。CREATEPROCstu_g_r@stu_novarchar(8)=NULL,@stu_scorerealOUTPUTASSELECT@stu_score=AVG(分数)FROMgradeWHERE(学号=@stu_no)（2）执行存储过程stu_g_r，输入学号0002。DECLARE@scorerealEXECstu_g_r'0002',@scoreOUTPUT（3）显示0002号学生的平均成绩。SELECT@score8．为grade表建立一个名为insert_g_tr的INSERT触发器，当用户向grade表中插入记录时，如果插入的是在curriculum表中没有的课程编号，则提示用户不能插入记录，否则提示记录插入成功。在进行插入测试时，分别输入以下数据：学号课程编号分数00040003760005000769观察插入数据时的运行情况，说明为什么？CREATETIGGERinsert_g_trONgradeFORUPDATE,INSERTASDECLARE@fkc_idvarchar(8)SET@fkc_id=(SELECT课程编号FROMINSERTED)IFEXISTS(SELECT*FROMcurriculumWHERE课程编号=@fkc_id)BEGINPRINT‘记录插入成功’ENDELSEBEGINRAISERROR(‘无此课程，不能插入记录’,16,1)END9．为curriculum表创建一个名为del_c_tr的DELETE触发器，该触发器的作用是禁止删除curriculum表中的记录。CREATETRIGGERdel_c_trONcurriculumFORDELETEASRAISERROR(‘禁止删除curriculum表中