第四章 查詢語句：MongoDb VS MySql 4.2

? Conditional Operators

<, <=, >, >=,$ne,$in,$nin, $mod

$gt:greater than >

    $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);

應用示例：

<, <=, >, >=,$ne:

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

// e.g. find all where competitionStatus > 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 ] } } )

    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");

    query = new BasicDBObject();

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

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

? $or

SELECT rsc,startDate,startTime, competitionStatus FROM schedule

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

asc,startTime asc limit 10");

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

this.competitionStatus==14

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

    field = new BasicDBObject();

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

? mysql between

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

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

    inList = new ArrayList<Object>();

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

>='2010-11-15' && this.startDate<='2010-11-17';}");

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

本文轉自jooben 51CTO部落格，原文連結:http://blog.51cto.com/jooben/350751