MongoDB基本语法
特别提示:本人博客部分有参考网络其他博客,但均是本人亲手编写过并验证通过。如发现博客有错误,请及时提出以免误导其他人,谢谢!欢迎转载,但记得标明文章出处:http://www.cnblogs.com/mao2080/
1、数据准备
1、MongoDB数据,工具为:Robo 3T
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/* 1 */ { "_id" : ObjectId("5b8a49d08e5dc1c0b571f775"), "userId" : "001", "uclass" : "A", "name" : "Jack", "age" : 10, "email" : "a@sina.com", "birthday" : ISODate("2018-08-31T03:46:13.885Z"), "dataStatus" : 1 } /* 2 */ { "_id" : ObjectId("5b8a4a758e5dc1c0b571f7b3"), "userId" : "002", "uclass" : "B", "name" : "Back", "age" : 11, "email" : "b@sina.com", "birthday" : ISODate("2018-07-31T03:46:13.885Z"), "dataStatus" : 1 } /* 3 */ { "_id" : ObjectId("5b8a4b158e5dc1c0b571f80a"), "userId" : "003", "uclass" : "B", "name" : "eack", "age" : 11, "email" : "b03@sina.com", "birthday" : ISODate("2003-07-31T03:46:13.885Z"), "dataStatus" : 1 } /* 4 */ { "_id" : ObjectId("5b8a4b238e5dc1c0b571f811"), "userId" : "004", "uclass" : "B", "name" : "tack", "age" : 11, "email" : "b04@sina.com", "birthday" : ISODate("2004-07-31T03:46:13.885Z"), "dataStatus" : 1 } /* 5 */ { "_id" : ObjectId("5b8a4b2e8e5dc1c0b571f818"), "userId" : "005", "uclass" : "B", "name" : "sack", "age" : 11, "email" : "b05@sina.com", "birthday" : ISODate("2005-07-31T03:46:13.885Z"), "dataStatus" : 1 } /* 6 */ { "_id" : ObjectId("5b8a4b3a8e5dc1c0b571f81f"), "userId" : "006", "uclass" : "B", "name" : "Back", "age" : 16, "email" : "b06@sina.com", "birthday" : ISODate("2006-07-31T03:46:13.885Z"), "dataStatus" : 1 } /* 7 */ { "_id" : ObjectId("5b8a4b438e5dc1c0b571f828"), "userId" : "007", "uclass" : "B", "name" : "pack", "age" : 19, "email" : "b07@sina.com", "birthday" : ISODate("2012-07-31T03:46:13.885Z"), "dataStatus" : 1 } /* 8 */ { "_id" : ObjectId("5b8a4b4f8e5dc1c0b571f82b"), "userId" : "008", "uclass" : "B", "name" : "uack", "age" : 11, "email" : "b08@sina.com", "birthday" : ISODate("2018-07-31T03:46:13.885Z"), "dataStatus" : 0 } /* 9 */ { "_id" : ObjectId("5b8a4b568e5dc1c0b571f832"), "userId" : "009", "uclass" : "A", "name" : "Back", "age" : 11, "email" : "b09@sina.com", "birthday" : ISODate("2009-07-31T03:46:13.885Z"), "dataStatus" : 1 } /* 10 */ { "_id" : ObjectId("5b8a4b608e5dc1c0b571f837"), "userId" : "010", "uclass" : "B", "name" : "Back", "age" : 11, "email" : "b@sina.com", "birthday" : ISODate("2018-07-31T03:46:13.885Z"), "dataStatus" : 1 } /* 11 */ { "_id" : ObjectId("5b8a4b668e5dc1c0b571f83c"), "userId" : "011", "uclass" : "B", "name" : "Back", "age" : 11, "email" : "b11@sina.com", "birthday" : ISODate("2011-07-31T03:46:13.885Z"), "dataStatus" : 0 } /* 12 */ { "_id" : ObjectId("5b8a4b6d8e5dc1c0b571f841"), "userId" : "012", "uclass" : "B", "name" : "Back", "age" : 11, "email" : "b12@sina.com", "birthday" : ISODate("2012-07-31T03:46:13.885Z"), "dataStatus" : 1 } /* 13 */ { "_id" : ObjectId("5b8a4b8e8e5dc1c0b571f850"), "userId" : "013", "uclass" : "B", "name" : "Back13", "age" : 20, "email" : "b13@sina.com", "birthday" : ISODate("2013-07-31T03:46:13.885Z"), "dataStatus" : 0 }
2、MySQL数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE `user` ( `userId` varchar(20) NOT NULL, `uclass` varchar(20) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `age` int(4) DEFAULT NULL, `email` varchar(50) DEFAULT NULL, `birthday` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `dataStatus` int(2) DEFAULT NULL, UNIQUE KEY `key` (`userId`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('001', 'A', 'Jack', '10', 'a@sina.com', '2018-08-31 11:46:13', '1'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('002', 'B', 'Back', '11', 'b@sina.com', '2018-07-31 11:46:13', '1'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('003', 'B', 'eack', '11', 'b03@sina.com', '2003-07-31 11:46:13', '1'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('004', 'B', 'tack', '11', 'b04@sina.com', '2004-07-31 11:46:13', '1'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('005', 'B', 'sack', '11', 'b05@sina.com', '2005-07-31 11:46:13', '1'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('006', 'B', 'Back', '16', 'b06@sina.com', '2006-07-31 11:46:13', '1'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('007', 'B', 'pack', '19', 'b07@sina.com', '2012-07-31 11:46:13', '1'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('008', 'B', 'uack', '11', 'b08@sina.com', '2018-07-31 11:46:13', '0'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('009', 'A', 'Back', '11', 'b09@sina.com', '2009-07-31 11:46:13', '1'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('010', 'B', 'Back', '11', 'b@sina.com', '2018-07-31 11:46:13', '1'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('011', 'B', 'Back', '11', 'b11@sina.com', '2011-07-31 11:46:13', '0'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('012', 'B', 'Back', '11', 'b12@sina.com', '2012-07-31 11:46:13', '1'); INSERT INTO `sz-temp`.`user` (`userId`, `uclass`, `name`, `age`, `email`, `birthday`, `dataStatus`) VALUES ('013', 'B', 'Back13', '20', 'b13@sina.com', '2013-07-31 11:46:13', '0');
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 | db.getCollection('user').find({"name":/Ba/}); | |
MySQL | select * from user where name like '%Ba%'; | ||
查询条件:distinct | MongoDB | db.getCollection('user').distinct("name"); | |
MySQL | select distinct uclass from user u; | ||
查询条件:$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、参考网站
个性签名:1.01的365次方=37.78343433289 >>>1
0.99的365次方= 0.02551796445229 <<<1
每天进步一点点的目标,贵在坚持…