【原】Mongodb相关资料
Mongodb与关系型数据库对比
Mongodb与关系型数据库对比
由于之前主要接触的是关系型数据库，所以主要将Mongodb与关系型数据库进行对比：主要从术语、Server与Client、数据定义语言和操作语言四个方面进行比较。
1.术语
Mongodb与关系型数据库的术语对比如下图
| 关系型数据库术语 | MongoDB 术语 |
|---|---|
| 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. |
2.数据库Server与Client对比
| type | MongoDB MySQL | Oracle | Informix | DB2 |
|---|---|---|---|---|
| Database Server | mongod | mysqld | oracle | IDS |
| Database Client | mongo | mysql | sqlplus | DB-Access |
3.数据定义语言CREATE、ALTER、DROP比较
| 关系型数据库SQL语句 | MongoDB语句 |
|---|---|
| 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 usersADD 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 usersDROP 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_ascON users(user_id) | db.users.createIndex( { user_id: 1 } ) |
| CREATE INDEX idx_user_id_asc_age_descON users(user_id, age DESC) | db.users.createIndex( { user_id: 1, age: -1 } ) |
| DROP TABLE users | db.users.drop() |
4.数据操作语言CURD比较
INSERT
| SQL INSERT 语句 | MongoDB insert语句 |
|---|---|
| INSERT INTO users(user_id,age,status)VALUES ("bcd001",45,"A") | db.users.insert( { user_id: "bcd001", age: 45, status: "A" }) |
SELECT
| SQL SELECT 语句 | MongoDB find 语句 |
|---|---|
| SELECT *FROM users | db.users.find() |
| SELECT id,user_id,statusFROM 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 usersWHERE status = "A" | db.users.find( { status: "A" }) |
| SELECT user_id, statusFROM usersWHERE status = "A" | db.users.find( { status: "A" }, { user_id: 1, status: 1, _id: 0 }) |
| SELECT *FROM usersWHERE status != "A" | db.users.find( { status: { $ne: "A" } }) |
| SELECT *FROM usersWHERE status = "A"AND age = 50 | db.users.find( { status: "A", age: 50 }) |
| SELECT *FROM usersWHERE status = "A"OR age = 50 | db.users.find( { $or: [ { status: "A" } , { age: 50 } ] }) |
| SELECT *FROM usersWHERE age > 25 | db.users.find( { age: { $gt: 25 } }) |
| SELECT *FROM usersWHERE age < 25 | db.users.find( { age: { $lt: 25 } }) |
| SELECT *FROM usersWHERE age > 25AND age <= 50 | db.users.find( { age: { $gt: 25, $lte: 50 } }) |
| SELECT *FROM usersWHERE user_id like "%bc%" | db.users.find( { user_id: /bc/ } ) |
| SELECT *FROM usersWHERE user_id like "bc%" | db.users.find( { user_id: /^bc/ } ) |
| SELECT *FROM usersWHERE status = "A"ORDER BY user_id ASC | db.users.find( { status: "A" } ).sort( { user_id: 1 } ) |
| SELECT *FROM usersWHERE status = "A"ORDER BY user_id DESC | db.users.find( { status: "A" } ).sort( { user_id: -1 } ) |
| SELECT COUNT(*)FROM users | db.users.count() or db.users.find().count() |
| SELECT COUNT(user_id)FROM users | db.users.count( { user_id: { $exists: true } } ) or db.users.find( { user_id: { $exists: true } } ).count() |
| SELECT COUNT(*)FROM users WHERE age > 30 | db.users.count( { age: { $gt: 30 } } ) or db.users.find( { age: { $gt: 30 } } ).count() |
| SELECT DISTINCT(status)FROM users | db.users.distinct( "status" ) |
| SELECT *FROM usersLIMIT 1 | db.users.findOne() or db.users.find().limit(1) |
| SELECT *FROM usersLIMIT 5SKIP 10 | db.users.find().limit(5).skip(10) |
| EXPLAIN SELECT *FROM usersWHERE status = "A" | db.users.find( { status: "A" } ).explain() |
UPDATE
| SQL Updatey语句 | MongoDB update语句 |
|---|---|
| UPDATE usersSET status = "C"WHERE age > 25 | db.users.update( { age: { $gt: 25 } }, { $set: { status: "C" } }, { multi: true }) |
| UPDATE usersSET age = age + 3WHERE status = "A" | db.users.update( { status: "A" } , { $inc: { age: 3 } }, { multi: true }) |
DELETE
| SQL Delete 语句 | MongoDB remove语句 |
|---|---|
| DELETE FROM usersWHERE status = "D" | db.users.remove( { status: "D" } ) |
| DELETE FROM users | db.users.remove({}) |
学习资料
网址：
Mongodb官方文档感觉写的真心不错，推荐！
