一、前言

MongoDB 虽说是文档型数据库,但是在学习和使用其语法时发现又与 MySQL 有些相似之处,在此记录点滴日后复习。

二、概念区别

比较MySQLMongoDB
databasedatabase
tablecollection
rowdocument
columnfield
索引indexindex
表关联table joins$lookup
主键primary keyprimary key
聚合aggregationaggregation pipeline

三、命令区别

比较MySQLMongoDB
服务端mysqldmongod
客户端mysqlmongo

四、关键字和函数区别

MySQLMongoDB
where$match
group by$group
having$match
select$project
order by$sort
limit$limit
sum()$sum
count()$sum
join$lookup

五、语句区别

# 5.1 表结构

# 5.1.1 创建表/集合

  1. db.people.insertOne( {
  2. user_id: "abc123",
  3. age: 55,
  4. status: "A"
  5. } )
  6. 相当于
  7. CREATE TABLE people (
  8. id MEDIUMINT NOT NULL AUTO_INCREMENT,
  9. user_id Varchar(30),
  10. age Number,
  11. status char(1),
  12. PRIMARY KEY (id)
  13. )

# 5.1.2 新增字段

  1. db.people.updateMany(
  2. { },
  3. { $set: { join_date: new Date() } }
  4. )
  5. 相当于 ALTER TABLE people ADD join_date DATETIME

# 5.1.3 删除字段

  1. db.people.updateMany(
  2. { },
  3. { $unset: { "join_date": "" } }
  4. )
  5. 相当于 ALTER TABLE people DROP COLUMN join_date

# 5.1.4 创建索引

  1. db.people.createIndex( { user_id: 1 } )
  2. 相当于 CREATE INDEX idx_user_id_asc ON people(user_id)

# 5.1.5 删除表/集合

  1. db.people.drop()
  2. 相当于 DROP TABLE people

# 5.2 新增记录/文档

  1. db.people.insertOne(
  2. { user_id: "bcd001", age: 45, status: "A" }
  3. )
  4. 相当于 INSERT INTO people(user_id,age,status) VALUES ("bcd001",45,"A")

# 5.3 查询记录/文档

# 5.3.1 简单查询

  1. db.people.find()
  2. 相当于 SELECT * FROM people
  1. db.people.find(
  2. { },
  3. { user_id: 1, status: 1 }
  4. )
  5. 相当于 SELECT id,user_id,status FROM people
  1. db.people.find(
  2. { },
  3. { user_id: 1, status: 1, _id: 0 }
  4. )
  5. 相当于 SELECT user_id, status FROM people

# 5.3.2 条件查询

  1. db.people.find(
  2. { status: "A" }
  3. )
  4. 相当于 SELECT * FROM people WHERE status = "A"
  1. db.people.find(
  2. { status: "A" },
  3. { user_id: 1, status: 1, _id: 0 }
  4. )
  5. 相当于 SELECT user_id, status FROM people WHERE status = "A"

# 5.3.3 非查询

  1. db.people.find(
  2. { status: { $ne: "A" } }
  3. )
  4. 相当于 SELECT * FROM people WHERE status != "A"

# 5.3.4 且查询

  1. db.people.find(
  2. { status: "A",
  3. age: 50 }
  4. )
  5. 相当于 SELECT * FROM people WHERE status = "A" AND age = 50

# 5.3.5 或查询

  1. db.people.find(
  2. { $or: [ { status: "A" } ,
  3. { age: 50 } ] }
  4. )
  5. 相当于 SELECT * FROM people WHERE status = "A" OR age = 50

# 5.3.6 大于查询

  1. db.people.find(
  2. { age: { $gt: 25 } }
  3. )
  4. 相当于 SELECT * FROM people WHERE age > 25

# 5.3.7 小于查询

  1. db.people.find(
  2. { age: { $lt: 25 } }
  3. )
  4. 相当于 SELECT * FROM people WHERE age < 25

# 5.3.8 范围查询

  1. db.people.find(
  2. { age: { $gt: 25, $lte: 50 } }
  3. )
  4. 相当于 SELECT * FROM people WHERE age > 25 AND age <= 50

# 5.3.9 模糊查询

  1. db.people.find( { user_id: /bc/ } ) 或 db.people.find( { user_id: { $regex: /bc/ } } )
  2. 相当于 SELECT * FROM people WHERE user_id like "%bc%"
  1. db.people.find( { user_id: /^bc/ } ) 或 db.people.find( { user_id: { $regex: /^bc/ } } )
  2. 相当于 SELECT * FROM people WHERE user_id like "bc%"

# 5.3.10 排序查询

  1. db.people.find( { status: "A" } ).sort( { user_id: 1 } )
  2. 相当于 SELECT * FROM people WHERE status = "A" ORDER BY user_id ASC
  1. db.people.find( { status: "A" } ).sort( { user_id: -1 } )
  2. 相当于 SELECT * FROM people WHERE status = "A" ORDER BY user_id DESC

# 5.3.11 统计查询

  1. db.people.count() 或 db.people.find().count()
  2. 相当于 SELECT COUNT(*) FROM people
  1. db.people.count( { user_id: { $exists: true } } ) 或 db.people.find( { user_id: { $exists: true } } ).count()
  2. 相当于 SELECT COUNT(user_id) FROM people
  1. db.people.count( { age: { $gt: 30 } } ) 或 db.people.find( { age: { $gt: 30 } } ).count()
  2. 相当于 SELECT COUNT(*) FROM people WHERE age > 30

# 5.3.12 去重查询

  1. db.people.distinct( "status" )
  2. 相当于 SELECT DISTINCT(status) FROM people

# 5.3.13 分页查询

  1. db.people.findOne() 或 db.people.find().limit(1)
  2. 相当于 SELECT * FROM people LIMIT 1
  1. db.people.find().limit(5).skip(10)
  2. 相当于 SELECT * FROM people LIMIT 5 SKIP 10

# 5.3.14 查询计划

  1. db.people.find( { status: "A" } ).explain()
  2. 相当于 EXPLAIN SELECT * FROM people WHERE status = "A"

# 5.4 修改记录/文档

  1. db.people.updateMany(
  2. { age: { $gt: 25 } },
  3. { $set: { status: "C" } }
  4. );
  5. 相当于 UPDATE people SET status = "C" WHERE age > 25;
  6. db.people.updateMany(
  7. { status: "A" } ,
  8. { $inc: { age: 3 } }
  9. );
  10. 相当于 UPDATE people SET age = age + 3 WHERE status = "A";

# 5.5 删除记录/文档

  1. db.people.deleteMany( { status: "D" } );
  2. 相当于 DELETE FROM people WHERE status = "D";
  3. db.people.deleteMany({});
  4. 相当于 DELETE FROM people;

六、参考资料