信息化2.0背景下基于平臺的數據庫綜合設計與實現

李艷杰

(山東華宇工學院，山東 德州 253034)

MySQL數據庫用途廣泛，絕大多數中小型企業的網站或管理系統均采用它作為后臺數據庫。此數據庫操作性較強，如果想掌握數據庫具體應用，必須從整體上把握數據庫用法。對用戶而言，通過數據庫綜合設計提高對其認識尤為重要。從整體上全面描述了數據庫綜合設計內容，內容相互聯系，保持了數據一致性，數據庫中所有知識點較全面地貫穿在一起。

1 設計數據庫和數據表

數據庫設計要考慮實際生活需要，數據庫名稱要能體現數據代表的含義，如學籍管理系統、企業員工管理系統、教務管理系統、科研管理系統等。以學籍管理系統為例進行設計，設計的4個表均存在聯系，通過學號、班級編號、課程編號聯系在一起，建立表及要求如下：

數據表1為學籍表，表名稱為xueji，包括學號、姓名、性別、班級編號、籍貫、出生年月、學號為主鍵，注意在設置性別字段時，要用枚舉類型，只能輸入男(1)或女(2)，不能輸入其他，這樣設計更符合實際需求，代碼如下：

mysql> create table xueji(xh int,xm char(6),xb enum(’男’,’女’) not null,bjbh int,jg varchar(20),csny date);輸入記錄如圖1所示。

圖1 學籍表記錄Fig.1 School enrollment records

數據表2為課程表，表名稱為kecheng，包括課程編號和課程名稱，代碼如下：

mysql> create table kecheng(kid int,kname char(18));輸入記錄如圖2所示。

圖2 課程表記錄Fig.2 Class schedule records

數據表3為成績表，表名稱為chengji，包括學號、課程編號、成績，代碼如下：

mysql> create table chj(xh int,kid int, chengji float); 輸入記錄如圖3所示。

圖3 成績表部分記錄Fig.3 Part records of grade table

數據表4為班級表，表名稱為bianji，包括班級編號、班級名稱、專業，代碼如下：

mysql> create table banji(bid int,bname char(10),zhuanye char(10)); 輸入記錄如圖4所示。

圖4 班級表記錄Fig.4 Class records

2 設計查詢

查詢在數據庫中應用廣泛，關鍵是如何根據需要設計出查詢條件。設計了以下6個查詢，并附完整代碼。

1.查找jg在“山東”所有男生記錄，顯示xb和jg。

mysql> select jg,xb from xsh where jg like ’山東%’and xb=’女’;

2.查找年齡在12～15歲的學生的學號和年齡，列的名稱為年齡和學號。

mysql> select xh,year(now()-year(csny)) as nianling from xueji where year(now())

-year(csny) between 18 and 20

3.查找來自“山東”和“河北”兩地的學生所有記錄，分別用in和left()函數實現。

mysql> select * from xueji where left(jg,2) in(’山東’,’河北’);

mysql> select* from xueji where left(jg,2)=’山東’or’河北’;

4.查找姓名以“李”開頭的所有記錄。

mysql> select *from xueji where xm like ’李%’;

5.查找姓名以“張”開頭的名字為兩個字符的所有記錄。

mysql> select * from xueji where xm like ’張_’;

6.按課程編號分組，要求顯示每組中的成績和每組中成績的最大值。

mysql>Select kid,group_concat(chengji), max(chengji) from chj group by kid;

3 設計索引

索引設計的目的是提高查詢速度。

1.給課程表的課程編號添加普通索引，索引名稱suoyin1。

alter table kecheng add index suoyin1(kid);

2.為xueji表的學號和姓名添加組合索引，索引名稱Suoyin2。

alter table xueji add index suoyin2(xh,xm);

4 設計視圖

視圖設計有以下3個優勢：一是保證簡單化，數據集中。根據需要進行查詢得到數據，數據行和數據列都少且集中。二是安全性高。數據庫管理員可以授權某些用戶具有不同權限，例如：學籍管理系統中學生僅有查看成績的權限，不能修改成績；某用戶僅有查看和修改視圖的權限，那么該用戶對表就沒有任何權限，這樣提高了數據庫安全性。三是隱藏數據復雜性，簡化操作。視圖可以幫助用戶屏蔽真實表結構變化帶來的影響。

設計視圖如下：創建視圖v1：顯示王建同學數據庫和軟件工程的成績。

mysql> create view v1 as select xm,kname,chengji from xueji,chj,kecheng where xueji.xh=chj.xh and chj.kid=kecheng.kid and xm=’王建’and kname=’軟件工程’;

5 設計存儲過程

存儲過程的優勢是一次編譯，多次使用，對于多次查詢功能而言，可以建立存儲過程，縮減操作執行時間。本案例設計以下兩個存儲過程。

5.1 存儲過程1

創建根據指定學號查詢學生所有課程成績信息的存儲過程p1，顯示內容包括學號、課程名稱和成績，并執行存儲過程查詢學號為20160101的學生的學習成績。

mysql>delimiter //

mysql> create procedure p1(in a int) begin select xh,kname,chengji from chj,kech

eng where kecheng.kid=chj.kid and xh=a; end //

mysql>delimiter ;

執行存儲過程結果如圖5所示：

圖5 存儲過程1設計結果Fig.5 Results of stored procedure 1 design

5.2 存儲過程2

創建統計每門課程總成績和平均成績的存儲過程，并將課程總成績和平均成績輸出，執行存儲過程，統計數據庫的總成績和平均成績。

mysql> create procedure p4()

-> begin

-> select kname,sum(chengji),avg(chengji) from kecheng,chj where kecheng.k

id=chj.kid group by chj.kid; end//

注意：group by chj.kid;一定要寫清楚 chj.kid，否則編譯系統會提示找不到kid這個字段，因為兩個表中都有此字段存在。執行存儲過程結果如圖6所示：

圖6 存儲過程2設計結果Fig.6 Results of stored procedure 2 design

6 總結

描述了數據庫綜合設計內容及實現過程，考慮到平臺使用效果，此綜合案例設計對于學生或用戶而言均可以根據實際環境進行修改，并根據代碼進行驗證，全面提高了人們對數據庫的認識。