文档：

Sequelize基础查询：https://github.com/demopark/sequelize-docs-Zh-CN/blob/master/core-concepts/model-querying-basics.md
Sequelize查询api：https://github.com/demopark/sequelize-docs-Zh-CN/blob/master/core-concepts/model-querying-finders.md
Sequelize关联查询:https://github.com/demopark/sequelize-docs-Zh-CN/blob/master/advanced-association-concepts/eager-loading.md#eager-loading—-%E9%A2%84%E5%85%88%E5%8A%A0%E8%BD%BD

基础查询

查询整张表

SELECT * FROM ...

const Admin = require('../models/Admin');
/**
 * 查询所有管理员
 * @returns 
 */
exports.getAdmins = async function() {
  const res = await Admin.findAll() // 等同于 SELECT * FROM ...
  // 将返回来的嵌套对象拍平，然后在转化为正常对象
  return JSON.parse(JSON.stringify(res))
}

SELECT 查询特定属性

可以查看顶部文档

where 查询

检测账号密码登录

const Admin = require('../models/Admin');
/**
 * 登录
 * 并没有对密码就行md5加密 以及返回的数据不能包含密码
 * @param {*} loginId 
 * @param {*} loginPwd 
 * @returns 
 */
exports.login = async function (loginId, loginPwd) {
  const res = await Admin.findOne({
    where: {
      loginId,
      loginPwd,
    },
  }); //等效于 SELECT * FROM admin WHERE loginId = loginId AND  loginPwd = loginPwd
  /**mysql 不区分大小写 */
  if (res && loginId === res.loginId && loginPwd === res.loginPwd) {
    return res.toJSON();
  } else {
    return null;
  }
};

对密码进行加密且控制返回值

const Admin = require('../models/Admin');
const md5 = require("md5");
exports.login = async function (loginId, loginPwd) {
  loginPwd = md5(loginPwd)
  const result = await Admin.findOne({
    where: {
      loginId,
      loginPwd,
    },
  });
  if (result && result.loginId === loginId) {
    const {id ,loginId} =  res.toJSON();
    return {
      id,
      loginId
    }
  }
  return null;
};

运算符

东西太多且零碎
文档地址：https://github.com/demopark/sequelize-docs-Zh-CN/blob/master/core-concepts/model-querying-basics.md

分页查询

使用 limit 和 offset 参数可以进行限制/分页：

// 提取10个实例/行
Project.findAll({ limit: 10 });
// 跳过8个实例/行
Project.findAll({ offset: 8 });
// 跳过5个实例,然后获取5个实例
Project.findAll({ offset: 5, limit: 5 });

联表查询

单个关联元素

exports.getStuall = async function() {
  const res = await Student.findAll({
    // attributes: ['id', 'name', 'sex', 'birthdady'],
    include: [Class],
  });
  return JSON.parse(JSON.stringify(res));
}
require('./models/relation'); // 必须有这个声明关系 不然查不到
const stuServ = require('./services/studentService');
stuServ.getStuall().then(res => {
  console.log(res)
})

输出

Executing (default): SELECT `Student`.`id`, `Student`.`name`, `Student`.`birthdady`, `Student`.`sex`, `Student`.`mobile`, `Student`.`deletedAt`, `Student`.`ClassId`, `Class`.`id` AS `Class.id`, `Class`.`name` AS `Class.name`, `Class`.`openDate` AS `Class.openDate`, `Class`.`deletedAt` AS `Class.deletedAt` FROM `Students` AS `Student` LEFT OUTER JOIN `Classes` AS `Class` ON `Student`.`ClassId` = `Class`.`id` AND (`Class`.`deletedAt` IS NULL) WHERE (`Student`.`deletedAt` IS NULL);
[
  {
    id: 1,
    name: '何杰',
    birthdady: '1988-05-24',
    sex: false,
    mobile: '13773242951',
    deletedAt: null,
    ClassId: 3,
    Class: {
      id: 3,
      name: '挖掘机炒菜 3 班',
      openDate: '2017-04-18',
      deletedAt: null
    }
  },
  {
    id: 2,
    name: '白秀英',
    birthdady: '1995-12-30',
    sex: true,
    mobile: '13821143636',
    deletedAt: null,
    ClassId: 10,
    Class: {
      id: 10,
      name: '挖掘机炒菜 10 班',
      openDate: '2010-04-24',
      deletedAt: null
    }
  },
  {
    id: 3,
    name: '任敏',
    birthdady: '2002-12-24',
    sex: false,
    mobile: '13535837226',
    deletedAt: null,
    ClassId: 14,
    Class: {
      id: 14,
      name: '挖掘机炒菜 14 班',
      openDate: '2002-02-20',
      deletedAt: null
    }
  },
  {
    id: 4,
    name: '孔娜',
    birthdady: '1999-10-04',
    sex: false,
    mobile: '13729124141',
    deletedAt: null,
    ClassId: 14,
    Class: {
      id: 14,
      name: '挖掘机炒菜 14 班',
      openDate: '2002-02-20',
      deletedAt: null
    }
  },
  {
    id: 5,
    name: '邵娜',
    birthdady: '1986-08-19',
    sex: false,
    mobile: '13534267583',
    deletedAt: null,
    ClassId: 7,
    Class: {
      id: 7,
      name: '挖掘机炒菜 7 班',
      openDate: '1971-09-27',
      deletedAt: null
    }
  }
]

复杂查询示例

const Book = require("../models/Book");
const { Op } = require("sequelize");
/**
 * 模糊查询书名或者作者名,且分页
 * @param {*} page 
 * @param {*} limit 
 * @param {*} keywords 
 * @returns 
 */
exports.getBooks = async function (page = 1, limit = 10, keywords = "") {
  const result = await Student.findAndCountAll({
    attributes: ["id", "name", "sex", "birthdady"],
    where: {
      [Op.or]: [
        //里面的两个条件是或者关系
        {
          //条件1：姓名模糊匹配关键词
          name: {
            [Op.like]: `%${keywords}%`,
          },
        },
        {
          //条件2：作者模糊匹配关键词
          author: {
            [Op.like]: `%${keywords}%`,
          },
        },
      ],
    },
    offset: (page - 1) * limit,
    limit: +limit,
  });
  return {
    total: result.count,
    datas: JSON.parse(JSON.stringify(result.rows)),
  };
};

const Student = require("../models/Student");
const { Op } = require("sequelize");
const Class = require("../models/Class");
/**
* 查询符合条件的学生，且附带班级信息
*/
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", "birthdady"],
    where,
    include: [Class], // 联表查询
    offset: (page - 1) * limit,
    limit: +limit,
  });
  return {
    total: result.count,
    datas: JSON.parse(JSON.stringify(result.rows)),
  };
};

只查询主键

findByPk 方法使用提供的主键从表中仅获得一个条目.

/**
 * 通过主键查询管理员
 * @param {*} id 
 * @returns 
 */
exports.getAdminById = async function (id) {
  const result = await Admin.findByPk(id);
  if (result) {
    return result.toJSON();
  }
  return null;
};

只查询一条数据

findOne 方法获得它找到的第一个条目(它可以满足提供的可选查询参数).
示例看上面where查询的示例

前端杂记

数据查询

文档：

基础查询

查询整张表

SELECT 查询特定属性

where 查询

运算符

分页查询

联表查询

复杂查询示例

只查询主键

只查询一条数据