使用node+express+mysql实现一个简单的后台管理（增删改查）

目录

 一、新建目录，如：test-demo，使用命令行初始化

 二、安装相关依赖

 三、自行安装mysql后，安装Navicat 16 for MySQL图形界面工具， 创建数据库，导入student_database.sql文件

 四、简单看下目录结构

 五、代码相关 （主要文件如下）

 1. app.js

 2. db/index.js

 3. router.js

 4. utils.js

 5. login.js 

 6. home.js   

 7. user.js 

 8. dept.js   

 9. course.js   

 10. score.js 

 11. student_database.sql

 六、在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: "该组织机构存在下级，不可直接删除！",      });    }  });};// 更新祖级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/src目录下，打开命令行工具，  运行 node app.js启动服务，成功后，浏览器打开如下地址：http://localhost/login

 

 

来源地址：https://blog.csdn.net/weixin_41549971/article/details/131382167