第二次试验报告

南昌航空大学实验报告

二零一四年4月15 日

课程名称：数据库概论实验名称：SQL语言-查询和更新操作

班级：122032 姓名：刘庆玉同组人：

指导教师评定：签名：

一、实验环境

1．Windows2000或以上版本；

2．SQLServer2000或2005。

二、实验目的

1．了解查询、更新的概念和方法；

2．掌握查询分析器或企业管理器中执行SELECT操作和更新数据操作的方法；

3．掌握SELECT语句在单表查询中的应用；

4．掌握SELECT语句在多表查询中的应用；

5．掌握SELECT语句在复杂查询中的使用方法。

6．掌握INSERT、UPDATE、DELETE语句在更新中的应用。

三、实验要求

完成实验指导书中p71第（4）题。

四、实验步骤及参考源代码

--1．创建供应商表

CREATE TABLE S(

SN CHAR(3),

SNAME VARCHAR(20),

CITY VARCHAR(20),

PRIMARY KEY(SN));

--2．创建零件表

CREATE TABLE P(

PN CHAR(3),

PNAME VARCHAR(20),

COLOR CHAR(4),

WEIGHT INT,

PRIMARY KEY(PN));

--3．创建工程表

CREATE TABLE J(

JN CHAR(3),

JNAME VARCHAR(20),

CITY VARCHAR(20),

PRIMARY KEY(JN));

--4．创建供应关系表

create table SPJ

(SN char(3),

PN char(3),

JN char(3),

QTY int,

primary key(SN,PN,JN),

foreign key(SN)references S, foreign key(PN)references P, foreign key(JN)references J); --5．插入数据

insert into S

values('S1','SN1 ','上海'); insert into S

values('S2','SN2 ','北京'); insert into S

values('S3','SN3 ','南京'); insert into S

values('S4','SN4 ','西安'); insert into S

values('S5','SN5 ','上海');

insert into P

values('P1','PN1 ','红',12); insert into P

values('P2','PN2 ','绿',18); insert into P

values('P3','PN3 ','蓝',20); insert into P

values('P4','PN4 ','红',13);

insert into P

values('P5','PN5 ','白',11); insert into P

values('P6','PN6 ','蓝',18);

insert into J

values('J1','JN1 ','上海'); insert into J

values('J2','JN2 ','广州'); insert into J

values('J3','JN3 ','武汉'); insert into J

values('J4','JN4 ','北京'); insert into J

values('J5','JN5 ','南京'); insert into J

values('J6','JN6 ','上海'); insert into J

values('J7','JN7 ','上海');

insert into SPJ

values('S1','P1','J1',200); insert into SPJ

values('S1','P1','J4',700); insert into SPJ

values('S2','P3','J1',800); insert into SPJ

values('S2','P3','J2',200); insert into SPJ

values('S2','P3','J3',30); insert into SPJ

values('S2','P3','J4',400); insert into SPJ

values('S2','P3','J5',500); insert into SPJ

values('S2','P3','J6',200); insert into SPJ

values('S2','P3','J7',300);

insert into SPJ

values('S2','P5','J2',200); insert into SPJ

values('S3','P3','J1',100); insert into SPJ

values('S3','P4','J2',200); insert into SPJ

values('S4','P6','J3',300); insert into SPJ

values('S4','P6','J7',500); insert into SPJ

values('S5','P2','J2',500); insert into SPJ

values('S5','P2','J4',250); insert into SPJ

values('S5','P5','J5',300); insert into SPJ

values('S5','P5','J7',100); insert into SPJ

values('S5','P6','J2',200); insert into SPJ

values('S5','P1','J4',300); insert into SPJ

values('S5','P3','J4',100); insert into SPJ

values('S5','P4','J4',200);

五、实验结果

1.取出所有工程的全部细节;

SELECT*

FROM J;

2.取出所在城市为上海的所有工程的全部信息。

SELECT*

FROM J

WHERE CITY='上海';

3.取出重量最轻的零件代号；

SELECT PN

FROM P

WHERE WEIGHT=

(

SELECT MIN(WEIGHT)

FROM P

);

4.取出为工程J1提供零件的供应商代号；

SELECT SN

FROM SPJ

WHERE JN='J1';

5.取出为工程J1 提供零件P1的供应商代号；

SELECT SN

FROM SPJ

WHERE JN='J1'AND PN='P1';

6.取出由供应商S1提供零件的工程名；

SELECT JNAME

FROM J,SPJ

WHERE J.JN= SPJ.JN AND SN='S1';

7.取出供应商S1提供的零件的颜色；

SELECT COLOR

FROM p,SPJ

WHERE p.PN= SPJ.pN AND SN='S1';

8.取出为工程J1 和J2 提供零件的供应商代号；

SELECT SN

FROM SPJ

WHERE JN='J1'AND SN IN

(

SELECT SN

FROM SPJ

WHERE JN='J2'

);

9.取出为工程J1提供红色零件的供应商代号；

SELECT SN

FROM SPJ,P

WHERE P.PN=SPJ.PN AND COLOR='红';

10.取出为所在城市为上海的工程提供零件的供应商代号；

SELECT SN

FROM SPJ,J

WHERE SPJ.JN=J.JN AND CITY='上海';

11.取出为所在城市为上海或北京的工程提供红色零件的供应商代号；

SELECT SN

FROM SPJ,J,P

WHERE SPJ.JN=J.JN AND P.PN=SPJ.PN AND(CITY='上海'OR CITY='北京')AND COLOR='红';

12.取出供应商与工程所在城市相同的供应商提供的零件代号。

SELECT DISTINCT PN

FROM SPJ,J,S

WHERE SPJ.JN=J.JN AND SPJ.SN=S.SN AND J.CITY=S.CITY;

13.取出上海的供应商提供给上海的任一工程的零件的代号；

SELECT DISTINCT PN

FROM SPJ,J,S

WHERE SPJ.JN=J.JN AND SPJ.SN=S.SN AND J.CITY=S.CITY AND S.CITY='上海';

14.取出至少由一个和工程不在同一城市的供应商提供零件的供应商提供零件的工程代号；

SELECT JN

FROM SPJ

WHERE NOT EXISTS

(

SELECT PN

FROM SPJ,J,S

WHERE SPJ.JN=J.JN AND SPJ.SN=S.SN AND J.CITY=S.CITY );

15.取出上海供应商不提供任何零件的工程代号。

SELECT DISTINCT JN

FROM SPJ

WHERE JN NOT IN

(

SELECT JN

FROM SPJ,S

WHERE SPJ.SN=S.SN AND CITY='上海'

);

16.取出这样一些供应商代号，他们能够提供至少一种提供红色零件的供应商所提供的零件；

SELECT DISTINCT SN

FROM SPJ

WHERE PN IN

(

SELECT PN

FROM P

WHERE COLOR='红'

);

17.取出由供应商S1提供零件的工程代号；

SELECT JN

FROM J

WHERE NOT EXISTS

(

SELECT*

FROM SPJ X

WHERE X.SN='S1'AND NOT EXISTS

(

SELECT*

FROM SPJ Y

WHERE Y.PN=X.PN AND Y.JN=J.JN

)

);

18.取出所有这样的二元组，使得第1个城市的供应商为第2个城市的工程提供零件；

SELECT DISTINCT S.CITY,J.CITY

FROM SPJ,S,J

WHERE SPJ.JN=J.JN AND SPJ.SN=S.SN;

19.取出所有这样的三元组，使得第1个城市的供应商为第2个城市的工程提供指定的零件；

SELECT DISTINCT S.CITY,PN,J.CITY

FROM SPJ,S,J

WHERE SPJ.JN=J.JN AND SPJ.SN=S.SN;

20.重复（19）题，但不检索两个CITY值相同的二元组；

SELECT DISTINCT S.CITY,PN,J.CITY

FROM SPJ,S,J

WHERE SPJ.JN=J.JN AND SPJ.SN=S.SN AND S.CITY NOT LIKE J.CITY;

21.求没有使用天津单位生产的红色零件的工程号；

SELECT JN

FROM SPJ,P,S

WHERE SPJ.PN=P.PN AND SPJ.SN=S.SN AND S.CITY NOT LIKE'天津'AND COLOR NOT LIKE'红';

22.求至少用了单位S1所供应的全部零件的工程号；

SELECT JN

FROM J

WHERE NOT EXISTS

{

SELECT *

FROM SPJ

WHERE SPJ.SN=’S1’AND SPJ.PN NOT IN

(

SELECT *

FROM SPJ Y

WHERE Y.JN=J.JN

)

};

23.完成如下更新操作：把全部红色零件的颜色改成蓝色，由S6供给J4的零件P6改为由S8供应，请作必要的修改，从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录，删除工程J8订购的S4的零件，请将（S9,J8,P4,200）插入供应零件关系。

UPDATE P

SET COLOR=’蓝’

WHERE COLOR=’红’;

DELETE

FROM S

WHERE SN=’S2’;

UPDATE SPJ

SET SN=’S8’

WHERE SN=’S6’AND JN=’J4’AND PN=’P6’;

DELETE

FROM SPJ

WHERE SN=’S2’;

DELETE

FROM SPJ

WHERE JN=’J8’AND SN=’S4’;

INSERT

INTO SPJ

VALUES(‘S9’,’J8’,’P4’,200);

六、实验体会

通过本次试验，我进一步了解了SELECT语句在单表查询中的应用；SELECT语句在多表查询中的应用；SELECT语句在复杂查询中的使用方法，以及INSERT、UPDATE、DELETE语句在更新中的应用。