mysql与mongoDB语法对比

迈不过友情╰ 2022-08-19 08:27 165阅读 0赞

随着大数据的到来，nosql的价值正被越来越多的人们认可，而mongodb就是近年来最流行的一款，之前分享过《[三分钟了解mongodb][mongodb]》，下面将使用程序员+开发者的角度了解它的语法与mysql的差异。

以下归纳了差别：

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> MySQL </td> 
 <td style="vertical-align:top;"> Mongo </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> CREATE TABLE USERS (a Number, b Number) </td> 
 <td style="vertical-align:top;"> <pre><code>db.createCollection("mycoll", {capped:true, size:100000})</code>"mycoll", {capped:true, size:100000})</pre> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> INSERT INTO USERS VALUES(1,1) </td> 
 <td style="vertical-align:top;"> db.users.insert({a:1,b:1}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT a,b FROM users </td> 
 <td style="vertical-align:top;"> db.users.find({}, {a:1,b:1}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users </td> 
 <td style="vertical-align:top;"> db.users.find() </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users WHERE age=33 </td> 
 <td style="vertical-align:top;"> db.users.find({age:33}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT a,b FROM users WHERE age=33 </td> 
 <td style="vertical-align:top;"> db.users.find({age:33}, {a:1,b:1}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users WHERE age=33 ORDER BY name </td> 
 <td style="vertical-align:top;"> db.users.find({age:33}).sort({name:1}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users WHERE age&gt;33 </td> 
 <td style="vertical-align:top;"> db.users.find({'age':{$gt:33}})}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users WHERE age&lt;33 </td> 
 <td style="vertical-align:top;"> db.users.find({'age':{$lt:33}})}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users WHERE name LIKE "%Joe%" </td> 
 <td style="vertical-align:top;"> db.users.find({name:/Joe/}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users WHERE name LIKE "Joe%" </td> 
 <td style="vertical-align:top;"> db.users.find({name:/^Joe/}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users WHERE age&gt;33 AND age&lt;=40 </td> 
 <td style="vertical-align:top;"> db.users.find({'age':{$gt:33,$lte:40}})}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users ORDER BY name DESC </td> 
 <td style="vertical-align:top;"> db.users.find().sort({name:-1}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> CREATE INDEX myindexname ON users(name) </td> 
 <td style="vertical-align:top;"> db.users.ensureIndex({name:1}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> CREATE INDEX myindexname ON users(name,ts DESC) </td> 
 <td style="vertical-align:top;"> db.users.ensureIndex({name:1,ts:-1}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users WHERE a=1 and b='q' </td> 
 <td style="vertical-align:top;"> db.users.find({a:1,b:'q'}) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users LIMIT 10 SKIP 20 </td> 
 <td style="vertical-align:top;"> db.users.find().limit(10).skip(20) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users WHERE a=1 or b=2 </td> 
 <td style="vertical-align:top;"> db.users.find( { $or : [ { a : 1 } , { b : 2 } ] } ) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT * FROM users LIMIT 1 </td> 
 <td style="vertical-align:top;"> db.users.findOne() </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> EXPLAIN SELECT * FROM users WHERE z=3 </td> 
 <td style="vertical-align:top;"> db.users.find({z:3}).explain() </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT DISTINCT last_name FROM users </td> 
 <td style="vertical-align:top;"> db.users.distinct('last_name') </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT COUNT(*)FROM users </td> 
 <td style="vertical-align:top;"> db.users.count() </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT COUNT(*)FROM users where AGE &gt; 30 </td> 
 <td style="vertical-align:top;"> db.users.find({age: {'$gt': 30}}).count() </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SELECT COUNT(AGE) from users </td> 
 <td style="vertical-align:top;"> db.users.find({age: {'$exists': true}}).count() </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> UPDATE users SET a=1 WHERE b='q' </td> 
 <td style="vertical-align:top;"> db.users.update({b:'q'}, {$set:{a:1}}, false, true) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> UPDATE users SET a=a+2 WHERE b='q' </td> 
 <td style="vertical-align:top;"> db.users.update({b:'q'}, {$inc:{a:2}}, false, true) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> DELETE FROM users WHERE z="abc" </td> 
 <td style="vertical-align:top;"> db.users.remove({z:'abc'});&nbsp; </td> 
 </tr> 
 </tbody> 
</table>

如果你想了解的更多，请参考我的另一篇《[入门手册][Link 1]》![mongodb][]

[mongodb]: /images/20220731/b4b5bafbf9a5404fa35375216471410f.png
[Link 1]: https://blog.csdn.net/alex_xfboy/article/details/82910320