数据库实验报告--SQL server
作者:mmseoamin日期:2023-12-18

前言

       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%,其他均采用默认设置。

数据库实验报告--SQL server,第1张

 1.2 创建表

       在查询编辑器中输入创建表的代码,分别创建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','zhao@163.com')
Insert into teaching.dbo.student
(studentno,sname,sex,birthday,classno,point,phone,Email)
Values
('082211121','余某','男','1989-12-12','1702','102','15211111111','yu@163.com')
Insert into teaching.dbo.student
(studentno,sname,sex,birthday,classno,point,phone,Email)
Values
('082211122','程某','女','1989-12-13','1703','103','15222222222','cheng@163.com')
Insert into teaching.dbo.student
(studentno,sname,sex,birthday,classno,point,phone,Email)
Values
('082211123','周某','男','1989-12-14','1704','104','15233333333','zhou@163.com')
Insert into teaching.dbo.student
(studentno,sname,sex,birthday,classno,point,phone,Email)
Values
('082211124','霍某','女','1989-12-15','1705','105','15244444444','huo@163.com')

 student 表数据

数据库实验报告--SQL server,第2张

向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表数据

数据库实验报告--SQL server,第3张

向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表数据

数据库实验报告--SQL server,第4张

向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表数据

数据库实验报告--SQL server,第5张

向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表数据

数据库实验报告--SQL server,第6张

向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表数据 

数据库实验报告--SQL server,第7张

 1.4 数据操作

      向student表插入、删除、修改一条记录

插入一条记录:

insert into teaching.dbo.student
values('0937221508','平静','女','1998-02-12','1709','109','15299999999','ping@qq.com')

删除一条记录:

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的外键。

数据库实验报告--SQL server,第8张

 2.2 创建unique约束

     在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 credit<=6)
Go
alter table course
drop constraint ck_credit

 2.4 创建、删除规则

     为teaching数据库创建规则prof_rule,规定教师职称取值只能为”助教”、“讲师”、“副教授”、“教授”,并将其绑定到teacher表的Prof列,删除创建的规则。

create rule prof_rule 
as @prof='助教' and @prof='讲授' and @prof='副教授' and @prof='教授'
Go
Exec sp_bindrule 'prof_rule','teacher.prof'   
EXEC sp_unbindrule 'teacher.prof'
Drop rule prof_rule

通过本次实验,我掌握了数据完整性的类型和实现机制,并掌握了约束、规则对象的创建和修改,实验中,在删除创建规则中遇到了一些问题,通过自己上网查找资料和翻阅书籍,最终得以解决,也让我对数据库这部分内容更加清晰的了解,希望自己在今后的学习中更加的努力认真。

3 数据检索

 3.1 单表数据查询

     (1)查询所有课程的课程编号、课程名和学分,查询160501班所有学生的基本信息

select courseno,cname,credit
from teaching.dbo.course
select * from teaching.dbo.student
where classno='160501'

 数据库实验报告--SQL server,第9张

     (2)查询student表中所有年龄大于20岁的男生的姓名和年龄

select sname as '姓名',
YEAR(getdate())-YEAR(birthday)as'年龄'
from teaching.dbo.student
where sex='男' and
YEAR(getdate())-YEAR(birthday)>20

 数据库实验报告--SQL server,第10张

      (3)查询计算机学院教师的专业名称

select prof from teaching.dbo.teacher
where department='计算机学院'

 数据库实验报告--SQL server,第11张

     (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 学生选课统计表

 数据库实验报告--SQL server,第12张

      (5)查询student表中所有学生的基本信息,查询结果按班级号classno升序排序,同一班级中的学生按入学成绩point降序排列

select * from teaching.dbo.student
order by classno,point desc

 数据库实验报告--SQL server,第13张

      (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

 数据库实验报告--SQL server,第14张

      (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)

数据库实验报告--SQL server,第15张

     (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

 数据库实验报告--SQL server,第16张

通过本次实验,我掌握了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

 数据库实验报告--SQL server,第17张

      (2)查询教师基本信息和教授课程信息,其中包括未分配课程的教师信息

select teacher.teacherno,tname,major,prof,courseno
from teaching.dbo.teacher left join teaching.dbo.teach_class on
teacher.teacherno=teach_class.teacherno

 数据库实验报告--SQL server,第18张

      (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

 数据库实验报告--SQL server,第19张

      (4)查询没有被任何学生选修的课程编号、课程名称和学分(子查询)

select courseno,cname,credit from teaching.dbo.course
where not exists (select * from teaching.dbo.score
where score.courseno=course.courseno)

 数据库实验报告--SQL server,第20张

      (5)查询入学成绩最高的学生学号、姓名和入学成绩(子查询)

select studentno,sname,point
from teaching.dbo.student
where studentno in (select top 1 studentno from teaching.dbo.student
order by point desc)

 数据库实验报告--SQL server,第21张

      (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')

数据库实验报告--SQL server,第22张

      (7)查询每门课程的课程号、课程名和选修该课程的学生人数,并按所选人数升序排序

select courseno,cname,COUNT(*)
from teaching.dbo.course
group by courseno,cname
order by COUNT(*)

 数据库实验报告--SQL server,第23张

      (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

数据库实验报告--SQL server,第24张

通过本次实验,我掌握了多表连接的各种方法(内连接和外连接),掌握了子查询的方法(相关子查询和不相关子查询),还有游标处理集的基本过程,会使用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的视图设计界面进行设计。

  数据库实验报告--SQL server,第25张   

   

 (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方面的知识也更加全面。