Mysql笔记-语法基础与在node项目中使用Esquelize

const Sequlize = require('sequelize')//引入包

//创建连接

const conf = {

host: 'localhost',

dialect:'mysql'

}

const seq = new Sequlize('blog_learn','root','zjl199761',conf)//数据库名，权限，密码

//测试连接,终端执行node src/seq.js测试

seq.authenticate().then(() => {

console.log('ok')

}).catch(() => {

console.log('err')

})

// module.exports = seq//输出实例

终端执行:node src/seq.js测试

创建模型(表)

新建model.js,例新建User模型表

const Sequelize = require('sequelize')

const seq = require('./seq')//导入创建连接的包

//创建User模型，数据表的名字是users

const User = seq.define('user',{

//id会自动创建，并设为主键

userName: {

type: Sequelize.STRING,//varvhar:255

allowNull:false

},

password: {

type:Sequelize.STRING,

allowNull:false

},

nickName:{

type: Sequelize.STRING,

comment:'昵称'//注释

}

//自动创建：createAt和updatedAt

})

module.exports ={

User

}

外键关联

//外键关联

//两者区别在于谁在前面，通过谁去查谁

Blog.belongsTo(User,{

//创建外键 Blog.userId -> User.Id

foreignKey:'useId'

})

User.hasMany(Blog,{

//创建外键 Blog.userId -> User.Id

foreignKey:'useId'

})

数据同步(sync)

在sync.js下

const seq = require('./seq')

require('./model')

//测试连接

seq.authenticate().then(() => {

console.log('auto ok')

}).catch(() => {

console.log('auto err')

})

//执行同步

//force:若数据库中有也强行创建

seq.sync({ force: true }).then(() =>{

console.log("sync ok")

process.exit()//退出

})

2.终端执行：node src/sync.js;在mysql中同步数据

插入数据

在create.js中(create方法，dataValues方法)

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

//!()()上面程序中断不影响里面代码执行

!(async function () {

//创建用户行

const zouzouzou = await User.create({

userName:'zouzouzou',

password:'123456',

nickName:'走走走'

})

console.log('zouzouzou',zouzouzou.dataValues)

const zouzouzouId = zouzouzou.dataValues.id

const blog22 = await Blog.create({

title:'标题22',

content:'内容22',

userId:zouzouzouId

})

})()

查询数据

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

!(async function () {

//查询一条记录FindOne

const zouzouzou = await User.findOne({

where:{

userName:'zouzouzou'

}

})

console.log('zouzouzou',zouzouzou.dataValues)

//查询特定的列，添加属性attributes如:['userName','nickName'],

const zouName = await User.findOne({

attributes:['userName','nickName'],

where:{

userName:'zouzouzou'

}

})

console.log('zouName',zouName.dataValues)

//查询一个列表findAll

const zouBlogList = await Blog.findAll({

where:{

userId:1

},

order:[

['id','desc']

]

})

console.log(

'zouList',//map处理数据，回调函数返回其中的值

zouBlogList.map(blog => blog.dataValues)

)

//分页查询：限制查询

const blogPageList = await Blog.findAll({

limit:2,//限制查询两行

offset:0,//跳过多少条

order:[

['id','desc']//降序

]

})

console.log(

'blogPageList',

blogPageList.map(blog=>blog.dataValues)

)

//查询总数findAndCountAll

const blogListAndCount = await Blog.findAndCountAll({

limit:2,//限制查询两行

offset:0,//跳过多少条

order:[

['id','desc']//降序

]

})

console.log(

'blogListAndCount',

blogListAndCount.count,//所有总数。不考虑分页

blogListAndCount.rows.map(blog=>blog.dataValues)//.row返回数组

)

//连表查询依赖Blog.belongsTo(User

const blogListWithUser = await Blog.findAndCountAll({

order:[

['id','desc']

],

include:[//包含

{

model:User,//在User表中查

attributes:['userName','nickName'],

where:{

userName:'zouzouzou'

}

}

]

})

console.log(

'blogListWithUser',

blogListWithUser.count,

blogListWithUser.rows.map(

blog =>{

const blogVal = blog.dataValues

blogVal.user = blogVal.user.dataValues//1对多关系所有用user

return blogVal

}

)

)

//连表查询依赖User.hasMany(Blog,{

const UserListWithBlog = await User.findAndCountAll({

attributes:['userName','nickName'],

include:[

{

model:Blog

}

]

})

console.log(

'userListWithBlog',

UserListWithBlog.count,

//JSON.stringify强制转化JSON

JSON.stringify(UserListWithBlog.rows.map(user=>{

const userVal = user.dataValues

userVal.blogs=userVal.blogs.map(blog=>blog.dataValues)//1对多关系所有用blogs

return userVal

}))

)

})()

更新数据

Update.js

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

//更新zouzouzou的昵称nickname

!(async function () {

const updateRes = await User.update({//两个参数

nickName:'走走走1'

},{

where:{

userName:'zouzouzou'

}

})

console.log('Update',updateRes[0]>0)

})()

删除数据

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

!(async function () {

//删除一条博客

const delBlogRes = await Blog.destroy({

where:{

id:4

}

})

console.log('delBlogRes',delBlogRes>0)

//删除一个用户,若有外键在mysql中外键设计要Do Delete:cascade

const delUserRes = await User.destroy({

where:{

id:1

}

})

console.log('delUserRes',delUserRes>0)

})()