安装$npminstall--savesequelize#还需要安装以下之一：$npminstall--savepgpg-hstore//postgreSql$npminstall--savemysql//mysqlormariadb$npminstall--savesqlite3$npminstall--savetedious//MSSQL建立连接constSequelize=require('sequelize')constsequelize=newSequelize(db.database,db.user,db.password,{//表名用户名密码host:db.host,//地址port:db.port,//端口方言:'mysql',//数据库类型:'mysql'|'mariadb'|'sqlite'|'postgres'|'mssql'pool:{//连接池Configuremax:5,min:0,acquire:30000,idle:10000,},timezone:'+08:00'//时区转换})definemodelconstSequelize=require('sequelize')constmoment=require('moment');moment.locale('zh-cn');User:sequelize.define('user',{id:{type:Sequelize.STRING(255),primaryKey:true,//主键}，名称：Sequelize.STRING，角色：Sequelize.INTEGER(11)，open_id：Sequelize.STRING，描述：Sequelize.STRING，状态：Sequelize.INTEGER(11)，lv：Sequelize.INTEGER(11),token:Sequelize.STRING,create_time:{type:Sequelize.DATE,get(){返回时刻(this.getDataValue('create_time')).format('YYYY-MM-DDHH:mm:ss');}},update_time:{type:Sequelize.DATE,get(){returnmoment(this.getDataValue('update_time')).format('YYYY-MM-DDHH:mm:ss');}}},{freezeTableName:true,timestamps:false})sql,orm对应关系sqlormselectfindAll,findOne,findById,findOrCreate,findAndCountAllupdateupdateinsertcreatedeletedestroyquery查询单条数据User.findOne({attributes:['id','name','role','open_id','describe'],其中：{id:id}}).then(result=>{console.log(result)}).catch(err=>{console.log(err)});queryMultiplefindAll(opts)orall(opts)User.findAll()分页查询findAndCount(opts)orfindAndCountAllUser.findAndCount({limit:10,//每页10条offset:0*10,//页x*个数每个地方的页面数：{}});按id查询findById(id,opts)User.findById(1);查询，创建一个新的findOrCreate(opts)或findCreateFindUser.findOrCreat如果它不存在e({其中：{open_id:req.body.open_id}，默认值：{id:id,name:req.body.name,open_id:req.body.open_id,token:token,create_time:Date.now()}}).then(result=>{//返回值是一个数组，[json,created]第一位是查询或创建的数据，第二位表示是否是新创建的})GroupingqueryGroupingqueryis通常与聚合函数一起使用，聚合函数包括：聚合函数COUNT()用于统计记录条数SUM()用于计算字段值的总和AVG()用于计算字段的平均值valuesMAX用于求查询字段的最大值MIX用于求查询字段的最小值//求表中like字段值的总和orm.Article.findAll({attributes:[[Sequelize.fn('SUM',Sequelize.col('like')),'likes']],}).then(result=>{result[0].get('likes')})更新用户.update({token:'token'},{where:{id:l}}).then(result=>{console.log(result)}).catch(err=>{console.log(err)});添加User.create({id:id,name:req.body.name,open_id:req.body.open_id,create_time:Date.now()}).then(result=>{console.log(result)}).catch(err=>{console.log(err)});删除User.destroy({where:{id:1}}).then(result=>{console.log(result)}).catch(err=>{console.log(err)});关联查询一对一sequelize提供了两种一对一关系关联方法belongsTo和hasOneUser.belongsTo(Article,{foreignKey:'id',as:'article',targetKey:'user_id'})User.hasOne(Article,{foreignKey:'user_id',as:'article'})第一个参数是Model，第二个是options配置。foreignKey：指定外键为：指定别名targetKey：目标键，即源模型上的外键列指向的目标模型上的列。默认情况下，它是目标模型的主键。这两种方法都是将userInfo表与User表关联起来，区别在于暴露外键的表不同：belongsTo暴露User表的'id'字段作为外键查询UserInfo表。hasOne方法将Article表的'user_id'暴露为外键，使用User.findeOne查询User表({where:{},include:{model:Article,as:'article'where:{},required:false//只过滤include的结果}})belongsTo`user`.`id`,`user`.`name`,`article`.`id`AS`article.id`,`article`生成的sqlSELECT.`title`AS`article.title`,`article`.`user_id`AS`article.user_id`FROM`user`AS`user`LEFTOUTERJOIN`article`AS`article`ON`user`.`id`=`article`.`user_id`WHERE`user`.`id`='1';hasOne`user`.`id`,`user`.`na生成的sqlSELECTme`,`article`.`id`AS`article.id`,`article`.`title`AS`article.title`,`article`.`user_id`AS`article.user_id`FROM`user`AS`user`LEFTOUTERJOIN`article`AS`article`ON`user`.`id`=`article`.`user_id`WHERE`user`.`id`='1';belongsTo使用User的外键作为去条件查询文章主键hasOne以文章外键为条件查询用户主键一对多hasMany多对多belongToMany常用符号运算符运算符说明[Op.and]:{a:5}AND(a=5)[Op.or]:[{a:5},{a:6}](a=5ORa=6)[Op.gt]:6,>6[Op.gte]:6,>=6[操作。lt]:10,<10[Op.lte]:10,<=10[Op.ne]:20,!=20[Op.eq]:3,=3[Op.not]:true,ISNOTTRUE[Op.between]:[6,10],BETWEEN6AND10[Op.notBetween]:[11,15],NOTBETWEEN11AND15[Op.in]:[1,2],IN[1,2]][Op.notIn]:[1,2],NOTIN[1,2][Op.like]:'%hat',LIKE'%hat'[Op.notLike]:'%hat'NOTLIKE'%hat'[Op.iLike]:'%hat'ILIKE'%hat'(caseinsensitive)(PGonly)[Op.notILike]:'%hat'NOTILIKE'%hat'(PGonly)[Op.startsWith]:'hat'LIKE'hat%'[Op.endsWith]:'hat'LIKE'%hat'[Op.substring]:'hat'LIKE'%hat%'[Op.regexp]:'^[hat]'REGEXP/~'^[hat]'（仅限MySQL/PG）[Op.notRegexp]:'^[hat]'NOTREGEXP/!~'^[hat]'（仅限MySQL/PG）[Op.iRegexp]:'^[hat]'~*'^[hat]'（仅限PG）[Op.notIRegexp]:'^[hat]'!~*'^[hat]'（仅限PG）[Op.like]:{[Op.any]:['cat','hat']}LIKEANYARRAY['cat','hat']-也适用于iLike和notLike[Op.overlap]:[1,2]&&[1,2](PG数组重叠运算符)[Op.contains]:[1,2]@>[1,2](PG数组包含运算符)[Op.contained]:[1,2]<@[1,2]（运算符包含的PG数组）[Op.any]：[2,3]ANYARRAY[2,3]::INTEGER（仅限PG）[Op.col]：'user.organization_id'="user"."organization_id",withdialectspecificcolumnidentifiers,PGinthisexampleconstOp=Sequelize.Op;//查询age<18或者小于5的数据User.findAll({where:{age：{[Op.or]:{[Op.lt]:18,[Op.eq]:5}}}}).then(result=>{console.log(result)}).catch(err=>{console.log(err)});永久链接：https://blog.qianxiaoduan.com/archives/776
