Sql与Mongodb 查询映射图

SQL to MongoDB Mapping Chart

In addition to the charts that follow, you might want to consider theFrequently Asked Questions section for a selection of common questions about MongoDB.

Executables

The following table presents the MySQL/Oracle executables and thecorresponding MongoDB executables.

 MySQL/OracleMongoDBDatabase Servermysqld/oraclemongodDatabase Clientmysql/sqlplusmongo

Terminology and Concepts

The following table presents the various SQL terminology and conceptsand the corresponding MongoDB terminology and concepts.

SQL Terms/ConceptsMongoDB Terms/Conceptsdatabasedatabasetablecollectionrowdocument orBSON documentcolumnfieldindexindextable joinsembedded documents and linking

primary key

Specify any unique column or column combination as primarykey.

primary key

In MongoDB, the primary key is automatically set to the_id field.

aggregation (e.g. group by)

aggregation framework

See the SQL to Aggregation Framework Mapping Chart.

Examples

The following table presents the various SQL statements and thecorresponding MongoDB statements. The examples in the table assume thefollowing conditions:

• The SQL examples assume a table named users.

• The MongoDB examples assume a collection named users that containdocuments of the following prototype:

  {  _id: ObjectID("509a8fb2f3f4948bd2f983a0"),  user_id: "abc123",  age: 55,  status: 'A'}

Create and Alter

The following table presents the various SQL statements related totable-level actions and the corresponding MongoDB statements.

SQL Schema StatementsMongoDB Schema StatementsReference

CREATE TABLE users (    id MEDIUMINT NOT NULL        AUTO_INCREMENT,    user_id Varchar(30),    age Number,    status char(1),    PRIMARY KEY (id))

Implicitly created on first insert operation. The primary key_idis automatically added if_id field is not specified.

db.users.insert( {    user_id: "abc123",    age: 55,    status: "A" } )

However, you can also explicitly create a collection:

db.createCollection("users")

Seeinsert() andcreateCollection()for more information.

ALTER TABLE usersADD join_date DATETIME

Collections do not describe or enforce the structure of itsdocuments; i.e. there is no structural alteration at thecollection level.

However, at the document level, update() operations can add fields to existingdocuments using the$set operator.

db.users.update(    { },    { $set: { join_date: new Date() } },    { multi: true })

See the Data Modeling Considerations for MongoDB Applications, update(), and$set for moreinformation on changing the structure of documents in acollection.

ALTER TABLE usersDROP COLUMN join_date

Collections do not describe or enforce the structure of itsdocuments; i.e. there is no structural alteration at the collectionlevel.

However, at the document level, update() operations can remove fields fromdocuments using the$unset operator.

db.users.update(    { },    { $unset: { join_date: "" } },    { multi: true })

See Data Modeling Considerations for MongoDB Applications, update(), and$unset for moreinformation on changing the structure of documents in acollection.

CREATE INDEX idx_user_id_ascON users(user_id)

db.users.ensureIndex( { user_id: 1 } )

See ensureIndex()andindexes for more information.

CREATE INDEX       idx_user_id_asc_age_descON users(user_id, age DESC)

db.users.ensureIndex( { user_id: 1, age: -1 } )

See ensureIndex()andindexes for more information.

DROP TABLE users

db.users.drop()

See drop() formore information.

Insert

The following table presents the various SQL statements related toinserting records into tables and the corresponding MongoDB statements.

SQL INSERT StatementsMongoDB insert() StatementsReference

INSERT INTO users(user_id,                  age,                  status)VALUES ("bcd001",        45,        "A")

db.users.insert( {       user_id: "bcd001",       age: 45,       status: "A"} )

See insert() for more information.

Select

The following table presents the various SQL statements related toreading records from tables and the corresponding MongoDB statements.

SQL SELECT StatementsMongoDB find() StatementsReference

SELECT *FROM users

db.users.find()

See find()for more information.

SELECT id, user_id, statusFROM users

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

See find()for more information.

SELECT user_id, statusFROM users

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

See find()for more information.

SELECT *FROM usersWHERE status = "A"

db.users.find(    { status: "A" })

See find()for more information.

SELECT user_id, statusFROM usersWHERE status = "A"

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

See find()for more information.

SELECT *FROM usersWHERE status != "A"

db.users.find(    { status: { $ne: "A" } })

See find()and$ne for more information.

SELECT *FROM usersWHERE status = "A"AND age = 50

db.users.find(    { status: "A",      age: 50 })

See find()and$and for more information.

SELECT *FROM usersWHERE status = "A"OR age = 50

db.users.find(    { $or: [ { status: "A" } ,             { age: 50 } ] })

See find()and$or for more information.

SELECT *FROM usersWHERE age > 25

db.users.find(    { age: { $gt: 25 } })

See find()and$gt for more information.

SELECT *FROM usersWHERE age < 25

db.users.find(   { age: { $lt: 25 } })

See find()and$lt for more information.

SELECT *FROM usersWHERE age > 25AND   age <= 50

db.users.find(   { age: { $gt: 25, $lte: 50 } })

See find(),$gt, and $lte formore information.

SELECT *FROM usersWHERE user_id like "%bc%"

db.users.find(   { user_id: /bc/ })

See find()and$regex for more information.

SELECT *FROM usersWHERE user_id like "bc%"

db.users.find(   { user_id: /^bc/ })

See find()and$regex for more information.

SELECT *FROM usersWHERE status = "A"ORDER BY user_id ASC

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

See find()andsort()for more information.

SELECT *FROM usersWHERE status = "A"ORDER BY user_id DESC

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

See find()andsort()for more information.

SELECT COUNT(*)FROM users

db.users.count()

or

db.users.find().count()

See find()andcount() formore information.

SELECT COUNT(user_id)FROM users

db.users.count( { user_id: { $exists: true } } )

or

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

See find(),count(), and$exists for more information.

SELECT COUNT(*)FROM usersWHERE age > 30

db.users.count( { age: { $gt: 30 } } )

or

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

See find(),count(), and$gt for more information.

SELECT DISTINCT(status)FROM users

db.users.distinct( "status" )

See find()anddistinct()for more information.

SELECT *FROM usersLIMIT 1

db.users.findOne()

or

db.users.find().limit(1)

See find(),findOne(),andlimit()for more information.

SELECT *FROM usersLIMIT 5SKIP 10

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

See find(),limit(), andskip() formore information.

EXPLAIN SELECT *FROM usersWHERE status = "A"

db.users.find( { status: "A" } ).explain()

See find()andexplain()for more information.

Update Records

The following table presents the various SQL statements related toupdating existing records in tables and the corresponding MongoDBstatements.

SQL Update StatementsMongoDB update() StatementsReference

UPDATE usersSET status = "C"WHERE age > 25

db.users.update(   { age: { $gt: 25 } },   { $set: { status: "C" } },   { multi: true })

See update(),$gt, and $set for moreinformation.

UPDATE usersSET age = age + 3WHERE status = "A"

db.users.update(   { status: "A" } ,   { $inc: { age: 3 } },   { multi: true })

See update(),$inc, and $set for moreinformation.

Delete Records

The following table presents the various SQL statements related todeleting records from tables and the corresponding MongoDB statements.

SQL Delete StatementsMongoDB remove() StatementsReference

DELETE FROM usersWHERE status = "D"

db.users.remove( { status: "D" } )

See remove()for more information.

DELETE FROM users

db.users.remove( )

See remove()for more information.