前端使用html，后台使用mysql数据库，使用php处理数据库，完成数据库课程的学籍管理系统大作业。
特此记录，有机会的话还是希望学习一番全栈的内容。
别的班好像做的都绘声绘色的，我们班验收者则寥寥，我太菜了
前台html服务
写的非常丑陋，很多冗余的代码，自己确实完全是个门外汉qaq
没有css 我不会
mysql.html
html
1<html>
2<head>
3 <meta charset="utf-8">
4 <title> 数据库大作业</title>
5</head>
6
7<center>
8<h1> 学籍管理系统</h1>
9<br><br>
10<a href="fun0.html"> <h3> admin</h3></a>
11
12
13<a href="fun1.html"> <h3> 功能1: 录入学生基本信息 </h3></a>
14<a href="fun2.html"><h3> 功能2: 录入学生课程成绩</h3></a>
15<a href="fun3.html"><h3> 功能3: 三种方式查询学生基本信息</h3></a>
16<a href="fun4.html"><h3> 功能4: 查询学生课程信息</h3></a>
17<a href="fun5.html"><h3> 功能5: 查询学生被哪些老师教过课</h3></a>
18<a href="fun6.html"><h3> 功能6: 查询快要被开除的学生</h3></a>
19
20</center>
21</html>fun0.html
html
1<html>
2<head>
3 <meta charset="utf-8">
4 <title> 数据库大作业</title>
5</head>
6
7<!-- <script type="text/javascript" src="../db.js"></script> -->
8
9<center>
10 <a href="mysql.html"><h1> 学籍管理系统</h1> </a>
11</center><br><br>
12
13<h3 style="margin-left: 2em"> 显示所有表的信息: </h3>
14<form style="margin-left: 4em" action="mysql.php" method="post">
15 <input type="hidden" value="QUERY" name="op">
16 <input type="submit" value="Query!">
17</form>
18
19<h3 style="margin-left: 2em"> 执行一堆sql语句 </h3>
20<form style="margin-left: 4em" action="mysql.php" method="post">
21 <input type="hidden" value="DO" name="op"> <br>
22 <input style="height: 10em; width: 50em" type="text" name="sql"> <br><br>
23 <input type="submit" value="Submit!">
24</form>
25
26
27</html>fun1.html
html
1<html>
2<head>
3 <meta charset="utf-8">
4 <title> 数据库大作业</title>
5</head>
6
7<!-- <script type="text/javascript" src="../db.js"></script> -->
8
9<center>
10 <a href="mysql.html"><h1> 学籍管理系统</h1> </a>
11</center><br><br>
12
13<h3 style="margin-left: 2em"> 功能1: 录入学生基本信息 </h3>
14<form style="margin-left: 4em" action="mysql.php" method="post">
15 <input type="hidden" value="1" name="op">
16 姓名：<input type="text" name= "sname"> <br>
17 学号：<input type="text" name= "sno"> <br>
18 性别：<input type="radio" name="ssex" value="male">男 <input type="radio" name="ssex" value="femail">女<br>
19 出生年月：<input type="text" name="sbirthyear">年<space></space><input type="text" name="sbirthmonth">月<br>
20 班级：<input type="text" name= "class"> <br>
21 <br><input type="submit" value="Submit">
22</form>
23
24
25
26</html>fun2.html
html
1<html>
2<head>
3 <meta charset="utf-8">
4 <title> 数据库大作业</title>
5</head>
6
7<!-- <script type="text/javascript" src="../db.js"></script> -->
8
9<center>
10 <a href="mysql.html"><h1> 学籍管理系统</h1> </a>
11</center><br><br>
12
13<h3 style="margin-left: 2em"> 功能2: 录入学生课程成绩 </h3>
14<form style="margin-left: 4em" action="mysql.php" method="post">
15 <input type="hidden" value="2" name="op">
16 学号：<input type="text" name= "sno"> <br>
17 课程名：<input type="text" name= "cname"> <br>
18 成绩：<input type="text" name= "grade"> <br>
19 是否是补考？是<input type="radio" name="flag" value="1"> 否 <input type="radio" name="flag" value="0" checked> <br>
20 <br><input type="submit" value="提交">
21</form>
22
23
24</html>fun3.html
html
1<html>
2<head>
3 <meta charset="utf-8">
4 <title> 数据库大作业</title>
5</head>
6
7<!-- <script type="text/javascript" src="../db.js"></script> -->
8
9<center>
10 <a href="mysql.html"><h1> 学籍管理系统</h1> </a>
11</center><br><br>
12
13<h3 style="margin-left: 2em"> 功能3.1: 按照学号查询学生信息 </h3>
14<form style="margin-left: 4em" action="mysql.php" method="post">
15 <input type="hidden" value="31" name="op">
16 <input type="text" name="sno"><br><br>
17 <input type="submit" value="提交">
18</form><br><br>
19<h3 style="margin-left: 2em"> 功能3.2: 按照姓名查询学生信息 </h3>
20<form style="margin-left: 4em" action="mysql.php" method="post">
21 <input type="hidden" value="32" name="op">
22 <input type="text" name="sname"><br><br>
23 <input type="submit" value="提交">
24</form><br><br>
25<h3 style="margin-left: 2em"> 功能3.3: 按照专业查询学生信息 </h3>
26<form style="margin-left: 4em" action="mysql.php" method="post">
27 <input type="hidden" value="33" name="op">
28 <input type="text" name="major"><br><br>
29 <input type="submit" value="提交">
30</form>
31
32
33
34</html>fun4.html
html
1<html>
2<head>
3 <meta charset="utf-8">
4 <title> 数据库大作业</title>
5</head>
6
7<!-- <script type="text/javascript" src="../db.js"></script> -->
8
9<center>
10 <a href="mysql.html"><h1> 学籍管理系统</h1> </a>
11</center><br><br>
12
13<h3 style="margin-left: 2em"> 功能4: 查询学生课程成绩 </h3>
14<form style="margin-left: 4em" action="mysql.php" method="post">
15 <input type="hidden" value="4" name="op">
16 学号：<input type="text" name= "sno"> <br><br>
17 <input type="submit" value="Submit">
18</form>
19
20
21
22</html>fun5.html
html
1<html>
2<head>
3 <meta charset="utf-8">
4 <title> 数据库大作业</title>
5</head>
6
7<!-- <script type="text/javascript" src="../db.js"></script> -->
8
9<center>
10 <a href="mysql.html"><h1> 学籍管理系统</h1> </a>
11</center><br><br>
12
13<h3 style="margin-left: 2em"> 功能5: 查询学生被哪些老师教过课 </h3>
14<form style="margin-left: 4em" action="mysql.php" method="post">
15 <input type="hidden" value="5" name="op">
16 学号：<input type="text" name= "sno"> <br><br>
17
18 <input type="submit" value="提交">
19</form>
20
21
22
23</html>fun6.html
html
1<html>
2<head>
3 <meta charset="utf-8">
4 <title> 数据库大作业</title>
5</head>
6
7<!-- <script type="text/javascript" src="../db.js"></script> -->
8
9<center>
10 <a href="mysql.html"><h1> 学籍管理系统</h1> </a>
11</center><br><br>
12
13<h3 style="margin-left: 2em"> 功能6: 查询所有将要/已经被开除的学生信息 </h3>
14<form style="margin-left: 4em" action="mysql.php" method="post">
15 <input type="hidden" value="6" name="op">
16 <input type="submit" value="查询">
17</form>
18
19
20
21</html>后台php服务
会被sql注入，容易遭到恶意攻击，安全性为零。
特地写了cpp程序来生成插入、查询、绘表等操作指令。
不知道有没有别的什么办法来弄的简洁漂亮一些orz
自己对于后端也是啥也不懂qaq
mysql.php
php
1<?php
2echo "<center> <a href=\"mysql.html\"><h1> 学籍管理系统</h1> </a></center><br><br>";
3
4$conn = mysqli_connect("localhost:3306", "root", "xxxx");
5if(!$conn) die("连接数据库失败: " . mysqli_error($conn));
6if(!mysqli_query($conn, "use db")) die("选择数据库失败: " . mysqli_error($conn));
7
8$op = $_POST['op'];
9//查询所有表的所有信息。
10if($op == 'QUERY'){
11
12 echo '<center>';
13 echo '<h2> 查询结果如下所示: <br><br></h2>';
14
15 $res = mysqli_query($conn, "select * from classinfo");
16 if(!$res) die("无法读取数据: " . mysqli_error($conn));
17 echo '<h4> 班级--专业表classinfo<br></h4>';
18 echo '<table border="1"><tr><td>class</td><td>major</td></tr>';
19 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
20 echo "<tr><td>{$row['class']}</td><td>{$row['major']}</td></tr>";
21 }
22 echo '</table><br><br>';
23
24 $res = mysqli_query($conn, "select * from course");
25 if(!$res) die("无法读取数据: " . mysqli_error($conn));
26 echo '<h4> 课程--学分表course <br></h4>';
27 echo '<table border="1"><tr><td>cname</td><td>ccre</td></tr>';
28 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
29 echo "<tr><td>{$row['cname']}</td><td>{$row['ccre']}</td></tr>";
30 }
31 echo '</table><br><br>';
32
33 $res = mysqli_query($conn, "select * from stu");
34 if(!$res) die("无法读取数据: " . mysqli_error($conn));
35 echo '<h4> 学生信息表stu <br></h4>';
36 echo '<table border="1"><tr><td>sno</td><td>sname</td><td>ssex</td><td>sbirthyear</td><td>sbirthmonth</td><td>class</td></tr>';
37 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
38 echo "<tr><td>{$row['sno']}</td><td>{$row['sname']}</td><td>{$row['ssex']}</td><td>{$row['sbirthyear']}</td><td>{$row['sbirthmonth']}</td><td>{$row['class']}</td></tr>";
39 }
40 echo '</table><br><br>';
41
42 $res = mysqli_query($conn, "select * from schedule");
43 if(!$res) die("无法读取数据: " . mysqli_error($conn));
44 echo '<h4> 培养方案表schedule <br></h4>';
45 echo '<table border="1"><tr><td>major</td><td>cname</td><td>iscompulsory</td><td>cdate</td></tr>';
46 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
47 echo "<tr><td>{$row['major']}</td><td>{$row['cname']}</td><td>{$row['iscompulsory']}</td><td>{$row['cdate']}</td></tr>";
48 }
49 echo '</table><br><br>';
50
51 $res = mysqli_query($conn, "select * from teaching");
52 if(!$res) die("无法读取数据: " . mysqli_error($conn));
53 echo '<h4> 带课情况表teaching<br></h4>';
54 echo '<table border="1"><tr><td>class</td><td>cname</td><td>tname</td></tr>';
55 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
56 echo "<tr><td>{$row['class']}</td><td>{$row['cname']}</td><td>{$row['tname']}</td></tr>";
57 }
58 echo '</table><br><br>';
59
60 $res = mysqli_query($conn, "select * from sc");
61 if(!$res) die("无法读取数据: " . mysqli_error($conn));
62 echo '<h4> 选课成绩表teaching<br></h4>';
63 echo '<table border="1"><tr><td>sno</td><td>cname</td><td>grade</td><td>是否是补考成绩</td></tr>';
64 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
65 echo "<tr><td>{$row['sno']}</td><td>{$row['cname']}</td><td>{$row['grade']}</td><td>{$row['flag']}</td></tr>";
66 }
67 echo '</table><br><br>';
68
69 echo '</center>';
70}
71else if($op == 'DO'){
72 $sql = $_POST['sql'];
73 // if(!mysqli_query($conn, $sql)) die("DO失败: " . mysqli_error($conn));
74 if(!mysqli_multi_query($conn, $sql)) die("DO失败: " . mysqli_error($conn));
75}
76else if($op == '1'){
77 $sno = $_POST['sno'];
78 $sname = $_POST['sname'];
79 $ssex = $_POST['ssex'];
80 $sbirthyear = $_POST['sbirthyear'];
81 $sbirthmonth = $_POST['sbirthmonth'];
82 $class = $_POST['class'];
83
84 $sql = "insert into stu (sno, sname, ssex, sbirthyear, sbirthmonth, class) values(
85 '" .$sno ."',
86 '" .$sname ."',
87 '" .$ssex ."',
88 '" .$sbirthyear ."',
89 '" .$sbirthmonth ."',
90 '" .$class ."'
91 )";
92 if(!mysqli_query($conn, $sql)) die("操作1执行失败，无法插入: " . mysqli_error($conn));
93 // echo $sql;
94}
95else if($op == '2'){
96 $sno = $_POST['sno'];
97 $cname = $_POST['cname'];
98 $grade = $_POST['grade'];
99 $flag =$_POST['flag'];
100
101 $fail = 0;
102 if($flag == '1'){
103 $sql = "select * from sc where sno = '" .$sno ."' AND cname = '" .$cname ."'";
104 $res = mysqli_query($conn, $sql);
105 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
106 if($row['flag'] == 1){
107 $fail = 1;
108 break;
109 }
110 if($row['grade'] > $grade){
111 $fail = 2;
112 break;
113 }
114 }
115 if($fail == 1) die("<center>该学生已经补考过一次, 不得再次补考!</center>");
116 if($fail == 2) die("<center>该学生补考成绩不如正式考试!不予修改!</center>");
117
118 $sql = "update sc
119 set grade = '" .$grade ."',
120 flag = '1'
121 where sno = '" .$sno ."'
122 AND cname = '" .$cname ."'
123 ";
124 // echo $sql. '<br>';
125 }
126 else{
127 $sql = "insert into sc (sno, cname, grade, flag) values(
128 '" .$sno ."',
129 '" .$cname ."',
130 '" .$grade ."',
131 '" .$flag . "'
132 )";
133 }
134
135
136
137
138 if(!mysqli_query($conn, $sql)) die("插入成绩失败: " . mysqli_error($conn));
139}
140else if($op == '31'){
141 $sno = $_POST['sno'];
142 $sql = "select * from stu where sno='" .$sno ."'";
143 $res = mysqli_query($conn, $sql);
144
145 echo '<center>';
146 if(!$res) die("无法读取数据: " . mysqli_error($conn));
147 echo '<h4> 学生信息表stu <br></h4>';
148 echo '<table border="1"><tr><td>学号</td><td>学生姓名</td><td>性别</td><td>出生年份</td><td>出生月份</td><td>班级</td></tr>';
149 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
150 echo "<tr><td>{$row['sno']}</td><td>{$row['sname']}</td><td>{$row['ssex']}</td><td>{$row['sbirthyear']}</td><td>{$row['sbirthmonth']}</td><td>{$row['class']}</td></tr>";
151 }
152 echo '</table><br><br></center>';
153}
154else if($op == '32'){
155 $sname = $_POST['sname'];
156 $sql = "select * from stu where sname='" .$sname ."'";
157 $res = mysqli_query($conn, $sql);
158
159 echo '<center>';
160 if(!$res) die("无法读取数据: " . mysqli_error($conn));
161 echo '<h4> 学生信息表stu <br></h4>';
162 echo '<table border="1"><tr><td>学号</td><td>学生姓名</td><td>性别</td><td>出生年份</td><td>出生月份</td><td>班级</td></tr>';
163 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
164 echo "<tr><td>{$row['sno']}</td><td>{$row['sname']}</td><td>{$row['ssex']}</td><td>{$row['sbirthyear']}</td><td>{$row['sbirthmonth']}</td><td>{$row['class']}</td></tr>";
165 }
166 echo '</table><br><br></center>';
167}
168else if($op == '33'){
169 $major = $_POST['major'];
170 $sql = "select * from stu, classinfo where stu.class=classinfo.class AND classinfo.major = '" .$major. "'";
171 $res = mysqli_query($conn, $sql);
172
173 echo '<center>';
174 if(!$res) die("无法读取数据: " . mysqli_error($conn));
175 echo '<h4> 学生信息表stu <br></h4>';
176 echo '<table border="1"><tr><td>学号</td><td>学生姓名</td><td>性别</td><td>出生年份</td><td>出生月份</td><td>班级</td></tr>';
177 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
178 echo "<tr><td>{$row['sno']}</td><td>{$row['sname']}</td><td>{$row['ssex']}</td><td>{$row['sbirthyear']}</td><td>{$row['sbirthmonth']}</td><td>{$row['class']}</td></tr>";
179 }
180 echo '</table><br><br></center>';
181}
182else if($op == '4'){
183 $sno = $_POST['sno'];
184 $sql="select * from temp where sno='" .$sno."'";
185 $res = mysqli_query($conn, $sql);
186
187 echo '<center>';
188 if(!$res) die("无法读取数据: " . mysqli_error($conn));
189 echo '<h4> 学生成绩单 <br></h4>';
190 echo '<table border="1"><tr><td>课程名</td><td>是否必修</td><td>课程开课时间</td><td>课程学分</td><td>课程成绩</td><td>是否补考</td></tr>';
191 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
192 $temp = "<tr><td>{$row['cname']}</td> <td>";
193 if($row['iscompulsory'] == '1') $temp .= "是";
194 else $temp .= "否";
195 $temp .= "</td><td>{$row['cdate']}</td><td>{$row['ccre']}</td><td>{$row['grade']}</td><td>";
196 if($row['flag'] == 1) $temp .= "是";
197 else $temp .= "否";
198 $temp .= "</td></tr>";
199 echo $temp;
200 }
201 echo '</table>';
202
203 $sql=
204 "select sno, sum(ccre * grade)/sum(ccre) aver
205 from temp
206 where iscompulsory='1' AND sno='" .$sno ."'";
207 $res = mysqli_query($conn, $sql);
208
209 echo '<center>';
210 if(!$res) die("无法读取数据: " . mysqli_error($conn));
211 echo '<br>';
212 echo '<table border="1"><tr><td>学号</td><td>必修平均分</td></tr>';
213 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
214 echo "<tr><td>{$row['sno']}</td><td>{$row['aver']}</td></tr>";
215 }
216 echo '</table>';
217
218
219 $sql=
220 "select sno, sum(ccre * grade)/sum(ccre) aver
221 from temp
222 where sno='" .$sno ."'";
223 $res = mysqli_query($conn, $sql);
224
225 echo '<center>';
226 if(!$res) die("无法读取数据: " . mysqli_error($conn));
227 echo '<br>';
228 echo '<table border="1"><tr><td>学号</td><td>总平均分</td></tr>';
229 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
230 echo "<tr><td>{$row['sno']}</td><td>{$row['aver']}</td></tr>";
231 }
232 echo '</table>';
233
234 echo '</center>';
235}
236else if($op == '5'){
237 $sno = $_POST['sno'];
238 $sql =
239 "select teaching.tname, teaching.cname
240 from stu, teaching, sc
241 where stu.class=teaching.class AND stu.sno=sc.sno AND sc.cname=teaching.cname
242 AND stu.sno='" .$sno."'";
243 $res = mysqli_query($conn, $sql);
244
245 echo '<center>';
246 if(!$res) die("无法读取数据: " . mysqli_error($conn));
247 echo '<h4> 学生-教师表stu <br></h4>';
248
249 echo '<table border="1"><tr><td>教师姓名</td><td>课程名</td></tr>';
250 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
251 echo "<tr><td>{$row['tname']}</td><td>{$row['cname']}</td></tr>";
252 }
253 echo '</table></center>';
254
255}
256else if($op == '6'){
257 $sno = $_POST['sno'];
258 $sql=
259 "select temp.sno , stu.sname, sum(temp.ccre) sumcre
260 from temp, stu
261 where temp.iscompulsory='1' AND temp.grade < 60 AND temp.sno=stu.sno
262 group by temp.sno
263 having sum(temp.ccre) > 7";
264 $res = mysqli_query($conn, $sql);
265
266 echo '<center>';
267 if(!$res) die("无法读取数据: " . mysqli_error($conn));
268 echo '<h4> 学生开除表(必修)<br></h4>';
269 echo '<table border="1"><tr><td>学号</td><td>学生姓名</td><td>不及格学分</td></tr>';
270 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
271 echo "<tr><td>{$row['sno']}</td><td>{$row['sname']}</td><td>{$row['sumcre']}</td></tr>";
272 }
273 echo '</table>';
274
275
276 $sql=
277 "select temp.sno , stu.sname, sum(temp.ccre) sumcre
278 from temp, stu
279 where temp.iscompulsory='0' AND temp.grade < 60 AND temp.sno=stu.sno
280 group by temp.sno
281 having sum(temp.ccre) > 12";
282 $res = mysqli_query($conn, $sql);
283 echo '<center>';
284 if(!$res) die("无法读取数据: " . mysqli_error($conn));
285 echo '<h4> 学生开除表(选修)<br></h4>';
286 echo '<table border="1"><tr><td>学号</td><td>学生姓名</td><td>不及格学分</td></tr>';
287 while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
288 echo "<tr><td>{$row['sno']}</td><td>{$row['sname']}</td><td>{$row['sumcre']}</td></tr>";
289 }
290 echo '</table>';
291
292
293}
294else die("请求的操作码有误");
295$conn->close();
296echo "<center><br>" . "完成". "<br></center>";
297
298?>