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