《数据库原理》上机实验：SQL实验3（详细代码+截图）

实验三 SQL练习3

一、实验目的

    1．掌握基本表的删除与修改；

    2．掌握实体完整性、参照完整性和用户定义的完整性的定义、检查和违约处理；

    3．掌握视图的定义、查询和更新，了解视图的作用。

二、实验学时

2学时

三、实验内容

1．完成以下操作：

⑴ 向在实验二中所定义的数据表增加“备注”列，其数据类型为字符型，并查看新增列的值。

ALTER TABLE 材料表

ADD 备注 VARCHAR(50);

ALTER TABLE 材料消耗表

ADD 备注 VARCHAR(50);

ALTER TABLE 成本表

ADD 备注 VARCHAR(50);

ALTER TABLE 单位表

ADD 备注 VARCHAR(50);

ALTER TABLE 油水井表

ADD 备注 VARCHAR(50);

 

ALTER TABLE 汇总表

ADD 备注 VARCHAR(50);

⑵ 对上述数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后再次执行实验二中实验内容3-⑵的操作，并观察记录执行结果。

因为在实验一中就定义了主码PRIMARY KEY，所以此步省略。

INSERT INTO 汇总表(施工单位,年月,结算金额)

       SELECT 单位名称,YEAR(结算日期)*100+MONTH(结算日期), SUM(结算金额)

       FROM 成本表,单位表

       WHERE 预算单位 = 单位代码

       GROUP BY 单位名称,YEAR(结算日期)*100+MONTH(结算日期);

⑶ 删除上述数据表中的数据，然后再删除该数据表，对这两个操作进行比较。

DELETE

FROM 汇总表;

(只删除了表格数据，但表格还存在，只是为空表)

DROP TABLE 汇总表;

（即把表格都给删除了，包括表格数据及表格本身）

2．完成以下任务：

⑴ 对实验一中所定义的5个数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后执行以下2个操作，观察并记录实体完整性的检查和违约处理。

① insert  into  材料消耗表  values('zy2022001','wm004',100)

② insert  into  材料消耗表  values('zy2022002',NULL,200)

ALTER TABLE 材料表 ALTER COLUMN 物码 CHAR(20) NOT NULL;

（不成功）

ALTER TABLE 材料表 ADD CONSTRAINT 主码 PRIMARY KEY (物码);

（不成功）

ALTER TABLE 材料消耗表 ALTER COLUMN 物码 CHAR(20) NOT NULL;

（不成功）

ALTER TABLE 材料消耗表 ALTER COLUMN 单据号 VARCHAR(20) NOT NULL;

（不成功）

ALTER TABLE 材料消耗表 ADD CONSTRAINT 主码_材料消耗表 PRIMARY KEY (单据号,物码);

（成功）

ALTER TABLE 单位表 ALTER COLUMN 单位代码 VARCHAR(20) NOT NULL;

（不成功）

ALTER TABLE 单位表 ADD CONSTRAINT 主码_单位表 PRIMARY KEY (单位代码);

（成功）

ALTER TABLE 油水井表 ALTER COLUMN 井号 VARCHAR(20) NOT NULL;

（不成功）

ALTER TABLE 油水井表 ADD CONSTRAINT 主码_油水井表 PRIMARY KEY(井号);

（成功）

ALTER TABLE 成本表 ALTER COLUMN 单据号 VARCHAR(20) NOT NULL;

（不成功）

ALTER TABLE 成本表 ADD CONSTRAINT 主码_成本表 PRIMARY KEY(单据号);

（成功）

INSERT  INTO  材料消耗表  VALUES('ZY2021001','WM004',100);

（报错）

INSERT  INTO  材料消耗表  VALUES('ZY2021002',NULL,200);

（报错）

⑵ 对实验一中所定义的5个数据表增加相应的参照完整性约束，并观察在数据表中存在数据的情况下参照完整性约束是否创建成功，然后执行以下操作，观察并记录参照完整性的检查和违约处理。

ALTER TABLE 材料消耗表 ADD FOREIGN KEY(单据号) REFERENCES 成本表(单据号);

（成功）

ALTER TABLE 材料消耗表 ADD FOREIGN KEY(物码) REFERENCES 材料表(物码);

（不成功）

ALTER TABLE 油水井表 ADD FOREIGN KEY(单位代码) REFERENCES 单位表(单位代码);

（成功）

ALTER TABLE 成本表 ADD FOREIGN KEY(预算单位) REFERENCES 单位表(单位代码);

（成功）

ALTER TABLE 成本表 ADD FOREIGN KEY(井号) REFERENCES 油水井表(井号);

（不成功）

① 将（y007   油井   112203002）插入到油水井表。

START TRANSACTION;

INSERT INTO 油水井表 VALUES('Y007','油井','112203002');

(不成功)

// INSERT 语句与 FOREIGN KEY 约束"FK__油水井表__单位代码__6E01572D"冲突。

② insert  into  材料消耗表  values('zy2022007','wm006',100)

INSERT  INTO  材料消耗表  VALUES('ZY2021007','WM006',100);

(不成功)

// INSERT 语句与 FOREIGN KEY 约束"FK__油水井__单位代码__2A4B4B5E"冲突

③ 将单位表中的（112202002  采油二矿二队）删除，查看油水井表和成本表中的数据有何变化。

DELETE

FROM 单位表

WHERE 单位代码 = '112202002'

AND 单位名称 ='采油二矿二队';

（不成功）

// DELETE 语句与 REFERENCE 约束"FK__油水井表__单位代码__6E01572D"冲突。

④ 将材料表中的（wm004 材料四 袋 10）修改为（wm04 材料四 袋 10）。

UPDATE 材料表

SET 物码 = 'WM04'

WHERE 物码 = 'WM004';

(成功)

⑤ 撤销上述成功的更新操作。

ROLLBACK;

⑶ 对实验一中所定义的5个数据表按以下要求增加相应的完整性约束条件，并观察在数据表中存在数据的情况下完整性约束是否创建成功。

① 单位表的单位名称不能取空值、且取值唯一。

ALTER TABLE 单位表 ALTER COLUMN 单位名称 CHAR(12) NOT NULL;

ALTER TABLE 单位表 ADD UNIQUE(单位名称);

② 油水井表的井别只允许取“油井”或“水井”，单位代码不能取空值。

ALTER TABLE 油水井表 ADD CHECK(井别 = '油井' OR 井别 = '水井');

ALTER TABLE 油水井表 ALTER COLUMN 单位代码 CHAR(10) NOT NULL;

③ 材料表的名称不能取空值、且取值唯一，计量单位不能取空值。

ALTER TABLE 材料表 ALTER COLUMN 名称 CHAR(20) NOT NULL;

ALTER TABLE 材料表 ADD UNIQUE(名称);

ALTER TABLE 材料表 ALTER COLUMN 计量单位 CHAR(20) NOT NULL;

④ 材料消耗表的消耗数量不能取空值。

ALTER TABLE 材料消耗表 ALTER COLUMN 消耗数量 FLOAT NOT NULL;

⑤ 对成本表根据实际应用的要求定义适当的用户定义的完整性约束条件。

ALTER TABLE 成本表 ALTER COLUMN 施工内容 VARCHAR(20) NOT NULL;

3．完成以下操作：

⑴ 定义视图V1，用于保存成本表和材料消耗表的全部列。

START TRANSACTION;

CREATE VIEW V1

AS

SELECT 成本表.*,材料消耗表.消耗数量,材料消耗表.物码

FROM 成本表,材料消耗表

WHERE 成本表.单据号 = 材料消耗表.单据号;

⑵ 查询上面定义的视图V1，可任意组合查询条件，构造出2个查询。

SELECT *

FROM V1

WHERE 单据号 = 'ZY2020001';

⑶ 定义一个反映成本表预算状态的视图V2，并向该视图插入('zy2022008','112202002','y005',10000,'张三', '2022-07-02')，查看成本表的数据有何变化。

CREATE VIEW V2

AS

SELECT 单据号,预算单位,井号,预算金额,预算人,预算日期

FROM 成本表;

INSERT

INTO V2

VALUES('ZY2021008','112202002','Y005',10000,'张三', '2021-07-02');

SELECT * FROM V2

​​​​​​​

⑷ 撤销上述成功的更新操作。

ROLLBACK;