似曾相识的MongoDB

Create and Alter¶

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

SQL Schema StatementsMongoDB Schema Statements
  1. 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.
  1. db.users.insert( {    user_id: "abc123",    age: 55,    status: "A" } )
However, you can also explicitly create a collection:
  1. db.createCollection("users")
  1. 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.
  1. db.users.update(    { },    { $set: { join_date: new Date() } },    { multi: true })
  1. 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.
  1. db.users.update(    { },    { $unset: { join_date: "" } },    { multi: true })
  1. CREATE INDEX idx_user_id_ascON users(user_id)
  1. db.users.createIndex( { user_id: 1 } )
  1. CREATE INDEX       idx_user_id_asc_age_descON users(user_id, age DESC)
  1. db.users.createIndex( { user_id: 1, age: -1 } )
  1. DROP TABLE users
  1. db.users.drop()

For more information, see db.collection.insert(),db.createCollection(), db.collection.update(),$set, $unset,db.collection.createIndex(), indexes,db.collection.drop(), and Data Modeling Concepts.

Insert¶

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

SQL INSERT StatementsMongoDB insert() Statements
  1. INSERT INTO users(user_id,                  age,                  status)VALUES ("bcd001",        45,        "A")
  1. db.users.insert(   { user_id: "bcd001", age: 45, status: "A" })

For more information, see db.collection.insert().

Select¶

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

Note

The find() method always includes the idfield in the returned documents unless specifically excluded through[_projection](https://piaosanlang.gitbooks.io/mongodb/tutorial/project-fields-from-query-results#projection). Some of the SQL queries below may include an_id field to reflect this, even if the field is not included in thecorresponding find() query.

SQL SELECT StatementsMongoDB find() Statements
  1. SELECT FROM users
  1. db.users.find()
  1. SELECT id,       user_id,       statusFROM users
  1. db.users.find(    { },    { user_id: 1, status: 1 })
  1. SELECT user_id, statusFROM users
  1. db.users.find(    { },    { user_id: 1, status: 1, _id: 0 })
  1. SELECT FROM usersWHERE status = "A"
  1. db.users.find(    { status: "A" })
  1. SELECT userid, statusFROM usersWHERE status = "A"
  1. db.users.find(    { status: "A" },    { user_id: 1, status: 1, _id: 0 })
  1. SELECT FROM usersWHERE status != "A"
  1. db.users.find(    { status: { $ne: "A" } })
  1. SELECT FROM usersWHERE status = "A"AND age = 50
  1. db.users.find(    { status: "A",      age: 50 })
  1. SELECT FROM usersWHERE status = "A"OR age = 50
  1. db.users.find(    { $or: [ { status: "A" } ,             { age: 50 } ] })
  1. SELECT FROM usersWHERE age > 25
  1. db.users.find(    { age: { $gt: 25 } })
  1. SELECT FROM usersWHERE age < 25
  1. db.users.find(   { age: { $lt: 25 } })
  1. SELECT FROM usersWHERE age > 25AND   age <= 50
  1. db.users.find(   { age: { $gt: 25, $lte: 50 } })
  1. SELECT FROM usersWHERE user_id like "%bc%"
  1. db.users.find( { user_id: /bc/ } )
  1. SELECT FROM usersWHERE user_id like "bc%"
  1. db.users.find( { user_id: /^bc/ } )
  1. SELECT FROM usersWHERE status = "A"ORDER BY user_id ASC
  1. db.users.find( { status: "A" } ).sort( { user_id: 1 } )
  1. SELECT FROM usersWHERE status = "A"ORDER BY user_id DESC
  1. db.users.find( { status: "A" } ).sort( { user_id: -1 } )
  1. SELECT COUNT(*)FROM users
  1. db.users.count()
_or
  1. db.users.find().count()
  1. SELECT COUNT(userid)FROM users
  1. db.users.count( { user_id: { $exists: true } } )
_or
  1. db.users.find( { userid: { $exists: true } } ).count()
  1. SELECT COUNT(*)FROM usersWHERE age > 30
  1. db.users.count( { age: { $gt: 30 } } )
_or
  1. db.users.find( { age: { $gt: 30 } } ).count()
  1. SELECT DISTINCT(status)FROM users
  1. db.users.distinct( "status" )
  1. SELECT FROM usersLIMIT 1
  1. db.users.findOne()
or
  1. db.users.find().limit(1)
  1. SELECT FROM usersLIMIT 5SKIP 10
  1. db.users.find().limit(5).skip(10)
  1. EXPLAIN SELECT *FROM usersWHERE status = "A"
  1. db.users.find( { status: "A" } ).explain()

For more information, seedb.collection.find(), db.collection.distinct(),db.collection.findOne(), $ne$and, $or, $gt, $lt,$exists, $lte, $regex,limit(), skip(),explain(), sort(), andcount().

Select¶

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

Note

The find() method always includes the idfield in the returned documents unless specifically excluded through[_projection](https://piaosanlang.gitbooks.io/mongodb/tutorial/project-fields-from-query-results#projection). Some of the SQL queries below may include an_id field to reflect this, even if the field is not included in thecorresponding find() query.

SQL SELECT StatementsMongoDB find() Statements
  1. SELECT FROM users
  1. db.users.find()
  1. SELECT id,       user_id,       statusFROM users
  1. db.users.find(    { },    { user_id: 1, status: 1 })
  1. SELECT user_id, statusFROM users
  1. db.users.find(    { },    { user_id: 1, status: 1, _id: 0 })
  1. SELECT FROM usersWHERE status = "A"
  1. db.users.find(    { status: "A" })
  1. SELECT userid, statusFROM usersWHERE status = "A"
  1. db.users.find(    { status: "A" },    { user_id: 1, status: 1, _id: 0 })
  1. SELECT FROM usersWHERE status != "A"
  1. db.users.find(    { status: { $ne: "A" } })
  1. SELECT FROM usersWHERE status = "A"AND age = 50
  1. db.users.find(    { status: "A",      age: 50 })
  1. SELECT FROM usersWHERE status = "A"OR age = 50
  1. db.users.find(    { $or: [ { status: "A" } ,             { age: 50 } ] })
  1. SELECT FROM usersWHERE age > 25
  1. db.users.find(    { age: { $gt: 25 } })
  1. SELECT FROM usersWHERE age < 25
  1. db.users.find(   { age: { $lt: 25 } })
  1. SELECT FROM usersWHERE age > 25AND   age <= 50
  1. db.users.find(   { age: { $gt: 25, $lte: 50 } })
  1. SELECT FROM usersWHERE user_id like "%bc%"
  1. db.users.find( { user_id: /bc/ } )
  1. SELECT FROM usersWHERE user_id like "bc%"
  1. db.users.find( { user_id: /^bc/ } )
  1. SELECT FROM usersWHERE status = "A"ORDER BY user_id ASC
  1. db.users.find( { status: "A" } ).sort( { user_id: 1 } )
  1. SELECT FROM usersWHERE status = "A"ORDER BY user_id DESC
  1. db.users.find( { status: "A" } ).sort( { user_id: -1 } )
  1. SELECT COUNT(*)FROM users
  1. db.users.count()
_or
  1. db.users.find().count()
  1. SELECT COUNT(userid)FROM users
  1. db.users.count( { user_id: { $exists: true } } )
_or
  1. db.users.find( { userid: { $exists: true } } ).count()
  1. SELECT COUNT(*)FROM usersWHERE age > 30
  1. db.users.count( { age: { $gt: 30 } } )
_or
  1. db.users.find( { age: { $gt: 30 } } ).count()
  1. SELECT DISTINCT(status)FROM users
  1. db.users.distinct( "status" )
  1. SELECT FROM usersLIMIT 1
  1. db.users.findOne()
or
  1. db.users.find().limit(1)
  1. SELECT FROM usersLIMIT 5SKIP 10
  1. db.users.find().limit(5).skip(10)
  1. EXPLAIN SELECT *FROM usersWHERE status = "A"
  1. db.users.find( { status: "A" } ).explain()

For more information, seedb.collection.find(), db.collection.distinct(),db.collection.findOne(), $ne$and, $or, $gt, $lt,$exists, $lte, $regex,limit(), skip(),explain(), sort(), andcount().

Delete Records¶

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

SQL Delete StatementsMongoDB remove() Statements
  1. DELETE FROM usersWHERE status = "D"
  1. db.users.remove( { status: "D" } )
  1. DELETE FROM users
  1. db.users.remove({})

For more information, see db.collection.remove().