Comparison between MongoDB study notes 2 and traditional SQL

Source: Internet
Author: User
Tags mongo shell
The following is an official comparison, which is clear and clear.

More information: http://www.mongodb.org/display/DOCS/Manual

SQL to Mongo mapping chart
MySQL executable Oracle executable Mongo executable
Mysqld
Oracle
Mongod

MySQL
Sqlplus
Mongo
MySQL term Mongo term
Database Database
Table Collection
Index Index
Row Bson document
Column Bson Field
Join Embedding and linking
Primary Key _ Id field

MongoDB queries are expressed as JSON (bson) objects. The following chart shows examples as both SQL and in Mongo query language syntax.

The query expression in MongoDB (and other things, such as index key patterns) is represented as JSON (bson ). however, the actual verb (e.g. "find") is done in one's regular programming language; thus the exact forms of these verbs vary by language. the
Examples below are Javascript and can be executed from
Mongo shell.

SQL statement
Mongo statement
CREATE TABLE USERS (a Number, b Number)
Implicit; can also be done
Explicitly

db.createCollection("mycoll")
ALTER TABLE users ADD ...
Implicit
   
INSERT INTO USERS VALUES(3,5)
db.users.insert({a:3,b:5})
   
SELECT a,b FROM users
db.users.find({}, {a:1,b:1})
SELECT * FROM users
db.users.find()
SELECT * FROM users WHERE age=33
db.users.find({age:33})
SELECT a,b FROM users WHERE age=33
db.users.find({age:33}, {a:1,b:1})
SELECT * FROM users WHERE age=33 ORDER BY name
db.users.find({age:33}).sort({name:1})
SELECT * FROM users WHERE age>33
db.users.find({age:{$gt:33}})
SELECT * FROM users WHERE age!=33
db.users.find({age:{$ne:33}})
SELECT * FROM users WHERE name LIKE "%Joe%"
db.users.find({name:/Joe/})
SELECT * FROM users WHERE name LIKE "Joe%"
db.users.find({name:/^Joe/})
SELECT * FROM users WHERE age>33 AND age<=40
db.users.find({'age':{$gt:33,$lte:40}})
SELECT * FROM users ORDER BY name DESC
db.users.find().sort({name:-1})
SELECT * FROM users WHERE a=1 and b='q'
db.users.find({a:1,b:'q'})
SELECT * FROM users LIMIT 10 SKIP 20
db.users.find().limit(10).skip(20)
SELECT * FROM users WHERE a=1 or b=2
db.users.find( { $or : [ { a : 1 } , { b : 2 } ] } )
SELECT * FROM users LIMIT 1
db.users.findOne()
SELECT DISTINCT last_name FROM users
db.users.distinct('last_name')
SELECT COUNT(*y)FROM users
db.users.count()
SELECT COUNT(*y)FROM users where AGE > 30
db.users.find({age: {'$gt': 30}}).count()
SELECT COUNT(AGE) from users
db.users.find({age: {'$exists': true}}).count()
   
CREATE INDEX myindexname ON users(name)
db.users.ensureIndex({name:1})
CREATE INDEX myindexname ON users(name,ts DESC)
db.users.ensureIndex({name:1,ts:-1})
   
EXPLAIN SELECT * FROM users WHERE z=3
db.users.find({z:3}).explain()
   
UPDATE users SET a=1 WHERE b='q'
db.users.update({b:'q'}, {$set:{a:1}}, false, true)
UPDATE users SET a=a+2 WHERE b='q'
db.users.update({b:'q'}, {$inc:{a:2}}, false, true)
   
DELETE FROM users WHERE z="abc"
db.users.remove({z:'abc'});

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.