MongoDB于SQL的对应

朱雀 2022-07-14 12:26 136阅读 0赞

首先介绍一些关系数据库中常用的概念对比MongoDB中与之对应的概念。

Oracle MongoDB

DataBase DataBase

Table(表) Collection(集合)

index(索引) index(索引)

row(一行记录) BSON(类似JSON格式) [http://bsonspec.org/][http_bsonspec.org]

column(列,字段) BSON中的字段

join(连接) embedding and linking(嵌入和连接)

primary key(主键) \_id field(ID标识符)

group by(分组) aggregation(聚合)

其实在学习MongoDB过程中我们就是要忘记模式,记住”键值对”就可以啦。

MongoDB的查询是通过JSON(BSON)对象，来表示的。接下来我们将通过对比展示SQL和MonggoDB的查询语法

SQL语句 MongoDB语句

Create table users(a int,b int)建立一张表                     我们无需显式创建Collection,前面讲了在我们保存第一条文档的时候MongoDB会自动创建，当然我们也可以显示的建立：
                                                                                Collection: db.createCollection("users", { capped:true, size:100000}) // capped:是否覆盖 size:大小以字节为单位

Alter table users……… 模式自由

<table style="color:rgb(0,0,0); font-family:Helvetica,Tahoma,Arial,sans-serif; border:1px solid rgb(192,192,192); border-collapse:collapse; font-size:14.4444px; line-height:25.5556px"> 
 <tbody> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> inert into users value(3,5) </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.insert({a:3,b:5}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select a,b from users </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({}, {a:1,b:1}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find() </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users where age=33 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({age:33}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select a,b from users where age=33 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({age:33}, {a:1,b:1}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users where age=33 order by name </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({age:33}).sort({name:1}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users where age&gt;33 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({age:{$gt:33}}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users where age!=33 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({age:{$ne:33}}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users where name like "%Joe%" </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({name:/Joe/}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users where name LIKE "Joe%" </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({name:/^Joe/}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users where age&gt;33 and age&lt;=40 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({'age':{$gt:33,$lte:40}}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users order by name desc </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find().sort({name:-1}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users where a=1 and b='q' </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({a:1,b:'q'}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users limit 10 skip 20 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find().limit(10).skip(20) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users where a=1 or b=2 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find( { $or : [ { a : 1 } , { b : 2 } ] } ) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select * from users limit 1 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.findOne() </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select order_id from orders o, order_line_items li where li.order_id=o.order_id and li.sku=12345 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.orders.find({"items.sku":12345},{_id:1}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select customer.name from customers,orders where orders.id="q179" and orders.custid=customer.id </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> var o = db.orders.findOne({_id:"q179"}); var name = db.customers.findOne({_id:o.custid}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select distinct last_name from users </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.distinct('last_name') </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select count(*y) from users </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.count() </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select count(*y) from users where age &gt; 30 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({age: {'$gt': 30}}).count() </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> select count(age) from users </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({age: {'$exists': true}}).count() </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> create index myindexname on users(name) </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.ensureIndex({name:1}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> create index myindexname ON users(name,ts desc) </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.ensureIndex({name:1,ts:-1}) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> explain select * from users where z=3 </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.find({z:3}).explain() </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> update users set a=1 where b='q' </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.update({b:'q'}, {$set:{a:1}}, false, true) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> update users set a=a+2 where b='q' </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.update({b:'q'}, {$inc:{a:2}}, false, true) </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> delete from users where z="abc" </td> 
 <td style="font-size:14px; border:1px solid rgb(192,192,192); border-collapse:collapse; padding:3px"> db.users.remove({z:'abc'}); </td> 
 </tr> 
 </tbody> 
</table>

更多请见: [http://api.mongodb.org/wiki/current/SQL%20to%20Aggregation%20Framework%20Mapping%20Chart.html][http_api.mongodb.org_wiki_current_SQL_20to_20Aggregation_20Framework_20Mapping_20Chart.html]

这些都是常用的SQL语句，当然掌握这些就足够后面的开发啦。

SQL Statement Mongo Query Language Statement

<table style="color:rgb(0,0,0); font-size:14px; border:0px; font-family:verdana,arial,helvetica,sans-serif"> 
 <tbody> 
 <tr></tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">CREATE TABLE USERS (a Number, b Number)</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">Implicit or use&nbsp;<a href="http://www.php.net/manual/en/mongodb.createcollection.php" style="color:rgb(0,0,153); background-color:transparent" rel="nofollow">MongoDB::createCollection()</a>.</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">INSERT INTO USERS VALUES(1,1)</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;insert(array("a" =&gt; 1, "b" =&gt; 1));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT a,b FROM users</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array(), array("a" =&gt; 1, "b" =&gt; 1));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users WHERE age=33</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("age" =&gt; 33));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT a,b FROM users WHERE age=33</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("age" =&gt; 33), array("a" =&gt; 1, "b" =&gt; 1));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT a,b FROM users WHERE age=33 ORDER BY name</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("age" =&gt; 33), array("a" =&gt; 1, "b" =&gt; 1))-&gt;sort(array("name" =&gt; 1));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users WHERE age&gt;33</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("age" =&gt; array('$gt' =&gt; 33)));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users WHERE age&lt;33</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("age" =&gt; array('$lt' =&gt; 33)));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users WHERE name LIKE "%Joe%"</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("name" =&gt; new MongoRegex("/Joe/")));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users WHERE name LIKE "Joe%"</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("name" =&gt; new MongoRegex("/^Joe/")));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users WHERE age&gt;33 AND age&lt;=40</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("age" =&gt; array('$gt' =&gt; 33, '$lte' =&gt; 40)));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users ORDER BY name DESC</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find()-&gt;sort(array("name" =&gt; -1));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">CREATE INDEX myindexname ON users(name)</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;ensureIndex(array("name" =&gt; 1));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">CREATE INDEX myindexname ON users(name,ts DESC)</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;ensureIndex(array("name" =&gt; 1, "ts" =&gt; -1));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users WHERE a=1 and b='q'</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("a" =&gt; 1, "b" =&gt; "q"));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users LIMIT 10 SKIP 20</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find()-&gt;limit(10)-&gt;skip(20);</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users WHERE a=1 or b=2</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array('$or' =&gt; array(array("a" =&gt; 1), array("b" =&gt; 2))));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT * FROM users LIMIT 1</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find()-&gt;limit(1);</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">EXPLAIN SELECT * FROM users WHERE z=3</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("z" =&gt; 3))-&gt;explain()</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT DISTINCT last_name FROM users</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;command(array("distinct" =&gt; "users", "key" =&gt; "last_name"));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT COUNT(*y) FROM users</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;count();</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT COUNT(*y) FROM users where AGE &gt; 30</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("age" =&gt; array('$gt' =&gt; 30)))-&gt;count();</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">SELECT COUNT(AGE) from users</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;find(array("age" =&gt; array('$exists' =&gt; true)))-&gt;count();</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">UPDATE users SET a=1 WHERE b='q'</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;update(array("b" =&gt; "q"), array('$set' =&gt; array("a" =&gt; 1)));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">UPDATE users SET a=a+2 WHERE b='q'</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;update(array("b" =&gt; "q"), array('$inc' =&gt; array("a" =&gt; 2)));</td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">DELETE FROM users WHERE z="abc"</td> 
 <td style="font-size:1em; padding:5px; background-color:rgb(240,240,240)">$db-&gt;users-&gt;remove(array("z" =&gt; "abc"));</td> 
 </tr> 
 </tbody> 
</table>

[http_bsonspec.org]: http://bsonspec.org/
[http_api.mongodb.org_wiki_current_SQL_20to_20Aggregation_20Framework_20Mapping_20Chart.html]: http://api.mongodb.org/wiki/current/SQL%20to%20Aggregation%20Framework%20Mapping%20Chart.html