数据库实验报告-使用SQL语句操作数据.doc

数据库原理实验报告
实验名称： 实验二 使用SQL语句操作数据 实验时间： 2013。4。16
学生姓名： 班级：软件 学号：

一、 实验目的
1、 熟悉SQL Server 2005的基本环境
2、 使用对象资源管理器管理数据库、基本表、视图、索引等对象
3、 锻炼学生对SQL Server 2005基本操作能力
二、实验内容
1、 使用对象资源管理器创建SPJ数据库
（1）更改主数据库文件的增长模式，改为按5MB自动增长；
（2)更改事务日志文件的增长模式，改为按1MB自动增长。
2、 在数据库中创建S， P, J, SPJ四个关系模式：
S ( SNO, SNAME， STATUS， CITY ）
P（PNO， PNAME， COLOR, WEIGHT）
J（JNO, JNAME, CITY）
SPJ(SNO, PNO, JNO, QTY）

【说明:
供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS)、供应商所在城市(CITY）组成；
零件表P由零件代码（SNO）、零件名（PNAME）、颜色（COLOR）、重量(WEIGHT)组成;
工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY)组成；
供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成。】

3、 建立数据库关系图.将建好的数据库和表转化为SQL语句,以sql脚本方式保存。

4、 使用对象资源管理器向表中插入下列数据。
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
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

5、 使用SQL语言完成如下操作：
1) 求供应工程J1零件的供应商号码SNO.
2) 求供应工程J1零件P1的供应商号码SNO。
3) 求供应工程J1零件为红色的供应商号码SNO。
4) 求没有使用天津供应商生产的红色零件的工程号JNO。
5) 求至少用了供应商S1所供应的全部零件的工程号JNO。
6) 找出所有供应商的姓名和所在城市.
7) 找出所有零件的名称、颜色、重量.
8) 找出使用供应商S1所供应零件的工程号码。
9) 找出工程项目J2使用的各种零件的名称及其数量。
10) 找出上海厂商供应的所有零件号码。
11) 找出使用上海产的零件的工程名程。
12) 找出没有使用天津产的零件的工程号码。
13) 把全部红色零件的颜色改为蓝色。
14) 由S5供给J4的零件P6改为由S3供应，请作必要修改。
15) 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录。
16) 请将（S2， J6，P4,200）插入供应情况关系。

三：试验步骤:
把这些语句输入并执行
1) 求供应工程J1零件的供应商号码SNO。
select Sno 供应商from SPJ where Jno = ’J1'；
2) 求供应工程J1零件P1的供应商号码SNO。
select Sno 供应商from SPJ where Pno = 'P1’；
3) 求供应工程J1零件为红色的供应商号码SNO。
select Sno from SPJ where Jno = ’J1’ and Pno in（select Pno from P where Color = '红’）;
4) 求没有使用天津供应商生产的红色零件的工程号JNO。
select distinct Jno from SPJ where Sno not in（select Sno from S where City='天津’) and Pno not in(select Pno from P where Color=’红' )；
5) 求至少用了供应商S1所供应的全部零件的工程号JNO.

6) 找出所有供应商的姓名和所在城市.
select Sname，City from S ；

7) 找出所有零件的名称、颜色、重量。
select Pname， Color, weight from P;
8) 找出使用供应商S1所供应零件的工程号码。
select Jno from SPJ where Sno=’S1'；

9) 找出工程项目J2使用的各种零件的名称及其数量。
select Pname，Qty from SPJ,P where Jno='J2' and P。Pno=SPJ.Pno；

10) 找出上海厂商供应的所有零件号码.
select Pno from SPJ where Sno in（select Sno from S where City=’上海’）;

11) 找出使用上海产的零件的工程名程.
select Jname from J where Jno in（select Jno from SPJ where Sno in（select Sno from S where City=’上海’)）；

12) 找出没有使用天津产的零件的工程号码.
select Jno from J
except
select Jno from SPJ where Sno in(select Sno from S where City=’天津'）；

13) 把全部红色零件的颜色改为蓝色。
update P set Color='蓝’ where Color=’红’；

14) 由S5供给J4的零件P6改为由S3供应，请作必要修改.
update SPJ set Sno = ’S3’ where Sno = ’S5' and Jno='J4’ and Pno=’P6';

15) 从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录。

alter table SPJ
add constraint Sno
foreign key(Sno)
references S(Sno)
on delete cascade；

delete from SPJ where Sno ='S2'；
16) 请将（S2, J6，P4，200）插入供应情况关系。
insert into SPJ values（'S2’,'P4','J6'，’200');
四：实验结果和分析：
1）找出所有供应商的名字和所在城市

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

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

4） 找出工程项目J2使用的各种零件的名称及其数量

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

6） 找出使用上海产的零件的工程名程。

7） 把全部红色零件的颜色改为蓝色.

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

五:试验心得：
此实验让我们更为熟悉关系模式及关系代数，sql语句练习很多，编写这些查询语句时可能得到的不是想要的实验结果，需要再看要求，修改查询语句.虽然实验时错误很多,但最终都化解了，学到了好多知识。