MongoDB SQL 对照表

10/19/2018 MongoDB后端

MongoDB SQL 对照表 首发于个人博客 CoderMiner技术博客 http://coderminer.com 术语、概念| SQL | MongoDB || :------------- | :------------- || database | datebase || tabl

# MongoDB SQL 对照表

首发于个人博客 CoderMiner技术博客 (opens new window) 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)
  )
1
2
3
4
5
6
7
db.createCollection("people")
db.people.insertOne({
  user_id:"ab123",
  age:55,
  status:"A"
  })
1
2
3
4
5
6
  • 创建索引
CREATE INDEX idx_user_id_asc ON people(user_id)
CREATE INDEX idx_user_id_asc_age_desc ON people(user_id,age DESC)
1
2
db.people.createIndex({user_id:1})
db.people.createIndex({user_id:1,age:-1})
1
2
  • 删除表
DROP TABLE people
1
db.people.drop()
1

# 插入操作

INSERT INTO people(user_id,age,status)
VALUES("abc001",45,"A")
1
2
db.people.insertOne({user_id:"abc001",age:45,status:"A"})
1

# 查询操作

  • 查询表中所有的数据
SELECT * FROM people
1
db.people.find({})
1
  • 从表中查询特定的字段
SELECT id,user_id,status FROM people
1
db.people.find(
  {},
  {user_id:1,status:1}
  )
1
2
3
4
  • 查询特定字段(不包含id)
SELECT user_id,status FROM people
1
db.people.find(
  {},
  {user_id:1,status:1,_id:0}
  )
1
2
3
4
  • 从表中查询特定条件的数据
SELECT * FROM people WHERE status = "A"
SELECT * FROM people WHERE status != "A"
1
2
db.people.find({status:"A"})
db.people.find({status:{$ne:"A"}})
1
2
  • 查询特定条件的特定字段
SELECT user_id,status FROM people WHERE status = "A"
1
db.people.find(
  {status:"A"},
  {user_id:1,status:1,_id:0}
  )
1
2
3
4
  • 多条件查询
SELECT * FROM people WHERE status = "A" AND age = 50
1
db.people.find(
  {status:"A",age:50}
  )
1
2
3
  • 查询排序
SELECT * FROM people WHERE status = "A" ORDER BY user_id
1
db.people.find({status:"A"}).sort({user_id:1})
1
  • 查询计数
SELECT COUNT(*) FROM people
SELECT COUNT(user_id) FROM people
1
2
db.people.count({}) / db.people.find({}).count()
db.people.count({user_id:{$exists:true}}) / db.people.find({user_id:{$exists:true}})
1
2
  • 查询限制
SELECT * FROM people LIMIT 1
1
db.people.findOne() / db.people.find({}).limit(1)
1

# 更新操作

UPDATE people SET status = "C" WHERE age > 25
1
db.people.updateMany(
  {age:{$gt:25}},
  {$set:{status:"C"}}
  )
1
2
3
4

# 删除操作

DELETE FROM  people WHERE status = "D"
1
db.people.deleteMany({status:"D"})
1

更多精彩内容 (opens new window) http://coderminer.com