数据库实验报告--SQL server_sqlserver数据库实验

前言

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

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

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

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

 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'

 

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

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

 

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