TP5 后端小知识点总结(后续会不断继续更新)

一、TP5 模糊查询-多条件-多种查询形式

1、具有相同效果单个字段对应单个查询条件

$where[] = ['title','like',"%" . $keyword . "%"];
$where['title'] = ['like', "%" . $keyword . "%"];
where('title', 'like', "%" . $keyword . "%");

2、具有相同效果单个字段对应多个查询条件

where('title', 'like', ["%" . $keyword1 . "%", "%" . $keyword2 . "%"], 'OR');
$where['title'] = ['like', ["%" . $keyword1 . "%", "%" . $keyword2 . "%"], 'OR'];

3、具有相同效果多个字段对应单个查询条件

$where['nickname|username|title'] = ['like', "%" . $keyword . "%"];
$where = [
	        ['nickname', 'like', "%" . $keyword . "%"],
	        ['username', 'like', "%" . $keyword . "%"],
	        ['title', 'like', "%" . $keyword  . "%"],
	    ];

4、多个字段对应多个查询条件

$where = [
	['nickname', 'like', "%" . $keyword1 . "%"],
	['username', 'like', "%" . $keyword2 . "%"],
	['title', 'like', "%" . $keyword3 . "%"],
];

5、多字段分别从左右两边进行匹配

$where['nickname|username|title'] = ['like', "%" . $keyword];  //从左侧开始匹配查询
$where['nickname|username|title'] = ['like', $keyword . "%"];  //从右侧开始匹配查询

二、TP5 多种运算符查询

条件表达式格式为：$where['字段名']  = ['TP运算符', '操作条件'];

TP运算符	SQL运算符	示例	实际查询条件
eq	=	$where[‘id’] = [‘eq’, 100]	id = 100
neq	=	$where[‘id’] = [‘neq’, 100]	id <> 100
gt	>	$where[‘id’] = [‘gt’, 100]	id > 100
egt	>=	$where[‘id’] = [‘egt’, 100]	id >= 100
lt	<	$where[‘id’] = [‘lt’, 100]	id < 100
elt	<=	$where[‘id’] = [‘elt’, 100]	id <= 100
like	like	$where[‘username’] = [‘like’, ‘%ThinkPHP%’]	username like ‘%ThinkPHP%’
between	between and	$where[‘id’] = [‘between’, ‘1,10’]	id between 1 and 10
not between	not between and	$where[‘id’] = [‘not between’, ‘1,10’]	id not between 1 and 10
in	in	$where[‘id’] = [‘in’, ‘1,2,3,4,5’]	id in(‘1,2,3,4,5’)
not in	not in	$where[‘id’] = [‘not in’, ‘1,2,3,4,5’]	id not in(‘1,2,3,4,5’)
and	and	$where[‘id’] = [[‘gt’, 5], [‘elt’, 10]]	(id > 5) and (id <= 10)
or	or	$where[‘id’] = [[‘elt’, 5], [‘gt’, 10], ‘or’]	(id <= 5) or (id > 10)
exp	综合表达式	$where[‘id’] = [‘exp’, ‘in(4,5,6)’] 等同于 $where[‘id’] = [‘in’, ‘4,5,6’]	id in(4,5,6)

备注：

• ThinkPHP运算符不区分大小写，例如：eq与EQ效果相同。
• between 和 in 支持字符串或者数组写法，如下所示：

$where['id'] = ['in','1,5,8'];  等同于  $where['id'] = ['in', ['1','5','8']];
$where['between'] = ['between','1,5'];  等同于  $where['between'] = ['between', ['1','5']];

• 上表中的 exp 不是一个运算符，而是一个综合表达式以支持更复杂的条件设置。exp 的操作条件不会被当成字符串，可以使用任何 SQL 支持的语法，包括使用函数和字段名称，exp 不仅用于 where 条件，也可以用于数据更新。示例如下：

// exp作为where查询条件
where('id','exp','in(4,5,6)'); 
等效于 where('id','in','4,5,6'); 
等效于 $where['id'] = ['in','4,5,6']; 
等效于 $where['id'] = ['in',['4','5','6']];  

$where['hits'] = ['exp', 'hits + 1'];  // 构建update的数组，文章点击量 +1

• where查询语句还支持其他写法：

where('id', 'eq', 100); 等同于 where(['id' => 100]); 等同于 where('id', 100); 等同于 where('id', '=', 100);

三、TP5 多张表链式查询(join)

1、链式查询

$orderInfo = Db::name('order')
    ->alias('a')
    ->join('order_details b', 'a.id = b.order_id', 'left')
    ->join('address c', 'a.address_id = c.id', 'left')
    ->join('classify d', 'b.classify_id = d.id', 'left')
    ->join('receive e', 'c.receive_id = e.id', 'left')
    ->where(['a.id' => $order_id])
    ->field("a.id as order_id,a.pay_time,from_unixtime(a.create_time, '%Y/%m/%d %H:%i:%s') as create_time,
    b.pickinfo,b.fragile_data,b.downstairs_data,b.images as pick_images,
    c.mobile,c.address,
    d.name as classify_name,
    e.build_name,e.dormitory_data")
    ->find();

备注：

from_unixtime(a.create_time, '%Y/%m/%d %H:%i:%s') as create_time  // SQL转化时间戳

2、聚合查询

// 获取用户数
Db::table('user')->where(['id' => $user_id])->count();
// 根据字段获取用户数            
Db::table('user')->where(['id' => $user_id])->count('username');
// 根据字段获取用户最高分   
Db::table('user')->where(['id' => $user_id])->max('score');
// 根据字段获取用户最低分    
Db::table('user')->where(['id' => $user_id])->min('score');
// 根据字段获取用户平均分   
Db::table('user')->where(['id' => $user_id])->avg('score');
// 根据字段获取用户分数总和
Db::table('user')->where(['id' => $user_id])->sum('score');