tp_data 資料表
value()
$name = Db::name('data') -> where('id', 16) -> value('name'); print_r($name); // 获取 tp_data 数据表中 id = 16，name 字段的值,并打印 // 结果：1111 /** 原生sql语句 >Prepare SELECT `name` FROM `tp_data` WHERE `id` = ? LIMIT 1 >Execute SELECT `name` FROM `tp_data` WHERE `id` = 16 LIMIT 1 */
column()
取得一列滿足條件的資料
$list = Db::name('data) -> where('status', 1) -> column('name'); print_r($list); // 从 tp_data 数据表获取一列 status = 1 的 name 字段值 /** 结果： Array( [0] => thinkphp [1] => thinkphp [2] => thinkphp [3] => thinkphp [4] => 7777777777 [5] => thinkphp [6] => thinkphp [7] => thinkphp [8] => thinkphp ) */
取得一列滿足條件的數據，並以id值為鍵名
$list = Db::name('data) -> where('status', 1) -> column('name', 'id'); print_r($list); // 从 tp_data 数据表获取一列 status=1 的 name 字段值集合 /** 结果： Array( [3] => thinkphp [4] => thinkphp [5] => thinkphp [6] => thinkphp [7] => 7777777777 [8] => thinkphp [9] => thinkphp [10] => thinkphp [11] => thinkphp ) */
##取得以id為鍵名的資料集
$list = Db::name('data')
-> where('status', 1)
-> column('*', 'id');
print_r($list);
// 从 tp_data 数据表获取一列 status=1 的数据集
/** 结果：
Array(
[3] => Array(
[id] => 3
[name] => thinkphp
[status] => 1
)
[4] => Array(
[id] => 4
[name] => thinkphp
[status] => 1
)
[5] => Array(
[id] => 5
[name] => thinkphp
[status] => 1
)
...
)
*/聚合查詢
#countmaxminavgsum統計數據 資料表的資料
$count = Db::name('data') -> where('status', 1) -> count(); echo $count; // 结果：9
$max = Db::name('data') -> where('status', 1) -> max('id); echo $max; // 结果：11
$result = Db::name('data')
-> where("id > :id and name like :name",
[
'id' => 10,
'name' => "%php%"
])
-> select();
print_r($result);
/** 结果：
Array(
[0] => Array(
[id] => 11
[name] => thinkphp
[status] => 1
)
)
*/
/** 原生sql语句：
>Prepare SELECT * FROM `tp_data` WHERE (id > ? and name like ?)
>Execute SELECT * FROM `tp_data` WHERE (id > '10' and name like '%php%')
*/日期查詢
日期類型int，時間戳格式查詢時間大於2016-1-1 的資料
$result = Db::name('users') -> whereTime('reg_time', '>', '2016-1-1') -> select(); print_r($result); /** 原生sql语句： >Prepare SELECT * FROM `tp_users` WHERE `reg_time` > ? >Execute SELECT * FROM `tp_users` WHERE `reg_time` > 1451577600 */
查詢本週
$result = Db::name('users') -> whereTime('reg_time', '>', 'this week') -> select(); print_r($result); // 从本周星期一开始
查詢最近兩天新增的資料
$result = Db::name('users') -> whereTime('reg_time', '>', '-2 days') -> select(); print_r($result);
查詢建立時間在2016 -1-1 ~ 2017-7-1 的資料
$result = Db::name('users') -> whereTime('reg_time', 'between', ['2016-1-1', '2017-7-1']) -> select(); print_r($result); /** 原生sql语句： >Prepare SELECT * FROM `tp_users` WHERE `reg_time` BETWEEN ? AND ? >Execute SELECT * FROM `tp_users` WHERE `reg_time` BETWEEN 1451577600 AND 1483200000 */
#查詢今天的資料
昨天：yesterday本週： week上週：last week$result = Db::name('users') -> whereTime('reg_time', 'today') -> select(); print_r($result);
分塊查詢#
Db::name('data')
-> where('status', '>', 0)
-> chunk(2, function($list) {
foreach($list as $data) {
//处理2条记录
}
});
/** 原生sql语句：
>Prepare SELECT * FROM `tp_data` WHERE `status` > ? ORDER BY `id` asc LIMIT 2
>Execute SELECT * FROM `tp_data` WHERE `status` > 0 ORDER BY `id` asc LIMIT 2
>Close stmt
>Prepare SELECT * FROM `tp_data` WHERE `status` > ? AND `id` > ? ORDER BY `id` asc LIMIT 2
>Execute SELECT * FROM `tp_data` WHERE `status` > 0 AND `id` > 4 ORDER BY `id` asc LIMIT 2
>Close stmt
>Prepare SELECT * FROM `tp_data` WHERE `status` > ? AND `id` > ? ORDER BY `id` asc LIMIT 2
>Execute SELECT * FROM `tp_data` WHERE `status` > 0 AND `id` > 6 ORDER BY `id` asc LIMIT 2
>Close stmt
...
>Prepare SELECT * FROM `tp_data` WHERE `status` > ? AND `id` > ? ORDER BY `id` asc LIMIT 2
>Execute SELECT * FROM `tp_data` WHERE `status` > 0 AND `id` > 16 ORDER BY `id` asc LIMIT 2
>Close stmt
>Prepare SELECT * FROM `tp_data` WHERE `status` > ? AND `id` > ? ORDER BY `id` asc LIMIT 2
>Execute SELECT * FROM `tp_data` WHERE `status` > 0 AND `id` > 17 ORDER BY `id` asc LIMIT 2
>Close stmt
*/改進
$p = 0;
do {
$result = Db::name('data') -> limit($p, 2) -> select();
$p += 2;
//处理数据
} while(count($result) > 0);以上是thinkphp5常用資料庫查詢語句介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！
