MongoDB数据库常用28条查询语句总结

目录

• 初始MongoDB
• MongoDB特点
• 1、查询所有记录
• 2、查询去掉后的当前聚集集合中的某列的重复数据
• 3、查询 age = 22 的记录
• 4、查询 age > 22 的记录
• 5、查询 age < 22 的记录
• 6、查询 age >= 25 的记录
• 7、查询 age <= 25 的记录
• 8、查询 age >= 23 并且 age <= 25 注意书写格式
• 9、查询 age != 25 的记录
• 10、查询 name 中包含 mongo 的数据 模糊查询用于搜索
• 11、查询 name 中以 mongo 开头的
• 12、查询 name 中以 mongo 结尾的
• 13、查询指定列 name、age 数据
• 14、查询指定列 name、age 数据, age > 25
• 15、按照年龄排序 1 升序 -1 降序
• 17、查询前 5 条数据
• 18、查询 10 条以后的数据
• 19、查询在 6-10条 之间的数据
• 20、and 查询 name = zhangsan, age = 22 的数据
• 21、or 查询
• 22、in 查询
• 23、查询某个结果集的记录条数
• 24、查询某个时间段的数据（时间为日期类型，非字符串类型）
• 25、对表中一字段进行统计求和
• 26、对表中一字段进行统计求平均值
• 27、对表中指定条件记录中的某字段求和
• 28、根据A表，匹配B表所有满足条件的集合，如根据用户表userInfo表中的userId字段找出userAdress表中所有地址的集合，其中userId也为userAdress中的字段。
• 总结

db.userInfo.find();

select* from userInfo;

db.userInfo.distinct("name");

select distict name from userInfo;

db.userInfo.find({"age": 22});

select * from userInfo where age = 22;

db.userInfo.find({age: {$gt: 22}});

select * from userInfo where age > 22;

db.userInfo.find({age: {$lt: 22}});

select * from userInfo where age < 22;

db.userInfo.find({age: {$gte: 25}});

select * from userInfo where age >= 25;

db.userInfo.find({age: {$lte: 25}});

select * from userInfo where age <= 25;

db.userInfo.find({age: {$gte: 23, $lte: 25}});

select * from userInfo where age>=23 and age <= 25;

db.userInfo.find({age: {$ne: 25}});

select * from userInfo where age != 25;

db.userInfo.find({name: /mongo/});

select * from userInfo where name like '%mongo%';

db.userInfo.find({name: /^mongo/});

select * from userInfo where name like 'mongo%';

db.userInfo.find({name: /mongo$/});

select * from userInfo where name like ‘%mongo';

db.userInfo.find({}, {name: 1, age: 1});

select name, age from userInfo;

db.userInfo.find({age: {$gt: 25}}, {name: 1, age: 1});

select name, age from userInfo where age > 25;

db.userInfo.find().sort({age: 1});

select * from userInfo order by age asc;

db.userInfo.find().sort({age: -1});

select * from userInfo order by age desc;

db.userInfo.find().limit(5);

select * from userInfo limit 5;

db.userInfo.find().skip(10);

db.userInfo.find().limit(10).skip(5);

select * from userInfo limit 5,5;

db.userInfo.find({name: 'zhangsan', age: 22});

select * from userInfo where name = 'zhangsan' and age = 22;

db.userInfo.find({$or: [{age: 22}, {age: 25}]});

select * from userInfo where age = 22 or age = 25;

db.userInfo.find({age :{$in:[22,25]}});

select * from userInfo where age in (22,25);

db.userInfo.find({age: {$gte: 25}}).count();

select count(*) from userInfo where age >= 20;

db.userInfo.find({createTime:{$gt:ISODate("2020-11-09T00:00:00Z")}});

select * from userInfo where createTime> '2020-11-09 00:00:00'; 

db.userInfo.aggregate({$group:{_id:null,score:{$sum:"$score"}}})

SELECT SUM(score) from userInfo;

db.userInfo.aggregate({$group:{_id:null,score:{$avg:"$score"}}})

SELECT AVG(score) from userInfo;

db.userInfo.aggregate({$match:{createTime:{$gte:ISODate("2020-11-10T00:00:00Z"),$lt:ISODate("2020-11-11T00:00:00Z")}}},{$group:{_id:null,score:{$sum:"$score"}}})

SELECT SUM(score) from userInfo where createTime >= '2020-11-10 00:00:00' and createTime < '2020-11-11 00:00:00';

db.userInfo.insert([
   { "_id" : 1, "userId" : "xxxx", "username" : "ruink", "website" : "www.51ste.com" },
   { "_id" : 2, "userId" : "yyyy", "username" : "foosingy", "website" : "www.scgossip.com" }
])

db.userAdress.insert([
   { "_id" : 1, "userId" : "xxxx", address: "测试地址1"},
   { "_id" : 2, "userId" : "yyyy", address: "测试地址2"},
   { "_id" : 3, "userId" : "xxxx", address: "测试地址3"},
])

db.userInfo.aggregate([
   {
     $lookup:
       {
         from: "userAdress",
         localField: "userId",
         foreignField: "userId",
         as: "address_detail"
       }
  },
  { $match : {"userId" :"xxxx"} }
])

[
  {
    _id: 1,
    userId: 'xxxx',
    username: 'ruink',
    website:'www.51ste.com',
    address_docs: [
      {
        _id: 1,
        userId: 'xxxx',
        address: '测试地址1'
      },
      {
        _id: 3,
        userId: 'xxxx',
        address: '测试地址3'
      }
    ]
  }
]