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
[APP]
DEFAULT_TIMEZONE = Asia/Shanghai
[DATABASE]
TYPE = mysql  //Database type  
HOSTNAME = 127.0.0.1 //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
[LANG]
default_lang = zh-cn
Dynamic configuration database connection query

\think\facade\Db::connect('demo')->table('user')->find();
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'  => '192.168.1.1,192.168.1.2', //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' =>[ '192.168.1.1','192.168.1.2','192.168.1.3'],
'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

Db::name('user')->master(true)->find(1);
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();
explain:
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
Db::table('think_user')->where('status',1)->column('name');
// 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'];
Db::name('user')->save($data);
or
Db::name('user')->insert($data);

  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

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

  Return primary key after adding

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

  Update data:

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

  Support expression update

Db::name('user')->where('id',1)->exp('name','UPPER(name)')->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
Db::table('think_user')->delete(1);
Db::table('think_user')->delete([1,2,3]);

// Condition deletion    
Db::table('think_user')->where('id',1)->delete();
Db::table('think_user')->where('id','<',10)->delete();

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

// Unconditionally delete all data
Db::name('user')->delete(true);

  Query expression:

  The format is as follows:

where('field name ',' query expression ',' query criteria ');
// = be equal to
Db::name('user')->where('id','=',100)->select();    
//<> Not equal to
Db::name('user')->where('id','<>',100)->select();
//>    greater than
Db::name('user')->where('id','>',100)->select();    
//>=    Greater than or equal to    
Db::name('user')->where('id','>=',100)->select();
//<    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    
Db::name('user')->where('id','between','1,8')->select();
//[NOT] IN    ((not present) IN query
Db::name('user')->where('id','in','1,5,8')->select();
or
Db::name('user')->where('id','in',[1,5,8])->select();
//[NOT] NULL : 
Db::name('user')->where('name', null)->where('email','null')
->where('name','not null')->select();

 

  Example of chain operation:

Db::table('think_user')
    ->where('status',1)
    ->order('create_time')
    ->limit(10)
    ->select();
Chain where condition:

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

//2,Array condition
Db::table('think_user')->where(['name'=>'thinkphp','status'=>1])->select(); 
//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
Db::table('think_user')
->whereRaw("id=:id and username=:name", ['id' => 1 , 'name' => 'thinkphp'])
->select();
Chained table operation

Db::field('user.name,role.title')
->table([
    'think_user'=>'user',
    'think_role'=>'role'
])
->limit(10)->select();
Chain alias operation to set the alias of the table

Db::table('think_user')
->alias('a')
->join('think_dept b ','b.user_id= a.id')
->select();
field operation

Db::table('user')->field('id,title,content')->select();
Or use an alias
Db::table('user')->field('id,nickname as name')->select();
Or use SQL function
Db::table('user')->fieldRaw('id,SUM(score)')->select();
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
Db::name('user')->strict(false)->insert($data);
limit operation

//Get 10 users who meet the requirements
Db::table('user')->where('status',1)->field('id,name')->limit(10)->select();
//limit Method can also be used for write operations, such as updating 3 pieces of data that meet the requirements:
Db::table('user')->where('score',100)->limit(3)->update(['level'=>'A']);

//Paging query, which queries 10 pieces of data starting from line 20
Db::table('article')->limit(20,10)->select();
page operation

// Query first page data
Db::table('article')->page(1,10)->select(); 
// Query second page data
Db::table('article')->page(2,10)->select(); 
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.

Db::table('user')->field('user_id,username,max(score)')
    ->group('user_id')->select();
Group multiple fields
Db::table('user')->field('user_id,test_time,username,max(score)')
    ->group('user_id,test_time')->select();
having operation

Db::table('score')
    ->field('username,max(score)')->group('user_id')
    ->having('count(test_time)>3')->select(); 
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
Db::table('think_artist')
->alias('a')
->join('work w','a.id = w.artist_id')
->join('card c','a.card_id = c.id')
->select();
//left join mode
Db::table('think_user')
->alias('a')
->leftJoin('word w','a.id = w.artist_id')
->select();

 

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:

<div>
<ul>
{volist name='list' id='user'}
    <li> {$user.nickname}</li>
{/volist}
</ul>
</div>
{$page|raw}
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'])
    ->select();
// Not in a certain time interval
Db::name('user')->whereTime('birthday', 'not between', ['1970-10-1', '2000-10-1'])
    ->select();

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 () {
    Db::table('think_user')->find(1);
    Db::table('think_user')->delete(1);
});
To run a transaction manually:

// Start transaction
Db::startTrans();
try {
    Db::table('think_user')->find(1);
    Db::table('think_user')->delete(1);
    // Commit transaction
    Db::commit();
} catch (\Exception $e) {
    // Rollback transaction
    Db::rollback();
}

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

 

Dataset:

// 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();
if($users->isEmpty()){
    echo 'The dataset is empty';
}

 

 

 

 

 

Tags: thinkphp

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