thinkphp mysql查询_thinkphp5中常用数据库查询语句介绍

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》