第四章 查询语句: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

上一篇:解读程序员人生的四大象限


下一篇:Java常见错误列表