ThinkPHP 数据库操作(四) : 聚合查询、时间查询、高级查询
聚合查询
在应用中我们经常会用到一些统计数据,例如当前所有(或者满足某些条件)的用户数、所有用户的最大积分、用户的平均成绩等等,ThinkPHP为这些统计操作提供了一系列的内置方法,包括:
用法示例:
获取用户数:
Db::table('think_user')->count(); // 助手函数 db('user')->count();
或者根据字段统计:
Db::table('think_user')->count('id'); // 助手函数 db('user')->count('id');
获取用户的最大积分:
Db::table('think_user')->max('score'); // 助手函数 db('user')->max('score');
获取积分大于0的用户的最小积分:
Db::table('think_user')->where('score>0')->min('score'); // 助手函数 db('user')->where('score>0')->min('score');
获取用户的平均积分:
Db::table('think_user')->avg('score'); // 助手函数 db('user')->avg('score');
统计用户的总成绩:
Db::table('think_user')->sum('score'); // 助手函数 db('user')->sum('score');
时间查询
时间比较
使用 where 方法 where
方法支持时间比较,例如:
// 大于某个时间 where('create_time','> time','2016-1-1'); // 小于某个时间 where('create_time','<= time','2016-1-1'); // 时间区间查询 where('create_time','between time',['2015-1-1','2016-1-1']);
第三个参数可以传入任何有效的时间表达式,会自动识别你的时间字段类型,支持的时间类型包括 timestamps 、 datetime 、 date 和 int 。
使用 whereTime 方法
whereTime方法提供了日期和时间字段的快捷查询,示例如下:
// 大于某个时间 db('user') ->whereTime('birthday', '>=', '1970-10-1') ->select(); // 小于某个时间 db('user') ->whereTime('birthday', '<', '2000-10-1') ->select(); // 时间区间查询 db('user') ->whereTime('birthday', 'between', ['1970-10-1', '2000-10-1']) ->select(); // 不在某个时间区间 db('user') ->whereTime('birthday', 'not between', ['1970-10-1', '2000-10-1']) ->select();
时间表达式
还提供了更方便的时间表达式查询,例如:
// 获取今天的博客 db('blog') ->whereTime('create_time', 'today') ->select(); // 获取昨天的博客 db('blog') ->whereTime('create_time', 'yesterday') ->select(); // 获取本周的博客 db('blog') ->whereTime('create_time', 'week') ->select(); // 获取上周的博客 db('blog') ->whereTime('create_time', 'last week') ->select(); // 获取本月的博客 db('blog') ->whereTime('create_time', 'month') ->select(); // 获取上月的博客 db('blog') ->whereTime('create_time', 'last month') ->select(); // 获取今年的博客 db('blog') ->whereTime('create_time', 'year') ->select(); // 获取去年的博客 db('blog') ->whereTime('create_time', 'last year') ->select();
如果查询当天、本周、本月和今年的时间,还可以简化为:
// 获取今天的博客 db('blog') ->whereTime('create_time', 'd') ->select(); // 获取本周的博客 db('blog') ->whereTime('create_time', 'w') ->select(); // 获取本月的博客 db('blog') ->whereTime('create_time', 'm') ->select(); // 获取今年的博客 db('blog') ->whereTime('create_time', 'y') ->select();
V5.0.5+ 版本开始,还可以使用下面的方式进行时间查询
// 查询两个小时内的博客 db('blog') ->whereTime('create_time','2 hours') ->select();
高级查询
快捷查询
快捷查询方式是一种多字段相同查询条件的简化写法,可以进一步简化查询条件的写法,在多个字段之间用 | 分割表示OR查询,用 & 分割表示AND查询,可以实现下面的查询,例如:
Db::table('think_user') ->where('name|title','like','thinkphp%') ->where('create_time&update_time','>',0) ->find();
生成的查询SQL是:
SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' OR `title` LIKE 'thinkphp%') AND ( `create_time` > 0 AND `update_time` > 0 ) LIMIT 1
快捷查询支持所有的查询表达式。
区间查询
区间查询是一种同一字段多个查询条件的简化写法,例如:
Db::table('think_user') ->where('name',['like','thinkphp%'],['like','%thinkphp']) ->where('id',['>',0],['<>',10],'or') ->find();
生成的SQL语句为:
SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' AND `name` LIKE '%thinkphp') AND ( `id` > 0 OR `id` <> 10 ) LIMIT 1
区间查询的查询条件必须使用数组定义方式,支持所有的查询表达式。
下面的查询方式是错误的:
Db::table('think_user') ->where('name',['like','thinkphp%'],['like','%thinkphp']) ->where('id',5,['<>',10],'or') ->find();
批量查询
可以进行多个条件的批量条件查询定义,例如:
Db::table('think_user') ->where([ 'name' => ['like','thinkphp%'], 'title' => ['like','%thinkphp'], 'id' => ['>',0], 'status'=> 1 ]) ->select();
生成的SQL语句为:
SELECT * FROM `think_user` WHERE `name` LIKE 'thinkphp%' AND `title` LIKE '%thinkphp' AND `id` > 0 AND `status` = '1'
闭包查询
Db::table('think_user')->select(function($query){ $query->where('name','thinkphp') ->whereOr('id','>',10); });
生成的SQL语句为:
SELECT * FROM `think_user` WHERE `name` = 'thinkphp' OR `id` > 10
使用Query对象查询
也可以事先封装Query对象,并传入select方法,例如:
$query = new \think\db\Query; $query->name('user') ->where('name','like','%think%') ->where('id','>',10) ->limit(10); Db::select($query);
如果使用 Query 对象的话, select 方法之前调用的任何的链式操作都是无效。
混合查询
可以结合前面提到的所有方式进行混合查询,例如:
Db::table('think_user') ->where('name',['like','thinkphp%'],['like','%thinkphp']) ->where(function($query){ $query->where('id',['<',10],['>',100],'or'); }) ->select();
生成的SQL语句是:
SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' AND `name` LIKE '%thinkphp') AND ( `id` < 10 or `id` > 100 )
字符串条件查询
对于一些实在复杂的查询,也可以直接使用原生SQL语句进行查询,例如:
Db::table('think_user') ->where('id > 0 AND name LIKE "thinkphp%"') ->select();
为了安全起见,我们可以对字符串查询条件使用参数绑定,例如:
Db::table('think_user') ->where('id > :id AND name LIKE :name ',['id'=>0, 'name'=>'thinkphp%']) ->select();
V5.0.4+ 开始,ThinkPHP支持对同一个字段多次调用查询条件,例如:
Db::table('think_user') ->where('name','like','%think%') ->where('name','like','%php%') ->where('id','in',[1,5,80,50]) ->where('id','>',10) ->find();
快捷方法( V5.0.5+ )
V5.0.5+ 版本开始新增了一系列快捷方法,用于简化查询,包括: