基本概念对比
SQL Terms/Concepts | MongoDB 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. |
执行
The following table presents the MySQL/Oracle executables and the corresponding MongoDB executables.
| MySQL/Oracle | MongoDB |
---|
Database Server | mysqld/oracle | mongod |
Database Client | mysql/sqlplus | mongo |
sql表结构 users
id user_id age status
1 "abc123" 55 'A'
mongo集合结构:
{
_id: ObjectId("509a8fb2f3f4948bd2f983a0"),
user_id: "abc123",
age: 55,
status: 'A'}
由于mongodb没有字段结构的Schema,字段类型可以很方便改变或增加,如果要查找没有存在某个字段的数据,可以db.users.find({age:{$exists:false}})
创建和修改
SQL Schema Statements | MongoDB Schema Statements |
---|
CREATE TABLE users (
id MEDIUMINT NOT NULL
AUTO_INCREMENT,
user_id Varchar(30),
age Number,
status char(1),
PRIMARY KEY (id))
|
db.users.insert( { user_id: "abc123",
age: 55, status: "A" } )
或者 db.createCollection("users")
|
ALTER TABLE usersADD join_date DATETIME
|
db.users.update({ },
{ $set:{ join_date: new Date() } },
{ multi: true })
|
ALTER TABLE usersDROP COLUMN join_date
|
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()
|
mongodb包含了等值测试、排序及范围过滤查询的组合索引建立方法:
1. 等值测试
在索引中加入所有需要做等值测试的字段,任意顺序。
2. 排序字段(多排序字段的升/降序问题 )
根据查询的顺序有序的向索引中添加字段。
3. 范围过滤
以字段的基数(Collection中字段的不同值的数量)从低到高的向索引中添加范围过滤字段。
还有一个规则:如果索引中的等值或者范围查询字段不能过滤出Collection中90%以上的文档,那么把它移除索引估计会更好一些。并且如果你在一个Collection上有多个索引,那么必须hint Mongos。
http://www.csdn.net/article/2012-11-09/2811690-optimizing-mongodb-compound
插入
SQL INSERT Statements | MongoDB insert() Statements |
---|
INSERT INTO users(user_id,
age,
status)VALUES ("bcd001",
45,
"A")
| db.users.insert(
{user_id:"bcd001", age:45, status:"A"} )
|
基本查询
SQL SELECT Statements | MongoDB 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()
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" )
如果distinct多个字段,使用使用聚合操作
|
SELECT * FROM usersLIMIT 1
| db.users.findOne()
or 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()
|
mongodb里的操作符
(1) $gt > (大于)
(2) $lt < (小于)
(3) $gte >= (大于等于)
(4) $lt <= (小于等于)
(5) $ne != (不等于)
(6) $in in (包含)
(7) $nin not in (不包含)
(8) $exists exist (字段是否存在)
(9) $inc 对一个数字字段field增加value(该字段必须有数字值)
(10) $set 就是相当于sql的set field = value
(11) $unset 就是删除字段
(12) $push 把value追加到field里面去,field一定要是数组类型才行,如果field不存在,会新增一个数组类型加进去
(13) $pushAll 同$push,只是一次可以追加多个值到一个数组字段内
(14) $addToSet 增加一个值到数组内,而且只有当这个值不在数组内才增加。
(15) $pop 删除最后一个值:{ $pop : { field : 1 } }删除第一个值:{ $pop : { field : -1 } }注意,只能删除一个值,也就是说只能用1或-1,而不能用2或-2来删除两条。
(16) $pull 从数组field内删除一个等于value值
(17) $pullAll 同$pull,可以一次删除数组内的多个值
(18) $ 操作符 自身的意思,代表按条件找出的数组里面某项自身数据。
复杂聚合查询group by,参考aggregate
更新记录
SQL Update Statements | MongoDB 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 | MongoDB remove() Statements |
---|
DELETE FROM usersWHERE status = "D"
| db.users.remove( { status: "D" } )
|
DELETE FROM users
| db.users.remove( )
|
参考
http://docs.mongodb.org/manual/reference/sql-comparison/