MySQL与MongoDB语法对比（简单）

在实习的过程中，主要是对mongo中的数据进行select操作，故重点总结的是mongo的查询语法。

----------
-- 增
  insert into student(name,sex,age) values('gx','female','20');    
  db.getCollection('student').save({"name":"gx","sex":"female","age":22});
  var stuinfo={"name":"gx","sex":"女","age":22}   
  db.getCollection('student').insert(stuinfo);

----------
-- 删
  delete from student where name="gx";
  db.getCollection("student").remove("name":"gx");

----------
-- 改
   update student set age="18" where name="gx";
   db.getCollection("student").update({"name":"gx"},{$set:{"age":18}});
   db.getCollection("student").update({"name":"gx"},{"age":18});
   update student set age=age+1;
   db.getCollection("student").update({},{$inc:{"age":1}});

----------
-- 查
 -- 1. 查询全部列
   select * from student;
   db.getCollection("student").find();
   db.getCollection("student").find({});
 -- 2. 查询不重复的列
   select distinct name from student;
   db.getCollection("student").find({});
 -- 3.条件查询（可从条件的反面查询）
  -- 等于
  select * from student where age=20;                
  db.getcollection('student').find({"age":20});
  -- 特殊的等于
  select * from student where a=b;               
  db.getCollection('student').find({$where:"this.a== this.b"});                                            
  db.getCollection('student').find({$where:function() {return this.a==this.b}});
  -- 不等于
  select * from student where age<>20;   
  db.getcollection('student').find({"age":{$ne:20}});
  db.getCollection('student').find({$nor:[{"age":20}]});
  -- 大于                        
  select * from student where age>20;  
  db.getCollection('student').find({"age":{$gt:20}});
  -- 小于
  select * from student where age<20 ;      
  db.getCollection('student').find({"age":{$lt:20}});
  -- 大于等于                                      
  select * from student where age>=20;     
  db.getCollection('student').find({"age":{$gte:20}};                                    
  db.getCollection('student').find({age:{$not:{$lt:20}}});
  -- 小于等于                                   
  select * from student where age<=20;     
  db.getCollection('student').find({"age":{$lte:20}});                                  
  db.getCollection('student').find({"age":{$not:{$gt:20}}});
  -- 4.AND运算符
  -- 数值范围查询
  select * from student where age between 15 and 20;   
  db.getcollection('student').find({age:{$gt:15,$lt:20}}); 
  -- 多条件查询，and连接                                   
  select * from sudent where age=20 and sex="女";  
  db.getcollection('student').find({"age":20,"sex":"女"});
  db.getCollection('student').find({$and:[{"age":20},{"sex":"女"}]});
 -- 5. OR运算符
 select * from student where age=20 or sex="女";  
 db.getCollection('student').find({$or:[{"age":20},{"sex":"女"}]});
 -- 6.空值查询
  -- 注意：不加exists将会把字段不存在的记录也查询出来
 select * fron student where name is null;  
 db.getCollection('student').find({name:{$in:[null],$exists:true}});
 -- 7. IN/ NOT IN
  -- IN
 select * from student where name in ('name1','name2');  
 db.getCollection('student').find({name:{$in:['name1','name2']}});
 -- NOT IN
 select * from student where name not in ('name1','name2');
 db.getCollection('student').find({name:{$nin:['name1','name2']}});
 -- 8. COUNT计数
 select count(*) from student; 
 db.getCollection('student').find().count();
 select count(name) from student;  
 db.getCollection('student').find({"name":{$exists:true}}).count();
 -- 9.模糊匹配查询(正则表达式)
 select * from student where name like "%g%";  
 db.getCollection('student').find({"name":/g/})
 select * from student where name like "g%";  
 db.getCollection('student').find({"name":/^g/});
 -- 10.排序
  -- 升序
 select * from student order by age asc;       
 db.getCollection('student').find().sort({"age":1});
  -- 降序
 select * from student order by age desc;        
 db.getCollection('student').find().sort({"age":-1});
 --11.查询某几行
  select top 10 from student;                
  db.getCollection('student').find().limit(10);
 --12.查询指定的列
  select name,sex from student;
  db.getcollection('student').find({},{"name":1,"sex":1,"_id":0});
  select name,sex from student where sex="女"     
 -- 13.查询内嵌文档
  db.getCollection("backend_charts").find({area:{x:278,y:432,w:71,h:114}});
  db.getCollection("backend_charts").find
 ({"area.x":278,"area.y":432,"area.w":71,"area.h":114});