数据库实验报告 |
您所在的位置：网站首页 › 数据库表的基本操作实验报告 › 数据库实验报告 |
数据库实验报告
|
前言 SQL Server是一种关系型数据库管理系统（RDBMS），由Microsoft开发和维护。它是一款功能强大、稳定可靠的数据库软件，支持事务、数据存储、管理和查询等各种功能，是企业级应用的首选数据库。SQL Server可以在Windows操作系统上运行，并提供了大量的管理工具和API，可以支持多个用户或应用程序同时访问数据库。SQL Server还提供了多种安全功能，包括数据加密、访问控制和身份验证机制等，可以保护数据库的安全性和完整性。此外，SQL Server还支持多种语言和平台，可以方便地与其他应用程序进行集成和交互，可用于各种应用场景，包括Web应用、企业应用、商业智能和数据仓库等。 目录 1 数据库、表的创建与管理 1.1 创建数据库 1.2 创建表 1.3 插入数据 1.4 数据操作 2 数据完整性的设置 2.1 设置外键 2.2 创建unique约束 2.3 创建、删除check约束 2.4 创建、删除规则 3 数据检索 3.1 单表数据查询 3.2 多表查询与子查询 4 索引和视图 4.1 创建索引 4.2 创建视图 4.3 删除索引和视图 5 存储过程和触发器 5.1 创建存储过程 5.2 创建触发器 1 数据库、表的创建与管理 1.1 创建数据库利用SQL Server Management Studio创建一个名为teaching数据库,初始大小为10MB,增长速度为10%,其他均采用默认设置。
在查询编辑器中输入创建表的代码，分别创建student、course、score、teacher、 class、teach_class这6张表 创建student表代码： create table teaching.dbo.student( studentno nchar(11) not null, sname nvarchar(8) null, sex nchar(1) null, birthday datetime null, classno nchar(7) null, point smallint null, phone nchar(12) null, Email nvarchar(20) null, constraint pk_student primary key (studentno asc))创建course表代码： create table teaching.dbo.course( courseno nchar(6) not null, cname nchar(20) null, type nchar(8) null, period tinyint null, credit numeric(4,1) null, constraint pk_course primary key (courseno asc))创建score表和teacher表代码： create table teaching.dbo.score( studentno nchar(11) not null, courseno nchar(6) not null, usually numeric(6,2) null, final numeric(6,2) null, constraint pk_score primary key (studentno asc,courseno asc)) create table teaching.dbo.teacher( teacherno nchar(6) not null, tname nchar(8) null, major nchar(10) null, prof nchar(10) null, department nchar(12) null, constraint pk_teacher primary key (teacherno asc))创建class表、teach_class表代码： create table teaching.dbo.class( classno nchar(7) not null, classname nchar(12) null, department nchar(12) null, monitor nchar(8) null, constraint pk_class primary key (classno asc)) create table teaching.dbo.teach_class( teacherno nchar(6) not null, classno nchar(7) not null, courseno nchar(6) not null, constraint pk_teach_class primary key (teacherno asc,classno asc,courseno asc)) 1.3 插入数据分别对这6张表输入记录（每张表不少于5条记录） 向student表插入数据： Insert into teaching.dbo.student (studentno,sname,sex,birthday,classno,point,phone,Email) Values ('082211120','赵某','男','1989-12-11','1701','101','15200000000','[email protected]') Insert into teaching.dbo.student (studentno,sname,sex,birthday,classno,point,phone,Email) Values ('082211121','余某','男','1989-12-12','1702','102','15211111111','[email protected]') Insert into teaching.dbo.student (studentno,sname,sex,birthday,classno,point,phone,Email) Values ('082211122','程某','女','1989-12-13','1703','103','15222222222','[email protected]') Insert into teaching.dbo.student (studentno,sname,sex,birthday,classno,point,phone,Email) Values ('082211123','周某','男','1989-12-14','1704','104','15233333333','[email protected]') Insert into teaching.dbo.student (studentno,sname,sex,birthday,classno,point,phone,Email) Values ('082211124','霍某','女','1989-12-15','1705','105','15244444444','[email protected]')student 表数据
向course表插入数据： Insert into teaching.dbo.course Values('c05120','数据结构','必修','60','1') Insert into teaching.dbo.course Values('c05121','操作系统','选修','61','2') Insert into teaching.dbo.course Values('c05122','数据逻辑','必修','62','3') Insert into teaching.dbo.course Values('c05123','计算方法','必修','63','4') Insert into teaching.dbo.course Values('c05124','大学体育','选修','64','5')course表数据
向score表插入数据： Insert into teaching.dbo.score Values('0822111201','c06101','89','91') Insert into teaching.dbo.score Values('0822111202','c06102','88','92') Insert into teaching.dbo.score Values('0822111203','c06103','87','93') Insert into teaching.dbo.score Values('0822111204','c06104','86','94') Insert into teaching.dbo.score Values('0822111205','c06105','85','95')score表数据
向teacher表插入数据： Insert into teaching.dbo.teacher Values('t05001','赵某某','数据结构','教授','计算机学院') Insert into teaching.dbo.teacher Values('t05002','余某某','操作系统','教授','计算机学院') Insert into teaching.dbo.teacher Values('t05003','程某某','数据逻辑','教授','经济学院') Insert into teaching.dbo.teacher Values('t05004','周某某','计算方法','导师','计算机学院') Insert into teaching.dbo.teacher Values('t05005','霍某某','大学体育','教授','经济学院')teacher表数据
向class表插入数据： Insert into teaching.dbo.class Values('080601','机械','机械学院','马飞') Insert into teaching.dbo.class Values('080602','机械','机械学院','李飞') Insert into teaching.dbo.class Values('080603','机械','机械学院','张飞') Insert into teaching.dbo.class Values('080604','机械','机械学院','周飞') Insert into teaching.dbo.class Values('080605','机械','机械学院','余飞')class表数据
向teach_class表插入数据： Insert into teaching.dbo.teach_class Values('t05001','080601','c05120') Insert into teaching.dbo.teach_class Values('t05002','080602','c05121') Insert into teaching.dbo.teach_class Values('t05003','080603','c05122') Insert into teaching.dbo.teach_class Values('t05004','080604','c05123') Insert into teaching.dbo.teach_class Values('t05005','080605','c05124')teach_class表数据
向student表插入、删除、修改一条记录 插入一条记录： insert into teaching.dbo.student values('0937221508','平静','女','1998-02-12','1709','109','15299999999','[email protected]')删除一条记录： delete from teaching.dbo.student where studentno='0937221508'修改一条记录： update teaching.dbo.student set point=888 where studentno='0937221508'通过本次实验，我掌握了创建、修改数据库的方法及管理数据库的方法，并掌握了创建、修改表结构的方法及插入、更新和删除表数据的方法，让我对SQL server2008的使用更加得心应手。总而言之，本次实验的内容让我受益匪浅，也为数据库这门课程的学习做了铺垫作用。 2 数据完整性的设置 2.1 设置外键利用SQL Server Management Studio将teaching数据库中score表的courseno列设置为引用表course的外键。
在teaching数据库中class表的classname创建UNIQUE约束。 alter table class add constraint u_classname unique nonclustered(classname) go 2.3 创建、删除check约束为teaching数据库中student表的birthday列创建check约束,规定学生的年龄在17~25之间,为course表的credit列创建check约束,规定学分的取值范围为1~6,删除check约束。 alter table student add constraint ck_birthday check (year(getdate()-year(birthday)) between 17 and 25) go alter table course add constraint ck_credit check(credit>=1 and credit20 (3)查询计算机学院教师的专业名称 select prof from teaching.dbo.teacher where department='计算机学院' (4)查询每名学生的学号、选修课程数目、总成绩，并将查询结果存放到生成的“学生选课统计表” use teaching go if exists (select * from sys.objects where name='学生选课统计表') drop table 学生选课统计表 select studentno,COUNT(*) as '选修课程数目',sum(final) as '总成绩' into 学生选课统计表 from score group by studentno select * from 学生选课统计表 (5)查询student表中所有学生的基本信息,查询结果按班级号classno升序排序,同一班级中的学生按入学成绩point降序排列 select * from teaching.dbo.student order by classno,point desc (6)查询各班学生的人数(按班级分组),查询各班期末成绩的最高分和最低分 select COUNT(*) as '人数',MAX(final) as '最高分', MIN(final) as '最低分' from teaching.dbo.student,teaching.dbo.score where student.studentno=score.studentno group by classno order by classno (7)查询教授一门及以上课程的教师编号、课程编号和任课班级 select teacherno,courseno,classno from teaching.dbo.teach_class where exists (select courseno,COUNT(*) from teaching.dbo.course group by courseno having COUNT(*)>=1)
(8)查询课程编号以c05开头,被三名及以上学生选修,且期末成绩的平均分高于75分的课程号、选修人数和期末成绩平均分，并按平均分降序排序。 use teaching go select courseno,count(studentno)as '选修人数',avg(final) as '期末平均分' from score where courseno like 'c06%' and final is not null GROUP BY courseno having COUNT(studentno)>=3 and AVG(final)>75 order by AVG(final) desc 通过本次实验，我掌握了SELECT各个子句的功能和检索数据的方法，掌握WHERE子句中LIKE、IN、BETWEEN、IS等逻辑运算符的使用，掌握了聚合函数的使用，本次实验，编写代码途中出现点小差错，导致运行失败，但经过自己查找资料，最终问题得以解决，让我更加对SQL server的使用游刃有余。希望自己在今后的实验中严谨认真，做好每一次实验。 3.2 多表查询与子查询(1)查询所有班级的期末成绩平均分,并按照平均分降序排序 select classno,AVG(final) from teaching.dbo.score join teaching.dbo.teach_class on score.courseno=teach_class.courseno group by classno order by AVG(final) desc (2)查询教师基本信息和教授课程信息,其中包括未分配课程的教师信息 select teacher.teacherno,tname,major,prof,courseno from teaching.dbo.teacher left join teaching.dbo.teach_class on teacher.teacherno=teach_class.teacherno (3)查询两门及以上课程的期末成绩超过80分的学生姓名及其平均成绩 select sname,AVG(final) from teaching.dbo.score join teaching.dbo.student on score.studentno=student.studentno where exists (select courseno,COUNT(*) from teaching.dbo.score group by courseno having COUNT(*)>=2) and final>80 group by sname (4)查询没有被任何学生选修的课程编号、课程名称和学分（子查询） select courseno,cname,credit from teaching.dbo.course where not exists (select * from teaching.dbo.score where score.courseno=course.courseno) (5)查询入学成绩最高的学生学号、姓名和入学成绩（子查询） select studentno,sname,point from teaching.dbo.student where studentno in (select top 1 studentno from teaching.dbo.student order by point desc) (6)查询同时教授c05120号和c05121号课程的教师信息(子查询) select * from teaching.dbo.teacher left join teaching.dbo.teach_class on teacher.teacherno=teach_class.teacherno where courseno='c05120' and teacher.teacherno in (select teacherno from teaching.dbo.teach_class where courseno='c05121')
(7)查询每门课程的课程号、课程名和选修该课程的学生人数，并按所选人数升序排序 select courseno,cname,COUNT(*) from teaching.dbo.course group by courseno,cname order by COUNT(*) (8)使用游标输出学生姓名、选修课程名称和期末考试成绩 declare student_cursor cursor for select sname,cname,final from teaching.dbo.student join teaching.dbo.score on student.studentno=score.studentno join teaching.dbo.course on score.courseno=course.courseno open student_cursor declare @sname nchar(8),@cname nchar(20),@final numeric(6,2) fetch next from student_cursor into @sname,@cname,@final print '学生名字课程名称期末成绩' print'-----------------------' while @@FETCH_STATUS=0 begin print @sname+@cname+cast(@final as nchar(6)) fetch next from student_cursor into @sname,@cname,@final end close student_cursor deallocate student_cursor
通过本次实验，我掌握了多表连接的各种方法（内连接和外连接），掌握了子查询的方法（相关子查询和不相关子查询），还有游标处理集的基本过程，会使用JOIN ON连接两个及两个以上表，查询到了表中相关信息，也学会了在SELECT查询语句中再嵌套一个SELECT语句，还学会了使用游标对查询结果集进行处理。总之，本次的多表查询与子查询的实验，我受益匪浅，更坚定了我对这门课程的学习的决心。 4 索引和视图 4.1 创建索引(1)在teaching数据库的student表的classno字段创建非聚集非唯一索引cu_classno create nonclustered index cu_classno on teaching.dbo.student(classno)(2)在teaching数据库中的teacher表的tname列上创建非聚集唯一索引UQ_name ,若该索引已存在,则删除后重建 if exists(select name from sysindexes where name='UQ_name') drop index teacher.UQ_name go create index UQ_name on teaching.dbo.teacher(tname) 4.2 创建视图创建视图步骤： 1.在“对象资源管理器”窗口中展开“数据库”下的teaching子目录。 2.右击“视图”选项，从弹出的快捷菜单中选择“新建视图”命令，进入视图设计页面。 3.同时在弹出的“添加表”对话框中，选择course和score两个表，单击“添加”按钮。 4.点击“添加表”对话框中的“关闭”按钮，返回SQL Server Management Studio的视图设计界面进行设计。
(1)在teaching 数据库中创建视图v_teacher_course，包含教师编号、教师姓名、职称、课程号、课程名和任课班级，通过视图v_teacher_course将教师编号为t05017的教师职称更改为”副教授” create view v_teacher_course as select teacher.teacherno,tname, prof,course.courseno,cname,classno from teaching.dbo.teacher,teaching.dbo.course,teaching.dbo.teach_class where teacher.teacherno=teach_class.teacherno and teach_class.courseno=course.courseno go update v_teacher_course set prof='副教授' where teacherno='t05107' Go(2)修改v_course_avg视图的定义,添加WITH CHECK OPTION选项 alter view v_course_avg as select course.courseno, cname,AVG(final) as avg from teaching.dbo.course,teaching.dbo.score group by course.courseno,cname having AVG(final)>80 with check option 4.3 删除索引和视图 drop index cu_classno on teaching.dbo.student drop view v_course_avg通过本次实验，我通过创建索引理解什么是聚集和非聚集，什么是唯一索引，在course表创建、删除视图，给视图添加条件约束。在实验中，创建视图总显示CREATE VIEW必须是批处理中仅有的语句，导致运行失败，经过资料的查询，可能是在这段代码之前还有其他语句是同时处理，需要在这段代码的开始和结束加一个GO才行，最终问题也成功解决，也弥补了自己对SQL server使用的盲区。 5 存储过程和触发器 5.1 创建存储过程(1)创建一个存储过程ProcNum，查询每个班级中学生的人数，按班级号升序排序 create proc procnum as select classno, COUNT(*) as 人数 from teaching.dbo.student group by classno order by classno asc(2)利用SQL语句创建一个带有参数的存储过程ProcInsert，向score表插入一条选课记录，并查询该学生的姓名、选修的所有课程名称、平时成绩和期末成绩 create proc ProcInsert @sna nvarchar(255),@cna nvarchar(255), @final float,@usually float as insert into teaching.dbo.score values(@sna,@cna,@final,@usually) select sname,cname,usually, final from teaching.dbo.student join teaching.dbo.score on student.studentno=score.studentno join teaching.dbo.course on course.courseno=score.courseno(3)利用SQL语句创建一个存储过程ProcAvg，查询指定课程的平均分。班级号和课程名称由输入参数确定，计算出的平均分通过输出参数返回，若该存储过程已存在，则删除后重建 if exists (select * from sysobjects where name='procavg') drop proc procavg go create proc procavg @classno nvarchar(255),@cname nvarchar(255), @average float output as select @average=AVG(final*0.8+usually*0.2) from teaching.dbo.student join teaching.dbo.score on student.studentno=score.studentno join teaching.dbo.course on course.courseno=score.courseno where classno=@classno and cname=@cname 5.2 创建触发器(1)创建一个AFTER触发器trigsex，当插入或修改student表中性别字段sex时，检查数据是否只为“男”或“女” use teaching go create trigger trigsex on teaching.dbo.student after insert,update as begin declare @sex nvarchar(255) select @sex=sex from teaching.dbo.student if @sex='男' or @sex='女' begin raiserror('性别只能为男或女', 16,2)(2)利用SQL语句创建一个AFTER触发器trigforeign，当向score表中插入或修改记录时，如果插入或修改的数据与student表中数据不匹配，即没有对应的学号存在，则将此记录删除 use teaching go create trigger trigforeign on score after insert ,update as begin declare @studentno nvarchar(255) select @studentno=studentno from teaching.dbo.score if not exists (select * from student where studentno=@studentno) begin raiserror('不能插入学号不存在的学生的信息',16,2) end end(3)利用SQL语句创建一个AFTER触发器trigclassname，当向class表中插入或修改数据时，如果出现班级名称重复则回滚事务，若该触发器已存在，则删除后重建 if exists (select * from sysobjects where name='trigclassname') drop trigger trigclassname go use teaching go create trigger trigclassname on class after insert,update as begin declare @classname nvarchar(255) select @classname=classname from class if exists (select classname from class) begin raiserror('班级名称不能重复',16,2) rollback end end通过本次实验，我掌握了创建、管理存储过程的方法，并学会创建AFTER触发器，当重复则回滚事务，如果触发器存在则删除触发器。在实验中，创建触发器时，不知道为什么一直出现“对象不存在或对此操作无效”的提示，最后经过查找资料才知需要在前面加入“use go”代码才可以，最后问题也迎之而解，SQL Server方面的知识也更加全面。 |
【本文地址】
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |