南工大數(shù)據(jù)庫實驗+考試-上機實驗指導(dǎo)-實驗內(nèi)容和思考參考答案

實驗2：SQLServer數(shù)據(jù)庫的管理二、實驗內(nèi)容及步驟4．單擊“新建查詢”打開查詢設(shè)計器窗口，在查詢設(shè)計器窗口中使用Transact-SQL語句CREATEDATABASE創(chuàng)建studb數(shù)據(jù)庫。然后通過系統(tǒng)存儲過程sp_helpdb查看系統(tǒng)中的數(shù)據(jù)庫信息。CREATEDATABASEstudbON(NAME=studb_dat,FILENAME='C:\DataBase\studb.mdf')EXECsp_helpdb5．在查詢設(shè)計器中使用Transact-SQL語句ALTERDATABASE修改studb數(shù)據(jù)庫的設(shè)置，指定數(shù)據(jù)文件大小為5MB，最大文件大小為20MB，自動遞增大小為1MB。ALTERDATABASEstudbMODIFYFILE(NAME=studb_data,SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=1MB)6．在查詢設(shè)計器中為studb數(shù)據(jù)庫增加一個日志文件，命名為studb_Log2，大小為5MB，最大文件大小為10MB。ALTERDATABASEstudbADDLOGFILE(NAME=studb_log2,FILENAME=studb_log2.ldf,SIZE=5MB,MAXSIZE=10MB)8．使用Transact-SQL語句DROPDATABASE刪除student_db數(shù)據(jù)庫。DROPDATABASEstudent_db實驗3SQLServer數(shù)據(jù)表的管理二、實驗內(nèi)容及步驟5．使用Transact-SQL語句CREATETABLE在studentsdb數(shù)據(jù)庫中創(chuàng)建grade表。CREATETABLEgrade( [學(xué)號][char](4)NULL, [課程編號][char](4)NULL, [分?jǐn)?shù)][char](5)NULL)ON[PRIMARY]8．使用Transact-SQL語句INSERTINTO...VALUES向studentsdb數(shù)據(jù)庫的grade表插入數(shù)據(jù)：學(xué)號為0004，課程編號為0001，分?jǐn)?shù)為80。INSERTgradeVALUES('0004','0001','80')9．使用Transact-SQL語句ALTERTABLE修改curriculum表的“課程編號”列，使之為非空。ALTERTABLEcurriculumALTERCOLUMN課程編號NOTNULL10．使用Transact-SQL語句ALTERTABLE修改grade表的“分?jǐn)?shù)”列，使其數(shù)據(jù)類型為real。ALTERTABLEgradeALTERCOLUMN分?jǐn)?shù)real11．使用Transact-SQL語句ALTERTABLE修改student_info表的“姓名”列，使其列名為“學(xué)生姓名”，數(shù)據(jù)類型為vachar(10)，非空。altertablestudent_infoadd學(xué)生姓名varchar(10)NOTNULL;updatestudent_infoset學(xué)生姓名=姓名;altertablestudent_infodropcolumn姓名;12．使用Transact-SQL語句DELETE刪除studentsdb數(shù)據(jù)庫的grade表中學(xué)號為0004的成績記錄。DELETEgradeWHERE學(xué)號='0004'13．使用Transact-SQL語句UPDATE修改studentsdb數(shù)據(jù)庫的grade表中學(xué)號為0003、課程編號為0005、分?jǐn)?shù)為90的成績記錄。UPDATEgradeSET分?jǐn)?shù)=90WHERE學(xué)號='0003'and課程編號='0005'14．使用Transact-SQL語句ALTER...ADD為studentsdb數(shù)據(jù)庫的grade表添加一個名為“備注”的數(shù)據(jù)列，其數(shù)據(jù)類型為VARCHAR(20)。ALTERTABLEgradeADD備注VARCHAR(20)NULL15．分別使用SQLServer管理平臺和Transact-SQL語句DROPTABLE刪除studentsdb數(shù)據(jù)庫中g(shù)rade表。DROPTABLEstudentsdb.dbo.grade三、實驗思考1．使用Transact-SQL語句刪除在studentsdb數(shù)據(jù)庫的grade表添加的“備注”數(shù)據(jù)列。ALTERTABLEgradeDROPCOLUMN備注實驗4數(shù)據(jù)查詢二、實驗內(nèi)容及步驟2．在studentsdb數(shù)據(jù)庫中使用SELECT語句進行基本查詢。（1）在student_info表中，查詢每個學(xué)生的學(xué)號、姓名、出生日期信息。SELECT學(xué)號,姓名,出生日期FROMstudent_info（2）查詢學(xué)號為0002的學(xué)生的姓名和家庭住址。SELECT姓名,家庭住址FROMstudent_infoWHERE學(xué)號='0002'（3）找出所有男同學(xué)的學(xué)號和姓名。SELECT學(xué)號,姓名FROMstudent_infoWHERE性別='男'3．使用SELECT語句進行條件查詢（1）在grade表中查找分?jǐn)?shù)在80~90范圍內(nèi)的學(xué)生的學(xué)號和分?jǐn)?shù)。SELECT學(xué)號,分?jǐn)?shù)FROMgradeWHERE(分?jǐn)?shù)BETWEEN80AND90)（2）在grade表中查詢課程編號為0003的學(xué)生的平均分。SELECTSUM(分?jǐn)?shù))/COUNT(*)AS平均分FROMgradeWHERE(課程編號='0003')（3）在grade表中查詢學(xué)習(xí)各門課程的人數(shù)。SELECT課程編號,COUNT(*)AS人數(shù)FROMgradeGROUPBY課程編號（4）將學(xué)生按出生日期由大到小排序。SELECT*FROMstudent_infoORDERBY出生日期DESC（5）查詢所有姓“張”的學(xué)生的學(xué)號和姓名。SELECT*FROMstudent_infoWHERE姓名LIKE'張%'4．對student_info表，按性別順序列出學(xué)生的學(xué)號、姓名、性別、出生日期及家庭住址，性別相同的按學(xué)號由小到大排序。SELECT學(xué)號,姓名,性別,出生日期,家庭住址FROMstudent_infoORDERBY性別,學(xué)號5．使用GROUPBY查詢子句列出各個學(xué)生的平均成績。SELECT學(xué)號,SUM(分?jǐn)?shù))/COUNT(*)AS平均成績FROMgradeGROUPBY學(xué)號6．使用UNION運算符將student_info表中姓“張”的學(xué)生的學(xué)號、姓名與curriculum表的課程編號、課程名稱返回在一個表中，且列名為u_編號、u_名稱，如圖1-8所示。圖1-8聯(lián)合查詢結(jié)果集SELECT學(xué)號ASu_編號,姓名ASu_名稱FROMstudent_infoWHERE(姓名LIKE'張%')UNIONSELECT課程編號ASu_編號,課程名稱ASu_名稱FROMcurriculum8．連接查詢（1）查詢分?jǐn)?shù)在80~90范圍內(nèi)的學(xué)生的學(xué)號、姓名、分?jǐn)?shù)。SELECTstudent_info.學(xué)號，姓名，分?jǐn)?shù)FROMstudent_info，gradeWHEREstudent_info.學(xué)號=grade.學(xué)號AND分?jǐn)?shù)BETWEEN80AND90（2）查詢學(xué)習(xí)“C語言程序設(shè)計”課程的學(xué)生的學(xué)號、姓名、分?jǐn)?shù)。SELECTstudent_info.學(xué)號，姓名，分?jǐn)?shù)FROMstudent_infoINNERJOINgradeONstudent_info.學(xué)號=grade.學(xué)號INNERJOINcurriculumON課程名稱='C語言程序設(shè)計'（3）查詢所有男同學(xué)的選課情況，要求列出學(xué)號、姓名、課程名稱、分?jǐn)?shù)。SELECTa.學(xué)號,a.姓名,c.課程名稱,b.分?jǐn)?shù)FROMstudent_infoaINNERJOINgradebONa.學(xué)號=b.學(xué)號INNERJOINcurriculumcONb.課程編號=c.課程編號WHERE(a.性別='男')實驗5：索引與視圖二、實驗內(nèi)容及操作步驟1．分別使用SQLServer管理平臺和Transact-SQL語句為studentsdb數(shù)據(jù)庫的student_info表和curriculum表創(chuàng)建主鍵索引。CREATEUNIQUECLUSTEREDINDEX[PK_curriculum]ON[curriculum]([課程編號])10．在studentsdb數(shù)據(jù)庫中，使用Transact-SQL語句CREATEVIEW建立一個名為v_stu_c的視圖，顯示學(xué)生的學(xué)號、姓名、所學(xué)課程的課程編號，并利用視圖查詢學(xué)號為0003的學(xué)生情況。CREATEVIEWv_stu_cASSELECTa.學(xué)號,a.姓名,b.課程編號FROMstudent_infoaINNERJOINgradebONa.學(xué)號=b.學(xué)號SELECT*FROMv_stu_cWHERE(學(xué)號='0003')11．基于student_info表、curriculum表和grade表，建立一個名為v_stu_g的視圖，視圖中具有所有學(xué)生的學(xué)號、姓名、課程名稱、分?jǐn)?shù)。使用視圖v_stu_g查詢學(xué)號為0001的學(xué)生的所有課程與成績，如圖1-9所示。圖1-9學(xué)號為0001的學(xué)生的視圖信息CREATEVIEWv_stu_gASSELECTa.學(xué)號,a.姓名,c.課程名稱,b.分?jǐn)?shù)FROMstudent_infoaINNERJOINgradebONa.學(xué)號=b.學(xué)號INNERJOINcurriculumcONb.課程編號=c.課程編號SELECT*FROMv_stu_gWHERE學(xué)號=’0001重要12．使用Transact-SQL語句修改視圖v_stu_c，使之顯示學(xué)號、姓名、每個學(xué)生所學(xué)課程數(shù)目。ALTERVIEWv_stu_cASSELECTa.學(xué)號,a.姓名,COUNT(*)AS課程數(shù)目FROMstudent_infoaINNERJOINgradebONa.學(xué)號=b.學(xué)號GROUPBYa.學(xué)號,a.姓名13．使用Transact-SQL語句ALTERVIEW修改視圖v_stu_i，使其具有列名學(xué)號、姓名、性別。ALTERVIEWv_stu_i(學(xué)號，姓名，性別)ASSELECT學(xué)號，姓名，性別FROMstudent_info14．使用系統(tǒng)存儲過程sp_rename將視圖v_stu_i更名為v_stu_info。sp_renamev_stu_i，v_stu_info21．利用視圖v_stu_i為student_info表添加一行數(shù)據(jù)：學(xué)號為0015、姓名為陳婷、性別為女。INSERTINTOv_stu_iVALUES(‘0015’,’陳婷’,’女’)22．利用視圖v_stu_i刪除學(xué)號為0015的學(xué)生記錄。DELETEFROMv_stu_iWHERE(學(xué)號='0015')23．利用視圖v_stu_g修改姓名為劉衛(wèi)平的學(xué)生的高等數(shù)學(xué)的分?jǐn)?shù)為84。UPDATEv_stu_gSET分?jǐn)?shù)=84WHERE姓名='劉衛(wèi)平'AND課程名稱='高等數(shù)學(xué)'24．使用Transact-SQL語句DROPVIEW刪除視圖v_stu_c和v_stu_g。DROPVIEWv_stu_cDROPVIEWv_stu_g實驗8：存儲過程和觸發(fā)器二、實驗內(nèi)容及步驟3．使用studentsdb數(shù)據(jù)庫中的student_info表、curriculum表、grade表。（1）創(chuàng)建一個存儲過程stu_grade，查詢學(xué)號為0001的學(xué)生的姓名、課程名稱、分?jǐn)?shù)。（2）執(zhí)行存儲過程stu_grade，查詢0001學(xué)生的姓名、課程名稱、分?jǐn)?shù)。（3）使用系統(tǒng)存儲過程sp_rename將存儲過程stu_grade更名為stu_g。CREATEPROCEDUREstu_gradeASSELECTa.學(xué)號,c.課程名稱,b.分?jǐn)?shù)FROMstudent_infoaINNERJOINgradebONa.學(xué)號=b.學(xué)號INNERJOINcurriculumcONb.課程編號=c.課程編號WHERE(a.學(xué)號='0001')EXECstu_gradesp_renamestu_grade,stu_g4．使用student_info表、curriculum表、grade表。（1）創(chuàng)建一個帶參數(shù)的存儲過程stu_g_p，當(dāng)任意輸入一個學(xué)生的姓名時，將從3個表中返回該學(xué)生的學(xué)號、選修的課程名稱和課程成績。（2）執(zhí)行存儲過程stu_g_p，查詢“劉衛(wèi)平”的學(xué)號、選修課程和課程成績。（3）使用系統(tǒng)存儲過程sp_helptext，查看存儲過程stu_g_p的文本信息。CREATEPROCEDUREstu_g_p@stu_namevarchar(8)ASSELECTa.學(xué)號,c.課程名稱,b.分?jǐn)?shù)FROMstudent_infoaINNERJOINgradebONa.學(xué)號=b.學(xué)號INNERJOINcurriculumcONb.課程編號=c.課程編號WHERE(a.姓名=@stu_name)EXECstu_g_p‘劉衛(wèi)平’sp_helptextstu_g_p5．使用student_info表。（1）創(chuàng)建一個加密的存儲過程stu_en，查詢所有男學(xué)生的信息。（2）執(zhí)行存儲過程stu_en，查看返回學(xué)生的情況。（3）使用Transact-SQL語句DROPPROCEDURE刪除存儲過程stu_en。CREATEPROCEDUREstu_enWITHENCRYPTIONASSELECT*FROMstudent_infoWHERE性別='男'EXECstu_enDROPPROCEDUREstu_en6．使用grade表。（1）創(chuàng)建一個存儲過程stu_g_r，當(dāng)輸入一個學(xué)生的學(xué)號時，通過返回輸出參數(shù)獲取該學(xué)生各門課程的平均成績。CREATEPROCstu_g_r@stu_novarchar(8)=NULL,@stu_scorerealOUTPUTASSELECT@stu_score=AVG(分?jǐn)?shù))FROMgradeWHERE(學(xué)號=@stu_no)（2）執(zhí)行存儲過程stu_g_r，輸入學(xué)號0002。DECLARE@scorerealEXECstu_g_r'0002',@scoreOUTPUT（3）顯示0002號學(xué)生的平均成績。SELECT@score8．為grade表建立一個名為insert_g_tr的INSERT觸發(fā)器，當(dāng)用戶向grade表中插入記錄時，如果插入的是在curriculum表中沒有的課程編號，則提示用戶不能插入記錄，否則提示記錄插入成功。在進行插入測試時，分別輸入以下數(shù)據(jù)：學(xué)號課程編號分?jǐn)?shù)00040003760005000769觀察插入數(shù)據(jù)時的運行情況，說明為什么？CREATETIGGERinsert_g_trONgradeFORUPDATE,INSERTASDECLARE@fkc_idvarchar(8)SET@fkc_id=(SELECT課程編號FROMINSERTED)IFEXISTS(SELECT*FROMcurriculumWHERE課程編號=@fkc_id)BEGINPRINT‘記錄插入成功’ENDELSEBEGINRAISERROR(‘無此課程，不能插入記錄’,16,1)END9．為curriculum表創(chuàng)建一個名為del_c_tr的DELETE觸發(fā)器，該觸發(fā)器的作用是禁止刪除curriculum表中的記錄。CREATETRIGGERdel_c_trONcurriculumFORDELETEASRAISERROR(‘禁止刪除curriculum表中