I. Database-related operations

1. Database Configuration

(1) Default global database configuration

Set up the global database configuration information in the application/database.php file. The basic definition information of the database is as follows:

return [
    // Database type
    'type'            => 'mysql',
    // server address
    'hostname'        => '127.0.0.1',
    // Database name
    'database'        => '',
    // User name
    'username'        => 'root',
    // Password
    'password'        => '',
    // port
    'hostport'        => '',
    // Connect dsn
    'dsn'             => '',
    // Database Connection Parameters
    'params'          => [],
    // Database coding defaults to utf8
    'charset'         => 'utf8',
    // Database table prefix
    'prefix'          => '',
    // Database Debugging Mode
    'debug'           => true,
    // Database deployment: 0 centralized (single server), 1 distributed (master-slave server)
    'deploy'          => 0,
    // Is it effective to separate master from slave in database reading and writing
    'rw_separate'     => false,
    // Number of primary servers after read-write separation
    'master_num'      => 1,
    // Specify slave server serial number
    'slave_no'        => '',
    // Are fields strictly checked for existence?
    'fields_strict'   => true,
    // Data Set Return Type
    'resultset_type'  => 'array',
    // Autowrite timestamp fields
    'auto_timestamp'  => false,
    // Default time format after time field is taken out
    'datetime_format' => 'Y-m-d H:i:s',
    // Do you need to perform SQL performance analysis?
    'sql_explain'     => false,
];

(2) Customizable database connection for a single module

Create database.php under the module folder. The database configuration file of the module only needs the different parts of the configuration file and the global database configuration file, the same does not need to be repeated configuration.

2. Native Query - CRUD

Once the database connection information is set up, the native SQL query operation can be carried out directly, including query and execute, to realize the CURD operation of the data table.
The table tb_admin data table already exists in the database

(1) Create - C (Create)

//insert record
$res=Db::execute("INSERT INTO tb_admin(`name`,pwd) VALUES('zhang',MD5('123456'))");
return dump($res);

The SQL statements executed are:

 INSERT INTO tb_admin(`name`,pwd) VALUES('zhang',MD5('123456')) 

(2) U pdate

// Update records
$result = Db::execute('update tb_admin set name = "lisi" where id = 6');
dump($result);

The SQL statements executed are:

 INSERT INTO tb_admin(`name`,pwd) VALUES('zhang',MD5('123456')) 

(3) Reading - R (Read)

//Read records
$result = Db::query('select * from tb_admin where id = 6');
dump($result);

The SQL statements executed are:

select * from tb_admin where id = 6

(4) Delete - D (Delete)

// Delete data
$result = Db::execute('delete from tb_admin where id = 9 ');
dump($result);

The SQL statements executed are:

 delete from tb_admin where id = 9 

(5) Other operations

In principle, the query method is used for reading operations and the execute method is used for writing operations.
The query method is used to query. By default, the data set (two-dimensional array) is returned. The return value of the execute method is the number of rows affected.

// Display a list of databases
$result = Db::query('show tables from myshop');
dump($result);
// Clean up the data sheet
$result = Db::execute('TRUNCATE table think_data');
dump($result);

(5) Switching database

Method 1:

$result = Db::connect([
// Database type
    'type' => 'mysql',
// server address
    'hostname' => '127.0.0.1',
// Database name
    'database' => 'myshop',
// Database username
    'username' => 'root',
// Database password
    'password' => '123456',
// Database Connection Port
    'hostport' => '3306',
// Database Connection Parameters
    'params' => [],
// Database coding defaults to utf8
    'charset' => 'utf8',
// Database table prefix
    'prefix' => 'tb_',
])->query('select * from tb_admin');
dump($result);

Method 2:
String Definition (String Definition Can't Define Data Table Prefixes and Connection Parameters)

$result = Db::connect('mysql://root:123456@127.0.0.1:3306/myshop#utf8'
)->query('select * from tb_admin where id = 6');
dump($result);

Method 3:
Define multiple database configurations:
In order to simplify the code, it is common to define the connection configuration of many databases in the configuration file beforehand, and add the following configuration in the application configuration file application/config.php:

// Database Configuration 1
'db1' => [
// Database type
    'type' => 'mysql',
// server address
    'hostname' => '127.0.0.1',
// Database name
    'database' => 'myshop',
// Database username
    'username' => 'root',
// Database password
    'password' => '123456',
// Database Connection Port
     'hostport' => '3306',
// Database Connection Parameters
    'params' => [],
// Database coding defaults to utf8
    'charset' => 'utf8',
// Database table prefix
    'prefix' => 'tb_',
],

// Database Configuration 2
'db2' => [
// Database type
    'type' => 'mysql',
// server address
    'hostname' => '127.0.0.1',
// Database name
    'database' => 'test',
// Database username
    'username' => 'root',
// Database password
    'password' => '123456',
// Database Connection Port
    'hostport' => '3306',
// Database Connection Parameters
    'params' => [],
// Database coding defaults to utf8
    'charset' => 'utf8',
// Database table prefix
    'prefix' => 'test_',
],

You can switch database connections directly by passing in configuration parameters in the connect method, such as:

$result = Db::connect('db1')->query('select * from tb_admin where id = 1');
$result = Db::connect('db2')->query('select * from think_data where id = 1');
dump($result);

The configuration parameters in the connect method need to be fully defined, and only if the query is valid, the default database connection will be used the next time the Db class is called. If you need to switch database queries several times, you can use:

$db1 = Db::connect('db1');
$db2 = Db::connect('db2');
$db1->query('select * from tb_admin where id = 1');
$db2->query('select * from tb_admin where id = 1');

(6) Parameter binding

In actual development, some data may use external incoming variables. In order to make query operations safer, we suggest using parameter binding mechanism. For example, the above operation can be changed to:
Method 1:
Use? Placeholders

$res=Db::execute("INSERT INTO tb_admin(`name`,pwd) VALUES(?,?)",["wu","md5('123456')"]);
return dump($res);

Method 2:
Use named placeholders

$res = Db::execute('update tb_admin set name =:name where id= :id',['name'=>'mang','id'=>'10']);
dump($res);

3. Query constructor

(1) Use table()

//insert record
$res=Db::table("tb_admin")
     ->insert(['name'=>'nihao','pwd'=>md5('123456')]);
 dump($res);

//Update records
 $res=Db::table("tb_admin")
     ->where('id',12)
     ->update(['name'=>'hello']);
 dump($res);

//Query data
 $res=Db::table("tb_admin")
     ->where('id',12)
     ->select();
 dump($res);

//Delete data
 $res=Db::table("tb_admin")
     ->where('id',13)
     ->delete();
 dump($res);

(2) Using name() - simplifies the code without changing the CURD code due to changes in the table prefix of the database

//insert record
$res=Db::name("admin")
     ->insert(['name'=>'hhh','pwd'=>md5('123456')]);
 dump($res);

//Update records
 $res=Db::name("admin")
     ->where('id',15)
     ->update(['name'=>'haha']);
 dump($res);

 //Query data
 $res=Db::name("admin")
     ->where('id',12)
     ->select();
 dump($res);

//Delete data
 $res=Db::name("admin")
     ->where('id',16)
     ->delete();
 dump($res);

Note: Use name to omit prefix names of tables

(3) Code simplification using the helper function db()

$db = db("admin");

//insert record
$res=$db ->insert(['name'=>'daha','pwd'=>md5('123456')]);
dump($res);

//Update records
$res=$db->where("id",16)
    ->update(['name'=>'xiaozi']);
dump($res);

//Query data
$res=$db->where('id',17)
    ->select();
dump($res);

//Delete data
$res=$db->where('id',18)
    ->delete();
dump($res);

Note: By default, db helper functions will reconnect to the database every time, so multiple calls should be avoided as far as possible.

4. Chain Operations - Complex Database Query Operations

Chain operations do not distinguish order, as long as they are invoked before the query method (select method):
The common chain operation methods are as follows:

(1) Examples:

// Query 10 eligible data and follow id Inverse Arrangement
$list = Db::name('admin')
    ->field('id,name')
    ->order('id', 'desc')
    ->limit(10)
    ->select();
dump($list);

(2) The difference between find() and select()

find([primary key]) - Query a record, the default is to take the first row of the query results, return a one-dimensional array, if you want to find a specific row in the result set, you can add the primary key value in find() for query, if not meet the conditions, then return null by default (also support setting whether to throw an exception).

$res=Db::name('admin')
    ->field('id,name')
    ->order('id','asc')
    ->find(3);
dump($res);

select() - Queries all qualified records and returns a two-dimensional array.

II. Query Language

1. Query expression

The relevant supporting query expressions are as follows:
Expressions are case-insensitive

(1) Using expressions

When querying expressions, the parameters of where method are in turn:

Where (field name, conditional expression, query value)

$result = Db::name('admin')
    ->where('id', '>=', 6)
    ->limit(10)
    ->select();
dump($result);

(2) Native SQL statement expression

The parameters of where method are in turn:

Where (field name,'exp', expression and value)

For example:

$result = Db::name('admin')
    ->where('id', 'exp','>= 3')
    ->limit(10)
    ->select();
dump($result)

(3) Query with multiple fields

$result = Db::name('admin')
    // id between 1 and 3
    ->where('id', 'between', [4, 8])
    // name contains think
    ->where('name', 'like', '%h%')
    ->select();
dump($result);

2. Batch Query - One Method, Multiple Query Conditions Mixed Query

(1) General batch query

$result = Db::name('admin')
->where([
    'id'=>['between','2,6'],
    'name'=>['like','%h%'],
     ])->select();
 dump($result);

(2) Mixed queries using OR and AND

$result = Db::name('data')
// name contains think
    ->where('name', 'like', '%h%')
    ->where('id', ['in', [1, 2, 3]],
        ['between', '5,8'],
        'or')
    ->limit(10);
dump($result);

(3) Using batch queries

$result = Db::name('admin')
    ->where([
        'id' => [['in', [1, 2, 3]], ['between', '5,8'], 'or'],
        'name' => ['like', '%h%'],
    ])->limit(10)->select();
dump($result);

3. Quick Query - Multiple fields need to use the same query condition

(1) & mode query

$result = Db::name('admin')
    ->where('id&status', '>', 0)
    ->limit(10)
    ->select();
dump($result);

(2) | mode query

$result = Db::name('admin')
    ->where('id|status', '>', 0)
    ->limit(10)
    ->select();
dump($result);

4. Get a value --- value() Get a value of a row using the value (parameter) method, the parameter is the field name of the query table.

$name = Db::name('admin')
    ->where('id','>', 8)
    ->value('name');
dump($name);

Note: No need to write select() method

5. Get column number - column()

(1) General access usage

$list=Db::name('admin')
    ->where('id','>',4)
    ->column('name');
dump($list);

** Note: No need to write select() method
The results are as follows: **

(2) Column data indexed by ID

$list = Db::name('admin')
    ->where('id','>', 6)
    ->column('name', 'id');
dump($list);

(3) Column data indexed by primary key

$list = Db::name('admin')
    ->where('id','>',6)
    ->column('*', 'id');
dump($list);

6. Aggregate Query --- No need to select()

Examples are given to illustrate:

$count = Db::name('admin')
    ->where('id','>', 1)
    ->count();
dump($count);

7. String query

$result = Db::name('admin')
    ->where('id > :id AND name IS NOT NULL', ['id' => 10])
    ->select();
dump($result);

The sql statement executed is:

 SELECT * FROM `tb_admin` WHERE ( id > '10' AND name IS NOT NULL ) 

8. Time (Date) Query - whereTime()

whereTime() has three fields, and the second can be saved:

// Data with query creation time greater than 2016-1-1
        $result = Db::name('data')
            ->whereTime('create_time', '>', '2016-1-1')
            ->select();
        dump($result);
// Query the data added this week
        $result = Db::name('data')
            ->whereTime('create_time', '>', 'this week')
            ->select();
        dump($result);
// Query the data added in the last two days
        $result = Db::name('data')
            ->whereTime('create_time', '>', '-2 days')
            ->select();
        dump($result);
// Data with query creation time from 2016-1-1 to 2016-7-1
        $result = Db::name('data')
            ->whereTime('create_time', 'between', ['2016-1-1', '2016-7-1'])
            ->select();
        dump($result);

9. Block Query - Query a large amount of data, chunk (number of processing items, callback function {})

The memory overhead can be reduced by dividing 10,000 records into 100 processing times and 100 records at a time. The code example is as follows:

Db::name('admin')
    ->where('id', '>', 0)
    ->chunk(30, function ($list) {
// Processing 30 records
        foreach($list as $data){
            echo "<pre>";
            print_r($data)."<br>";
            echo "</pre>";
        }
    });