thinkphp笔记-查询表达式where、时间查询、聚合、原生、子查询

查询表达式

比较查询

查询表达式支持大部分常用的SQL 语句，语法格式如下：

where('字段名','查询表达式','查询条件');

在查询数据进行筛选时，我们采用where()方法，比如id=80；

Db::name('user')->where('id', 80)->find();
等价
Db::name('user')->where('id','=',80)->find();

使用<>、>、<、>=、<=可以筛选出各种符合比较值的数据列表（找到不等于19的全部记录）；

Db::name('user')->where('id','<>',19)->select();

区间查询

使用like 表达式进行模糊查询；

$user = Db::name('user')->where('email', 'like', 'xiao%')->select();
// 找到email前面是 xiao开头的项

like 表达式还可以支持数组传递进行模糊查询；

$user = Db::name('user')->where('email', 'like', ['xiao%', 'wu%'])->select();
// SELECT * FROM `tp_user` WHERE (`email` LIKE 'xiao%' AND `email` LIKE 'wu%')

return Db::getlastSql();

如果不传递第四个参数，那么默认是and，所以这里我们给它传入第四个参数or（无所谓大小写）

 $user = Db::name('user')->where('email', 'like', ['xiao%', 'wu%'], 'or')->select();
// SELECT * FROM `tp_user` WHERE (`email` LIKE 'xiao%' OR `email` LIKE 'wu%')

like 表达式具有两个快捷方式whereLike()和whereNoLike()；

 $user = Db::name('user')->whereLike('email', 'xiao%')->select();

Db::name('user')->whereNotLike('email', 'xiao%')->select(); // 反向，全部email中不含有xiao的

between 表达式具有两个快捷方式whereBetween()和whereNotBetween()；

 // 列出id在21~27之间的项 以下均效果等价
$user = Db::name('user')->where('id', 'between', '21, 27')->select();
// 同时也支持数组方式
$user = Db::name('user')->where('id', 'between', [21, 27])->select();
$user = Db::name('user')->whereBetween('id', [21, 27])->select();
$user = Db::name('user')->whereBetween('id', '21, 27')->select();

in 表达式具有两个快捷方式whereIn()和whereNotIn()；

// 以下语句效果等价
$user = Db::name('user')->where('id', 'in', '25, 26,29')->select();
$user = Db::name('user')->where('id', 'in', [25, 26,29])->select();
Db::name('user')->whereIn('id','19,21,29')->select();
Db::name('user')->whereNotIn('id','19,21,29')->select();

null 表达式具有两个快捷方式whereNull()和whereNotNull()；

 // 找到所有的null数据 和 非null数据 以下效果等价
$user = Db::name('user')->where('uid', 'null')->select();
$user = Db::name('user')->where('uid', 'not null')->select();
Db::name('user')->whereNull('uid')->select();
Db::name('user')->whereNotNull('uid')->select();

EXP 查询

假设提供的查询方法没有想要的，那么使用exp 可以自定义字段后的SQL 语句；

exp方法主要就是使用mysql原生的查询语句进行拼装。

比如：Db::name('user')->select(); 等价于 SELECT * FROM `tp_user`

Db::name('user')->where('id','in','19,21,22')->select(); 等价于 SELECT * FROM `tp_user` WHERE `id` IN (19,21,22)

所以使用exp实现的方法就是：

Db::name('user')->where('id', 'exp', 'IN (19,21,22)')->select();
// 等价于
SELECT * FROM `tp_user` WHERE ( `id` in (19,21,22) )

即第二个参数传递exp，然后第三个参数传递一个sql语句

同样的也有一个简单方法

Db::name('user')->whereExp('id', 'IN (19,21,25)')->select();

时间查询

传统方式

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

 // 大于2018-1-1日期的
$user = Db::name('user')->where('create_time', '>', '2018-01-01')->select();

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

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

以及反向区间

 // 反向区间操作
$user = Db::name('user')->where('create_time', 'not between', ['2018-01-01', '2019-12-1'])->select();

快捷方式

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

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

默认的大于>，可以省略（如果表达式是大于某个时间），一般不要省略，语义化更好

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

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

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

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

还有一种快捷方式为：whereBetweenTime()和whereNotBetweenTime()；这种形式就是不用数组，而用字符串的形式。

Db::name('user')->whereBetweenTime('create_time', '2018-01-01', '2019-12-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();

其他查询

比如我要找到两个小时之内新增的数据、两个小时内注册的用户全部列出来，就可以使用这个方法

$user = Db::name('user')->whereTime('create_time', '-2 hours')->select();
// SELECT * FROM `tp_user` WHERE `create_time` >= '2024-04-02 08:14:00'
// 当前时间是2024-04-02 10:14:00 所以sql语句就是大于8：14的时间筛选
return Db::getLastSql();

查询两个时间字段时间有效期的数据，比如会员开始到结束的期间；比如，查询两个字段的之间的有效期，根据会员开始时间和结束时间去查找一下有哪些，在有效期内的会员以及在有效期之内的活动等。

 $user = Db::name('user')->whereBetweenTimeField('create_time', 'update_time')->select();
// SELECT * FROM `tp_user` WHERE `create_time` <= '2024-04-02 10:19:41' AND `update_time` >= '2024-04-02 10:19:41'
return Db::getLastSql();

聚合.原生.子查询

聚合查询

系统提供了一系列的方法，更好的计算所有列整合的数据。

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

 $result = Db::name('user')->count();
return json($result);
// 返回17 代表共有17条数据

此返回的sql语句为：SELECT COUNT(*) AS think_count FROM `tp_user` LIMIT 1

count()可设置指定id，比如有空值(Null)的uid，不会计算数量,即排除值为null的行，统计其余有值行的数量；

$result = Db::name('user')->count('uid');
return json($result);

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

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

使用min()方法，求出所查询数据字段的最小值

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

min、max方法都有第二个参数，如果得到的结果不是数值，则通过第二参数强制转换；比如

 $result = Db::name('user')->min('email'); // 得到的是0 

然后我们传入第二个参数，不要求其强制转换

$result = Db::name('user')->min('email', false); // 得到HUIYE@163.COM

avg()方法，求出所查询数据字段的平均值，sum()方法，求出所查询数据字段的总和；

$result = Db::name('user')->avg('price');
$result = Db::name('user')->sum('price');

子查询

使用fetchSql()方法，可以设置不执行SQL，而返回SQL 语句，默认true；

$result = Db::name('user')->fetchSql(true)->select();
return json($result);
// 结果返回 "SELECT * FROM `tp_user`" 所以它返回的是一个sql语句

如果传入的是false，那么就会执行语句，得到查询结果

 $result = Db::name('user')->fetchSql(false)->select();

$result = Db::name('user')->buildSql(true);
return json($result);
// 得到的结果带括号，方便拼接查询的时候不需要拼装了( SELECT * FROM `tp_user` )

使用buildSql()方法，也是返回SQL 语句，不需要再执行select()，且有括号；

下面来看一个子查询的例子，有如下两张表：

tp_two：

tp_one:

我们要实现根据第one表的id=two表的uid且是男的行。

第一步，求出所有男的uid

 // 求出所有男的uid 返回其sql语句，如果末尾换成->selcet（）则会输出结果
$subQuery = Db::name('two')->field('uid')->where('gender', '男')->buildSql(true);
// 得到 ( SELECT `uid` FROM `tp_two` WHERE  `gender` = '男' )

第二步，找到id在第一步区间内的行

// 这个写法不对
$result = Db::name('one')->where('id', 'in', $subQuery)->select();
return Db::getLastSql();

上面的写法会返回：SELECT * FROM `tp_one` WHERE `id` = ( SELECT `uid` FROM `tp_two` WHERE `gender` = '男' ) 这个语句不对，所以第二个参数不能用In，应该使用exp进行拼接。

$result = Db::name('one')->where('id', 'exp', 'IN ' . $subQuery)->select();
// SELECT * FROM `tp_one` WHERE ( `id` IN ( SELECT `uid` FROM `tp_two` WHERE `gender` = '男' ) )
return Db::getLastSql();

最终得到的结果就是：

[
{
id: 1,
user: "张三"
},
{
id: 3,
user: "王五"
},
{
id: 4,
user: "马七"
}
]

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

// $query 就是数据库链接对象 相当于Db
$result = Db::name('one')->where('id', 'in', function($query) {
    $query->name('two')->field('uid')->where('gender', '男');
})->select();

以上得到的sql语句也是：SELECT * FROM `tp_one` WHERE `id` IN (SELECT `uid` FROM `tp_two` WHERE `gender` = '男')

原生查询

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

$result = Db::query('SELECT * FROM tp_user');
return json($result);

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

$result = Db::execute('UPDATE tp_user SET username="孙武" WHERE id=29');
return json($result);