MongoDB基本语法和MySQL语法对比

1、数据准备

　　1、MongoDB数据，工具为：Robo 3T

　　

 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');

正在上传…重新上传取消​

MongoDB基本语法 - mao2080 - 博客园特别提示：本人博客部分有参考网络其他博客，但均是本人亲手编写过并验证通过。如发现博客有错误，请及时提出以免误导其他人，谢谢！欢迎转载，但记得标明文章出处：http://www.cnblogs.com/https://www.cnblogs.com/mao2080/p/9570909.html

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、参考网站

　　MongoDB 聚合 | 菜鸟教程http://www.runoob.com/mongodb/mongodb-aggregate.html

　　MongoDB基础语法 - 简书https://www.jianshu.com/p/5e870132ca7c