目录
技术栈
前端运用html+css,后端为pthon中的flsak框架,数据库为mysql
数据库
1.首先在navicat中连接上mysql,然后新建数据库student
2.新建表admins,用于储存管理员姓名(admin_name)和密码(admin_password)
3.新建表grade_infos,用于储存学号(student_id)、课程号(student_class_id)、成绩(grade)
4.新建表student_decision_infos,用于存储学号(student_id)、课程号(student_class_id)、教师编号(teacher_id)
5.新建表student_infos,用于存储存储学号(student_id)、班级(student_class)、学生姓名(student_name)
/*
Navicat Premium Data Transfer
Source Server : text
Source Server Type : MySQL
Source Server Version : 50740
Source Host : localhost:3306
Source Schema : student
Target Server Type : MySQL
Target Server Version : 50740
File Encoding : 65001
Date: 14/12/2022 22:37:21
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for admins
-- ----------------------------
DROP TABLE IF EXISTS `admins`;
CREATE TABLE `admins` (
`id` int(11) NOT NULL,
`admin_name` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`admin_password` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of admins
-- ----------------------------
INSERT INTO `admins` VALUES (1, 'admin', '123456');
-- ----------------------------
-- Table structure for grade_infos
-- ----------------------------
DROP TABLE IF EXISTS `grade_infos`;
CREATE TABLE `grade_infos` (
`student_id` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`student_class_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`grade` tinyint(4) NULL DEFAULT NULL,
PRIMARY KEY (`student_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of grade_infos
-- ----------------------------
INSERT INTO `grade_infos` VALUES ('1001', 'g687', 96);
INSERT INTO `grade_infos` VALUES ('1002', 'g687', 67);
INSERT INTO `grade_infos` VALUES ('1003', 'g687', 96);
INSERT INTO `grade_infos` VALUES ('1004', 'g688', 96);
INSERT INTO `grade_infos` VALUES ('1005', 'g688', 76);
INSERT INTO `grade_infos` VALUES ('1006', 'g112', 78);
INSERT INTO `grade_infos` VALUES ('1007', 'g112', 78);
INSERT INTO `grade_infos` VALUES ('1008', 'g688', 76);
INSERT INTO `grade_infos` VALUES ('1009', 'g687', 67);
INSERT INTO `grade_infos` VALUES ('1010', 'g688', 78);
INSERT INTO `grade_infos` VALUES ('1011', 'g688', 96);
INSERT INTO `grade_infos` VALUES ('1012', 'g687', 67);
INSERT INTO `grade_infos` VALUES ('1013', 'g687', 78);
INSERT INTO `grade_infos` VALUES ('1014', 'g687', 78);
INSERT INTO `grade_infos` VALUES ('1015', 'g687', 78);
-- ----------------------------
-- Table structure for students_decision_infos
-- ----------------------------
DROP TABLE IF EXISTS `students_decision_infos`;
CREATE TABLE `students_decision_infos` (
`student_id` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`student_class_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`teacher_id` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`student_id`) USING BTREE,
CONSTRAINT `id` FOREIGN KEY (`student_id`) REFERENCES `students_infos` (`student_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students_decision_infos
-- ----------------------------
INSERT INTO `students_decision_infos` VALUES ('1001', 'g687', 't001');
INSERT INTO `students_decision_infos` VALUES ('1002', 'g687', 't001');
INSERT INTO `students_decision_infos` VALUES ('1003', 'g688', 't002');
INSERT INTO `students_decision_infos` VALUES ('1004', 'g688', 't001');
INSERT INTO `students_decision_infos` VALUES ('1005', 'g789', 't003');
INSERT INTO `students_decision_infos` VALUES ('1006', 'g789', 't003');
INSERT INTO `students_decision_infos` VALUES ('1007', 'g789', 't003');
INSERT INTO `students_decision_infos` VALUES ('1008', 'g688', 't002');
INSERT INTO `students_decision_infos` VALUES ('1009', 'g789', 't002');
INSERT INTO `students_decision_infos` VALUES ('1010', 'g687', 't002');
INSERT INTO `students_decision_infos` VALUES ('1011', 'g687', 't001');
INSERT INTO `students_decision_infos` VALUES ('1012', 'g687', 't003');
-- ----------------------------
-- Table structure for students_infos
-- ----------------------------
DROP TABLE IF EXISTS `students_infos`;
CREATE TABLE `students_infos` (
`student_id` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`student_class` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`student_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`student_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students_infos
-- ----------------------------
INSERT INTO `students_infos` VALUES ('1001', '1', 'zs');
INSERT INTO `students_infos` VALUES ('1002', '1', 'ls');
INSERT INTO `students_infos` VALUES ('1003', '1', 'jhy');
INSERT INTO `students_infos` VALUES ('1004', '1', 'sdf');
INSERT INTO `students_infos` VALUES ('1005', '1', 'jyu');
INSERT INTO `students_infos` VALUES ('1006', '1', 'syh');
INSERT INTO `students_infos` VALUES ('1007', '2', 'kjy');
INSERT INTO `students_infos` VALUES ('1008', '2', 'dty');
INSERT INTO `students_infos` VALUES ('1009', '2', 'dht');
INSERT INTO `students_infos` VALUES ('1010', '2', 'der');
INSERT INTO `students_infos` VALUES ('1011', '2', 'fff');
INSERT INTO `students_infos` VALUES ('1012', '2', 'rtf');
INSERT INTO `students_infos` VALUES ('1013', '2', 'jjj');
INSERT INTO `students_infos` VALUES ('1014', '2', 'joi');
INSERT INTO `students_infos` VALUES ('1015', '2', 'ccx');
INSERT INTO `students_infos` VALUES ('1016', '2', 'lik');
INSERT INTO `students_infos` VALUES ('1017', '2', 'ghy');
INSERT INTO `students_infos` VALUES ('1018', '2', 'fgu');
SET FOREIGN_KEY_CHECKS = 1;
前端(html+css)
1.登录页面
login.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>学生成绩管理系统登陆</title>
<link rel="icon" href="../static/images/1.ico">
<style>
li {
list-style: none;
}
body {
margin: 0;
padding: 0;
box-sizing: border-box;
background-image: url(../static/images/bgc.jpg);
/* background-image: url(./static/img/1.jpg); */
background-repeat: no-repeat;
/*这里的100% auto 表示将背景图片的长度100%显示,高度自适应*/
background-size: 100% auto;
}
#maxbox {
margin: 0 auto;
margin-top: 200px;
padding: 20px, 50px;
/*这里的90表示以不透明度90%显示*/
background-color: #00000090;
text-align: center;
width: 600px;
height: 400px;
border-radius: 10px;
}
#maxbox h1 {
padding: 0;
padding-top: 60px;
color: white;
font-size: 30px;
padding-bottom: 4px;
border-bottom: solid 1px white;
}
#maxbox h2 {
font-weight: 700;
}
#maxbox .inputbox {
margin-top: 30px;
}
#maxbox .inputText {
margin-top: 20px;
}
#maxbox .inputText span {
color: white;
font-size: 18px;
}
#maxbox .inputText input {
border: 0;
padding: 6px;
border-bottom: 1px solid white;
/*这里的00表示不透明度为0,即透明显示*/
background-color: #FFFFFF00;
color: white;
}
#maxbox .inputbox .inputButton {
margin: 0;
border: 0;
margin-top: 20px;
width: 145px;
height: 25px;
/*给这个按钮变为圆角边角*/
border-radius: 25px;
color: white;
background-color: #3498db;
}
#sign_up {
margin-top: 50px;
color: white;
font-size: 17px;
}
#sign_up a {
color: #3498db;
}
</style>
</head>
<body>
<div id="maxbox">
<h1>学生成绩管理系统</h1>
<h2>Please sign in</h2>
<div class="inputbox">
<form name="frm" action="" method="post">
<div class="inputText">
<span class="iconfont icon-mine"></span>
<input class="username" type="text" placeholder="admin_name" name="user" style="color:while" />
</div>
<div class="inputText">
<span class="iconfont icon-lock"></span>
<input type="password" placeholder="Password" name="pwd" style="color:white" />
<br>
<input class="remember" name="remember" type="checkbox" value="" checked="checked">
<span style="color:rgb(255, 255, 255)">Remember me</span>
</div>
<input class="inputButton" type="submit" value="Sign in" />
</form>
<div style="color: white">{{msg}}</div>
</div>
</div>
</body>
</html>
2.学生信息录入
student.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>学生成绩管理系统</title>
<link rel="icon" href="../static/images/1.ico">
<style>
* {
margin: 0;
padding: 0;
}
li {
list-style: none;
}
a {
text-decoration: none;
color: white;
}
.header {
position: relative;
width: 100%;
height: 55px;
background-color: black;
}
.left {
position: absolute;
left: 20px;
font-size: 20px;
line-height: 55px;
color: white;
text-align: center;
}
.right {
position: absolute;
right: 160px;
line-height: 55px;
color: white;
text-align: center;
}
.right_right {
position: absolute;
right: 24px;
line-height: 55px;
color: white;
text-align: center;
}
.leftside {
float: left;
background-color: rgb(245, 245, 245);
/* height: 663px; */
width: 230px;
}
.leftside ul {
height: 663px;
}
.leftside .first {
background-color: rgb(66, 139, 202);
margin-top: 25px;
}
.leftside .first a {
color: white;
}
.leftside ul li {
border-bottom: 0.2px solid white;
font-size: 20px;
height: 60px;
line-height: 60px;
width: 100%;
text-align: center;
}
.leftside ul li a {
color: black;
}
.container-fluid {
position: relative;
top: 35px;
left: 70px;
}
/* .sub-header {
margin-top: 35px;
} */
.table-responsive {
margin-top: 10px;
}
/* .table-striped {
width: 1250px;
} */
thead tr th {
background-color: white;
}
tbody tr {
background-color: rgb(245, 245, 245);
}
tbody tr td .long {
width: 270px;
height: 30px;
}
tbody tr td .last {
height: 40px;
width: 60px;
}
</style>
</head>
<body>
<div class="header">
<span class="left">学 生 信 息 录 入</span>
<span class="right">你 好,{{user_info}}管 理 员!</span>
<span class="right_right"><a href="/">退出登陆</a></span>
</div>
<div class="leftside">
<ul>
<li class="first"><a href="/student">学生信息录入</a></li>
<li><a href="/teacher">选课信息录入</a></li>
<li><a href="/grade">成绩信息录入</a></li>
<li><a href="/grade_infos">学生成绩查询</a></li>
<li><a href="/adminstator">系统管理员变动</a></li>
</ul>
</div>
<div class="container-fluid">
<h1 class="sub-header">学 生 信 息 录 入 系 统</h1>
<hr>
<div class="table-responsive">
<table class="table table-striped">
<thead>
<tr>
<th>学号</th>
<th>班级</th>
<th>姓名</th>
<th>#</th>
</tr>
</thead>
<tbody>
<tr>
<form action="" method="post">
<td><input class="long" name="student_id" type="text" /> </td>
<td><input class="long" name="student_class" type="text" />
</td>
<td><input class="long" name="student_name"
type="text" />
</td>
<td><input class="last" type="submit" value="提交" /> </td>
<td><span>提交结果:{{insert_result}}</span></td>
</form>
</tr>
<tr>
<td>学生学号</td>
<td>所属班级</td>
<td>学生姓名</td>
</tr>
{% for result in results %}
<tr>
<td>{{result[0]}}</td>
<td>{{result[1]}}</td>
<td>{{result[2]}}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
</body>
</html>
3. 选课信息录入
teacher.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>学生成绩管理系统</title>
<link rel="icon" href="../static/images/1.ico">
<style>
* {
margin: 0;
padding: 0;
}
li {
list-style: none;
}
a {
text-decoration: none;
color: white;
}
.header {
position: relative;
width: 100%;
height: 55px;
background-color: black;
}
.left {
position: absolute;
left: 20px;
font-size: 20px;
line-height: 55px;
color: white;
text-align: center;
}
.right {
position: absolute;
right: 160px;
line-height: 55px;
color: white;
text-align: center;
}
.right_right {
position: absolute;
right: 24px;
line-height: 55px;
color: white;
text-align: center;
}
.leftside {
float: left;
background-color: rgb(245, 245, 245);
/* height: 663px; */
width: 230px;
}
.leftside ul {
height: 663px;
}
.leftside .first {
background-color: rgb(66, 139, 202);
margin-top: 25px;
}
.leftside .first a {
color: white;
}
.leftside ul li {
border-bottom: 0.2px solid white;
font-size: 20px;
height: 60px;
line-height: 60px;
width: 100%;
text-align: center;
}
.leftside ul li a {
color: black;
}
.container-fluid {
position: relative;
top: 35px;
left: 70px;
}
/* .sub-header {
margin-top: 35px;
} */
.table-responsive {
margin-top: 10px;
}
/* .table-striped {
width: 1250px;
} */
thead tr th {
background-color: white;
}
tbody tr {
background-color: rgb(245, 245, 245);
}
tbody tr td .long {
width: 270px;
height: 30px;
}
tbody tr td .last {
height: 40px;
width: 60px;
}
</style>
</head>
<body>
<div class="header">
<span class="left">选 课 信 息 录 入</span>
<span class="right">你 好,{{user_info}}管 理 员!</span>
<span class="right_right"><a href="/">退出登陆</a></span>
</div>
<div class="leftside">
<ul>
<li><a href="/student">学生信息录入</a></li>
<li class="first"><a href="#">选课信息录入</a></li>
<li><a href="/grade">成绩信息录入</a></li>
<li><a href="/grade_infos">学生成绩查看</a></li>
<li><a href="/adminstator">系统管理员变动</a></li>
</ul>
</div>
<div class="container-fluid">
<h1 class="sub-header">学 生 选 课 信 息 录 入 系 统</h1>
<hr>
<div class="table-responsive">
<table class="table table-striped">
<thead>
<tr>
<th>学号</th>
<th>课程号</th>
<th>教师号</th>
<th>#</th>
</tr>
</thead>
<tbody>
<tr>
<form action="" method="post">
<td><input class="long" name="student_id" type="text" /> </td>
<td><input class="long" name="student_class_id" type="text" />
</td>
<td><input class="long" name="teacher_id"
type="text" />
</td>
<td><input class="last" type="submit" value="提交" /> </td>
<td><span>提交结果:{{insert_result}}</span></td>
</form>
</tr>
<tr>
<td>学生学号</td>
<td>所选课程号</td>
<td>课程教师号</td>
</tr>
{% for result in results %}
<tr>
<td>{{result[0]}}</td>
<td>{{result[1]}}</td>
<td>{{result[2]}}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
</body>
</html>
4.成绩信息录入
grade.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>学生成绩管理系统</title>
<link rel="icon" href="../static/images/1.ico">
<style>
* {
margin: 0;
padding: 0;
}
li {
list-style: none;
}
a {
text-decoration: none;
color: white;
}
.header {
position: relative;
width: 100%;
height: 55px;
background-color: black;
}
.left {
position: absolute;
left: 20px;
font-size: 20px;
line-height: 55px;
color: white;
text-align: center;
}
.right {
position: absolute;
right: 160px;
line-height: 55px;
color: white;
text-align: center;
}
.right_right {
position: absolute;
right: 24px;
line-height: 55px;
color: white;
text-align: center;
}
.leftside {
float: left;
background-color: rgb(245, 245, 245);
/* height: 663px; */
width: 230px;
}
.leftside ul {
height: 663px;
}
.leftside .first {
background-color: rgb(66, 139, 202);
margin-top: 25px;
}
.leftside .first a {
color: white;
}
.leftside ul li {
border-bottom: 0.2px solid white;
font-size: 20px;
height: 60px;
line-height: 60px;
width: 100%;
text-align: center;
}
.leftside ul li a {
color: black;
}
.container-fluid {
position: relative;
top: 35px;
left: 70px;
}
/* .sub-header {
margin-top: 35px;
} */
.table-responsive {
margin-top: 10px;
}
/* .table-striped {
width: 1250px;
} */
thead tr th {
background-color: white;
}
tbody tr {
background-color: rgb(245, 245, 245);
}
tbody tr td .long {
width: 270px;
height: 30px;
}
tbody tr td .last {
height: 40px;
width: 60px;
}
</style>
</head>
<body>
<div class="header">
<span class="left">成 绩 信 息 录 入</span>
<span class="right">你 好,{{user_info}}老 师!</span>
<span class="right_right"><a href="/">退出登陆</a></span>
</div>
<div class="leftside">
<ul>
<li><a href="/student">学生信息录入</a></li>
<li><a href="/teacher">选课信息录入</a></li>
<li class="first"><a href="#">成绩信息录入</a></li>
<li><a href="/grade_infos">学生成绩查询</a></li>
<li><a href="/adminstator">系统管理员变动</a></li>
</ul>
</div>
<div class="container-fluid">
<h1 class="sub-header">学 生 成 绩 信 息 录 入 系 统</h1>
<hr>
<div class="table-responsive">
<table class="table table-striped">
<thead>
<tr>
<th>学号</th>
<th>课程号</th>
<th>成绩</th>
<th>#</th>
</tr>
</thead>
<tbody>
<tr>
<form action="" method="post">
<td><input class="long" name="student_id" type="text" /> </td>
<td><input class="long" name="student_class_id" type="text" />
</td>
<td><input class="long" name="grade"
type="text" />
</td>
<td><input class="last" type="submit" value="提交" /> </td>
<td><span>提交结果:{{insert_result}}</span></td>
</form>
</tr>
<tr>
<td>学生学号</td>
<td>所选课程号</td>
<td>课程成绩</td>
</tr>
{% for result in results %}
<tr>
<td>{{result[0]}}</td>
<td>{{result[1]}}</td>
<td>{{result[2]}}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
</body>
</html>
5.学生成绩查询
grade_infos.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>学生成绩管理系统</title>
<link rel="icon" href="../static/images/1.ico">
<style>
* {
margin: 0;
padding: 0;
}
li {
list-style: none;
}
a {
text-decoration: none;
color: white;
}
.header {
position: relative;
width: 100%;
height: 55px;
background-color: black;
}
.left {
position: absolute;
left: 20px;
font-size: 20px;
line-height: 55px;
color: white;
text-align: center;
}
.right {
position: absolute;
right: 160px;
line-height: 55px;
color: white;
text-align: center;
}
.right_right {
position: absolute;
right: 24px;
line-height: 55px;
color: white;
text-align: center;
}
.leftside {
float: left;
background-color: rgb(245, 245, 245);
/* height: 663px; */
width: 230px;
}
.leftside ul {
height: 663px;
}
.leftside .first {
background-color: rgb(66, 139, 202);
margin-top: 25px;
}
.leftside .first a {
color: white;
}
.leftside ul li {
border-bottom: 0.2px solid white;
font-size: 20px;
height: 60px;
line-height: 60px;
width: 100%;
text-align: center;
}
.leftside ul li a {
color: black;
}
.container-fluid {
position: relative;
top: 35px;
left: 70px;
}
/* .sub-header {
margin-top: 35px;
} */
.table-responsive {
margin-top: 10px;
}
/* .table-striped {
width: 1250px;
} */
thead tr th {
background-color: white;
}
tbody tr {
background-color: rgb(245, 245, 245);
}
tbody tr td select {
height: 26px;
}
tbody tr td .long {
width: 120px;
height: 22px;
}
tbody tr td .last {
height: 25px;
width: 60px;
}
</style>
</head>
<body>
<div class="header">
<span class="left">学 生 成 绩 查 询</span>
<span class="right">你 好,{{user_info}}老 师!</span>
<span class="right_right"><a href="/">退出登陆</a></span>
</div>
<div class="leftside">
<ul>
<li><a href="/student">学生信息录入</a></li>
<li><a href="/teacher">选课信息录入</a></li>
<li><a href="/grade">成绩信息录入</a></li>
<li class="first"><a href="#">学生成绩查询</a></li>
<li><a href="/adminstator">系统管理员变动</a></li>
</ul>
</div>
<div class="container-fluid">
<h1 class="sub-header">学 生 成 绩 查 询 系 统</h1>
<hr>
<div class="table-responsive">
<table class="table table-striped" cellspaing="10">
<!-- <thead>
</thead> -->
<tbody>
<tr>
<form action="" method="post">
<td><label for="#">请选择查询的方式:(学号/姓名/课程号/所在班级)</label> </td>
<td><select name="selected_one">
<option value="学号" selected="selected">学号</option>
<option value="姓名">姓名</option>
<option value="课程号">课程号</option>
<option value="所在班级">所在班级</option>
</select></td>
<td><input class="long" type="text" name="query"></td>
<td><input class="last" type="submit" value="查询" /></td>
<td><span>查询结果:{{query_result}}</span></td>
</form>
</tr>
<tr>
<td>学生学号</td>
<td>课程号</td>
<td>成绩</td>
</tr>
{% for result in results %}
<tr>
<td>{{result[0]}}</td>
<td>{{result[1]}}</td>
<td>{{result[2]}}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</body>
</html>
6.系统管理员变动
adminstator.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>学生成绩管理系统</title>
<link rel="icon" href="../static/images/1.ico">
<style>
* {
margin: 0;
padding: 0;
}
li {
list-style: none;
}
a {
text-decoration: none;
color: white;
}
.header {
position: relative;
width: 100%;
height: 55px;
background-color: black;
}
.left {
position: absolute;
left: 20px;
font-size: 20px;
line-height: 55px;
color: white;
text-align: center;
}
.right {
position: absolute;
right: 160px;
line-height: 55px;
color: white;
text-align: center;
}
.right_right {
position: absolute;
right: 24px;
line-height: 55px;
color: white;
text-align: center;
}
.leftside {
float: left;
background-color: rgb(245, 245, 245);
/* height: 663px; */
width: 230px;
}
.leftside ul {
height: 663px;
}
.leftside .first {
background-color: rgb(66, 139, 202);
margin-top: 25px;
}
.leftside .first a {
color: white;
}
.leftside ul li {
border-bottom: 0.2px solid white;
font-size: 20px;
height: 60px;
line-height: 60px;
width: 100%;
text-align: center;
}
.leftside ul li a {
color: black;
}
.container-fluid {
position: relative;
top: 35px;
left: 70px;
}
/* .sub-header {
margin-top: 35px;
} */
.table-responsive {
margin-top: 10px;
}
/* .table-striped {
width: 1250px;
} */
thead tr th {
background-color: white;
}
tbody tr {
background-color: rgb(245, 245, 245);
}
tbody tr td select {
height: 26px;
}
tbody tr td .long {
width: 120px;
height: 22px;
}
tbody tr td .last {
height: 25px;
width: 60px;
}
</style>
</head>
<body>
<div class="header">
<span class="left">学 生 成 绩 查 询</span>
<span class="right">你 好,{{user_info}}老 师!</span>
<span class="right_right"><a href="/">退出登陆</a></span>
</div>
<div class="leftside">
<ul>
<li><a href="/student">学生信息录入</a></li>
<li><a href="/teacher">选课信息录入</a></li>
<li><a href="/grade">成绩信息录入</a></li>
<li class="first"><a href="#">学生成绩查询</a></li>
<li><a href="/adminstator">系统管理员变动</a></li>
</ul>
</div>
<div class="container-fluid">
<h1 class="sub-header">学 生 成 绩 查 询 系 统</h1>
<hr>
<div class="table-responsive">
<table class="table table-striped" cellspaing="10">
<!-- <thead>
</thead> -->
<tbody>
<tr>
<form action="" method="post">
<td><label for="#">请选择查询的方式:(学号/姓名/课程号/所在班级)</label> </td>
<td><select name="selected_one">
<option value="学号" selected="selected">学号</option>
<option value="姓名">姓名</option>
<option value="课程号">课程号</option>
<option value="所在班级">所在班级</option>
</select></td>
<td><input class="long" type="text" name="query"></td>
<td><input class="last" type="submit" value="查询" /></td>
<td><span>查询结果:{{query_result}}</span></td>
</form>
</tr>
<tr>
<td>学生学号</td>
<td>课程号</td>
<td>成绩</td>
</tr>
{% for result in results %}
<tr>
<td>{{result[0]}}</td>
<td>{{result[1]}}</td>
<td>{{result[2]}}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</body>
</html>
后端(flask框架)
import flask
from flask import url_for # 进行网页跳转
import os # 用于操作系统文件的依赖库
import re # 引入正则表达式对用户输入进行限制
import pymysql # 连接数据库
# 初始化
app = flask.Flask(__name__)
db = pymysql.connect(host='127.0.0.1', port=3306, user='root',
password='root', database='student', charset='utf8')
cursor = db.cursor()
db.ping(reconnect=True)
users = []
@app.route("/", methods=["GET", "POST"])
def login():
flask.session['login'] = ''
if flask.request.method == 'POST':
user = flask.request.values.get("user", "")
pwd = flask.request.values.get("pwd", "")
result_user = re.search(r"^[a-zA-Z]+$", user)
result_pwd = re.search(r"^[a-zA-Z\d]+$", pwd)
if result_user != None and result_pwd != None:
msg = '用户名或密码错误'
sql = "select * from admins where admin_name='" + \
user + "' and admin_password='" + pwd + "';"
cursor.execute(sql)
result = cursor.fetchone()
if result:
flask.session['login'] = 'OK'
users.append(user)
return flask.redirect(flask.url_for('student'))
# return flask.redirect('/file')
else:
msg = '非法输入'
else:
msg = ''
user = ''
return flask.render_template('login.html', msg=msg, user=user)
@app.route('/student', methods=['GET', "POST"])
def student():
if flask.session.get("login", "") == '':
print('用户还没有登陆!即将重定向!')
return flask.redirect('/')
insert_result = ''
if users:
for user in users:
user_info = user
else:
user_info = ''
if flask.request.method == 'GET':
sql_list = "select * from students_infos"
cursor.execute(sql_list)
results = cursor.fetchall()
if flask.request.method == 'POST':
student_id = flask.request.values.get("student_id", "")
student_class = flask.request.values.get("student_class", "")
student_name = flask.request.values.get("student_name", "")
print(student_id, student_class, student_name)
try:
sql = "create table if not exists students_infos(student_id varchar(15) primary key,student_class varchar(20),student_name varchar(10));"
cursor.execute(sql)
sql_1 = "insert into students_infos(student_id, student_class, student_name)values(%s,%s,%s)"
cursor.execute(sql_1, (student_id, student_class, student_name))
insert_result = "成功存入一条学生信息"
print(insert_result)
except Exception as err:
print(err)
insert_result = "学生信息插入失败"
print(insert_result)
pass
db.commit()
sql_list = "select * from students_infos"
cursor.execute(sql_list)
results = cursor.fetchall()
return flask.render_template('student.html', insert_result=insert_result, user_info=user_info, results=results)
@app.route('/teacher', methods=['GET', "POST"])
def teacher():
if flask.session.get("login", "") == '':
print('用户还没有登陆!即将重定向!')
return flask.redirect('/')
insert_result = ''
if users:
for user in users:
user_info = user
else:
user_info = ''
if flask.request.method == 'GET':
sql_list = "select * from students_decision_infos"
cursor.execute(sql_list)
results = cursor.fetchall()
if flask.request.method == 'POST':
student_id = flask.request.values.get("student_id", "")
student_class_id = flask.request.values.get("student_class_id", "")
teacher_id = flask.request.values.get("teacher_id", "")
print(student_id, student_class_id, teacher_id)
try:
sql = "create table if not exists students_decision_infos(student_id varchar(15) primary key,student_class_id varchar(20),teacher_id varchar(15),foreign key(student_id) references students_infos(student_id));"
cursor.execute(sql)
sql_1 = "insert into students_decision_infos(student_id, student_class_id, teacher_id)values(%s,%s,%s)"
cursor.execute(sql_1, (student_id, student_class_id, teacher_id))
insert_result = "成功存入一条选课信息"
print(insert_result)
except Exception as err:
print(err)
insert_result = "选课信息插入失败"
print(insert_result)
pass
db.commit()
sql_list = "select * from students_decision_infos"
cursor.execute(sql_list)
results = cursor.fetchall()
return flask.render_template('teacher.html', insert_result=insert_result, user_info=user_info, results=results)
@app.route('/grade', methods=['GET', "POST"])
def grade():
if flask.session.get("login", "") == '':
print('用户还没有登陆!即将重定向!')
return flask.redirect('/')
insert_result = ''
if users:
for user in users:
user_info = user
else:
user_info = ''
if flask.request.method == 'GET':
sql_list = "select * from grade_infos"
cursor.execute(sql_list)
results = cursor.fetchall()
if flask.request.method == 'POST':
student_id = flask.request.values.get("student_id", "")
student_class_id = flask.request.values.get("student_class_id", "")
grade = flask.request.values.get("grade", "")
print(student_id, student_class_id, grade)
try:
sql = "create table if not exists grade_infos(student_id varchar(15) primary key,student_class_id varchar(20),grade tinyint unsigned,foreign key(student_id) references students_decision_infos(student_id));"
cursor.execute(sql)
sql_1 = "insert into grade_infos(student_id, student_class_id,grade)values(%s,%s,%s)"
cursor.execute(sql_1, (student_id, student_class_id, grade))
insert_result = "成功存入一条学生成绩信息"
print(insert_result)
except Exception as err:
print(err)
insert_result = "学生成绩信息插入失败"
print(insert_result)
pass
db.commit()
sql_list = "select * from grade_infos"
cursor.execute(sql_list)
results = cursor.fetchall()
return flask.render_template('grade.html', insert_result=insert_result, user_info=user_info, results=results)
@app.route('/grade_infos', methods=['GET', 'POST'])
def grade_infos():
# login session值
if flask.session.get("login", "") == '':
# 用户没有登陆
print('用户还没有登陆!即将重定向!')
return flask.redirect('/')
query_result = ''
results = ''
# 当用户登陆有存储信息时显示用户名,否则为空
if users:
for user in users:
user_info = user
else:
user_info = ''
# 获取下拉框的数据
if flask.request.method == 'POST':
select = flask.request.form.get('selected_one')
query = flask.request.values.get('query')
print(select, query)
# 判断不同输入对数据表进行不同的处理
if select == '学号':
try:
sql = "select * from grade_infos where student_id = %s; "
cursor.execute(sql, query)
results = cursor.fetchall()
if results:
query_result = '查询成功!'
else:
query_result = '查询失败!'
except Exception as err:
print(err)
pass
if select == '姓名':
try:
sql = "select * from grade_infos where student_id in(select student_id from students_infos where student_name=%s);"
cursor.execute(sql, query)
results = cursor.fetchall()
if results:
query_result = '查询成功!'
else:
query_result = '查询失败!'
except Exception as err:
print(err)
pass
if select == '课程号':
try:
sql = "select * from grade_infos where student_class_id in(select student_class_id from students_infos where student_class_id=%s);"
cursor.execute(sql, query)
results = cursor.fetchall()
if results:
query_result = '查询成功!'
else:
query_result = '查询失败!'
except Exception as err:
print(err)
pass
if select == "所在班级":
try:
sql = "select * from grade_infos where student_class_id in(select student_class_id from students_infos where student_class=%s);"
cursor.execute(sql, query)
results = cursor.fetchall()
if results:
query_result = '查询成功!'
else:
query_result = '查询失败!'
except Exception as err:
print(err)
pass
return flask.render_template('grade_infos.html', query_result=query_result, user_info=user_info, results=results)
@app.route('/adminstator', methods=['GET', "POST"])
def adminstator():
# login session值
if flask.session.get("login", "") == '':
# 用户没有登陆
print('用户还没有登陆!即将重定向!')
return flask.redirect('/')
insert_result = ''
# 获取显示管理员数据信息(GET方法的时候显示数据)
if flask.request.method == 'GET':
sql_list = "select * from admins"
cursor.execute(sql_list)
results = cursor.fetchall()
# 当用户登陆有存储信息时显示用户名,否则为空
if users:
for user in users:
user_info = user
else:
user_info = ''
if flask.request.method == 'POST':
# 获取输入的管理员信息
admin_name = flask.request.values.get("admin_name", "")
admin_password = flask.request.values.get("admin_password", "")
#print(admin_name, admin_password)
admin_name_result = re.search(r"^[a-zA-Z]+$", admin_name) # 限制用户名为全字母
admin_password_result = re.search(
r"^[a-zA-Z\d]+$", admin_password) # 限制密码为 字母和数字的组合
# 验证通过
if admin_name_result != None and admin_password_result != None: # 验证通过
# 获取下拉框的数据
select = flask.request.form.get('selected_one')
if select == '增加管理员':
try:
sql = "create table if not exists admins(id int primary key auto_increment,admin_name varchar(15),admin_password varchar(20));"
cursor.execute(sql)
sql_1 = "insert into admins(admin_name,admin_password)values(%s,%s)"
cursor.execute(sql_1, (admin_name, admin_password))
insert_result = "成功增加了一名管理员"
print(insert_result)
except Exception as err:
print(err)
insert_result = "增加管理员操作失败"
print(insert_result)
pass
db.commit()
if select == '修改管理员密码':
try:
sql = "update admins set admin_password=%swhere admin_name=%s;"
cursor.execute(sql, (admin_password, admin_name))
insert_result = "管理员" + admin_name + "的密码修改成功!"
except Exception as err:
print(err)
insert_result = "修改管理员密码失败!"
pass
db.commit()
if select == '删除管理员':
try:
sql_delete = "delete from admins where admin_name='" + admin_name + "';"
cursor.execute(sql_delete)
insert_result = "成功删除管理员" + admin_name
except Exception as err:
print(err)
insert_result = "删除管理员失败"
pass
db.commit()
else: # 输入验证不通过
insert_result = "输入的格式不符合要求!"
# POST方法时显示数据
sql_list = "select * from admins"
cursor.execute(sql_list)
results = cursor.fetchall()
return flask.render_template('adminstator.html', user_info=user_info, insert_result=insert_result, results=results)
app.debug = True
app.secret_key = 'carson'
try:
app.run()
except Exception as err:
print(err)
db.close()
后记
前端小白第一次连接数据库,参考了好多资料,终于完成了数据库答辩。不足之处,还请各位大佬指正。