ThinkPHP5数据库的进阶查询

Published on 2016 - 12 - 19

聚合查询

在应用中我们经常会用到一些统计数据,例如当前所有(或者满足某些条件)的用户数、所有用户的最大积分、用户的平均成绩等等,ThinkPHP为这些统计操作提供了一系列的内置方法,包括:

方法 说明
count 统计数量,参数是要统计的字段名(可选)
max 获取最大值,参数是要统计的字段名(必须)
min 获取最小值,参数是要统计的字段名(必须)
avg 获取平均值,参数是要统计的字段名(必须)
sum 获取总分,参数是要统计的字段名(必须)

用法示例:

获取用户数:

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

高级查询

快捷查询

快捷查询方式是一种多字段相同查询条件的简化写法,可以进一步简化查询条件的写法,在多个字段之间用|分割表示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();

视图查询

视图查询可以实现不依赖数据库视图的多表查询,并不需要数据库支持视图,例如:

Db::view('User','id,name')
    ->view('Profile','truename,phone,email','Profile.user_id=User.id')
    ->view('Score','score','Score.user_id=Profile.id')
    ->where('score','>',80)
    ->select();

生成的SQL语句类似于:

SELECT User.id,User.name,Profile.truename,Profile.phone,Profile.email,Score.score FROM think_user User INNER JOIN think_profile Profile ON Profile.user_id=User.id INNER JOIN think_socre Score ON Score.user_id=Profile.id WHERE Score.score > 80

注意,视图查询无需调用table和join方法,并且在调用where和order方法的时候只需要使用字段名而不需要加表名。

默认使用INNER join查询,如果需要更改,可以使用:

Db::view('User','id,name')
    ->view('Profile','truename,phone,email','Profile.user_id=User.id','LEFT')
    ->view('Score','score','Score.user_id=Profile.id','RIGHT')
    ->where('score','>',80)
    ->select();

生成的SQL语句类似于:

SELECT User.id,User.name,Profile.truename,Profile.phone,Profile.email,Score.score FROM think_user User LEFT JOIN think_profile Profile ON Profile.user_id=User.id RIGHT JOIN think_socre Score ON Score.user_id=Profile.id WHERE Score.score > 80

可以使用别名:

Db::view('User',['id'=>'uid','name'=>'account'])
    ->view('Profile','truename,phone,email','Profile.user_id=User.id')
    ->view('Score','score','Score.user_id=Profile.id')
    ->where('score','>',80)
    ->select();

生成的SQL语句变成:

SELECT User.id AS uid,User.name AS account,Profile.truename,Profile.phone,Profile.email,Score.score FROM think_user User INNER JOIN think_profile Profile ON Profile.user_id=User.id INNER JOIN think_socre Score ON Score.user_id=Profile.id WHERE Score.score > 80

可以使用数组的方式定义表名以及别名,例如:

Db::view(['think_user'=>'member'],['id'=>'uid','name'=>'account'])
    ->view('Profile','truename,phone,email','Profile.user_id=member.id')
    ->view('Score','score','Score.user_id=Profile.id')
    ->where('score','>',80)
    ->select();

生成的SQL语句变成:

SELECT member.id AS uid,member.name AS account,Profile.truename,Profile.phone,Profile.email,Score.score FROM think_user member INNER JOIN think_profile Profile ON Profile.user_id=member.id INNER JOIN think_socre Score ON Score.user_id=Profile.id WHERE Score.score > 80

子查询

首先构造子查询SQL,可以使用下面三种的方式来构建子查询。

使用select方法

当select方法的参数为false的时候,表示不进行查询只是返回构建SQL,例如:

$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id','>',10)
    ->select(false); 

生成的subQuery结果为:

SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 

使用fetchSql方法

fetchSql方法表示不进行查询而只是返回构建的SQL语句,并且不仅仅支持select,而是支持所有的CURD查询。

$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id','>',10)
    ->fetchSql(true)
    ->select();

生成的subQuery结果为:

SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 

使用buildSql构造子查询

$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id','>',10)
    ->buildSql();

生成的subQuery结果为:

( SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 )

调用buildSql方法后不会进行实际的查询操作,而只是生成该次查询的SQL语句(为了避免混淆,会在SQL两边加上括号),然后我们直接在后续的查询中直接调用。

需要注意的是,使用前两种方法需要自行添加‘括号’。

然后使用子查询构造新的查询:

Db::table($subQuery.' a')
    ->where('a.name','like','thinkphp')
    ->order('id','desc')
    ->select();

生成的SQL语句为:

SELECT * FROM ( SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 ) a WHERE a.name LIKE 'thinkphp' ORDER BY `id` desc

使用闭包构造子查询

IN/NOT IN和EXISTS/NOT EXISTS之类的查询可以直接使用闭包作为子查询,例如:

Db::table('think_user')
->where('id','IN',function($query){
    $query->table('think_profile')->where('status',1)->field('id');
})
->select();

生成的SQL语句是

SELECT * FROM `think_user` WHERE `id` IN ( SELECT `id` FROM `think_profile` WHERE `status` = 1 )
Db::table('think_user')
->where(function($query){
    $query->table('think_profile')->where('status',1);
},'exists')
->find();

生成的SQL语句为

SELECT * FROM `think_user` WHERE EXISTS ( SELECT * FROM `think_profile` WHERE `status` = 1 ) 

原生查询

Db类支持原生SQL查询操作,主要包括下面两个方法:

query方法

query方法用于执行SQL查询操作,如果数据非法或者查询错误则返回false,否则返回查询结果数据集(同select方法)。

使用示例:

Db::query("select * from think_user where status=1");

如果你当前采用了分布式数据库,并且设置了读写分离的话,query方法始终是在读服务器执行,因此query方法对应的都是读操作,而不管你的SQL语句是什么。

execute方法

execute用于更新和写入数据的sql操作,如果数据非法或者查询错误则返回false ,否则返回影响的记录数。

使用示例:

Db::execute("update think_user set name='thinkphp' where status=1");

如果你当前采用了分布式数据库,并且设置了读写分离的话,execute方法始终是在写服务器执行,因此execute方法对应的都是写操作,而不管你的SQL语句是什么。

参数绑定

支持在原生查询的时候使用参数绑定,包括问号占位符或者命名占位符,例如:

Db::query("select * from think_user where id=? AND status=?",[8,1]);
// 命名绑定
Db::execute("update think_user set name=:name where status=:status",['name'=>'thinkphp','status'=>1]);

参考文档