node学习笔记之通过sequelize对mysql进行建表、联表查询

前期准备：

npm install sequelize mysql2 --save

1.建立数据库链接:src/db/sequelize.js

const { Sequelize } = require("sequelize");


const seq = new Sequelize('数据库名称', '数据库用户名', '密码', {
    host: 'localhost',//主机地址
    dialect: "mysql",
});

// 测试数据库连接
// seq
//     .authenticate()
//     .then(() => {
//         console.log("数据库链接成功");
//     })
//     .catch((err) => {
//         console.log("数据库链接失败", err);
//     });
module.exports = seq;

2.建表：这里只提供建表的代码，想看向表里插入数据、实现具体业务逻辑的同学可以移步我上一篇文章~~~：B站杰哥node+koa+mysql项目实战学习笔记_sinat_38628334的博客-CSDN博客

用户表：src/model/user.model.js

const { DataTypes } = require("sequelize"); //https://www.sequelize.com.cn/core-concepts/model-basics
const seq = require("../db/sequelize");
// 创建模型   seq.define('User',)会自动生成复数形式表名users表
const User = seq.define(
    "User_table", {
        user_name: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true,
            comment: "用户名，唯一",
        },
        user_pass: {
            type: DataTypes.CHAR,
            allowNull: false,
            unique: false,
            comment: "用户密码",
        },
        is_admin: {
            type: DataTypes.BOOLEAN,
            allowNull: false,
            unique: false,
            defaultValue: 0,
            comment: "用户类型标志，0 普通用户  1管理员",
        },
    }, {
        timestamps: false, //不会自动生成 createdAt和updatedAt字段
    }
);

// User.sync({ force: true }); //force:true 数据库存在User_tables表则强制删除原表，新建

module.exports = User

建表结果：

 

商品分类表：src/model/goodsClassify.model.js

const { DataTypes } = require("sequelize"); //https://www.sequelize.com.cn/core-concepts/model-basics
const seq = require("../db/sequelize");
const GoodsClassify = seq.define(
    "goods_classify", {
        id: {
            type: DataTypes.UUID,
            primaryKey: true,
            defaultValue: DataTypes.UUIDV4
        },
        classify_name: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: false,
            comment: "分类名称",
        },
        is_used: {
            type: DataTypes.BOOLEAN,
            allowNull: false,
            defaultValue: true,
            comment: "上架、下架",
        },
    }
);

// GoodsClassify.sync({ force: true }); //force:true 数据库存在User_tables表则强制删除原表，新建

module.exports = GoodsClassify

 建表结果：

商品表：src/model/goods.model.js

const { DataTypes } = require("sequelize"); //https://www.sequelize.com.cn/core-concepts/model-basics
const { model } = require("../db/sequelize");
const seq = require("../db/sequelize");
const GoodsClassify = require("../model/goodsClassify.model");
const User = require("./user.model")

const Goods = seq.define(
    "Goods_table", {
        id: {
            type: DataTypes.UUID,
            primaryKey: true,
            defaultValue: DataTypes.UUIDV4
        },
        goods_name: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: false,
            comment: "商品名称",
        },
        classifyId: {
            type: DataTypes.CHAR,
            allowNull: true,
            unique: false,
            comment: "商品分类名称",
        },
        goods_price: {
            type: DataTypes.CHAR,
            allowNull: false,
            unique: false,
            comment: "商品价格",
        },
        goods_num: {
            type: DataTypes.CHAR,
            allowNull: false,
            unique: false,
            defaultValue: 0,
            comment: "商品数量",
        },
        add_by: {
            type: DataTypes.INTEGER,
            allowNull: true,
            unique: false,
            defaultValue: 8,
            comment: "添加人",
        },
        goods_img: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: false,
            defaultValue: 0,
            comment: "商品图片",
        },
        is_used: {
            type: DataTypes.BOOLEAN,
            allowNull: false,
            defaultValue: true,
            comment: "上架、下架",
        },
    }, {
        timestamps: false, //不会自动生成 createdAt和updatedAt字段
    }
);

// Goods.sync({ alter: true }); //force:true 数据库存在User_tables表则强制删除原表，新建  alter: true 在表里新增字段后更新表结构

module.exports = Goods

 建表结果：

3.单表查询：根据classifyId查询当前分类下的所有商品

查询结果格式封装：listRes.js

const { goodsParamError } = require("../constant/errorr.type");
const listRes = async(pageNum, pageSize, count, row) => {
    const lastPage = Math.ceil(count / pageSize);

    let prePage = null;
    if (pageNum > 1) {
        prePage = pageNum - 1;
    }
    return {
        lastPage,
        prePage,
        pageNum,
        pageSize,
        total: count
    };
};
module.exports = {
    listRes,
};

    async findGoodsByClassifyId(pageNum, pageSize) {
        try {
            const count = await Goods.count();
            const offset = (pageNum - 1) * pageSize;
            const rows = await Goods.findAll({
                offset: offset,
                limit: Number(pageSize),
                raw: true,
                where: { classifyId: ctx.request.query.classifyId},
            });
            console.log("查询的结果", rows);
            let returnObj = await listRes(pageNum, pageSize, count);
            returnObj.list = rows;
            ctx.body = {
                code: 0,
                message: "获取数据成功",
                result: returnObj,
            };
        } catch (error) {
            console.error("查询出错了", error);
        }
    }

查询结果：

 

4.联表查询：查询所有商品，每个商品需要有所属分类的名称和添加人

在goods.model.js建立goods表与user表、goodsClassify表的外键关系：

const GoodsClassify = require("../model/goodsClassify.model");
const User = require("./user.model")
Goods.belongsTo(GoodsClassify, { foreignKey: 'classifyId', targetKey: 'id', as: "aa" })//as:起别名，可要可不要
Goods.belongsTo(User, { foreignKey: 'add_by', targetKey: 'id', as: "add" })

方式一 

    async findGoods(pageNum, pageSize) {
        try {
            const count = await Goods.count();
            const offset = (pageNum - 1) * pageSize;
            const row = await Goods.findAll({
                offset: offset,
                limit: Number(pageSize),
                attributes: ['goods_name', ['id', 'goods_id'], 'goods_price'],//想给用户返回的字段取别名的话使用['表里字段名','返给用户的别名']
                include: [{
                        attributes: ['classify_name'],
                        model: GoodsClassify,
                        as: "aa" // 需要与前面建立外键关系时取的名称一致，如果前面取了别名，那这里一定也要写
                    },
                    {
                        attributes: [
                            ['user_name', 'addby']
                        ],
                        model: User,
                        as: "add"
                    }
                ],
            });
            let returnObj = await listRes(pageNum, pageSize, count);
            returnObj.list = row;
            ctx.body = {
                code: 0,
                message: "获取数据成功",
                result: returnObj,
            };
        } catch (error) {
            console.error(error);
        }
    }

查询结果

方式二：观察返回的结果，关注row:true这个属性的设置

    async findGoods(pageNum, pageSize) {
        try {
            const count = await Goods.count();
            const offset = (pageNum - 1) * pageSize;
            const row = await Goods.findAll({
                offset: offset,
                limit: Number(pageSize),
                raw: true,//关注这个属性，为true时可以把从其他表查询的结果跟从本表查询出来的结果同一级返回
                attributes: ['goods_name', ['id', 'goods_id'], 'goods_price'],
                include: [{
                        attributes: ['classify_name'],
                        model: GoodsClassify,
                        as: "aa"
                    },
                    {
                        attributes: [
                            ['user_name', 'addby']
                        ],
                        model: User,
                        as: "add"
                    }
                ],
            });
            let returnObj = await listRes(pageNum, pageSize, count);
            returnObj.list = row;
            ctx.body = {
                code: 0,
                message: "获取数据成功",
                result: returnObj,
            };
        } catch (error) {
            console.error(error);
        }
    }

查询结果

 

方式三：使用 Sequelize.col()，对比和方式二返回结果的区别----他表的字段前面没有表名的标志了

const { Sequelize } = require("sequelize")
    
async findGoods(pageNum, pageSize) {
        try {
            const count = await Goods.count();
            const offset = (pageNum - 1) * pageSize;
            const row = await Goods.findAll({
                offset: offset,
                limit: Number(pageSize),
                raw: true,
                attributes: ['goods_name', ['id', 'goods_id'], 'goods_price', Sequelize.col('aa.classify_name'), Sequelize.col('add.user_name', 'addby')],
                include: [{
                        attributes: [],
                        model: GoodsClassify,
                        as: "aa"
                    },
                    {
                        attributes: [],
                        model: User,
                        as: "add"
                    }
                ],
            });
            let returnObj = await listRes(pageNum, pageSize, count);
            returnObj.list = row;
            ctx.body = {
                code: 0,
                message: "获取数据成功",
                result: returnObj,
            };
        } catch (error) {
            console.error(error);
        }
    }

查询结果