node学习—数据查询

一、数据查询

前面所学习的模拟数据和数据抓取都是往数据库里存放数据，那么当我们真正使用时又该怎样去拿取数据呢。

1、管理员查询

1.单个查询

//services/adminService
exports.login = async function (loginId, loginPwd){
  const result = await Admin.findOne({
    where: {
      loginId,
      loginPwd,
    },
  });//sql是不区分大小写的，所以这里要自己检验一下
  if (result && result.loginId === loginId && result.loginPwd === loginPwd) {
    return result.toJSON();//返回JSON格式
  }
  return null;
}

//index
require("./models/relation");
const adminSer = require("./services/adminService");
adminSer.login('xiugai','adsfasdfd').then((res)=>{
  console.log(res);
});

如果没有result && result.loginId === loginId && result.loginPwd === loginPwd判断，那么无法区分大小写

判断后就能避免大小写的问题

2.查id

//services/adminService
exports.getAdminById = async function (id) {
  const result = await Admin.findByPk(id);
  if (result) {
    return result.toJSON();
  }
  return null;
};

//index
require("./models/relation");
const adminSer = require("./services/adminService");
adminSer.getAdminById(3).then((res)=>{
  console.log(res)
})

这里作者发现查Number的类型3和查String类型的‘3’都能查到结果哦！

3.全部查询

//services/adminService
exports.getAdmins = async function () {
  const result = await Admin.findAll();
  return JSON.parse(JSON.stringify(result));
};

//index
const adminSer = require("./services/adminService");
adminSer.getAdmins().then((res)=>{//全部查询
  console.log(res);
})

二、学生查询

前面管理员查询，我们能够一次性全部查询。但是一旦对于其他类的数据，可能成千上万乃至更多，那么此时就不能一次性全部查询，服务器撑不起。

1、分页查询

1.方法1

//services/studentService
const Student = require("../models/Student");
exports.getStudents = async function(page = 1,limit = 10){
  const results = await Student.findAll({
    offset: (page-1)*limit, //隔多少查
    limit: +limit //查多少个
  });
  const total = await Student.count();//全部数据数量
  const datas = JSON.parse(JSON.stringify(results));//扁平化数据
  return {
    total,
    results
  }
}

//index
const stuSer = require("./services/studentService");
require("./models/relation");

stuSer.getStudents().then((res)=>{
  console.log(res)
});

2.方法二

//services/studentService
const Student = require("../models/Student");
exports.getStudents = async function(page = 1,limit = 10){
  const result = await Student.findAndCountAll({
    offset: (page - 1)*limit,
    limit: +limit
  });
  return {
    total: result.count,
    datas: JSON.parse(JSON.stringify(result.rows))
  }
}

2、查询需求字段

//services/studentService
const Student = require("../models/Student");
exports.getStudents = async function(page = 1,limit = 10){
  const result = await Student.findAndCountAll({
  	attributes: ["id", "name", "sex", "ClassId"],//需要的字段
    offset: (page - 1)*limit,
    limit: +limit
  });
  return {
    total: result.count,
    datas: JSON.parse(JSON.stringify(result.rows))
  }
}

3、定向查询

查询名中含“平”的女同学

//services/studentService
const { Op } = require("sequelize");
const Student = require("../models/Student");
exports.getStudents = async function(page = 1,limit = 10,sex = -1,name = ""){
  const where = {};
  if (sex !== -1) {
    where.sex = !!sex;
  }
  if (name) {
    where.name = {
      [Op.like]: `%${name}%`,//模糊查询
    };
  }
  const result = await Student.findAndCountAll({
    attributes: ["id", "name", "sex", "ClassId"],
    where,
    offset: (page - 1)*limit,
    limit: +limit
  });
  return {
    total: result.count,
    datas: JSON.parse(JSON.stringify(result.rows))
  }
}

//index
const stuSer = require("./services/studentService");
require("./models/relation");

stuSer.getStudents(1, 10, false, "平").then((r) => {
  console.log(r);
});

如果需要查询关联的表，那么就需要再const Class = require("../models/Class");，然后在参数中include: [Class],

博主开始运营自己的公众号啦，感兴趣的可以关注“飞羽逐星”微信公众号哦，拿起手机就能阅读感兴趣的文章啦！