1、数据准备
1、MongoDB数据，工具为：Robo 3T
2、MySQL数据
2、新增语句
| 方法 | 说明 | 语法 | 备注 |
| 新增 | MongoDB |
db.getCollection(‘user‘).insert({"userId" : "014","uclass" : "B","name" : "Back","age" : 11,"email" : "b14@sina.com","birthday" : ISODate("2018-07-31T03:46:13.885Z"),"dataStatus" : 1}); |
|
| MySQL | INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES (‘014‘, ‘B‘, ‘Back13‘, ‘20‘, ‘b14@sina.com‘, ‘2013-07-31 11:46:13‘, ‘0‘); |
3、删除语句
| 方法 | 说明 | 语法 | 备注 |
| 删除 | MongoDB | db.getCollection(‘user‘).remove({"userId":"014"}); | |
| MySQL | delete from user where userId = ‘014‘; |
4、修改语句
| 方法 | 说明 | 语法 | 备注 |
| 修改 | MongoDB | db.getCollection(‘user‘).update({"userId":"013"}, {$set:{"email":"b13@sina.com", "age":20}}); | |
| MySQL | update user set email = ‘b13@sina.com‘, age = 20 where userId = ‘013‘; |
5、查询语句
| 查询方法 | 说明 | 语法 | 备注 |
| 查询所有 | MongoDB | db.getCollection(‘user‘).find({}); | |
| MySQL | select * from user; | ||
| 查询条件：= | MongoDB | db.getCollection(‘user‘).find({"uclass":"A"}); | |
| MySQL | select * from user where uclass = ‘A‘; | ||
| 查询条件：like | MongoDB | select * from user where name like ‘%Ba%‘; | |
| MySQL | db.getCollection(‘user‘).find({"name":/Ba/}); | ||
| 查询条件：distinct | MongoDB | select distinct uclass from user u; | |
| MySQL | db.getCollection(‘user‘).distinct("name"); | ||
| 查询条件：$gt | MongoDB | db.getCollection(‘user‘).find({"age":{$gt:16}}); | greater than > |
| MySQL | select * from user where age >16; | ||
| 查询条件：$gte | MongoDB | db.getCollection(‘user‘).find({"age":{$gte:16}}); | gt equal >= |
| MySQL | select * from user where age >= 16; | ||
| 查询条件：$lt | MongoDB | db.getCollection(‘user‘).find({"age":{$lt:16}}); | less than < |
| MySQL | select * from user where age < 16; | ||
| 查询条件：$lte | MongoDB | db.getCollection(‘user‘).find({"age":{$lte:16}}); | lt equal <= |
| MySQL | select * from user where age <= 16; | ||
| 查询条件：$ne | MongoDB | db.getCollection(‘user‘).find({"age":{$ne:16}}); | not equal != |
| MySQL | select * from user where age != 16; | ||
| 查询条件：$eq | MongoDB | db.getCollection(‘user‘).find({"age":{$eq:16}});等效于：db.getCollection(‘user‘).find({"age":16}); | equal = |
| MySQL | select * from user where age = 16; | ||
| 查询条件：in | MongoDB | db.getCollection(‘user‘).find({"uclass":{$in:[‘A‘, ‘B‘]}}); | |
| MySQL | select * from user where uclass in (‘A‘, ‘B‘); | ||
| 查询条件：and | MongoDB | db.getCollection(‘user‘).find({"uclass":"B", "age":{$gt:16}}); | |
| MySQL | select * from user where uclass = ‘B‘ and age > 16; | ||
| 查询条件：or | MongoDB | db.getCollection(‘user‘).find({$or:[{"uclass":"A"},{"class":"B"}]}); | |
| MySQL | select * from user where uclass = ‘A‘ or uclass = ‘B‘; | ||
| 查询条件：时间 | MongoDB | db.getCollection(‘user‘).find({"birthday":{$gt: new Date("2008-08-14T06:24:40.110Z"), $lt: new Date("2015-08-14T06:14:40.089Z")}}); | |
| MySQL | select * from user where birthday > ‘2008-08-14 06:24:40‘ and birthday < ‘2015-08-14 06:14:40‘; | ||
| 查询条数：count | MongoDB | db.getCollection(‘user‘).find({"uclass":"A"}).count(); | |
| MySQL | select count(1) from user where uclass = ‘A‘; | ||
| 查询条件：sort升序 | MongoDB | db.getCollection(‘user‘).find({}).sort({"age":1}); | |
| MySQL | select * from user order by age asc; | ||
| 查询条件：sort降序 | MongoDB | db.getCollection(‘user‘).find({}).sort({"age":-1}); | |
| MySQL | select * from user order by age desc; | ||
| 聚合查询：count单列 | MongoDB | db.getCollection(‘user‘).aggregate([{$group:{_id:"$uclass",num:{$sum:1}}}]); | |
| MySQL | select uclass, count(1) as num from user group by uclass; | ||
| 聚合查询：count多列 | MongoDB | db.getCollection(‘user‘).aggregate([{$group:{_id:{uclass:"$uclass", age:"$age"},num:{$sum:1}}}]); | |
| MySQL | select uclass, age, count(1) as num from user group by uclass, age; | ||
| 分页查询：limit n | MongoDB | db.getCollection(‘user‘).find({}).limit(5); | 查询前n条 |
| MySQL | select * from user limit 5; | ||
| 分页查询：limit m,n | MongoDB | db.getCollection(‘user‘).find({}).limit(5).skip(5); | 查询n条，从第m条开始 |
| MySQL | select * from user limit 5,5; | ||
| 查询指定字段 | MongoDB | db.getCollection(‘user‘).find({}, {userId:1, name:1}); | 第一个{}为查询条件 |
| MySQL | select userId, name from user; | ||
| 排查指定字段 | MongoDB | db.getCollection(‘user‘).find({}, {dataStatus:0, _id:0}); | 第一个{}为查询条件 |
| MySQL | 无 |
6、参考网站
本文摘自 https://www.cnblogs.com/mao2080/p/9570909.html
http://www.runoob.com/mongodb/mongodb-aggregate.html