MongoDB和mysql常用查询语句对比
1、查询所有记录
db.member_audit.find();
select* from member_audit;
2、查询 age = 2 的记录
db.member_audit.find({"age": 2});
select * from member_audit where age = 2;
3、查询 age = 2 ,name = guo 的数据
db.member_audit.find({name: 'guo', age: 2});
select * from member_audit where name = ‘guo’ and age = ‘2’;
4、查询 age > 2 的记录
db.member_audit.find({age: {$gt: 2}});
select * from member_audit where age >2;
5、查询 age < 2 的记录
db.member_audit.find({age: {$lt: 2}});
select * from member_audit where age <2;
6、查询 age >= 2 的记录
db.member_audit.find({age: {$gte: 2}});
select * from member_audit where age >= 2;
7、查询 age <= 2 的记录
db.member_audit.find({age: {$lte: 2}});
select * from member_audit where age <= 2;
8、查询 age >= 2 并且 age <= 6
db.member_audit.find({age: {$gte: 2, $lte: 6}});
9、查询 memberNo 中包含 2 的数据(模糊查询)
db.member_audit.find({memberNo: /2/}); //模糊查询
db.member_audit.find({memberNo: "2"}); //精准查询
select * from member_audit where name like ‘%2%’;
10、查询 memberNo 中以 E 开头的(模糊查询)
db.member_audit.find({memberNo: /^E/});
select * from member_audit where memberNo like ‘E%’;
11、查询指定列 id、age 数据
db.member_audit.find({_id: 1, age: 1}); //查询id=1并age=1的某行数据
db.member_audit.find({}, {_id: 1, age: 1}); //查询指定的某列数据
select id, age from member_audit;
12、查询指定列 id、age 数据, 并且id > 2
db.member_audit.find({_id: {$gt: 2}}, {id: 1, age: 1});//前面括号是条件,后面括号是指定的列
select id, age from member_audit where id >2;
13、查询 5 条以后的数据
db.member_audit.find().skip(5);
select * from member_audit where id not in(
selecttop 10 * from member_audit
);
14、查询id=2或者mobile=1的
db.member_audit.find({$or: [{_id: 2}, {mobile: "1"}]});
select * from member_audit where id = 2 or mobile = 1;
15、findOne 查询第一条数据
db.member_audit.findOne();
selecttop 1 * from member_audit;
16、查询auditStatus=1的记录条数
db.member_audit.find({auditStatus: 1}).count();
select count(*) from member_audit where age = 20;
17、查询auditStatus>=1的记录条数(大于小于同理上面)
db.member_audit.find({auditStatus: {$gte: 1}}).count();
select count(*) from member_audit where age >= 20;