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

$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
*/

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

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

$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 = 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%')
*/

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

$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
*/

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