开发实例

开发实例

本文介绍如何使用PHP语言开发一个基于B/S结构的简单实例系统——学生成绩管理系统。

数据库的设计与实现

这部分需要创建四个表:班级表、学生表、课程表和成绩表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
drop database if exists db_school;
create database db_school;

# 班级表
use db_school;
create table tb_class
(
classNo char(6) not null primary key,
className varchar(20) not null,
department varchar(30) not null,
grade smallint,
classNum tinyint,
constraint UQ_class unique (className)
)engine = InnoDB;

# 学生表
use db_school;
create table tb_student
(
studentNo char(10) not null,
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
constraint PK_student primary key (studentNo),
constraint FK_student foreign key (classNo)
references tb_class(classNo)
)engine = InnoDB;

# 课程表tb_course的实现
use db_school;
create table tb_course
(
courseNo char(6) not null,
courseName varchar(20) not null,
credit int not null,
courseHour int not null,
Iterm char(2),
priorCourse char(6),
constraint PK_course primary key (courseNo),
constraint FK_course foreign key (priorCourse)
references tb_course(courseNo),
constraint CK_course check (credit = courseHour / 16)
)engine = InnoDB;

# 成绩表
use db_school;
create table tb_score
(
studentNo char(10) not null,
courseNo char(6) not null,
score float check (score >= 0 and score <= 100),
constraint PK_score primary key (studentNo, courseNo),
constraint FK_score1 foreign key (studentNo)
references tb_student(studentNo),
constraint FK_score2 foreign key (courseNo)
references tb_course(courseNo)
)engine = InnoDB;

# 插入数据
insert into db_school.tb_class
values
('AC1301', '会计13-1班', '会计学院', 2013, 35),
('AC1302', '会计13-2班', '会计学院', 2013, 35),
('CS1401', '计算机14-1班', '计算机学院', 2014, 35),
('IS1301', '信息系统13-1班', '信息学院', 2013, null),
('IS1401', '信息系统14-1班', '信息学院', null, 30);

insert into db_school.tb_student
values
('2013110101', '张晓勇', '男', '1997-12-11', '山西', default, 'AC1301'),
('2013110103', '王一敏', '女', '1996-03-25', '河北', default, 'AC1301'),
('2013110201', '江山', '女', '1996-03-25', '内蒙古', '锡伯', 'AC1302'),
('2013110202', '李明', '男', '1996-01-14', '广西', '壮', 'AC1302'),
('2013310101', '黄菊', '女', '1995-09-30', '北京', default, 'IS1301'),
('2013310103', '吴昊', '男', '1995-11-08', '河北', default, 'IS1301'),
('2014210101', '刘涛', '男', '1997-04-03', '湖南', '侗', 'CS1401'),
('2014210102', '郭志坚', '男', '1997-02-21', '上海', default, 'CS1401'),
('2014310101', '王林', '男', '1996-10-09', '河南', default, 'IS1401'),
('2014310102', '李怡然', '女', '1996-12-31', '辽宁', default, 'IS1401');

insert into db_school.tb_course
values
('11003', '管理学', 2, 32, 2, null),
('11005', '会计学', 3, 48, 3, null),
('21001', '计算机基础', 3, 48, 1, null),
('21002', 'OFFICE高级应用', 3, 48, 2, '21001'),
('21004', '程序设计', 4, 64, 2, '21001'),
('21005', '数据库', 4, 64, 4, '21004'),
('21006', '操作系统', 4, 64, 5, '21001'),
('31001', '管理信息系统', 3, 48, 3, 21004),
('31002', '信息系统_分析与设计', 2, 32, 4, '31001'),
('31005', '项目管理', 3, 48, 5, '31001');

insert into db_school.tb_score
values
('2013110101', '11003', 90),
('2013110101', '21001', 86),
('2013110103', '11003', 89),
('2013110103', '21001', 88),
('2013110201', '11003', 78),
('2013110201', '21001', 92),
('2013110202', '11003', 82),
('2013110202', '21001', 85),
('2013310101', '21004', 83),
('2013310101', '31002', 68),
('2013310103', '21004', 80),
('2013310103', '31002', 76),
('2014210101', '21002', 93),
('2014210101', '21004', 89),
('2014210102', '21002', 95),
('2014210102', '21004', 88),
('2014310101', '21001', 79),
('2014310101', '21004', 80),
('2014310102', '21001', 91),
('2014310102', '21004', 87);

应用系统的编程

由于本实例系统的最终运行和应用都是基于B/S结构,因此本实例系统的开发与实现将采用下图所示的三层软件架构:

上图所示的三层软件体系架构由表示成、应用层和数据层煮成。其中,表示层是本实例系统的用户接口,具体表现为Web页面,其主要使用HTML标签语言实现;应用层是本实例系统的功能层,表现为应用服务器,位于表示层和数据层之间,主要负责具体的业务逻辑处理,以及表示层、数据层的信息交互,其所处理的各种业务逻辑主要由PHP语言编写的动态脚本来实现;数据层位于本案例系统的最底层,具体表现为MySQL数据库服务器,其主要通过SQL数据库操作语言,负责对MySQL数据库中的数据进行读写管理,以及更新的检索,并与应用层实现数据交互。

本案例所需的代码文件之间的链接关系如下图:

实例系统的主页面设计与实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>学生成绩管理系统实例</title>
</head>

<body>
<h2>学生管理系统</h2>
<h3>学生管理</h3>
<a href="add_user.php">添加学生</a><br />
<a href="show_user.pnh">查看学生</a>
<h3>班级管理</h3>
<a href="add_dept.php">添加班级</a><br />
<a href="show_dept.php">查看班级</a>
<h3>课程管理</h3>
<a href="add_usergroup.php">添加课程</a><br />
<a href="show_usergroup.php">查看课程</a>
<h3>成绩管理</h3>
<a href="add_fun.php">添加成绩</a><br />
<a href="show_fun.php">查看成绩</a>
</body>
</html>

公共代码模块的设计与实现

连接数据库、选择数据库和设置字符集三个部分为公共部分,可以单独写给common.php:

1
2
3
4
5
6
7
8
9
10
11
12
13
<?php
/**
* Created by PhpStorm.
* User: mr.cheng
* Date: 2018/9/7
* Time: 15:11
*/
$con = mysqli_connect("localhost:3306", "root", "12345")
or die("<font color='red'>🙅🙅数据库服务器连接失败!</font><br>");
mysqli_select_db($con, "db_school")
or die("<font color='red'>🙅🙅数据库选择失败!</font><br>");
mysqli_query($con, "set names utf8");
?>

效果:

添加学生页面设计与实现

通过该页面,可以向学生管理系统添加一位新的学生个人信息。
添加学生的Web页面文件add_student.php的实现代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset='UTF-8'">
<title>添加学生</title>
</head>
<?php require "common.php"; ?>

<body>
<h3>添加学生</h3>
<form id = "add_student" name = "add_student" method = "post" action="insert_student.php">
学生学号:<input type="text" name="studentNo" /><br />
学生姓名:<input type="text" name="studentName" /><br />
学生性别:<input type="text" name="sex" /><br />
学生年龄:<input type="text" name="birthday" /><br />
学生籍贯:<input type="text" name="native" /><br />
学生民族:<input type="text" name="nation" /><br />
所属班级:<select name="classNo">
<?php
$sql = "select * from tb_class";
$result = mysqli_query($con, $sql);
while($rows = mysqli_fetch_row($result)){
echo "<option value=".$rows[0].">".$rows[1]."</option>";
}
?>
</select><br/>
<input type="submit" value="添加" />
</form>
</body>
</html>

效果:

插入学生部分代码的实现

insert_student.php部分的代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
/**
* Created by PhpStorm.
* User: mr.cheng
* Date: 2018/9/7
* Time: 15:42
*/
require "common.php";
$studentNo = $_POST['studentNo'];
$studentName = $_POST['studentName'];
$sex = $_POST['sex'];
$birthday = $_POST['birthday'];
$native = $_POST['native'];
$nation = $_POST['nation'];
$classNo = $_POST['classNo'];
$sql = "insert into tb_student(studentNo, studentName, sex, birthday, native, nation, classNo)";
$sql = $sql." values('".$studentNo."','".$studentName."','".$sex.
"','".$birthday."','".$native."','".$nation."','".$classNo."')";
if(mysqli_query($con, $sql)){
echo "学生添加成功!<br>";
}
else echo "学生添加失败!<br>";
?>

把我添加进去试试:

提交之后即可看到学生添加成功!

查看学生的页面设计与实现

通过该页面可以通过指定学生姓名或学生所属班级来查看该学生的全部个人信息。
查看学生的Web 页面文件show_student.php的实现代码描述如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<html>
<head>
<title>查看学生的详细信息</title>
<meta http-equiv="content-type" content="text/html; charset = 'UTF-8'">
</head>

<body>
<h3>查看学生的详细信息</h3>
<form id="show_student" name="show_student" method="post" action="select_student.php">
学生姓名:<input type="text" name="show_student_name" /><br />
所属班级:<select name="show_student_class">
<option value = 0>所有班级</option>
<?php
require "common.php";
$sql = "Select * from tb_class";
$result = mysqli_query($con, $sql);
while($rows = mysqli_fetch_row($result)){
echo "<option values = ".$rows[0].">".$rows[1]."</option>";
}
?>
</select><br>
<br>
<input type="submit" value="查看"/>
</form>
</body>
</html>

效果:

select_student.php部分的代码为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<?php
/**
* Created by PhpStorm.
* User: mr.cheng
* Date: 2018/9/7
* Time: 16:20
*/
require "common.php";
$studentName = trim($_POST['show_student_name']);
$classNo = trim($_POST['show_student_class']);
$sql = "select * from tb_student where studentName='";
if($classNo==0)
$sql = $sql.$studentName."'";
else
$sql = $sql.$studentName."' and classNo=".$classNo;
$result = mysqli_query($con, $sql);
$array = mysqli_fetch_array($result, MYSQLI_NUM);
$studentNo = $array[0];
$class_sql = "select * from tb_class where classNo=";
$class_sql = $class_sql.$classNo;
$class_result = mysqli_query($con, $class_sql);
$class_array = mysqli_fetch_array($class_result, MYSQLI_NUM);
$class_name = $class_array[1];
echo "学生学号:";
echo $array[0];
echo "<br>";
echo "学生姓名:";
echo $array[1];
echo "<br>";
echo "学生性别:";
echo $array[2];
echo "<br>";
echo "出生日期:";
echo $array[3];
echo "<br>";
echo "籍贯:";
echo $array[4];
echo "<br>";
echo "民族:";
echo $array[5];
echo "<br>";
echo "所属班级:";
echo $class_name;
echo "<br>";
?>
<a href="<?php echo 'change_student.php?num='.$studentNo ?>">修改学生信息</a>
<br/>
<a href="<?php echo 'delete_student.php?num='.$studentNo ?>">删除这个调皮的学生!</a>

修改学生的页面设计与实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset='UTF-8'">
<title>修改学生</title>
</head>
<body>
<h3>修改学生信息</h3>
<?php
require_once "common.php";
$studentNo = trim($_GET['num']);
echo "<form id='add_student' name='add_student' method='post' action='update_student.php?studentNo=$studentNo'>";
echo "<input type='text' name='studentNo' value='$studentNo' /><br>";
echo "学生姓名:<input type='text' name='studentName'><br>";
echo "学生性别:<input type='text' name='sex'><br>";
echo "出生日期:<input type='text' name='birthday'><br>";
echo "学生籍贯:<input type='text' name='native'><br>";
echo "学生民族:<input type='text' name='nation'><br>";
echo "所属班级:<select name='classNo'>";
$sql = "select * from tb_class";
$result = mysqli_query($con, $sql);
while($rows = mysqli_fetch_row($result)){
echo "<option value=".$rows[0].">".$rows[1]."</option>";
}
echo "</select><br>";
echo "<input type='submit' value='确认修改该学生的信息'>";
echo "</form>";
?>
</body>
</html>

update_student.php部分的代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
/**
* Created by PhpStorm.
* User: mr.cheng
* Date: 2018/9/7
* Time: 16:57
*/
require_once "common.php";
$studentNo = trim($_GET['studentNo']);
$studentName = trim($_POST['studentName']);
$classNo = trim($_POST['classNo']);
$sex = trim($_POST['sex']);
$birthday = trim($_POST['birthday']);
$native = trim($_POST['native']);
$nation = trim($_POST['nation']);
$sql = "update tb_student set studentName ='".$studentName."', sex = '".$sex.
"', birthday ='".$birthday."', native ='".$native."', nation='".$nation.
"', classNo='".$classNo."' where studentNo='";
$sql = $sql.$studentNo."'";
if(mysqli_query($con, $sql))
echo "学生信息修改成功!";
else
echo "学生信息修改失败!";

?>

删除学生的底层代码实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php require_once "common.php"; ?>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset='UTF-8'">
<title>删除学生</title>
</head>
<body>
<?php
$studentNo = trim($_GET['num']);
$sql = "delete from tb_student where studentNo='";
$sql = $sql.$studentNo."'";
if(mysqli_query($con, $sql))
echo "学生删除成功!<br>";
else
echo "学生删除失败!<br>";
?>
</body>
</html>

终于基本抄完了这本书!🎉🎉🎉

# MySQL, PHP

评论

程振兴

程振兴 @czxa.top
截止今天,我已经在本博客上写了659.4k个字了!

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×