行业知识
数据库实验报告--SQL server
前言
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.courseValues('c05120','数据结构','必修','60','1')Insert into teaching.dbo.courseValues('c05121','操作系统','选修','61','2')Insert into teaching.dbo.courseValues('c05122','数据逻辑','必修','62','3')Insert into teaching.dbo.courseValues('c05123','计算方法','必修','63','4')Insert into teaching.dbo.courseValues('c05124','大学体育','选修','64','5') course表数据
向score表插入数据：
Insert into teaching.dbo.scoreValues('0822111201','c06101','89','91')Insert into teaching.dbo.scoreValues('0822111202','c06102','88','92')Insert into teaching.dbo.scoreValues('0822111203','c06103','87','93')Insert into teaching.dbo.scoreValues('0822111204','c06104','86','94')Insert into teaching.dbo.scoreValues('0822111205','c06105','85','95') score表数据
向teacher表插入数据：
Insert into teaching.dbo.teacherValues('t05001','赵某某','数据结构','教授','计算机学院')Insert into teaching.dbo.teacherValues('t05002','余某某','操作系统','教授','计算机学院')Insert into teaching.dbo.teacherValues('t05003','程某某','数据逻辑','教授','经济学院')Insert into teaching.dbo.teacherValues('t05004','周某某','计算方法','导师','计算机学院')Insert into teaching.dbo.teacherValues('t05005','霍某某','大学体育','教授','经济学院') teacher表数据
向class表插入数据：
Insert into teaching.dbo.classValues('080601','机械','机械学院','马飞')Insert into teaching.dbo.classValues('080602','机械','机械学院','李飞')Insert into teaching.dbo.classValues('080603','机械','机械学院','张飞')Insert into teaching.dbo.classValues('080604','机械','机械学院','周飞')Insert into teaching.dbo.classValues('080605','机械','机械学院','余飞') class表数据
向teach_class表插入数据：
Insert into teaching.dbo.teach_classValues('t05001','080601','c05120')Insert into teaching.dbo.teach_classValues('t05002','080602','c05121')Insert into teaching.dbo.teach_classValues('t05003','080603','c05122')Insert into teaching.dbo.teach_classValues('t05004','080604','c05123')Insert into teaching.dbo.teach_classValues('t05005','080605','c05124') teach_class表数据
1.4 数据操作
向student表插入、删除、修改一条记录
插入一条记录：
insert into teaching.dbo.studentvalues('0937221508','平静','女','1998-02-12','1709','109','15299999999','ping@qq.com') 删除一条记录：
delete from teaching.dbo.studentwhere studentno='0937221508'
修改一条记录：
update teaching.dbo.student set point=888where studentno='0937221508'
通过本次实验，我掌握了创建、修改数据库的方法及管理数据库的方法，并掌握了创建、修改表结构的方法及插入、更新和删除表数据的方法，让我对SQL server2008的使用更加得心应手。总而言之，本次实验的内容让我受益匪浅，也为数据库这门课程的学习做了铺垫作用。
2 数据完整性的设置
2.1 设置外键
利用SQL Server Management Studio将teaching数据库中score表的courseno列设置为引用表course的外键。
2.2 创建unique约束
在teaching数据库中class表的classname创建UNIQUE约束。
alter table classadd constraint u_classnameunique nonclustered(classname)go
2.3 创建、删除check约束
为teaching数据库中student表的birthday列创建check约束,规定学生的年龄在17~25之间,为course表的credit列创建check约束,规定学分的取值范围为1~6,删除check约束。
alter table studentadd constraint ck_birthdaycheck(year(getdate()-year(birthday)) between 17 and 25)goalter table courseadd constraint ck_credit check(credit>=1 and credit20
(3)查询计算机学院教师的专业名称
select prof from teaching.dbo.teacherwhere department='计算机学院'
(4)查询每名学生的学号、选修课程数目、总成绩，并将查询结果存放到生成的“学生选课统计表”
use teachinggoif exists (select * from sys.objects where name='学生选课统计表')drop table 学生选课统计表select studentno,COUNT(*) as '选修课程数目',sum(final) as '总成绩' into 学生选课统计表from scoregroup by studentnoselect * from 学生选课统计表
(5)查询student表中所有学生的基本信息,查询结果按班级号classno升序排序,同一班级中的学生按入学成绩point降序排列
select * from teaching.dbo.studentorder by classno,point desc
(6)查询各班学生的人数(按班级分组),查询各班期末成绩的最高分和最低分
select COUNT(*) as '人数',MAX(final) as '最高分',MIN(final) as '最低分'from teaching.dbo.student,teaching.dbo.scorewhere student.studentno=score.studentnogroup by classnoorder by classno
(7)查询教授一门及以上课程的教师编号、课程编号和任课班级
select teacherno,courseno,classnofrom teaching.dbo.teach_classwhere exists (select courseno,COUNT(*)from teaching.dbo.course group by coursenohaving COUNT(*)>=1)
(8)查询课程编号以c05开头,被三名及以上学生选修,且期末成绩的平均分高于75分的课程号、选修人数和期末成绩平均分，并按平均分降序排序。
use teachinggoselect courseno,count(studentno)as '选修人数',avg(final) as '期末平均分'from scorewhere courseno like 'c06%' and final is not nullGROUP BY coursenohaving COUNT(studentno)>=3 and AVG(final)>75order 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 onscore.courseno=teach_class.coursenogroup by classnoorder by AVG(final) desc
(2)查询教师基本信息和教授课程信息,其中包括未分配课程的教师信息
select teacher.teacherno,tname,major,prof,coursenofrom teaching.dbo.teacher left join teaching.dbo.teach_class onteacher.teacherno=teach_class.teacherno
(3)查询两门及以上课程的期末成绩超过80分的学生姓名及其平均成绩
select sname,AVG(final)from teaching.dbo.score join teaching.dbo.student on score.studentno=student.studentnowhere exists (select courseno,COUNT(*)from teaching.dbo.score group by courseno having COUNT(*)>=2)and final>80group by sname
(4)查询没有被任何学生选修的课程编号、课程名称和学分（子查询）
select courseno,cname,credit from teaching.dbo.coursewhere not exists (select * from teaching.dbo.scorewhere score.courseno=course.courseno)
(5)查询入学成绩最高的学生学号、姓名和入学成绩（子查询）
select studentno,sname,pointfrom teaching.dbo.studentwhere studentno in (select top 1 studentno from teaching.dbo.studentorder by point desc)
(6)查询同时教授c05120号和c05121号课程的教师信息(子查询)
select * from teaching.dbo.teacher left join teaching.dbo.teach_classon teacher.teacherno=teach_class.teachernowhere courseno='c05120' and teacher.teacherno in (select teacherno from teaching.dbo.teach_class where courseno='c05121')
(7)查询每门课程的课程号、课程名和选修该课程的学生人数，并按所选人数升序排序
select courseno,cname,COUNT(*)from teaching.dbo.coursegroup by courseno,cnameorder by COUNT(*)
(8)使用游标输出学生姓名、选修课程名称和期末考试成绩
declare student_cursor cursorfor select sname,cname,finalfrom teaching.dbo.student join teaching.dbo.score on student.studentno=score.studentnojoin teaching.dbo.course on score.courseno=course.coursenoopen student_cursordeclare @sname nchar(8),@cname nchar(20),@final numeric(6,2)fetch next from student_cursor into @sname,@cname,@finalprint '学生名字课程名称期末成绩'print'-----------------------'while @@FETCH_STATUS=0beginprint @sname+@cname+cast(@final as nchar(6))fetch next from student_cursor into @sname,@cname,@finalendclose student_cursordeallocate student_cursor
通过本次实验，我掌握了多表连接的各种方法（内连接和外连接），掌握了子查询的方法（相关子查询和不相关子查询），还有游标处理集的基本过程，会使用JOIN ON连接两个及两个以上表，查询到了表中相关信息，也学会了在SELECT查询语句中再嵌套一个SELECT语句，还学会了使用游标对查询结果集进行处理。总之，本次的多表查询与子查询的实验，我受益匪浅，更坚定了我对这门课程的学习的决心。
4 索引和视图
4.1 创建索引
(1)在teaching数据库的student表的classno字段创建非聚集非唯一索引cu_classno
create nonclustered index cu_classnoon teaching.dbo.student(classno)
(2)在teaching数据库中的teacher表的tname列上创建非聚集唯一索引UQ_name ,若该索引已存在,则删除后重建
if exists(select namefrom sysindexeswhere name='UQ_name')drop index teacher.UQ_namegocreate 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,classnofrom teaching.dbo.teacher,teaching.dbo.course,teaching.dbo.teach_class whereteacher.teacherno=teach_class.teachernoandteach_class.courseno=course.coursenogoupdate v_teacher_courseset prof='副教授'where teacherno='t05107'Go
(2)修改v_course_avg视图的定义,添加WITH CHECK OPTION选项
alter view v_course_avg asselect course.courseno,cname,AVG(final) as avgfrom teaching.dbo.course,teaching.dbo.scoregroup by course.courseno,cnamehaving AVG(final)>80with check option
4.3 删除索引和视图
drop index cu_classno on teaching.dbo.studentdrop 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.studentgroup 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.scorevalues(@sna,@cna,@final,@usually) select sname,cname,usually,final from teaching.dbo.student join teaching.dbo.score on student.studentno=score.studentnojoin teaching.dbo.course on course.courseno=score.courseno
(3)利用SQL语句创建一个存储过程ProcAvg，查询指定课程的平均分。班级号和课程名称由输入参数确定，计算出的平均分通过输出参数返回，若该存储过程已存在，则删除后重建
if exists (select * from sysobjects where name='procavg')drop proc procavggocreate proc procavg@classno nvarchar(255),@cname nvarchar(255),@average float outputas select @average=AVG(final*0.8+usually*0.2)from teaching.dbo.student join teaching.dbo.score on student.studentno=score.studentnojoin teaching.dbo.course on course.courseno=score.coursenowhere classno=@classno and cname=@cname
5.2 创建触发器
(1)创建一个AFTER触发器trigsex，当插入或修改student表中性别字段sex时，检查数据是否只为“男”或“女”
use teachinggocreate trigger trigsex on teaching.dbo.student after insert,updateas begin declare @sex nvarchar(255) select @sex=sex from teaching.dbo.studentif @sex='男' or @sex='女' begin raiserror('性别只能为男或女', 16,2) (2)利用SQL语句创建一个AFTER触发器trigforeign，当向score表中插入或修改记录时，如果插入或修改的数据与student表中数据不匹配，即没有对应的学号存在，则将此记录删除
use teachinggocreate trigger trigforeign on score after insert ,update asbegin declare @studentno nvarchar(255) select@studentno=studentno from teaching.dbo.score if not exists (select * fromstudent where studentno=@studentno) beginraiserror('不能插入学号不存在的学生的信息',16,2) end end (3)利用SQL语句创建一个AFTER触发器trigclassname，当向class表中插入或修改数据时，如果出现班级名称重复则回滚事务，若该触发器已存在，则删除后重建
if exists (select * from sysobjects where name='trigclassname')drop trigger trigclassnamegouse teachinggocreate trigger trigclassname on class after insert,updateas begin declare @classname nvarchar(255)select @classname=classname from class if exists (select classname from class) beginraiserror('班级名称不能重复',16,2) rollback end end 通过本次实验，我掌握了创建、管理存储过程的方法，并学会创建AFTER触发器，当重复则回滚事务，如果触发器存在则删除触发器。在实验中，创建触发器时，不知道为什么一直出现“对象不存在或对此操作无效”的提示，最后经过查找资料才知需要在前面加入“use go”代码才可以，最后问题也迎之而解，SQL Server方面的知识也更加全面。
