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

1、实训的课程名称*参加实训的年级及学期实训的周数或天数一、实训的目的*二、实训的理论基础*三、实训内容1.*2.*四、实训要求*五、实训安排 时间 任 务 地点 指导教师 * * * * * *六、实训纪律及注意事项 1.*2.*3.*4.*七、考核办法及标准*数据库原理与应用实训报告班 级 二级学院 姓 名 实训地点：指导教师： 年 月 日实训报告第一部分：实训目的通过本次课程设计让学生能够综合运用所学的关系数据库原理知识解决并能设计一个实际问题，进一步掌握数据库原理的相关理论和数据库的设计实现过程，进一步提高学生的分析问题和解决问题的能力以及学生的动手能力，能够独立完成数据库的功能的设计和开

2、发。第二部分：实训准备 安装有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：创建一个te

3、st数据库，其主数据文件逻辑名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&#

4、39;,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语句删除数据库test drop database test数据库go实验2 表操作1创建表：操作2.1：创建学生表：表名：student 说明：学生基本信息表属性列数据类型长度空值列约束说明st_idnVarChar9Not NullPK学生学号st_n

5、mnVarChar8Not Null 学生姓名st_sexnVarChar2Null 学生性别st_birthdatetime Null 出生日期st_scoreint Null 入学成绩st_datedatetime Null 入学日期st_fromnChar20Null 学生来源st_dpidnVarChar2Null 所在系编号st_mnttinyint Null 学生职务use test数据库gocreate table student(st_id nvarchar

6、(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_tmi

7、nt Null 课程学时cs_scint Null 课程学分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学生编号scoreint Null

8、 课程成绩sltdatedatetime Null 选课日期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_idfo

9、reign 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 n

10、ull,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”表

11、的“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','201

12、1-09-01','广东','10','1')insert studentvalues ('000000002','小三','男','1991-01-02','79','2011-09-01','广东','12','1')insert studentvalues ('000000003','小四','女','1991-01-03',&

13、#39;69','2011-09-01','广东','8','1')insert studentvalues ('000000004','小六','男','1991-04-01','59','2011-09-01','广东','13','1')insert studentvalues ('000000005','小七','男',

14、9;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','S

15、QL数据库','72','2')insert cousevalues ('0004','计算机导论','72','2')insert cousevalues ('0005','高级数学','72','2')Gouse test数据库goinsert slt_cousevalues ('0001','000000001','72','2011-10-12')inser

16、t 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 (&#

17、39;0005','000000005','52','2011-10-12')use test数据库goinsert deptvalues ('8','艺术系','王大仁','12345768')insert deptvalues ('9','建筑系','李小仁','12645678')insert deptvalues ('10','信息系','李大仁','

18、;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) no

19、t 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' goex

20、ec sp_bindefault 'df_today','slt_couse.sltdate'goexec sp_unbindefault 'slt_couse.sltdate'godrop default df_today go4检查约束( CHECK )操作3.4：将slt_couse表中的score列的检查约束设置为>=0且<=100use test数据库goalter table slt_couseadd constraint ck_scorecheck (score>=0 and score<=100)go5规则

21、约束对象操作3.5：创建规则约束对象rl_sex，用于检查性别的取值仅限于“男”和“女”，并将其绑定到student表中的st_sex列，然后取消绑定，最后删除规则约束对象rl_sex。use test数据库gocreate rule rl_sex as sex ='男' or sex='女'goexec sp_bindrule 'rl_sex' , 'student.st_sex'goexec sp_unbindrule 'student.st_sex'godrop rule rl_sex go6主键操作3.6：

22、将dept表中的dp_id列设置为主键use test数据库goalter table deptadd constraint pk_dp_idprimary key (dp_id)go7唯一性约束( UNIQUE )操作3.7：将dept表中的dp_nm列设置为唯一性约束use test数据库goalter table deptadd constraint ix_nmunique (dp_nm)go8标识列操作3.8：向slt_couse表中添加标识列id，第1行默认值为1，相邻两个标识列间的增量为1use test数据库goalter table slt_couseadd id int ID

23、ENTITY (1,1) not nullgo9外键（ FOREIGN KEY ）操作3.9：被参照表为dept，参照表为studentuse test数据库goalter table studentadd constraint fk_dpidforeign key (st_dpid)references dept(dp_id)go实验4 数据更新1表中插入数据操作4.1：向dept表插入一条记录，系号11，系名自动控制系，系主任为李其余，电话81234567use test数据库goinsert deptvalues ('11','自动控制系','李其

24、余','81234567')go操作4.2：向student表插入一条记录，学号070201001，姓名为王小五，性别为男，出生日期1990年9月9日，系号为11，其余字段为NULL或默认值use test数据库goinsert student(st_id,st_nm,st_sex,st_birth, st_dpid)values ('070201001','王小五','男','1990-9-9','11')go操作4.3：向couse表插入一条记录，课程号1234，课程名为操作系统，其余字

25、段为NULL或默认值use test数据库goinsert couse(cs_id,cs_nm)values ('1234','操作系统')go操作4.4：向slt_couse表插入一条记录，课程号1234，学名070201001，其余字段为NULL或默认值use test数据库goinsert slt_couse (cs_id,st_id)values ('1234','070201001')go2修改表中数据操作4.5：修改student表记录，将王小五的入学成绩改为88use test数据库goupdate students

26、et st_score ='88'where st_nm='王小五'go操作4.6：修改couse表记录，将所有记录的学分改为4，学时改为64use test数据库goupdate couseset cs_sc='4' ,cs_tm='64'go操作4.7：修改slt_couse表记录，将课程号为1234，学名为070201001的记录的成绩改为77use test数据库goupdate slt_couseset score= 77where cs_id='1234'and st_id='070201001

27、'go3删除表中数据操作4.8：删除slt_couse表记录，将课程号为1234，学名为070201001的记录删除use test数据库godelete slt_cousewhere cs_id='1234'and st_id='070201001'go操作4.9：删除couse表记录，将课程号为1234的记录删除use test数据库godelete cousewhere cs_id='1234'go实验5 数据查询（1）简单查询（1）查询表中所有的列操作5.1：查询所有系的信息use test数据库goselect *from de

28、ptgo（2）查询表中指定列的信息操作5.2：查询所有的课程号与课程名称use test数据库goselect cs_id ,cs_nmfrom couse（3）在查询列表中使用列表达式操作5.3：在查询student表时使用列表达式：入学成绩+400use test数据库goselect st_score+400from studentgo（4）重新命名查询结果操作5.4：使用AS关键字为dept表中属性指定列名：系号、系名、系主任、联系电话use test数据库goselect dp_id as '系号',dp_nm as '系名',dp_drt as &#

29、39;系主任', dt_tel as '联系电话'from deptgo操作5.5：使用"="号为couse表中属性指定列名：课程号、课程名、学时(=cs_sc*16)、学分use test数据库goselect '课程号'=cs_id,'课程名'=cs_nm ,'学时=cs_sc*16'=cs_tm, '学分'=cs_scfrom cousego（5）增加说明列操作5.6：查询dept表的系号、系名和系主任，向查询结果中插入说明列：系号、系名和系主任use test数据库goselec

30、t dp_id '系号' ,dp_nm '系名',dp_drt '系主任'from deptgo（6）查询列表中使用系统函数操作5.7：显示所有学生的学号、姓名、性别和入学年份use test数据库goselect st_id ,st_nm ,st_sex , datepart(yy,st_date) '入学年份'from studentgo操作5.8：显示所有学生学号、姓名、性别和班级(学号前6位)use test数据库goselect left(st_id ,6) '班级',st_nm ,st_sex , st

31、_dpidfrom studentgo（7）消除查询结果中的重复项操作5.9：显示所有学生班级use test数据库goselect distinct left(st_id,6) '班级'from studentgo（8）取得查询结果的部分行集操作5.10：显示前5条学生记录信息use test数据库goselect top 5 *from studentgo操作5.11：显示前25%条学生记录信息use test数据库goselect top 25 percent *from studentgo操作5.12：显示前n条学生记录信息，n为局部变量use test数据库godec

32、lare n intset n = 5select top n *from studentgo实验6 数据查询（2）条件查询1使用关系表达式表示查询条件操作6.1：查询dept表中系号为11的院系信息use test数据库goselect *from deptwhere dp_id='11'go操作6.2：查询student表中11系的学生学号、姓名、性别和所在系编号use test数据库goselect st_id ,st_nm ,st_sex , st_dpidfrom studentwhere st_dpid ='11'go操作6.3：查询student表

33、中2008年及以后入学的学生信息use test数据库goselect *from studentwhere datepart(yy,st_date)>='2008'go操作6.4：在查询student表080808班学生的学号、姓名、性别和入学成绩use test数据库goselect left(st_id,6) ,st_nm ,st_sex ,st_scorefrom studentwhere left(st_id,6)='080808'go2使用逻辑表达式表示查询条件操作6.5：查询student表中非11系的学生信息use test数据库gosel

34、ect *from studentwhere not (st_dpid ='11')go操作6.6：查询选修了1002号课程且成绩在60以下的学生学号use test数据库goselect st_idfrom slt_cousewhere cs_id='1002'and score<60go操作6.7：查询2007年入学的11系所有男生信息use test数据库goselect *from studentwhere st_dpid='11'and st_sex='男'and datepart(yy,st_date)='

35、;2007'go操作6.8：查询11系和12系的学生信息use test数据库goselect *from studentwhere st_dpid='11'or st_dpid='12'go操作6.9：查询11系和12系所有2007年入学的学生信息use test数据库goselect *from studentwhere (st_dpid='11'or st_dpid='12')and datepart(yy,st_date)='2007'go3使用LIKE关键字进行模糊查询操作6.10：查询所有“计算

36、机”开头的课程信息use test数据库goselect *from cousewhere cs_nm like '计算机%'go操作6.11：查询所有由三个字组成的“王”姓学生信息use test数据库goselect *from studentwhere st_nm like '王_'go操作6.12：查询所有课程名中包含“信息”的课程信息use test数据库goselect *from cousewhere cs_id like '%信息%'go操作6.13：查询学生姓名介于王姓到张姓的信息use test数据库goselect *fro

37、m studentwhere st_nm like '王-张%'go4使用BetweenAnd关键字进行查询操作6.14：查询在1989.7.1到1990.6.30之间出生的学生信息use test数据库goselect *from studentwhere st_birth between '1989-7-1' and '1990-6-30'go操作6.15：查询选修了1001号课程且成绩在60到80之间的学生选课信息use test数据库goselect *from slt_cousewhere cs_id='1001'and

38、 score between 60 and 80go5使用IN关键字进行查询操作6.16：查询11系、12系、13系的学生信息use test数据库goselect *from studentwhere st_dpid in (11,12,13)go操作6.17：查询所有张，王，李，赵姓的学生的学号、姓名、性别use test数据库goselect st_id ,st_nm ,st_sexfrom studentwhere left(st_nm,1) in ('张','王','李','赵')go6使用NOT NULL关键字进行查询

39、操作6.18：查询所有生源为非空的学生信息use test数据库goselect *from studentwhere st_from is not nullgo操作6.19：查询选修了1001号课程且成绩为空的学生选课信息use test数据库goselect *from slt_cousewhere cs_id='1001'and score is nullgo实验7 数据查询（3）查询排序与查询结果存储操作7.1：查询课程信息，按课程名称降序排序use test数据库goselect *from couseorder by cs_nm descgo操作7.2：查询选修了1

40、001号课程成绩非空的学生学号和成绩，并按成绩降序排序use test数据库goselect st_id ,scorefrom slt_cousewhere cs_id='1001'and score is not nullorder by score descgo操作7.3：查询11系学生学号、姓名和年龄，按年龄升序排序use test数据库goselect st_id ,st_nm ,datepart(yy,getdate()-datepart(yy,st_birth) '年龄'from studentwhere st_dpid='11'or

41、der by 年龄 ascgo操作7.4：查询学生信息，按姓名升序排序，再按系号降序排序use test数据库goselect *from studentorder by st_nm asc , st_dpid descgo操作7.5：创建学生表副本student01，仅保留学生学号、姓名和性别use test数据库goselect st_id ,st_nm ,st_sexinto student01from studentgo操作7.6：查询陕西籍学生，将结果保存在新表st_shanxiuse test数据库goselect *into st_shanxifrom studentwhere

42、st_from='陕西省'go操作7.7：查询选修了1001号课程学生的选课信息，按学号升序排序，将结果保存在新表use test数据库goselect *into slt_couse01from slt_cousewhere cs_id='1001'order by st_id ascgo操作7.8：用局部变量stage保存学生张三的年龄use test数据库godeclare stage intselect stage = datepart(yy,getdate()-datepart(yy,st_birth)from studentwhere st_nm=&

43、#39;张三'go操作7.9：用局部变量name和stscore保存070101班按学号排序后最后一个学生的姓名和入学成绩use test数据库godeclare name nvarchar (8) ,stscore intselect name=st_nm ,stscore=st_scorefrom studentwhere left(st_id,6)='070101' order by st_id descgo 实验8 数据查询（4）查询统计与汇总操作8.1：查询课程总数use test数据库goselect count(*)from cousego操作8.2：查询

44、选修1001号课程的学生人数use test数据库goselect count(st_id)from slt_cousewhere cs_id='1001'go操作8.3：查询被选修课程的数量use test数据库goselect count(distinct cs_id)from slt_cousego操作8.4：查询选修070101班学生的平均入学成绩use test数据库goselect avg(st_score)from studentwhere left(st_id ,6) ='070101'go操作8.5：查询070101001号学生选修课程的数量、

45、总分以及平均分use test数据库goselect count(cs_id) '总量' ,sum(score) '总分' ,avg(score) '平均分'from slt_cousego操作8.6：查询选修1001号课程的学生人数、最高分、最低分和平均分use test数据库goselect count(*) '学生总数' ,max(score) '最高分' ,min(score) '最低分'from slt_cousewhere cs_id='1001' go操作8.7：求各个

46、课程号和相应的选课人数use test数据库goselect cs_id ,count(st_id)from slt_cousegroup by cs_idgo操作8.8：统计各班人数use test数据库goselect left(st_id ,6) '班级' ,count(st_id) '人数'from studentgroup by left(st_id ,6)go操作8.9：依次按班级、系号对学生进行分类统计人数、入学平均分use test数据库goselect (left(st_id,6) '班级' ,(st_dpid) '系级

47、' ,avg(st_score) '入学平均分' ,count(st_id) '人数'from studentgroup by left(st_id ,6),st_dpidgo操作8.10：查询选修了均分在75以上的课程号及均分use test数据库goselect cs_id '课程号' ,avg(score) '平均分'from slt_cousegroup by cs_idhaving avg(score)>75go操作8.11：查询选修了2门以上课程的学生学号use test数据库goselect st_id

48、from 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_b

49、irth)go操作8.13：按班级明细汇总成绩<85分的学生，汇总学生数、均分use test数据库goselect st_nm ,left(st_id,6) '班级' ,st_score from studentwhere st_score<85order 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 stud

50、ent.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 t

51、est数据库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_i

52、dinner 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='1002'and cs_id='1001'go操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩use test数据库goselect student.s

53、t_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操作

54、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_

55、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

56、_sexfrom studentwhere st_id in (select st_id from slt_couse where cs_id='1002' and score<60)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')<60order by st_nmgo操作10.4：查询“东方红”同学所在班的学生信息，并按姓名降序排序通过子查询实现：IN运算符use test数据库goselect *from studentwhere left(st_id,6) in (select left(st_id,6) from stud