section
SQL
MongoDB
Delete Records
DELETE FROM users WHERE status = 'D'
db.users.remove( { status: 'D' } )
Delete Records
DELETE FROM users
db.users.remove()
SQL Update Statements
UPDATE users SET status = 'C' WHERE age > 25
db.users.update({ age: { $gt: 25 } },{ $set: { status: 'C' } },{ multi: true })
SQL Update Statements
UPDATE users SET age = age + 3 WHERE status = 'A'
db.users.update({ status: 'A'},{ $inc: { age: 3 } },{ multi: true })
SQL SELECT Statements
SELECT * FROM users
db.users.find()
SQL SELECT Statements
SELECT id, user_id, status FROM users
db.users.find({ },{ user_id: 1, status: 1 })
SQL SELECT Statements
SELECT user_id, status FROM users
db.users.find({ },{ user_id: 1, status: 1, _id: 0 })
SQL SELECT Statements
SELECT * FROM users WHERE status = 'A'
db.users.find({ status: A" })"
SQL SELECT Statements
SELECT user_id, status FROM users WHERE status = 'A'
db.users.find({ status: 'A' },{ user_id: 1, status: 1, _id: 0 })
SQL SELECT Statements
SELECT * FROM users WHERE status != 'A'
db.users.find({ status: { $ne: A" } })"
SQL SELECT Statements
SELECT * FROM users WHERE status = 'A' AND age = 50
db.users.find({ status: 'A',age: 50 })
SQL SELECT Statements
SELECT * FROM users WHERE status = 'A' OR age = 50
db.users.find({ $or: [ { status: 'A' } ,{ age: 50 } ] })
SQL SELECT Statements
SELECT * FROM users WHERE age > 25
db.users.find({ age: { $gt: 25 } })
SQL SELECT Statements
SELECT * FROM users WHERE age < 25
db.users.find({ age: { $lt: 25 } })
SQL SELECT Statements
SELECT * FROM users WHERE age > 25 AND age <= 50
db.users.find({ age: { $gt: 25, $lte: 50 } })
SQL SELECT Statements
SELECT * FROM users WHERE user_id like '%bc%'
db.users.find({ user_id: /bc/ })
SQL SELECT Statements
SELECT * FROM users WHERE user_id like 'bc%'
db.users.find({ user_id: /^bc/ })
SQL SELECT Statements
SELECT * FROM users WHERE status = 'A' ORDER BY user_id ASC
db.users.find( { status: 'A' } ).sort( { user_id: 1 } )
SQL SELECT Statements
SELECT * FROM users WHERE status = 'A' ORDER BY user_id DESC
db.users.find( { status: 'A' } ).sort( { user_id: -1 } )
SQL SELECT Statements
SELECT COUNT(*) FROM users
db.users.count()
SQL SELECT Statements
SELECT COUNT(*) FROM users
db.users.find().count()
SQL SELECT Statements
SELECT COUNT(user_id) FROM users
db.users.count( { user_id: { $exists: true } } )
SQL SELECT Statements
SELECT COUNT(user_id) FROM users
db.users.find( { user_id: { $exists: true } } ).count()
SQL SELECT Statements
SELECT COUNT(*) FROM users WHERE age > 30
db.users.count( { age: { $gt: 30 } } )
SQL SELECT Statements
SELECT * FROM users LIMIT 1
db.users.findOne()
SQL SELECT Statements
SELECT * FROM users LIMIT 1
db.users.find().limit(1)
SQL SELECT Statements
SELECT * FROM users LIMIT 5 SKIP 10
db.users.find().limit(5).skip(10)
SQL SELECT Statements
EXPLAIN SELECT * FROM users WHERE status = 'A'
db.users.find( { status: 'A' } ).explain()
SQL INSERT Statements
INSERT INTO users(user_id,age,status) VALUES ('bcd001',45,'A')
db.users.insert( {user_id: 'bcd001',age: 45,status: 'A'} )
SQL Schema Statements
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 _id is 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')
SQL Schema Statements
ALTER TABLE users ADD join_date DATETIME
Collections do not describe or enforce the structure of the constituent documents. See the Schema Design wiki page for more information. See update() and $set for more information on changing the structure of documents in a collection.
SQL Schema Statements
ALTER TABLE users DROP COLUMN join_date
Collections do not describe or enforce the structure of the constituent documents. See the Schema Design wiki page for more information. See update() and $set for more information on changing the structure of documents in a collection.
SQL Schema Statements
CREATE INDEX idx_user_id_asc ON users(user_id)
db.users.ensureIndex( { user_id: 1 } )
SQL Schema Statements
CREATE INDEX idx_user_id_asc_age_desc ON users(user_id, age DESC)
db.users.ensureIndex( { user_id: 1, age: -1 } )
SQL Schema Statements
DROP TABLE users
db.users.drop()
FAQ
how to SHOW DATABASES
show databases, show dbs, db.adminCommand('listDatabases'), db.getMongo().getDBNames()
FAQ
how to SHOW COLLECTIONS
show collections, show tables, db.getCollectionNames()
FAQ
how to authorise
db.auth({user:'root',pwd:'example'})
FAQ
how to create database?
use test
FAQ
how to insert
db.test.insert({name:Alex"
FAQ
how to do select
db.test.find()
FAQ
how to update record?
db.test.update({name:'Alex'},{$set:{lasttask:'Testing'}})
FAQ
how to increment record
db.test.update({name:'Alex'},{$inc:{age:5}})
FAQ
how to remove field
db.test.update({name:'Alex'},{$unset:{lasttask:1}})
FAQ
how to insert if not found
db.test.update({name:'Mary'},{$set:{lasttask:'Testing'}},{upsert:true})