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

发布于:2020-02-10 08:55:23

查询一个数据使用:


// table方法必须指定完整的数据表名

Db::table('think_user')->where('id',1)->find();




2.查询某个字段的值可以用


// 返回某个字段的值

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



3.查询某一列的值可以用

// 返回数组

Db::table('think_user')->where('status',1)->column('name');

// 指定索引

Db::table('think_user')->where('status',1)->column('name','id');


4.添加一条数据

// 使用 Db 类的 insert 方法向数据库提交数据

$data = ['foo' => 'bar', 'bar' => 'foo'];

Db::table('think_user')->insert($data);

// 如果你在database.PHP配置文件中配置了数据库前缀(prefix),那么可以直接使用 Db 类的 name 方法提交数据

Db::name('user')->insert($data);


5.添加多条数据


//添加多条数据直接向 Db 类的 insertAll 方法传入需要添加的数据即可

$data = [

    ['foo' => 'bar', 'bar' => 'foo'],

    ['foo' => 'bar1', 'bar' => 'foo1'],

    ['foo' => 'bar2', 'bar' => 'foo2']

];

Db::name('user')->insertAll($data);



6.更新数据表中的数据

Db::table('think_user')

    ->where('id', 1)

    ->update(['name' => 'thinkphp']);

如果数据中包含主键,可以直接使用:

Db::table('think_user')

    ->update(['name' => 'thinkphp','id'=>1]);


7.更新某个字段的值:

Db::table('think_user')

    ->where('id',1)

    ->setField('name', 'thinkphp');


8.删除数据表中的数据

// 根据主键删除

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



9.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(); 



10.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



11.用于查询指定字段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();


12.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(); 


13.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']);




14.分页查询

用于文章分页查询是limit方法比较常用的场合,例如:

Db::table('think_article')->limit('10,25')->select();

表示查询文章数据,从第10行开始的25条数据(可能还取决于where条件和order排序的影响 这个暂且不提)。

你也可以这样使用,作用是一样的:

Db::table('think_article')->limit(10,25)->select();

对于大数据表,尽量使用limit限制查询结果,否则会导致很大的内存开销和性能问题。



15.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(); 

————————————————


16.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



17.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


18.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();


19.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 语句中的列的顺序必须相同。



20.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方法的参数是一个布尔值。



21.lock

Lock方法是用于数据库的锁机制,如果在查询或者执行操作的时候使用:

lock(true);

就会自动在生成的sql语句最后加上 FOR UPDATE或者FOR UPDATE NOWAIT(oracle数据库)。



22.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);

最后查询的数据会是更新后的数据。



23.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 /* 查询考试前十名分数 */



24.fetchSql


fetchSql用于直接返回SQL而不是执行查询,适用于任何的CURD操作方法。 例如:

$result = Db::table('think_user')->fetchSql(true)->find(1);

输出result结果为: SELECT * FROM think_user where id = 1



25.force


force 方法用于数据集的强制索引操作,例如:

Db::table('think_user')->force('user')->select();

对查询强制使用user索引,user必须是数据表实际创建的索引名称。




26.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');



27.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();


28.strict

strict方法用于设置是否严格检查字段名,用法如下:

// 关闭字段严格检查

Db::name('user')

    ->strict(false)

    ->insert($data);

注意,系统默认值是由数据库配置参数fields_strict决定,因此修改数据库配置参数可以进行全局的严格检查配置,如下:

// 关闭严格检查字段是否存在

'fields_strict'  => false,

如果开启字段严格检查的话,在更新和写入数据库的时候,一旦存在非数据表字段的值,则会抛出异常。



29.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();


30.聚合查询


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


31.时间段查询


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



32.快捷查询


快捷查询方式是一种多字段相同查询条件的简化写法,可以进一步简化查询条件的写法,在多个字段之间用|分割表示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

快捷查询支持所有的查询表达式。



33.区间查询


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


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方法之前调用的任何的链式操作都是无效。



35.混合查询


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


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



36.原生查询


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语句是什么。


37.参数绑定

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


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


原网址:  https://blog.csdn.net/weixin_44535476/article/details/100891206


阅读 1040+

一片空白

父爱如山,不善表达。回想十多年前,总记得父亲有个宽厚的肩膀,小小的自己跨坐在上面,越过人山人海去看更广阔的天空,那个时候期望自己有一双翅膀,能够像鸟儿一样飞得高,看得远。虽然父亲有时会和自己开玩笑,但在做错事的时候会受到严厉的训斥。父亲有双粗糙的大手掌。