数据库原理实验报告
--------------------------------------------------------------
实验一熟悉SQL SERVER的环境
（验证性实验 2学时）
1. 目的要求：
了解SQL Server数据库的各个实用工具
2. 实验内容：
使用SQL Server的相关工具，并掌握如何使用工具对SQL Server完成基本操作。
--------------------------------------------------------------
实验二建立表格，并插入若干记录
（验证性实验 2学时）
1.目的要求：
学会使用Create Table语句和Insert语句
2.实验内容：
在SQL SERVER中使用Create Table语句建立Student，SC，Course三张表格（包括主键），分析他们之间的关系，并使用Insert语句向这三张表格里添加至少10条记录。
1.建表
create table student--建立学生表
(sno char(9) primary key,
snamevarchar(20) unique,
ssex char(2),
sagesmallint,
sdeptvarchar(20)
);
create table course--建立课程表
(cno char(4) primary key,
cname char(40),
cpno char(4),
ccreditsmallint
);
create table sc--建立选课表
(sno char(9),
cno char(4),
gradesmallint,
primary key (sno,cno));
2.插入记录
--插入学生数据
insert into student (sno,sname,ssex,sdept,sage)values('0101','李1','男','cs','18');
insert into student (sno,sname,ssex,sdept,sage)values('0102','李2','女','is','19');
insert into student (sno,sname,ssex,sdept,sage)values('0103','张3','男','cs','20');
insert into student (sno,sname,ssex,sdept,sage)values('0104','张 4','男','cs','15');
insert into student (sno,sname,ssex,sdept,sage)values('0105','张5','女','cs','21');
insert into student (sno,sname,ssex,sdept,sage)values('0106','王6','男','ma','17');
insert into student (sno,sname,ssex,sdept,sage)values('0107','王7','男','cs','22');
insert into student (sno,sname,ssex,sdept,sage)values('0108','赵8','女','cs','19');
insert into student (sno,sname,ssex,sdept,sage)values('0109','赵9','男','ma','20');
insert into student (sno,sname,ssex,sdept,sage)values('0110','常10','男','cs','22');
insert into student (sno,sname,ssex,sdept,sage)values('0111','常11','男','cs','22');
--------------------------------------------------
--插入课程数据
insert into course (cno,cname,cpno,ccredit)values('1','语文','2','3');
insert into course (cno,cname,cpno,ccredit)values('2','数学',null,'3');
insert into course (cno,cname,cpno,ccredit)values('3','英语','1','2');
insert into course (cno,cname,cpno,ccredit)values('4','数据库','5','4');
insert into course (cno,cname,cpno,ccredit)values('5','数据结构','6','4');
insert into course (cno,cname,cpno,ccredit)values('6','PASCAL语音','7','3');
insert into course (cno,cname,cpno,ccredit)values('7','数据处理',null,'2');
insert into course (cno,cname,cpno,ccredit)values('8','信息系统','4','2');
insert into course (cno,cname,cpno,ccredit)values('9','操作系统','7','4');
--------------------------------------------------------
--插入选课表信息
insert into sc(sno,cno,grade)values('0101','1','88');
insert into sc(sno,cno,grade)values('0101','2','72');
insert into sc(sno,cno,grade)values('0101','3','52');
insert into sc(sno,cno,grade)values('0102','1','82');
insert into sc(sno,cno,grade)values('0102','2','86');
insert into sc(sno,cno,grade)values('0102','3','72');
insert into sc(sno,cno,grade)values('0103','1','62');
insert into sc(sno,cno,grade)values('0103','2','45');
insert into sc(sno,cno,grade)values('0104','5','92');
insert into sc(sno,cno,grade)values('0104','4','72');
insert into sc(sno,cno,grade)values('0104','1','72');
insert into sc(sno,cno,grade)values('0105','3','92');
insert into sc(sno,cno,grade)values('0105','1','72');
insert into sc(sno,cno,grade)values('0106','6','62');
insert into sc(sno,cno,grade)values('0107','4','100');
insert into sc(sno,cno,grade)values('0107','1','72');
insert into sc(sno,cno,grade)values('0105','4','94');
insert into sc(sno,cno,grade)values('0106','5','63');
insert into sc(sno,cno,grade)values('0107','5','59');
insert into sc(sno,cno,grade)values('0107','6','77');
insert into sc(sno,cno,grade)values('0105','6','98');
insert into sc(sno,cno,grade)values('0108','6','77');
insert into sc(sno,cno,grade)values('0108','5','98');
insert into sc(sno,cno,grade)values('0109','6','67');
insert into sc(sno,cno,grade)values('0109','4','97');
insert into sc(sno,cno,grade)values('0110','6','67');
insert into sc(sno,cno,grade)values('0110','1','98');
insert into sc(sno,cno,grade)values('0107','1','98');
insert into sc(sno,cno,grade)values('0108','1','98');
--------------------------------------------------------------
实验三修改表格结构，修改和删除表格中的数据
（验证性实验 4学时）
1.目的要求：
用ALTER语句修改表结构：添加列，修改列定义，删除列。使用UPDATE和DELETE语句修改和删除Student,sc, course表格中的数据。
2.实验内容：
添加，修改和删除表格中的列；
使用UPDATE和DELETE语句修改和删除Student, sc, course表格中的数据（注意表格中的主键）；
对表格做一些普通查询：单表查询，要求使用LIKE，BETWEEN..AND等比较条件：
1. update语句
--为Student表添加列“班级号”（10个长度定长字符串）。
Alter Table student add classnumvarchar(50)
update student Set classnum='1'Where sno = '0102'
update student Set classnum='1'Where sno = '0103'
update student Set classnum='1'Where sno = '0104'
update student Set classnum='3'where sno = '0105'
update student Set classnum='1'Where sno = '0106'
update student Set classnum='4'Where sno = '0107'
update student Set classnum='1'Where sno = '0108'
update student Set classnum='1'Where sno = '0109'
2. delete语句
--将”数据库”的选课记录全部删除
delete
from sc where '数据库'=(select cname from course where course.cno=sc.cno)
3.以like 或 between…and为比较条件
列出姓张的学生的学号、姓名。 select sno，sname from student where sname like‘张%’
检索成绩在70分至80分之间的学生学号,课程号和成绩
select *from sc
where grade between 70 and 80
--------------------------------------------------------------
实验四查询（多表查询，嵌套查询，分组查询）
（验证性实验 12学时）
1.目的要求：
实现单表和多表的普通查询和嵌套查询。包括返回单值的子查询和返回多值的子查询。使用5个聚合函数以及GROUP BY子句和HAVING子句实现分组查询.
2.实验内容
使用SELECT语句实现多表查询（需要连接）。
实现单表和多表的嵌套查询。包括返回单值的子查询（直接使用比较运算符）和返回多值的子查询（包括IN，ALL，SOME，EXISTS子查询）。
使用AVG，SUM，MAX，MIN，COUNT等5个聚合函数并结合GROUP BY字句和HAVING字句实现分组查询。
1.使用select语句实现多表查询
查询CS系姓李的学生选修的课程，列出学号，课程号和成绩。
selectsno,cno,grade
from student ,sc where sname like '李%'and sdept='cs'andstudent.sno=sc.sno
2.in/all/some/exists子查询
（1）检索选修了“C语言”课程的学生的姓名（可用子查询—IN或Exists）
selectsname
from student
where sno in(select sno from sc
wherecno in(select cno from course
where cname='数据库'))
（2）找出年龄最小的学生
select * from studentwhere sage <= all (
select sage from student )
（3）查询比任意一个女同学年龄大的男同学
selectsname from student
where sage >some
(select sage from student where ssex=‘f’)
and ssex=‘m’
（4）列出选修了C01课程的学生的学号、姓名
selectsno,sname
fromstudent
wereexists (select *
from sc
wheresc.sno = student.snoand
cno = ‘C01’)
3.AVG，SUM，MAX，MIN，COUNT等5个聚合函数并结合GROUP BY字句和HAVING字句实现分组查询。
（1）查询平均分在75以上的课程。
selectcno,avg(grade)
fromsc
group by cno
havingavg(grade)>=75;
（2）查询每个系学生的最高成绩。
selectsdept ,max(grade) as maxx
fromstudent,sc where student.sno=sc.sno
（3）查询女同学中成绩最高的学生所在的系。
selectsdept ,ssex,max(grade) as maxx
from student,sc where student.sno=sc.sno and ssex='女'
group by sdept,ssex
（4）查询选课人数最多的课程。
selectcno,count(cno) num
fromsc
group by cno
having count(cno)>=all(select count(cno) from sc group by cno)
（5）求每一个学生的最高分和最低分。
selectsno,max(grade)mgrade ,min(grade)mgrade
fromsc
group by sno
--------------------------------------------------------------
实验五为表格建立约束，修改约束和查询约束
（验证性实验 4学时）
1.目的要求：
使用ALTER语句和CREATE语句建立、修改、删除和查询约束
2.实验内容
为Student,sc, course表建立和删除主键约束，唯一性约束，检查约束，缺省约束，外键约束。 修改约束：关闭和打开某个约束。
使用系统存储过程，sp_help, sp_helpconstraint等对约束进行查询和管理
1.为Student,sc, course表建立和删除主键约束，唯一性约束，检查约束，缺省约束，外键约束。
（1）、为Student表的Sage（小于30），Ssex（M或F，缺省为M）添加约束。
alter table student constraint student_sage check(sage<=30)
alter table student constraint student_ssex check(ssex in ('男','女'))
（2）、为SC表的sno（外码），cno（外码），grade（1到100分）添加约束。
alter table sc add constraint sc_sno foreign key (sno)references student(sno)
alter table sc add constraint sc_cno foreign key (cno)references course(cno)
alter table sc add constraint sc_grade check(grade between 1 and 100)
（3）、为Course表的Cname（唯一），Credit（1到5）添加约束。
alter table course constraint course_cname check (unique(cname))
alter table course constraint couesr_credit check (credit between 1 and 5)
--------------------------------------------------------------
实验六Sql Server数据库安全管理
（验证性实验 4学时）
1.目的要求：
建立用户，为用户赋权限，收回权限，建立角色，给用户赋角色
2.实验内容
使用sp_addlogin或者SQL SERVER系统工具企业管理器创建用户，用GRANT语句或企业管理器给用赋权限。用REVOKE和DENY或使用工具将用户权限收回。
使用SP_ADDROLE或使用工具创建角色，用GRANT语句或使用工具给角色赋权限。用REVOKE和DENY或使用工具将角色权限收回。
1.使用sp_addlogin或者SQL SERVER系统工具企业管理器创建用户，用GRANT语句或企业管理器给用赋权限。用REVOKE和DENY或使用工具将用户权限收回。
（1）创建登录帐号
excesp_addlogin‘ll’, ’123’, ’kk’
（2）用GRANT语句或企业管理器给用赋权限。用REVOKE和DENY或使用工具将用户权限收回。
grant select on customers to u1
grant insert , update, delete on customers to pp
revoke select on student from u1
deny select on studentto u1
（3）
grant select ,insert,uodate on custumers
tosales
deny select,insert,updateon customers
topp
--------------------------------------------------------------
实验七使用ADO连接数据库
（综合性实验 4学时）
1.目的要求：
在高级语言中通过ADO连接SQL SERVER数据库，做一些简单应用
2.实验内容
在高级语言中使用ADO控件或ADO对象连接SQL SERVER数据库，实现对表格的基本操作，能与数据库交互。