php 简易教学管理系统
想法源于网络,代码非常非常冗长!!
想优化的,请查找css,js,及各类模板优化。
【实验题目】
1、有登录界面,不同的用户登录后进入不同的页面
2、能完成基本数据(包括学生、教师、课程、选课)的增删改
3、根据指定要求完成查询
3.1可以查询指定姓名的学生或教师
3.2可以查找某位教师的授课门数
3.3可以查找指定学生选修的课程列表,包括课程名、任课教师、成绩等
3.4可以查找指定所有课程的平均成绩
【实验数据】
–建表
– student
CREATE TABLE IF NOT EXISTS Student
(
s_id
VARCHAR(20),
s_name
VARCHAR(20) NOT NULL DEFAULT ‘’,
s_birth
VARCHAR(20) NOT NULL DEFAULT ‘’,
s_sex
VARCHAR(10) NOT NULL DEFAULT ‘’,
PRIMARY KEY(s_id
)
) character set = utf8;
– course
CREATE TABLE IF NOT EXISTS Course
(
c_id
VARCHAR(20),
c_name
VARCHAR(20) NOT NULL DEFAULT ‘’,
t_id
VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id
)
)character set = utf8;
– teacher
CREATE TABLE IF NOT EXISTS Teacher
(
t_id
VARCHAR(20),
t_name
VARCHAR(20) NOT NULL DEFAULT ‘’,
PRIMARY KEY(t_id
)
)character set = utf8;
– score
CREATE TABLE IF NOT EXISTS Score
(
s_id
VARCHAR(20),
c_id
VARCHAR(20),
s_score
INT(3),
PRIMARY KEY(s_id
,c_id
)
)character set = utf8;
– 用户密码表
CREATE TABLE IF NOT EXISTS Users
(
username
VARCHAR(20) not null primary key,
password
VARCHAR(20),
usertype
varchar(20)
)character set = utf8;
– 插入学生表测试数据
insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
insert into Student values(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);
– 课程表测试数据
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);
– 教师表测试数据
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);
– 成绩表测试数据
insert into Score values(‘01’ , ‘01’ , 80);
insert into Score values(‘01’ , ‘02’ , 90);
insert into Score values(‘01’ , ‘03’ , 99);
insert into Score values(‘02’ , ‘01’ , 70);
insert into Score values(‘02’ , ‘02’ , 60);
insert into Score values(‘02’ , ‘03’ , 80);
insert into Score values(‘03’ , ‘01’ , 80);
insert into Score values(‘03’ , ‘02’ , 80);
insert into Score values(‘03’ , ‘03’ , 80);
insert into Score values(‘04’ , ‘01’ , 50);
insert into Score values(‘04’ , ‘02’ , 30);
insert into Score values(‘04’ , ‘03’ , 20);
insert into Score values(‘05’ , ‘01’ , 76);
insert into Score values(‘05’ , ‘02’ , 87);
insert into Score values(‘06’ , ‘01’ , 31);
insert into Score values(‘06’ , ‘03’ , 34);
insert into Score values(‘07’ , ‘02’ , 89);
insert into Score values(‘07’ , ‘03’ , 98);
– 插入用户密码数据 ,s表示学生,t表示教师,a表示管理员
insert into users values(‘student’,‘123456’,‘s’);
insert into users values(‘teacher’,‘123456’,‘t’);
insert into users values(‘admin’,‘123456’,‘a’);
【实验源码目录】
以上均使用Bootstrap模板以及utf-8编码,请联网测试。
代码过多,不一一展示。
1.html
登陆界面
<!DOCTYPE html>
<html>
<head>
<title>登陆</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<style>
body {
background-image: url("timg.jpg ");
}
</style>
</head>
<body>
<div class="container">
<div class="col-md-4"></div>
<div class="col-md-4" style="background-color:#eee;;margin-top:200px;">
<center>
<h3></h3>
</center>
<form action="3.php" method="POST">
<div class="form-group">
<label for="">用户名:</label>
<input type="text" class="form-control" name="username" style="color:red;">
</div>
<div class="form-group">
<label for="">密码</label>
<input type="text" class="form-control" name="password" style="color:red;">
</div>
<div class="form-group">
<input type="submit" value="登陆" class="btn btn-primary form-control">
</div>
</form>
</div>
<div class="col-md-4"></div>
</div>
<script src="https://code.jquery.com/jquery.js"></script>
<script src="js/bootstrap.min.js"></script>
</body>
</html>
timg.jpg是背景图
效果:
3.php
<?php
header("Content-Type: text/html;charset=utf-8");
$host = '127.0.0.1';
$dbuser = 'root';
$password = '168168';
$con = mysqli_connect($host, $dbuser, $password);
//$con = mysqli_connect("localhost","root","168168");
if (!$con) {
die('连接失败: ' . mysqli_error($con));
}
$db_selected = mysqli_select_db($con, "student");
if (!$db_selected) {
die("不能选择指定的数据库: " . mysqli_error($con));
} else {
echo "选择数据库成功";
}
$username = $_POST["username"];
$password = $_POST["password"];
mysqli_query($con, "SET NAMES utf8"); //设置字符集为中文
$sql = "select * from users where username='$username'";
$result = mysqli_query($con, $sql) or die("<br/>ERROR:<b>" . mysqli_error($con) .
"</b><br/>产生问题的SQL是:" . $sql);
$row = mysqli_fetch_array($result);
//根据密码账号转去不同页面
if ($password==$row[1] and $row[2]=='s')
{$success = '1';}
elseif ($password==$row[1] and $row[2]=='t')
{$success = '2';}
elseif ($password==$row[1] and $row[2]=='a')
{$success = '3';}
else{$success = '0';}
if ($success=='1') {
$location = "student.php?username=$username";
header("location:$location");
}
elseif($success=='2') {
$location = "teacher.php?username=$username";
header("location:$location");
}
elseif($success=='3') {
$location = "admin.php?username=$username";
header("location:$location");
}
else {
$location = "failure.php?username=$username";
header("location:$location");
}
?>
failure.php
功能:登陆失败,点击返回按钮可返回登陆页面
student.php
功能:学号为01的学生信息,可修改基础信息
teacher.php
功能:教师号为01的老师信息,可修改成绩信息,删除选课学生
代码:
<!DOCTYPE html>
<html>
<head>
<title>老师</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
<h4><p class="text-center">老师,登陆成功</p></h4>
</div>
<?php
header("Content-Type: text/html;charset=utf-8");
$host = '127.0.0.1';
$dbuser = 'root';
$password = '168168';
$con = mysqli_connect($host, $dbuser, $password);
//$con = mysqli_connect("localhost","root","168168");
if (!$con) {
die('连接失败: ' . mysqli_error($con));
}
$db_selected = mysqli_select_db($con, "student");
if (!$db_selected) {
die("不能选择指定的数据库: " . mysqli_error($con));
} else {
//echo "选择数据库成功";
}
mysqli_query($con, "SET NAMES utf8"); //设置字符集为中文
$sql = "SELECT c.c_id,t.t_name,c.c_name,st.* ,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
LEFT JOIN teacher t ON t.t_id=c.t_id
WHERE t.t_name='张三'";
$result = mysqli_query($con, $sql) or die("<br/>ERROR:<b>" . mysqli_error($con) .
"</b><br/>产生问题的SQL是:" . $sql);
?>
<table class="table table-striped">
<caption></caption>
<thead>
<tr>
<th>课程号</th>
<th>老师</th>
<th>课程名</th>
<th>学号</th>
<th>姓名</th>
<th>生日</th>
<th>性别</th>
<th>选课分数</th>
<th>更新</th>
<th>删除</th>
</tr>
</thead>
<tbody>
<?php
while ($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row['c_id']; ?></td>
<td><?php echo $row['t_name']; ?></td>
<td><?php echo $row['c_name']; ?></td>
<td><?php echo $row['s_id']; ?></td>
<td><?php echo $row['s_name']; ?></td>
<td><?php echo $row['s_birth']; ?></td>
<td><?php echo $row['s_sex']; ?></td>
<td><?php echo $row['s_score']; ?></td>
<td><a onclick="javascript:if(confirm('确定修改信息吗?')) return true;else return false;" href="teacherinfo.php?sid=<?php echo $row['s_id']; ?>">更新</a></td>
<td><a onclick="javascript:if(confirm('确定删除信息吗?')) return true;else return false;" href="deleteteacher.php?sid=<?php echo $row['s_id'];?>&cid=<?php echo $row['c_id'];?>">删除</a></td>
</tr>
<?php }
mysqli_close($con);
?>
</tbody>
</table>
<p class="text-center"><a href="1.html"><button type="button" class="btn btn-primary btn-lg" >返回</button></a></p>
<script src="https://code.jquery.com/jquery.js"></script>
<script src="js/bootstrap.min.js"></script>
</body>
</html>
效果:
admin.php
功能:管理员,可查看学生老师成绩等信息,可增加,更新,删除信息
学生信息更新,删除,增加
源码:addstudent.php deletestudent.php studentinfo.php updatedb.php
老师信息更新
源码:addteacher.php deleteteacher.php teacherinfo.php updatedb1.php
成绩信息更新
源码:addscore.php deletescore.php
第三题
源码:no.3.php
代码:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<form name="form1" method="post" action="">
3.1:指定姓名的学生或教师(请输入学生或老师id)
<br />
<input type="text" name="name1" id="XH" />
<select name="id">
<option value="老师">老师</option>
<option value="学生">学生</option>
</select>
<br />
3.2:某教师的授课门数(请输入老师id)
<br />
<input type="text" name="number" id="XH" />
<br />
3.3:指定学生选修的课程列表,包括课程名、任课教师、成绩(请输入学生id)
<br />
<input type="text" name="name2" id="XH" />
<br />
3.4:是否查询所有课程的平均成绩
<br />
<select name="name3">
<option value="空"></option>
<option value="是">是</option>
<option value="否">否</option>
</select>
<input type="submit" name="btnsubmit1" value="提交" />
<br />
</form>
<?php
header("Content-Type: text/html;charset=utf-8");
$host = '127.0.0.1';
$dbuser = 'root';
$password = '168168';
$con = mysqli_connect($host, $dbuser, $password);
if (!$con) {
die('连接失败: ' . mysqli_error($con));
}
$db_selected = mysqli_select_db($con, "student");
if (!$db_selected) {
die("不能选择指定的数据库: " . mysqli_error($con));
} else {}
mysqli_query($con, "SET NAMES utf8"); //设置字符集为中文
$st='';
if(isset($_POST['btnsubmit1']))
{
$id=$_POST["name1"];
$tid=$_POST["number"];
$sid=$_POST["name2"];
$st=$_POST["id"];
if(isset($_POST['name1']))
{
if($_POST['id']=='老师'&&$id!='')
{
$sql = "SELECT t.t_name,c.c_id,c.c_name,st.s_id ,st.s_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
LEFT JOIN teacher t ON t.t_id=c.t_id
WHERE t.t_id=$id
";
$result = mysqli_query($con, $sql) or die("<br/>ERROR:<b>" . mysqli_error($con) .
"</b><br/>产生问题的SQL是:" . $sql);
}
if($_POST['id']=='学生'&&$id!='')
{
$sql = "SELECT st.s_id,st.s_name,st.s_birth,st.s_sex,COUNT(sc.c_id)'选课总数',SUM(CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END)'总成绩' ,AVG(sc4.s_score) '平均分',sc.s_score '语文',sc2.s_score '数学',sc3.s_score '英语' FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01'
LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02'
LEFT JOIN score sc3 ON sc3.s_id=st.s_id AND sc3.c_id='03'
LEFT JOIN score sc4 ON sc4.s_id=st.s_id
WHERE st.s_id=$id
";
$result = mysqli_query($con, $sql) or die("<br/>ERROR:<b>" . mysqli_error($con) .
"</b><br/>产生问题的SQL是:" . $sql);
}
}
if(isset($_POST['number'])&&$tid!='')
{
$sql = " SELECT COUNT(c.c_id)'授课门数'
FROM course c
LEFT JOIN teacher t ON t.t_id=c.t_id WHERE t.t_id=$tid
";
$result = mysqli_query($con, $sql) or die("<br/>ERROR:<b>" . mysqli_error($con) .
"</b><br/>产生问题的SQL是:" . $sql);
}
if(isset($_POST['name2'])&&$sid!='')
{
$sql = "SELECT st.s_id,st.s_name ,c.c_name ,t.t_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
LEFT JOIN teacher t ON t.t_id=c.t_id
WHERE st.s_id=$sid
";
$result = mysqli_query($con, $sql) or die("<br/>ERROR:<b>" . mysqli_error($con) .
"</b><br/>产生问题的SQL是:" . $sql);
}
if((isset($_POST['name3'])))
{
if($_POST['name3']=='是')
{
$sql = "select c.c_id,c.c_name,avg(sc.s_score)'平均成绩' from course c
inner join score sc on sc.c_id=c.c_id
LEFT JOIN teacher t ON t.t_id=c.t_id
group by c.c_id
";
$result = mysqli_query($con, $sql) or die("<br/>ERROR:<b>" . mysqli_error($con) .
"</b><br/>产生问题的SQL是:" . $sql);
}
}
}
?>
<?php
if($st=='老师'&&$id!='')
{
?>
<table class="table table-striped">
<caption></caption>
<thead>
<tr>
<th>老师</th>
<th>课程号</th>
<th>课程名</th>
<th>学号</th>
<th>姓名</th>
<th>选课分数</th>
</tr>
</thead>
<tbody>
<?php
while ($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row['t_name']; ?></td>
<td><?php echo $row['c_id']; ?></td>
<td><?php echo $row['c_name']; ?></td>
<td><?php echo $row['s_id']; ?></td>
<td><?php echo $row['s_name']; ?></td>
<td><?php echo $row['s_score']; ?></td>
</tr>
<?php }
mysqli_close($con);
?>
</tbody>
</table>
<?php
}
?>
<?php
if($st=='学生'&&$id!='')
{
?>
<table class="table table-striped">
<caption></caption>
<thead>
<tr>
<th>学号</th>
<th>姓名</th>
<th>生日</th>
<th>性别</th>
<th>选课总数</th>
<th>总成绩</th>
<th>平均分</th>
<th>语文</th>
<th>数学</th>
<th>英语</th>
</tr>
</thead>
<tbody>
<?php
while ($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row['s_id']; ?></td>
<td><?php echo $row['s_name']; ?></td>
<td><?php echo $row['s_birth']; ?></td>
<td><?php echo $row['s_sex']; ?></td>
<td><?php echo $row['选课总数']; ?></td>
<td><?php echo $row['总成绩']; ?></td>
<td><?php echo $row['平均分']; ?></td>
<td><?php echo $row['语文']; ?></td>
<td><?php echo $row['数学']; ?></td>
<td><?php echo $row['英语']; ?></td>
</tr>
<?php }
mysqli_close($con);
?>
</tbody>
</table>
<?php
}
?>
<?php
if(isset($_POST['number'])&&$tid!='')
{
?>
<table class="table table-striped">
<caption></caption>
<thead>
<tr>
<th>授课门数</th>
</tr>
</thead>
<tbody>
<?php
while ($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row['授课门数']; ?></td>
</tr>
<?php }
mysqli_close($con);
?>
</tbody>
</table>
<?php
}
?>
<?php
if(isset($_POST['name2'])&&$sid!='')
{
?>
<table class="table table-striped">
<caption></caption>
<thead>
<tr>
<th>学号</th>
<th>姓名</th>
<th>课程名</th>
<th>授课老师</th>
<th>成绩</th>
</tr>
</thead>
<tbody>
<?php
while ($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row['s_id']; ?></td>
<td><?php echo $row['s_name']; ?></td>
<td><?php echo $row['c_name']; ?></td>
<td><?php echo $row['t_name']; ?></td>
<td><?php echo $row['s_score']; ?></td>
</tr>
<?php }
mysqli_close($con);
?>
</tbody>
</table>
<?php
}
?>
<?php
if((isset($_POST['name3']))&&$_POST['name3']=='是')
{
?>
<table class="table table-striped">
<caption></caption>
<thead>
<tr>
<th>课程号</th>
<th>课程名</th>
<th>平均成绩</th>
</tr>
</thead>
<tbody>
<?php
while ($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row['c_id']; ?></td>
<td><?php echo $row['c_name']; ?></td>
<td><?php echo $row['平均成绩']; ?></td>
</tr>
<?php }
mysqli_close($con);
?>
</tbody>
</table>
<?php
}
?>
</body>
</html>
效果:
【实验缺陷】
- 学生页面修改信息时,可修改网址上的学号查看他人信息。
- 通过前进后退,可以不用通过账号密码,查看网页信息。
不会改了,努力,加油。
代码链接 : 链接:https://pan.baidu.com/s/1H8NckpF_xHigBJDzuYX2_g
提取码:2nqt