MongoDB常用语句

分手后的思念是犯贱 2024-03-18 00:37 71阅读 0赞

#### MongoDB常用语句 ####

*  使用
 *  创建和删除
 *  查询
 *   *  条件查询
     *  模糊查询
     *  分页
     *  排序
     *  聚合
     *  两表连接
 *  插入

## 使用 ##

展示数据库

show dbs 或 show databases

查看当前在使用的数据库

展示数据库下所有表

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表中）