MongoDB SQL 对照表

MongoDB SQL 对照表

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

术语、概念

SQL MongoDB
database datebase
table collection
row document
column field
index index
table joins $lookup
primary key primary key
aggregation aggregation pipeline

创建

  • 创建表
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