ThinkPHP5 – 常用数据库查询语句
     发布在:PHP      浏览:46      评论:0 条评论

tp_data 数据表

id name status
3 thinkphp 1
4 thinkphp 1
5 thinkphp 1
6 thinkphp 1
7 77777777777 1
8 thinkphp 1
9 thinkphp 1
10 thinkphp 1
11 thinkphp 1
12 111 127
13 333 127
14 1111 127
15 333 127
16 1111 127
17 3333 127

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);
Responses