mysql——sequelize浅学浅记

一、sequelize----创建连接

1、sequelize——ORM工具

• ORM——Object Relational Mapping（对象关系映射）
• 建模（外键） & 同步到数据库
• 增删改查 & 连表查询

2、ORM

• 数据表，用JS中的模型（class或对象）代替
• 一条或多条记录，用JS中一个对象或数组代替
• ​sql语句，用对象方法代替

3、创建连接

const Sequelize = require('sequelize');
const conf = {
    host: 'localhost',
    dialect: 'mysql'
};
const seq = new Sequelize('数据库名', '用户名', '密码', conf);
// 测试连接
seq.authenticate().then(() => {
    console.log('ok');
}).catch(
    err => {
        console.log(err);
    }
)

二、sequelize----创建模型

（1）创建模型

const Sequelize = require('sequelize');
const seq = require('./seq');

// 创建User模型
const User = seq.define('user', {
    userName: {
        type: Sequelize.STRING,
        allowNull: false
    },
    password: {
        type: Sequelize.STRING,
        allowNull: false
    },
    nickName: {
        type: Sequelize.STRING,
    }
});

module.exports = {
    User
}

（2）同步到数据库

const seq = require('./seq');
require('./model');

// 测试连接
seq.authenticate().then(() => {
    console.log('conn ok');
}).catch(
    err => {
        console.log(err);
    }
)

// 执行同步 force: true（如果有这个表则删除，重新建表）
seq.sync({ force: true }).then(() => {
    console.log('sync ok');
    process.exit();
}).catch(err => {
    console.log(err);
})

三、sequelize----创建外键关联

const Sequelize = require('sequelize');
const seq = require('./seq');

// 创建User模型
const User = seq.define('user', {
    username: {
        type: Sequelize.STRING,
        allowNull: false
    },
    password: {
        type: Sequelize.STRING,
        allowNull: false
    },
    nickname: {
        type: Sequelize.STRING,
    }
});

// 创建Blog模型
const Blog = seq.define('blog', {
    title: {
        type: Sequelize.STRING,
        allowNull: false
    },
    content: {
        type: Sequelize.STRING,
        allowNull: false
    },
    userId: {
        type: Sequelize.INTEGER,
        allowNull: false,
    }
});

// 外键关联
Blog.belongsTo(User, {
    // 注意userId需要用引号包起来
    foreignKey: 'userId'
});
User.hasMany(Blog, {
    foreignKey: 'userId'
});

module.exports = {
    User,
    Blog
}

四、sequelize----插入数据

const { User, Blog } = require('./model');

!(async function () {
    // 创建用户
    const zhangsan = await User.create({
        username: 'zhangsan',
        password: '123',
        nickname: '张三'
    });
    const zhangsanId = zhangsan.dataValues.id;
    // 创建博客
    const blog1 = await Blog.create({
        title: '标题1',
        content: '内容1',
        userId: zhangsanId
    })
    console.log(blog1.dataValues);

    const lisi = await User.create({
        username: 'lisi',
        password: '123',
        nickname: '李四'
    });
    const lisiId = lisi.dataValues.id;

    const blog2 = await Blog.create({
        title: '标题1',
        content: '内容1',
        userId: lisiId
    })
    console.log(blog2.dataValues);
})()

五、sequelize----查询数据

const { User, Blog } = require('./model');

!(async function () {
    // // 查询一条数据
    // const zhangsan = await User.findOne({
    //     where: {
    //         username: 'zhangsan'
    //     }
    // })
    // console.log('zhangsan', zhangsan.dataValues);

    // 查询特定的列
    // const zhangsan = await User.findOne({
    //     attributes: ['username', 'nickname'],
    //     where: {
    //         username: 'zhangsan'
    //     }
    // });
    // console.log('zhangsan', zhangsan.dataValues);

    // 查询列表
    // const zhangsanBlogList = await Blog.findAll({
    //     where: {
    //         userId: 1
    //     },
    //     order: [
    //         ['id', 'desc']
    //     ]
    // });
    // console.log('zhangsanBlogList', zhangsanBlogList.map(blog => blog.dataValues));

    // 分页
    // const blogList = await Blog.findAll({
    //     limit: 2,  // 限制多少条
    //     offset: 0, // 跳过多少条
    //     order: [
    //         ['id', 'desc']
    //     ]
    // });
    // console.log('blogList', blogList.map(blog => blog.dataValues));

    // 查询总数
    const blogAndCount = await Blog.findAndCountAll({
        limit: 2,
        offset: 2
    });
    console.log('blogAndCount',
        blogAndCount.count, // 总数
        blogAndCount.rows.map(blog => blog.dataValues)
    )
    // 连表查询1
    const blogListWithUser = await Blog.findAndCountAll({
        order: [
            ['id', 'desc']
        ],
        include: [
            {
                model: User,
                attributes: ['username', 'nickname'],
                where: {
                    username: 'zhangsan'
                }
            }
        ]
    })
    console.log('blogListWithUser',
        blogListWithUser.count,
        blogListWithUser.rows.map(blog => {
            const blogVal = blog.dataValues;
            blogVal.user = blog.user.dataValues;
            return blogVal;
        })
    );

    // 连表查询2
    const userListWithBlog = await User.findAndCountAll({
        order: [
            ['id', 'desc']
        ],
        include: [
            {
                model: Blog,
            }
        ]
    })
    console.log('userListWithBlog',
        userListWithBlog.count,
        userListWithBlog.rows.map(user => {
            const userVal = user.dataValues;
            userVal.blogs = userVal.blogs.map(blog => blog.dataValues);
            return userVal;
        })
    );
})()

六、sequelize----更新和删除数据

const { User, Blog } = require('./model');

!(async function () {
    // 更新一条数据
    const updateZhangsan = await User.update({
        username: 'zhangsan1111'
    }, {
        where: {
            username: 'zhangsan'
        }
    });

    console.log('updateZhangsan', updateZhangsan);
})()

const { User, Blog } = require('./model');

!(async function () {
    // 删除一条微博
    // const deleteRes = await Blog.destroy({
    //     where: {
    //         id: 4
    //     }
    // });
    // console.log('deleteRes', deleteRes);

    // 删除一个用户
    const deleteUser = await User.destroy({
        where: {
            id: 1
        }
    });
    console.log('deleteUser', deleteUser);
})()

七、sequelize----线上连接池

const Sequelize = require('sequelize');

const conf = {
    host: 'localhost',
    dialect: 'mysql'
};

// 线上环境——使用连接池
conf.pool = {
    max: 5,  // 连接的最大数量
    min: 0,  // 最小数量
    idle: 10000  // 如果一个连接池10s之内没有被使用，则释放
};

const seq = new Sequelize('koa2_weibo_db', 'root', '419616and6LOVEZ', conf);

module.exports = seq;