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"})