node.js

Sequelize 基本sql语句

2016-08-12  本文已影响5831人  wmtcore

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:返回数字,代表删除记录数

上一篇 下一篇

猜你喜欢

热点阅读