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
}
复制代码
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');
复制代码
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、参考网站
http://www.runoob.com/mongodb/mongodb-aggregate.html
https://www.jianshu.com/p/5e870132ca7c