实验报告—基本表的创建、查询及更新任务 实验日期和时间： 实验室： 班级： 学号： 姓名： 实验环境： 硬件： 软件： 实验目的：

本次实验的主要目的是?? 实验主要任务：(不够时另附页) 一.建立各个表的SQL语句。

二.单表查询操作及（任选三题结果截图附上）。

三.多表查询操作其结果截图（任选三题结果截图附上））。 四.数据更新及其结果截图（最后附上四个基本表的数据截图）。

一．利用查询分析器在数据库ShiYan中建立以下四个数据表（S表，P表，J表，SPJ表），并依次向各数据表中输入相应的数据记录。

(1) 供应商表S（NO，SNAME，STATUS，CITY）

SNO：表示供应商编号，定义其为长度为4字节的字符串类型。

SNAME：表示供应商的名称，定义其为最大长度为20字节的变长字符串类型。 STATUS：表示供应商的基本状况，定义其为短整数类型。

CITY：表示供应商所在的城市，定义其为最大长度为30字节的变长字符串类型。 要求：SNO字段为表的主键，其属性值必须不重复且不允许取空值，SNAME属性值不允许取空值。 SNO S1 S2 S3 S4 S5 PNO P1 P2 P3 P4 P5 SNAME 精 益 盛 锡 东方红 丰泰盛 为 民 PNAME 螺 母 螺 栓 螺丝刀 螺丝刀 凸 轮 STATUS 20 10 30 20 30 COLOR 红 绿 蓝 红 蓝 CITY 天津 北京 北京 天津 上海 WEIGHT 12 17 14 14 40 (2) 零件表P（PNO，PNAME，COLOR，WEIGHT）

PNO：表示零件的编号，定义其为长度为4字节的字符串类型。

PNAME：表示零件的名称，定义其为最大长度为20字节的变长字符串类型。 COLOR：表示零件的颜色，定义其为长度为6字节的字符串类型。 WEIGHT：表示零件的重量，定义其为实数类型且缺省值为0。

要求：PNO字段为表的主键，PNAME属性值不允许取空值。

(3) 工程项目表J（JNO，JNAME，CITY）

JNO：表示工程项目的编号，定义其为长度为4字节的字符串类型。

JNAME：表示工程项目的名称，定义其为最大长度为20字节的变长字符串类型。

CITY：表示工程项目所在的施工城市，定义其为最大长度为30字节的变长字符串类型。 要求：JNO字段为表的主键，其属性值必须不重复且不允许取空值，JNAME属性值不允许取空值。

P6 JNO 齿 轮 JNAME 红 30 CITY J1 J2 J3 J4 J5 J6 J7 三 建 一 汽 弹簧厂 造船厂 机车厂 无线电厂 半导体厂 北京 长春 天津 天津 唐山 常州 南京 (4) 供应商-零件-工程项目关联关系表SPJ（SNO，PNO，JNO，QTY）

SNO：表示为某工程项目供应零件的供应商的编号，定义其为长度为4字节的字符串类型。

PNO：表示某供应商为工程项目所供应的零件的编号，定义其长度为4字节的字符串类型。

JNO：表示正在被施工的工程项目的编号，定义其为长度为4字节的字符串类型。 QTY：表示某供应商为工程项目所供应的零件的数量，定义其为整数类型。

要求：SNO、PNO、JNO属性值不允许取空值；SPJ表的主键为SNO、PNO和JNO三属性的组合；SNO、PNO、JNO均为外码，且SNO字段的取值参照S表中SNO字段的取值，PNO字段的取值参照P表中PNO字段的取值，JNO字段的取值参照J表中JNO字段的取值；QTY属性值不能为空值，且QTY属性值限制在1～10000范围内。

SNO PNO JNO QTY S1 S1 S1 S1 S2 S2 S2 S2 S2 S2 S3 S3 S4 S4 S4 S5 S5 S5 S5 P1 P1 P1 P2 P3 P3 P3 P3 P5 P5 P1 P3 P5 P6 P6 P2 P3 P6 P6 J1 J3 J4 J2 J1 J2 J4 J5 J1 J2 J1 J1 J1 J3 J4 J4 J1 J2 J4 200 100 700 100 400 200 500 400 400 100 200 200 100 300 200 100 200 200 500 二、针对实验数据库ShiYan，完成以下单表查询操作： 1．查询为工程J1供应零件的供应商号码SNO。 2．查询为工程J1供应零件P1的供应商号码SNO。 3．找出所有供应商的名称和所在城市。

4．找出零件的所有信息，以及仅找出零件的颜色和重量。 5．找出使用供应商S1所供应零件的工程号码。 6．找出为工程供应零件的总数量不低于500的供应商号码及供应总数量，结果按供应商号码分类并且按供应总数量降序排列。

7．从J表中分别检索出第1条及前33%的工程项目信息。

8．统计P表中颜色为红色的零件的个数，并指定该查询列的名称为“红色零件数”。 9查询P表中各工程项目编号，名称及重量按86%计算后的信息，其中重量按86%计算后的查询列名改为“零件净重”。

10．查询SPJ表，要求查询结果式样为“供应商S1为工程项目J1供应零件P1的数量为300”。

11．查询S表STATUS值大于20且小于40，或SNAME字段值的第一个字为“精”或第三个字为“益”或“民”的供应商信息。

12．查询J表中JNAME值为三建和机车厂的工程项目信息。

三、完成以下多表查询操作：

1．查询为工程J1供应红色零件的供应商号码SNO。 2．查询没有使用天津供应商生产的零件并且当前工程所使用零件的颜色全部为红色的工程号JNO。

3．查询至少选用了供应商S1所供应的全部零件的工程号JNO。 4．找出工程项目J2使用的各种零件的名称及其重量。

5．找出上海厂商供应的所有零件号码。 6．找出使用上海产的零件的工程名称。 7．找出没有使用天津产的零件的工程号码。 8．找出重量最轻的红色零件的零件编号PNO。

9．找出供应商与工程所在城市相同的供应商提供的零件号码。

10．找出所有这样的一些＜CITY，CITY，PNAME＞三元组，使得第一个城市的供应商为第二个城市的工程供应零件的名称为PNAME。

11．重复第15题，但不检索两个CITY值相同的三元组。

12．找出供应商S1为工程名中含有“厂”字的工程供应的零件数量总和。

四、针对实验数据库ShiYan，完成下列数据更新操作：

1．在S表中插入元组“s6，华誉，40，广州，02085268888”。 2．在J表中插入元组“j8，传感器厂”。

3．对每一个供应商，求他为各种工程供应零件的总数量，并将此结果存入数据库。 4．将P表中PNO值为p6的元组的color属性值改为绿，weight属性值改为60。 5．将SPJ表中前4个元组的qty属性值统一修改为300。

6．将S表中city属性名含有“京”或“津”的相应status属性值增加100。 7．将供应商s2为“一汽”工程项目所供应的零件数量修改为2000。 8．将全部红色零件的颜色修改为浅红色。

9．由s5供给j4的零件p6改为由s3供应，请在数据库中作必要的数据修改。

10．在SPJ表中新增一列属性名为SDATE的属性列，对该表中的每一元组在SDATE属性列上填上实验当时的日期和时间。