SQL to MongoDB
来源:互联网 发布:欧洲杯 网络直播 编辑:程序博客网 时间:2024/05/22 02:26
In addition to the charts that follow, you might want to consider the Frequently Asked Questions section for a selection of common questions about MongoDB.
Executables
The following table presents the MySQL/Oracle executables and the corresponding MongoDB executables.
Terminology and Concepts
The following table presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.
primary key
Specify any unique column or column combination as primary key.
primary key
In MongoDB, the primary key is automatically set to the _idfield.
aggregation (e.g. group by)aggregation framework
See the SQL to Aggregation Framework Mapping Chart.
Examples
The following table presents the various SQL statements and the corresponding MongoDB statements. The examples in the table assume the following conditions:
The SQL examples assume a table named users.
The MongoDB examples assume a collection named users that contain documents of the following prototype:
{ _id: ObjectID("509a8fb2f3f4948bd2f983a0"), user_id: "abc123", age: 55, status: 'A'}
Create and Alter
The following table presents the various SQL statements related to table-level actions and the corresponding MongoDB 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 usersADD join_date DATETIME
ALTER TABLE usersDROP COLUMN join_date
CREATE INDEX idx_user_id_ascON users(user_id)
db.users.ensureIndex( { user_id: 1 } )
CREATE INDEX idx_user_id_asc_age_descON users(user_id, age DESC)
db.users.ensureIndex( { user_id: 1, age: -1 } )
DROP TABLE users
db.users.drop()
Insert
The following table presents the various SQL statements related to inserting records into tables and the corresponding MongoDB statements.
INSERT INTO users(user_id, age, status)VALUES ("bcd001", 45, "A")
db.users.insert( { user_id: "bcd001", age: 45, status: "A"} )
Select
The following table presents the various SQL statements related to reading records from tables and the corresponding MongoDB statements.
SELECT *FROM users
db.users.find()
SELECT id, user_id, statusFROM users
db.users.find( { }, { user_id: 1, status: 1 })
SELECT user_id, statusFROM 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 usersWHERE 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 Records
The following table presents the various SQL statements related to updating existing records in tables and the corresponding MongoDB statements.
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 Records
The following table presents the various SQL statements related to deleting records from tables and the corresponding MongoDB statements.
DELETE FROM usersWHERE status = "D"
db.users.remove( { status: "D" } )
DELETE FROM users
db.users.remove( )
- SQL-to-MongoDB
- SQL to MongoDB
- Translate SQL to MongoDB MapReduce
- SQL to MongoDB Mapping Chart
- mongodb:SQL to Aggregation Mapping Chart
- SQL to MongoDB: An Updated Mapping
- mysql与MOngodb书写形式转化SQL-to-MongoDB
- Mongodb is vulnerable to SQL injection in PHP at least
- introduction to mongoDB(介绍 mongodb)
- start to learn MongoDB
- mongodb sql query
- MySQL MongoDB SQL 对应
- SQL 和 MongoDB
- mongoDB应用(sql语句)
- MongoDB Sql语法
- SQL数据导入mongodb
- mongodb-drill for sql
- MongoDB数据库SQl语法
- 山东省第七届ACM省赛——Memory Leak
- 浏览器缓存
- Bessie hears that an extraordinary meteor shower is coming; reports say that these meteors will cras
- NAT 原理与NAT穿越
- JS拖拽元素 兼容IE (未测试)
- SQL to MongoDB
- Matrix67的情书 题解 恺撒移位密码
- (4.2.35.1)数据加密:Conceal
- 程序员如何谋划出月薪3万
- 常用知识备份
- FZU-2150-Fire Game
- HashMap的工作原理
- JS实现无刷新倒计时(未测试)
- 使用nmap 验证多种漏洞