【資料庫系統概論】實驗四資料編輯及查詢

一、實驗目的

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．在使用查詢分析器進行查詢練習中，遇到什麼問題？你是如何解決的？

-- 判斷表是否存在
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;

【資料庫系統概論】實驗四資料編輯及查詢

一、實驗目的

二、實驗内容

三、問題和要求

1．寫出你操作并正确執行的代碼及結果。

（1）按照P71表内容，用insert語句插入資料記錄；

（2）建立所有供應商供應零件名稱、項目名稱及數量的視圖；

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

（4）找出工程項目J2使用的各種零件的名稱及其數量。

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

（6）由S5供給J4的零件P6改為由S3供應。

（7）從供應商關系中删除S2的記錄，并從供應關系中删除相應的記錄。

2．在使用查詢分析器進行查詢練習中，遇到什麼問題？你是如何解決的？

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

詳解STM32單片機的堆棧

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

【資料庫系統概論】實驗四 資料編輯及查詢

一、實驗目的

二、實驗内容

三、問題和要求

1．寫出你操作并正确執行的代碼及結果。

（1）按照P71表内容，用insert語句插入資料記錄；

（2）建立所有供應商供應零件名稱、項目名稱及數量的視圖；

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

（4）找出工程項目J2使用的各種零件的名稱及其數量。

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

（6）由S5供給J4的零件P6改為由S3供應。

（7）從供應商關系中删除S2的記錄，并從供應關系中删除相應的記錄。

2．在使用查詢分析器進行查詢練習中，遇到什麼問題？你是如何解決的？

繼續閱讀

【資料庫系統概論】實驗四資料編輯及查詢