SQL和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

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()