ACCESS數(shù)據(jù)庫實驗指導書

1、數(shù)據(jù)庫原理及應用實驗指導書(ACCESS數(shù)據(jù)庫) 指導老師：陳俊智昆明理工大學國土資源工程學院學院2012年3月目 錄實驗(一)：SQL數(shù)據(jù)定義功能實驗(二)：練習SQL數(shù)據(jù)操縱功能實驗(三)：數(shù)據(jù)庫綜合設計實驗(一)：SQL數(shù)據(jù)定義功能一、實驗內(nèi)容及要求1. 使用SQL語句建立學生管理系統(tǒng)相關的表，同時完善各表的相關完整性約束。其中帶有下劃線的為主碼 學院（學院代碼，學院名稱） create table 學院(學院代碼 char(8) primary key,學院名稱 char(8); 學生（學號，姓名，性別，學院代碼） ，學院代碼為外碼create table 學生(學號 char(8)

2、not null unique,姓名 char(8),性別 char(8),學院代碼 char(8),primary key(學號),foreign key(學院代碼) references 學院(學院代碼); 教師（教師號，教師姓名，學院代碼），學院代碼為外碼create table 教師(教師號 char(8) primary key,教師姓名 char(8),學院代碼 char(8),foreign key(學院代碼) references 學院(學院代碼); 課程（課程號，課程名，學時）create table 課程(課程號 char(8) primary key,課程名 char(8

3、),學時 char(8); 學習（學號，課程號，成績），學號為外碼，課程號為外碼create table 學習(學號 char(8),課程號 char(8),成績 char(8),primary key(學號,課程號),foreign key(學號) references 學生(學號),foreign key(課程號) references 課程(課程號); 開課（教師號，課程號），教師號為外碼，課程號為外碼create table 開課(教師號 char(8),課程號 char(8),primary key(教師號,課程號),foreign key(教師號) references 教師(教師

4、號),foreign key(課程號) references 課程(課程號);2. 對各表進行增加、刪除、修改屬性操作添加操作：對學生表添加出生日期字段，和家庭地址字段，教師表增加性別字段，出生日期字段，對課程表增加先修課程字段，并且先修課程為本關系外碼?？梢栽赼ccess圖形界面中用鼠標點擊單元格添加新字段右鍵選擇插入列新建一個列，同時也可以用sql建立表例如alter table 學生 add 出生日期 char(8);alter table 學生 add 地址 char(20);alter table 教師 add 性別 char(8);alter table 教師 add 出生日期 d

5、ate;alter table 課程 add 先修課程 char(8) ;alter table 課程 foreign key(先修課程) references 課程(先修課程);刪除操作：刪除學生表家庭地址字段，刪除教師表出生日期字段alter table 學生 drop column 地址;alter table 教師 drop column出生日期;修改操作：修改學生姓名字段，該字段值不允許取空值Alter table 學生 alter column 姓名 char(20) not null unique;3. 建立索引為學生表在學生姓名上建立名為sname-index 的索引，在學院代

6、碼字段上建立名為dept-index，降序。Create unique index sname_index on 學生(姓名);Create unique index dept_index on 學院(學院代碼 desc)4. 利用ACCESS 2003 /2007完善各表的數(shù)據(jù)內(nèi)容數(shù)據(jù)可以由所建表中直接輸入，也可以由 insert into 學生 values(. )插入例如在學生表中插入一組數(shù)據(jù)Insert into 學院 values ('101','計算機' );5. 建立tempstudent表，表模式自行定義，之后將其刪除建立tempstudent表

7、create table tempstudent(sno char(8),sname char(8);刪除表可以由語句 drop table tempstudent cascade; 執(zhí)行二、實驗目的熟練掌握SQL的各種數(shù)據(jù)定義功能，包括1.定義表的功能，包括主碼和外碼的定義2.修改表的定義功能，包括增加屬性，刪除屬性，修改屬性類型4.建立和刪除索引操作，理解索引的作用5.刪除表功能三、實驗步驟及運行結果四、實驗體會通過本次實驗我了解到了數(shù)據(jù)庫基本的建表方法，明白了如何去對一個表進行插入修改刪除和建立索引，掌握了基本的建表方法實驗(二)：練習SQL數(shù)據(jù)操縱功能一、實驗內(nèi)容及要求建表截圖學院表學

8、生表教師表學習表開課表課程表1求選修了某個老師上課的學生，成績在90分以上的姓名、課程名稱和成績； SELECT 姓名, 課程名, 成績FROM 學生, 教師, 學習, 課程, 開課WHERE 學生.學號=學習.學號 And 學習.課程號=課程.課程號 And 課程.課程號= 開課.課程號 And 開課.教師號=教師.教師號 And 教師姓名='張強' And 成績 >'90'2求選修了某個老師所授全部課程的學生姓名和學院名稱； select 姓名,學院名稱from 學生,學院where 學生.學院代碼=學生.學院代碼and 姓名 in(select di

9、stinct 姓名from 學生,學院where 學號 in(select 學號from 學習where 課程號 in(select 課程號from 開課where 教師號 in(select 教師號from 教師where 教師姓名='王強')and 學生.學院代碼=學院.學院代碼;3求沒有選修某門課程的學生學號和姓名； select distinct 學生.學號,姓名from 學生,課程,學習where 學生.學號=學習.學號and 學習.課程號=課程.課程號and 學生.學號 not in (select 學生.學號from 學生,課程,學習where 學生.學號=學習.

10、學號and 學習.課程號=課程.課程號and 課程名='數(shù)據(jù)結構'); 4求至少選修了兩門課程的學生學號； select 學號,count(課程號) as 選課數(shù)from 學習where 學號 between '08101'and '08108'group by 學號having count(課程號)>=2;5求某門課程不及格學生姓名和考試成績 select 姓名,成績from 學生,學習,課程where 學生.學號=學習.學號and 學習.課程號=課程.課程號and 課程名='數(shù)據(jù)庫'and 成績<'60&#

11、39;6求至少選修了與某個同學選修的課程相同的學生學號 select distinct 學號from 學習 as awhere not exists(select *from 學習 as bwhere 學號='08101'and not exists(select *from 學習 as cwhere a.學號=b.學號and b.課程號=c.課程號);7求至少選修了某兩門課程的學生姓名和學院名稱。 select 姓名,學院名稱from 學生,學習,課程,學院where 學生.學號=學習.學號and 學習.課程號=課程.課程號and 學生.學院代碼=學院.學院代碼and 課程名

12、='數(shù)據(jù)庫'and 學生.學號 in(select 學生.學號from 學生,學習,課程,學院where 學生.學號=學習.學號and 學習.課程號=課程.課程號and 學生.學院代碼=學院.學院代碼and 課程名='數(shù)據(jù)結構');8查詢某個同學沒有選修的課程號和課程名 select 課程號,課程名from 課程where 課程.課程號 not in(select 課程.課程號from 學生,學習,課程where 學生.學號=學習.學號and 學習.課程號=課程.課程號and 姓名='顧曉娜');9查詢沒有被任何學生選修的課程的課程號 selec

13、t 課程號from 課程where 課程.課程號 not in(select 課程.課程號from 課程,學習where 課程.課程號=學習.課程號);10求選修了全部課程的學生姓名 select 姓名from 學生 where not exists(select *from 學習where not exists(select *from 課程where 學生.學號=學習.學號and 學習.課程號=課程.課程號);11查詢各學院某門課程的平均分，并按照成績從高到低的順序排列。select 學院名稱,avg(成績) as 平均成績from 學生,學院,學習,課程where 學生.學號=學習.學號

14、 and 學生.學院代碼=學院.學院代碼and 學習.課程號=課程.課程號and 課程名='數(shù)據(jù)庫'group by 學院名稱 order by avg(成績) desc12查詢選修數(shù)據(jù)庫原理課程的學生姓名和所在院系，結果按各院系排列，同時成績從高到低排列。 select 學院名稱,成績from 學生,學院,學習,課程where 學生.學號=學習.學號 and 學生.學院代碼=學院.學院代碼and 學習.課程號=課程.課程號and 課程名='數(shù)據(jù)庫'order by 學院名稱,成績 desc13求學時在30-45之間（含30和45）的課程的課程號和課程名稱及授課

15、教師。 select 課程.課程號,課程名,教師姓名from 教師,開課,課程where 教師.教師號=開課.教師號and 開課.課程號=課程.課程號and 學時 between '30' and '45'14檢索選修數(shù)據(jù)庫原理課程的最高分學生的姓名 select 姓名,成績from 學生,學習,課程where 學生.學號=學習.學號and 學習.課程號=課程.課程號and 課程名='數(shù)據(jù)庫'and 成績= (select max(成績)from 學生,學習,課程where 學生.學號=學習.學號and 學習.課程號=課程.課程號and 課程名=

16、'數(shù)據(jù)庫');15查詢選課人數(shù)超過50（根據(jù)情況自己定人數(shù)）人的課程的課程號及課程名。select 課程號,課程名from 課程where 課程名 in(select 課程名from 課程,學習where 學習.課程號=課程.課程號group by 課程名 having count(課程.課程號)>=2);16自行提出查詢要求，內(nèi)容包括并運算、差運算、交運算、笛卡兒積運算、連接運算、自然連接運算以及復雜統(tǒng)計表。16查詢選修了張強老師和王慧老師卻沒有選修王鵬老師課程的學生的學號和姓名和成績，并且按成績由高到低排序.select 學生.學號,姓名,成績from 學生,學習wh

17、ere 學生.學號=學習.學號and 學習.學號 in(select 學號from 開課,教師,學習where 學習.課程號=開課.課程號and 開課.教師號=教師.教師號and 教師姓名='張強'and 學號 in(select 學號from 開課,教師,學習where 學習.課程號=開課.課程號and 開課.教師號=教師.教師號and 教師姓名='王慧'and 學號 not in(select 學號from 開課,教師,學習where 學習.課程號=開課.課程號and 開課.教師號=教師.教師號and 教師姓名='王強')order by 成

18、績 desc; 二、實驗目的使用SQL語句進行各種查詢1、熟練掌握單表查詢，包括 (1) 選擇表中的若干列（投影） (2) 選擇表中的若干元組（選擇） (3) 對查詢結果排序 (4) 使用集函數(shù) (5) 對查詢結果分組2、熟練掌握復雜查詢，包括1) 等值與非等值連接查詢 2) 自身連接:一個表與其自己進行連接。3) 外連接查詢4) 復合條件連接：WHERE 子句中可以有多個連接條件。3、熟練掌握嵌套查詢，包括1) 帶有IN謂詞的子查詢2) 帶有比較運算符的子查詢（子查詢一定要跟在比較符之后）3) 帶有ANY或ALL謂詞的子查詢（使用ANY和ALL時必須同時使用比較運算符）4) 帶有EXISTS

19、謂詞的子查詢（查詢結果不返回任何數(shù)據(jù)，只產(chǎn)生邏輯“真”或“假”）4、使用SQL和ACCESS結合完成一些復雜的統(tǒng)計三、實驗步驟及運行結果四、實驗體會通過本次實驗我了解到了如何在語句中進行查詢，而且還學會了很多不同的查詢方法，明白了如何通過多種途徑來實現(xiàn)自己的查詢結果，最總都在accsse中得到了驗證。實驗(三)：數(shù)據(jù)庫綜合設計1 數(shù)據(jù)庫設計1.1公司描述該虛擬公司的主營業(yè)務為日常商品的銷售，業(yè)務描述為其上游有供應商為其提供所有商品，下游面向所有個人主體客戶和法人客戶。公司有送貨上門服務，是由公司的員工完成。1.2 部分ER圖圖1-1供應商供應商品包括顧客銷售我公司配送雇員屬于填寫訂單訂單明細方

20、框表示實體，菱形表示實體之間的關系。數(shù)據(jù)庫的中表是根據(jù)ER圖中的實體和關系來確定的。如圖1-1所示圖1供應商供應商品包括顧客銷售我公司配送雇員屬于填寫訂單訂單明細1.3數(shù)據(jù)庫邏輯結構設計（數(shù)據(jù)庫的表結構設計）根據(jù)ER圖可以抽象出七個實體和六個關系，經(jīng)分析得出數(shù)據(jù)庫邏輯結構，也就是數(shù)據(jù)庫中的表：供應商表、商品表、顧客表、雇員表、訂單表、訂單明細表。各個表的結構如下:（紅色字體為表名、括號中為標的屬性、中括號內(nèi)為屬性的類型、有下劃線的屬性為表的主鍵）供應商（供應商ID自動編號、供應商名稱文本、供應商地址文本、供應商電話文本、供應商傳真文本） 商品（商品ID自動編號、商品名稱文本、單價貨幣、供應商I

21、D數(shù)字） 顧客（顧客ID自動編號 、顧客名文本、顧客地址文本、顧客電話文本） 雇員（雇員ID自動編號、雇員姓名文本、雇員電話文本、雇員職位文本）訂單（訂單ID自動編號、訂單編號文本、顧客ID數(shù)字、雇員ID數(shù)字、訂單日期時間日期、送貨日期時間日期）訂單明細（訂單明細ID自動編號、訂單ID數(shù)字、商品ID數(shù)字、商品數(shù)量數(shù)字）2 數(shù)據(jù)庫實現(xiàn)2.1 新建數(shù)據(jù)庫新建一個數(shù)據(jù)庫將其命名為自己公司的名稱，如圖2-1所示。圖2-1122.2新建表用“設計視圖”進行表設計，如圖2-2所示。圖2-2建立“供應商”表，并將“供應商ID”設置為主鍵，如圖2-3所示圖2-3然后根據(jù)前面設計的數(shù)據(jù)庫表結構分別建立商品、顧客

22、、我公司、雇員、訂單和訂單明細6個表。最終建成的表如圖49所示。(1) “商品”表，如圖2-4所示圖2-4(2)“顧客”表，如圖2-5所示圖2-5(3)“訂單”表，如圖2-6所示圖2-6(4)“訂單明細”表，如圖2-7所示圖2-7（6）“雇員”表，如圖2-8所示（7）“我公司”表，如圖2-9所示圖2-9圖2-8 為各個表添加記錄。（1）訂單表：如圖2-10所示圖2-10（2）訂單明細表：如圖2-11所示圖2-11（3）供應商表：如圖2-12所示圖2-12（4）顧客表：如圖2-13所示圖2-13（5）雇員表：如圖2-14所示圖2-14（6）商品表，如圖2-15所示圖2-15圖2-16為表之間建立

23、關系，如圖2-16所示2.3查詢 在設計一個數(shù)據(jù)庫時，為了節(jié)省存儲空間，常常把數(shù)據(jù)分類，并分別存放在多個表里。盡管可以在基本表中可以進行許多操作，如瀏覽、篩選、更新等，但很多時候還是需要檢索一個或多個表（或查詢）中符合條件的數(shù)據(jù)，將這些數(shù)據(jù)集合在一起，執(zhí)行瀏覽、計算等各種操作。查詢實際上就是將這些分散的數(shù)據(jù)再集合起來。使用查詢可以執(zhí)行一組選定的數(shù)據(jù)記錄集合，雖然這個記錄在數(shù)據(jù)庫中實際上并不存在，只是在運行查詢時，ACCESS才從查詢源數(shù)據(jù)表中創(chuàng)建它，但正是這個特性，使查詢具有了靈活方便的數(shù)據(jù)操縱能力。2.3.1查詢的基本作用（1）通過查詢?yōu)g覽表中的數(shù)據(jù)，分析數(shù)據(jù)和修改數(shù)據(jù)（2）利用查詢可以使用

24、戶的注意力集中在自己感興趣的數(shù)據(jù)上，而將當前不需要的數(shù)據(jù)排除在查詢之外。（3）將經(jīng)常處理的原始數(shù)據(jù)或統(tǒng)計計算定義為查詢，可大大簡化數(shù)據(jù)的處理工作。（4）查詢的結果集可以用于生成新的基本表，可以進行新的查詢，還可以為窗體、報表提供數(shù)據(jù)。下面就根據(jù)查詢的四個主要作用，針對我們的公司數(shù)據(jù)庫進行查詢設計與實施。2.3.2.查詢的設計與實施1）通過查詢?yōu)g覽表中的數(shù)據(jù)實例：查詢出哪些供應商提供哪些商品，操作過程如圖2-17所示。圖2-17選擇需要的屬性（字段），將表中的字段“拖動”到如圖2-28所示的2位置運行效果為“供應商供應的商品”，如圖2-18所示圖2-182）建立有查詢條件的查詢實例：查詢商品價格

25、小于2.00元的所有商品在單價下面的條件中輸入” >0.5”，保存后運行，如圖2-19 。圖2-192.4窗體在Access中，窗體是一種數(shù)據(jù)對象的格式，是輸入和維護表中數(shù)據(jù)的另一種方式。窗體起著聯(lián)系數(shù)據(jù)庫用戶與用戶的橋梁作用，窗體提供了查閱、新建、編輯和刪除數(shù)據(jù)的最富彈性的方法。窗體與下一章將要講到的報表都是用來進行數(shù)據(jù)庫數(shù)據(jù)維護的，但窗體主要用于數(shù)據(jù)的輸入，報表則用于屏幕或打印輸出的窗體中查閱的數(shù)據(jù)。2.4.1窗體的基本作用1） 數(shù)據(jù)的顯示和編輯2） 數(shù)據(jù)輸入3） 應用程序流控制4） 信息顯示和數(shù)據(jù)打印2.4.2窗體的設計與實施下面根據(jù)窗體的四個作用來設計并實施四個窗體。1） 數(shù)據(jù)的顯示和編輯（1）使用“窗體向?qū)А眲?chuàng)建窗體，如圖2-20所示，。圖2-202）數(shù)據(jù)輸入本實例是創(chuàng)建一個帶有子窗體的窗體，效果圖如圖2-21所示。左側(cè)紅框內(nèi)的內(nèi)容是訂單相關信息，即哪位顧客的哪個訂單為子窗體，其中顯示主窗體的訂單中訂購的所有商品的名稱、數(shù)量和單價圖2-21該窗體中顯示的