攀枝花学院计算机学院的实验报告

             实验一

实验名称：数据库基本操作实验

实验学时：2

实验目的：

1、通过实验，加深学生对数据库基本概念和理论的理解与掌握，能够更好的理论联系实际。

2、通过实验，使学生掌握如下的数据库操作的基本技能与方法：

创建、修改、删除数据库

创建、修改、删除数据库表；

创建数、删除数据库表的索引；

实验内容及要求（用企业管理期或查询分析器完成）：

一、创建、修改、删除数据库；（分别用企业管理器、查询分析器完成）

1创建数据库“MyDB”，该数据库有两个数据文件，一个日志文件。数据文件中的一个件初始大小30M，最大值50M，文件属性为自动增长，另一个固定尺寸为20M。日志文件初始大小为50M，每次增长10M。

use master  go  create database MyDB on

primary (name=class1_data,Filename='E:\\data\\class1.mdf',Size=30MB,maxsize=50MB,

Filegrowth=10%),

filegroup classgroup

(name=class2_data,Filename='E:\\data\\class1.ndf',Size=20MB,Filegrowth=10MB)

log on

(name=classlog1,Filename= 'E:\\data\\classlog1.ldf ',Size=50MB,Maxsize=80MB,

Filegrowth=10MB),

(name=classlog2,Filename='E:\\data\\classlog2.ldf ',Size=50MB,Maxsize=80MB,

Filegrowth=10MB)

2、修改数据库“MyDB”，删除固定大小的数据文件，将数据库名称改为“TESTDB”，增加一个数据文件，数据文件位于E:\，其他参数默认。

alter database MyDB remove file class2_data 

alter database MyDB modify name=TESTDB

alter database TESTDB add file (name=class3_data,filename='E:\\data\\class3.ndf')

3、删除创建的数据库。

use master go drop database TESTDB

二、熟悉数据库表的定义、修改操作

1、在Northwind数据库下建立数据库表TEST，表结构为

create table TEST

(ShipperID int,   CompanyName nvarchar(40),  Phone  nvarchar(24) )

2、将数据表名称更改为TEST1。

exec sp_rename 'TEST','TEST1'

3、将CompanyName的字段长度缩小为30。

alter table TEST1 alter column CompanyName nvarchar(30)

4、添加ShippID为主键。

alter table TEST1 alter column ShipperID int not null

alter table TEST1 add primary key(ShipperID)

5、将ShippID数据类型改为CHAR（4）。

alter table TEST1 alter column ShipperID int char(4)

6、将ShippID列名改为SID。

exec sp_rename 'TEST1.ShipperID','SID','column'

7、添加列名TestCol，数据类型为VARCHAR(32)。

alter table TEST1 add  TestCol varchar(32)

8、删除列TestCol

alter table TEST1 drop column TestCol

9、调换CompanyName、Phone列的顺序。

三、熟悉数据库表索引的建立

1、为上述表建立聚簇索引（ShipperID）与非聚簇（CompanyName）唯一升序索引。输入如下两条数据进行测试（观察现象）。

1    A     3332223

2    A     3352223

Create cluster index TESTshipperID on TEST1(ShipperID);

Create unique index TESTCompanyName on TEST1(CompanyName);

2、删除CompanyName列上的索引，在CompanyName建立聚簇索引。

Drop index TEST1CompanyName;

3、删除该数据库表

Drop table TEST1

实验二

实验名称：数据库完整性约束实验

实验学时：2

实验目的：

数据库完整性是保证数据库中数据正确性的重要手段，通过实验，使学生加深对数据库完整性的基本概念的理解与掌握,并能够熟练创建、修改、删除各类完整性约束，从而达到灵活应用的目的。

实体完整性约束验证；

引用完整性验证；

唯一约束；

非空约束；

缺省值；

检查约束；

实验内容及要求：

一、数据库完整性约束试验。

使用查询分析器，建立数据库SC，数据库参数默认。建立Student、Course、SC数据库表，并完成下面题目：

create table student

(SNO  CHAR (10) PRIMARY KEY check (left(sno, 8) = '20073210'),

SNAME VARCHAR(16) not null,

SEX VARCHAR(2) not null,

DEPT VARCHAR(16) not null,

AGE SMALLINT not null)

create table course

（CNO CHAR(4) PRIMARY KEY,

CNAME VARCHAR(32) unique,

SCORE SMALLINT not null default 4 check (score in (1, 2, 3, 4)))

create table sc

(SNO CHAR(10) foreign key references student(sno),

CNO CHAR(4) foreign key  references course(cno),

GRADE INT check (grade >= 0 and grade <= 100), PRIMARY KEY (sno, cno),)

1、“课程名”属性必须取唯一值。插入重复值，出现什么现象，为什么？

alter table course add cname (unique)

2、“课程名”也不能取空值。插入空值，出现什么现象，为什么？

alter table course alter column cname varchar(32) not null

3、“学分”属性只能取值{1，2，3，4}，如果没有输入学分信息，学分的缺省值为3。插入非{1，2，3，4}的值，出现什么现象，为什么？

alter table course add constraint check (score in (1, 2, 3, 4))

alter table course add default 3 for score

4、“学号”的前两位为“20”，第三位与第四位为数字。插入前两位不是“20”的学号，出现什么现象，为什么？

alter table student add constraint xxdd check ((left(sno,2)='20'))

5、“GRADE”大于等于0并且小于等于100。插入分数不在0~100之间的值，出现什么现象，为什么？

alter table sc add constraint mk check(grade>=0 and grade<=100)

6、SC中，SNO、CNO为外键，请建立外键约束。插入SNO或CNO没有在Student和Course中出现的记录，出现什么现象，为什么？

alter table sc add constraint s foreign key(sno) refenrences student(sno)

alter table sc add constraint a foreign key (cno) refenrences course(cno)

实验三

实验名称：数据库DML语句操作实验

实验学时：2

实验目的：

数据库更新操作是SQL语句的重要组成部分，通过试验，加深学生对数据更新操作基本概念的理解与掌握,从而达到灵活应用的目的。

实验内容及要求：

试验所使用的数据库表结构如下：

Student（学生记录表）

SNO（学号）   CHAR（10） PRIMARY KEY CHECK LEFT(SNO, 7) = '20073210'

SNAME（姓名） VARCHAR（16） NOT NULL

SEX（性别） VARCHAR(2) NOT NULL

DEPT(专业号) VARCHAR(16) NOT NULL

AGE(年龄) SMALLINT NOT NULL

Course（课程表）

CNO(课程号)  CHAR(4) PRIMARY KEY

CNAME(课程名) VARCHAR(32) NOT NULL UNIQUE

SCORE(学分)  SMALLINT  NOT NULL DEFAULT 4

SC（学生选课成绩表）

SNO（学号）   CHAR（7）   PRIMARY KEY   FOREIGN KEY

CNO(课程号)  CHAR(4) PRIMARY KEY  FOREIGN KEY

GRADE（成绩） INT CHECK (Grade >= 0 and Grade <= 100)

二、Insert

1、向Student表插入下面5单条记录

insert into student values ('2007321001', '张三', '男', 'CS', 17)

insert into student values ('2007321002', '李四', '男', 'CS', 18)

insert into student values ('2007321003', '王武', '男', 'CS', 18)

insert into student values ('2007321004', '郑艳', '女', 'CS', 16)

insert into student values ('2007321005', '刘莉', '女', 'CS', 18)

2、向Course插入4门课程信息

insert into course (CNO, cNAme) values ('C001', '数据库原理')

insert into course values ('C002', '自动控制原理', 3)

insert into course values ('C003', '专业英语', 2)

insert into course values ('C004', '数字电路', 3)

3、向SC表插入

insert into sc (sno, cno, grade) values ('2007321001', 'C001', 80)

insert into sc (sno, cno, grade) values ('2007321001', 'C002', 79)

insert into sc (sno, cno, grade) values ('2007321001', 'C003', 69)

insert into sc (sno, cno, grade) values ('2007321001', 'C004', 88)

insert into sc (sno, cno, grade) values ('2007321002', 'C001', 68)

insert into sc (sno, cno, grade) values ('2007321002', 'C002', 73)

insert into sc (sno, cno, grade) values ('2007321002', 'C003', 99)

insert into sc (sno, cno, grade) values ('2007321002', 'C004', 82)

insert into sc (sno, cno, grade) values ('2007321003', 'C001', 70)

insert into sc (sno, cno, grade) values ('2007321003', 'C002', 72)

insert into sc (sno, cno, grade) values ('2007321003', 'C003', 87)

insert into sc (sno, cno, grade) values ('2007321003', 'C004', 45)

insert into sc (sno, cno, grade) values ('2007321004', 'C001', NULL)

insert into sc (sno, cno, grade) values ('2007321004', 'C002', NULL)

insert into sc (sno, cno, grade) values ('2007321004', 'C003', 87)

insert into sc (sno, cno, grade) values ('2007321004', 'C004', 93)

4、将Student表的数据批量插入##Student中

insert into ##student  select * from  student

5、向Student表插入一条记录

学号：2007321006   姓名：李新   性别：男    专业：与2007321003在同一专业   年龄：与2007321002的年龄相同

insert into student(sname,sno,dept,age,sex)  select top 1 '李新','2007321006',

(select dept from student  where sno='2007321003'),(select age from student where sno='2007321002') ,'男'  from student

6、学号为2007321005的学生，选修了所有的课程，每门功课都还没有成绩，请将该同学的选课记录插入到选课表中。

insert into sc(sno,cno ,grade) select top 4 '2007321005',cno,null from sc 

三、Update

1、单条记录更新：将student表中，学号为2007321003的记录年龄更新19，系编码更2007321004新为MA

update student set age=19 where sno='2007321003' 

update student set dept='MA' where sno='2007321004'

2、单条记录更新：将student表中，学号为2007321003的记录年龄更新为学号2007321004的年龄减1，性别更新成与2007321005的性别相同

update student set age=(select age from student where sno ='2007321004')-1 where sno='2007321003'

update student set sex=(select age from student where sno ='2007321005') where sno='2007321003'

3、批量更新：将student表中，所有年龄大于18岁的男同学的专业信息更改成MA，年龄更新成20岁

update student set dept='ma',age=20 where age>=18 and sex='男'

4、查询的更新：将SC表中， Student中计算机系的学生的所有考试成绩更新为60

Update sc set grade = 60 where sno in (select sno from student where dept = ‘CS’)

三、Delete

1、单条记录删除: 删除student中，学号为2007321005的记录（想想应该如何删除）

delete from sc where sno='2007321005'

delete from course where sno='2007321005'

delete from student where sno='2007321005'

2、批量删除: 删除SC中，学号为2007321004的记录

delete from sc where sno='2007321004'

3、带子查询的删除：删除Student表中存在，但是在SC表中不存在选课记录的学生。

delete from student where sno in (select sno from student) and sno not in (select sno from sc)

实验四

实验名称：简单数据查询

实验学时：2

实验目的：

数据查询语句是SQL语句的重要组成部分，合理使用数据查询语句，可以极大的简化应用程序编制、快速的定位分析数据库系统的故障，查询语句是编程人员与数据库管理人员必不可少的工具，通过试验，加深学生对查询语句基本概念的理解与掌握,最终达到灵活应用的目的。

实验内容：

恢复上次课的数据库表结构和数据。

1.选择表中的若干列

2.对表和列使用别名

3.查询计算列

4.选择表中的若干元组

5.对查询结果排序

6.使用集函数

7.对查询结果分组

实验内容及要求：

1.查询全体学生的学号与姓名

select sno ,sname from student

2.查询全体学生的全部信息，并为学生表命名别名

select *from student stu

3.查全体学生的出生年份，并为年份加上标题

select year(getdate())-age nianfen from student

4.查询选修了课程的学生学号，要求消除重复行

select distinct sno from sc

5.查询所有年龄在20岁以下的学生姓名及其年龄

select sname ,age from student where age<20

6.查询年龄在20~23岁的学生信息（要求至少使用两种方式完成查询）

select *from student where age between 20 and 23 select *from student where age > 20 and age< 23

7.使用IN关键字查询信息系（IS）、数学系（MA）和计算机科学系（CS）的学生

select *from student where dept in('MA','IS','CS')

8.查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

select sname ,sex from student where dept not in('MA','IS','CS')

9.查询所有姓刘学生的姓名、学号和性别

select sname,sno,sex from student where sname like'刘%'

10.查询名字中第2个字为"阳"字的学生的姓名和学号

select sname,sno from student where sname like'-阳%'

11.查询DB_Design课程的课程号和学分（先在Course表中插入“DB_Design”课程信息）

select cno,score from course where cname like 'DB\\-Design'escape'\\'

12.查询没有考试成绩的学生学号和课程号

select sno,cno from sc where grade is null

13.查询计算机系年龄在20岁以下的学生姓名

Select sname from student where dept='cs' and age<20

13.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列

select *from student  order by dept ,age desc

14.查询学生总人数

select count(*) from student

15.查询选修了课程的学生人数

Select distinct count(sno)from sc where  cno  in('C001','C002','C003','C004') group by sno

16.计算1号课程的学生平均成绩

select avg(grade) from sc  where cno='C001'

17.查询选修1号课程的学生最高分数

select max(grade) from sc where cno='C001'

18.求各个课程号及相应的选课人数

select cno,count(sno) from sc group by cno

20.查询选修了3门以上课程的学生学号（提示用Having字句）

select sno from sc group by sno having count(*)>3

19.查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。

select sno,cno from sc where sno in (select sno from sc where grade>90 group by sno having count(sno)>3)

实验五

实验名称：连接查询

实验学时：2

实验目的：

数据查询语句是SQL语句的重要组成部分，合理使用数据查询语句，可以极大的简化应用程序编制、快速的定位分析数据库系统的故障，查询语句是编程人员与数据库管理人员必不可少的工具，通过试验，加深学生对查询语句基本概念的理解与掌握,最终达到灵活应用的目的。

实验内容：

恢复上次课的数据库表结构和数据。

1.广义笛卡尔积连接

2.连接查询 

自身连接 

外连接

复合条件连接

多表连接

3.嵌套查询

不相关子查询

相关子查询

4.集合查询

实验要求：

1.查询全体学生与选课表的笛卡尔积

select *from student ,sc

2.查询每个学生及其选修课程的情况

select s.*,sc.* from sc,student s where s.sno=sc.sno

3.查询每个学生及其选修课程的情况（去掉重复属性）

select s.sno,sname,cno,age,sex,dept,grade from sc,student s where s.sno=sc.sno

4.查询某门课程考试成绩相同的学生学号和课程信息

Select x.sno,x.cno,x.grade from sc x,sc y where  x.sno>y.sno and x.grade=y.grade

4.查询每个学生的选修课程包括没有选修课程的学生（外连接）

select s.*,cno,grade from student s left join sc on (s.sno=sc.sno)

5.查询每个学生的选修课程包括没有被学生选修的课程（外连接）

select s.sno,s.sname,grade,course.cno from student s left join sc on (s.SNO=sc.sno) right join course on (sc.CNO=course.cno)

6.查询每个学生的选修课程即包括没有被学生选修的课程又包括没有被学生选修的课程（全连接）

select s.sno,s.sname,grade,course.cno from student s left join sc on (s.SNO=sc.sno) right join course on (sc.CNO=course.cno)

7.查询选修2号课程且成绩在90分以上的所有学生的学号、姓名

select s.sno,cno from sc,student s where sc.grade>90 and cno='c002' and s.sno=sc.sno

8.查询每个学生的学号、姓名、选修的课程名及成绩

select s.sno,cname,grade,sname from student s,sc ,course c where s.sno=sc.sno and c.cno=sc.cno

9.查询与“张三”在一个系学习的学生（IN）

select *from student where dept in (select dept from student where sname='张三')

10.查询选修了课程名为“信息系统”的学生学号和姓名

select sno,sname from student  where sno in( select sno from sc where cno in (select cno from course where cname='信息系统'))

11.查询与“张三”在同一个系学习的学生（EXISTS）

select sno,sname from student where exists (select dept from student where sname='张三')

12.查询选修了课程1或者选修了课程2的学生（要求消除重复组UNION）

select s.sno ,sname from student s,sc where cno='c001'and s.sno=sc.sno union select s.sno ,sname from student s,sc where cno='c002'and s.sno=sc.sno

13.查询选修了课程1或者选修了课程2的学生（要求不消除重复组UNION ALL）

select s.sno ,sname from student s,sc where cno='c001'and s.sno=sc.sno union all select s.sno ,sname from student s,sc where cno='c002'and s.sno=sc.sno

14.查询所有考试成绩均在90分以上的同学的信息

select student.*,cno ,grade from student,sc where student.SNO=sc.sno and student.sno in  (select sc.sno from sc where grade>90 group by sc.sno  having count(sc.sno)=4)

实验六

实验名称：视图的使用

一、创建视图

1、创建信息系学生的视图（注意with check option的用途）

drop view  is_student

create view is_student(sno,sname,age,sex) as

select sno,sname,age,sex from student where dept='cs'

2、创建信息系选修了1号课程的学生的视图

create view is_s1(sno,sname,grade) as

select student.sno,sname,grade from student,sc

where dept='cs' and student.sno=sc.sno and sc.cno='c001'

3、创建信息系选修了3号课程并且该课程的考试成绩大于90分的学生视图

create view is_s2(sno,sname,grade)

as

select student.sno,sname,grade

from student,sc

where dept='cs' and student.sno=sc.sno and sc.cno='c003' and grade>90

    4、创建反映学生出生年份的视图

create view is_s3(sno,sname,sbirth) as

select sno,sname,2004 – age from student

    5、创建视图，该视图包含学号、姓名、所在系、性别、课程名称、考试成绩

 create view is_s4(sno,sname,dept,sex,cno,grade) as

select student.sno,sname,dept,sex,sc.cno,grade from student,sc,course

    6、为所有考试成绩都大于85的学生创建视图，该视图包含学号、姓名、所在系、课程名称、考试成绩

create view is_s5(sno,sname,dept,sex,cno,grade)as

select student.sno,sname,dept,sex,sc.cno,grade

from student,sc,course where sc.cno=course.cno and grade>85

7、为选修了3门以上课程的学生信息创建视图

create view is_s6 as

select sc.sno from student,sc group by sc.sno having count(*)>3

8、依据（１）创建信息系的女生信息视图

create view is_s7 as

select *from is_student where sex like '女'

9、创建信息系学生的视图,信息包括学生姓名、性别、年龄、系别

create view is_s8(sname,sex,age,dept) as

select sname,sex,age,dept from student where dept='cs'

二、视图查询

1、在信息系学生的视图中，查询年龄小于20的学生

select sno,age from is_student where age<20

2、查询选修了1号课程的计算机系的学生

select * from is_student,sc where is_student.sno=sc.sno and sc.cno='c001'

3、创建信息系选修了3号课程并且该课程的考试成绩大于90分的数学系的学生信息

create view is_s9(sno,sname,grade) as

select student.sno,sname,grade from student,sc

where dept='math' and student.sno=sc.sno and sc.cno='c003' and grade>90

    4、查询信息系学生出生年份信息

select sbirth from is_s3

    5、查询信息系的女生的考试情况，包括学号、姓名、课程名称、考试成绩

select distinct is_s7.sno,sname,cno,grade from is_s7,sc`where sex='女'

    6、查询信息系，考试成绩都大于85的学生考试成绩信息

select * from is_s4 where grade>90

三、更新视图

1、将信息系的学生“张三”名字更改为“李四”

update is_student set sname='李四' where sname='张三'

2、向信息系插入一条新的学生记录：学号为2007321015,姓名为赵兴，年龄为23

insert into is_student values('2007321015','赵兴','23','男')

3、删除信息系中，学号为2007321015的学生

Delete from is_student where sno='2007321015'

4、对创建视图中的９创建的视图，进行插入操作，出现什么情况，为什么？

insert into is_s8    values('2007321015','赵兴','23','女')

四、删除视图

对上述创建的视图进行删除（注意，删除视图１时，对视图８进行查询，出现什么情况，为什么？

drop view is_s5

实验八

实验名称：数据库事务及并发控制

1、事务的控制

数据准备：执行如下命令Select * into ##temp from Customers，将Customers的数据倒入到##temp表中。

启动事务，执行删除后，回滚事务

选择##temp的数据，察看记录总数

select *into ##temp from customers

显式启动事务

begin transaction

删除##temp表的数据

drop table ##temp

选择##temp数据，察看记录总数

select *from ##temp

回滚事务

rollback transaction

选择##temp数据，察看记录总数

select *from ##temp

启动事务，执行删除后，提交事务选择##temp数据，察看记录总数

select *into ##temp from customers

显式启动事务

begin transaction

删除##temp表的数据

drop table ##temp

选择##temp的数据，察看记录总数

select *from ##temp

回滚事务

commit transaction

选择##temp数据，察看记录总数

select *from ##temp

2、事务的隔离级别试验

数据准备：执行如下命令Select * into ##temp from Customers，将Customers的数据倒入到##temp表中。

启动两个查询分析器，分别叫（A，B）

在A中，选择##temp表中数据，察看记录总数

select *from ##temp

在A中，显式启动事务

begin transaction

在A中，删除##temp表的数据

drop table ##temp

在B中，将事务隔离级别设为UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

在该查询分析器中选择##temp表数据，察看记录总数

select *from ##temp

回到先前窗口回滚事务

rollback transaction

选择##temp数据，察看记录总数

select *from ##temp

再次切查询分析器窗口选择##temp数据，察看记录总数

select *from ##temp

不可重复读启动两个查询分析器，分别叫（A，B）

在A中，显式启动事务，察看customerid = 'ANTON'的记录（注意地址中的数据）

select *from ##temp where customerid = 'ANTON'

在B中，显式启动事务，察看customerid = 'ANTON'的记录（注意地址中的数据）

select *from ##temp where customerid = 'ANTON'

在B中，将customerid = 'ANTON’地址更新为’AAAAAA’

update ##temp set customerid='AAAAAA' where customerid='ANTON'  

在B中，再次察看customerid = 'ANTON'的记录（注意地址中的数据）

select *from ##temp where customerid = 'ANTON'

在B中，提交事务

commit transaction

在A中，再次察看customerid = 'ANTON'的记录（注意地址中的数据）

select *from ##temp where customerid = 'ANTON'

在A中，提交事务

commit transaction

丢失修改启动两个查询分析器，分别叫（A，B）

在A中，显式启动事务，察看customerid = 'ANTON'的记录（注意地址中的数据）

select *from ##temp where customerid = 'ANTON'

在B中，显式启动事务，察看customerid = 'ANTON'的记录（注意地址中的数据）

select *from ##temp where customerid = 'ANTON'

在B中，将customerid = 'ANTON’地址更新为’AAAAAA’

update ##temp set customerid='AAAAAA' where customerid='ANTON'

在B中，再次察看customerid = 'ANTON'的记录（注意地址中的数据）

select *from ##temp where customerid = 'ANTON'

在B中，提交事务

commit transaction  

在A中，再次察看customerid = 'ANTON'的记录（注意地址中的数据）

select *from ##temp where customerid = 'ANTON'

在A中，将customerid = 'ANTON’地址更新为’BBBBBB’

update ##temp set customerid='BBBBBB' where customerid='ANTON'

在A中，提交事务

commit transaction

3、锁的模拟

启动两个查询分析器，分别叫（A，B）

在A中，显式启动事务，察看customerid = 'ANTON'的记录

select *from ##temp where customerid = 'ANTON'

在B中，显式启动事务，察看customerid = 'ANTON'的记录

select *from ##temp where customerid = 'ANTON'

在B中，将customerid = 'ANTON’地址更新为’AAAAAA’

update ##temp set customerid='AAAAAA' where customerid='ANTON'  

在A中，将customerid = 'ANTON’地址更新为’BBBBBB’

update ##temp set customerid='BBBBBB' where customerid='ANTON'

观察A窗口的结果，为什么？

select *from ##temp  

在B中，提交事务

commit transaction    

观察A窗口的结果，为什么？

select *from ##temp

在A窗口，再次察看customerid = 'ANTON'的记录，结果如何，为什么?

select *from ##temp where customerid = 'ANTON'