Thinkphp 模型和数据库：高级查询技巧

2019-07-09 本文已影响0人寒冬夜行人_51a4

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

获取查询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_times' => ['exp', 'read_times+1'],
    ]);

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

基本运算和字段的递增递减框架的查询类已经做好了封装，例如上面的写法可以改为：

Db::table('user')
    ->count();

Db::table('data')
    ->where('id', 1)
    ->setInc('read_times');

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

聚合查询

查询类封装了常用的聚合查询方法，包括：

方法	说明
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_times')
    ->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，则会终止后续的查询。

总结

本章我们了解和学习了几个高级查询技巧，希望对你有所帮助，数据库的学习内容就到此告一段落，如果还有困惑的话可以尝试多做一些查询构造器的例子巩固下，下一章开始我们会讲解本书的关键——模型。

上一篇：第三章：查询构造器
下一篇：第五章：模型和对象