使用 sequelize 操作 MYSQL 数据库

淩亂°似流年 2023-03-01 12:48 26阅读 0赞

## [https://sequelize.org/][https_sequelize.org] ##

#### `mkdir mysql-koa` ####

#### `npm init -y` ####

#### `npm install mysql2@1.7.0 -D` ####

#### `npm install sequelize@5.17.1 -D` ####

#### 新建一个 src 文件夹，里面新建一个 seq.js ####

const {  Sequelize, } = require('sequelize');
    
    const conf = { 
        host: 'localhost',
        dialect: 'mysql'
    }
    // koa_weibo 自己的数据库名称，root 用户（一般情况就是这个），406103 自己数据库的密码
    const seq = new Sequelize('koa_weibo', 'root', '406103', conf);
    
    // 测试连接是否成功
    seq.authenticate().then(() => { 
        console.log('success')
    }).catch(() => { 
        console.log('fail')
    })
    
    module.exports = seq

#### 在同级目录下新建一个 model.js ####

const {  Sequelize, } = require('sequelize');
    const seq = require('./seq.js')
    
    // User 模型
    const User = seq.define('user', { 
        userName: { 
        	// 指定数据的类型
            type: Sequelize.STRING,
            // 是否允许为空
            allowNull: false
        },
        password: { 
            type: Sequelize.STRING,
            allowNull: false
        },
        nickName: { 
            type: Sequelize.STRING,
            comment: '昵称'
        }
    })
    
    // Blog 模型
    const Blog = seq.define('blog', { 
        title: { 
            type: Sequelize.STRING,
            allowNull: false
        },
        content: { 
            type: Sequelize.STRING,
            allowNull: false
        },
        userId: { 
            type: Sequelize.INTEGER,
            allowNull: false
        }
    })
    
    // 外键关联
    
    module.exports = { 
        User,
        Blog
    }

#### 同级目录下新建一个 sync.js 文件 ####

const seq = require('./seq.js')
    
    require('./model.js')
    
    seq.authenticate().then(() => { 
        console.log('sync success')
    }).catch(() => { 
        console.log('sync fail')
    })
    
    // 强制同步
    seq.sync({  force: true }).then(() => { 
        console.log('sync ok')
        process.exit()
    })
    
    module.exports = { 
        User,
        Blog
    }

#### 运行 node src/sync.js ####

#### 打开 workbench，可以看到表已经成功建立 ####

#### 建立外键关系 ####

const {  Sequelize, } = require('sequelize');
    const seq = require('./seq.js')
    
    // User 模型
    const User = seq.define('user', { 
        。。。
    })
    
    // Blog 模型
    const Blog = seq.define('blog', { 
        。。。。
    })
    
    // 外键关联
    Blog.belongsTo(User, { 
        // Blog.userId -> User.id
        foreignKey: 'userId'
    })
    
    User.hasMany(Blog, { 
        foreignKey: 'userId'
    })
    
    module.exports = { 
        User,
        Blog
    }

#### 一切准备好之后，我们就可以开始对数据库进行操作了。 ####

1.  插入数据  
    （同级目录下新建一个 create.js 文件）

const {  Blog, User } = require('./model.js')
    
    !(async function () { 
        const zhangsan = await User.create({ 
            userName: 'zhangsan',
            password: '123',
            nickName: '张三'
        })
    
        console.log('zhangsan', zhangsan.dataValues)
    
        const zhangsanId = zhangsan.dataValues.id
    
        const lisi = await User.create({ 
            userName: 'lisi',
            password: '123',
            nickName: '李四'
        })
    
        const lisiId = lisi.dataValues.id
    
        console.log('lisi', lisi.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
        })
    
        console.log('blog1', blog1.dataValues)
    })()

###### 运行 node/create.js 进行数据的插入 ######

1.  查询数据  
    （同级目录下新建一个 select.js 文件）

const {  Blog, User } = require('./model.js')
    
    !(async function () { 
        // 查询一条记录
        const zhangsan = await User.findOne({ 
            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: 2,
            order: [
                ['id', 'desc']
            ]
        })
    
        console.log('blogPageList', blogPageList.map(blog => blog.dataValues))
    
        const blogListAndCount = await Blog.findAndCountAll({ 
            limit: 2,
            offset: 0,
            order: [
                ['id', 'desc']
            ]
        })
    
        console.log('blogListAndCount', 
            blogListAndCount.count,
            blogListAndCount.rows.map(blog => blog.dataValues))
    
        // 外键关联查询
        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 = blogVal.user.dataValues
            return blogVal
        }))
    
        // 连表查询2
        const userListWithUser = await User.findAndCountAll({ 
            attributes: ['userName', 'nickName'],
            include: [
                { 
                    model: Blog,
                }
            ]
        })
    
        console.log('userListWithUser', 
        userListWithUser.count,
        userListWithUser.rows.map(user => { 
            const userVal = user.dataValues
            userVal.blogs = userVal.blogs.map(blog => blog.dataValues)
            return userVal
        }))
    
    })()

###### 运行 node/select.js 进行数据的插入 ######

1.  数据的更新  
    （同级目录下新建一个 update.js 文件）

const {  Blog, User } = require('./model.js')
    
    !(async function () { 
        const zhangsan = await User.update({ 
            nickName: '张sa'
        }, { 
            where: { 
                userName: 'zhangsan'
            }
        })
    
        console.log('zhangsan', zhangsan[0] > 0)
    })()

1.  数据的删除  
    （同级目录下新建一个 update.js 文件）

const {  Blog, User } = require('./model.js')
    
    !(async function () { 
        
        // 删除一条博客
        const delBlogRes = await Blog.destroy({ 
            where: { 
                id: 4
            }
        })
    
        console.log('delete', delBlogRes > 0)
    
        const delUserRes = await User.destroy({ 
            where: { 
                id: 1
            }
        })
    
        console.log('delUserRes', delUserRes > 0)
    
    })()

[https_sequelize.org]: https://sequelize.org/