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 ) ...)*/
聚合查询
count
max
min
avg
sum
统计 data 表的数据
$count = db::name('data') -> where('status', 1) -> count();echo $count;// 结果：9
统计 data 表的最大 id
$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);
推荐教程：《tp5》
以上就是thinkphp5中常用数据库查询语句介绍的详细内容。