四川师范大学计算机学院
实验报告册
院系名称： 计算机科学学院
课程名称： 数据库原理概论
实验学期 2013 年至 2014 年 第二 学期
专业班级： 网络工程3班
姓名： 学号：
指导教师： 俞晓
实验最终成绩：
《数据库原理》实验大纲
课程总学时：64 学分：2
实验学时：32 实验个数： 7 实验学分： 1
课程性质：（专业必修）
适用专业： 计算机软件工程，计算机科学技术，网络工程，电子商务
教材及参考书：数据库系统概论
大纲执笔人： 俞晓
大纲审定人：
一、实验课的性质与任务
数据库技术是计算机科学技术中发展最快的领域之一，也是应用最广的技术之一，它已成为计算机信息系统与应用系统的核心技术和重要基础。
本实验课程主要是在SQL SERVER 下实现SQL语言的基本操作：建立、修改、删除表格等数据库对象，插入、修改、删除表格中的数据，对数据做多种查询。并熟悉使用SQL SERVER的系统工具创建数据库，用户等。最后用VB连接SQL SERVER做一个简单的数据库系统。该课程注重教学体系的严密性、教学内容的实用性、知识体系的扩展性。通过练习，提高学生的动手能力，使学生能较熟练的在SQL SERVER数据库管理系统中实现数据管理和维护。
二、实验课程目的与要求
1．实验目的：本课程的目的和任务是使学生全面地了解和掌握数据库的原理，SQL SERVER系统的基本操作和应用技术，以适应社会对数据库技术应用的大量需求。
2．实验要求：
1）实验安排在上机时间进行。
2）实验以前，学生应对实验设计的相关知识点和相关设计技术有所了解
3）每个实验要求写出实验报告（实验报告包括：实验内容、目的、实现方法、调试结果）
三、实验项目及内容提要
四、实验内容安排：
实验1 熟悉SQL SERVER的环境
（ 操作性实验 2学时）
实验目的
了解SQL Server数据库的各个工具软件
实验内容
使用SQL Server的相关工具，其中包括查询分析器和企业管理器，在查询分析器里写简单的SQL语句，在企业管理器中建立表格等。
工具及准备
SQL SERVER数据库
实验2 建立表格，并插入若干记录
（ 操作性实验 2学时）
实验目的
学会使用Create Table语句和Insert语句
实验内容
在SQL SERVER的查询分析器中使用Create Table语句建立Student，SC，Course三张表格（包括主键），并用Insert语句向这三张表格里添加至少10条记录。
一、建表
建立学生表
Create Table Student
(
sno char(10) primary key ,
sname varchar(20) not null,
sage smallint,
ssex char(2),
sdept varchar(20))
建立课程表
Create Table Course
(
cno char(10),
primary key (cno) ,
cname varchar(20) ,
cpno char(10),
credit smallint )
建立选课表
Create Table SC
(sno char(10),
cno char(10),
grade smallint,
primary key (sno, cno) )
二、 插入数据
插入学生数据
insert into student values('001','张四','20','男','CS');
insert into student values('002','刘五','19','女','IS');
insert into student values('003','邓小庆','21','女','MA');
insert into student values('004','彭大琛','22','男','IS');
insert into student values('005','张三','19','男','CS');
insert into student values('006','李四','21','女','IS');
insert into student values('007','雷锋','20','男','MA');
insert into student values('008','刘健','18','女','CS');
insert into student values('009','陈东','23','女','MA');
insert into student values('010','代大','19','男','IS');
insert into student values('011','都敏俊','20','女','CS');
insert into student values('012','余小','20','女','MA');
insert into student values('013','余雨','20','女','MA');
插入课程数据
insert into course values('1001','数据库','5','4')
insert into course values('1002','数学',' ','2')
insert into course values('1003','信息系统',' ','4')
insert into course values('1004','操作系统','6','3')
insert into course values('1005','数据结构','7','4')
insert into course values('1006','数据处理',' ','2')
insert into course values('1007','p语言','6','4')
insert into course values('1008','C语言',' ','4')
插入选课数据
insert into sc values('001','1001','92')
insert into sc values('001','1002','90')
insert into sc values('001','1003','95')
insert into sc values('001','1004','56')
insert into sc values('002','1001','93')
insert into sc values('002','1002','78')
insert into sc values('002','1005','82')
insert into sc values('002','1006','65')
insert into sc values('002','1007','71')
insert into sc values('003','1006','88')
insert into sc values('003','1008','66')
insert into sc values('004','1001','59')
insert into sc values('004','1003','74')
insert into sc values('004','1005','66')
insert into sc values('004','1008','66')
insert into sc values('005','1002','70')
insert into sc values('005','1005','87')
insert into sc values('005','1006','64')
insert into sc values('005','1007','78')
insert into sc values('005','1008','80')
insert into sc values('006','1002','90')
insert into sc values('006','1004','67')
insert into sc values('006','1005','89')
insert into sc values('006','1008','70')
insert into sc values('007','1003','76')
insert into sc values('007','1004','46')
工具及准备
SQL SERVER数据库
实验3 修改表格结构，修改和删除表格中的数据
（ 操作性实验 4学时）
实验目的
用ALTER语句修改表结构：添加列，修改列定义，删除列。使用UPDATE和DELETE语句修改和删除Student,sc, course表格中的数据。
实验内容
添加，修改和删除列：为STUDENT表添加一列，观察添加的列对应于已经有的数据行的数据是多少？为其修改数据；修改STUDENT表的列定义，删除才添加的多余的列。
使用UPDATE和DELETE语句修改和删除Student, sc, course表格中的数据（注意表格中的主键），并对表格做一些普通查询：单表查询，要求使用LIKE，BETWEEN..AND等比较条件。
一、
1、添加一列，并观察
代码：alter table student add sarea varchar(50);
2、修改列定义：
代码：alter table student alter column sarea varchar(100);
3、删除才添加的多余的列：
代码：alter table student drop column sarea;
二、UPDATE
update student set classno=1 where sno = 001;
update student set classno=2 where sno = 002;
update student set classno=1 where sno = 003;
update student set classno=3 where sno = 004;
update student set classno=2 where sno = 005;
update student set classno=1 where sno = 006;
update student set classno=3 where sno = 007;
update student set classno=2 where sno = 008;
update student set classno=3 where sno = 009;
update student set classno=2 where sno = 010;
update student set classno=1 where sno = 011;
update student set classno='T'+classno where sno=001;
update student set classno='T'+classno where sno=003;
update student set classno=rtrim(classno) + 'T' where sno =002;
update student set classno=2 where sno = 002;
update student set classno= substring(classno,2,10) where sno = 003;
update student set classno= substring(classno,1,len(classno) -1) where sno = 002;
三、DELETE
delete from student where sno = 001;
delete from student where classno is null
delete from sc where grade<50
四、要求使用LIKE，BETWEEN..AND等比较条件。
select sno,sname from student where sname like '刘%';
select sname from sc,student
where grade between 60 and 80 and student.sno=sc.sno;
工具/准备工作
SQL SERVER数据库
实验4 查询（多表查询，嵌套查询，分组查询）
（ 操作性实验 12学时）
实验目的
实现单表和多表的普通查询和嵌套查询。包括返回单值的子查询和返回多值的子查询。使用5个聚合函数以及GROUP BY子句和HAVING子句实现分组查询.
实验内容
使用SELECT语句实现多表查询（需要连接）。
实现单表和多表的嵌套查询。包括返回单值的子查询（直接使用比较运算符）和返回多值的子查询（包括IN，ALL，SOME，EXISTS子查询）。
使用AVG，SUM，MAX，MIN，COUNT等5个聚合函数并结合GROUP BY字句和HAVING字句实现分组查询。
--检索张三同学所学课程的课程号及成绩
select cno,grade from student,sc where student.sno = sc.sno and sname = '张三';
--检索所有学生的姓名、选课名称和成绩
select sname,cname,grade from student,course,sc where student.sno=sc.sno and course.cno=sc.cno
--查询选修‘’课程，并且年龄不大于岁的学生的学号和成绩，并按成绩降序排列
select student.sno,grade from student,sc where student.sno = sc.sno and sage <=26 and cno = '1005' order by grade desc;
--查询CS系姓李的学生选修的课程，列出学号，课程号和成绩。
select student.sno,cno,grade from student,sc where student.sno =sc.sno and sname like '张%' and sdept= 'cs'
select *from student
--查询选修了数据库课程的学生的学号，成绩，按成绩降序排列
select student.sno,grade from student,sc
where cno =(select cno from course where cname = '数据库' and course.cno=sc.cno)and student.sno = sc.sno
order by grade desc
--找出学分为分以上的课程的选修情况，列出学号，课程名，成绩。
select student.sno,cname,grade from student,course,sc
where credit >= 4 and course.cno=sc.cno and student.sno = sc.sno
select * from course
--检索数据库的成绩在分以上的学生的学号和姓名
select student.sno,sname from student,sc,course
where cname='数据库'and student.sno=sc.sno and sc.cno=course.cno and grade > 90
--查询和数据库相同学分的课程
select cname from course where credit in
(select credit from course where cname = '数据库') and cname <>'数据库'
--查询选修了数据库课程的学生的学号。
select sno from sc where cno in (select cno from course where cname = '数据库')
--查询成绩最高的学生的姓名
select sname from student where sno in(select sno from sc where grade >=all(select grade from sc))
select *from sc
select * from student
--查询CS系成绩最高的学生的学号、姓名以及成绩。
select student.sno,sname,grade from student,sc where sdept = 'cs' and
grade >= all (select grade from sc) and student.sno=sc.sno
--查询数据库课程成绩最高的学生的姓名。
select sname from student where sno in
(select sno from sc where cno in
(select cno from course where cname = '数据库')
and grade >= all(select grade from sc where cno in (select cno from course where cname = '数据库')))
--查询每门课程的平均成绩
select avg(grade)from sc group by cno
--查询每个系学生的最高成绩
select sdept ,MAX(grade) from SC ,Student where sc.sno = student.sno group by sdept
--查询平均分在以上的课程
select cno,AVG(grade ) from SC group by cno having AVG (grade)>75
select AVG(grade) from SC where cno = '1004'
select * from sc
--查询女同学中平均成绩最高的学生所在的系。
select sdept from student where sno in(select student.sno from sc,student where student.sno=sc.sno and ssex='女'
group by student.sno
having avg(grade)>=all (select avg(grade) savg from sc,student where student.sno=sc.sno and ssex='女' group by student.sno))
select *from SC,Student
where ssex='女' order by grade desc
--查询选课人数最多的课程
select cno , count(cno) ccno from SC group by cno having
COUNT (cno )>= all (select COUNT(cno) from SC group by cno )
select *from Course
--检索所有学生中年龄最大的学生的姓名及年龄。
select sname,sage from Student where sage >=all (select sage from Student)
--求每一个学生的最高分和最低分。
select sno,MAX(grade) mgrade,MIN(grade) mingrade from SC group by sno
--查询CS系所有男同学考C05课程的成绩，列出这些学生的学号，姓名，成绩，并按成绩降序排列。
select student.sno,sname,grade from Student,SC
where sdept = 'CS' and Student.sno=SC.sno and ssex='男' and cno = '1005'
order By grade desc
select sname,grade from SC,Student where sc.sno=Student.sno and ssex = '男' and sdept='cs' and cno='1005'
Select sname from student where sage >some
(select sage from student where ssex='男') and ssex='女'
Select cno ,avg(grade) as avg from sc group by cno
--检索选修了“C语言”课程的学生的姓名（可用子查询—IN或Exists）
select sname from Student where sno in(select sno from SC where cno in(select cno from Course where cname = 'C语言'))
--5检索选修了课程号为C01或C02课程，且成绩高于或等于分的学生的姓名，课程名和成绩
select sname,cname,grade from Student,SC,Course
where Student.sno=sc.sno and SC.cno=Course.cno and sc.cno in(1001,1002) and grade >=70
--有问题检索所有学生的姓名、所选课程的课程名和成绩以及课程号，并且按成绩的降序和课程号的升序进行排列（使用外连接将没有选课的同学列出来）。
select sname,cno,grade from Student,SC
where Student left join sc on student.sno = sc.sno and sc.sno not in(select sno from SC)
order by cno desc, grade asc
--列出没有选课的学生姓名
select sname from Student where sno not in (select sno from SC)
select * from SC where sno =(select sno from Student where sname = '代大')
--8. 列出平均分最高的学生所在系的所有学生的姓名
select sname from Student where sno in
(select sno from Student where sdept in
(select sdept from Student where sno in
(select sno from SC group by sno having AVG(grade)>=all(select AVG(grade)from SC group by sno ) )))
工具/准备工作
SQL SERVER数据库
实验5 为表格建立约束，修改约束和查询约束
（ 操作性实验 4学时）
实验目的
使用ALTER语句和CREATE语句建立、修改、删除和查询约束
实验内容
为Student,sc, course表建立和删除主键约束，唯一性约束，检查约束，缺省约束，外键约束。
暂时关闭某个约束。
使用系统存储过程， sp_help, sp_helpconstraint等对约束进行查询和管理
--1、为Student表的Sage（小于），Ssex（M或F，缺省为M）添加约束。
alter table student add constraint stu_sage check(sage<30);
alter table student add constraint stu_ssex check(ssex in ('m','f'));
alter table student add constraint sex default 'm' for ssex;
select * from student;
--2、为SC表的sno（外码），cno（外码），grade（到分）添加约束。
alter table sc add constraint sc_sno foreign key(sno) references student(sno),
foreign key(cno) references course(cno);
alter table sc add constraint sc_garde check(grade between 0 and 100);
--3、为Course表的Cname（唯一)，Credit（到）添加约束。
alter table course add constraint c_cname unique cname;
alter table course add constraint c_credit check(credit between 1 and 5);
--删除约束
alter table student drop stu_sage;
工具/准备工作
SQL SERVER数据库
实验6 Sql Server数据库安全管理
（ 操作性实验 4学时）
实验目的
建立用户，为用户赋权限，收回权限，建立角色，给用户赋角色
实验内容
使用 sp_addlogin或者SQL SERVER系统工具企业管理器创建用户，用GRANT语句或企业管理器给用户赋权限。用REVOKE和DENY或企业管理器将用户权限收回。
使用SP_ADDROLE或企业管理器创建角色，用GRANT语句或企业管理器给角色赋权限。用REVOKE和DENY或企业管理器将角色权限收回。
exec sp_addlogin lwq,lwq;
--用GRANT语句或企业管理器给用赋权限。用REVOKE和DENY或使用工具将用户权限收回。
grant select on student to ,lwq;
grant insert，update,delete on student to ,lwq;
deny select on student to ,lwq;
--创建角色
sp_addrole r1 ,user1;
--用GRANT语句或企业管理器给角色赋权限。用REVOKE和DENY或使用工具将角色权限收回。
grant select on student to lwq;
revoke select on student from lwq;
deny select on sc to lwq;
工具/准备工作
SQL SERVER数据库
实验7 使用ADO技术连接数据库
（操作性实验 4学时）
实验目的
在高级语言中通过ADO连接SQL SERVER数据库，做一些简单应用
实验内容
在高级语言中使用ADO控件或ADO对象连接SQL SERVER数据库，实现对表格的简单查询
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strC As String
Dim strSQL As String
Sub conDB()
strC = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=lwq;passwd=123456;Initial Catalog=master;Data Source=."
Set con = New ADODB.Connection
con.Open strC
End Sub
工具/准备工作
1． SQL SERVER
2． 高级语言（如VC，VB等）