SQL 和Mongo 对比图表

参看官方说明:

   http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart

 

MySQL executable

Oracle executable

Mongo executable

mysqld

oracle

mongod

mysql

sqlplus

mongo

MySQL term

Mongo term/concept

database

database

table

collection

index

index

row

BSON   document

column

BSON field

join

embedding and linking

primary key

_id field

group by

aggregation

    

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 the  mongo shell .

 

SQL Statement

Mongo Statement

CREATE TABLE USERS (a  Number , b  Number )

implicit; can also be done  explicitly   with

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 order_id FROM orders o, order_line_items li WHERE li.order_id=o.order_id AND li.sku=12345

db.orders.find({ "items.sku" :12345},{_id:1})

SELECT customer.name FROM customers,orders WHERE orders.id= "q179"   AND orders.custid=customer.id

var   o = db.orders.findOne({_id: "q179" });

var   name = db.customers.findOne({_id:o.custid})

 

 

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'});

More examples, specifically aggregation examples, here

 

 

 

MongoDB资料汇总专题

 

1.MongoDB是什么

2.MongoDB内部实现

3.MongoDB实战经验分享

4.MongoDB与开源项目

5.MongoDB客户端与工具集

6.MongoDB应用 教程

7.MongoDB性能与优化

8.MongoDB部署 、运维与监控

9.MongoDB新闻

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
收集整理的mongodb学习资料,与大家分享。 包括: MongoDB 概念理解.pdf MongoDB_使用手册-中文版.pdf MongoDB使用手册.pdf Mongodb文档 与 php操作.pdf MongoDB应用.pdf =========================================== 常用命令: mongod.exe --dbpath "d:\mongodb\data\db" --directoryperdb --logpath "d:\mongodb\data\logs" --logappend mongo.exe >show dbs >use memo //使用 数据库 memo >show collections //列出当前数据库的collections >db //显示当前数据库 >show users //列出用户 help //更多语法 创建数据库与数据集合: >db >show dbs >use test2 >db.createCollection("t_test"); >use test2 >db >t={name:"hb",addr:"shanghai"}; >db.t_test.find(); >db.t_test.save(t); >db.t_test.find(); >db.t_test.insert({name:"test",addr:"beijing"}); >db.t_test.save({name:"test2",addr:"tianjin",phone:"025-0001"}); 数据查询: db.t_test.find() //select * from t_test db.t_test.find().limit(2) //select * from t_test limit 2 db.t_test.find().sort({x:1}) //select * from t_test order by x asc db.t_test.find().sort({x:1}).skip(2).limit(3) //select * from t_test order by x asc limit 2,3 db.t_test.find({x:10}) //select * from t_test where x = 10 db.t_test.find({x:{$lt:10}}) //select * from t_test where x<10 db.t_test.find({x:{$in:["01","03","10"]}}); // select * from t_test where x in ("01","03","10") db.t_test.find({},{y:true}) //select y from t_test db.t_test.find().count(); db.t_test.find({"address.city":"gz"}) //搜索嵌套文档address中city值为gz的记录 db.t_test.find({likes:"math"}) //搜索数组 db.t_test.ensureIndex({"address.city":1}) //在嵌套文档的字段上建立索引 更新数据: db.t_text.update({},{}) //第一个参数是查询对象,第二个是替代对象,要使用$set db.t_test.update({name:"test"},{$set:{addr:"shenZheng"}}); db.t_test.find(); 数据删除: db.t_test.remove({name:"test2"}); //删除数据 db.t_test.find(); show collections db.t_test.drop(); //删除数据集合(表) use test2 db.dropDatabase(); 索引: db.t_test.ensureIndex({productid:1}) //在productid上建立普通索引 db.t_test.ensureIndex({productid:1,plate:1}) //多字段索引 db.t_test.ensureIndex({productid,1},{unique:true}) //唯一索引 备份与恢复: mongodump.ext --help mongodump.ext -d test -o ../data/backup/test //备份数据库test中所有的数据集合 mongorestore.exe --help mongorestore.exe -d
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值