MongoDB和mysql常用查询语句对比

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;

• Java中gt，lt。gte，lte代表什么？？戳☞我
• Java中gt，lt。gte，lte代表什么？？戳☞我

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;