一、数据查询
前面所学习的模拟数据和数据抓取都是往数据库里存放数据,那么当我们真正使用时又该怎样去拿取数据呢。
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],
博主开始运营自己的公众号啦,感兴趣的可以关注“飞羽逐星”微信公众号哦,拿起手机就能阅读感兴趣的文章啦!