thinkphp5.0高级查询技巧

thinkphp5.0高级查询技巧

 

数据库操作是一个入门易精通难的部分,本章我们来带你了解下数据访问层的一些高级查询技巧,熟练掌握的话会让你的查询如虎添翼,学习内容主要包括:

•获取查询SQL
•返回PDOStatement对象
•使用SQL函数或运算
•聚合查询
•快捷查询
•快捷更新
•动态查询
•时间查询
•视图查询
•子查询
•数据分批处理
•总结

获取查询SQL

在任何查询中可以使用fetchSql方法来获取查询的SQL而不是实际执行查询,比较下下面两个查询:
// 有实际查询 返回数组
$result = Db::table(‘user’)
->where(‘id’, 1)
->find();
dump($result);

// 不会进行实际的查询 返回SQL字符串
$result = Db::table(‘user’)
->fetchSql(true)
->where(‘id’, 1)
->find();
echo $result;

fetchSql是一个特殊的链式查询方法,只接受一个布尔值参数,表示是否获取sql语句,为true(默认为true)的时候表示当前的查询不会实际执行,而只会返回最终组装的SQL语句。在个别需要查看运行错误调试的情况下有所帮助。

查询操作使用fetchSql后并不会检测字段是否存在,只是完成查询的SQL组装,但写入操作依然会检测字段是否存在。

返回PDOStatement对象

如果需要进行一些特殊的查询,并且需要利用PDO的一些查询特性,可以使用fetchPdo方法来返回PDOStatement对象而不是数据,以便进行下一步操作,下面是一个示例代码:
$pdo = Db::table(‘user’)
->fetchPdo(true)
->field(‘name’)
->where(‘id’, 1)
->select();
$result = $pdo->fetchColumn();

使用了fetchPdo方法后无论是select还是find方法都是等效的,最终都只是返回PDOStatement对象,然后自己进行查询。

无论是否有符合条件的数据,始终返回PDOStatement对象,然后可以调用PDOStatement对象的所有方法和属性。

V5.0.5+版本开始,查询类新增了getPdo方法直接返回PDOStatement对象,上面的代码可以改为:
$pdo = Db::table(‘user’)
->where(‘id’, 1)
->field(‘name’)
->getPdo();
$result = $pdo->fetchColumn();

由于单独封装的原因,getPdo方法比使用fetchPdo性能更好。

使用SQL函数或运算

如果需要在查询中使用SQL函数或者运算,例如在field方法、update等方法中,可以用下面的方式。

使用SQL函数:
Db::table(‘user’)
->field(‘COUNT(*)’)
->find();

下面是一个使用字段值递增更新数据的例子:
Db::table(‘data’)
->where(‘id’, 1)
->update([
‘read’ => [‘exp’, ‘read+1’],
]);

虽然大部分时候都建议用PHP的运算和方法来替代SQL运算和函数,但该方式在复杂查询的时候仍然可以发挥作用。

基本运算和字段的递增递减框架的查询类已经做好了封装,例如上面的写法可以改为:
Db::table(‘user’)
->count();

Db::table(‘data’)
->where(‘id’, 1)
->setInc(‘read’);

在后面的聚合查询部分会列出全部的聚合运算方法,关于字段的运算和设置可以参考后面的快捷更新。

聚合查询

查询类封装了常用的聚合查询方法,包括:

方法

说明

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

示例代码:
// 统计数量
Db::table(‘user’)
->where(‘age’, ‘>’, 20)
->count();

// 查询用户ID是1的用户成绩总分
Db::table(‘score’)
->where(‘user_id’, 1)
->sum(‘score’);

// 查询班级ID为1的英语成绩最低分
Db::table(‘score’)
->where(‘class’, 1)
->min(‘english’);

// 查询班级的英语平均分
Db::table(‘score’)
->where(‘class’, 1)
->avg(‘english’);

聚合查询方法同样支持fetchSql方法。

快捷查询

为了提高查询语言的书写效率,系统提供了一些技巧或者方法来简化查询用法,我们称之为快捷查询,下面为你一一列举。

多字段相同查询条件

对于多个字段相同查询条件的查询,系统提供了简化写法,多个字段之间用|分割表示OR查询,用&分割表示AND查询,例如:
Db::table(‘user’)
->where(‘name|title’, ‘like’, ‘thinkphp%’)
->where(‘create_time&update_time’, ‘>’, 0)
->find();

生成的查询SQL是:
SELECT * FROM `user` WHERE ( `name` LIKE ‘thinkphp%’ OR `title` LIKE ‘thinkphp%’ ) AND ( `create_time` > 0 AND `update_time` > 0 ) LIMIT 1

快捷查询支持所有的查询表达式。

同一字段多个查询条件

对于同一字段多个查询条件的查询,系统也提供了简化写法,例如:
Db::table(‘user’)
->where(‘name’, [‘like’, ‘thinkphp%’], [‘like’, ‘%thinkphp’])
->where(‘id’, [‘>’, 0], [‘<>’, 10], ‘or’)
->find();

生成的SQL语句为:
SELECT * FROM `user` WHERE ( `name` LIKE ‘thinkphp%’ AND `name` LIKE ‘%thinkphp’ ) AND ( `id` > 0 OR `id` <> 10 ) LIMIT 1

区间查询的查询条件必须使用数组定义方式,支持所有的查询表达式。

下面的查询方式是错误的:
Db::table(‘user’)
->where(‘name’, [‘like’, ‘thinkphp%’], [‘like’, ‘%thinkphp’])
->where(‘id’, 5, [‘<>’, 10], ‘or’)
->find();

一定避免直接使用用户提交的表单数据作为数组查询条件,查询字段应该由系统决定。

快捷查询方法

我们知道,查询方法中where方法是最常用的,因此系统额外封装了(V5.0.5+版本开始)一些用于快捷查询的方法,对IN/NOT IN/BETWEEN/NOT BETWEEN/EXISTS/NOT EXISTS/EXP查询进行了简化,可以省去where方法的第二个查询表达式参数,而且也便于记忆。

包含如下方法:

方法

作用

whereNull 查询字段是否为Null
whereNotNull 查询字段是否不为Null
whereIn 字段IN查询
whereNotIn 字段NOT IN查询
whereBetween 字段BETWEEN查询
whereNotBetween 字段NOT BETWEEN查询
whereLike 字段LIKE查询
whereNotLike 字段NOT LIKE查询
whereExists EXISTS条件查询
whereNotExists NOT EXISTS条件查询
whereExp 表达式查询

举例说明下:
Db::table(‘user’)
->whereNotNull(‘name’)
->whereIn(‘id’, [1, 2, 3])
->whereLike(‘name’, ‘%think%’)
->whereExists(function ($query) {
$query->table(‘profile’)
->whereBetween(‘user_id’, [1, 10]);
})
->select();

系统并没有封装whereOr的快捷查询,只需要在最后一个参数传入OR即可实现OR逻辑条件查询。
Db::table(‘user’)
->whereNotNull(‘name’)
->whereIn(‘id’, [1, 2, 3], ‘or’)
->whereLike(‘name’, ‘%think%’, ‘or’)
->whereExists(function ($query) {
$query->table(‘profile’)
->whereBetween(‘user_id’, [1, 10]);
})
->select();

快捷更新

在写入操作的时候,经常需要使用函数或者运算,为此框架提供了几个快捷更新方法,包括:

方法

描述

setField 更新字段值
setInc 递增更新字段值
setDec 递减更新字段值
data 设置数据(5.0.5+)
inc 递增字段值(5.0.5+)
dec 递减字段值(5.0.5+)
exp 使用SQL表达式写入字段值(5.0.5+)

setField、setInc和setDec方法不属于链式操作方法,用于查询的最终语句,因此每次只能调用一个方法。

data、inc、dec和exp方法属于链式操作方法,仅能配合insert和update方法一起使用。

下面举个例子说明用法:
Db::table(‘data’)
->where(‘id’, 1)
->inc(‘read’)
->dec(‘score’, 3)
->exp(‘name’, ‘UPPER(name)’)
->update();

动态查询

查询构造器还提供了两个动态查询机制,用于简化查询条件,包括getBy和getFieldBy。

动态查询

描述

getByFieldName 根据某个字段查询
getFieldByFieldName 根据某个字段获取某个值

其中FieldName表示数据表的实际字段名称的驼峰法表示,举例来说可能比较明白一些。

假设数据表user中有email和nick_name字段,我们可以这样来查询。
// 根据邮箱查询用户信息
$user = Db::table(‘user’)
->getByEmail(‘thinkphp@qq.com’);
// 根据昵称查询用户信息
$user = Db::table(‘user’)
->field(‘id,name,nick_name,email’)
->getByNickName(‘流年’);
// 根据邮箱查询用户的昵称
$nickname = Db::table(‘user’)
->getFieldByEmail(‘thinkphp@qq.com’, ‘nick_name’);
// 根据昵称查询用户邮箱
$email = Db::table(‘user’)
->getFieldByNickName(‘流年’, ’email’);

getBy和getFieldBy方法只会查询一条记录,可以和其它的链式方法搭配使用

时间查询

5.0的查询语言强化了对时间日期字段的查询支持,对> time 和 between time查询表达式进行了快捷封装。

例如:
// 查询创建时间大于2016-1-1的数据
$result = Db::name(‘data’)
->whereTime(‘create_time’, ‘>’, ‘2016-1-1’)
->select();
dump($result);

// 查询本周添加的数据
$result = Db::name(‘data’)
->whereTime(‘create_time’, ‘>’, ‘this week’)
->select();
dump($result);

// 查询最近两天添加的数据
$result = Db::name(‘data’)
->whereTime(‘create_time’, ‘>’, ‘-2 days’)
->select();
dump($result);

// 查询创建时间在2016-1-1~2016-7-1的数据
$result = Db::name(‘data’)
->whereTime(‘create_time’, ‘between’, [‘2016-1-1’, ‘2016-7-1’])
->select();
dump($result);

日期查询对字段类型没有要求,可以是int/string/timestamp/datetime/date中的任何一种,系统会自动识别进行处理,只要你确认该字段存储的是时间和日期内容。

whereTime方法的优势不仅如此,更为重要的是还可以支持使用人性化日期查询方式,格式为:

whereTime(‘日期字段名’,‘日期表达式’)

支持的日期表达式包括:

today或d 今天
week或w 本周
month或m 本月
year或y 今年
yesterday 昨天
last week 上周
last month 上月
last year 去年

下面是一些代码示例:
// 获取今天的数据
$result = Db::name(‘data’)
->whereTime(‘create_time’, ‘today’)
->select();
dump($result);

// 获取昨天的数据
$result = Db::name(‘data’)
->whereTime(‘create_time’, ‘yesterday’)
->select();
dump($result);

// 获取本周的数据
$result = Db::name(‘data’)
->whereTime(‘create_time’, ‘week’)
->select();
dump($result);

// 获取上周的数据
$result = Db::name(‘data’)
->whereTime(‘create_time’, ‘last week’)
->select();
dump($result);

除了上述时间表达式之外,还可以支持任何有效的时间日期表达式(V5.0.5+版本开始),默认会按照大于该时间表达式代表的时间进行时间和日期查询。
// 获取10小时之前到现在的数据
$result = Db::name(‘data’)
->whereTime(‘create_time’, ’10 hours ago’)
->select();
dump($result);

视图查询

如果需要快捷查询多个表的数据,推荐使用视图查询,相当于通过多次使用view方法在数据库创建了一个视图,例如:
$result = Db::view(‘user’, ‘id,name,status’)
->view(‘profile’, [‘name’ => ‘truename’, ‘phone’, ’email’], ‘profile.user_id=user.id’)
->where(‘status’, 1)
->order(‘id desc’)
->select();
dump($result);

视图查询中指定的字段名可以被直接用于后面的条件和排序方法。

虽然JOIN方法也能实现,但没有视图查询方便,视图查询可以指定字段而不需要调用field方法,并且不用担心字段冲突问题,view方法的参数如下:

view(‘数据表’,‘字段’,‘表JOIN条件’,‘JOIN方式’)

view方法可以被多次调用,每次调用则增加一个数据表关联,第一个调用的view方法不需要指定JOIN条件,后面的其它调用则需要明确指定JOIN条件。

第一个参数表示指定数据表,支持下列用法:
•数据表名(自动识别表前缀)
•[‘完整数据表名’,‘别名’]

如果设置了数据表的前缀,该参数支持使用完整表名或者不带前缀的表名。

视图查询不需要调用table或者name方法,也不建议和join方法混合使用。

第二个参数表示要查询的字段,默认值为true表示查询该表的所有字段,需要设置的话可以使用逗号分割的字符串或者数组,数组方式可以单独设置某个字段的别名,字段名称不需要添加任何别名,系统会自动加上,以免出现字段混淆。

第三个参数表示JOIN条件,和JOIN方法的ON条件一样,注意如果指定了数据表的别名,这里应该使用别名。

第四个参数表示JOIN方式,默认为INNER,支持LEFT/RIGHT/INNER(不区分大小写)。

子查询

如果需要构造子查询,有下面两种方式:

第一种,使用buildSql方法:
$subQuery = Db::table(‘user’)
->field(‘id,name’)
->where(‘id’, ‘>’, 10)
->buildSql();

//然后使用子查询构造新的查询
Db::table($subQuery . ‘ a’)
->where(‘a.name’, ‘like’, ‘thinkphp’)
->order(‘id’, ‘desc’)
->select();

第二种,在查询条件中使用闭包子查询:

对于IN/NOT IN和EXISTS/NOT EXISTS之类的查询可以直接使用闭包作为子查询,例如:
Db::table(‘user’)
->whereIn(‘id’, function ($query) {
$query->table(‘profile’)->where(‘status’, 1)->field(‘user_id’);
})
->select();

Db::table(‘user’)
->whereExists(function ($query) {
$query->table(‘profile’)->where(‘status’, 1);
})
->find();

数据分批处理

对于大量数据的查询,可以使用数据分批处理方法,可以避免一次读取大量数据导致内存开销过大而出错,例如:
Db::table(‘user’)->chunk(100, function ($users) {
foreach ($users as $user) {
// 处理user数据
}
});

上面的代码中,默认会按照主键(顺序)依次处理,每次读取100个,处理完毕后会自动读取下100个数据。

如果不希望使用主键排序来处理,可以在第三个参数指定分批处理的排序字段。
Db::table(‘user’)->chunk(100, function ($users) {
foreach ($users as $user) {
// 处理user数据
}
}, ‘create_time’);

chunk内部也是调用了select方法,并且不需要使用order方法,如果在闭包中返回了false,则会终止后续的查询。

发表评论

电子邮件地址不会被公开。 必填项已用*标注