前面說到了mongodb安裝,配置,集群,以及php的插入與更新等,請參考:mongodb。
下面說一下,mongodb select的常用操作
測試數據:
復制代碼 代碼如下:
{ "_id" : 1, "title" : "紅樓夢", "auther" : "曹雪芹", "typeColumn" : "test", "money" : 80, "code" : 10 }
{ "_id" : 2, "title" : "圍城", "auther" : "錢鐘書", "typeColumn" : "test", "money" : 56, "code" : 20 }
{ "_id" : 3, "title" : "朝發白帝城", "auther" : "李白", "typeColumn" : "test", "money" : 30, "code" : 30 }
{ "_id" : 4, "title" : "將近酒", "auther" : "李白", "money" : 90, "code" : 40 }
1、取表條數
復制代碼 代碼如下:
> db.books.count();
4
> db.books.find().count();
4
> db.books.count({auther: "李白" });
2
> db.books.find({money:{$gt:40,$lte:60}}).count();
1
> db.books.count({money:{$gt:40,$lte:60}});
1
php代碼如下,按順序對應的:
復制代碼 代碼如下:
$collection->count(); //結果:4
$collection->find()->count(); //結果:4
$collection->count(array("auther"=>"李白")); //結果:2
$collection->find(array("money"=>array('$gt'=>40,'$lte'=>60)))->count(); //結果:1
$collection->count(array("money"=>array('$gt'=>40,'$lte'=>60))); //結果:1
提示:$gt為大於、$gte為大於等於、$lt為小於、$lte為小於等於、$ne為不等於、$exists不存在、$in指定范圍、$nin指定不在某范圍
2、取單條數據
復制代碼 代碼如下:
> db.books.findOne();
{
"_id" : 1,
"title" : "紅樓夢",
"auther" : "曹雪芹",
"typeColumn" : "test",
"money" : 80,
"code" : 10
}
> db.books.findOne({auther: "李白" });
{
"_id" : 3,
"title" : "朝發白帝城",
"auther" : "李白",
"typeColumn" : "test",
"money" : 30,
"code" : 30
}
php代碼如下,按順序對應的
復制代碼 代碼如下:
$collection->findOne();
$collection->findOne(array("auther"=>"李白"));
3、find snapshot 游標
復制代碼 代碼如下:
> db.books.find( { $query: {auther: "李白" }, $snapshot: true } );
{ "_id" : 3, "title" : "朝發白帝城", "auther" : "李白", "typeColumn" : "test", "money" : 30, "code" : 30 }
{ "_id" : 4, "title" : "將近酒", "auther" : "李白", "money" : 90, "code" : 40 }
php代碼如下:
復制代碼 代碼如下:
/**
* 注意:
* 在我們做了find()操作,獲得 $result 游標之後,這個游標還是動態的.
* 換句話說,在我find()之後,到我的游標循環完成這段時間,如果再有符合條件的記錄被插入到collection,那麼這些記錄也會被$result 獲得.
*/
$result = $collection->find(array("auther"=>"李白"))->snapshot();
foreach ($result as $id => $value) {
var_dump($value);
}
4、自定義列顯示
復制代碼 代碼如下:
> db.books.find({},{"money":0,"auther":0}); //money和auther不顯示
{ "_id" : 1, "title" : "紅樓夢", "typeColumn" : "test", "code" : 10 }
{ "_id" : 2, "title" : "圍城", "typeColumn" : "test", "code" : 20 }
{ "_id" : 3, "title" : "朝發白帝城", "typeColumn" : "test", "code" : 30 }
{ "_id" : 4, "title" : "將近酒", "code" : 40 }
> db.books.find({},{"title":1}); //只顯示title列
{ "_id" : 1, "title" : "紅樓夢" }
{ "_id" : 2, "title" : "圍城" }
{ "_id" : 3, "title" : "朝發白帝城" }
{ "_id" : 4, "title" : "將近酒" }
/**
*money在60到100之間,typecolumn和money二列必須存在
*/
> db.books.find({money:{$gt:60,$lte:100}},{"typeColumn":1,"money":1});
{ "_id" : 1, "typeColumn" : "test", "money" : 80 }
{ "_id" : 4, "money" : 90 }
php代碼如下,按順序對應的:
復制代碼 代碼如下:
$result = $collection->find()->fields(array("auther"=>false,"money"=>false)); //不顯示auther和money列
$result = $collection->find()->fields(array("title"=>true)); //只顯示title列
/**
*money在60到100之間,typecolumn和money二列必須存在
*/
$where=array('typeColumn'=>array('$exists'=>true),'money'=>array('$exists'=>true,'$gte'=>60,'$lte'=>100));
$result = $collection->find($where);
5、分頁
復制代碼 代碼如下:
> db.books.find().skip(1).limit(1); //跳過第條,取一條
{ "_id" : 2, "title" : "圍城", "auther" : "錢鐘書", "typeColumn" : "test", "money" : 56, "code" : 20 }
這根mysql,limit,offset有點類似,php代碼如下:
復制代碼 代碼如下:
$result = $collection->find()->limit(1)->skip(1);//跳過 1 條記錄,取出 1條
6、排序
復制代碼 代碼如下:
> db.books.find().sort({money:1,code:-1}); //1表示降序 -1表示升序,參數的先後影響排序順序
{ "_id" : 3, "title" : "朝發白帝城", "auther" : "李白", "typeColumn" : "test", "money" : 30, "code" : 30 }
{ "_id" : 2, "title" : "圍城", "auther" : "錢鐘書", "typeColumn" : "test", "money" : 56, "code" : 20 }
{ "_id" : 1, "title" : "紅樓夢", "auther" : "曹雪芹", "typeColumn" : "test", "money" : 80, "code" : 10 }
{ "_id" : 4, "title" : "將近酒", "auther" : "李白", "money" : 90, "code" : 40 }
php代碼如下:
復制代碼 代碼如下:
$result = $collection->find()->sort(array('code'=>1,'money'=>-1));
7、模糊查詢
復制代碼 代碼如下:
> db.books.find({"title":/城/}); //like '%str%' 糊查詢集合中的數據
{ "_id" : 2, "title" : "圍城", "auther" : "錢鐘書", "typeColumn" : "test", "money" : 56, "code" : 20 }
{ "_id" : 3, "title" : "朝發白帝城", "auther" : "李白", "typeColumn" : "test", "money" : 30, "code" : 30 }
> db.books.find({"auther":/^李/}); //like 'str%' 糊查詢集合中的數據
{ "_id" : 3, "title" : "朝發白帝城", "auther" : "李白", "typeColumn" : "test", "money" : 30, "code" : 30 }
{ "_id" : 4, "title" : "將近酒", "auther" : "李白", "money" : 90, "code" : 40 }
> db.books.find({"auther":/書$/}); //like '%str' 糊查詢集合中的數據
{ "_id" : 2, "title" : "圍城", "auther" : "錢鐘書", "typeColumn" : "test", "money" : 56, "code" : 20 }
> db.books.find( { "title": { $regex: '城', $options: 'i' } } ); //like '%str%' 糊查詢集合中的數據
{ "_id" : 2, "title" : "圍城", "auther" : "錢鐘書", "typeColumn" : "test", "money" : 56, "code" : 20 }
{ "_id" : 3, "title" : "朝發白帝城", "auther" : "李白", "typeColumn" : "test", "money" : 30, "code" : 30 }
php代碼如下,按順序對應的:
復制代碼 代碼如下:
$param = array("title" => new MongoRegex('/城/'));
$result = $collection->find($param);
$param = array("auther" => new MongoRegex('/^李/'));
$result = $collection->find($param);
$param = array("auther" => new MongoRegex('/書$/'));
$result = $collection->find($param);
8、$in和$nin
復制代碼 代碼如下:
> db.books.find( { money: { $in: [ 20,30,90] } } ); //查找money等於20,30,90的數據
{ "_id" : 3, "title" : "朝發白帝城", "auther" : "李白", "typeColumn" : "test", "money" : 30, "code" : 30 }
{ "_id" : 4, "title" : "將近酒", "auther" : "李白", "money" : 90, "code" : 40 }
> db.books.find( { auther: { $in: [ /^李/,/^錢/ ] } } ); //查找以李,錢開頭的數據
{ "_id" : 2, "title" : "圍城", "auther" : "錢鐘書", "typeColumn" : "test", "money" : 56, "code" : 20 }
{ "_id" : 3, "title" : "朝發白帝城", "auther" : "李白", "typeColumn" : "test", "money" : 30, "code" : 30 }
{ "_id" : 4, "title" : "將近酒", "auther" : "李白", "money" : 90, "code" : 40 }
php代碼如下,按順序對應的:
復制代碼 代碼如下:
$param = array("money" => array('$in'=>array(20,30,90)));
$result = $collection->find($param);
foreach ($result as $id=>$value) {
var_dump($value);
}
$param = array("auther" => array('$in'=>array(new MongoRegex('/^李/'),new MongoRegex('/^錢/'))));
$result = $collection->find($param);
foreach ($result as $id=>$value) {
var_dump($value);
}
9、$or
復制代碼 代碼如下:
> db.books.find( { $or: [ { money: 20 }, { money: 80 } ] } ); //查找money等於20,80的數據
{ "_id" : 1, "title" : "紅樓夢", "auther" : "曹雪芹", "typeColumn" : "test", "money" : 80, "code" : 10 }
php代碼如下:
復制代碼 代碼如下:
$param = array('$or'=>array(array("money"=>20),array("money"=>80)));
$result = $collection->find($param);
foreach ($result as $id=>$value) {
var_dump($value);
}
10、distinct
復制代碼 代碼如下:
> db.books.distinct( 'auther' );
[ "曹雪芹", "錢鐘書", "李白" ]
> db.books.distinct( 'auther' , { money: { $gt: 60 } });
[ "曹雪芹", "李白" ]
php代碼如下:
復制代碼 代碼如下:
$result = $curDB->command(array("distinct" => "books", "key" => "auther"));
foreach ($result as $id=>$value) {
var_dump($value);
}
$where = array("money" => array('$gte' => 60));
$result = $curDB->command(array("distinct" => "books", "key" => "auther", "query" => $where));
foreach ($result as $id=>$value) {
var_dump($value);
}
先寫到這兒,上面只是SELECT的一些常用操作,接下來,還會寫一點。
據我所知,目前mongoDB沒有“或”這個東西
但我剛才在網上查了下
發現了下面的信息,你參考下吧
在mongodb中有$or 操作符的,官網中給出的例子如下:
Simple:
db.foo.find( { $or : [ { a : 1 } , { b : 2 } ] } )
With another field
db.foo.find( { name : "bob" , $or : [ { a : 1 } , { b : 2 } ] } )
The $or operator retrieves matches for each or clause individually and eliminates duplicates when returning results. A number of $or optimizations are planned for 1.8. See this thread for details.
$or cannot be nested.
復合查詢?例如。詳細的意思不用我解說了吧
$sql="select * from p_newsbase as a, p_newscontent as b where a.id=b.nid and a.id='$_GET[id]'"