MongoDB常用语句

使用

展示数据库

show dbs 或 show databases 

查看当前在使用的数据库

db

展示数据库下所有表

show collections 或 show tables;

终端内容过多，用该指令清屏

cls

创建和删除

如果数据库【school】不存在，则创建它，否则就切换到【school】数据库。
因为是创建了一个空的，所以它只存放在内存中，没到磁盘里。用show dbs无法查看，但db可以

use school 

删除当前使用的数据库

db.dropDatabase()  <=>  drop database school;

创建名为student的集合（用创建语句，插入内容为空也可以创建）

db.createCollection("student")
db.student.insertOne({})

删除student集合

db.student.drop()  <=>  drop table student;

查询

db.student.find().pretty()  美化输出结果

查询所有记录

db.student.find()  <=>  select * from student;

查询指定列 name、age 数据
当然 name 也可以用 true 或 false,当用 ture 的情况下和 name:1 效果一样，如果用 false 就是排除 name，显示 name 以外的列信息。

db.student.find({}, {name: 1, age: 1});  <=>  select name, age from student;

查询去重后的一列

db.student.distinct("name")  <=>  select distict name from student;

条件查询

db.student.find({"age": 22})  		<=>  select * from student where age = 22;
db.student.find({age: {$gt: 22}})   <=>  select * from student where age > 22;
db.student.find({age: {$lt: 22}})   <=>  select * from student where age < 22;
db.student.find({age: {$gte: 25}})  <=>  select * from student where age >= 25;
db.student.find({age: {$lte: 25}})  <=>  select * from student where age <= 25;
db.student.find({age: {$ne: 25}})   <=>  select * from student where age != 25;

db.student.find({age: {$gte: 23, $lte: 25}});    <=>  select * from student where age>=23 and age <= 25;
db.student.find({name: 'zhangsan', age: 22});  	 <=>  select * from student where name = 'zhangsan' and age = 22;
db.student.find({$or: [{age: 22}, {age: 25}]});  <=>  select * from student where age = 22 or age = 25;
db.student.find({age :{$in:[22,25]}});        	 <=>  select * from student where age in (22,25);

db.student.find({createtime:{$gt:isodate("2020-11-09t00:00:00z")}});  <=>  select * from student where createtime> '2020-11-09 00:00:00'; 

db.student.aggregate({$match:{createtime:{$gte:isodate("2020-11-10t00:00:00z"),$lt:isodate("2020-11-11t00:00:00z")}}});  <=>  select * from student where createtime >= '2020-11-10 00:00:00' and createtime < '2020-11-11 00:00:00';

查询指定列

db.student.find({age: {$gt: 25}}, {name: 1, age: 1});  <=>  select name, age from student where age > 25;

模糊查询

db.student.find({name: /zhang/})   <=>  select * from student where name like '%zhang%';
db.student.find({name: /^zhang/})  <=>  select * from student where name like 'zhang%';
db.student.find({name: /zhang$/})  <=>  select * from student where name like '%zhang';

分页

# 查询前 5 条数据
db.student.find().limit(5);  <=>  select * from student limit 5;
# 查询在 6-10条 之间的数据
db.student.find().limit(10).skip(5);  <=>  select * from student limit 5,5;

查询 10 条以后的数据

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

排序

# 升序
db.student.find().sort({age: 1});  <=>  select * from student order by age asc;
# 降序
db.student.find().sort({age: -1});  <=>  select * from student order by age desc;

聚合

db.student.find({age: {$gte: 25}}).count();  <=>  select count(*) from student where age >= 20;

db.student.aggregate({$group:{_id:null,score:{$sum:"$score"}}});  <=>  select sum(score) from student;

db.student.aggregate({$group:{_id:null,score:{$avg:"$score"}}});  <=>  select avg(score) from student;

db.student.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 student where createtime >= '2020-11-10 00:00:00' and createtime < '2020-11-11 00:00:00';

两表连接

【student】表和【stuAdress】表关联，两表关联字段是userId相等，关联条件是userId等于2102123

db.student.aggregate([
   {
     $lookup:
       {
         from: "stuAdress",
         localField: "userId",
         foreignField: "userId",
         as: "address_detail"
       }
  },
  { $match : {"userId" :"2102123"} }
])

有点相当于 select a.*，b.* from student a, stuAdress b where a.userId=b.userId and userId='2102123'

输出结果长这样

[
  {
    _id: 1,
    userId: '2102123',
    username: 'zhangsan',
    age:'32',
    address_docs: [
      {
        _id: 1,
        userId: '2102123',
        address: '黑龙江哈尔滨'
      },
      {
        _id: 3,
        userId: '2102123',
        address: '四川成都'
      }
    ]
  }
]

skip(), limilt(), sort()三个放在一起执行的时候，执行的顺序是先 sort(), 然后是 skip()，最后是显示的 limit()。

插入

# 新建一个实例，再插入
doc={"name":"zhangsan","age":"32","sex":"man"}
db.test.insertOne(doc)
# 数据内容直接写入语句
db.test.insertOne({"name":"zhangsan","age":"32","sex":"man"})

# 插入数据成功会返回true，而且会在数据中添加一个_id属性
db.test.insertOne({"name":"zhangsan","age":"32","sex":"man"})				（将数据插入到test表中）
db.coil_new_test.insertOne({"name":"zhangsan","age":"32","sex":"man"})		（将数据插入到coil_new_test表中）
db.coil.new.test.insertOne({"name":"zhangsan","age":"32","sex":"man"})		（将数据插入到coil.new.test表中）