目录
六、在test-demo/src目录下，打开命令行工具， 运行 node app.js启动服务，成功后，浏览器打开如下地址：http://localhost/login
一、新建目录，如：test-demo，使用命令行初始化
npm init二、安装相关依赖
npm install mysql express cors body-parser connect-history-api-fallback三、自行安装mysql后，安装Navicat 16 for MySQL图形界面工具， 创建数据库，导入student_database.sql文件
四、简单看下目录结构
五、代码相关 （主要文件如下）
1. app.js
let express = require("express");
let history = require('connect-history-api-fallback'); //用于处理路由配置 mode：history，强制刷新问题
let app = express();
let cors = require("cors");
// 导入 body-parser中间件解析表单数据
let bodyParser = require("body-parser");
let router = require("./router");
// 解析 url-encoded格式的表单数据
app.use(bodyParser.urlencoded({ extended: false }));
// 解析json格式的表单数据
app.use(bodyParser.json());
app.use(cors()); //配置跨域，必须在路由之前
app.use(router) //配置路由
app.use(history()) //用于处理路由配置 mode：history，强制刷新问题
app.use(express.static(__dirname+'/static'))
app.listen(80, () => {
console.log("服务器启动成功");
});
2. db/index.js
let mysql = require("mysql");
let db = mysql.createPool({
host: "127.0.0.1", //数据库IP地址
// host:"localhost",
port:"3306",//数据库端口
user: "******", //数据库登录账号
password: "******", //数据库登录密码
database: "student_database", //要操作的数据库
});
module.exports = db;
3. router.js
let express = require("express");
let router = express.Router();
let user = require("./API/user");
let dept = require("./API/dept");
let course = require("./API/course");
let score = require("./API/score");
let login = require("./API/login");
let home = require("./API/home");
// --------------------------登录相关-----------------------------------------------
// 列表
router.get("/user/login", login.loginUser);
// --------------------------首页相关-----------------------------------------------
// 组织机构列表
router.get("/home/getHomeDeptList", home.getHomeDeptList);
// 统计数量
router.get("/home/getTotalNum", home.getTotalNum);
// 统计男女比例
// router.get("/home/getSexTotalList", home.getSexTotalList);
// --------------------------课程信息管理相关-----------------------------------------------
// 列表
router.get("/course/list", course.getCourseList);
// 详情
router.get("/course/detail", course.getCourseDetail);
// 新增
router.post("/course/add", course.addCourse);
// 修改
router.post("/course/update", course.updateCourse);
// 删除
router.get("/course/del", course.delCourse);
// 根据专业id查询对应的课程
router.get("/course/getCourseBySpecialityId", course.getCourseBySpecialityId);
//给对应的专业添加课程
router.post("/course/updateSpeciality", course.updateSpeciality);
//给对应的专业删除课程
router.post("/course/delSpecialityById", course.delSpecialityById);
// --------------------------成绩信息管理相关-----------------------------------------------
// 查询班级列表
router.get("/score/getClassList", score.getClassList);
//根据班级id查询对应用户列表
router.get("/score/getUserListByClassId", score.getUserListByClassId);
//根据班级id--》查询对应专业id-->查询对应的课程列表
router.get("/score/getCourseByClassId", score.getCourseByClassId);
// 新增
router.post("/score/addStudentScore", score.addStudentScore);
// 列表
router.get("/score/getStudentScoreList", score.getStudentScoreList);
// 详情
router.get("/score/getStudentScoreDetail", score.getStudentScoreDetail);
// 修改
router.post("/score/updateStudentScore", score.updateStudentScore);
// 删除
router.get("/score/delStudentScore", score.delStudentScore);
// --------------------------组织结构管理相关-----------------------------------------------
// 列表
router.get("/dept/list", dept.getDeptList);
// 详情
router.get("/dept/detail", dept.getDeptDetail);
// 获取 除当前组织以及下级外的其他组织列表
router.get("/dept/otherlist", dept.getOtherDeptList);
// 新增
router.post("/dept/add", dept.addDept);
// 修改
router.post("/dept/update", dept.updateDept);
// 删除
router.get("/dept/del", dept.delDept);
// --------------------------用户管理相关-----------------------------------------------
// 列表
router.get("/user/list", user.getUserList);
// 详情
router.get("/user/detail", user.getUserDetail);
// 新增
router.post("/user/add", user.addUser);
// 修改
router.post("/user/update", user.updateUser);
// 删除
router.get("/user/del", user.delUser);
module.exports = router;
4. utils.js
const isEmpty = function(data) {
return data == "" || data == undefined || data == null || data.length == 0
}
const splitString = function(data) {
if (isEmpty(data)) {
return []
} else {
return data.split(",")
}
}
function _formatNum(num) {
return num < 10 ? "0" + num : "" + num
}
const nowDate = function() {
var date = new Date();
var year = _formatNum(date.getFullYear());
var month = _formatNum(date.getMonth() + 1);
var day = _formatNum(date.getDate());
var hours = _formatNum(date.getHours());
var minutes = _formatNum(date.getMinutes());
var seconds = _formatNum(date.getSeconds());
let Y_CHN = year + "年"
let YMD = year + "-" + month + "-" + day
let YMD_CHN = year + "年" + month + "月" + day + "日"
let YMDHM = year + "-" + month + "-" + day + " " + hours + ":" + minutes
let YMDHM_CHN = year + "年" + month + "月" + day + "日 " + hours + "时" + minutes + "分"
let YMDHMS = year + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds
let YMDHMS_CHN = year + "年" + month + "月" + day + "日 " + hours + "时" + minutes + "分" + seconds + "秒"
let MDHM = month + "-" + day + " " + hours + ":" + minutes
let MDHM_CHN = month + "月" + day + "日 " + hours + "时" + minutes + "分"
let HM = hours + ":" + minutes
let HM_CHN = hours + "时" + minutes + "分"
let Y_M = year + "-" + month
return {
year: year.toString(),
month: month.toString(),
day: day.toString(),
hours: hours.toString(),
minutes: minutes.toString(),
seconds: seconds.toString(),
Y_CHN: Y_CHN,
YMD: YMD,
YMD_CHN: YMD_CHN,
YMDHM: YMDHM,
YMDHM_CHN: YMDHM_CHN,
YMDHMS: YMDHMS,
YMDHMS_CHN: YMDHMS_CHN,
MDHM: MDHM,
MDHM_CHN: MDHM_CHN,
HM: HM,
HM_CHN: HM_CHN,
Y_M:Y_M,
}
}
module.exports = {
isEmpty,
splitString,
nowDate,
}5. login.js
let db = require("../db/index");
let utils = require("../utils/utils");
let create_time = utils.nowDate().YMDHMS;
let update_time = utils.nowDate().YMDHMS;
//登录
exports.loginUser = (req, res) => {
let { username,password } = req.query
let sql = `select * from sys_user WHERE user_name =? and password = ? `
db.query(sql,[username,password], (err, data) => {
// console.log(err,'err')
if (err) {
return res.send('错误：' + err.msg)
}
// console.log(data,'data')
if(data.length!=0){ //存在账号
res.send({
data:data[0],
sqlList: [
{
title: '登录（返回该账号的信息）',
con: sql
}
]
})
}else{ //不存在账号
res.send({
code: 500,
msg: "账号或密码错误！",
sqlList: [
{
title: '登录（返回该账号的信息）',
con: sql
}
]
})
}
})
}
6. home.js
let db = require("../db/index");
let utils = require("../utils/utils");
let create_time = utils.nowDate().YMDHMS;
let update_time = utils.nowDate().YMDHMS;
// 查询学院||专业列表
exports.getHomeDeptList = (req, res) => {
let { organization_type } = req.query
if (organization_type == undefined) {
organization_type = ''
}
let sql = `select * from sys_dept WHERE organization_type = '${organization_type}'`
db.query(sql, (err, data) => {
if (err) {
return res.send('错误：' + err.msg)
}
res.send({
data,
sqlList: [
{
title:organization_type==1? '查询学院列表':'查询专业列表',
con: sql
}
]
})
})
}
//根据学院deptId ，专业 deptId 查询一下 对应的学生数，教师数量，专业数 ，课程数
exports.getTotalNum = (req, res) => {
let { dept_id } = req.query
let sql_01 = `select role, count(*) as total from sys_user group by role `
if (dept_id) {
sql_01+=` WHERE dept_id = '${dept_id}'`
}
// let sqlList =[]
db.query(sql_01, (err, data) => {
if (err) {
return res.send('错误：' + err.msg)
}
// console.log(data,'data')
let totalObj = {
studentNum:'',
teacherNum:'',
specialityNum:'',
courseNum:''
};
if(data){
data.forEach(element => {
if(element.role == 2){ //教师
totalObj.teacherNum = element.total
}
if(element.role == 3){ //学生
totalObj.studentNum = element.total
}
});
}
// sqlList.push({
// title:'统计老师，学生数量',
// con: sql
// })
// console.log(totalObj,'totalObj')
// ...................................................................................
let sql_02 = `select count(*) as specialityNum from sys_dept WHERE organization_type = '2'`
db.query(sql_02, (err, data) => {
if (err) {
return res.send('错误：' + err.msg)
}
// console.log(data,'data')
totalObj.specialityNum = data[0].specialityNum
// console.log(totalObj,'totalObj')
// sqlList.push({
// title:'统计专业数量',
// con: sql
// })
// ..................................................................................
let sql_03 = `select count(*) as courseNum from sys_course`
db.query(sql_03, (err, data) => {
if (err) {
return res.send('错误：' + err.msg)
}
// sqlList.push({
// title:'统计专业数量',
// con: sql
// })
// console.log(data,'data')
totalObj.courseNum = data[0].courseNum
// console.log(totalObj,'totalObj')
res.send({
data:totalObj,
sqlList:[
{
title:'统计学生，老师数量',
con: sql_01
},
{
title:'统计专业数量',
con: sql_02
},
{
title:'统计课程数量',
con: sql_03
},
],
})
})
})
})
}
// 统计学院的人员（男，女） group by dept_id
// exports.getSexTotalList = (req, res) => {
// let sql = `select * from sys_user a join sys_dept b on a.dept_id=b.dept_id where b.organization_type=1 `
// db.query(sql, (err, data) => {
// if (err) {
// return res.send('错误：' + err.msg)
// }
// console.log(data,'data')
// res.send({
// data,
// sqlList: [
// {
// title:'统计学院的人员（男，女）',
// con: sql
// }
// ]
// })
// })
// }7. user.js
let db = require("../db/index");
let utils = require("../utils/utils");
let create_time = utils.nowDate().YMDHMS;
let update_time = utils.nowDate().YMDHMS;
//查询用户列表
exports.getUserList = (req, res) => {
let {user_name,nick_name,dept_id,role,sex,pageNum,pageSize,order,dateRange} = req.query;
let totalNum = (parseInt(pageNum) - 1)*pageSize;
//获取符合条件的总数据条数
let sql_01 = `SELECT count(*) AS total FROM sys_user WHERE user_name like '%${user_name}%' and nick_name like '%${nick_name}%' and role like '%${role}%' and sex like '%${sex}%'`;
//如果存在单位
if(dept_id){
sql_01+=` and dept_id like '%${dept_id}%'`;
}
if(dateRange&&dateRange.length!=0){
let dateRange_01 = `${dateRange[0]}`+' 00:00:00';
let dateRange_02 = `${dateRange[1]}`+' 23:59:59';
sql_01 +=` and create_time between '${dateRange_01}' and '${dateRange_02}'`
}
// console.log(sql_01,'sql_01')
db.query(sql_01, (err1, data1) => {
if (err1) {
return res.send("错误：" + err1.message);
}
let total = data1[0].total;
let sql = `(SELECT user_id FROM sys_user where user_name like '%${user_name}%' and nick_name like '%${nick_name}%' and role like '%${role}%' and sex like '%${sex}%')`;
//如果存在单位
if(dept_id){
sql+=` and dept_id like '%${dept_id}%'`;
}
if(dateRange&&dateRange.length!=0){
let dateRange_01 = `${dateRange[0]}`+' 00:00:00';
let dateRange_02 = `${dateRange[1]}`+' 23:59:59';
sql +=` and create_time between '${dateRange_01}' and '${dateRange_02}'`
}
let sql_02 = `SELECT * FROM sys_user where user_id in `+sql+` ORDER BY create_time ${order} LIMIT ${totalNum}, ${pageSize}`;
// console.log(sql_02,'sql_02')
db.query(sql_02, (err, data) => {
if (err) {
return res.send("错误：" + err.message);
}
// 返回数据
res.send({
rows:data,
total,
sqlList:[
{
title:'查询总条数',
con:sql_01
},
{
title:'查询列表数据',
con:sql_02
},
]
});
});
});
};
//通过id查询用户详情
exports.getUserDetail = (req, res) => {
let {user_id} = req.query;
let sql = "select * from sys_user where user_id = ?"; //？用于占位
db.query(sql, [user_id], (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
res.send({
data,
sqlList:[
{
title:'通过id查询用户详情',
con:sql
},
]
});
});
};
// 新增用户
exports.addUser = (req, res) => {
let sql_01 = "select * from sys_user where user_name = ?"; //？用于占位
db.query(sql_01, [req.body.user_name], (err1, data1) => {
if (err1) {
return res.send("错误：" + err1.msg);
}
if (data1.length == 0) { //用户账号未存在
let {nick_name,phonenumber,user_name,password,sex,remark,dept_id,role} = req.body;
let sql = "insert into sys_user (nick_name,phonenumber,user_name,password,sex,remark,dept_id,role,create_time) values (?,?,?,?,?,?,?,?,?)";
db.query(
sql,
[nick_name,phonenumber,user_name,password,sex,remark,dept_id,role,create_time],
(err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
if (data.affectedRows > 0) {
res.send({
code: 200,
msg: "success",
sqlList:[
{
title:'通过user_name查询数据',
con:sql_01
},
{
title:'新增用户',
con:sql
},
],
});
} else {
res.send({
code: 202,
msg: "error",
});
}
}
);
} else {
res.send({
code: 500,
msg: "该用户名称已存在，不可重复添加！",
});
}
});
};
// 修改用户
exports.updateUser = (req, res) => {
let {nick_name,phonenumber,password,sex,remark,role,dept_id,user_id} = req.body;
//通过id更新数据
let sql = "update sys_user set nick_name = ?, phonenumber = ?, password = ?, sex = ?, remark = ?, role = ?, dept_id = ? where user_id = ?";
db.query(
sql,
[nick_name,phonenumber,password,sex,remark,role,dept_id,user_id],
(err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
if (data.changedRows > 0||data.affectedRows > 0) {
res.send({
code: 200,
msg: "success",
sqlList:[
{
title:'修改用户',
con:sql
},
]
});
} else {
res.send({
code: 202,
msg: "error",
});
}
}
);
};
//通过id删除用户
exports.delUser = (req, res) => {
let {user_id} = req.query;
let sql = "delete from sys_user where find_in_set(user_id,?)";
// let sql = "delete from sys_user where user_id = ?";
db.query(sql,[user_id], (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
if (data.affectedRows > 0) {
res.send({
code: 200,
msg: "删除成功",
sqlList:[
{
title:'通过id删除用户 ',
con:sql
},
]
});
} else {
res.send({
code: 202,
msg: "error",
});
}
});
};8. dept.js
let db = require("../db/index");
let utils = require("../utils/utils");
let create_time = utils.nowDate().YMDHMS;
let update_time = utils.nowDate().YMDHMS;
// 查询组织列表 DESC utils.isEmpty()
exports.getDeptList = (req, res) => {
let {dept_name,organization_type} = req.query;
if(dept_name == undefined){
dept_name = ''
}
if(organization_type == undefined){
organization_type = ''
}
let sql = `select * from sys_dept WHERE dept_name like '%${dept_name}%' and organization_type like '%${organization_type}%' ORDER BY order_num ASC`;
db.query(sql,(err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
res.send({
data,
sqlList:[
{
title:'查询组织列表',
con:sql
},
]
});
});
};
//通过id查询组织数据详情
exports.getDeptDetail = (req, res) => {
let {dept_id} = req.query;
let sql = "select * from sys_dept where dept_id = ?"; //？用于占位
db.query(sql, [dept_id], (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
res.send({
data,
sqlList:[
{
title:'通过id查询组织数据详情',
con:sql
},
]
});
});
};
// 获取 除当前组织以及下级外的其他组织列表
exports.getOtherDeptList = (req, res) => {
let {dept_id} = req.query;
let sql = "select * from sys_dept where dept_id != ? and not find_in_set(?,ancestors)"; //？用于占位
db.query(sql, [dept_id,dept_id], (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
res.send({
data,
sqlList:[
{
title:'除当前组织以及下级外的其他组织列表',
con:sql
},
]
});
});
};
// 新增组织机构
exports.addDept = (req, res) => {
let {parent_id,dept_name,order_num,organization_type,create_by} = req.body;
let sql = "insert into sys_dept (parent_id,dept_name,order_num,organization_type,create_by,create_time) values (?,?,?,?,?,?)";
db.query(
sql,
[parent_id,dept_name,order_num,organization_type,create_by,create_time],
(err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
if (data.affectedRows > 0) {
res.send({
code: 200,
msg: "success",
sqlList:[
{
title:'新增组织机构',
con:sql
},
]
});
//更新父级id
updateAncestors();
} else {
res.send({
code: 202,
msg: "error",
});
}
}
);
};
// 修改组织机构
exports.updateDept = (req, res) => {
let {parent_id,dept_name,order_num,organization_type,dept_id} = req.body;
//通过id更新数据
let sql = "update sys_dept set parent_id = ?, dept_name = ?, order_num = ?, organization_type = ? where dept_id = ?";
db.query(
sql,
[parent_id,dept_name,order_num,organization_type,dept_id],
(err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// console.log(data,'data')
if (data.changedRows > 0||data.affectedRows > 0) {
res.send({
code: 200,
msg: "success",
sqlList:[
{
title:'修改组织机构',
con:sql
},
]
});
//更新父级id
updateAncestors();
} else {
res.send({
code: 202,
msg: "error",
});
}
}
);
};
//通过id删除组织机构数据
exports.delDept = (req, res) => {
let {dept_id} = req.query;
let sql ="select * from sys_dept where parent_id=?";
let sql_02 = "delete from sys_dept where dept_id = ?";
db.query(sql, [dept_id], (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
if (data.length == 0) {//当前组织机构不存在下级
db.query(sql_02,[dept_id], (err2, data2) => {
if (err2) {
return res.send("错误：" + err2.msg);
}
// console.log(data2,'data2')
if (data2.affectedRows > 0) {
res.send({
code: 200,
msg: "删除成功",
sqlList:[
{
title:'根据parent_id查询数据',
con:sql
},
{
title:'通过id删除组织机构数据',
con:sql_02
},
]
});
//更新父级id
updateAncestors();
//更新对应的专业的课程绑定的id
delSpecialityById(dept_id);
} else {
res.send({
code: 202,
msg: "error",
});
}
});
} else {
res.send({
code: 500,
msg: "该组织机构存在下级，不可直接删除！",
});
}
});
};
// 更新祖级id
let updateAncestors = (req, res) => {
let sql_02 = "update sys_dept a left join sys_dept p on a.parent_id=p.dept_id set a.ancestors=concat(p.ancestors, ',', p.dept_id) where a.parent_id!=0 "; //？用于占位
db.query(sql_02, (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
});
};
// -------------------------------------------------------------------根据专业id---------------------------------------------------------------------------------
//删除专业id的时候,专业对应的绑定的课程中speciality_id也要更新一下
let delSpecialityById = (speciality_id) => {
// 专业id, 课程id串
// let {speciality_id} = req.body;
let sql = `update sys_course set speciality_ids=trim(both ',' from replace(concat(',', speciality_ids, ','), ',${speciality_id},', ',')) where speciality_ids is not null`;
// console.log(sql,'sql')
db.query(sql, (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
});
};
9. course.js
let db = require("../db/index");
let utils = require("../utils/utils");
let create_time = utils.nowDate().YMDHMS;
let update_time = utils.nowDate().YMDHMS;
//查询课程列表
exports.getCourseList = (req, res) => {
let {course_id,course_name,course_type,pageNum,pageSize,dept_id} = req.query;
let totalNum = (parseInt(pageNum) - 1)*pageSize;
//获取符合条件的总数据条数
let sql_01 = `SELECT count(*) AS total FROM sys_course WHERE course_name like '%${course_name}%' and course_type like '%${course_type}%'`;
// 课程编号
if(course_id){
sql_01 = sql_01+ `and course_id like '${course_id}'`
}
//根据dept_id做一些筛选(已经选择过的不能再选,专业核心课,实践类课程如果被其他专业选择了,就不能再选择了)
if(dept_id){
sql_01 += ` and (not find_in_set('${dept_id}',speciality_ids) or speciality_ids is null)`
// sql_01 += ` and (case when (course_type=3 or course_type=4) then else 0 end)>0 `
sql_01 += ` and (CASE
WHEN (course_type=3 or course_type=4) THEN
CASE
WHEN speciality_ids is null THEN 1
ELSE 0
END
ELSE 1
END)>0 `
}
// console.log(sql_01,'sql_01')
db.query(sql_01, (err1, data1) => {
if (err1) {
return res.send("错误：" + err1.message);
}
let total = data1[0].total;
let sql = `(SELECT course_id FROM sys_course where course_name like '%${course_name}%' and course_type like '%${course_type}%'`;
// 课程编号
if(course_id){
sql += ` and course_id like '${course_id}'`;
}
//根据dept_id做一些筛选(已经选择过的不能再选,专业核心课,实践类课程如果被其他专业选择了,就不能再选择了)
if(dept_id){
sql += ` and (not find_in_set('${dept_id}',speciality_ids) or speciality_ids is null)`
sql += ` and (CASE
WHEN (course_type=3 or course_type=4) THEN
CASE
WHEN speciality_ids is null THEN 1
ELSE 0
END
ELSE 1
END)>0 `
}
sql+=')';
let sql_02 = `SELECT * FROM sys_course where course_id in ` +sql+ ` LIMIT ${totalNum}, ${pageSize}`;
// console.log(sql_02,'sql_02')
db.query(sql_02, (err, data) => {
if (err) {
return res.send("错误：" + err.message);
}
// 返回数据
res.send({
rows:data,
total,
sqlList:[
{
title:dept_id?'根据dept_id查询可以添加的课程总数':'查询总条数',
con:sql_01
},
{
title:dept_id?'根据dept_id查询可以添加的课程列表':'查询列表数据',
con:sql_02
},
]
});
});
});
};
//通过id查询课程详情
exports.getCourseDetail = (req, res) => {
let {course_id} = req.query;
let sql = "select * from sys_course where course_id = ?"; //？用于占位
db.query(sql, [course_id], (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
res.send({
data,
sqlList:[
{
title:'通过id查询课程详情',
con:sql
},
]
});
});
};
// 新增课程
exports.addCourse = (req, res) => {
let sql_01 = "select * from sys_course where course_name = ?"; //？用于占位
db.query(sql_01, [req.body.course_name], (err1, data1) => {
if (err1) {
return res.send("错误：" + err1.msg);
}
if (data1.length == 0) { //课程账号未存在
let {course_name,course_type,class_hour,credit} = req.body;
let sql = "insert into sys_course (course_name,course_type,class_hour,credit,create_time) values (?,?,?,?,?)";
db.query(
sql,
[course_name,course_type,class_hour,credit,create_time],
(err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
if (data.affectedRows > 0) {
res.send({
code: 200,
msg: "success",
sqlList:[
{
title:'通过course_name查询数据',
con:sql_01
},
{
title:'新增课程',
con:sql
},
],
});
} else {
res.send({
code: 202,
msg: "error",
});
}
}
);
} else {
res.send({
code: 500,
msg: "该课程名称已存在，不可重复添加！",
});
}
});
};
// 修改课程
exports.updateCourse = (req, res) => {
let {course_name,course_type,class_hour,credit,course_id} = req.body;
//通过id更新数据
let sql = "update sys_course set course_name = ?, course_type = ?, class_hour = ?, credit = ? where course_id = ?";
db.query(
sql,
[course_name,course_type,class_hour,credit,course_id],
(err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
if (data.changedRows > 0||data.affectedRows > 0) {
res.send({
code: 200,
msg: "success",
sqlList:[
{
title:'修改课程',
con:sql
},
]
});
} else {
res.send({
code: 202,
msg: "error",
});
}
}
);
};
//通过id删除课程
exports.delCourse = (req, res) => {
let {course_id} = req.query;
let sql = "delete from sys_course where find_in_set(course_id,?)";
db.query(sql,[course_id], (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
if (data.affectedRows > 0) {
res.send({
code: 200,
msg: "删除成功",
sqlList:[
{
title:'通过id删除课程 ',
con:sql
},
]
});
} else {
res.send({
code: 202,
msg: "error",
});
}
});
};
// -------------------------------------------------------------------根据专业id---------------------------------------------------------------------------------
// 根据专业id查询对应的课程
exports.getCourseBySpecialityId= (req, res) => {
let {course_id,course_name,course_type,pageNum,pageSize,dept_id} = req.query;
let totalNum = (parseInt(pageNum) - 1)*pageSize;
//获取符合条件的总数据条数
let sql_01 = `SELECT count(*) AS total FROM sys_course WHERE course_name like '%${course_name}%' and course_type like '%${course_type}%' and find_in_set('${dept_id}',speciality_ids)`;
if(course_id){
sql_01 = sql_01+ `and course_id like '${course_id}'`
}
db.query(sql_01, (err1, data1) => {
if (err1) {
return res.send("错误：" + err1.message);
}
let total = data1[0].total;
let sql = `(SELECT course_id FROM sys_course where course_name like '%${course_name}%' and course_type like '%${course_type}%' and find_in_set('${dept_id}',speciality_ids))`;
if(course_id){
sql = `(SELECT course_id FROM sys_course where course_name like '%${course_name}%' and course_type like '%${course_type}%' and course_id like '${course_id}' and find_in_set('${dept_id}',speciality_ids))`;
}
let sql_02 = `SELECT * FROM sys_course where course_id in ` +sql+ ` LIMIT ${totalNum}, ${pageSize}`;
db.query(sql_02, (err, data) => {
if (err) {
return res.send("错误：" + err.message);
}
// 返回数据
res.send({
rows:data,
total,
sqlList:[
{
title:'根据专业id查询课程总条数',
con:sql_01
},
{
title:'根据专业id查询对应的课程列表',
con:sql_02
},
]
});
});
});
};
// 将选中的课程course_id 中的speciality_ids 添加上当前专业id
exports.updateSpeciality = (req, res) => {
// 专业id, 课程id串
let {speciality_id,course_ids} = req.body;
let sql = `update sys_course set speciality_ids=concat(COALESCE(speciality_ids,''),',','${speciality_id}') where find_in_set(course_id,'${course_ids}')`;
db.query(
sql,
(err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
if (data.changedRows > 0||data.affectedRows > 0) {
res.send({
code: 200,
msg: "success",
sqlList:[
{
title:'给专业添加对应的课程',
con:sql
},
]
});
} else {
res.send({
code: 202,
msg: "error",
});
}
}
);
};
//通过course_ids删除对应的专业id,speciality_id
exports.delSpecialityById = (req, res) => {
// 专业id, 课程id串
let {speciality_id,course_ids} = req.body;
let sql = `update sys_course set speciality_ids=trim(both ',' from replace(concat(',', speciality_ids, ','), ',${speciality_id},', ',')) where find_in_set(course_id,'${course_ids}')`;
// console.log(sql,'sql')
db.query(sql, (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
if (data.affectedRows > 0) {
res.send({
code: 200,
msg: "删除成功",
sqlList:[
{
title:'根据speciality_id取消某专业对课程绑定 ',
con:sql
},
]
});
} else {
res.send({
code: 202,
msg: "error",
});
}
});
};
10. score.js
let db = require('../db/index')
let utils = require('../utils/utils')
let create_time = utils.nowDate().YMDHMS
let update_time = utils.nowDate().YMDHMS
// 查询班级列表
exports.getClassList = (req, res) => {
let { organization_type } = req.query
if (organization_type == undefined) {
organization_type = ''
}
let sql = `select * from sys_dept WHERE organization_type like '%${organization_type}%'`
db.query(sql, (err, data) => {
if (err) {
return res.send('错误：' + err.msg)
}
res.send({
data,
sqlList: [
{
title: '查询班级列表',
con: sql
}
]
})
})
}
//根据班级id,查询对应的人员列表
//查询用户列表
exports.getUserListByClassId = (req, res) => {
let { dept_id } = req.query
let role = '3' //学生角色
let sql = `SELECT * FROM sys_user where dept_id like '%${dept_id}%' and role = '${role}'`
// console.log(sql,'sql')
db.query(sql, (err, data) => {
if (err) {
return res.send('错误：' + err.message)
}
// 返回数据
res.send({
data,
sqlList: [
{
title: '查询学生列表',
con: sql
}
]
})
})
}
//根据班级id--》查询对应专业id-->查询对应的课程列表
exports.getCourseByClassId = (req, res) => {
// 班级id
let { dept_id } = req.query
let sql_01 = `select * from sys_dept WHERE dept_id = '${dept_id}'`
db.query(sql_01, (err, data) => {
if (err) {
return res.send('错误：' + err.msg)
}
// console.log(data,'data1')
// 专业id
let parent_id = data[0].parent_id
// console.log(parent_id,'parent_id')
let sql_02 = `SELECT * FROM sys_course where find_in_set('${parent_id}',speciality_ids)`
db.query(sql_02, (err, data) => {
if (err) {
return res.send('错误：' + err.message)
}
// console.log(data,'data2')
// 返回数据
res.send({
data,
sqlList: [
{
title: '根据班级id查询对应专业id',
con: sql_01
},
{
title: '根据专业id查询对应的课程列表',
con: sql_02
}
]
})
})
})
}
//查询学生成绩列表
exports.getStudentScoreList = (req, res) => {
let { nick_name, dept_id, sex, pageNum, pageSize, order, year } = req.query
let totalNum = (parseInt(pageNum) - 1) * pageSize
//获取符合条件的总数据条数
let sql_01 = `SELECT student_id AS total FROM sys_score a join sys_user b on a.student_id = b.user_id WHERE nick_name like '%${nick_name}%'`
//如果存在单位
if (dept_id) {
sql_01 += ` and dept_id = '${dept_id}'`
}
//如果存在性别
if (sex != undefined) {
sql_01 += ` and sex = '${sex}'`
}
//如果存在年份
if (year) {
sql_01 += ` and year = '${year}'`
}
sql_01 += ` group by student_id,year`
// console.log(sql_01, 'sql_01')
db.query(sql_01, (err1, data1) => {
if (err1) {
return res.send('错误：' + err1.message)
}
// console.log(data1,'data1')
let total = data1.length
// console.log(total,'total')
// ..................................................................................................................
let sql_02 = `SELECT student_id,nick_name,c.parent_id,d.dept_name as speciality_name ,c.dept_name,year FROM
sys_score a join sys_user b on a.student_id = b.user_id
join sys_dept c on b.dept_id = c.dept_id
join sys_dept d on c.parent_id = d.dept_id
WHERE nick_name like '%${nick_name}%'`
//如果存在单位
if (dept_id) {
sql_02 += ` and c.dept_id = '${dept_id}'`
}
//如果存在性别
if (sex != undefined) {
sql_02 += ` and sex = '${sex}'`
}
//如果存在年份
if (year) {
sql_02 += ` and year = '${year}'`
}
sql_02 += ` group by student_id,year LIMIT ${totalNum}, ${pageSize}`
// console.log(sql_02,'sql_02')
db.query(sql_02, (err, data) => {
if (err) {
return res.send('错误：' + err.message)
}
// console.log(data,'data---------')
// 返回数据
res.send({
rows: data,
total,
sqlList: [
{
title: '查询学生成绩总条数',
con: sql_01
},
{
title: '查询学生成绩列表数据',
con: sql_02
}
]
})
})
})
}
// 查询 学生成绩详情
exports.getStudentScoreDetail = (req, res) => {
let { student_id, year } = req.query
let sql = `
SELECT * FROM
sys_user b join sys_dept c on b.dept_id = c.dept_id
join sys_score a on a.student_id = b.user_id
WHERE user_id = '${student_id}' and year = '${year}'
`
// console.log(sql, 'sql')
db.query(sql, (err, data) => {
if (err) {
return res.send('错误：' + err.msg)
}
// console.log(data, 'data')
let obj = data[0]
// ...........................................................................................................
let sql_02 = `SELECT * FROM
sys_score a join sys_course b on a.course_id = b.course_id
WHERE student_id = '${student_id}' and year = '${year}'`
// sql_02 += ` group by student_id,year`
// console.log(sql_02, 'sql_02')
db.query(sql_02, (err, data) => {
if (err) {
return res.send('错误：' + err.message)
}
// console.log(data,'data---------')
// 返回数据
res.send({
data:{
...obj,
courseList:data,
},
sqlList: [
{
title: '查询学生信息',
con: sql
},
{
title: '查询学生课程信息',
con: sql_02
}
]
})
})
})
}
// 新增学生成绩
exports.addStudentScore = (req, res) => {
let { student_id, year, courseList } = req.body
let sql_01 = `select * from sys_score where student_id = '${student_id}' and year = '${year}'` //？用于占位
db.query(sql_01, (err1, data1) => {
if (err1) {
return res.send('错误：' + err1.msg)
}
let sqlList = [
{
title: '通过student_id+year查询数据',
con: sql_01
}
]
let successNum = 0
// console.log(data1, 'data1')
// console.log(data1.length, 'data1.length')
if (data1.length == 0) {
//该学年该学生成绩记录未存在
insertMore(req, res, sqlList, successNum)
} else {
res.send({
code: 500,
msg: '该学年该学生成绩已存在，不可重复添加！'
})
}
})
}
//批量循环插入数据
let insertMore = (req, res, sqlList, successNum) => {
let { student_id, year, courseList } = req.body
let item = courseList[successNum]
let sql =
'insert into sys_score (student_id,course_id,score,year,create_time) values (?,?,?,?,?)'
if (successNum == 0) {
sqlList.push({
title: '新增学生成绩',
con: sql
})
}
db.query(
sql,
[student_id, item.course_id, item.score, year, create_time],
(err, data) => {
if (err) {
return res.send('错误：' + err.msg)
}
// console.log(data, 'data')
// 返回数据
if (data.changedRows > 0 || data.affectedRows > 0) {
successNum++
if (successNum <= courseList.length - 1) {
insertMore(req, res, sqlList, successNum)
} else {
res.send({
code: 200,
msg: 'success',
sqlList
})
}
} else {
res.send({
code: 202,
msg: 'error'
})
}
}
)
}
// 修改学生成绩
exports.updateStudentScore = (req, res) => {
//判断该学年，该学生，该课程是否已经存在
let successNum = 0
let sqlList = [
{
title: '通过student_id+year+course_id查询数据',
con: ''
}
]
selectMore(req, res, sqlList,successNum)
};
//批量一一判断当前课程是否已经存在
let selectMore = (req, res, sqlList,successNum) => {
let { student_id, year, courseList } = req.body
let item = courseList[successNum]
let sql_01 = `select * from sys_score where student_id = '${student_id}' and year = '${year}' and course_id = '${item.course_id}'` //？用于占位
db.query(sql_01, (err1, data1) => {
if (err1) {
return res.send('错误：' + err1.msg)
}
if(successNum == 0){
sqlList[0].con = sql_01
}
// console.log(data1.length, 'select长度')
if (data1.length == 0) { //批量一一新增
//该学年该学生该课程成绩记录未存在
addHandle(req, res, sqlList, successNum)
} else { //批量一一修改
editHandle(req, res, sqlList, successNum)
}
})
}
// 一一新增：
let addHandle = (req, res, sqlList, successNum) => {
let { student_id, year, courseList } = req.body
let item = courseList[successNum]
let sql =`insert into sys_score (student_id,course_id,score,year,create_time) values ('${student_id}','${item.course_id}','${item.score}','${year}','${create_time}')`
sqlList.push({
title: '新增学生成绩',
con: sql
})
// console.log(successNum,'successNum----add')
// console.log(sql,'sql----add')
// console.log(sqlList,'sqlList----add')
db.query(
sql,
(err, data) => {
if (err) {
return res.send('错误：' + err.msg)
}
// console.log(data, 'data---add')
// 返回数据
if (data.changedRows > 0 || data.affectedRows > 0) {
successNum++
if (successNum <= courseList.length - 1) {
selectMore(req, res,sqlList, successNum)
} else {
res.send({
code: 200,
msg: 'success',
sqlList
})
}
} else {
res.send({
code: 202,
msg: 'error'
})
}
}
)
}
// 一一修改：
let editHandle = (req, res, sqlList, successNum) => {
let { student_id, year, courseList } = req.body
let item = courseList[successNum]
let sql =`update sys_score set score = '${item.score}' where student_id ='${student_id}' and course_id ='${item.course_id}'`
sqlList.push({
title: '修改学生成绩',
con: sql
})
// console.log(successNum,'successNum----edit')
// console.log(sql,'sql----edit')
// console.log(sqlList,'sqlList----edit')
db.query(
sql,
(err, data) => {
if (err) {
return res.send('错误：' + err.msg)
}
// console.log(data, 'data------edit')
// 返回数据
if (data.changedRows > 0 || data.affectedRows > 0) {
successNum++
if (successNum <= courseList.length - 1) {
selectMore(req, res, sqlList,successNum)
} else {
res.send({
code: 200,
msg: 'success',
sqlList
})
}
} else {
res.send({
code: 202,
msg: 'error'
})
}
}
)
}
// 删除学生成绩信息
exports.delStudentScore = (req, res) => {
let {student_ids,year} = req.query;
let sql = "delete from sys_score where find_in_set(student_id,?) and year = ?";
db.query(sql,[student_ids,year], (err, data) => {
if (err) {
return res.send("错误：" + err.msg);
}
// 返回数据
if (data.affectedRows > 0) {
res.send({
code: 200,
msg: "删除成功",
sqlList:[
{
title:'通过id删除学生成绩信息 ',
con:sql
},
]
});
} else {
res.send({
code: 202,
msg: "error",
});
}
});
};
11. student_database.sql
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80033
Source Host : localhost:3306
Source Schema : student_database
Target Server Type : MySQL
Target Server Version : 80033
File Encoding : 65001
Date: 27/06/2023 10:46:42
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sys_course
-- ----------------------------
DROP TABLE IF EXISTS `sys_course`;
CREATE TABLE `sys_course` (
`course_id` int NOT NULL AUTO_INCREMENT COMMENT '课程号',
`course_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '课程名',
`class_hour` float NULL DEFAULT NULL COMMENT '学时',
`credit` float NULL DEFAULT NULL COMMENT '学分',
`course_type` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '课程类型：1公共课 2专业基础课 3专业核心课 4实践类课程',
`speciality_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '对应专业id串',
`create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 59 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for sys_dept
-- ----------------------------
DROP TABLE IF EXISTS `sys_dept`;
CREATE TABLE `sys_dept` (
`dept_id` bigint NOT NULL AUTO_INCREMENT COMMENT '组织id',
`parent_id` bigint NULL DEFAULT 0 COMMENT '父组织id',
`ancestors` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '祖级列表',
`dept_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '组织名称',
`order_num` int NULL DEFAULT 0 COMMENT '显示顺序',
`organization_type` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '组织类型（1学院 2专业 3班级）',
`del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '删除标志（1代表存在 2代表删除）',
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '创建者',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '更新者',
`update_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 241 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '部门表' ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for sys_score
-- ----------------------------
DROP TABLE IF EXISTS `sys_score`;
CREATE TABLE `sys_score` (
`score_id` int NOT NULL AUTO_INCREMENT COMMENT '成绩id',
`student_id` bigint NOT NULL COMMENT '学生id',
`course_id` int NOT NULL COMMENT '课程Id',
`score` float NULL DEFAULT NULL COMMENT '成绩',
`year` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`score_id`) USING BTREE,
INDEX `student_id`(`student_id` ASC) USING BTREE,
INDEX `course_id`(`course_id` ASC) USING BTREE,
CONSTRAINT `course_id` FOREIGN KEY (`course_id`) REFERENCES `sys_course` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `student_id` FOREIGN KEY (`student_id`) REFERENCES `sys_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 43 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`user_id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门ID',
`user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号',
`nick_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户昵称',
`phonenumber` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '手机号码',
`sex` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '2' COMMENT '用户性别（0男 1女 2未知）',
`role` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '角色（1管理员2教师3学生）',
`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '密码',
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '创建者',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '更新者',
`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`user_id`) USING BTREE,
INDEX `dept_id`(`dept_id` ASC) USING BTREE,
CONSTRAINT `dept_id` FOREIGN KEY (`dept_id`) REFERENCES `sys_dept` (`dept_id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE = InnoDB AUTO_INCREMENT = 192 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC;
SET FOREIGN_KEY_CHECKS = 1;
注意：1. 前端界面文件，数据库sql文件（包括结构和数据）可在代码包中查看。2.也可以直接在码云下载（下载地址）。
六、在test-demo/src目录下，打开命令行工具， 运行 node app.js启动服务，成功后，浏览器打开如下地址：http://localhost/login
扫一扫
1165
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
