数据库查询和更新操作实验报告

二00 年 月 日

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

班级： 102022 姓名： 涂海 同组人： 指导教师评定： 签名：

一、 实验环境

1． Windows2000或以上版本； 2． SQLServer2000或2005。

二、 实验目的

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

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

3． 掌握SELECT语句在单表查询中的应用； 4． 掌握SELECT语句在多表查询中的应用； 5． 掌握SELECT语句在复杂查询中的使用方法。

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

三、 实验要求

供应商表S(SN,SNAME,CITY)，其中SN为供应商代号，SNAME为供应商名字，CITY为供应商所在城市，主关键字为SN；

零件表P（PN,PNAME,COLOR,WEIGHT）其中PN为零件代号，PNAME为零件名字，COLOR为零件颜色，WEIGHT为零件重量，主关键字为PN；

工程表J（JN,JNAME,CITY），其中JN为工程代号，JNAME为工程名字，CITY为工程所在城市，主关键字为JN；

供应关系表SPJ（SN,PN,JN,QTY），其中SN、PN、JN含义同上，QTY表示提供的零件数量，主关键字为（SN，PN,JN），外关键字为SN、PN、JN

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

1 先将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

where CITY='上海' 2

SELECT PN FROM P

WHERE WEIGHT= ( select MIN(WEIGHT) FROM P ) 3

SELECT SN FROM SPJ

WHERE JN='J1' 4

SELECT SN FROM SPJ

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

SELECT JNAME FROM J SPJ

WHERE JN IN( SELECT JN FROM SPJ WHERE SN='S1' ) 6

SELECT COLOR FROM P SPJ

WHERE PN IN( SELECT PN FROM SPJ WHERE SN='S1' ) 7

SELECT S2.SN FROM SPJ S1,SPJ S2

WHERE S1.JN='J1'AND S2.JN IN( SELECT JN FROM SPJ S2 WHERE S2.JN='J2' )

GROUP BY S2.SN 8

SELECT SN FROM SPJ P

WHERE JN='J1'AND PN IN( SELECT PN FROM P WHERE COLOR='红' )

GROUP BY SN 9

SELECT SN FROM SPJ J

WHERE JN IN( SELECT JN FROM J WHERE CITY='上海' )

GROUP BY SN 10

SELECT SN FROM SPJ J

WHERE JN IN( SELECT JN FROM J WHERE CITY='上海'OR CITY='北京' )

GROUP BY SN 11

SELECT SN FROM S J

WHERE CITY IN( SELECT CITY FROM J WHERE CITY='上海' ) GROUP BY SN 12

SELECT PN FROM SPJ S WHERE SN IN( SELECT SN FROM S J WHERE CITY IN( SELECT CITY FROM J WHERE CITY='上海' ) )

GROUP BY PN 13

SELECT SPJ.PN FROM SPJ,S,J

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

SELECT SPJ.JN FROM S,SPJ,J

WHERE S.SN=SPJ.SN AND SPJ.JN=J.JN AND S.CITY!=J.CITY EXCEPT

SELECT SPJJN FROM S,SPJ,J

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

SELECT JN FROM SPJ

WHERE SN NOT IN( SELECT SN FROM S

WHERE CITY='上海') EXCEPT SELECT JN FROM SPJ

WHERE SN NOT IN( SELECT SN FROM S WHERE CITY='上海'); 21

SELECT JN FROM J

WHERE JN NOT IN( SELECT J.JN FROM S,P,SPJ WHERE S.SN=SPJ.SN AND SPJ.PN=P.PN

AND S.CITY='天津' and p.color='红'

); 23

UPDATE P

SET COLOR='蓝' WHERE COLOR='红'

UPDATE SPJ SET SN='s8'

WHERE SN='s6' AND JN='j4'

DELETE FROM SPJ

WHERE SN='s2' DELETE FROM S

WHERE SN='s2'

INSERT INTO J

VALUES('j8','JN8','江西');

INSERT INTO S

VALUES('s9','SN9','江西'); INSERT Into SPJ

VALUES('s9','p4','j8',200);

五、 实验结果

创建的数据库为

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

15.

21.

23.

六、 实验体会

在本次实验中，我已经了解查询、更新的概念和方法，查询的的关键字为select，更新的为update。并且掌握查询分析器或企业管理器中执行SELECT操作和更新数据操作的方法；掌握SELECT语句在单表查询中的应用；掌握SELECT语句在多表查询中的应用；掌握SELECT语句在复杂查询中的使用方法。掌握INSERT、UPDATE、DELETE语句在更新中的应用。