使用node+express+mysql实现一个简单的后台管理（增删改查）
短信预约 -IT技能 免费直播动态提醒
目录
一、新建目录，如：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: "该组织机构存在下级，不可直接删除！", }); } });};// 更新祖级idlet 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 添加上当前专业idexports.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().YMDHMSlet 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
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;注意：前端界面文件，数据库sql文件（包括结构和数据）可在代码包中查看。
六、在test-demo/class="lazy" data-src目录下，打开命令行工具， 运行 node app.js启动服务，成功后，浏览器打开如下地址：http://localhost/login
来源地址：https://blog.csdn.net/weixin_41549971/article/details/131382167
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/46.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
