資料庫實驗3

一、實驗內容

1．完成以下操作

⑴ 向在實驗二中所定義的資料表增加“備注”列，其資料型別為字符型，并查看新增列的值

alter table 匯總表
    add 備注 char(20);

select *
from 匯總表

⑵ 對上述資料表增加主碼約束條件，并觀察在資料表中存在資料的情況下主碼約束是否創建成功，然后再次執行實驗二中實驗內容3-⑵的操作，并觀察記錄執行結果

alter table 匯總表
    alter column 施工單位 set default 5;
alter table 匯總表
    alter column 年月 set default 5;
alter table 匯總表
    add primary key (施工單位, 年月);
TRUNCATE TABLE 匯總表;

insert into 匯總表(施工單位, 年月, 結算金額)
select 施工單位,
       convert(結算日期, char),
       sum(結算金額)
from 作業專案表
group by 施工單位, convert(結算日期, char);

⑶ 洗掉上述資料表中的資料，然后再洗掉該資料表，對這兩個操作進行比較

# delete
# from 匯總表;

truncate table 匯總表;

2．完成以下任務

⑴ 對實驗一中所定義的5個資料表增加主碼約束條件，并觀察在資料表中存在資料的情況下主碼約束是否創建成功，然后執行以下2個操作，觀察并記錄物體完整性的檢查和違約處理

alter table 作業專案表
    add primary key (單據號);

alter table 單位表
    add primary key (單位代碼);

alter table 材料消耗表
    add primary key (單據號, 物碼, 消耗數量);

alter table 材料表
    add primary key (物碼);

alter table 油水井表
    add primary key (井號);

①

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

②

insert into 材料消耗表
values ('zy2020002', 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 單位表 (單位名稱);



insert into 油水井表
values ('y007', '油井', '112203002');
insert into 材料表
values (' zy2016007', ' wm006', 100, 10);

update 作業專案表
set 施工單位='作業公司作業五隊'
where 單據號 = 'zy2016001';

① 將（y007 油井 112203002）插入到油水井表，

② insert into 材料消耗表 values(‘zy2020007’,‘wm006’,100)

③ 將單位表中的（112202002 采油二礦二隊）洗掉，查看油水井表和成本表中的資料有何變化，

delete
from 單位表
where 單位代碼 = ' 112202002';

④ 將材料表中的（wm004 材料四 袋 10）修改為（wm04 材料四 袋 10），

update 材料表
set 物碼='wm04'
where 名稱 = '材料四';

⑤ 撤銷上述成功的更新操作，

⑶ 對實驗一中所定義的5個資料表按以下要求增加相應的完整性約束條件，并觀察在資料表中存在資料的情況下完整性約束是否創建成功

① 單位表的單位名稱不能取空值、且取值唯一，

② 油水井表的井別只允許取“油井”或“水井”，單位代碼不能取空值，

③ 材料表的名稱不能取空值、且取值唯一，計量單位不能取空值，

④ 材料消耗表的消耗數量不能取空值，

⑤ 對成本表根據實際應用的要求定義適當的用戶定義的完整性約束條件，

alter table 單位表
    add constraint aa1 check 單位名稱 is not null );
alter table 單位表
    add constraint uni unique (單位代碼);
alter table 油水井表
    add constraint aa2 check 井別 in (' 油井', '水井'));
alter table 油水井表
    alter column 單位代碼 char (20) not null;
alter table 材料表
    alter column 計量單位 char (20) not null;
alter table 材料表
    add constraint uni2 unique (計量單位);



alter table 材料表
    alter column 計量單位 char (20) not null;
alter table 材料表
    alter column 消耗數量 int not null;
alter table 材料費表
    alter column 單價 int not null;
alter table 作業專案表
    add constraint asd check (材料費 + 人工費 + 設備費 + 其它費用 = 結算金額);

3．完成以下操作

⑴ 定義視圖V1，用于保存成本表和材料消耗表的全部列

create view 視圖一
as
select 作業專案表.*, 物碼, 消耗數量, 單價
from 作業專案表,
     材料表
where 作業專案表.單據號 = 材料表.單據號;

⑵ 查詢上面定義的視圖V1，可任意組合查詢條件，構造出2個查詢

select 結算金額, 消耗數量
from 視圖一
where 物碼 = 'wm003';



select 預算單位,
       施工內容
from 視圖一
where 單據號 = 'zy201 6001'
  and 物碼 = 'wm001';

⑶ 定義一個反映成本表預算狀態的視圖V2，并向該視圖插入(‘zy2020008’,‘112202002’,‘y005’,10000,‘張三’, ‘2020-07-02’)，查看成本表的資料有何變化

create view 預算狀態
as
select 單據號,
       預算單位,
       井號,
       預算金額,
       預算人,
       預算日期
from 作業專案表;


insert into 預算狀態
values (' zy2016008', '112202002', ' y005', '10000', '張三', ' 2020-07-02');

⑷ 撤銷上述成功的更新操作