TP5 数据库操作方法集锦(超全)

查询一个数据使用:

// table方法必须指定完整的数据表名
Db::table('think_user')->where('id',1)->find();

查询某个字段的值可以用

// 返回某个字段的值
Db::table('think_user')->where('id',1)->value('name');

查询某一列的值可以用

// 返回数组
Db::table('think_user')->where('status',1)->column('name');
// 指定索引
Db::table('think_user')->where('status',1)->column('name','id');

添加一条数据

// 使用 Db 类的 insert 方法向数据库提交数据
$data = ['foo' => 'bar', 'bar' => 'foo'];
Db::table('think_user')->insert($data);
// 如果你在database.PHP配置文件中配置了数据库前缀(prefix),那么可以直接使用 Db 类的 name 方法提交数据
Db::name('user')->insert($data);

添加多条数据

//添加多条数据直接向 Db 类的 insertAll 方法传入需要添加的数据即可
$data = [
    ['foo' => 'bar', 'bar' => 'foo'],
    ['foo' => 'bar1', 'bar' => 'foo1'],
    ['foo' => 'bar2', 'bar' => 'foo2']
];
Db::name('user')->insertAll($data);

更新数据表中的数据

Db::table('think_user')
    ->where('id', 1)
    ->update(['name' => 'thinkphp']);
如果数据中包含主键,可以直接使用:
Db::table('think_user')
    ->update(['name' => 'thinkphp','id'=>1]);

更新某个字段的值:

Db::table('think_user')
    ->where('id',1)
    ->setField('name', 'thinkphp');

删除数据表中的数据

// 根据主键删除
Db::table('think_user')->delete(1);
Db::table('think_user')->delete([1,2,3]);
// 条件删除    
Db::table('think_user')->where('id',1)->delete();
Db::table('think_user')->where('id','<',10)->delete();

where语句

Db::table('think_user')
    ->where('id','>',1)
    ->where('name','thinkphp')
    ->select(); 

最简单的数组查询方式如下:
$map['name'] = 'thinkphp';
$map['status'] = 1;
// 把查询条件传入查询方法
Db::table('think_user')->where($map)->select(); 

表达式查询
可以在数组条件中使用查询表达式,例如:
$map['id']  = ['>',1];
$map['mail']  = ['like','%thinkphp@qq.com%'];
Db::table('think_user')->where($map)->select(); 

字符串条件
使用字符串条件直接查询和操作,例如:
Db::table('think_user')->where('type=1 AND status=1')->select(); 

alias

alias用于设置当前数据表的别名,便于使用其他的连贯操作例如join方法等。
Db::table('think_user')->alias('a')->join('__DEPT__ b ','b.user_id= a.id')->select();
最终生成的sql语句类似于:
SELECT * FROM think_user a INNER JOIN think_dept b ON b.user_id= a.id
v5.0.2+版本开始,可以传入数组批量设置数据表以及别名,例如:
Db::table('think_user')->alias(['think_user'=>'user','think_dept'=>'dept'])->join('think_dept','dept.user_id= user.id')->select();
最终生成的SQL语句类似于:
SELECT * FROM think_user user INNER JOIN think_dept dept ON dept.user_id= user.id

用于查询指定字段field

在查询操作中field方法是使用最频繁的。
Db::table('think_user')->field('id,title,content')->select();
可以给某个字段设置别名,例如:
Db::table('think_user')->field('id,nickname as name')->select();
可以在field方法中直接使用函数,例如:
Db::table('think_user')->field('id,SUM(score)')->select();
字段排除
如果我希望获取排除数据表中的content字段(文本字段的值非常耗内存)之外的所有字段值,我们就可以使用field方法的排除功能,例如下面的方式就可以实现所说的功能:
Db::table('think_user')->field('content',true)->select();
则表示获取除了content之外的所有字段,要排除更多的字段也可以:
Db::table('think_user')->field('user_id,content',true)->select();
//或者用
Db::table('think_user')->field(['user_id','content'],true)->select();

order方法属于模型的连贯操作方法之一,用于对操作的结果排序。

Db::table('think_user')->where('status=1')->order('id desc')->limit(5)->select();
注意:连贯操作方法没有顺序,可以在select方法调用之前随便改变调用顺序。
支持对多个字段的排序,例如:
Db::table('think_user')->where('status=1')->order('id desc,status')->limit(5)->select();
如果没有指定desc或者asc排序规则的话,默认为asc。
如果你的字段和mysql关键字有冲突,那么建议采用数组方式调用,例如:
Db::table('think_user')->where('status=1')->order(['order','id'=>'desc'])->limit(5)->select(); 

limit方法也是模型类的连贯操作方法之一,主要用于指定查询和操作的数量,特别在分页查询的时候使用较多。thinkphp的limit方法可以兼容所有的数据库驱动类的。

限制结果数量
例如获取满足要求的10个用户,如下调用即可:
Db::table('think_user')
    ->where('status=1')
    ->field('id,name')
    ->limit(10)
    ->select();
limit方法也可以用于写操作,例如更新满足要求的3条数据:
Db::table('think_user')
->where('score=100')
->limit(3)
->update(['level'=>'A']);
分页查询
用于文章分页查询是limit方法比较常用的场合,例如:
Db::table('think_article')->limit('10,25')->select();
表示查询文章数据,从第10行开始的25条数据(可能还取决于where条件和order排序的影响 这个暂且不提)。
你也可以这样使用,作用是一样的:
Db::table('think_article')->limit(10,25)->select();
对于大数据表,尽量使用limit限制查询结果,否则会导致很大的内存开销和性能问题。

page

利用扩展类库中的分页类Page可以自动计算出每个分页的limit参数,但是如果要自己写就比较费力了,如果用page方法来写则简单多了,例如:
// 查询第一页数据
Db::table('think_article')->page('1,10')->select(); 
// 查询第二页数据
Db::table('think_article')->page('2,10')->select(); 
显而易见的是,使用page方法你不需要计算每个分页数据的起始位置,page方法内部会自动计算。
和limit方法一样,page方法也支持2个参数的写法,例如:
Db::table('think_article')->page(1,10)->select();
// 和下面的用法等效
Db::table('think_article')->page('1,10')->select();
page方法还可以和limit方法配合使用,例如:
Db::table('think_article')->limit(25)->page(3)->select();
当page方法只有一个值传入的时候,表示第几页,而limit方法则用于设置每页显示的数量,也就是说上面的写法等同于:
Db::table('think_article')->page('3,25')->select(); 

group

GROUP方法也是连贯操作方法之一,通常用于结合合计函数,根据一个或多个列对结果集进行分组 。
group方法只有一个参数,并且只能使用字符串。
例如,我们都查询结果按照用户id进行分组统计:
Db::table('think_user')
    ->field('user_id,username,max(score)')
    ->group('user_id')
    ->select();
生成的sql语句是:
SELECT user_id,username,max(score) FROM think_score GROUP BY user_id
也支持对多个字段进行分组,例如:
Db::table('think_user')
    ->field('user_id,test_time,username,max(score)')
    ->group('user_id,test_time')
    ->select();
生成的SQL语句是:
SELECT user_id,test_time,username,max(score) FROM think_score GROUP BY user_id,test_time

having

HAVING方法也是连贯操作之一,用于配合group方法完成从分组的结果中筛选(通常是聚合条件)数据。
having方法只有一个参数,并且只能使用字符串,例如:
Db::table('think_user')
    ->field('username,max(score)')
    ->group('user_id')
    ->having('count(test_time)>3')
    ->select(); 
生成的sql语句是:
SELECT username,max(score) FROM think_score GROUP BY user_id HAVING count(test_time)>3

join查询

Db::table('think_artist')
->alias('a')
->join('think_work w','a.id = w.artist_id')
->join('think_card c','a.card_id = c.id')
->select();
Db::table('think_artist')
->alias('a')
->join('__WORK__ w','a.id = w.artist_id')
->join('__CARD__ c','a.card_id = c.id')
->select();
$join = [
    ['think_work w','a.id=w.artist_id'],
    ['think_card c','a.card_id=c.id'],
];
Db::table('think_user')->alias('a')->join($join)->select();
以上三种写法的效果一样,__WORK__和 __CARD__在最终解析的时候会转换为 think_work和 think_card。注意:'_表名_'这种方式中间的表名需要用大写
如果不想使用别名,后面的条件就要使用表全名,可以使用下面这种方式
Db::table('think_user')->join('__WORK__','__ARTIST__.id = __WORK__.artist_id')->select();

UNION操作用于合并两个或多个 SELECT 语句的结果集。

Db::field('name')
      ->table('think_user_0')
      ->union('SELECT name FROM think_user_1')
      ->union('SELECT name FROM think_user_2')
      ->select();
Db::field('name')
      ->table('think_user_0')
      ->union(['SELECT name FROM think_user_1','SELECT name FROM think_user_2'])
      ->select();
支持UNION ALL 操作,例如:
Db::field('name')
      ->table('think_user_0')
      ->union('SELECT name FROM think_user_1',true)
      ->union('SELECT name FROM think_user_2',true)
      ->select();
或者
Db::field('name')
      ->table('think_user_0')
      ->union(['SELECT name FROM think_user_1','SELECT name FROM think_user_2'],true)
      ->select();
每个union方法相当于一个独立的SELECT语句。
注意:UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。

distinct

DISTINCT 方法用于返回唯一不同的值 。
以下代码会返回user_login字段不同的数据
Db::table('think_user')->distinct(true)->field('user_login')->select();
生成的sql语句是: SELECT DISTINCT user_login FROM think_user
返回以下数组
array(2) {
  [0] => array(1) {
    ["user_login"] => string(7) "chunice"
  }
  [1] => array(1) {
    ["user_login"] => string(5) "admin"
  }
}
distinct方法的参数是一个布尔值。

lock

Lock方法是用于数据库的锁机制,如果在查询或者执行操作的时候使用:
lock(true);
就会自动在生成的sql语句最后加上 FOR UPDATE或者FOR UPDATE NOWAIT(oracle数据库)。

cache

cache方法用于查询缓存操作,也是连贯操作方法之一。
cache可以用于select、find、value和column方法,以及其衍生方法,使用cache方法后,在缓存有效期之内不会再次进行数据库查询操作,而是直接获取缓存中的数据,关于数据缓存的类型和设置可以参考缓存部分。
下面举例说明,例如,我们对find方法使用cache方法如下:
Db::table('think_user')->where('id=5')->cache(true)->find();
第一次查询结果会被缓存,第二次查询相同的数据的时候就会直接返回缓存中的内容,而不需要再次进行数据库查询操作。
默认情况下, 缓存有效期是由默认的缓存配置参数决定的,但cache方法可以单独指定,例如:
Db::table('think_user')->cache(true,60)->find();
// 或者使用下面的方式 是等效的
Db::table('think_user')->cache(60)->find();
表示对查询结果的缓存有效期60秒。
当你删除或者更新数据的时候,可以使用cache方法手动更新(清除)缓存,例如:
Db::table('think_user')->cache('user_data')->select([1,3,5]);
Db::table('think_user')->cache('user_data')->update(['id'=>1,'name'=>'thinkphp']);
Db::table('think_user')->cache('user_data')->select([1,5]);
最后查询的数据不会受第一条查询缓存的影响,确保查询和更新或者删除使用相同的缓存标识才能自动清除缓存。
如果使用find方法并且使用主键查询的情况,不需要指定缓存标识,会自动清理缓存,例如:
Db::table('think_user')->cache(true)->find(1);
Db::table('think_user')->update(['id'=>1,'name'=>'thinkphp']);
Db::table('think_user')->cache(true)->find(1);
最后查询的数据会是更新后的数据。

comment

COMMENT方法 用于在生成的sql语句中添加注释内容,例如:
Db::table('think_score')->comment('查询考试前十名分数')
    ->field('username,score')
    ->limit(10)
    ->order('score desc')
    ->select();
最终生成的SQL语句是:
SELECT username,score FROM think_score ORDER BY score desc LIMIT 10 /* 查询考试前十名分数 */

fetchSql

fetchSql用于直接返回SQL而不是执行查询,适用于任何的CURD操作方法。 例如:
$result = Db::table('think_user')->fetchSql(true)->find(1);
输出result结果为: SELECT * FROM think_user where id = 1

force

force 方法用于数据集的强制索引操作,例如:
Db::table('think_user')->force('user')->select();
对查询强制使用user索引,user必须是数据表实际创建的索引名称。

bind

bind方法用于手动参数绑定,大多数情况,无需进行手动绑定,系统会在查询和写入数据的时候自动使用参数绑定。
bind方法用法如下:
// 用于查询
Db::table('think_user')
->where('id',':id')
->where('name',':name')
->bind(['id'=>[10,\PDO::PARAM_INT],'name'=>'thinkphp'])
->select();
// 用于写入
Db::table('think_user')
->bind(['id'=>[10,\PDO::PARAM_INT],'email'=>'thinkphp@qq.com','name'=>'thinkphp'])
->where('id',':id')
->update(['name'=>':name','email'=>':email');

partition

partition 方法用于是数据库水平分表
partition($data, $field, $rule);
// $data 分表字段的数据
// $field 分表字段的名称
// $rule 分表规则
注意:不要使用任何 SQL 语句中会出现的关键字当表名、字段名,例如 order 等。会导致数据模型拼装 SQL 语句语法错误。
partition 方法用法如下:
// 用于写入
$data = [
    'user_id'   => 110,
    'user_name' => 'think'
];

$rule = [
    'type' => 'mod', // 分表方式
    'num'  => 10     // 分表数量
];

Db::name('log')
    ->partition(['user_id' => 110], "user_id", $rule)
    ->insert($data);

// 用于查询
Db::name('log')
    ->partition(['user_id' => 110], "user_id", $rule)
    ->where(['user_id' => 110])
    ->select();

strict

strict方法用于设置是否严格检查字段名,用法如下:
// 关闭字段严格检查
Db::name('user')
    ->strict(false)
    ->insert($data);
注意,系统默认值是由数据库配置参数fields_strict决定,因此修改数据库配置参数可以进行全局的严格检查配置,如下:
// 关闭严格检查字段是否存在
'fields_strict'  => false,
如果开启字段严格检查的话,在更新和写入数据库的时候,一旦存在非数据表字段的值,则会抛出异常。

failException

failException设置查询数据为空时是否需要抛出异常,如果不传入任何参数,默认为开启,用于select和find方法,例如:
// 数据不存在的话直接抛出异常
Db:name('blog')->where(['status' => 1])->failException()->select();
// 数据不存在返回空数组 不抛异常
Db:name('blog')->where(['status' => 1])->failException(false)->select();
或者可以使用更方便的查空报错
// 查询多条
Db:name('blog')->where(['status' => 1])->selectOrFail();
// 查询单条
Db:name('blog')->where(['status' => 1])->findOrFail();

聚合查询

count	统计数量,参数是要统计的字段名(可选)
max	获取最大值,参数是要统计的字段名(必须)
min	获取最小值,参数是要统计的字段名(必须)
avg	获取平均值,参数是要统计的字段名(必须)
sum	获取总分,参数是要统计的字段名(必须)

获取用户数:
Db::table('think_user')->count();
// 助手函数
db('user')->count();
或者根据字段统计:
Db::table('think_user')->count('id');
// 助手函数
db('user')->count('id');
获取用户的最大积分:
Db::table('think_user')->max('score');
// 助手函数
db('user')->max('score');
获取积分大于0的用户的最小积分:
Db::table('think_user')->where('score>0')->min('score');
// 助手函数
db('user')->where('score>0')->min('score');
获取用户的平均积分:
Db::table('think_user')->avg('score');
// 助手函数
db('user')->avg('score');
统计用户的总成绩:
Db::table('think_user')->sum('score');
// 助手函数
db('user')->sum('score');

时间段查询

where方法支持时间比较,例如:

// 大于某个时间
where('create_time','> time','2016-1-1');
// 小于某个时间
where('create_time','<= time','2016-1-1');
// 时间区间查询
where('create_time','between time',['2015-1-1','2016-1-1']);
第三个参数可以传入任何有效的时间表达式,会自动识别你的时间字段类型,支持的时间类型包括timestamps、datetime、date和int。

使用whereTime方法
whereTime方法提供了日期和时间字段的快捷查询,示例如下:

// 大于某个时间
db('user')
    ->whereTime('birthday', '>=', '1970-10-1')
    ->select();
// 小于某个时间
db('user')
    ->whereTime('birthday', '<', '2000-10-1')
    ->select();
// 时间区间查询
db('user')
    ->whereTime('birthday', 'between', ['1970-10-1', '2000-10-1'])
    ->select();
// 不在某个时间区间
db('user')
    ->whereTime('birthday', 'not between', ['1970-10-1', '2000-10-1'])
    ->select();
时间表达式
还提供了更方便的时间表达式查询,例如:

// 获取今天的博客
db('blog')
    ->whereTime('create_time', 'today')
    ->select();
// 获取昨天的博客
db('blog')
    ->whereTime('create_time', 'yesterday')
    ->select();
// 获取本周的博客
db('blog')
    ->whereTime('create_time', 'week')
    ->select();   
// 获取上周的博客
db('blog')
    ->whereTime('create_time', 'last week')
    ->select();    
// 获取本月的博客
db('blog')
    ->whereTime('create_time', 'month')
    ->select();   
// 获取上月的博客
db('blog')
    ->whereTime('create_time', 'last month')
    ->select();      
// 获取今年的博客
db('blog')
    ->whereTime('create_time', 'year')
    ->select();    
// 获取去年的博客
db('blog')
    ->whereTime('create_time', 'last year')
    ->select();     
如果查询当天、本周、本月和今年的时间,还可以简化为:

// 获取今天的博客
db('blog')
    ->whereTime('create_time', 'd')
    ->select();
// 获取本周的博客
db('blog')
    ->whereTime('create_time', 'w')
    ->select();   
// 获取本月的博客
db('blog')
    ->whereTime('create_time', 'm')
    ->select();   
// 获取今年的博客
db('blog')
    ->whereTime('create_time', 'y')
    ->select();    

快捷查询

快捷查询方式是一种多字段相同查询条件的简化写法,可以进一步简化查询条件的写法,在多个字段之间用|分割表示OR查询,用&分割表示AND查询,可以实现下面的查询,例如:

Db::table('think_user')
    ->where('name|title','like','thinkphp%')
    ->where('create_time&update_time','>',0)
    ->find();
生成的查询SQL是:

SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' OR `title` LIKE 'thinkphp%' ) AND ( `create_time` > 0 AND `update_time` > 0 ) LIMIT 1
快捷查询支持所有的查询表达式。

区间查询

区间查询是一种同一字段多个查询条件的简化写法,例如:

Db::table('think_user')
    ->where('name',['like','thinkphp%'],['like','%thinkphp'])
    ->where('id',['>',0],['<>',10],'or')
    ->find();
生成的sql语句为:

SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' AND `name` LIKE '%thinkphp' ) AND ( `id` > 0 OR `id` <> 10 ) LIMIT 1
区间查询的查询条件必须使用数组定义方式,支持所有的查询表达式。

下面的查询方式是错误的:

Db::table('think_user')
    ->where('name',['like','thinkphp%'],['like','%thinkphp'])
    ->where('id',5,['<>',10],'or')
    ->find();
批量查询
可以进行多个条件的批量条件查询定义,例如:

Db::table('think_user')
    ->where([
        'name'  =>  ['like','thinkphp%'],
        'title' =>  ['like','%thinkphp'],
        'id'    =>  ['>',0],
        'status'=>  1
    ])
    ->select();
生成的SQL语句为:

SELECT * FROM `think_user` WHERE `name` LIKE 'thinkphp%' AND `title` LIKE '%thinkphp' AND `id` > 0 AND `status` = '1'
闭包查询
Db::table('think_user')->select(function($query){
    $query->where('name','thinkphp')
        ->whereOr('id','>',10);
});
生成的SQL语句为:

SELECT * FROM `think_user` WHERE `name` = 'thinkphp' OR `id` > 10
使用Query对象查询
也可以事先封装Query对象,并传入select方法,例如:

$query = new \think\db\Query;
$query->name('user')
    ->where('name','like','%think%')
    ->where('id','>',10)
    ->limit(10);
Db::select($query);    
如果使用Query对象的话,select方法之前调用的任何的链式操作都是无效。

混合查询

可以结合前面提到的所有方式进行混合查询,例如:

Db::table('think_user')
    ->where('name',['like','thinkphp%'],['like','%thinkphp'])
    ->where(function($query){
        $query->where('id',['<',10],['>',100],'or');
    })
    ->select();
生成的SQL语句是:

SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' AND `name` LIKE '%thinkphp' ) AND ( `id` < 10 or `id` > 100 )
字符串条件查询
对于一些实在复杂的查询,也可以直接使用原生SQL语句进行查询,例如:

Db::table('think_user')
    ->where('id > 0 AND name LIKE "thinkphp%"')
    ->select();
为了安全起见,我们可以对字符串查询条件使用参数绑定,例如:

Db::table('think_user')
    ->where('id > :id AND name LIKE :name ',['id'=>0, 'name'=>'thinkphp%'])
    ->select();
V5.0.4+开始,ThinkPHP支持对同一个字段多次调用查询条件,例如:

Db::table('think_user')
    ->where('name','like','%think%')
    ->where('name','like','%php%')
    ->where('id','in',[1,5,80,50])
    ->where('id','>',10)
    ->find();

原生查询

Db类支持原生SQL查询操作,主要包括下面两个方法:

query方法
query方法用于执行SQL查询操作,如果数据非法或者查询错误则返回false,否则返回查询结果数据集(同select方法)。

使用示例:

Db::query("select * from think_user where status=1");
如果你当前采用了分布式数据库,并且设置了读写分离的话,query方法始终是在读服务器执行,因此query方法对应的都是读操作,而不管你的sql语句是什么。

execute方法
execute用于更新和写入数据的sql操作,如果数据非法或者查询错误则返回false ,否则返回影响的记录数。

使用示例:

Db::execute("update think_user set name='thinkphp' where status=1");
如果你当前采用了分布式数据库,并且设置了读写分离的话,execute方法始终是在写服务器执行,因此execute方法对应的都是写操作,而不管你的SQL语句是什么。

参数绑定

支持在原生查询的时候使用参数绑定,包括问号占位符或者命名占位符,例如:

Db::query("select * from think_user where id=? AND status=?",[8,1]);
// 命名绑定
Db::execute("update think_user set name=:name where status=:status",['name'=>'thinkphp','status'=>1]);

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

微信扫一扫

微信扫一扫

微信扫一扫,分享到朋友圈

TP5  数据库操作方法集锦(超全)