MongoDB基本語法特別提示：本人部落格部分有參考網路其他部落格，但均是本人親手編寫過並驗證通過。如發現部落格有錯誤，請及時提出以免誤導其他人，謝謝！歡迎轉載，但記得標明文章出處：http://www.cnblogs.com/mao2080/
1、資料準備
1、MongoDB資料，工具為：Robo 3T
/* 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}
mongo-data
2、MySQL資料
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');
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 | 無 |
|
