Database operation in TP6

Database operation in TP6

To use the Db class, you must use the facade mode (think\facade\Db) call

use think\facade\Db;

1, Database connection configuration

The configuration file is located in config/database.php

Or the. env file in the root directory of the development environment

APP_DEBUG = true
TYPE = mysql  //Database type  
HOSTNAME = //Connection address
DATABASE = test  //Database name
USERNAME = username  //Database user name
PASSWORD = password  //Database password
HOSTPORT = 3306  //port
CHARSET = utf8  //character set
DEBUG = true   //Open debug
default_lang = zh-cn
Dynamic configuration database connection query

Define the connection attribute in the data model

protected $connection = 'demo';


ThinkPHP's database connection is inert. It will connect to the database only when the actual data operation is carried out.

2, Distributed database support

If you need to support distributed databases, including read-write separation, you need to set the value of the configuration parameter deploy to 1

// Enable distributed database
'deploy'    =>  1,
'hostname'  => ',', //By default, the first address is the primary server
// Database user name
'username' => 'root,slave,slave',
// Database password
'password' => '123456',
For hostname, username, password, hostport, database, DSN and charset, either configure one with the same value, or set them separately

It is recommended to use an array to define parameters, for example:

'hostname' =>[ '','',''],
'password' => ['123456','abc,def','hello']

3, The database supports read-write separation

Enable read / write classification configuration:

'rw_separate' => true,
By default, the first one is the primary database, which is responsible for writing data. If master is set_ Num, you can support multiple master server writes.

If native SQL is used, the execute method must be used for write operations and the query method must be used for read operations

After the data has just been written, the slave database data has not been synchronized in time. You can use master() to query the master database

It can also be enabled: when we write to a data table, all subsequent queries on the table in the current request will be read by the main database

'read_master' => true,


4, Chain operation

Query single data
Db::table('think_user')->where('id', 1)->find();
Null query returns an empty array
Db::table('think_user')->where('id', 1)->findOrEmpty();
If there are no query conditions and the order method is not called, the find query will not return any results.

Query result set:

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

  The query result of the select method is a dataset object, which can be used if it needs to be converted into an array

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

  Query a field

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

  Query a column

// Return array
// appoint id The value of the field as an index
Db::table('think_user')->where('status',1)->column('name', 'id');

  Add data:

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

  When using the save method, if there is an ID in the data, it is updated, and if there is no ID, it is new

The insert method successfully adds data and returns the number of successfully added items. Normally, it returns 1
If you don't want to throw an exception because there are fields not in the data table, you can use the following method


  Return primary key after adding

$userId = Db::name('user')->strict(false)->insertGetId($data);

  Update data:

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

  Support expression update

Or use raw
Db::name('user')->where('id', 1)->update([
        'name'    =>    Db::raw('UPPER(name)'),
        'score'    =>    Db::raw('score-3'),
        'read_time'    =>    Db::raw('read_time+1')

  Self increasing and self decreasing

// score Field plus 1
Db::table('think_user')->where('id', 1)->inc('score')->update();
// score Field plus 5
Db::table('think_user')->where('id', 1)->inc('score', 5)->update();
// score Field minus 1
Db::table('think_user')->where('id', 1)->dec('score')->update();
// score Field minus 5
Db::table('think_user')->where('id', 1)->dec('score', 5)->update();
Delete data:
// Delete by primary key

// Condition deletion    

Calling delete without any conditions returns an error. If you really need to delete all data, you can use

// Unconditionally delete all data

  Query expression:

  The format is as follows:

where('field name ',' query expression ',' query criteria ');
// = be equal to
//<> Not equal to
//>    greater than
//>=    Greater than or equal to    
//<    less than
Db::name('user')->where('id', '<', 100)->select();    
<=    Less than or equal to
Db::name('user')->where('id', '<=', 100)->select();
//[NOT] LIKE Fuzzy query
Db::name('user')->where('name', 'like', 'thinkphp%')->select();
//[NOT] BETWEEN    (Not in) interval query    
//[NOT] IN    ((not present) IN query
//[NOT] NULL : 
Db::name('user')->where('name', null)->where('email','null')
->where('name','not null')->select();


  Example of chain operation:

Chain where condition:

//1,Expression condition

//2,Array condition
//3,String condition
Db::table('think_user')->whereRaw('type=1 AND status=1')->select(); 
Or you need to pass in variables and cooperate with the preprocessing mechanism to ensure more security
->whereRaw("id=:id and username=:name", ['id' => 1 , 'name' => 'thinkphp'])
Chained table operation

Chain alias operation to set the alias of the table

->join('think_dept b ','b.user_id=')
field operation

Or use an alias
Db::table('user')->field('id,nickname as name')->select();
Or use SQL function
strict operation

If an illegal field is submitted, an exception will be thrown. You can turn off strict field checking in the configuration

'fields_strict'    =>    false,
It can also be closed temporarily

// Close field strict check
limit operation

//Get 10 users who meet the requirements
//limit Method can also be used for write operations, such as updating 3 pieces of data that meet the requirements:

//Paging query, which queries 10 pieces of data starting from line 20
page operation

// Query first page data
// Query second page data
order operation

//No collation specified, default asc
Db::table('user')->where('status', 1)->order('id')->select();
//Specify collation
Db::table('user')->where('status', 1)->order('id', 'desc')->select();
group operation

It is usually used to group the result set according to one or more columns in combination with the total function.

Group multiple fields
having operation

join operation

INNER JOIN: Equivalent to JOIN(default JOIN (type),If there is at least one match in the table, the row is returned
LEFT JOIN: Even if there is no match in the right table, all rows are returned from the left table
RIGHT JOIN: Even if there is no match in the left table, all rows are returned from the right table
FULL JOIN: Rows are returned whenever there is a match in one of the tables
//join mode
->join('work w',' = w.artist_id')
->join('card c','a.card_id =')
//left join mode
->leftJoin('word w',' = w.artist_id')


5, Aggregate query

count: Db::table('think_user')->count();
max: Db::table('think_user')->max('score');
min: Db::table('think_user')->min('score');
avg: Db::table('think_user')->avg('score');
sum: Db::table('think_user')->sum('score');

6, Paging query

  Controller code:

// Query the user data with status 1 and display 10 pieces of data per page
$list = Db::name('user')->where('status',1)->order('id', 'desc')->paginate(10);
// Get paged display
$page = $list->render();
return view('index', ['list' => $list, 'page' => $page]);


  HTML code:

{volist name='list' id='user'}
    <li> {$user.nickname}</li>
Get total data

// Get total records
$count = $list->total();
Post paging data processing

$list = Db::name('user')->where('status',1)->order('id', 'desc')
->paginate()->each(function($item, $key){
    $item['nickname'] = 'think';
    return $item;
Paging data processing in the model, no return required

$list = User::where('status',1)->order('id', 'desc')->paginate()->each(function($item, $key){
    $item->nickname = 'think';
Paging parameters:

$list = Db::name('user')->where('status',1)->paginate([
    'list_rows'=> 20,
    'query'    => $data

7, Time query whereTime

// Greater than a certain time
Db::name('user')->whereTime('birthday', '>=', '1970-10-1')->select();
// Less than a certain time
Db::name('user')->whereTime('birthday', '<', '2000-10-1')->select();
// Time interval query
Db::name('user')->whereTime('birthday', 'between', ['1970-10-1', '2000-10-1'])
// Not in a certain time interval
Db::name('user')->whereTime('birthday', 'not between', ['1970-10-1', '2000-10-1'])

8, Native Query

Starting from V6.0.3 +, native queries only support Db operations

query method

Db::query("select * from think_user where status=:id", ['id' => 1]);
//Read from main library
Db::query("select * from think_user where status=:id", ['id' => 1], true);
execute method

Db::execute("update think_user set name='thinkphp' where status=1");
Parameter binding

//Question mark placeholder binding
Db::query("select * from think_user where id=? AND status=?", [8, 1]);
// Named binding
Db::execute("update think_user set name=:name where status=:status",
 ['name' => 'thinkphp', 'status' => 1]);
 //Note that parameter binding for table names is not supported

9: Transaction operation

Using transaction processing requires the database engine to support transaction processing. For example, MySQL's MyISAM does not support transaction processing, and the InnoDB engine needs to be used.

The simplest transaction closure operation:

Db::transaction(function () {
To run a transaction manually:

// Start transaction
try {
    // Commit transaction
} catch (\Exception $e) {
    // Rollback transaction

10: Stored procedure

//Data access layer calls stored procedures
$resultSet = Db::query('call procedure_name');
foreach ($resultSet as $result) {


//Stored procedures can support input and output parameters, as well as parameter binding operations.
$resultSet = Db::query('call procedure_name(:in_param1,:in_param2,:out_param)', [
    'in_param1' => $param1,
    'in_param2' => [$param2, PDO::PARAM_INT],
    'out_param' => [$outParam, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 4000],



// Get dataset
$users = Db::name('user')->select();
// Direct operation on the first element
$item  = $users[0];
// Gets the number of dataset records
$count = count($users);
// Traversal dataset
foreach($users as $user){
    echo $user['name'];
    echo $user['id'];
To judge whether the dataset is empty, you cannot directly use empty, but must use the isEmpty method of the dataset object,

$users = Db::name('user')->select();
    echo 'The dataset is empty';






Tags: thinkphp

Posted on Thu, 04 Nov 2021 05:17:13 -0400 by karldesign