SQL和MongoDB对比

关系型数据库如MySQL和非关系型数据库MongoDB的对应关系：

SQL	MongoDB
database	database
table	collection
row	document or Bson document
column	field
index	index
table joins	$lookup
primary key	primary key
指定任何唯一的列或列组合作为主键	主键会自动设置为_id字段
aggregation (group by)	aggregation pipeline
select into table	$out
merge into table	$merge
union all	$unionWith
transactions	transacctions

Example

CREATE TABLE people (id MEDIUMINT NOT NULLAUTO_INCREMENT,user_id Varchar(30),age Number,status char(1),PRIMARY KEY (id) )	Implicitly created on first insertOne() or insertMany() operation. The primary key _id is automatically added if _id field is not specified. db.people.insertOne( {user_id: "abc123",age: 55,status: "A"} )
ALTER TABLE people DROP COLUMN join_date	使用$set添加field db.people.updateMany({ },{ $set: { join_date: new Date() } } )
ALTER TABLE people DROP COLUMN join_date	使用$unset删除field db.people.updateMany({ },{ $unset: { "join_date": "" } } )
创建索引 CREATE INDEX idx_user_id_asc ON people(user_id)	db.people.createIndex( { user_id: 1 } )
CREATE INDEXidx_user_id_asc_age_desc ON people(user_id, age DESC)	-1表示降序 db.people.createIndex( { user_id: 1, age: -1 } )
DROP TABLE people	db.people.drop()
INSERT INTO people(user_id,age,status) VALUES ("bcd001",45,"A")	db.people.insertOne({ user_id: "bcd001", 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})
SELECT user_id,status FROM people	//0表示不显示改行 db.people.find({},{user_id:1,status:1,_id:0})
SELECT * FROM people WHERE status="A"	db.people.find({status:"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"	db.people.find({status:{$ne:"A"}})
SELECT * FROM people WHERE status = "A" AND age=50	db.people.find({status:"A",age:50})
SELECT * FROM people OR age = 50	db.people.find({$or:[{status:"A"},{age:50}]})
SELECT * FROM people WHERE age >= 25	db.people.find({age:{$gte:25}})
SELECT * FROM people WHERE age > 25 AND age <= 50	db.people.find({age:{$gt:25,$lte:50}})
SELECT * FROM people WHERE user_id like "%bc%"	db.people.find({user_id:/bc/} ) db.people.find({user_id:{$regex:/bc/}})
SELECT * FROM people WHERE user_id LIKE "%bc"	db.people.find({user_id:/^bc/}) db.people.find({user_id:{$regex:/^bc/}})
SELECT * FROM people WHERE status = "A" ORDER BY user_id ASC SELECT * FROM people WHERE status = "A" ORDER BY user_id DESC	db.people.find({status:"A"}).sort({user_id:1}) db.people.find({status:"A"}).sort({user_id:-1})
SELECT count(*) FROM people	db.people.count() or db.people.find().count() or db.people.count({user_id:{$exists:true}})
SELECT count(*) FROM people WHERE age>30	db.people.count({age:{$gt:30}})
SELECT DISTINCT(status) FROM people	db.people.distinct("status")
SELECT * FROM people LIMIT 1	db.people.findOne() or db.people.find().limit(1)
SELECT * FROM people LIMIT 5 SKIP 10	db.people.find(().limit(5).skip(10)
EXPLAIN SELECT * FROM people WHERE status = "A"	db.people.find({status:"A"}).explain()