MongoDB 语法和mysql语法对比学习

朴灿烈づ我的快乐病毒、 2022-08-20 12:13 172阅读 0赞

查询test所有数据  
        db.test.find() //select \* from test

通过指定条件查询  
        db.test.find(\{‘last\_name': ‘Smith'\});//select \* from test where last\_name='Smith'   
    指定多条件查询  
        db.test.find( \{ x : 3, y : “foo” \} );//select \* from test where x=3 and y='foo'  
  
    指定条件范围查询  
        db.test.find(\{j: \{$ne: 3\}, k: \{$gt: 10\} \});//select \* from test where j!=3 and k>10  
  
  
    查询不包括某内容  
        db.test.find(\{\}, \{a:0\});//查询结果不显示a列  
  
  
    支持<, <=, >, >=查询，需用符号替代分别为$lt，$lte，$gt，$gte  
        db.test.find(\{ “field” : \{ $gt: value \} \} );   
        db.test.find(\{ “field” : \{ $lt: value \} \} );   
        db.test.find(\{ “field” : \{ $gte: value \} \} );  
        db.test.find(\{ “field” : \{ $lte: value \} \} );  
  
  
    也可对某一字段做范围查询  
        db.test.find(\{ “field” : \{ $gt: value1, $lt: value2 \} \} );  
  
  
    不等于查询用字符$ne  
        db.test.find( \{ x : \{ $ne : 3 \} \} );  
  
  
    in查询用字符$in  
        db.test.find( \{ “field” : \{ $in : array \} \} );  
        db.test.find(\{ age :\{$in: \[2,4,6\]\}\});  
  
  
    not in查询用字符$nin  
        db.test.find(\{age:\{$nin: \[2,4,6\]\}\});  
  
  
    取模查询用字符$mod  
        db.test.find( \{ age : \{ $mod : \[ 10 , 1 \] \} \} )// where a % 10 == 1  
  
  
    $all查询  
        db.test.find( \{ age: \{ $all: \[ 2, 3 \] \} \} );//指定a满足数组中任意值时  
  
  
    $size查询  
        db.test.find( \{ age : \{ $size: 1 \} \} );//对对象的数量查询，此查询查询a的子对象数目为1的记录  
  
  
    $exists查询  
        db.test.find( \{ age : \{ $exists : true \} \} ); // 存在a对象的数据  
        db.test.find( \{ age : \{ $exists : false \} \} ); // 不存在a对象的数据  
  
  
    $type查询$type值为bsonhttp://bsonspec.org/数 据的类型值  
        db.test.find( \{ age : \{ $type : 2 \} \} ); // 匹配a为string类型数据  
        db.test.find( \{ age : \{ $type : 16 \} \} ); // 匹配a为int类型数据  
  
  
    使用正则表达式匹配  
        db.test.find( \{ name : /acme.\*corp/i \} );//类似于SQL中like  
  
  
    内嵌对象查询  
        db.test.find( \{ “author.name” : “joe” \} );  
  
  
    1.3.3版本及更高版本包含$not查询  
        db.test.find( \{ name : \{ $not : /acme.\*corp/i \} \} );  
        db.test.find( \{ age : \{ $not : \{ $mod : \[ 10 , 1 \] \} \} \} );  
  
  
    sort()排序  
        db.test.find().sort( \{ ts : -1 \} );//1为升序2为降序  
  
  
    limit()对限制查询数据返回个数  
        db.test.find().limit(10)  
  
  
    skip()跳过某些数据  
        db.test.find().skip(10)  
  
  
    snapshot()快照保证没有重复数据返回或对象丢失  
  
  
    count()统计查询对象个数  
        db.students.find(\{‘address.state' : ‘CA'\}).count();//效率较高  
        db.students.find(\{‘address.state' : ‘CA'\}).toArray().length;//效率很低  
  
  
    group()对查询结果分组和SQL中group by函数类似  
    distinct()返回不重复值

<table style="color:rgb(0,0,0); font-size:14px; line-height:28px; border-collapse:collapse; border-color:rgb(163,163,163); border-width:1px; font-family:sans-serif; padding-left:3px"> 
 <tbody> 
  <tr> 
   <td>grant * privileges on *.* to insert</td> 
   <td>db.addUser()<br> db.auth()</td> 
   <td>新建用户并权限</td> 
  </tr> 
  <tr> 
   <td>show databases</td> 
   <td>show dbs</td> 
   <td>显示库列表</td> 
  </tr> 
  <tr> 
   <td>show tables</td> 
   <td>show collections</td> 
   <td>显示表列表</td> 
  </tr> 
  <tr> 
   <td>show slave status</td> 
   <td>rs.status</td> 
   <td>查询主从状态</td> 
  </tr> 
  <tr> 
   <td>create table users(a int, b int)</td> 
   <td>db.createCollection("mycoll", {capped:true,size:100000}))<br> 另：可隐式创建表。</td> 
   <td>创建表</td> 
  </tr> 
  <tr> 
   <td>create index idxname on users(name)</td> 
   <td>db.users.ensureIndex({name:1})</td> 
   <td>创建索引</td> 
  </tr> 
  <tr> 
   <td>create index idxname on users(name,ts desc)</td> 
   <td>db.users.ensureIndex({name:1,ts:-1})</td> 
   <td>创建索引</td> 
  </tr> 
  <tr> 
   <td>insert into users (a,b) values (1, 1)</td> 
   <td>db.users.insert({a:1, b:1})</td> 
   <td>插入记录</td> 
  </tr> 
  <tr> 
   <td>select * from users</td> 
   <td>db.users.find()</td> 
   <td>查询表</td> 
  </tr> 
  <tr> 
   <td>select a, b from users</td> 
   <td>db.users.find({},{a:1, b:1})</td> 
   <td>查询某列</td> 
  </tr> 
  <tr> 
   <td>select * from users where age=33</td> 
   <td>db.users.find({age:33})</td> 
   <td>条件查询</td> 
  </tr> 
  <tr> 
   <td>select a, b from users where age=33</td> 
   <td>db.users.find({age:33},{a:1, b:1})</td> 
   <td>条件查询</td> 
  </tr> 
  <tr> 
   <td>select * from users where age&lt;33</td> 
   <td>db.users.find({age:{$lt:33}})</td> 
   <td>小于</td> 
  </tr> 
  <tr> 
   <td>select * from users where age&gt;33 and age&lt;=40</td> 
   <td>db.users.find({age:{$gt:33,$lte:40}})</td> 
   <td>大于 、小于</td> 
  </tr> 
  <tr> 
   <td>select * from users where a=1 and b='q'</td> 
   <td>db.users.find({a:1,b:'q'})</td> 
   <td>条件查询</td> 
  </tr> 
  <tr> 
   <td>select * from users where a=1 or b=2</td> 
   <td>db.users.find({$or:[{a:1},{b:2}]})</td> 
   <td>条件查询</td> 
  </tr> 
  <tr> 
   <td>select * from users limit 1</td> 
   <td>db.users.findOne()</td> 
   <td>条件查询</td> 
  </tr> 
  <tr> 
   <td>select * from users where name like '%Joe%'</td> 
   <td>db.users.find({name:/Joe/})</td> 
   <td>模糊查询</td> 
  </tr> 
  <tr> 
   <td>select * from users where name like 'Joe%'</td> 
   <td>db.users.find({name:/^Joe/})</td> 
   <td>模糊查询</td> 
  </tr> 
  <tr> 
   <td>select count(1) from users</td> 
   <td>db.users.count()</td> 
   <td>获取表记录数</td> 
  </tr> 
  <tr> 
   <td>select count(1) from users where age &gt;30</td> 
   <td>db.users.find({age: {$gt: 30}}).count()</td> 
   <td>获取表记录数</td> 
  </tr> 
  <tr> 
   <td>select distinct last_name from users</td> 
   <td>db.users.distinct('last_name')</td> 
   <td>去掉重复值</td> 
  </tr> 
  <tr> 
   <td>select * from users order by name</td> 
   <td>db.users.find().sort({name:-1})</td> 
   <td>排序</td> 
  </tr> 
  <tr> 
   <td>select * from users order by name desc</td> 
   <td>db.users.find().sort({name:-1})</td> 
   <td>排序</td> 
  </tr> 
  <tr> 
   <td>explain select * from users where z=3</td> 
   <td>db.users.find({z:3}).explain()</td> 
   <td>获取存储路径</td> 
  </tr> 
  <tr> 
   <td>update users set a=1 where b='q'</td> 
   <td>db.users.update({b:'q'}, {$set:{a:1}},false,true)</td> 
   <td>更新记录</td> 
  </tr> 
  <tr> 
   <td>update users set a=a+2 where b='q'</td> 
   <td>db.users.update({b:'q'}, {$inc:{a:2}},false,true)</td> 
   <td>更新记录</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db. users.remove()</td> 
   <td>删除所有的记录</td> 
  </tr> 
  <tr> 
   <td>delete from users where z='abc'</td> 
   <td>db.users.remove({z:'abc'})</td> 
   <td>删除记录</td> 
  </tr> 
  <tr> 
   <td>drop database if exists test;</td> 
   <td>use test db.dropDatabase()</td> 
   <td>删除数据库</td> 
  </tr> 
  <tr> 
   <td>drop table if exists test;</td> 
   <td>db.mytable.drop()</td> 
   <td>删除表/collection</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db.addUser('test', 'test')</td> 
   <td>添加用户 readOnly--&gt;false</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db.addUser('test', 'test', true)</td> 
   <td>添加用户 readOnly--&gt;true</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db.addUser("test","test222")</td> 
   <td>更改密码</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db.system.users.remove({user:"test"}) 或者db.removeUser('test')</td> 
   <td>删除用户</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>use admin</td> 
   <td>超级用户</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db.auth('test','test')</td> 
   <td>用户授权</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db.system.users.find()</td> 
   <td>查看用户列表</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>show users</td> 
   <td>查看所有用户</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db.printCollectionStats()</td> 
   <td>查看各collection的状态</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db.printReplicationInfo()</td> 
   <td>查看主从复制状态</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>show profile</td> 
   <td>查看profiling</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db.copyDatabase('mail_addr','mail_addr_tmp')</td> 
   <td>拷贝数据库</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db.users.dataSize()</td> 
   <td>查看collection数据的大小</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>db. users.totalIndexSize()</td> 
   <td>查询索引的大小<br> </td> 
  </tr> 
 </tbody> 
</table>