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

一、实验目的

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

二、实验内容

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

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

三、问题和要求

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

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

-- 判断表是否存在DROPTABLEIFEXISTS SPJ;DROPTABLEIFEXISTS S;DROPTABLEIFEXISTS P;DROPTABLEIFEXISTS J;-- 创建供应商表CREATETABLE S(
SNO CHAR(3)PRIMARYKEY,
SNAME CHAR(10),STATUSCHAR(2),
CITY CHAR(10));-- 创建零件表CREATETABLE P(
PNO CHAR(3),
PNAME CHAR(10),
COLOR CHAR(4),
WEIGHT INT,PRIMARYKEY(PNO));-- 创建项目表CREATETABLE J(
JNO CHAR(3),
JNAME CHAR(10),
CITY CHAR(10),PRIMARYKEY(JNO));-- 创建供应关系表CREATETABLE SPJ(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY INT,PRIMARYKEY(SNO,PNO,JNO),FOREIGNKEY(SNO)REFERENCES S(SNO),FOREIGNKEY(PNO)REFERENCES P(PNO),FOREIGNKEY(JNO)REFERENCES J(JNO));-- 插入供应商数据INSERTINTO S(SNO,SNAME,STATUS,CITY)VALUES('S1','精益','20','天津');INSERTINTO S VALUES('S2','盛锡','10','北京');INSERTINTO S VALUES('S3','东方红','30','北京');INSERTINTO S VALUES('S4','丰泰盛','20','天津');INSERTINTO S VALUES('S5','为民','30','上海');-- 插入零件表数据INSERTINTO P VALUES('P1','螺母','红',12);INSERTINTO P VALUES('P2','螺栓','绿',17);INSERTINTO P VALUES('P3','螺丝刀','蓝',14);INSERTINTO P VALUES('P4','螺丝刀','红',14);INSERTINTO P VALUES('P5','凸轮','蓝',40);INSERTINTO P VALUES('P6','齿轮','红',30);-- 插入项目数据INSERTINTO J VALUES('J1','三建','北京');INSERTINTO J VALUES('J2','一汽','长春');INSERTINTO J VALUES('J3','弹簧厂','天津');INSERTINTO J VALUES('J4','造船厂','天津');INSERTINTO J VALUES('J5','机车厂','唐山');INSERTINTO J VALUES('J6','无限电厂','常州');INSERTINTO J VALUES('J7','半导体厂','南京');-- 插入供应关系数据INSERTINTO SPJ VALUES('S1','P1','J1',200);INSERTINTO SPJ VALUES('S1','P1','J3',100);INSERTINTO SPJ VALUES('S1','P1','J4',700);INSERTINTO SPJ VALUES('S1','P2','J2',100);INSERTINTO SPJ VALUES('S2','P3','J1',400);INSERTINTO SPJ VALUES('S2','P3','J2',200);INSERTINTO SPJ VALUES('S2','P3','J4',500);INSERTINTO SPJ VALUES('S2','P3','J5',400);INSERTINTO SPJ VALUES('S2','P5','J1',400);INSERTINTO SPJ VALUES('S2','P5','J2',100);INSERTINTO SPJ VALUES('S3','P1','J1',200);INSERTINTO SPJ VALUES('S3','P3','J1',200);INSERTINTO SPJ VALUES('S4','P5','J1',100);INSERTINTO SPJ VALUES('S4','P6','J3',300);INSERTINTO SPJ VALUES('S4','P6','J4',200);INSERTINTO SPJ VALUES('S5','P2','J4',100);INSERTINTO SPJ VALUES('S5','P3','J1',200);INSERTINTO SPJ VALUES('S5','P6','J2',200);INSERTINTO SPJ VALUES('S5','P6','J4',500);-- 查询SELECT*FROM  J;SELECT*FROM  P;SELECT*FROM  S;SELECT*FROM  SPJ;

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

CREATEVIEW VIEW_SPJ(SNAME,PNAME,JNAME,QTY)ASSELECT 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：设置DELETEFROM SPJ WHERE SNO='S2';DELETEFROM S WHERE SNO='S2';-- 方法2：设置级联删除ALTERTABLE SPJ ADDCONSTRAINT FK_CASCCADE FOREIGNKEY(SNO)REFERENCES S(SNO)ONDELETECASCADE;DELETEFROM S WHERE SNO='S2';

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

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

全部SQL语句：

-- 判断表是否存在DROPTABLEIFEXISTS SPJ;DROPTABLEIFEXISTS S;DROPTABLEIFEXISTS P;DROPTABLEIFEXISTS J;-- 创建供应商表CREATETABLE S(
  SNO CHAR(3)PRIMARYKEY,
  SNAME CHAR(10),STATUSCHAR(2),
  CITY CHAR(10));-- 创建零件表CREATETABLE P(
  PNO CHAR(3),
  PNAME CHAR(10),
  COLOR CHAR(4),
  WEIGHT INT,PRIMARYKEY(PNO));-- 创建项目表CREATETABLE J(
  JNO CHAR(3),
  JNAME CHAR(10),
  CITY CHAR(10),PRIMARYKEY(JNO));-- 创建供应关系表CREATETABLE SPJ(
  SNO CHAR(3),
  PNO CHAR(3),
  JNO CHAR(3),
  QTY INT,PRIMARYKEY(SNO,PNO,JNO),FOREIGNKEY(SNO)REFERENCES S(SNO),FOREIGNKEY(PNO)REFERENCES P(PNO),FOREIGNKEY(JNO)REFERENCES J(JNO));-- 插入供应商数据INSERTINTO S(SNO,SNAME,STATUS,CITY)VALUES('S1','精益','20','天津');INSERTINTO S VALUES('S2','盛锡','10','北京');INSERTINTO S VALUES('S3','东方红','30','北京');INSERTINTO S VALUES('S4','丰泰盛','20','天津');INSERTINTO S VALUES('S5','为民','30','上海');-- 插入零件表数据INSERTINTO P VALUES('P1','螺母','红',12);INSERTINTO P VALUES('P2','螺栓','绿',17);INSERTINTO P VALUES('P3','螺丝刀','蓝',14);INSERTINTO P VALUES('P4','螺丝刀','红',14);INSERTINTO P VALUES('P5','凸轮','蓝',40);INSERTINTO P VALUES('P6','齿轮','红',30);-- 插入项目数据INSERTINTO J VALUES('J1','三建','北京');INSERTINTO J VALUES('J2','一汽','长春');INSERTINTO J VALUES('J3','弹簧厂','天津');INSERTINTO J VALUES('J4','造船厂','天津');INSERTINTO J VALUES('J5','机车厂','唐山');INSERTINTO J VALUES('J6','无限电厂','常州');INSERTINTO J VALUES('J7','半导体厂','南京');-- 插入供应关系数据INSERTINTO SPJ VALUES('S1','P1','J1',200);INSERTINTO SPJ VALUES('S1','P1','J3',100);INSERTINTO SPJ VALUES('S1','P1','J4',700);INSERTINTO SPJ VALUES('S1','P2','J2',100);INSERTINTO SPJ VALUES('S2','P3','J1',400);INSERTINTO SPJ VALUES('S2','P3','J2',200);INSERTINTO SPJ VALUES('S2','P3','J4',500);INSERTINTO SPJ VALUES('S2','P3','J5',400);INSERTINTO SPJ VALUES('S2','P5','J1',400);INSERTINTO SPJ VALUES('S2','P5','J2',100);INSERTINTO SPJ VALUES('S3','P1','J1',200);INSERTINTO SPJ VALUES('S3','P3','J1',200);INSERTINTO SPJ VALUES('S4','P5','J1',100);INSERTINTO SPJ VALUES('S4','P6','J3',300);INSERTINTO SPJ VALUES('S4','P6','J4',200);INSERTINTO SPJ VALUES('S5','P2','J4',100);INSERTINTO SPJ VALUES('S5','P3','J1',200);INSERTINTO SPJ VALUES('S5','P6','J2',200);INSERTINTO SPJ VALUES('S5','P6','J4',500);-- 查询SELECT*FROM  J;SELECT*FROM  P;SELECT*FROM  S;SELECT*FROM  SPJ;-- 1.按照P71表内容，用insert语句插入数据记录；-- 2.建立所有供应商供应零件名称、项目名称及数量的视图；CREATEVIEW VIEW_SPJ(SNAME,PNAME,JNAME,QTY)ASSELECT 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的记录，并从供应关系中删除相应的记录。DELETEFROM SPJ WHERE SNO='S2';DELETEFROM S WHERE SNO='S2';-- 设置级联删除ALTERTABLE SPJ ADDCONSTRAINT FK_CASCCADE FOREIGNKEY(SNO)REFERENCES S(SNO)ONDELETECASCADE;DELETEFROM S WHERE SNO='S2';SELECT*FROM SPJ;SELECT*FROM S;