sequelize的简单使用（增、删、查、改）

一、说明

使用的是mysql数据库，数据库叫做koa2_weibo_db，其中一共有2张表，一张blogs表，一张users表

• blogs
  

• users
  

二、安装

npm i mysql2 sequelize -d 

三、创建连接

const Sequelize = require('sequelize')
const conf = {
    host:'localhost',
    dialect:'mysql'
}
const seq = new Sequelize('koa2_weibo_db','root','root',conf)
module.exports = seq

四、创建模型

//引入模块
const Sequelize = require('sequelize')
const seq = require('./seq')

//创建 User 模型。数据表的名字是users
const  User  = seq.define('user',{
    //id会自动创建，并且自动递增
    userName:{
        type:Sequelize.STRING,//vachar(255)
        allowNull:false
    },
    password:{
        type:Sequelize.STRING,//vachar(255)
        allowNull:false
    },
    nickName:{
        type:Sequelize.STRING,
        commit:'昵称'
    }
})

//创建 Blog 模型。数据表的名字是blogs
const  Blog  = seq.define('blogs',{
    //id会自动创建，并且自动递增
    title:{
        type:Sequelize.STRING,//vachar(255)
        allowNull:false
    },
    content:{
        type:Sequelize.TEXT,
        allowNull:false
    },
    userId:{
        type:Sequelize.INTEGER,
        allowNull:false
    }
})
module.exports = {
    User,
    Blog
}

五、增加

 const { Blog, User } = require('./mode')
 
 !(async function(){
     //创建用户
     const zhangsan = await User.create({
         userName:'zhangsan',
         password:'123',
         nickName:'张三'
     })
     const zhangsanId = zhangsan.dataValues.id 
     console.log('zhangshan:',zhangsan.dataValues)

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

    const blog1 = await Blog.create({
        title:'标题1',
        content:'内容1',
        userId:zhangsanId
    })
    const blog2 = await Blog.create({
        title:'标题2',
        content:'内容2',
        userId:zhangsanId
    })
    const blog3 = await Blog.create({
        title:'标题3',
        content:'内容3',
        userId:lisiId
    })
    const blog4 = await Blog.create({
        title:'标题4',
        content:'内容4',
        userId:lisiId
    })
 })()

六、删除

const { User, Blog} = require('./mode') 
!(async function(){
    const destroyRes = await User.destroy({
        where:{
            id:2
        }
    })
    console.log('destroy......',destroyRes)
})()

七、查找

const { Blog, User } = require('./mode')
 
!(async function(){
    一条记录
    const zhangsan = await User.findOne({
        where:{
            userName:'zhangsan'
        }
    })
    console.log('zhangsan:',zhangsan.dataValues)
    
    查询特定的列
    const zhangsanName = await User.findOne({
        attributes:['userName','nickName'],
        where:{
            userName:'zhangsan'
        }
    })
    console.log('zhangsanName:',zhangsanName.dataValues)

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

    分页
    const blogPageList = await Blog.findAll({
        limit:2,
        offset:0,
        order:[
            ['id','desc']
        ]
    })
    console.log('zhangsanBlogList:',blogPageList.map(blog => blog.dataValues))

    查询总数
    const blogListAndCount = await Blog.findAndCountAll({
        limit:2,
        offset:0,
        order:[
            ['id','desc']
        ]
    })
    console.log(blogListAndCount.count)
    console.log('..............................................')
    console.log(blogListAndCount.rows.map(blog => blog.dataValues))
})()

八、更改

const { User } = require('./mode') 
!(async function(){
    const updateRes = await User.update({
        nickName:'张三'
    },{
        where:{
            userName:'zhangsan'
        }
    })
    console.log('updating......',updateRes)
})()