【ThinkPHP6.x框架】(5)数据库进阶1（高级查询）

时间查询

传统方式

        可以使用>、<、>=、<=来筛选匹配时间的数据；

Db::name('user')->where('create_time', '>', '2018-1-1')->select();

        可以使用  between关键字来设置时间的区间；

Db::name('user')->where('create_time', 'between', ['2018-1-1',
                        '2019-12-31'])->select();
Db::name('user')->where('create_time', 'not between', ['2018-1-1',
                        '2019-12-31'])->select();

快捷方式

        时间查询的快捷方法为  whereTime()，直接使用>、<、>=、<=；

Db::name('user')->whereTime('create_time', '>', '2018-1-1')->select();

        快捷方式也可以使用  between和  not between；

Db::name('user')->whereBetween('create_time', ['2018-1-1',
                               '2019-12-31'])->select();

        还有一种快捷方式为：whereBetweenTime()和  whereNotBetweenTime()；

Db::name('user')->whereBetweenTime('create_time', '2018-1-1',
                                   '2019-12-31')->select();

        默认的大于>，可以省略；

Db::name('user')->whereTime('create_time', '2018-1-1')->select();

固定查询

        使用  whereYear查询今年的数据、去年的数据和某一年的数据；

Db::name('user')->whereYear('create_time')->select();
Db::name('user')->whereYear('create_time', 'last year')->select(); 
Db::name('user')->whereYear('create_time', '2016')->select();

        使用  whereMonth查询当月的数据、上月的数据和某一个月的数据；

Db::name('user')->whereMonth('create_time')->select();
Db::name('user')->whereMonth('create_time', 'last month')->select(); 
Db::name('user')->whereMonth('create_time', '2016-6')->select();

        使用  whereDay查询今天的数据、昨天的数据和某一个天的数据；

Db::name('user')->whereDay('create_time')->select();
Db::name('user')->whereDay('create_time', 'last day')->select(); 
Db::name('user')->whereDay('create_time', '2016-6-27')->select();

其它查询

        查询指定时间的数据，比如两小时内的；

Db::name('user')->whereTime('create_time', '-2 hours')->select();

        查询两个时间字段时间有效期的数据，比如会员开始到结束的期间；

Db::name('user')->whereBetweenTimeField('start_time',
                                        'end_time')->select();

聚合查询

注意：以下的方法不用写select()和find()

1. 使用  count()方法，可以求出所查询数据的数量；

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

2. count()可设置指定  id，比如有空值(Null)的  uid，不会计算数量；

Db::name('user')->count('uid');

3. 使用  max()方法，求出所查询数据字段的最大值；

Db::name('user')->max('price');

4. 如果  max()方法，求出的值不是数值，则通过第二参数强制转换；

Db::name('user')->max('price', false);

5. 使用  min()方法，求出所查询数据字段的最小值，也可以强制转换；

 Db::name('user')->min('price');

6. 使用  avg()方法，求出所查询数据字段的平均值；

 Db::name('user')->avg('price');

7. 使用  sum()方法，求出所查询数据字段的总和；

Db::name('user')->sum('price');

子查询

one

two

 1. 使用  fetchSql()方法，可以设置不执行  SQL，而返回  SQL语句，默认  true（false为执行数据）；

Db::name('user')->fetchSql(true)->select();

2. 使用  buildSql()方法，也是返回  SQL语句，不需要再执行select()，且有括号（参数为false则返回的结果不带括号）；

Db::name('user')->buildSql(true);

3. 结合以上方法，我们实现一个子查询；

$subQuery = Db::name('two')->field('uid')->where('gender', '男')->buildSql(true);
$result = Db::name('one')->where('id','exp', 'IN '.$subQuery)->select();

        首先我们可以输出$subQuery的结果：

( SELECT `uid` FROM `tp_two` WHERE `gender` = '男' )

        如果第二行写为如下的形式，输出sql语句，我们会发现其实并没有实现'in'：

$result = Db::name('one')->where('id','in',$subQuery)->select();

        其对应的sql语句为：

 "SELECT * FROM `tp_one` WHERE `id` = ( SELECT `uid` FROM `tp_two` WHERE `gender` = '男' )"

        正确的sql语句应为下面所示：

( SELECT * FROM `tp_one` WHERE ( `id` IN ( SELECT `uid` FROM `tp_two` WHERE `gender` = '男' ) ) )

4. 使用闭包的方式执行子查询； 

$result = Db::name('one')->where('id', 'in', function ($query) 
{ 
    $query->name('two')->where('gender', '男')->field('uid');
})->select();

原生查询

1. 使用  query()方法，进行原生  SQL查询，适用于读取操作，SQL错误返回  false；

Db::query('select * from tp_user');

2. 使用  execute方法，进行原生  SQL更新写入等，SQL错误返回  false；

Db::execute('update tp_user set username="孙悟空" where id=29');