2020数据库实验



 2021.01.15 23:04  2023.05.29 23:05  数据库  SQL  前后端 󰈭 5228字

前端使用html，后台使用mysql数据库，使用php处理数据库，完成数据库课程的学籍管理系统大作业。

特此记录，有机会的话还是希望学习一番全栈的内容。

~~别的班好像做的都绘声绘色的，我们班验收者则寥寥，我太菜了~~

前台html服务

写的非常丑陋，很多冗余的代码，自己确实完全是个门外汉qaq

~~没有css 我不会~~

mysql.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

 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

 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

 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

 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

 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

 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

 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

  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?>