李艷杰

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

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

1 設(shè)計數(shù)據(jù)庫和數(shù)據(jù)表

數(shù)據(jù)庫設(shè)計要考慮實際生活需要，數(shù)據(jù)庫名稱要能體現(xiàn)數(shù)據(jù)代表的含義，如學(xué)籍管理系統(tǒng)、企業(yè)員工管理系統(tǒng)、教務(wù)管理系統(tǒng)、科研管理系統(tǒng)等。以學(xué)籍管理系統(tǒng)為例進行設(shè)計，設(shè)計的4個表均存在聯(lián)系，通過學(xué)號、班級編號、課程編號聯(lián)系在一起，建立表及要求如下：

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

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

圖1 學(xué)籍表記錄Fig.1 School enrollment records

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

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

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

數(shù)據(jù)表3為成績表，表名稱為chengji，包括學(xué)號、課程編號、成績，代碼如下：

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

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

數(shù)據(jù)表4為班級表，表名稱為bianji，包括班級編號、班級名稱、專業(yè)，代碼如下：

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

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

2 設(shè)計查詢

查詢在數(shù)據(jù)庫中應(yīng)用廣泛，關(guān)鍵是如何根據(jù)需要設(shè)計出查詢條件。設(shè)計了以下6個查詢，并附完整代碼。

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

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

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

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

-year(csny) between 18 and 20

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

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 設(shè)計索引

索引設(shè)計的目的是提高查詢速度。

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

alter table kecheng add index suoyin1(kid);

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

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

4 設(shè)計視圖

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

設(shè)計視圖如下：創(chuàng)建視圖v1：顯示王建同學(xué)數(shù)據(jù)庫和軟件工程的成績。

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 設(shè)計存儲過程

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

5.1 存儲過程1

創(chuàng)建根據(jù)指定學(xué)號查詢學(xué)生所有課程成績信息的存儲過程p1，顯示內(nèi)容包括學(xué)號、課程名稱和成績，并執(zhí)行存儲過程查詢學(xué)號為20160101的學(xué)生的學(xué)習(xí)成績。

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 ;

執(zhí)行存儲過程結(jié)果如圖5所示：

圖5 存儲過程1設(shè)計結(jié)果Fig.5 Results of stored procedure 1 design

5.2 存儲過程2

創(chuàng)建統(tǒng)計每門課程總成績和平均成績的存儲過程，并將課程總成績和平均成績輸出，執(zhí)行存儲過程，統(tǒng)計數(shù)據(jù)庫的總成績和平均成績。

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，否則編譯系統(tǒng)會提示找不到kid這個字段，因為兩個表中都有此字段存在。執(zhí)行存儲過程結(jié)果如圖6所示：

圖6 存儲過程2設(shè)計結(jié)果Fig.6 Results of stored procedure 2 design

6 總結(jié)

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