首页  :: 新随笔  :: 管理

MongoDB与SQL术语的对比

Posted on 2022-03-31 18:47  高&玉  阅读(73)  评论(0)  编辑  收藏  举报

SQL与MongoDB术语对比

SQL术语 MongoDB术语
database database
table collection
row document or BSON document
column field
index index
table joins $lookup，嵌入式文档

primary key

指定任何唯一的列或列组合为主键

primary key

MongoDB中会自动设置_id字段为主键
aggregation(group by) aggregation pipeline
SELECT INTO NEW_TABLE $out
MERGE INTO TABLE $merge（MongoDB 4.2开始支持）
UNION ALL $unionWith（MongoDB 4.4开始支持）
transactions transactions

 

数据库执行文件对比

  MongoDB MySQL Oracle informix DB2
Database Server mongod mysqld oracle IDS DB2 Server
Database Client mongosh mysql sqlplus DB-Access DB2 Client

Create与Alter对比

SQL语句 MongoDB语句 
CREATE TABLE people (
    id MEDIUMINT NOT NULL
        AUTO_INCREMENT,
    user_id Varchar(30),
    age Number,
    status char(1),
    PRIMARY KEY (id)
)

MongoDB在第一次insertOne()或insertMany()的时候隐式创建集合，如果_id字段没有显示指定，那么会自动添加_id字段为主键。

db.people.insertOne( {
    user_id: "abc123",
    age: 55,
    status: "A"
 } )

 或者显示创建集合

db.createCollection("people")
 
ALTER TABLE people
ADD join_date DATETIME

集合没有结构的概念，但是可以在文档级别，updateMany()操作可以使用$set操作符向现有文档添加字段。

    { },
    { $set: { join_date: new Date() } }
)
 
ALTER TABLE people
DROP COLUMN join_date

集合没有结构的概念，但是可以在文档级别，updateMany()操作可以使用$unset操作符从现有文档中删除字段。

db.people.updateMany(
    { },
    { $unset: { "join_date": "" } }
)
 
CREATE INDEX idx_user_id_asc
ON people(user_id)
 
db.people.createIndex( { user_id: 1 } )
 
CREATE INDEX
       idx_user_id_asc_age_desc
ON people(user_id, age DESC)
 
db.people.createIndex( { user_id: 1, age: -1 } )
 
DROP TABLE people
 
db.people.drop()

insert对比

SQL INSERT语句 MongoDB insertOne()语句 
INSERT INTO people(user_id,
                  age,
                  status)
VALUES ("bcd001",
        45,
        "A")
 
db.people.insertOne(
   { user_id: "bcd001", age: 45, status: "A" }
)

select对比

SQL SELECT语句 MongoDB find()语句 
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
 
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
WHERE status = "A"
OR age = 50
 
db.people.find(
    { $or: [ { status: "A" } , { age: 50 } ] }
)
 
SELECT *
FROM people
WHERE age > 25
 
db.people.find(
    { age: { $gt: 25 } }
)
 
SELECT *
FROM people
WHERE age < 25
 
	
db.people.find(
   { age: { $lt: 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
 
db.people.find( { status: "A" } ).sort( { user_id: 1 } )
 
SELECT *
FROM people
WHERE status = "A"
ORDER BY user_id DESC
 
db.people.find( { status: "A" } ).sort( { user_id: -1 } )
 
SELECT COUNT(*)
FROM people
 
db.people.count()

或者

db.people.find().count()
 
SELECT COUNT(user_id)
FROM people
 
db.people.count( { user_id: { $exists: true } } )

或者

db.people.find( { user_id: { $exists: true } } ).count()
 
SELECT COUNT(*)
FROM people
WHERE age > 30
 
db.people.count( { age: { $gt: 30 } } )

或者

db.people.find( { age: { $gt: 30 } } ).count()
 
SELECT DISTINCT(status)
FROM people
 
db.people.aggregate( [ { $group : { _id : "$status" } } ] )

或者，不超过BSON大小限制的不同集合

db.people.distinct( "status" )
 
SELECT *
FROM people
LIMIT 1
 
db.people.findOne()

或者

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

update对比

SQL update语句 MongoDB updateMany()语句 
UPDATE people
SET status = "C"
WHERE age > 25
 
db.people.updateMany(
   { age: { $gt: 25 } },
   { $set: { status: "C" } }
)
 
UPDATE people
SET age = age + 3
WHERE status = "A"
 
db.people.updateMany(
   { status: "A" } ,
   { $inc: { age: 3 } }
)

delete对比

SQL Delete语句 MongoDB deleteMany()语句 
DELETE FROM people
WHERE status = "D"
 
db.people.deleteMany( { status: "D" } )
 
DELETE FROM people
 
db.people.deleteMany({})

 

参考官方文档：https://www.mongodb.com/docs/manual/reference/sql-comparison/