thinkphp5中常用数据库查询语句介绍

ThinkPHP  /  管理员 发布于 8年前   448

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      )  ...)*/

聚合查询

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中常用数据库查询语句介绍的详细内容，更多请关注其它相关文章！