增删改查,对应以下四个函数:
select() update() insert() delete()
查询select
select() 返回二维数组,数组的元素为数据表的每行
// 预期SQL: "SELECT * FROM `user` WHERE (`name`='xiaofang')"; $row_list = m('user')->where(['name' => 'xiaofang'])->select(); // 返回二维数组多行,每个元素为一行
find() 返回一维数组
$row = m('user')->where(['name' => 'xiaofang'])->find(); // 返回一行,key为列名的一维数组 // find为查询到的第一行
增加insert
insert($data) :$data 为一维数组 key为field value为值
// 预期SQL: "INSERT INTO `user` (id,name,age) VALUES ('1','xiaofang','18')"; // 数据 $data = ['id'=> 1,'name'=>'xiaofang','age'=>'18']; // 插入函数 $id = m('user')->insert($data); // 查看刚执行的sql,等于$expectSql $sql = m('user')->sql();
insert() 返回值:主键ID
更新update
// 预期SQL: "UPDATE `user` SET `age`='20' WHERE (`id`='1')"; // 执行update函数 $effectRows = m('user')->update(['id'=>1,'age'=>20]); // 默认 id为 where 条件 // 或者这样写 $effectRows = m('user')->where(['id'=>1])->update(['age'=>20]);
update() 返回值:受影响的行数
删除delete
delete()
// 预期SQL: "DELETE FROM `user` WHERE (`id`='1')"; $effectRows = m('user')->where(['id'=>1])->delete();
delete() 返回值:受影响的行数
Where条件高级特性
where in
// 预期SQL: "SELECT * FROM `user` WHERE (`id` IN (1,2) AND `age`='20')"; $where = [ 'id' => ['in',[1,2]], 'age' => 20, ]; $row = m('user')->where($where)->select();
where between
// 预期SQL: "SELECT * FROM `user` WHERE (`id` BETWEEN '1' AND '2')"; $where = [ 'id' => ['between',[1,2]], ]; m('user')->where($where)->select();
where 表达式
// 预期SQL: "SELECT * FROM `user` WHERE (`id` != '1')"; $where = [ 'id' => ['!=',1], // 支持 '/^(=|!=|<|<>|<=|>|>=)$/' ]; m('user')->where($where)->select();
where like
// 预期SQL: "SELECT * FROM `user` WHERE (`name` LIKE 'test%')"; $where = [ 'name' => ['like','test%'], // 支持 like no like ]; m('user')->where($where)->select();
where 多个条件逻辑 OR 、AND
// 预期SQL: "SELECT * FROM `user` WHERE (`id`='1' OR `name` LIKE 'test%')"; $where = [ 'id' => 1, // 支持 like no like 'name' => ['like','test%'], // 支持 like no like '_logic' => 'OR', // 默认 条件连接为 AND ]; m('user')->where($where)->select();
where 嵌套复杂表达式
多个where 条件嵌套
// 预期SQL:"SELECT * FROM `user` WHERE ((`name` LIKE 'zhang%'OR`name` LIKE 'li%') AND ((`id`='1' OR `age` IN (18,20))))"; $where_complex = [ 'id' => 1, 'age' => ['in',[18,20]], '_logic' => 'OR', ]; $where = [ 'name' => ['like', ['zhang%','li%'],'OR'], '_complex' => [ $where_complex ], ]; m('user')->where($where)->select();
直接使用sql语句
sql查询
query()
$expectSql = "SELECT * FROM `user` WHERE (`name`='test')"; m('user')->query($expectSql);
sql执行(含增/删/改)
exec()
$expectSql = "UPDATE `user` set `age`='20' WHERE (`id`='1')"; m('user')->exec($expectSql);
同时插入多行
insert_multi($datas, $chunk_size=1000)
将 $datas 拆分为每一块大小$chunk_size,插入数据库。
// 预期SQL: "INSERT INTO `user` (name,age,company) VALUES ('lisi',21,'baidu'),('zhsa',28,'baidu')"; // 二维数组,一个元素为一行 $datas = [ ['name' => 'lisi', 'age' => 21, 'company' => 'baidu'], ['name' => 'zhsa', 'age' => 28, 'company' => 'tencent'], ]; $id = m('user')->insert_multi($datas); // 返回最后一个插入的ID
统计行数
count() 统计查询的行数
// 预期SQL: "SELECT count(*) as num FROM `user` where `age`=21"; $map = ['age' => 21]; $sum = m('user')->where($map)->count();
设置自增
/** * @param string $field 数据表字段 * @param int $num 自增数 * @return int $count 返回影响的函数 */ set_increase($field, $num)
例子:
// 预期SQL: "UPDATE `user` SET `age`=`age`+1 WHERE (`id`='1')"; $data = m('user')->where(['id'=>1])->set_increase('age',1);
设置递减
/** * @param string $field 数据表字段 * @param int $num 递减数 * @return int $count 返回影响的函数 */ set_decrease($field, $num)
例子:
// 预期SQL: "UPDATE `user` SET `age`=`age`-1 WHERE (`id`='1')"; $data = m('user')->where(['id'=>1])->set_decrease('age',1);
高级特性
多种条件查询 distinct / field / join / having / where / limit/ order/ group ,可以随意组合,选择使用
// 预期SQL: " SELECT DISTINCT id,age,name FROM `user` INNER JOIN company on company.user_id = user.id WHERE (`id`='1') GROUP BY name HAVING id < 100 ORDER BY id desc LIMIT 0,1"; // 框架写法 $rows = m('user') ->distinct("id") ->field("id,age,name") // default * ->join("company on company.user_id = user.id") ->where(['id'=>1]) ->group('name') ->having("id < 100") ->order('id desc') ->limit(1) ->select(); // 获取执行的sql $sql = model('user')->sql(); // 待执行完后返回结果就是 $expectSql