信息化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 总结

描述了数据库综合设计内容及实现过程，考虑到平台使用效果，此综合案例设计对于学生或用户而言均可以根据实际环境进行修改，并根据代码进行验证，全面提高了人们对数据库的认识。