数据库实验报告

实验一实验目的1.熟练掌握SQLServer200X的使用方法。2.熟练掌握使用SQLServer200X的对象资源管理器以及SQL的DDL完成数据库的创建、删除和连接方法；数据表的建立、删除；表结构的修改。3.加深对表的实体完整性、参照完整性和用户自定义完整性的理解。实验环境Windows7,SQLServer2008;实验内容 1．用企业管理器与SQL语句分别完成Part1andpart2ofSSD7exercise1。2．在以下实验中，使用学生-课程数据库，它描述了学生的基本信息、课程的基本信息及学生选修课程的信息。用对象资源管理器（企业管理器）与SQL语句分别完成。（1）

创建学生-课程数据库，数据文件名为student_data、大小10M，日志文件名为student_log、大小5M的新数据库，该数据库名为student。 （2）

创建学生关系表S：学号姓名性别年龄所在系SnoSnameSsexSagesdept （3）

创建课程关系表C：课程号课程名先行课学分CnoCnameCpnoccredit（4）

创建学生-课程表SC：学号课程号成绩SnoCnograde （5）

将以上创建表S、C、SC的SQL命令以.SQL文件的形式保存在磁盘上。在表中加入至少4个元组，第一个为本人信息。 （6）

在表S上增加“出生日期”属性列。 （7）

删除表S的“年龄”属性列。 （8）

删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。 （9）备份数据库，再还原。实验过程及结果（1）创建学生-课程数据库，数据文件名为student_data、大小10M，日志文件名为student_log、大小5M的新数据库，该数据库名为student。（2）分别创建学生关系表S,C,SC（3）右键选择表，在所打开表标签右键保存sql，选择磁盘。（4）在表S上增加“出生日期”属性列。对表s新建查询，输入ALTERTABLEsADDbirthdateDATETIME执行结果（5）删除表S的“年龄”属性列。对表S新建查询，输入altertablesdropcolumnsage执行结果（6）删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。删除表scusestudentgodroptablesc恢复结果实验二实验目的熟悉和掌握对数据表中数据的查询操作和SQL命令的使用，学会灵活熟练的使用SQL语句的各种形式；加深理解关系运算的各种操作（尤其是关系的选择，投影，连接和除运算）实验环境Windows7,SQLServer2008实验内容在表S，C，SC上完成以下查询：1．

查询学生的基本信息；2．

查询“CS”系学生的基本信息；3．

查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；4．

找出“CS”系年龄最大的学生，显示其学号、姓名；5．

找出各系年龄最大的学生，显示其学号、姓名；6．

统计“CS”系学生的人数；7．

统计各系学生的人数，结果按升序排列；8．

按系统计各系学生的平均年龄，结果按降序排列；9．

查询无先修课的课程的课程名和学时数；10．统计每位学生选修课程的门数、学分及其平均成绩；

统计选修每门课程的学生人数及各门课程的平均成绩；找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；13．

查询选修了“1”或“214．

查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；15．

查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；16．

查询没有选修课程的学生的基本信息；17．

查询选修了3门以上课程的学生学号；18．

查询选修课程成绩至少有一门在80分以上的学生学号；19．

查询选修课程成绩均在80分以上的学生学号；20．

查询选修课程平均成绩在80分以上的学生学号；(1)(2)(3)(4)(5)(6)(7)(8)(9)(10)(11)(12)(13)(14)(15)(16)(17)(18)(19)(20)查询语句useStudent;1)select*froms;2)select*fromswheresDept='魏';3)selectsNo,sNamefromswheresdept='魏'andsAgenotin(19,20,21);4)selectsNo,sNamefromswheresAge=(selectmax(sAge)fromswheresDept='魏');5)selectsNo,sNamefromsass1wheres1.sAge=(selectmax(sAge)fromsass2wheres1.sDept=s2.sdept);6)selectsDept,count(*)fromswheresDept='魏';7)selectsDept,count(*)fromsgroupbysDeptorderbycount(*)asc;8)selectsDept,avg(sAge)'平均年龄'fromsgroupbysDeptorderByavg(sAge)desc;9)selectcName'课程名',cTime'学时'fromcwherecpno='无';10)selectsc.sno'学号',count(sc.cNo)'选修门数',avg(grade)'平均成绩',sum(ccredit)'总学分'fromsc,cwhereo=ogroupbysc.sno;11)selectcNo'课程',count(sNo)'人数',avg(grade)'平均分'fromscgroupbycNo;12)selectsc.sno'学号',sDept'专业',avg(grade)'平均分'fromsc,swheresc.sno=s.snogroupbys.sDept,sc.snohavingavg(grade)>85orderbysDept,avg(grade);13)selectdistincts.sno'学号',s.sNamefroms,scwheres.sno=sc.snoand(o='1'oro='2');14)selects.sno,s.sName,sc.gradefroms,sc,cwheresc.sno=s.snoando=oandgrade<60andc.cName='数据库';15)selects.sno,s.sName,o,c.cName,sc.gradefroms,sc,cwheresc.sno=s.snoando=o16)selects.snofromswheres.snonotin(selectsnofromsc)17)selectsno,count(cno)fromscgroupbysnohavingcount(cno)>=3;18)selectdistinct(sno),gradefromscwheregrade>80;19)selectsnofromscgroupbysnohavingmin(grade)>8020)selectsno,avg(grade)fromscgroupbysnohavingavg(grade)>80实验三实验目的熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用（熟悉使用UPDATE/INSERT/DELETE语句进行表操作）；加深理解表的定义对数据更新的作用。实验环境Windows7,SQLServer2008实验要求1．使用SQL进行数据完整性控制。(1)在创建下列关系表时完成如下约束：定义实体完整性;参照完整性（外码、在删除S中的元组时级联删除SC中相应元组、当更新S中的Sno时同时更新SC中的Sno）;用户定义完整性：学生年龄<30、所在系取值唯一。修改S中的约束条件，学号在100－1000之间。学生关系表S：学号姓名性别年龄所在系SnoSnameSsexSagesdept创建课程关系表C：课程号课程名先行课学分CnoCnameCpnoccredit创建成绩关系表SC：学号课程号成绩SnoCnograde用实验验证当操作违反了完整性约束时，系统如何处理？如：在S中插入一条记录，学号不在100-1000之间；在SC中插入一条记录，课程号在C表中没有。删除C中的一条课程记录（该课程至少应有一学生选修，即在SC表中有记录），观察SC表中记录的变化。删除2中增加的约束，再在S中插入一条记录，学号不在100-1000之间，结果如何？问题：外键与参照主键(或候选键)是否一定要相同？2.数据更新(1)

将数据分别插入表S、C、SC；(2)

在表S、C、SC上练习数据的插入、修改、删除操作。（比较在表上定义/未定义主码（PrimaryKey）或外码（ForeignKey）时的情况）(3)

将表S、C、SC中的数据全部删除，再利用磁盘上备份的数据来恢复数据。(4)

如果要在表SC中插入某个学生的选课信息（如：学号为“2007001005”，课程号为“c123(5)

求各系学生的平均成绩，并把结果存入数据库；(6)

将“CS”系全体学生的成绩置零；(7)

删除“CS”系全体学生的选课记录；(8)

删除学号为“S1”(9)

将学号为“S1”的学生的学号修改为“S001(10)

把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S_GRADE（SNO，AVG_GRADE）；(11)

把选修了课程名为“数据结构”的学生的成绩提高10%；(12)

把选修了“C2”实验结果selectS.Sdept,avg(SC.grade)as’平均成绩’fromS,SCwhereS.Sno=SC.SnogroupbyS.Sdept.updateSCsetgrade=0.updateCsetCpron=’’实验四实验目的熟悉和掌握对数据表中视图的定义操作和SQL命令的使用；熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系；学习灵活熟练的进行视图的操作，认识视图的作用。实验环境Windows7,SQLServer2008实验要求以S,C,SC表为基础完成以下视图定义及使用１．

定义“SSCH”院学生基本情况视图V_SSCH；２．

将S，C，SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G;３．

将各院学生人数，平均年龄定义为视图V_NUM_AVG;4．

将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果; 5．

查询平均成绩为90分以上的学生学号、姓名和成绩；6．通过视图V_SSCH，新增加一个学生记录('S12','YANXI',19,'SSCH')，并查询结果； 7．通过视图V_SSCH，删除学号为“S12”8．将视图V_SSCH中学号为“S12”的学生改名“中南人”。实验结果

实验五实验目的熟悉SQL语句对数据库进行完整性控制的方法。实验环境Windows7,SQLServer2008实验要求使用SQL进行数据完整性控制：包括三类完整性、check短语、constrain子句。在创建下列关系表时完成如下约束：定义实体完整性;参照完整性（外码、在删除S中的元组时级联删除SC中相应元组、当更新S中的Sno时同时更新SC中的Sno）;用户定义完整性：学生年龄<30、所在系取值唯一。修改S中的约束条件，学号在100－1000之间。学生关系表S：学号姓名性别年龄所在系SnoSnameSsexSagesdept创建课程关系表C：课程号课程名先行课学分CnoCnameCpnoccredit创建成绩关系表SC：学号课程号成绩SnoCnograde用实验验证当操作违反了完整性约束时，系统如何处理？如：在S中插入一条记录，学号不在100-1000之间；在SC中插入一条记录，课程号在C表中没有。删除C中的一条课程记录（该课程至少应有一学生选修，即在SC表中有记录），观察SC表中记录的变化。删除2中增加的约束，再在S中插入一条记录，学号不在100-1000之间，结果如何？实验结果(1)在创建下列关系表时完成如下约束：定义实体完整性;参照完整性（外码、在删除S中的元组时级联删除SC中相应元组、当更新S中的Sno时同时更新SC中的Sno）;用户定义完整性：学生年龄<30、所在系取值唯一。altertableSaddconstraintSage_rangecheck(Sage<30)altertableSaddconstraintuni_sdeptunique(sdept)修改S中的约束条件，学号大于1000。altertableSaddconstraintSno_rangecheck(Sno>1000)学生关系表S：学号姓名性别年龄所在系SnoSnameSsexSagesdept创建课程关系表C：课程号课程名先行课学分CnoCnameCpnoCcredit创建成绩关系表SC：学号课程号成绩SnoCnograde用实验验证当操作违反了完整性约束时，系统如何处理？如：在S中插入一条记录，学号不大于1000；在SC中插入一条记录，课程号在C表中没有。答：语句会被终止。insertintoSvalues(90,'','','','',null)insertintoSCvalues(90,008,null)INSERT语句与FOREIGNKEY约束"FK__SC__Sno__1B0907CE"冲突。该冲突发生于数据库"student"，表"dbo.S",column'Sno'。语句已终止。INSERT语句与CHECK约束"Sno_range"冲突。该冲突发生于数据库"student"，表"dbo.S",column'Sno'。语句已终止。删除C中的一条课程记录（该课程至少应有一学生选修，即在SC表中有记录），观察SC表中记录的变化。未删除之前的表SC中的数据：删除后的表SC中的数据：（5）删除2中增加的约束，再在S中插入一条记录，学号不大于1000，结果如何？altertableSdropconstraintSno_rangeinsertintoSvalues(3901090113,'曲欣','男','音乐','19891229',null)实验六实验目的理解存储过程的概念、建立和调用方法实验环境Windows7,SQLServer2008实验要求1、建立五张表，每张表至少需要10条记录。（1）/*员工人事表employee*/emp_nochar(5)Notnullprimarykey员工编号emp_namechar(10)Notnull员工姓名sexchar(1)性别deptchar(4)所属部门titlechar(6)职称date_hireddatetime到职日birthdaydatetime生日salaryint薪水addrchar(50)住址（2）/*客户表customer*/cust_idchar(5)Notnullprimarykey客户号cust_namechar(20)Notnull,客户名称addrchar(40)客户住址tel_nochar(10)客户电话zipchar(6)邮政编码（3）/*销售主表sales*/order_nointNotnullprimarykey订单编号cust_idchar(5)Notnull,客户号sale_idchar(5)业务员编号tot_amtnumeric(9,2)订单金额order_datedatetime订货日期ship_datedatetime出货日期invoice_nochar(10)发票号码（4）/*销货明细表sale_item*/order_nointNotnull,primarykey订单编号prod_idchar(5)Notnull,primarykey产品编号qtyint销售数量unit_pricenumeric(7,2)单价order_datedatetime订单日期（5）/*产品名称表product*/pro_idchar(5)Notnullprimarykey产品编号prod_namechar(20)Notnull产品名称2、建立表的同时创建表的约束。为每张表建立主键约束。通过拖放操作加入外键。在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。为销售主表sales中的发票编号字段建立UNIQUE约束。3、通过快捷菜单得到脚本。4、利用存储过程，给employee表添加一条业务部门员工的信息。5、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。利用存储过程计算出订单编号为10003的订单的销售金额。实验结果CREATEDATABASEcompanyONPRIMARY(NAME='company_data',FILENAME='H:\wlwDatabase\company_data.mdf',SIZE=3072KB,FILEGROWTH=1024KB)LOGON(NAME=N'company_log',FILENAME=N'H:\wlwDatabase\company_log.ldf',SIZE=1MB,FILEGROWTH=10%)GOGOSETANSI_PADDINGOFFGO/******对象:Table[dbo].[product]脚本日期:05/19/201113:53:59******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[product]( [pro_id][char](5)NOTNULL, [prod_name][char](20)NOTNULL,PRIMARYKEYCLUSTERED( [pro_id]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOSETANSI_PADDINGOFFGO/******对象:Table[dbo].[employee]脚本日期:05/19/201113:53:57******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[employee]( [emp_no][char](5)NOTNULL, [emp_name][char](10)NOTNULL, [sex][char](1)NULL, [dept][char](4)NULL, [title][char](6)NULL, [date_hired][datetime]NULL, [birthday][datetime]NULL, [salary][int]NULL, [addr][char](50)NULL,PRIMARYKEYCLUSTERED( [emp_no]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOSETANSI_PADDINGOFFGO/******对象:Table[dbo].[sales]脚本日期:05/19/201113:54:06******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[sales]( [order_no][int]NOTNULL, [cust_id][char](5)NULL, [sale_id][char](5)NULL, [tot_amt][numeric](9,2)NULL, [order_date][datetime]NULL, [ship_date][datetime]NULL, [invoice_no][char](10)NULL,PRIMARYKEYCLUSTERED( [order_no]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOSETANSI_PADDINGOFFGO/******对象:Table[dbo].[sale_item]脚本日期:05/19/201113:54:01******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[sale_item]( [order_no][int]NULL, [prod_id][char](5)NULL, [qty][int]NULL, [unit_price][numeric](7,2)NULL, [order_date][datetime]NULL)ON[PRIMARY]GOSETANSI_PADDINGOFFGO/******对象:Check[CK__employee__014935CB]脚本日期:05/19/201113:53:57******/ALTERTABLE[dbo].[employee]WITHCHECKADDCHECK(([emp_no]like'E____'AND([sex]='F'OR[sex]='M')))GO/******对象:ForeignKey[FK__sale_item__order__07F6335A]脚本日期:05/19/201113:54:02******/ALTERTABLE[dbo].[sale_item]WITHCHECKADDFOREIGNKEY([order_no])REFERENCES[dbo].[sales]([order_no])GO/******对象:ForeignKey[FK__sale_item__prod___08EA5793]脚本日期:05/19/201113:54:02******/ALTERTABLE[dbo].[sale_item]WITHCHECKADDFOREIGNKEY([prod_id])REFERENCES[dbo].[product]([pro_id])GO/******对象:ForeignKey[FK__sales__cust_id__060DEAE8]脚本日期:05/19/201113:54:06******/ALTERTABLE[dbo].[sales]WITHCHECKADDFOREIGNKEY([cust_id])REFERENCES[dbo].[customer]([cust_id])GO4、利用存储过程，给employee表添加一条业务部门员工的信息。createprocadd_inferas begininsertintoemployeevalues('e3244','宋士杰','M','业务','经理','20