一、 實驗環(huán)境
1． Windows2000或以上版本；
2． SQLServer 2005或以上版本。
二、 實驗目的
1． 掌握數(shù)據(jù)庫表與視圖的基礎知識；
2． 掌握創(chuàng)建、修改、使用、刪除表與視圖的不同方法。
三、 實驗內(nèi)容
完成實驗指導書中p60-61頁 “實驗內(nèi)容與要求”。
四、 實驗步驟、源代碼及實驗結果
- 創(chuàng)建數(shù)據(jù)庫及表
- 用你掌握的某種方法，創(chuàng)建訂報管理子系統(tǒng)的數(shù)據(jù)庫DingBao,在DingBao數(shù)據(jù)庫中用交互式界面操作方法或CREATE TABLE創(chuàng)建如下三表的表結構(表名及字段名使用括號中華出的英文名)，并完成三表所示內(nèi)容的輸人，根據(jù)需要可自行設計輸人更多的表記錄。
- 創(chuàng)建表結構時要求滿足:①報紙編碼表(PAPER)以報紙編號(pno)為主鍵，如表3-1所示;②顧客編碼表(CUSTOMER)以顧客編號(cno)為主鍵，如表3-2 所示;③報紙訂閱表(CP以報紙編號(pno)與顧客編號(cno)為主鍵，訂閱份數(shù)(num)的缺省值為1,如表3-3所示。
①、報紙編碼表
CREATE TABLE PAPER
(
pno char(9) PRIMARY KEY,
pna char(9),
ppr float
);
②、顧客編碼表
CREATE TABLE CUSTOMER
(
cno char(9) PRIMARY KEY,
cna char(9) UNIQUE NOT NULL,
adr char(20)
);
③、報紙訂閱表
CREATE TABLE CP
(cno CHAR(9),
pno CHAR(9),
num int DEFAULT 1,
PRIMARY KEY(cno,pno),
FOREIGN KEY(cno) REFERENCES CUSTOMER(cno),
FOREIGN KEY(pno) REFERENCES PAPER(pno)
);
- 插入數(shù)據(jù)
①、
insert into PAPER values('000001','人物日報','12.5');
insert into PAPER values('000002','解放日報','14.5');
insert into PAPER values('000003','光明日報','10.5');
insert into PAPER values('000004','青年報','11.5');
insert into PAPER values('000005','揚子網(wǎng)報','18.5');
②、
insert into CUSTOMER values('10000001','李濤','無錫市解放東路123號');
insert into CUSTOMER values('10000002','錢金浩','無錫市人民西路234號');
insert into CUSTOMER values('10000003','鄧杰','無錫市惠河路270號');
insert into CUSTOMER values('10000004','朱海紅','無錫市中山東路432號');
insert into CUSTOMER values('10000005','歐陽陽文','無錫市中山東路532號');
③、
insert into CP values('10000001','000001','2');
insert into CP values('10000001','000002','4');
insert into CP values('10000001','000005','6');
insert into CP values('10000002','000001','2');
insert into CP values('10000002','000003','2');
insert into CP values('10000002','000005','2');
insert into CP values('10000003','000003','2');
insert into CP values('10000003','000004','4');
insert into CP values('10000004','000001','1');
insert into CP values('10000004','000003','3');
insert into CP values('10000004','000005','2');
insert into CP values('10000005','000003','4');
insert into CP values('10000005','000002','1');
insert into CP values('10000005','000004','3');
insert into CP values('10000005','000005','5');
insert into CP values('10000005','000001','4');
2、創(chuàng)建與使用視圖
①、
CREATE VIEW C_P_N
AS
SELECT CP.cno,cna,CP.pno,pna,num
FROM PAPER,CP,CUSTOMER
WHERE CP.cno=CUSTOMER.cno AND CP.pno=PAPER.pno;
②、ALTER VIEW C_P_N
AS
SELECT PAPER.pno,pna,CUSTOMER.cno,cna,num,ppr
FROM PAPER,CUSTOMER,CP;
③、
查詢“人物日報”的訂閱數(shù)量
SELECT *
FROM C_P_N
WHERE pna='人物日報';
將李濤顧客改為羅豬豬
UPDATE C_P_N
SET cna='羅豬豬'
WHERE cna='李濤';
發(fā)現(xiàn)表中數(shù)據(jù)也改變了
將‘人物日報’修改為‘人民日報’
UPDATE C_P_N
SET pna='人民日報'
WHERE pna='人物日報';
有重復的經(jīng)過和老師的探討修改了代碼如下
①、
CREATE VIEW C_P_N
AS
SELECT CP.cno,cna,CP.pno,pna,num
FROM PAPER,CP,CUSTOMER
WHERE CP.cno=CUSTOMER.cno AND CP.pno=PAPER.pno;
②、ALTER VIEW C_P_N
AS
SELECT CP.cno,cna,CP.pno,pna,num,ppr
FROM PAPER,CUSTOMER,CP
WHERE CP.cno=CUSTOMER.cno AND CP.pno=PAPER.pno;
查詢“人民日報”的訂閱數(shù)量
SELECT *
FROM C_P_N
WHERE pna='人民日報';
④、DROP VIEW C_P_N;
更新多個數(shù)據(jù)表格：
ALTER VIEW C_P_N
AS
SELECT CP.cno,cna,CP.pno,pna
FROM PAPER,CUSTOMER,CP
WHERE CP.cno=CUSTOMER.cno AND CP.pno=PAPER.pno;
五、 實驗體會
通過這次實驗學會了主要建表時需要將幾個表中重復的數(shù)據(jù)進行合并，并且對更新視圖的語言記憶更加深刻了，中間因為表格中是人物日報，但是要我們查找的是人民日報而出現(xiàn)錯誤，將UPDATE語句中的‘人民日報’改為‘人物日報’之后就能查詢到對應的數(shù)據(jù)
附：實驗準備代碼
CREATE TABLE PAPER
(
pno char(9) PRIMARY KEY,
pna char(9),
ppr float
);
CREATE TABLE CUSTOMER
(
cno char(9) PRIMARY KEY,
cna char(9) UNIQUE NOT NULL,
adr char(20)
);
CREATE TABLE CP
(cno CHAR(9),
pno CHAR(9),
num int DEFAULT 1,
PRIMARY KEY(cno,pno),
FOREIGN KEY(cno) REFERENCES CUSTOMER(cno),
FOREIGN KEY(pno) REFERENCES PAPER(pno)
);
insert into PAPER values('000001','人物日報','12.5');
insert into PAPER values('000002','解放日報','14.5');
insert into PAPER values('000003','光明日報','10.5');
insert into PAPER values('000004','青年報','11.5');
insert into PAPER values('000005','揚子網(wǎng)報','18.5');
insert into CUSTOMER values('10000001','李濤','無錫市解放東路123號');
insert into CUSTOMER values('10000002','錢金浩','無錫市人民西路234號');
insert into CUSTOMER values('10000003','鄧杰','無錫市惠河路270號');
insert into CUSTOMER values('10000004','朱海紅','無錫市中山東路432號');
insert into CUSTOMER values('10000005','歐陽陽文','無錫市中山東路532號');
insert into CP values('10000001','000001','2');
insert into CP values('10000001','000002','4');
insert into CP values('10000001','000005','6');
insert into CP values('10000002','000001','2');
insert into CP values('10000002','000003','2');
insert into CP values('10000002','000005','2');
insert into CP values('10000003','000003','2');
insert into CP values('10000003','000004','4');
insert into CP values('10000004','000001','1');
insert into CP values('10000004','000003','3');
insert into CP values('10000004','000005','2');
insert into CP values('10000005','000003','4');
insert into CP values('10000005','000002','1');
insert into CP values('10000005','000004','3');
insert into CP values('10000005','000005','5');
insert into CP values('10000005','000001','4');
CREATE VIEW C_P_N
AS
SELECT CP.cno,cna,CP.pno,pna,num
FROM PAPER,CP,CUSTOMER
WHERE CP.cno=CUSTOMER.cno AND CP.pno=PAPER.pno;
ALTER VIEW C_P_N
AS
SELECT CP.cno,cna,CP.pno,pna,num,ppr
FROM PAPER,CUSTOMER,CP
WHERE CP.cno=CUSTOMER.cno AND CP.pno=PAPER.pno;
SELECT *
FROM C_P_N
WHERE pna='人民日報';
UPDATE C_P_N
SET cna='羅豬豬'
WHERE cna='李濤';
UPDATE C_P_N
SET pna='人民日報'
WHERE pna='人物日報';
DROP VIEW C_P_N;
浙公網(wǎng)安備 33010602011771號
浙ICP備2021040463號-3