数据库原理与应用实训报告.doc

实训的课程名称*参加实训的年级及学期实训的周数或天数一、实训的目的*二、实训的理论基础*三、实训内容1.*2.*四、实训要求*五、实训安排 时间 任 务 地点 指导教师 * * * * * *六、实训纪律及注意事项 1.*2.*3.*4.*七、考核办法及标准*数据库原理与应用实训报告班 级 二级学院 姓 名 实训地点：指导教师： 年 月 日实训报告第一部分：实训目的通过本次课程设计让学生能够综合运用所学的关系数据库原理知识解决并能设计一个实际问题，进一步掌握数据库原理的相关理论和数据库的设计实现过程，进一步提高学生的分析问题和解决问题的能力以及学生的动手能力，能够独立完成数据库的功能的设计和开发。第二部分：实训准备 安装有SQL SERVER 2005以上版本的软件的计算机。第三部分：实训要求 1.画出所给任务中表的E-R图2.实训部分（全部使用T-SQL语句完成以下实验内容）第四部分：实训内容(步骤及程序)l E-R图cs_tmcs_scdeptst_fromst_score slt_cousecousestudentdp_idscoresltdatecs_idcs_idcs_nmst_idst_dpidst_datest_idst_sexxst_nmst_birthst_mntdt_teldp_iddp_drtdp_nmm实验1 数据库操作1创建数据库：操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为10%。create database test数据库on(name=test_data,filename=D:课程设计数据库test_data.mdf,size=10mb,filegrowth=1mb,maxsize=unlimited)log on(name=test_log,filename=D:课程设计数据库test_log.ldf,size=1mb,filegrowth=10%,maxsize=5mb)go2查看数据库属性：操作1.2：使用T-SQL语句查看数据库test属性exec sp_helpdb test数据库go3删除数据库：操作1.3：使用T-SQL语句删除数据库testdrop database test数据库go实验2 表操作1创建表：操作2.1：创建学生表：表名：student说明：学生基本信息表属性列数据类型长度空值列约束说明st_idnVarChar9Not NullPK学生学号st_nmnVarChar8Not Null学生姓名st_sexnVarChar2Null学生性别st_birthdatetimeNull出生日期st_scoreintNull入学成绩st_datedatetimeNull入学日期st_fromnChar20Null学生来源st_dpidnVarChar2Null所在系编号st_mnttinyintNull学生职务use test数据库gocreate table student(st_id nvarchar (9) not null primary key,st_nm nvarchar (8) not null,st_sex nvarchar (2),st_birth datetime ,st_score int ,st_date datetime,st_from nchar (20),st_dpid nvarchar (2),st_mnt tinyint )go操作2.2：创建课程信息表：表名：couse说明：课程信息表属性列数据类型长度空值列约束说明cs_idnVarChar4Not NullPK课程编号cs_nmnVarChar20Not Null课程名称cs_tmintNull课程学时cs_scintNull课程学分use test数据库gocreate table couse(cs_id nvarchar (4) not null primary key,cs_nm nvarchar (20) not null,cs_tm int ,cs_sc int)go操作2.3：创建选课表：表名：slt_couse说明：选课表属性列数据类型长度空值列约束说明cs_idnVarChar4Not NullFK课程编号st_idnVarChar9Not NullFK学生编号scoreintNull课程成绩sltdatedatetimeNull选课日期use test数据库gocreate table slt_couse(cs_id nvarchar (4) not null,st_id nvarchar (9) not null,score int,sltdate datetime)goalter table slt_couseadd constraint fk_cs_idforeign key (cs_id)references couse(cs_id)goalter table slt_couseadd constraint fk_st_idforeign key (st_id)references student(st_id)go操作2.4：创建院系信息表：表名：dept说明：院系信息表属性列数据类型长度空值列约束说明dp_idnVarChar2Not Null系编号dp_nmnVarChar20Not Null院系名称dp_drtnVarChar8Null院系主任dt_telnVarChar12Null联系电话use test数据库gocreate table dept(dp_id nvarchar(2) not null,dp_nm nvarchar(20) not null,dp_drt nvarchar (8) ,dt_tel nvarchar (12)go2修改表结构：（1）向表中添加列：操作2.5：为“dept”表添加“dp_count”列（数据类型为nvarchar，长度为3，允许为空）use test数据库goalter table deptadd dp_count nvarchar (3)go（2）修改列数据类型：操作2.6：修改“dept”表的“dp_count”列数据类型为intuse test数据库goalter table deptalter column dp_count intgo（3）删除表中指定列：操作2.7：删除“dept”表的“dp_count”列use test数据库goalter table deptdrop column dp_countgo3删除表操作2.8：删除“dept”表use test数据库godrop table deptgo4向表中输入数据记录操作2.9：分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录use test数据库goinsert studentvalues (000000001,小二,男,1991-01-01,89,2011-09-01,广东,10,1)insert studentvalues (000000002,小三,男,1991-01-02,79,2011-09-01,广东,12,1)insert studentvalues (000000003,小四,女,1991-01-03,69,2011-09-01,广东,8,1)insert studentvalues (000000004,小六,男,1991-04-01,59,2011-09-01,广东,13,1)insert studentvalues (000000005,小七,男,1991-01-11,89,2011-09-01,广东,11,1)Gouse test数据库goinsert cousevalues (0001,计算机网络,72,2)insert cousevalues (0002,c语言程序设计,72,2)insert cousevalues (0003,SQL数据库,72,2)insert cousevalues (0004,计算机导论,72,2)insert cousevalues (0005,高级数学,72,2)Gouse test数据库goinsert slt_cousevalues (0001,000000001,72,2011-10-12)insert slt_cousevalues (0002,000000002,92,2011-10-12)insert slt_cousevalues (0003,000000003,62,2011-10-12)insert slt_cousevalues (0004,000000004,82,2011-10-12)insert slt_cousevalues (0005,000000005,52,2011-10-12)use test数据库goinsert deptvalues (8,艺术系,王大仁,12345768)insert deptvalues (9,建筑系,李小仁,12645678)insert deptvalues (10,信息系,李大仁,12349878)insert deptvalues (13,管理系,王小仁,16345678)insert deptvalues (12,外语系,王小明,14512678)go实验3 数据完整性1空值约束( NULL )操作3.1：将student表中的st_sex列属性更改为NOT NULLuse test数据库goalter table studentalter column st_sex nvarchar(2) not nullgo2默认值约束( DEFAULT )操作3.2：将student表中的st_from列默认值设置为“陕西省”use test数据库goalter table studentadd constraint df_fromdefault 陕西省 for st_fromgo3默认值对象操作3.3：创建默认值对象df_today为当前日期，并将其绑定到slt_couse表中的sltdate列，然后取消绑定，最后删除默认值对象df_today。use test数据库gocreate default df_today as 2014-12-30 goexec sp_bindefault df_today,slt_couse.sltdategoexec sp_unbindefault slt_couse.sltdategodrop default df_today go4检查约束( CHECK )操作3.4：将slt_couse表中的score列的检查约束设置为=0且=0 and score=2008go操作6.4：在查询student表080808班学生的学号、姓名、性别和入学成绩use test数据库goselect left(st_id,6) ,st_nm ,st_sex ,st_scorefrom studentwhere left(st_id,6)=080808go2使用逻辑表达式表示查询条件操作6.5：查询student表中非11系的学生信息use test数据库goselect *from studentwhere not (st_dpid =11)go操作6.6：查询选修了1002号课程且成绩在60以下的学生学号use test数据库goselect st_idfrom slt_cousewhere cs_id=1002and score75go操作8.11：查询选修了2门以上课程的学生学号use test数据库goselect st_idfrom slt_cousegroup by st_idhaving count(*)2go操作8.12：明细汇总年龄20的学生，并汇总学生数量、平均年龄use test数据库goselect st_nm,datepart(yy,getdate()-datepart(yy,st_birth) 平均年龄from studentwhere datepart(yy,getdate()-datepart(yy,st_birth)20compute count(st_nm), avg(datepart(yy,getdate()-datepart(yy,st_birth)go操作8.13：按班级明细汇总成绩85分的学生，汇总学生数、均分use test数据库goselect st_nm ,left(st_id,6) 班级 ,st_score from studentwhere st_score85order by left(st_id,6)compute count(st_nm),avg(st_score) by left(st_id,6)go 实验9 数据查询（5）连接查询操作9.1：用SQL Server形式连接查询学生学号、姓名、性别及其所选课程编号use test数据库goselect student.st_id, st_nm, st_sex, cs_idfrom student , slt_couse where student.st_id =slt_couse.st_idgo操作9.2：用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号use test数据库goselect student.st_id, st_nm, st_sex, cs_idfrom student inner join slt_couse on student.st_id = slt_couse.st_idgo操作9.3：用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩use test数据库goselect student.st_id ,st_nm ,cs_nm ,scorefrom student, couse,slt_cousewhere student.st_id=slt_couse.st_idand couse.cs_id=slt_couse.cs_idgo操作9.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩use test数据库goselect student.st_id ,st_nm ,cs_nm ,scorefrom slt_couse inner join student on student.st_id=slt_couse.st_idinner join couse on slt_couse.cs_id=couse.cs_idgo操作9.5：查询选修了1002课程的学生学号、姓名及1001课程成绩use test数据库goselect student.st_id ,st_nm ,scorefrom student ,slt_cousewhere student.st_id=slt_couse.st_idand cs_id=1002and cs_id=1001go操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩use test数据库goselect student.st_id,st_nm,scorefrom student,slt_couse,cousewhere student.st_id=slt_couse.st_idand couse.cs_id=slt_couse.cs_idand cs_nm=数据结构go操作9.7：用左外连接查询没有选修任何课程的学生学号、姓名use test数据库goselect student.st_id ,st_nm ,scorefrom student left join slt_couseon student.st_id=slt_couse.st_idwhere score is null go操作9.8：用右外连接查询选修各个课程的学生学号use test数据库goselect slt_couse.st_id ,couse.cs_idfrom slt_couse right join couseon couse.cs_id=slt_couse.cs_idgo实验10 数据查询（6）子查询操作10.1：用子查询对各班人数进行查询（新增列）use test数据库goselect distinct left(st_id ,6) 班级,人数=(select count(st_id) from student bwhere left(student.st_id,6)= left(b.st_id,6)from studentgo操作10.2：用子查询对各课程的选课人数进行查询（新增列）use test数据库goselect distinct slt_couse.cs_id ,人数=(select count(cs_id) from slt_couse bwhere slt_couse.cs_id=b.cs_id)from slt_cousego操作10.3：查询选修了1002课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序通过子查询实现：使用IN关键字use test数据库goselect st_id,st_nm,st_sexfrom studentwhere st_id in (select st_id from slt_couse where cs_id=1002 and score60)Order by st_nmgo通过子查询实现：使用比较运算符use test数据库goselect st_id ,st_nm ,st_sexfrom studentwhere (select score from slt_couse where student.st_id=slt_couse.st_id and cs_id=1002) any(select score from slt_cousewhere cs_id=1002 and left(st_id,6)=070511 and left(st_id,6) 070511 and cs_id=1002)go操作10.6：查询其它班比070511班任一学生的1002号课程成绩高的学生信息（ANY/ALL）use test数据库goselect * from slt_cousewhere score all(select score from slt_cousewhere cs_id=1002 and left(st_id,6)=070511 and left(st_id,6) 070511 and cs_id=1002)go操作10.7：查询大于等于60分且且比1003课程平均成绩低的学生课程信息（BetweenAnd）use test数据库goselect *from slt_couse awhere a.score between 60 and(select avg(b.score) from slt_couse bwhere b.cs_id=1003)go操作10.8：查询系主