thinkphp笔记-数据库高级查询、快捷查询

高级查询

使用|(OR)或&(AND)来实现where 条件的高级查询，where 支持多个连缀；

$user = Db::name('user')
                    ->where('username|email', 'like', '%xiao%')
                    ->select();

上面这个对应的sql语句：SELECT * FROM `tp_user` WHERE ( `username` LIKE '%xiao%' OR `email` LIKE '%xiao%' )

所以username|email的意思就是username或者email里面含有xiao的行。

因为支持连缀，所以还可以再添加一个where

$user = Db::name('user')
                    ->where('username|email', 'like', '%xiao%')
                    ->where('price&uid', '>', 0)
                    ->select();

上面这个又添加了price和uid要大于0，对应的sql语句：SELECT * FROM `tp_user` WHERE ( `username` LIKE '%xiao%' OR `email` LIKE '%xiao%' ) AND ( `price` > '0' AND `uid` > 0 )，多一个where会自动加括号。

关联数组方式，可以在where 进行多个字段进行查询；

$user = Db::name('user')
                    ->where([
                        ['id', '>', 0],
                        ['status', '>=', 1],
                        ['price', '>=', 80],
                        ['email', 'like', '%xiao%']
                    ])
                    ->select();

对应生成的sql语句：SELECT * FROM `tp_user` WHERE `id` > 0 AND `status` >= 1 AND `price` >= '80' AND `email` LIKE '%xiao%'

因为只有一个where，所以没有添加括号。且多个数组条件是处于并列关系。

根据之前的课程中，条件字符串复杂组装，比如使用exp 了，就使用raw()方法；比如使用exp，

Db::name('user')
                    ->where([
                        ['id', '>', 0],
                        ['status', '>=', 1],
                        ['price', 'exp', ‘>=80’], // 这样写会有错误
                        ['email', 'like', '%xiao%']
                    ])
                    ->select();

正确写法应该是使用Db::raw（‘自定义的语句’）

Db::name('user')
                    ->where([
                        ['id', '>', 0],
                        ['status', '>=', 1],
                        ['price', 'exp', Db::raw('>=80')], // 正确
                        ['email', 'like', '%xiao%']
                    ])
                    ->select();

SELECT * FROM `tp_user` WHERE `id` > 0 AND `status` >= 1 AND ( `price` >=80 ) AND `email` LIKE '%xiao%'

如果有多个where，并且where 条件是分离的$map，而$map 本身有多个条件；

$map = [
                ['id', '>', 0],
                ['price', '>=', 80],
                ['email', 'like', '%xiao%']
];
$user = Db::name('user')->where($map)->select();

SELECT * FROM `tp_user` WHERE `id` > 0 AND `price` >= '80' AND `email` LIKE '%xiao%'

这时候如果再添加一个where，加上之前没有添加的status条件

$map = [
                ['id', '>', 0],
                ['price', '>=', 80],
                ['email', 'like', '%xiao%']
];
 $user = Db::name('user')
                    ->where($map)
                    ->where('status', 1)
                    ->select();

这时候返回的sql语句：SELECT * FROM `tp_user` WHERE `id` > 0 AND `price` >= '80' AND `email` LIKE '%xiao%' AND `status` = 1

这样的话是所有的条件都是平行的，email与status都处于平级关系，而我要的是$map的结果与status平级

$map = [
    ['id', '>', 0],
    ['price', '>=', 80],
    ['email', 'like', '%xiao%']
];
$user = Db::name('user')
        ->where([$map])
        ->where('status', 1)
        ->select();

SQL：SELECT * FROM `tp_user` WHERE ( `id` > 0 AND `price` >= '80' AND `email` LIKE '%xiao%' ) AND `status` = 1

用[$map]，$map 条件如果需要先执行出结果，再和后续条件判断，也就是加上括号，那么，需要对这个$map 变量，再加上一个中括号处理优先级；

如果，条件中有多次出现一个字段，并且需要OR 来左右筛选，可以用whereOr；

$map1 = [
    ['username', 'like', '%小%'],
    ['email', 'like', '%163%']
];
$map2 = [
    ['username', 'like', '%孙%'],
    ['email', 'like', '%.com%']
];
$user = Db::name('user')
->where([$map1, $map2])
->select();

SELECT * FROM `tp_user` WHERE ( `username` LIKE '%小%' AND `email` LIKE '%163%' ) AND ( `username` LIKE '%孙%' AND `email` LIKE '%.com%' )

默认的where之间是用AND链接，如果需要使用OR链接，则改为whereOr

$user = Db::name('user')
->whereOr([$map1, $map2])
->select();

这时的sql语句：SELECT * FROM `tp_user` WHERE ( `username` LIKE '%小%' AND `email` LIKE '%163%' ) OR ( `username` LIKE '%孙%' AND `email` LIKE '%.com%' )

闭包查询可以连缀，会自动加上括号，更清晰，如果是OR，请用whereOR()；

比如一个简单的闭包查询例子：

// 闭包查询 $query就是user对象
$user = Db::name('user')->where(function($query) {
    $query->where('id', '>', 0);
})->select();

所谓的闭包连缀，就是再闭包后面再跟一个where闭包

$user = Db::name('user')->where(function($query) {
    $query->where('id', '>', 0);
})->where(function($query) {
    $query->where('username', 'like', '%小%');
})
->select();

SQL：SELECT * FROM `tp_user` WHERE ( `id` > 0 ) AND ( `username` LIKE '%小%' )

如果闭包之间不想用AND，呢么直接使用whereOr即可。

$user = Db::name('user')->where(function($query) {
    $query->where('id', '>', 0);
})->whereOr(function($query) {
    $query->where('username', 'like', '%小%');
})
->select();

SELECT * FROM `tp_user` WHERE ( `id` > 0 ) OR ( `username` LIKE '%小%' )

对于比较复杂或你不知道如何拼装SQL 条件，那么就直接使用whereRaw()即可；

$user = Db::name('user')
->whereRaw('(username LIKE "%小%" AND status = 1) OR id>0')
->select();

SELECT * FROM `tp_user` WHERE ( (username LIKE "%小%" AND status = 1) OR id>0 )

对于复杂的原生SQL语句查询，建议直接使用whereRaw

whereRaw()方式也支持参数绑定操作，与PDO的预处理配合使用。

$user = Db::name('user')
->whereRaw(
    '(username LIKE :username AND status = :status) OR id>:id',
    ['username' => '%小%', 'status' => 1, 'id'=>0]
)
->select();

SELECT * FROM `tp_user` WHERE ( (username LIKE "%小%" AND status = 1) OR id>0 )

最终结果的SQL语句与上面的一样，这里只是改为了参数绑定。

快捷查询

系统封装了很多where 方法的快捷方式，之前学习了一些，全部如下：

whereColumn()方法，比较两个字段的值，符合的就筛选出来

// 找出update>=create的所有行
$user = Db::name('user')
           ->whereColumn('update_time', '>=', 'create_time')
            ->select();
 return json($user);

如果是等于，第二个参数传”=“，>whereColumn('update_time', '=', 'create_time')或者就不需要传递第二个参数了。

$user = Db::name('user')
            ->whereColumn('update_time', 'create_time')
            ->select();
return Db::getLastSql();

SELECT * FROM `tp_user` WHERE ( `update_time` = `create_time` )，这个主要用于对比出哪些用户的账号与email是相同的啊之类的查询。

系统还针对字段查询提供了几个方便查询的快捷方式

whereFieldName()方法，查询某个字段的值，注意FileName 是字段名，比如我要查询email=xiaoxin@163.com的行。

 $user = Db::name('user')->whereEmail('xiaoxin@163.com')->select();

如果不填参数

$user = Db::name('user')->whereEmail()->select();

SELECT * FROM `tp_user` WHERE `email` IS NULL，得到的结果就是空数组。

$user = Db::name('user')->whereUsername('蜡笔小新')->find();

这里要特别注意，因为字段名就是username，所以where+字段名（首字母要大写）= whereUsername，这里如果我改成：

$user = Db::name('user')->whereUserName('蜡笔小新')->find();

那么就会报错：Column not found: 1054 Unknown column 'user_name' in 'where clause'

所以，如果字段名中有下划线的，比如create_time，那么查询的时候使用驼峰式写法，即whereCreateTime：

 Db::name('user')->whereCreateTime('2016-06-27 16:45:26')->find();

getByFieldName()方法，查询某个字段的值，注意只能查询一条，不需要find()；

 Db::name('user')->getByEmail('xiaoxin@163.com');

SELECT * FROM `tp_user` WHERE `email` = 'xiaoxin@163.com' LIMIT 1

这个方法只会查询到一条数据！

getFieldByFieldName()方法，通过查询得到某个指定字段的单一值，即通过一个字段找到另一个字段的值，而不是整条信息。也只返回1行。

getFieldBy固定+字段名（首字母大写）

// 第一个参数是要查找的字段内容，第二个参数是要显示的字段
// 这里是查找email字段=xiaoxin@163.com的行中的username字段的值
$user = Db::name('user')->getFieldByEmail('xiaoxin@163.com', 'username');

结果：只有字符串：蜡笔小新

SQL：SELECT `username` FROM `tp_user` WHERE `email` = 'xiaoxin@163.com' LIMIT 1

其他补充

when()可以通过条件判断，执行闭包里的分支查询，不是链式查询的那种合并。

$user = Db::name('user')->when(
    true,
    function($query) {
        $query->where('id', '>', 0);
    },
    function($query) {
        $query->where('username', 'like', '%小%');
    }
)->select();

 第一个参数是布尔值，决定执行第几个闭包，第一个参数是第一个匿名函数，第二个参数是第二个匿名函数。

如果第一个参数为true，那么执行第一个参数的匿名函数，如上例，执行语句如下：

SELECT * FROM `tp_user` WHERE `id` > 0

如果改为false:

Db::name('user')->when(
    false,
    function($query) {
        $query->where('id', '>', 0);
    },
    function($query) {
        $query->where('username', 'like', '%小%');
    }
)->select();

执行的SQL为：SELECT * FROM `tp_user` WHERE `username` LIKE '%小%'，即为第二个匿名函数。

即在执行条件判断的时候，根据不同条件判断执行不同SQL的时候可以使用这个方法。力图减少代码中的If-else。