php中的mongodb select常用操作代码示例
前面说到了mongodb安装,配置,集群,以及php的插入与更新等,请参考:mongodb。
下面说一下,mongodbselect的常用操作
测试数据:
{"_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、findsnapshot游标
>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($resultas$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"=>newMongoRegex('/城/')); $result=$collection->find($param); $param=array("auther"=>newMongoRegex('/^李/')); $result=$collection->find($param); $param=array("auther"=>newMongoRegex('/书$/')); $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($resultas$id=>$value){ var_dump($value); } $param=array("auther"=>array('$in'=>array(newMongoRegex('/^李/'),newMongoRegex('/^钱/')))); $result=$collection->find($param); foreach($resultas$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($resultas$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($resultas$id=>$value){ var_dump($value); } $where=array("money"=>array('$gte'=>60)); $result=$curDB->command(array("distinct"=>"books","key"=>"auther","query"=>$where)); foreach($resultas$id=>$value){ var_dump($value); }
先写到这儿,上面只是SELECT的一些常用操作,接下来,还会写一点。