MongoDB 与 MySQL 的区别

2020-06-08 14:28:40 浏览数 (5)

概念区别

关键字和函数区别

语句区别 创建表/集合

代码语言：javascript复制
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)
)

新增字段

代码语言：javascript复制
db.people.updateMany(
    { },
    { $set: { join_date: new Date() } }
)

相当于 ALTER TABLE people ADD join_date DATETIME

删除字段

代码语言：javascript复制
db.people.updateMany(
    { },
    { $unset: { "join_date": "" } }
)

相当于 ALTER TABLE people DROP COLUMN join_date

创建索引

代码语言：javascript复制
db.people.createIndex( { user_id: 1 } )

相当于 CREATE INDEX idx_user_id_asc ON people(user_id)

删除表/集合

代码语言：javascript复制
db.people.drop()

相当于 DROP TABLE people

新增记录/文档

代码语言：javascript复制
db.people.insertOne(
   { user_id: "bcd001", age: 45, status: "A" }
)

相当于 INSERT INTO people(user_id,age,status) VALUES ("bcd001",45,"A")

简单查询

代码语言：javascript复制
db.people.find()

相当于 SELECT * FROM people

db.people.find(
    { },
    { user_id: 1, status: 1 }
)

相当于 SELECT id,user_id,status FROM people

db.people.find(
    { },
    { user_id: 1, status: 1, _id: 0 }
)

相当于 SELECT user_id, status FROM people

条件查询

代码语言：javascript复制
db.people.find(
    { status: "A" }
)

相当于 SELECT * FROM people WHERE status = "A"

db.people.find(
    { status: "A" },
    { user_id: 1, status: 1, _id: 0 }
)

相当于 SELECT user_id, status FROM people WHERE status = "A"

非查询

代码语言：javascript复制
db.people.find(
    { status: { $ne: "A" } }
)

相当于 SELECT * FROM people WHERE status != "A"

且查询

代码语言：javascript复制
db.people.find(
    { status: "A",
      age: 50 }
)

相当于 SELECT * FROM people WHERE status = "A" AND age = 50

或查询

代码语言：javascript复制
db.people.find(
    { $or: [ { status: "A" } ,
             { age: 50 } ] }
)

相当于 SELECT * FROM people WHERE status = "A" OR age = 50

大于查询

代码语言：javascript复制
db.people.find(
    { age: { $gt: 25 } }
)

相当于 SELECT * FROM people WHERE age > 25

小于查询

代码语言：javascript复制
db.people.find(
   { age: { $lt: 25 } }
)

相当于 SELECT * FROM people WHERE age < 25

范围查询

代码语言：javascript复制
db.people.find(
   { age: { $gt: 25, $lte: 50 } }
)

相当于 SELECT * FROM people WHERE age > 25 AND   age <= 50

模糊查询

代码语言：javascript复制
db.people.find( { user_id: /bc/ } ) 或 db.people.find( { user_id: { $regex: /bc/ } } )

相当于 SELECT * FROM people WHERE user_id like "�%"

db.people.find( { user_id: /^bc/ } ) 或 db.people.find( { user_id: { $regex: /^bc/ } } )

相当于 SELECT * FROM people WHERE user_id like "bc%"

排序查询

代码语言：javascript复制
db.people.find( { status: "A" } ).sort( { user_id: 1 } )

相当于 SELECT * FROM people WHERE status = "A" ORDER BY user_id ASC

db.people.find( { status: "A" } ).sort( { user_id: -1 } )

相当于 SELECT * FROM people WHERE status = "A" ORDER BY user_id DESC

统计查询

代码语言：javascript复制
db.people.count() 或 db.people.find().count()

相当于 SELECT COUNT(*) FROM people

db.people.count( { user_id: { $exists: true } } ) 或 db.people.find( { user_id: { $exists: true } } ).count()

相当于 SELECT COUNT(user_id) FROM people

db.people.count( { age: { $gt: 30 } } ) 或 db.people.find( { age: { $gt: 30 } } ).count()

相当于 SELECT COUNT(*) FROM people WHERE age > 30

去重查询

代码语言：javascript复制
db.people.distinct( "status" )

相当于 SELECT DISTINCT(status) FROM people

分页查询

代码语言：javascript复制
db.people.findOne() 或 db.people.find().limit(1)

相当于 SELECT * FROM people LIMIT 1

db.people.find().limit(5).skip(10)

相当于 SELECT * FROM people LIMIT 5 SKIP 10

查询计划

代码语言：javascript复制
db.people.find( { status: "A" } ).explain()

相当于 EXPLAIN SELECT * FROM people WHERE status = "A"

修改记录/文档

代码语言：javascript复制
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";

删除记录/文档

代码语言：javascript复制
db.people.deleteMany( { status: "D" } );

相当于 DELETE FROM people WHERE status = "D";

db.people.deleteMany({});

相当于 DELETE FROM people;
分页 集合 排序 索引 统计

0 人点赞