第四章 查询语句：MongoDb VS MySql 4.2

? Conditional Operators

<, &lt;=, >, &gt;=,$ne,$in,$nin, $mod

$gt:greater than &gt;

    $lt:less than <

     $gte:greater than or equal to >=

     $lte:less than or equal to <=

$ne: not equals !=

$in:select * from schedule where competitionStatus in (1,2,3);

    

应用示例：

&lt;, &lt;=, >, &gt;=,$ne:

query.put("competitionStatus", new BasicDBObject("$gt", 0)); 

// e.g. find all where competitionStatus &gt; 0

? $in,$nin:

List inList = new ArrayList<Object>();

    inList.add("ATM401101");

    inList.add("ATM053101");

query.put("rsc", new BasicDBObject("$in", inList)); 

// e.g. find all where rsc in (‘ATM401101’,’ ATM053101’)

? $mod特殊写法

按照文档：db.things.find( { a : { $mod : [ 10 , 1 ] } } )

List inList = new ArrayList<Object>();

    inList.add(12);

    inList.add(1);

query.put("competitionStatus", new BasicDBObject("$mod", inList));

这种写法失败，修改为：

System.out.println("-----------华丽分隔线mod---------------");

System.out.println("

SELECT rsc,startDate,startTime, competitionStatus FROM schedule WHERE competitionStatus/12=1

order by startDate asc,startTime asc");

    startTime = System.currentTimeMillis();  

    query = new BasicDBObject();

query.put("$where", "function() { return this.competitionStatus/12

== 1; }");//这种写法成功

field = new BasicDBObject();

    field.put("rsc",true);

    field.put("startDate",true);

    field.put("startTime",true);

    field.put("competitionStatus",true);

    sort = new BasicDBObject();

    sort.put("startDate", 1);

    sort.put("startTime", 1);

    cur = coll.find(query,field).sort(sort); 

    System.out.println("查询获得的长度"+cur.count());

System.out.println("查询耗

时:"+(System.currentTimeMillis()-startTime)+"毫秒.");

showAllDataFromCursor(cur);

? $exists

Check for existence (or lack thereof) of a field.

Currently $exists is not able to use an index. Indexes on other fields are still used. 

System.out.println("-----------华丽分隔线---------------");

    System.out.println("SELECT count(isGold) FROM schedule");

    startTime = System.currentTimeMillis();  

    query = new BasicDBObject();

    query.put("isGold", new BasicDBObject("$exists", true));

    System.out.println(coll.find(query).count()); 

System.out.println("查询耗

时:"+(System.currentTimeMillis()-startTime)+"毫秒.");

? $or

System.out.println("-----------华丽分隔线---------------");

System.out.println("

SELECT rsc,startDate,startTime, competitionStatus FROM schedule

WHERE competitionStatus=14 or rsc='ARM102' order by startDate

asc,startTime asc limit 10");

    startTime = System.currentTimeMillis();  

    query = new BasicDBObject();

query.put("$where", "function() { return

this.competitionStatus==14

|| this.rsc=='ARM102'; }"); 

    field = new BasicDBObject();

    field.put("rsc",true);

    field.put("startDate",true);

    field.put("startTime",true);

    field.put("competitionStatus",true);

    cur = coll.find(query,field).sort(sort).limit(10);  

    System.out.println("查询获得的长度"+cur.count());

System.out.println("查询耗

时:"+(System.currentTimeMillis()-startTime)+"毫秒.");

showAllDataFromCursor(cur);

? mysql between

System.out.println("-----------华丽分隔线---------------");

System.out.println("SELECT rsc,startDate,startTime FROM schedule

WHERE startDate between '2010-11-15' and '2010-11-17'

order by startDate asc,startTime asc");

    startTime = System.currentTimeMillis();  

    query = new BasicDBObject();

    inList = new ArrayList<Object>();

    inList.add(12);

    inList.add(1);

query.put("$where", "function() { return this.startDate

&gt;='2010-11-15' && this.startDate&lt;='2010-11-17';}");

    field = new BasicDBObject();

    field.put("rsc",true);

    field.put("startDate",true);

    field.put("startTime",true);

    sort = new BasicDBObject();

    sort.put("startDate", 1);

    sort.put("startTime", 1);

    cur = coll.find(query,field).sort(sort).limit(10); 

    System.out.println("查询获得的长度"+cur.count());

System.out.println("查询耗

时:"+(System.currentTimeMillis()-startTime)+"毫秒.");

showAllDataFromCursor(cur);

本文转自jooben 51CTO博客，原文链接:http://blog.51cto.com/jooben/350751