ThinkPHP database operations: addition, deletion, modification and query

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

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

can use multiple database connections, using

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

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

Find method query result does not exist, return null

To query a data set, use:

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

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

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

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

　

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

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

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

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

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

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

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

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) {    
//    
}
});

JSON type data query (mysql V5.0.1)

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

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

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

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

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

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

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

添加多条数据

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

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

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

助手函数

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

快捷更新

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

下面举个例子说明用法：

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

更新数据

更新数据表中的数据

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

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

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

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

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

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

更新某个字段的值

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

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

延迟更新

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

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

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

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

快捷更新（ 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();

删除数据

删除数据表中的数据

// 根据主键删除
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','<&#39;,10)->delete();

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

助手函数

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

 

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!