ThinkPHP database operations: addition, deletion, modification and query-ThinkPHP-php.cn

The following tutorial column will introduce you to the addition, deletion, modification and query of ThinkPHP database operations. I hope it will be helpful to friends in need!Basic use

You can directly use the database to run native SQL operations, supporting query (query operation) and execute (write operation) methods , and supports parameter binding.

Db::query('select * from think_user where id=?',[8]); Db::execute('insert into think_user (id, name) values (?, ?)',[8,'thinkphp']);

Copy after login

also supports named placeholder binding, for example:

Db::query('select * from think_user where id=:id',['id'=>8]); Db::execute('insert into think_user (id, name) values (:id, :name)',['id'=>8,'name'=>'thinkphp']);

Copy after login

can use multiple database connections, using

Db::connect($config)->query('select * from think_user where id=:id',['id'=>8]);

Copy after login

config is a separate database configuration, supports arrays and string, it can also be the configuration parameter name of a database connection.

Query data

##Basic query

To query a data, use:

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

Copy after login

Find method query result does not exist, return null

To query a data set, use:

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

Copy after login

The select method query result does not exist and returns an empty array

If the data table prefix parameter is set, you can use

Db::name('user')->where('id',1)->find(); Db::name('user')->where('status',1)->select();

Copy after login

If your data table does not use the table prefix function, then the name and table methods have the same effect.

All chain operation methods can be used before the find and select methods.

By default, the find and select methods return arrays.

Assistant function

The system provides a db assistant function, which can be more convenient Query:

db('user')->where('id',1)->find(); db('user')->where('status',1)->select();

Copy after login

Note: Using the db assistant function will reconnect to the database every time by default, while using the Db::name or Db::table method is a singleton. If the db function needs to use the same link, you can pass in the third parameter, for example:

db('user',[],false)->where('id',1)->find(); db('user',[],false)->where('status',1)->select();

Copy after login

The above method will use the same database connection, and the second parameter is the database connection. Parameter, leaving it blank means using the configuration of the database configuration file.

Use Query object or closure to query

Or use query object to query, For example:

$query = new \think\db\Query();$query->table('think_user')->where('status',1); Db::find($query); Db::select($query);

Copy after login

Or directly use the closure function to query, for example:

Db::select(function($query){ 　　$query->table('think_user')->where('status',1); });

Copy after login

Value and column query

To query the value of a certain field, you can use the

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

Copy after login

value method to query the result if it does not exist and return null

Query The value of a certain column can be queried using the

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

Copy after login

column method. If the query result does not exist, an empty array will be returned.

The data set is processed in batches

If you need to process thousands or hundreds of database records, you can consider using the chunk method. This method obtains a small piece of the result set at a time, and then fills each small piece of data into the closure to be processed. , this method is very useful when writing to process a large number of database records.For example, we can process all user table data in batches, processing 100 user records each time:

Db::table('think_user')->chunk(100, function($users) { foreach ($users as $user) { //查询数据 } });// 或者交给回调方法 myUserIterator处理 Db::table('think_user')->chunk(100, 'myUserIterator');

Copy after login

You can abort processing of the data set by returning false from the closure function Processing:

Db::table('think_user')->chunk(100, function($users) { // 处理结果集... return false; });

Copy after login

also supports calling other query methods before the chunk method, for example:

Db::table('think_user')->where('score','>',80)->chunk(100,function($users) { foreach ($users as $user) { // } });

Copy after login

JSON type data query (mysql V5.0.1)

// 查询JSON类型字段 （info字段为json类型） Db::table('think_user')->where('info$.email','thinkphp@qq.com')->find();

Copy after login

Add data

Add a piece of data

Use the insert method of the Db class to submit data to the database

$data = ['foo' => 'bar', 'bar' => 'foo']; Db::table('think_user')->insert($data);

Copy after login

If you configure the database prefix (prefix) in the database.php configuration file, you can directly use the name method of the Db class to submit Data

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

Copy after login

The insert method returns the number of successfully added data if the data is added successfully. Insert normally returns 1

添加数据后如果需要返回新增数据的自增主键，可以使用 getLastInsID 方法：

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

Copy after login

或者直接使用 insertGetId 方法新增数据并返回主键值：

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

Copy after login

insertGetId 方法添加数据成功返回添加数据的自增主键

添加多条数据

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

$data = [ 　　['foo' => 'bar', 'bar' => 'foo'],　　['foo' => 'bar1', 'bar' => 'foo1'],　　['foo' => 'bar2', 'bar' => 'foo2'] ]; Db::name('user')->insertAll($data);

Copy after login

　　insertAll 方法添加数据成功返回添加成功的条数

助手函数

// 添加单条数据 db('user')->insert($data); // 添加多条数据 db('user')->insertAll($list);

Copy after login

快捷更新

V5.0.5+ 以上版本封装的快捷更新方法 data ，可以配合 insert 使用。

下面举个例子说明用法：

Db::table('data')->data(['name'=>'tp','score'=>1000])->insert();

Copy after login

更新数据

更新数据表中的数据

Db::table('think_user')->where('id', 1)->update(['name' => 'thinkphp']);

Copy after login

如果数据中包含主键，可以直接使用：

Db::table('think_user')->update(['name' => 'thinkphp','id'=>1]);

Copy after login

update 方法返回影响数据的条数，没修改任何数据返回 0

如果要更新的数据需要使用 SQL 函数或者其它字段，可以使用下面的方式：

Db::table('think_user')->where('id', 1)->update([ 'login_time' => ['exp','now()'], 'login_times' => ['exp','login_times+1'],]);

Copy after login

更新某个字段的值

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

Copy after login

　　setField 方法返回影响数据的条数，没修改任何数据字段返回 0

自增或自减一个字段的值

setInc/setDec 如不加第二个参数，默认值为1

// score 字段加 1 Db::table('think_user') ->where('id', 1) ->setInc('score'); // score 字段加 5 Db::table('think_user') ->where('id', 1) ->setInc('score', 5); // score 字段减 1 Db::table('think_user') ->where('id', 1) ->setDec('score'); // score 字段减 5 Db::table('think_user') ->where('id', 1) ->setDec('score', 5);

Copy after login

延迟更新

setInc/setDec 支持延时更新，如果需要延时更新则传入第三个参数

下例中延时10秒，给 score 字段增加1

Db::table('think_user')->where('id', 1)->setInc('score', 1, 10);

Copy after login

　　setInc/setDec 方法返回影响数据的条数

助手函数

// 更新数据表中的数据 db('user')->where('id',1)->update(['name' => 'thinkphp']); // 更新某个字段的值 db('user')->where('id',1)->setField('name','thinkphp'); // 自增 score 字段 db('user')->where('id', 1)->setInc('score'); // 自减 score 字段 db('user')->where('id', 1)->setDec('score');

Copy after login

快捷更新（ V5.0.5+ ）

V5.0.5+ 以上版本封装的 data 、 inc 、 dec 和 exp 方法属于链式操作方法，可以配合 update 使用。

下面举个例子说明用法：

Db::table('data')->where('id',1)->inc('read')->dec('score',3)->exp('name','UPPER(name)')->update();

Copy after login

删除数据

删除数据表中的数据

// 根据主键删除 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();

Copy after login

delete 方法返回影响数据的条数，没有删除返回 0

助手函数

// 根据主键删除 db('user')->delete(1); // 条件删除 db('user')->where('id',1)->delete();

Copy after login

The above is the detailed content of ThinkPHP database operations: addition, deletion, modification and query. For more information, please follow other related articles on the PHP Chinese website!