51工具盒子一、前言 {#一、前言}
MongoDB 虽说是文档型数据库，但是在学习和使用其语法时发现又与 MySQL 有些相似之处，在此记录点滴日后复习。
二、概念区别 {#二、概念区别}
| 比较 | MySQL | MongoDB | |-----|-------------|--------------------------------------------------------------------------------------------------| | 库 | database | database | | 表 | table | collection | | 行 | row | document | | 列 | column | field | | 索引 | index | index | | 表关联 | table joins | $lookup | | 主键 | primary key | primary key | | 聚合 | aggregation | aggregation pipeline |
三、命令区别 {#三、命令区别}
| 比较 | MySQL | MongoDB | |-----|--------|---------| | 服务端 | mysqld | mongod | | 客户端 | mysql | mongo |
四、关键字和函数区别 {#四、关键字和函数区别}
| MySQL | MongoDB | |----------|-----------------------------------------------------------------------------------------------------| | where | $match | | group by | $group | | having | $match | | select | $project | | order by | $sort | | limit | $limit | | sum() | $sum | | count() | $sum | | join | $lookup |
五、语句区别 {#五、语句区别}
5.1 表结构 {#5.1-表结构}
5.1.1 创建表/集合 {#5.1.1-创建表-集合}
|---------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | db.people.insertOne( { user_id: "abc123", age: 55, status: "A" } ) 相当于 CREATE TABLE people ( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(30), age Number, status char(1), PRIMARY KEY (id) ) |
5.1.2 新增字段 {#5.1.2-新增字段}
|---------------------|------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 | db.people.updateMany( { }, { $set: { join_date: new Date() } } ) 相当于 ALTER TABLE people ADD join_date DATETIME |
5.1.3 删除字段 {#5.1.3-删除字段}
|---------------------|-------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 | db.people.updateMany( { }, { $unset: { "join_date": "" } } ) 相当于 ALTER TABLE people DROP COLUMN join_date |
5.1.4 创建索引 {#5.1.4-创建索引}
|---------------|-----------------------------------------------------------------------------------------------------|
| 1 2 3 | db.people.createIndex( { user_id: 1 } ) 相当于 CREATE INDEX idx_user_id_asc ON people(user_id) |
5.1.5 删除表/集合 {#5.1.5-删除表-集合}
|---------------|------------------------------------------------|
| 1 2 3 | db.people.drop() 相当于 DROP TABLE people |
5.2 新增记录/文档 {#5.2-新增记录-文档}
|-------------------|------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 | db.people.insertOne( { user_id: "bcd001", age: 45, status: "A" } ) 相当于 INSERT INTO people(user_id,age,status) VALUES ("bcd001",45,"A") |
5.3 查询记录/文档 {#5.3-查询记录-文档}
5.3.1 简单查询 {#5.3.1-简单查询}
|---------------|---------------------------------------------------|
| 1 2 3 | db.people.find() 相当于 SELECT * FROM people |
|---------------------|---------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 | db.people.find( { }, { user_id: 1, status: 1 } ) 相当于 SELECT id,user_id,status FROM people |
|---------------------|---------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 | db.people.find( { }, { user_id: 1, status: 1, _id: 0 } ) 相当于 SELECT user_id, status FROM people |
5.3.2 条件查询 {#5.3.2-条件查询}
|-------------------|---------------------------------------------------------------------------------------|
| 1 2 3 4 5 | db.people.find( { status: "A" } ) 相当于 SELECT * FROM people WHERE status = "A" |
|---------------------|----------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 | db.people.find( { status: "A" }, { user_id: 1, status: 1, _id: 0 } ) 相当于 SELECT user_id, status FROM people WHERE status = "A" |
5.3.3 非查询 {#5.3.3-非查询}
|-------------------|-------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 | db.people.find( { status: { $ne: "A" } } ) 相当于 SELECT * FROM people WHERE status != "A" |
5.3.4 且查询 {#5.3.4-且查询}
|---------------------|-------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 | db.people.find( { status: "A", age: 50 } ) 相当于 SELECT * FROM people WHERE status = "A" AND age = 50 |
5.3.5 或查询 {#5.3.5-或查询}
|---------------------|------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 | db.people.find( { $or: [ { status: "A" } , { age: 50 } ] } ) 相当于 SELECT * FROM people WHERE status = "A" OR age = 50 |
5.3.6 大于查询 {#5.3.6-大于查询}
|-------------------|----------------------------------------------------------------------------------------|
| 1 2 3 4 5 | db.people.find( { age: { $gt: 25 } } ) 相当于 SELECT * FROM people WHERE age > 25 |
5.3.7 小于查询 {#5.3.7-小于查询}
|-------------------|----------------------------------------------------------------------------------------|
| 1 2 3 4 5 | db.people.find( { age: { $lt: 25 } } ) 相当于 SELECT * FROM people WHERE age < 25 |
5.3.8 范围查询 {#5.3.8-范围查询}
|-------------------|------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 | db.people.find( { age: { $gt: 25, $lte: 50 } } ) 相当于 SELECT * FROM people WHERE age > 25 AND age <= 50 |
5.3.9 模糊查询 {#5.3.9-模糊查询}
|---------------|--------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 | db.people.find( { user_id: /bc/ } ) 或 db.people.find( { user_id: { $regex: /bc/ } } ) 相当于 SELECT * FROM people WHERE user_id like "%bc%" |
|---------------|---------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 | db.people.find( { user_id: /^bc/ } ) 或 db.people.find( { user_id: { $regex: /^bc/ } } ) 相当于 SELECT * FROM people WHERE user_id like "bc%" |
5.3.10 排序查询 {#5.3.10-排序查询}
|---------------|-----------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 | db.people.find( { status: "A" } ).sort( { user_id: 1 } ) 相当于 SELECT * FROM people WHERE status = "A" ORDER BY user_id ASC |
|---------------|-------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 | db.people.find( { status: "A" } ).sort( { user_id: -1 } ) 相当于 SELECT * FROM people WHERE status = "A" ORDER BY user_id DESC |
5.3.11 统计查询 {#5.3.11-统计查询}
|---------------|--------------------------------------------------------------------------------------|
| 1 2 3 | db.people.count() 或 db.people.find().count() 相当于 SELECT COUNT(*) FROM people |
|---------------|------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 | db.people.count( { user_id: { $exists: true } } ) 或 db.people.find( { user_id: { $exists: true } } ).count() 相当于 SELECT COUNT(user_id) FROM people |
|---------------|-------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 | db.people.count( { age: { $gt: 30 } } ) 或 db.people.find( { age: { $gt: 30 } } ).count() 相当于 SELECT COUNT(*) FROM people WHERE age > 30 |
5.3.12 去重查询 {#5.3.12-去重查询}
|---------------|--------------------------------------------------------------------------------|
| 1 2 3 | db.people.distinct( "status" ) 相当于 SELECT DISTINCT(status) FROM people |
5.3.13 分页查询 {#5.3.13-分页查询}
|---------------|------------------------------------------------------------------------------------------|
| 1 2 3 | db.people.findOne() 或 db.people.find().limit(1) 相当于 SELECT * FROM people LIMIT 1 |
|---------------|-------------------------------------------------------------------------------------|
| 1 2 3 | db.people.find().limit(5).skip(10) 相当于 SELECT * FROM people LIMIT 5 SKIP 10 |
5.3.14 查询计划 {#5.3.14-查询计划}
|---------------|---------------------------------------------------------------------------------------------------------|
| 1 2 3 | db.people.find( { status: "A" } ).explain() 相当于 EXPLAIN SELECT * FROM people WHERE status = "A" |
5.4 修改记录/文档 {#5.4-修改记录-文档}
|---------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | db.people.updateMany( { age: { $gt: 25 } }, { $set: { status: "C" } } ); 相当于 UPDATE people SET status = "C" WHERE age > 25; db.people.updateMany( { status: "A" } , { $inc: { age: 3 } } ); 相当于 UPDATE people SET age = age + 3 WHERE status = "A"; |
5.5 删除记录/文档 {#5.5-删除记录-文档}
|-----------------------|-----------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 7 | db.people.deleteMany( { status: "D" } ); 相当于 DELETE FROM people WHERE status = "D"; db.people.deleteMany({}); 相当于 DELETE FROM people; |
