明金同学

明金同学

关注
发信
关注(28)粉丝(399)

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

一、实验目的

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

二、实验内容

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

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

三、问题和要求

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

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

（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;
【数据库系统概论】实验四 数据编辑及查询-LMLPHP
（3）找出使用供应商S1所供应零件的工程号码；
SELECT JNO FROM SPJ WHERE SNO='S1';
【数据库系统概论】实验四 数据编辑及查询-LMLPHP
（4）找出工程项目J2使用的各种零件的名称及其数量。
SELECT PNAME J2的零件名称,WEIGHT 数量 FROM SPJ,P WHERE SPJ.PNO=P.PNO AND JNO='J2';
【数据库系统概论】实验四 数据编辑及查询-LMLPHP
（5）找出使用上海产的零件的工程名称。
SELECT JNAME FROM SPJ,S,J WHERE SPJ.SNO=S.SNO AND SPJ.JNO = J.JNO AND S.CITY='上海';
【数据库系统概论】实验四 数据编辑及查询-LMLPHP
（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';
【数据库系统概论】实验四 数据编辑及查询-LMLPHP
（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';

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

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

全部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;
10-16 17:00
Powered by LMLPHP ©2024 明金同学 0.005821