MongoDB SQL 对照表

MongoDB SQL 对照表

首发于个人博客 CoderMiner技术博客 http://coderminer.com

术语、概念

创建

• 创建表

CREATE TABLE people (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  user_id Varchar(30),
  age Number,
  status char(1),
  PRIMARY KEY(id)
  )

db.createCollection("people")
db.people.insertOne({
  user_id:"ab123",
  age:55,
  status:"A"
  })

• 创建索引

CREATE INDEX idx_user_id_asc ON people(user_id)
CREATE INDEX idx_user_id_asc_age_desc ON people(user_id,age DESC)

db.people.createIndex({user_id:1})
db.people.createIndex({user_id:1,age:-1})

• 删除表

DROP TABLE people

db.people.drop()

插入操作

INSERT INTO people(user_id,age,status)
VALUES("abc001",45,"A")

db.people.insertOne({user_id:"abc001",age:45,status:"A"})

查询操作

• 查询表中所有的数据

SELECT * FROM people

db.people.find({})

• 从表中查询特定的字段

SELECT id,user_id,status FROM people

db.people.find(
  {},
  {user_id:1,status:1}
  )

• 查询特定字段(不包含id)

SELECT user_id,status FROM people

db.people.find(
  {},
  {user_id:1,status:1,_id:0}
  )

• 从表中查询特定条件的数据

SELECT * FROM people WHERE status = "A"
SELECT * FROM people WHERE status != "A"

db.people.find({status:"A"})
db.people.find({status:{$ne:"A"}})

• 查询特定条件的特定字段

SELECT user_id,status FROM people WHERE status = "A"

db.people.find(
  {status:"A"},
  {user_id:1,status:1,_id:0}
  )

• 多条件查询

SELECT * FROM people WHERE status = "A" AND age = 50

db.people.find(
  {status:"A",age:50}
  )

• 查询排序

SELECT * FROM people WHERE status = "A" ORDER BY user_id

db.people.find({status:"A"}).sort({user_id:1})

• 查询计数

SELECT COUNT(*) FROM people
SELECT COUNT(user_id) FROM people

db.people.count({}) / db.people.find({}).count()
db.people.count({user_id:{$exists:true}}) / db.people.find({user_id:{$exists:true}})

• 查询限制

SELECT * FROM people LIMIT 1

db.people.findOne() / db.people.find({}).limit(1)

更新操作

UPDATE people SET status = "C" WHERE age > 25

db.people.updateMany(
  {age:{$gt:25}},
  {$set:{status:"C"}}
  )

删除操作

DELETE FROM  people WHERE status = "D"

db.people.deleteMany({status:"D"})

更多精彩内容 http://coderminer.com