MongoDB 与SQL操作映射

Create or Alter

Create table or collection

CREATE TABLE people (                       db.people.insertOne( {
    id MEDIUMINT NOT NULL                       user_id: "abc123",
        AUTO_INCREMENT,                         age: 55,
    user_id Varchar(30),                        status: "A"
    age Number,                                 } )
    status char(1),
    PRIMARY KEY (id)
)

Add column or key

ALTER TABLE people                          db.people.updateMany(
ADD join_date DATETIME                          { },
                                                { $set: { join_date: new Date() } }
                                                )

Delete column or key

ALTER TABLE people                          db.people.updateMany(
DROP COLUMN join_date                           { },
                                                { $unset: { "join_date": "" } }
                                                )

Create single index

CREATE INDEX idx_user_id_asc                db.people.createIndex( { user_id: 1 } )
ON people(user_id)

Create union and desc index

CREATE INDEX                                db.people.createIndex( { user_id: 1, age: -1 } )
       idx_user_id_asc_age_desc
ON people(user_id, age DESC)

Drop table or collection

DROP TABLE people                           db.people.drop()

See also:

• db.collection.insertOne()
• db.collection.insertMany()
• db.createCollection()
• db.collection.updateMany()
• $set
• $unset
• db.collection.createIndex()
• Indexes
• db.collection.drop()
• Data Modeling Concepts.

Insert

Insert one

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

See also:

db.collection.insertOne()

Select

Select all

SELECT *                                    db.people.find()
FROM people

Select specifically

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

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

Selct logically

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

SELECT *                                    db.people.find(
FROM people                                     { status: { $ne: "A" } }
WHERE status != "A"                         )

SELECT *                                    db.people.find(
FROM people                                     { status: "A",
WHERE status = "A"                              age: 50 }
AND age = 50                                )

SELECT *                                    db.people.find(
FROM people                                     { $or: [ { status: "A" } ,
WHERE status = "A"                                  { age: 50 } ] }
OR age = 50                                 )

SELECT *                                    db.people.find(
FROM people                                     { age: { $gt: 25 } }
WHERE age > 25                              )

SELECT *                                    db.people.find(
FROM people                                     { age: { $lt: 25 } }
WHERE age < 25                              )

SELECT *                                    db.people.find(
FROM people                                     { age: { $gt: 25, $lte: 50 } }
WHERE age > 25                              )
AND   age <= 50

Select fuzzily

SELECT *                                    db.people.find( { user_id: /bc/ } )
FROM people                                 -or-
WHERE user_id like "%bc%"                   db.people.find( { user_id: { $regex: /bc/ } } )

SELECT *                                    db.people.find( { user_id: /^bc/ } )
FROM people                                 -or-
WHERE user_id like "bc%"                    db.people.find( { user_id: { $regex: /^bc/ } } )

Select and Sort

SELECT *                                    db.people.find(
FROM people                                      { status: "A" } ).sort( { user_id: 1 }
WHERE status = "A"                          )
ORDER BY user_id ASC

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

Select and Count

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

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

Select and Distinct

SELECT DISTINCT(status)                     db.people.aggregate(
FROM people                                     [ { $group : { _id : "$status" } } ]
                                            )
                                            -or-
                                            db.people.distinct( "status" )

Select limitedly

SELECT *                                    db.people.findOne()
FROM people                                 -or-
LIMIT 1                                     db.people.find().limit(1)

SELECT *                                    db.people.find().limit(5).skip(10)
FROM people
LIMIT 5
SKIP 10

Explain Select

EXPLAIN SELECT *                            db.people.find( { status: "A" } ).explain()
FROM people
WHERE status = "A"

See also:

• db.collection.find()
• db.collection.distinct()
• db.collection.findOne()
• Query operators: $ne, $and, $or, $gt, $lt, $exists, $lte, and $regex.
• limit()
• skip()
• explain()
• sort()
• count()

Update

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

See also

db.collection.updateMany(), $set, $inc, and $gt.

Delete

DELETE FROM people                          db.people.deleteMany( { status: "D" } )
WHERE status = "D"

DELETE FROM people                          db.people.deleteMany({})

See also

db.collection.deleteMany().

MongoDB 与SQL操作映射

标签：creat   medium   models   example   xpl   cursor   bcd   pts   opera