当前位置：首页 > news >正文

【数据库系统概论】实验四数据编辑及查询

news 来源：原创 2024/5/2 10:14:36

一、实验目的

1．掌握SQL Server数据库系统基本操作
2．掌握SQL Server数据库系统数据查询与更新使用方法

二、实验内容

建立教材P71页的数据库表，用代码完成以下操作：

按照P71表内容，用insert语句插入数据记录；
建立所有供应商供应零件名称、项目名称及数量的视图；
找出使用供应商S1所供应零件的工程号码；
找出工程项目J2使用的各种零件的名称及其数量。
找出使用上海产的零件的工程名称。
由S5供给J4的零件P6改为由S3供应。
从供应商关系中删除S2的记录，并从供应关系中删除相应的记录。

三、问题和要求

1．写出你操作并正确执行的代码及结果。

（1）按照P71表内容，用insert语句插入数据记录；

-- 判断表是否存在
DROP TABLE IF EXISTS SPJ;
DROP TABLE IF EXISTS S;
DROP TABLE IF EXISTS P;
DROP TABLE IF EXISTS J;

-- 创建供应商表
CREATE TABLE S(
SNO CHAR(3) PRIMARY KEY,
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10)
);
-- 创建零件表
CREATE TABLE P(
PNO CHAR(3),
PNAME CHAR(10),
COLOR CHAR(4),
WEIGHT INT,
PRIMARY KEY(PNO)
);
-- 创建项目表
CREATE TABLE J(
JNO CHAR(3),
JNAME CHAR(10),
CITY CHAR(10),
PRIMARY KEY(JNO)
);
-- 创建供应关系表
CREATE TABLE SPJ(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY INT,
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(SNO,SNAME,STATUS,CITY) 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','上海');
-- 插入零件表数据
INSERT INTO P VALUES('P1','螺母','红',12);
INSERT INTO P VALUES('P2','螺栓','绿',17);
INSERT INTO P VALUES('P3','螺丝刀','蓝',14);
INSERT INTO P VALUES('P4','螺丝刀','红',14);
INSERT INTO P VALUES('P5','凸轮','蓝',40);
INSERT INTO P VALUES('P6','齿轮','红',30);
-- 插入项目数据
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','无限电厂','常州');
INSERT INTO J VALUES('J7','半导体厂','南京');
-- 插入供应关系数据
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 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  J;
SELECT * FROM  P;
SELECT * FROM  S;
SELECT * FROM  SPJ;

（2）建立所有供应商供应零件名称、项目名称及数量的视图；

CREATE VIEW VIEW_SPJ(SNAME,PNAME,JNAME,QTY) AS SELECT SNAME,PNAME,JNAME,QTY FROM SPJ,S,P,J WHERE SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND SPJ.PNO=P.PNO;
SELECT * FROM  VIEW_SPJ;

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

SELECT JNO FROM SPJ WHERE SNO='S1';

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

SELECT PNAME J2的零件名称,WEIGHT 数量 FROM SPJ,P WHERE SPJ.PNO=P.PNO AND JNO='J2';

（5）找出使用上海产的零件的工程名称。

SELECT JNAME FROM SPJ,S,J WHERE SPJ.SNO=S.SNO AND SPJ.JNO = J.JNO AND S.CITY='上海';

（6）由S5供给J4的零件P6改为由S3供应。

SELECT * FROM SPJ WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
UPDATE SPJ SET SN0='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6';

（7）从供应商关系中删除S2的记录，并从供应关系中删除相应的记录。

-- 方法1：设置
DELETE FROM SPJ WHERE SNO='S2';
DELETE FROM S WHERE SNO='S2';

-- 方法2：设置级联删除
ALTER TABLE SPJ ADD CONSTRAINT FK_CASCCADE FOREIGN KEY(SNO) REFERENCES S(SNO) ON DELETE CASCADE;
DELETE FROM S WHERE SNO='S2';

2．在使用查询分析器进行查询练习中，遇到什么问题？你是如何解决的？

**答：**对多个数据删除的时候设置级联删除无法从DELETE 中使用CASCADE直接对级联数据进行删除。
**解决方法：**先使用数据库修改语句ALTER TABLE添加联级删除，然后再删除数据。

全部SQL语句：

-- 判断表是否存在
DROP TABLE IF EXISTS SPJ;
DROP TABLE IF EXISTS S;
DROP TABLE IF EXISTS P;
DROP TABLE IF EXISTS J;

-- 创建供应商表
CREATE TABLE S(
  SNO CHAR(3) PRIMARY KEY,
  SNAME CHAR(10),
  STATUS CHAR(2),
  CITY CHAR(10)
);
-- 创建零件表
CREATE TABLE P(
  PNO CHAR(3),
  PNAME CHAR(10),
  COLOR CHAR(4),
  WEIGHT INT,
  PRIMARY KEY(PNO)
);
-- 创建项目表
CREATE TABLE J(
  JNO CHAR(3),
  JNAME CHAR(10),
  CITY CHAR(10),
  PRIMARY KEY(JNO)
);
-- 创建供应关系表
CREATE TABLE SPJ(
  SNO CHAR(3),
  PNO CHAR(3),
  JNO CHAR(3),
  QTY INT,
  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(SNO,SNAME,STATUS,CITY) 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','上海');
-- 插入零件表数据
INSERT INTO P VALUES('P1','螺母','红',12);
INSERT INTO P VALUES('P2','螺栓','绿',17);
INSERT INTO P VALUES('P3','螺丝刀','蓝',14);
INSERT INTO P VALUES('P4','螺丝刀','红',14);
INSERT INTO P VALUES('P5','凸轮','蓝',40);
INSERT INTO P VALUES('P6','齿轮','红',30);
-- 插入项目数据
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','无限电厂','常州');
INSERT INTO J VALUES('J7','半导体厂','南京');
-- 插入供应关系数据
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 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  J;
SELECT * FROM  P;
SELECT * FROM  S;
SELECT * FROM  SPJ;

-- 1.按照P71表内容，用insert语句插入数据记录；

-- 2.建立所有供应商供应零件名称、项目名称及数量的视图；
CREATE VIEW VIEW_SPJ(SNAME,PNAME,JNAME,QTY) AS SELECT SNAME,PNAME,JNAME,QTY FROM SPJ,S,P,J WHERE SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND SPJ.PNO=P.PNO;
SELECT * FROM VIEW_SPJ;
-- 3.找出使用供应商S1所供应零件的工程号码；
SELECT JNO FROM SPJ WHERE SNO='S1';
-- 4.找出工程项目J2使用的各种零件的名称及其数量。
SELECT PNAME J2的零件名称,WEIGHT 数量 FROM SPJ,P WHERE SPJ.PNO=P.PNO AND JNO='J2';
-- 5.找出使用上海产的零件的工程名称。
SELECT JNAME FROM SPJ,S,J WHERE SPJ.SNO=S.SNO AND SPJ.JNO = J.JNO AND S.CITY='上海';
-- 6.由S5供给J4的零件P6改为由S3供应。
SELECT * FROM SPJ WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
UPDATE SPJ SET SN0='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
SELECT * FROM SPJ WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
-- 7.从供应商关系中删除S2的记录，并从供应关系中删除相应的记录。
DELETE FROM SPJ WHERE SNO='S2';
DELETE FROM S WHERE SNO='S2';

-- 设置级联删除
ALTER TABLE SPJ ADD CONSTRAINT FK_CASCCADE FOREIGN KEY(SNO) REFERENCES S(SNO) ON DELETE CASCADE;
DELETE FROM S WHERE SNO='S2';

SELECT * FROM SPJ;
SELECT * FROM S;