MongoDb与其他关系型数据库的CRUD操作对照关系

一、术语与概念对照表
    这个表格主要说明mongodb与其他关系型数据库在术语与概念上的区别。
 
 
SQL Terms/ConceptsMongoDB Terms/Concepts
database database
table collection
row document or BSON document
column field
index index
table joins embedded documents and linking

primary key

Specify any unique column or column combination as primary key.

primary key

In MongoDB, the primary key is automatically set to the_id field.
aggregation (e.g. group by)

aggregation pipeline

See the SQL to Aggregation Mapping Chart.

二、示例创建和修改数据表结构(create and alter) 创建数据库的操作请查看我的这篇文章http://www.cnblogs.com/whynever/p/3801150.html mongodb的基本操作

SQL Schema StatementsMongoDB 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")

ALTER TABLE users
ADD join_date DATETIME

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

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

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

ALTER TABLE users
DROP COLUMN join_date

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

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

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

CREATE INDEX idx_user_id_asc
ON users(user_id)

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

CREATE INDEX
       idx_user_id_asc_age_desc
ON users(user_id, age DESC)

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

DROP TABLE users

db.users.drop()

三、插入数据(insert save)

SQL INSERT StatementsMongoDB insert() Statements
 
INSERT INTO users(user_id,  age, status)
VALUES ("bcd001", 45, "A")

db.users.insert(
   { user_id: "bcd001", age: 45, status: "A" }
)
        关于插入数据的更多信息(http://docs.mongodb.org/manual/reference/method/db.collection.insert/#db.collection.insert)

四、查询数据(select操作)

SQL SELECT StatementsMongoDB find() Statements
 
SELECT * FROM users

db.users.find()

SELECT id,  user_id, status FROM users

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

SELECT user_id, status FROM users

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

SELECT * FROM users WHERE status = "A"

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

SELECT user_id, status FROM users WHERE status = "A"

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

SELECT *
FROM users
WHERE status != "A"

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

SELECT *
FROM users
WHERE status = "A"
AND age = 50

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

SELECT *
FROM users
WHERE status = "A"
OR age = 50

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

SELECT *
FROM users
WHERE age > 25

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

SELECT *
FROM users
WHERE age < 25

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

SELECT *
FROM users
WHERE age > 25
AND   age <= 50

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

SELECT *
FROM users
WHERE user_id like "%bc%"

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

SELECT *
FROM users
WHERE user_id like "bc%"

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

SELECT *
FROM users
WHERE status = "A"
ORDER BY user_id ASC

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

SELECT *
FROM users
WHERE status = "A"
ORDER BY user_id DESC

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

SELECT COUNT(*) FROM users

db.users.count() 或 db.users.find().count()
 
SELECT COUNT(user_id) FROM users

db.users.count( { user_id: { $exists: true } } ) 或 db.users.find( { user_id: { $exists: true } } ).count()
 
SELECT COUNT(*) FROM users WHERE age > 30

db.users.count( { age: { $gt: 30 } } ) 或 db.users.find( { age: { $gt: 30 } } ).count()
 
SELECT DISTINCT(status) FROM users

db.users.distinct( "status" )

SELECT * FROM users LIMIT 1

db.users.findOne() 或 db.users.find().limit(1)
 
SELECT * FROM users LIMIT 5 SKIP 10

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

EXPLAIN SELECT * FROM users WHERE status = "A"

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

五、更新数据(Update操作)

SQL Update StatementsMongoDB update() Statements
 
UPDATE users SET status = "C" WHERE age > 25

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

UPDATE users SET age = age + 3 WHERE status = "A"

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

六、删除数据

SQL Delete Statements(SQL操作)MongoDB remove() Statements(Mongo操作)
 
DELETE FROM users WHERE status = "D"

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

DELETE FROM users

db.users.remove({})
 更多信息，点击这里查看官方文档http://docs.mongodb.org/manual/reference/sql-comparison/
 
附录：一些聚合函数的术语与概念对照表
SQL Terms, Functions, and ConceptsMongoDB Aggregation Operators
WHERE $match
GROUP BY $group
HAVING $match
SELECT $project
ORDER BY $sort
LIMIT $limit
SUM() $sum
COUNT() $sum
join No direct corresponding operator; however, the $unwindoperator allows for somewhat similar functionality, but with fields embedded within the document.
posted @ 2014-06-21 17:34  北羽  阅读(474)  评论(0)    收藏  举报