「ThinkPHP 6.0」数据库链式操作

原生查询聚合查询
高级查询视图查询
子查询

原生查询

参考资料：TP6 原生查询官方文档
原生查询可以说是让人又爱又恨
简单的语句用不到，复杂的语句用TP6 的构造器又更麻烦，导致无法彻底抛弃原生
query() 可执行任何原生语句
execute() 仅用于更新以及写入数据，不可做其他用处

因为execute() 在SQL语句无错误的情况下，仅返回受影响条数，否则返回false

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;

class Index extends BaseController
{
    public function index()
    {
        //下面用原生查询 think_tp 表中的所有数据
        $sql = " SELECT * FROM think_tp ";
        dump(Db::query($sql));

        //用execute() 执行查询语句
        dump(Db::execute($sql));
    }
}

图↓

聚合查询

参考资料：TP6 聚合查询官方文档
在查询中，经常用到一些需要统计数据
count() 统计数量，参数是要统计的字段名（可选）
max() 获取最大值，参数是要统计的字段名（必须）
min() 获取最小值，参数是要统计的字段名（必须）
avg() 获取平均值，参数是要统计的字段名（必须）
sum() 获取总和，参数是要统计的字段名（必须）

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;

class Index extends BaseController
{
    public function index()
    {
        //查询 think_tp 表数据总条数
        echo "count() 查询总条数";
        dump(Db::name("tp")->count());

        echo "查询 age 最大值";
        dump(Db::name("tp")->max("age"));

        echo "查询 age 最小值";
        dump(Db::name("tp")->min("age"));

        echo "查询 age 平均值";
        dump(Db::name("tp")->avg("age"));

        echo "查询 age 总和";
        dump(Db::name("tp")->sum("age"));
    }
}

图 ↓

注意：max() min() 支持第二个参数，是否开启强制转换，因为有时候，查询的字段并不都是数值
第二值默认为 true ，可传入 false 关闭强制转换

高级查询

参考链接：TP6 高级查询官方文档

1.快捷查询
快捷查询方式是一种多字段相同查询条件的简化写法
| 代表 or ，& 代表 and

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;

class Index extends BaseController
{
    public function index()
    {
        //快捷查询 | 代表or ，& 代表 and
        $back = Db::name("tp")
            ->where("id|email","like","12%")
            ->where("id&age",">","9")
            ->select();

        echo Db::name("tp")
            ->where("id|emali","like","12%")
            ->where("id&age",">","9")
            ->fetchSql()
            ->select();

        dump($back);

    }
}

图 ↓

2.批量（字段）查询

基础用法

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;

class Index extends BaseController
{
    public function index()
    {
        $back = Db::name("tp")
            ->where([
                ["email","like","123%"],
                ["id",">","4"]
            ])
            ->select();

        echo Db::name("tp")
            ->where([
                ["email","like","123%"],
                ["id",">","4"]
            ])
            ->fetchSql()
            ->select();

        dump($back);

    }
}

图 ↓

exp BUG疑问
此BUG（暂做BUG看）告诉我们，字段与字段比对，必须exp
whereExp()

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;

class Index extends BaseController
{
    public function index()
    {
        $back = Db::name("tp")
            ->where([
                ["email","like","123%"],
                ["id",">","age"]
            ])
            ->select();
        echo Db::name("tp")
            ->where([
                ["email","like","123%"],
                ["id",">","age"]
            ])
            ->fetchSql()
            ->select();
        
        $back2 = Db::name("tp")
            ->where([
                ["email","like","123%"],
                ["id","exp",Db::raw(">age")]
            ])
            ->select();
        echo "<br>".Db::name("tp")
            ->where([
                ["email","like","123%"],
                ["id","exp",Db::raw(">age")]
            ])
            ->fetchSql()
            ->select();

        dump($back);
        dump($back2);
    }
}

图 ↓

注意：exp 必须配合 Db::raw() 使用

3.数组组合方式，直接变量组合

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;

class Index extends BaseController
{
    public function index()
    {
        //数组查询方式，变量直接组合
        $email = "Sn";
        $nick = "s";
        $back = Db::name("tp")
            ->where([
                ["nickname","like",$nick."%"],
                ["email","like",$email."%"]
            ])
            ->select();

        echo Db::name("tp")
            ->where([
                ["nickname","like",$nick."%"],
                ["email","like",$email."%"]
            ])
            ->fetchSql()
            ->select();
        dump($back);
    }
}

图 ↓

4.闭包查询

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;

class Index extends BaseController
{
    public function index()
    {
        //闭包查询
        $email = "Sn";
        $nick = "s";
        $back = Db::name("tp")
            ->where(function ($query) use ($email,$nick){
                $query->where([
                        ["nickname","like",$nick."%"],
                        ["email","like",$email."%"]
                ]);
            })
            ->select();

        echo Db::name("tp")
            ->where(function ($query) use ($email,$nick){
                $query->where([
                    ["nickname","like",$nick."%"],
                    ["email","like",$email."%"]
                ]);
            })
            ->fetchSql()
            ->select();
        dump($back);
    }
}

图 ↓

5.混合查询
只要符合语法规则的写法，都能同时一起写

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;

class Index extends BaseController
{
    public function index()
    {
        //混合查询
        $email = "Sn";
        $nick = "s";
        $back = Db::name("tp")
            //变量组合写法
            ->where("nickname","like",$nick."%")
            //闭包写法
            ->where(function ($query) use ($email){
                $query->where([
                    ["email","like",$email."%"]
                ])
                //字符串写法
                ->whereOr("age > 0 and 1=1");
            })
            //快捷写法
            ->where("id|age",">",0)
            //字符串条件查询，绑定写法
            ->whereRaw("email like :email",["email" => $email."%"])
            ->select();

        echo Db::name("tp")
            ->where("nickname","like",$nick."%")
            ->where(function ($query) use ($email){
                $query->where([
                    ["email","like",$email."%"]
                ])
                    ->whereOr("age > 0 and 1=1");
            })
            ->where("id|age",">",0)
            ->whereRaw("email like :email",["email" => $email."%"])
            ->fetchSql()
            ->select();
        dump($back);
    }
}

图 ↓

6.更多快捷方法

视图查询

多表查询，join 的推荐替代方法

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;

class Index extends BaseController
{
    public function index()
    {
        $back = Db::view("tp a","id,nickname")
            ->view("tp1 b","id,email,nickname","a.id = b.id","LEFT")
            ->where("a.id",">","5")
            ->select();

        echo Db::view("tp a","id,nickname")
            ->view("tp1 b","id,email,nickname","a.id = b.id","LEFT")
            ->where("a.id",">","5")
            ->fetchSql()
            ->select();

        dump($back);
    }
}

图 ↓

注意：如果使用数组形式设置别名，如果表名有前缀，需要写前缀
如 think_ 为表前缀，需写成 ['think_tp' => 'a'] 不然报错

子查询

参考资料：TP6 子查询官方文档

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;

class Index extends BaseController
{
    public function index()
    {
        //其实子查询，就是利用TP 构造器返回 sql 语句 而不执行
        echo "fetchSql() 可以用在任何sql构造中，不仅限于 select() ";
        dump(
            Db::name("tp")
                ->where("id",">","5")
                ->fetchSql()
                ->select()
        );

        echo "buildSql() 一般纯用于构造sql 返回，后续不能跟随 select() update() 之类<br>
                并且可以返回到变量中，作为复用，此方法会把 sql 语句用（）包裹起来";
        dump(
            Db::name("tp")
                ->where("id",">","5")
                ->buildSql()
        );

        echo "利用闭包特性，会在sql 构造中，把闭包内容用 （）包裹的特性";
        dump(
            Db::name("tp")
                ->where("id","in",function ($query){
                    $query->name("tp1")
                    ->where("id",">",5)
                    ->field("id");
                })
                ->fetchSql()
                ->select()
        );
    }
}

图 ↓

「谨言慎行」