Sequelize 基本sql语句

SELECT、DELETE、UPDATE

sql	orm
select	findAll,findOne,findById,findOrCreate,findAndCountAll
delete	destroy
update	update
insert	create

sql	orm
SELECT foo, bar ...	Model.findAll({attributes: ['foo', 'bar']});
SELECT foo, bar AS baz ...	Model.findAll({attributes: ['foo', ['bar', 'baz']]});
SELECT COUNT(hats) AS no_hats ...	Model.findAll({attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]});
SELECT id, foo, bar, quz ...	Model.findAll({attributes: {exclude: ['baz'] }});

Model.findAll({
  attributes: ['id', 'foo', 'bar', 'baz', 'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});
//等于
Model.findAll({
  attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']] }
});

SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...

WHERE

sql	orm
SELECT * FROM post WHERE authorId = 12 AND status = 'active'	Post.findAll({where: { authorId: 2,status: 'active'}});

Operators

Post.update({
  updatedAt: null,
}, {
  where: {
    deletedAt: {
      $ne: null
    }
  }
});
// UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;

Post.findAll({
  where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;

{
  rank: {
    $or: {
      $lt: 1000,
      $eq: null
    }
  }
}
// rank < 1000 OR rank IS NULL

{
  createdAt: {
    $lt: new Date(),
    $gt: new Date(new Date() - 24 * 60 * 60 * 1000)
  }
}
// createdAt < [timestamp] AND createdAt > [timestamp]

{
  $or: [
    {
      title: {
        $like: 'Boat%'
      }
    },
    {
      description: {
        $like: '%boat%'
      }
    }
  ]
}
// title LIKE 'Boat%' OR description LIKE '%boat%'

op	define
$and: {a: 5}	AND (a = 5)
$or: [{a: 5}, {a: 6}]	(a = 5 OR a = 6)
$gt: 6,	> 6
$gte: 6,	>= 6
$lt: 10,	< 10
$lte: 10,	<= 10
$ne: 20,	!= 20
$between: [6, 10],	BETWEEN 6 AND 10
$notBetween: [11, 15],	NOT BETWEEN 11 AND 15
$in: [1, 2],	IN [1, 2]
$notIn: [1, 2],	NOT IN [1, 2]
$like: '%hat',	LIKE '%hat'
$notLike: '%hat'	NOT LIKE '%hat'
$iLike: '%hat'	ILIKE '%hat' (case insensitive) (PG only)
$notILike: '%hat'	NOT ILIKE '%hat' (PG only)
$like: { $any: ['cat', 'hat']}	LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
$overlap: [1, 2]	&& [1, 2] (PG array overlap operator)
$contains: [1, 2]	@> [1, 2] (PG array contains operator)
$contained: [1, 2]	<@ [1, 2] (PG array contained by operator)
$any: [2,3]	ANY ARRAY[2, 3]::INTEGER (PG only)
$col: 'user.organization_id'	"user"."organization_id", with dialect specific column identifiers, PG in this example --$col取表的字段

WARNING

tabel need primarykey:如果没有，Sequlize会自己加个自增主键，可能引起错误

Node

findOrCreate: 查到一个，查不到就新建

  models.BrandReview.findOrCreate({
      where: {
          mem_id: mem_id,
          brand_id: brand_id
      },
      defaults: {
          score: score //新建的数据
      }
  })
 //返回值为数组，[json,created] 第一位是查询或创建的数据，第二位标识是否新建
 

update:返回值为数据，[2],数字代码改动记录数

destroy:返回数字，代表删除记录数