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

mm111222

　　
　　? Conditional Operators
　　, >=,$ne,$in,$nin, $mod
　　$gt:greater than >
　　$lt:less than <
　　$gte:greater than or equal to >=
　　$lte:less than or equal to , &gt;=,$ne:
　　query.put(&quot;competitionStatus&quot;, new BasicDBObject(&quot;$gt&quot;, 0));
　　// e.g. find all where competitionStatus &gt; 0
　　
　　? $in,$nin:
　　List inList = new ArrayList();
　　inList.add(&quot;ATM401101&quot;);
　　inList.add(&quot;ATM053101&quot;);
　　query.put(&quot;rsc&quot;, new BasicDBObject(&quot;$in&quot;, inList));
　　// e.g. find all where rsc in (‘ATM401101’,’ ATM053101’)
　　
　　? $mod特殊写法
　　按照文档：db.things.find( { a : { $mod : [ 10 , 1 ] } } )
　　List inList = new ArrayList();
　　inList.add(12);
　　inList.add(1);
　　query.put(&quot;competitionStatus&quot;, new BasicDBObject(&quot;$mod&quot;, inList));
　　这种写法失败，修改为：
　　System.out.println(&quot;-----------华丽分隔线mod---------------&quot;);
　　System.out.println(&quot;
　　SELECT rsc,startDate,startTime, competitionStatus FROM schedule WHERE competitionStatus/12=1
　　order by startDate asc,startTime asc&quot;);
　　startTime = System.currentTimeMillis();
　　query = new BasicDBObject();
　　query.put(&quot;$where&quot;, &quot;function() { return this.competitionStatus/12
　　== 1; }&quot;);//这种写法成功
　　field = new BasicDBObject();
　　field.put(&quot;rsc&quot;,true);
　　field.put(&quot;startDate&quot;,true);
　　field.put(&quot;startTime&quot;,true);
　　field.put(&quot;competitionStatus&quot;,true);
　　sort = new BasicDBObject();
　　sort.put(&quot;startDate&quot;, 1);
　　sort.put(&quot;startTime&quot;, 1);
　　cur = coll.find(query,field).sort(sort);
　　System.out.println(&quot;查询获得的长度&quot;+cur.count());
　　System.out.println(&quot;查询耗
　　时:&quot;+(System.currentTimeMillis()-startTime)+&quot;毫秒.&quot;);
　　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(&quot;-----------华丽分隔线---------------&quot;);
　　System.out.println(&quot;SELECT count(isGold) FROM schedule&quot;);
　　startTime = System.currentTimeMillis();
　　query = new BasicDBObject();
　　query.put(&quot;isGold&quot;, new BasicDBObject(&quot;$exists&quot;, true));
　　System.out.println(coll.find(query).count());
　　System.out.println(&quot;查询耗
　　时:&quot;+(System.currentTimeMillis()-startTime)+&quot;毫秒.&quot;);
　　
　　? $or
　　
　　System.out.println(&quot;-----------华丽分隔线---------------&quot;);
　　System.out.println(&quot;
　　SELECT rsc,startDate,startTime, competitionStatus FROM schedule
　　WHERE competitionStatus=14 or rsc='ARM102' order by startDate
　　asc,startTime asc limit 10&quot;);
　　startTime = System.currentTimeMillis();
　　query = new BasicDBObject();
　　query.put(&quot;$where&quot;, &quot;function() { return
　　this.competitionStatus==14
　　|| this.rsc=='ARM102'; }&quot;);
　　field = new BasicDBObject();
　　field.put(&quot;rsc&quot;,true);
　　field.put(&quot;startDate&quot;,true);
　　field.put(&quot;startTime&quot;,true);
　　field.put(&quot;competitionStatus&quot;,true);
　　cur = coll.find(query,field).sort(sort).limit(10);
　　System.out.println(&quot;查询获得的长度&quot;+cur.count());
　　System.out.println(&quot;查询耗
　　时:&quot;+(System.currentTimeMillis()-startTime)+&quot;毫秒.&quot;);
　　showAllDataFromCursor(cur);
　　
　　? mysql between
　　System.out.println(&quot;-----------华丽分隔线---------------&quot;);
　　System.out.println(&quot;SELECT rsc,startDate,startTime FROM schedule
　　WHERE startDate between '2010-11-15' and '2010-11-17'
　　order by startDate asc,startTime asc&quot;);
　　startTime = System.currentTimeMillis();
　　query = new BasicDBObject();
　　inList = new ArrayList();
　　inList.add(12);
　　inList.add(1);
　　query.put(&quot;$where&quot;, &quot;function() { return this.startDate
　　&gt;='2010-11-15' && this.startDate&lt;='2010-11-17';}&quot;);
　　field = new BasicDBObject();
　　field.put(&quot;rsc&quot;,true);
　　field.put(&quot;startDate&quot;,true);
　　field.put(&quot;startTime&quot;,true);
　　sort = new BasicDBObject();
　　sort.put(&quot;startDate&quot;, 1);
　　sort.put(&quot;startTime&quot;, 1);
　　cur = coll.find(query,field).sort(sort).limit(10);
　　System.out.println(&quot;查询获得的长度&quot;+cur.count());
　　System.out.println(&quot;查询耗
　　时:&quot;+(System.currentTimeMillis()-startTime)+&quot;毫秒.&quot;);
　　showAllDataFromCursor(cur);