Related operations on the THINKPHP5 database

thinkphp5-Database Operations
First, the database configuration
1. Add the following configuration parameters to the database.php in the application directory or module directory:

return [    //Database type ' type    '        = ' mysql ',    //Database connection DSN configuration    ' dsn ' = ' = ', '/'    server address    ' Hostname ' +    ' 127.0.0.1 ',    //database name '    thinkphp '    ,    //database user name    ' username '    = ' root ',    //Database password    ' password ' + ', '/    /Database connection Port    ' hostport '    = ',    //Database connection Parameters    ' params '      = [],    //Database encoding defaults to UTF8    ' charset '     = ' utf8 ',    // database table prefix    ' prefix '      = ' think_ ',    //Database debug mode    ' Debug '       = False,    //Database deployment method: 0 Centralized (single server), 1 distributed (Master-slave server)    ' deploy ' +      0,    ///database read/write whether separate master-slave effective    ' rw_separate ' and false,    // Read/write separation of the number of master servers    ' master_num ' +  1,    //Specify from server serial number    ' slave_no ' = ', '//    whether the field is strictly checked for existence    ' fields_strict '  = true,    ];

2, the string way:

Db::connect (' Mysql://root:1234@127.0.0.1:3306/thinkphp#utf8 '); database type://user name: password @ Database address: Database port/Database name # Character Set

Second, query (Inquiry operation) execute (write operation) original ecological SQL statement additions and deletions

Db::execute ("INSERT into t_test (Username,password) VALUES (' QQQ ', ' QQQ ')");  Db::execute ("Update t_test set username = ' WHERE id = '");D b::query (' select * from t_test where id = 5 ');D B::execute (' Delete from t_test where id = 6 ');

Three-parameter binding named placeholder bindings

Support parameter binding: Db::query (' select * from Think_user where id=? ', [8]);D B::execute (' INSERT into Think_user (ID, name) VALUES (?,?) ' , [8, ' thinkphp ']); supports placeholder bindings: db::query (' select * from Think_user where Id=:id ', [' ID ' =>8]);D b::execute (' INSERT INTO Think_user (ID, name) VALUES (: ID,: Name) ', [' id ' =>8, ' name ' = ' thinkphp ']);

Iv. Query Builder
1. Query data:
(1) Query a data use:

The table method must specify the full data table name db::table (' Think_user ')->where (' id ', 1)->find (); The//find method query result does not exist and returns null

(2) Querying data sets

Db::table (' Think_user ')->where (' status ', 1)->select (); The Select method query result does not exist, returning an empty array

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

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

If your data table does not use the table prefix feature, then the same effect as the name and table method.
(3) Helper function
The system provides a DB helper function that makes it easier to query:

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

2. Add Data:
(1) Add a piece of data

$data = [' foo ' = ' bar ', ' bar ' = ' foo '];D b::table (' Think_user ')->insert ($data);

(2) Add more than one data

$data = [    [' foo ' = ' bar ', ' bar ' = ' + ' foo '],    [' foo ' = ' bar1 ', ' bar ' = ' foo1 '],    [' foo ' and ' = ' bar2 ', ' bar ' = ' foo2 ']];D b::name (' user ')->insertall ($data); The Insertall method adds data successfully returns the number of successfully added bars

(3) Helper function

Add a single data db (' user ')->insert ($data);//Add multiple Data db (' user ')->insertall ($list);

3. Update data:
(1) updating data in a data table

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

(2) Helper function

Update the data in the data table in db (' user ')->where (' id ', 1)->update (' name ' = ' = ' thinkphp ');//Update the value of a field db (' user ')->where (' Id ', 1)->setfield (' name ', ' thinkphp ');//self-Increment score field db (' user ')->where (' id ', 1)->setinc (' score ');//auto-subtract score Field db (' user ')->where (' id ', 1)->setdec (' score ');

4. Delete data
(1) Delete data table//delete by primary key
Db::table (' Think_user ')->delete (1);
Db::table (' Think_user ')->delete ([+]);

Conditional delete
Db::table (' Think_user ')->where (' id ', 1)->delete ();
Db::table (' Think_user ')->where (' id ', ' < ', ten)->delete (); helper functions

Delete db (' user ')->delete (1) Based on primary key,//condition to delete    db (' user ')->where (' id ', 1)->delete ();

Five, chain-operated
If we were to query a user table for the first 10 records with a status of 1, and want to sort by the creation time of the users, the code is as follows:

Db::table (' Think_user ')    ->where (' status ', 1)    ->order (' create_time ')    ->limit ()    Select ();

The Where, order, and limit methods here are called chained operations, except that the Select method must be placed last (because the Select method is not a chained operation method), and the method invocation order of the chained operation is not sequential.

This article explains about the THINKPHP5 database related operations, more relevant content please pay attention to the PHP Chinese network.

Related recommendations:

Database and model usage on THINKPHP5

About the case of thinkphp5.0 database operations

List some similarities and differences between ThinkPHP5 and ThinkPHP3.