乐趣区

mongodb和mysql单表查询

1. 选择表中的若干字段
db.teacher.find({},{_id:0,name:1})
select name from teacher;
2. 查询满足条件的元组
(1) 比较大小
db.teacher.find({salary : {$gt : 5000}})
select * from teacher where salary > 5000;

db.teacher.find({salary : {$gte : 5000}})
select * from teacher where salary >=5000;

db.teacher.find({salary : {$lt : 5000}})
select * from teacher where salary < 5000;

db.teacher.find({salary : {$lte : 5000}})
select * from teacher where salary <= 5000;

db.teacher.find({salary : {$ne : 5000}})
select * from teacher where salary != 5000;
(2) 多重条件查询
db.teacher.find({salary : {$lt :10000, $gt : 5000}})
select * from teacher where salary>5000 AND  salary<10000;

db.teacher.find({salary: {$gt:5000}, $or: [{birthday: "1997-01-01"},{name: "大明"}]})
select * from teacher where salary>5000 AND (birthday = "1997-01-01" OR name = "大明");
(3) 确定集合
db.teacher.find({birthday:{$in:["1997-01-01","2006-01-01"]}})
select * from teacher where birthday in ("1997-01-01","2006-01-01");
 
db.teacher.find({birthday:{$nin:["2007-01-01","2006-01-01"]}})
select * from teacher where birthday not in ("2007-01-01","2006-01-01");
(4) 字符匹配
db.teacher.find({name:/ 大 /})
select * from teacher where name like "% 大 %";

db.teacher.find({name:/^ 小 /})
select * from teacher where name like "大 %";

db.teacher.find({name:{$not:/^ 明 /}})
select * from teacher where name not like "明 %";
(5) 涉及空值的查询
db.teacher.find({salary:null})
select * from teacher where salary=null;
退出移动版