数据库上机实验报告

数据库上机实验指导书

数据库上机实验

网络1002 3100610055

一、实验目的

1. 通过上机操作，加深对数据库理论知识的理解。 2. 通过使用具体的微机DBMS（SQL Server），了解一种实际的数据库管理

系统，并掌握其操作技术。

3. 通过实际题目的上机实验，提高动手能力，提高分析问题和解决问题的

能力。

二﹑实验题目

某供应商（S）供应（SPJ）某种零件（P）给某个工程项目（J），其中一个供应商可以供给多个项目零件，而每个项目可以使用多个供应商供应的零件，每种零件可由不同供应商供给，S﹑P﹑J﹑SPJ的数据见附表1。

三、实验报告

实验报告按照各实验的实验报告内容来整理。 实验报告提交地址：ftp://202.195.168.13/ （在“up”文件夹里的“《数据库系统原理A》上机实验”文件夹中找到班级文件夹）

用户名：nianyi 密码：123456

实验报告命名规则：班级+学号+姓名 ，如“软件080101王鹏晓”。

四、考核

上机实验采用书面分析、平时实验和最后抽查考核相结合的方法评定成绩。

1

数据库上机实验指导书

实验一 数据库的定义和建立

一、实验目的

要求学生熟练掌握和使用T-SQL﹑SQL Server对象资源管理器创建数据库﹑基本表和修改表结构，及向数据库输入数据的操作，学会创建和使用表的主码、外码和约束。 二、实验内容

1﹑利用SQL Server对象资源管理器创建数据库和查看数据库属性；2﹑利用T-SQL创建基本表﹑确定表的主码、外码和相应的约束； 3﹑利用SQL Server对象资源管理器创建表之间的关系图； 4﹑利用T-SQL向数据库输入数据。 三、注意事项

1﹑输入数据时要注意数据类型、主码和数据约束的限制。 2﹑注意数据库的主码﹑外码和数据约束的定义。

3、注意参照表和被参照表之间的关系，主码和外码间的关系。 四、实验报告内容

1﹑创建基本表（包括表的主码、外码和相应的约束）的SQL语句；3﹑输入数据的SQL语句。 create table s

(sno char(8) primary key, sname char(20) unique, statuss smallint, city char(20) );

create table p

(pno char(8) primary key, pname char(20), color char(8), weightt smallint );

create table j

(jno char(8) primary key, jname char(20), city char(20) );

2

数据库上机实验指导书

create table spj (sno char(8), pno char(8), jno char(8), qty smallint,

primary key(sno,pno,jno),

foreign key(sno) references s(sno), foreign key(pno) references p(pno), foreign key(jno) references j(jno) ); insert into s

values('s1','精益','20','天津'); insert into s

values('s2','盛锡','10','北京'); insert into s

values('s3','东方红','30','北京'); insert into s

values('s4','丰泰盛','20','天津'); insert into s

values('s5','为民','30','上海'); select* from s;

insert into p

values('p1','螺母','红','12'); insert into p

values('p2','螺栓','绿','17');

3

数据库上机实验指导书

insert into p

values('p3','螺丝刀','蓝','14'); insert into p

values('p4','螺丝刀','红','14'); insert into p

values('p5','凸轮','蓝','40'); insert into p

values('p6','齿轮','红','30'); select* from p;

insert into j

values('j1','三建','北京'); insert into j

values('j2','一汽','长春'); insert into j

values('j3','弹簧厂','天津'); insert into j

values('j4','造船厂','天津'); insert into j

values('j5','机车厂','唐山'); insert into j

values('j6','无线电厂','常州');

4

数据库上机实验指导书

insert into j

values('j7','半导体厂','南京'); select* from j;

insert into spj

values('s1','p1','j1','200'); insert into spj

values('s1','p1','j3','100'); insert into spj

values('s1','p1','j4','700'); insert into spj

values('s1','p2','j2','100'); insert into spj

values('s2','p3','j1','400'); insert into spj

values('s2','p3','j2','200'); insert into spj

values('s2','p3','j4','500'); insert into spj

5

数据库上机实验指导书

values('s2','p3','j5','400'); insert into spj

values('s2','p5','j1','400'); insert into spj

values('s2','p5','j2','100'); insert into spj

values('s3','p1','j1','200'); insert into spj

values('s3','p3','j1','200'); insert into spj

values('s4','p5','j1','100'); insert into spj

values('s4','p6','j3','300'); insert into spj

values('s4','p6','j4','200'); insert into spj

values('s5','p2','j4','100'); insert into spj

values('s5','p3','j1','200'); insert into spj

values('s5','p6','j2','200'); insert into spj

values('s5','p6','j4','500'); select* from spj;

6

数据库上机实验指导书

2﹑表之间的关系图；7

数据库上机实验指导书

8

数据库上机实验指导书

4、总结实验过程中遇到的问题及解决。

1) 第一次上机不知道如何下手。经过请教老师，熟悉了如何使用该软件。

2）Smallint是短整形，写SQL语句时候不了解。后来在书上找到了相关例子。

3）语句执行成功后，不能再次重复执行

4）没有select* from s; 这个语句，无法显示出执行的结果

9

数据库上机实验指导书

实验二 数据查询及更新

一﹑实验目的

要求学生掌握T-SQL语言的使用方法，熟练掌握和使用查询操作和插入、删除、修改等更新操作。 二﹑实验内容

用T-SQL语言完成以下操作：

1. 找出所有供应商的姓名和所在城市； 2. 找出所有零件的名称、颜色、重量；

3. 找出使用供应商S1所供应零件的工程号码；

4. 找出工程项目J2使用的各种零件的名称及其数量； 5. 找出上海厂商供应的所有零件号码； 6. 找出使用上海产的零件的工程名称； 7. 找出没有使用天津产的零件的工程号码； 8. 把全部红色零件的颜色改成蓝色；

9. 由S5供给J4的零件P6改为由S3供应，请作必要的修改；

10. 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录； 11. 请将（S2,J6,P4,200）插入供应情况关系。

12. 查询至少用了供应商S1所供应的全部零件的工程号JNO。（选作） 三、注意事项

1．输入SQL语句时应注意，语句中均使用西文操作符号。 2．数据修改时要注意外码约束。 3．数据删除时要注意外码约束。 四、实验报告内容

1﹑实验内容中各操作的SQL语句； 2﹑实验内容中各操作的执行结果；

1. 找出所有供应商的姓名和所在城市；

SELECT SNAME,CITY FROM S;

10

数据库上机实验指导书

2. 找出所有零件的名称、颜色、重量；

SELECT PNAME,COLOR,WEIGHTT FROM P;

3. 找出使用供应商S1所供应零件的工程号码；

SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1';

11

数据库上机实验指导书

4. 找出工程项目J2使用的各种零件的名称及其数量；SELECT PNAME,QTY FROM SPJ,P

WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2';

5. 找出上海厂商供应的所有零件号码；

SELECT PNO FROM SPJ,S

12

数据库上机实验指导书

WHERE S.SNO=SPJ.SNO AND S.CITY='上海';

6. 找出使用上海产的零件的工程名称；

SELECT JNAME FROM SPJ,S,J

WHERE S.SNO=SPJ.SNO AND S.CITY='上海' AND J.JNO=SPJ.JNO;

13

数据库上机实验指导书

7. 找出没有使用天津产的零件的工程号码；

SELECT DISTINCT JNO FROM SPJ

WHERE JNO NOT IN

(SELECT DISTINCT JNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND S.CITY='天津');

8. 把全部红色零件的颜色改成蓝色；

UPDATE P SET COLOR='蓝' WHERE COLOR='红' SELECT* FROM P;

14

数据库上机实验指导书

9. 由S5供给J4的零件P6改为由S3供应，请作必要的修改；UPDATE SPJ SET SNO='S3'

WHERE SNO='S5' AND JNO='J4' AND PNO='P6' SELECT* FROM SPJ;

15

数据库上机实验指导书

10. 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录；

DELETE FROM S WHERE SNO='S2' SELECT* FROM S DELETE FROM SPJ WHERE SNO='S2' SELECT* FROM SPJ;

16

数据库上机实验指导书

此处是还原s2记录的代码

INSERT INTO S

values('s2','盛锡','10','北京'); SELECT* FROM S; INSERT INTO SPJ

values('s2','p3','j1','400'); insert into spj

values('s2','p3','j2','200'); insert into spj

values('s2','p3','j4','500'); insert into spj

values('s2','p3','j5','400'); insert into spj

values('s2','p5','j1','400'); insert into spj

values('s2','p5','j2','100'); SELECT* FROM SPJ;

17

数据库上机实验指导书

11. 请将（S2,J6,P4,200）插入供应情况关系。

INSERT INTO SPJ

VALUES('S2','P4','J6','200') SELECT* FROM SPJ;

18

数据库上机实验指导书

19

数据库上机实验指导书

3﹑总结实验过程中遇到的问题及解决。

1）因为用了输入法，标点符号导致不能运行，后来发现该问题，重新输入标点，结果就可以执行出来了

2）这个实验中第10题和11题的联系，第10题中删除S2的所有记录，而11题插入一个S2的记录不能执行。后来在S表中插入S2纪录，在spj表也加入S2记录，以达到恢复的目的，在运行11题的语句，就得出结果

20

数据库应用上机实验指导书

实验三 数据库的安全性

一﹑实验目的

要求学生熟练使用和掌握数据库的存取控制操作，包括新建登录，新建数据库用户，给用户授权以及用户权限的撤销。 二﹑实验内容

1．利用SQL Server对象资源管理器新建登录（包含用户名：U1、U2，口令，默认访问的数据库）；

2．利用SQL Server对象资源管理器在数据库中新建数据库用户U1和U2； 3．利用T-SQL给用户U1授权（用户U1具有对SPJ表的所有权力，并具有给用户U2授权的权力），；

4．利用T-SQL验证对用户U1的授权是否生效； 5．撤销用户U1的权限并利用T-SQL验证。

三、注意事项

1. 对用户U1的授权是在用系统管理员身份（Administrator或sa）登录后进行，对用户U2的授权是在用U1用户身份登录后进行。

2. 用户U1的权限可以通过查询、插入、修改、删除以及给用户U2授权等操作来验证。

3. 验证用户U1的权限，必须先用U1登录，再进行相关操作。 4. 用户U1给用户U2授权前后，请查看用户U2的权限变化。 5. 用户U2获得授权后，也必须先用U2登录，再进行相关操作。 四、实验报告内容

1﹑给用户U1授权的SQL语句；

grant all privileges on spj to u1

with grant option;

2、验证用户U1权限的SQL语句及执行结果（查询、插入、修改、删除以及给用户U2授权）； 给U2授权语句

grant all privileges on spj to u2

21

数据库应用上机实验指导书

验证U1查询权限

SELECT JNO FROM SPJ WHERE SNO='S1';

验证U1插入权限 insert into spj

values('s3','p2','j2','200') select* from spj;

22

数据库应用上机实验指导书

验证U1修改权限 UPDATE SPJ SET SNO='S4'

WHERE SNO='S5' AND JNO='J2' AND PNO='P6' SELECT* FROM SPJ;

23

数据库应用上机实验指导书

验证U1删除权限 DELETE FROM SPJ WHERE SNO='S3' SELECT* FROM SPJ;

24

数据库应用上机实验指导书

3、验证用户U2权限的SQL语句及执行结果； 验证U2查询权限

SELECT JNO FROM SPJ WHERE SNO='S5';

25

数据库应用上机实验指导书

验证U2插入权限 insert into spj

values('s3','p2','j2','200') select* from spj;

验证U2修改权限 UPDATE SPJ SET SNO='S3'

WHERE SNO='S5' AND JNO='J1' AND PNO='P3' SELECT* FROM SPJ;

26

数据库应用上机实验指导书

验证U2删除权限 DELETE FROM SPJ WHERE SNO='S2' SELECT* FROM SPJ;

27

数据库应用上机实验指导书

4、撤销用户U1、U2权限的SQL语句； 先在U1界面撤销U1对U2的授权 revoke all privileges

on spj from u2;

再在sa界面撤销sa对U1的授权，cascade是撤销所有U1的联级revoke all privileges on spj

from u1 cascade;

撤销权限后验证

SELECT JNO FROM SPJ WHERE SNO='S1';

28

数据库应用上机实验指导书

上两幅图是撤销U1和U2权限后，验证得到的

5﹑总结实验过程中遇到的问题及解决。 1）

对于新建用户后，验证权限时没有理清得到授权用户登录的顺

序，后来请教同学，理解了授权验证的意思。 2）

revoke all privileges

on spj from u2;

原来是on spj table 就无法执行，去掉table

29

数据库应用上机实验指导书

实验四 数据库的完整性

一﹑实验目的

熟练掌握和使用T-SQL定义和验证数据库完整性的方法。 二﹑实验内容

1. 2. 3. 4. 5. 6. 7. 8. 9.

实体完整性的验证（S表主码SNO）； 实体完整性的验证（P表主码PNO）； 实体完整性的验证（J表主码JNO）；

实体完整性的验证（SPJ表主码（SNO,PNO,JNO））； 参照完整性的验证（SPJ表外码SNO）； 参照完整性的验证（SPJ表外码PNO）； 参照完整性的验证（SPJ表外码JNO）；

用T-SQL实现用户定义的完整性（SPJ表中供应量QTY必须大于0）； 用户定义的完整性的验证。

三﹑注意事项

1. 实体完整性主要通过对表的插入操作，针对主码的唯一性和非空，来验

证完整性的定义是否有效。

2. 参照完整性主要通过对表的插入、删除和修改操作，来验证完整性的定

义是否有效。

3. 用户定义的完整性主要通过对表的插入和修改操作，来验证完整性的定

义是否有效。 四、实验报告内容

1﹑验证S表主码SNO的SQL语句及执行结果（插入操作）；

insert into S(SNO) values('s1') insert into S(SNO) values(NULL)

30

数据库应用上机实验指导书

2、验证P表主码PNO的SQL语句及执行结果（插入操作）；

insert into P(PNO) values('P2') insert into P(PNO) values(NULL)

3、验证J表主码JNO的SQL语句及执行结果（插入操作）；

insert into J(JNO) values('J7') insert into J(JNO) values(NULL)

31

数据库应用上机实验指导书

4﹑验证SPJ表主码（SNO,PNO,JNO）的SQL语句及执行结果（插入操作）；

insert

into SPJ(SNO,PNO,JNO) values('S4','P5','J1') insert

into SPJ(SNO) values(NULL)

5、验证SPJ表外码SNO的SQL语句及执行结果（插入、删除和修改操作）；

insert

into SPJ(SNO,PNO,JNO,QTY) values('S6','P4','J7','200')

32

数据库应用上机实验指导书

UPDATE s SET sno='s8' WHERE sno='s1'

DELETE FROM s

WHERE SNO='s2'

33

数据库应用上机实验指导书

UPDATE spj SET sno='s8' WHERE sno='s2'

6、验证SPJ表外码PNO的SQL语句及执行结果（插入、删除和修改操作）；

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','p8','J1','100')

UPDATE p SET pno='p8' WHERE pno='p2'

34

数据库应用上机实验指导书

DELETE FROM p

WHERE pno='p3'

UPDATE spj SET pno='p9' WHERE pno='p3'

7、验证SPJ表外码JNO的SQL语句及执行结果（插入、删除和修改操作）；

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','p3','J9','100')

35

数据库应用上机实验指导书

UPDATE j SET jno='j8' WHERE jno='j2'

DELETE FROM j

WHERE jno='j3'

36

数据库应用上机实验指导书

UPDATE spj SET jno='j9' WHERE jno='j3'

8、实现用户定义的完整性的SQL语句；

alter table SPJ

add constraint c1 check (QTY>0)

9、验证用户定义的完整性的SQL语句；

insert

into SPJ(SNO,PNO,JNO,QTY) values('S6','P4','J3','0')

10、总结实验过程中遇到的问题及解决。

1）用户定义完整性的语句不熟悉，后来在书上找了相关内容参考 alter table SPJ

add constraint c1 check (QTY>0)

37

数据库应用上机实验指导书

2）这个实验验证的报错与目标一致，验证时按照实体完整性、参照完整性、用户定义完整性的几个小方面分别验证，没遇到其他问题

38

数据库应用上机实验指导书

附表1

供应商S

SNO SNAME STATUS CITY S1 精益 20 天津 S2 盛锡 10 北京 S3 东方红 30 北京 S4 丰泰盛 20 天津 S5 为民 30 上海 零件P PNO PNAME COLOR WEIGHT P1 螺母 红 12 P2 螺栓 绿 17 P3 螺丝刀 蓝 14 P4 螺丝刀 红 14 P5 凸轮 蓝 40 P6 齿轮 红 30

工程项目J

JNO JNAME CITY J1 三建 北京 J2 一汽 长春 J3 弹簧厂 天津 J4 造船厂 天津 J5 机车厂 唐山 J6 无线电厂 常州 J7 半导体厂 南京

供求关系SPJ SNO PNO JNO QTY S1 P1 J1 200 S1 P1 J3 100 S1 P1 J4 700 S1 P2 J2 100 S2 P3 J1 400 S2 P3 J2 200 S2 P3 J4 500 S2 P3 J5 400 S2 P5 J1 400 S2 P5 J2 100 39

数据库应用上机实验指导书

S3 P1 J1 200 S3 P3 J1 200 S4 P5 J1 100 S4 P6 J3 300 S4 P6 J4 200 S5 P2 J4 100 S5 P3 J1 200 S5 P6 J2 200 S5 P6 J4 500 40

本文来源：https://www.bwwdw.com/article/nhdw.html