APP下载

信息化2.0背景下基于平台的数据库综合设计与实现

2022-01-20李艳杰

黑龙江科学 2021年24期
关键词:数据表视图管理系统

李艳杰

(山东华宇工学院,山东 德州 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 总结

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

猜你喜欢

数据表视图管理系统
基于James的院内邮件管理系统的实现
湖北省新冠肺炎疫情数据表(2.26-3.25)
湖北省新冠肺炎疫情数据表
湖北省新冠肺炎疫情数据表
停车场寻车管理系统
生产管理系统概述
视图
Y—20重型运输机多视图
SA2型76毫米车载高炮多视图
海盾压载水管理系统