Detailed explanation of query statements in thinkphp

ThinkPHP is an open source PHP-based Web application framework that provides many tools and solutions for convenient development, including the construction and execution of query statements. This article will introduce the query statement function in ThinkPHP, including query builder and data model, to help developers perform database operations more efficiently.

1. Query Builder

ThinkPHP’s query builder is a set of object-oriented SQL statement builders that are used to quickly build complex SQL statements. In the query builder, you can build SQL statements of types SELECT, UPDATE, INSERT, and DELETE using several methods.

1. SELECT query statement

Use the SELECT query statement to obtain data in the database. You can use the select() method of the query builder to build a SELECT query statement:

// 查询所有用户数据
Db::name('user')->select();

// 查询 id 为 1 的用户数据
Db::name('user')->where('id', 1)->find();

// 查询年龄大于 18 岁的用户数据
Db::name('user')->where('age', '>', 18)->select();

In the above code, Db::name('user') represents the data table to be queried. The select() method indicates querying all data; the find() method indicates querying a single record.

2. UPDATE query statement

Use the UPDATE query statement to update data in the database. You can use the update() method of the query builder to build an UPDATE query statement:

// 更新 id 为 1 的用户数据
Db::name('user')->where('id', 1)->update(['name' => 'Tom']);

// 将所有用户的角色都更新为 2
Db::name('user')->update(['role_id' => 2]);

In the update() method, the first parameter represents the condition of the record to be updated, and the second parameter is the updated data. content.

3. INSERT query statement

Use the INSERT query statement to insert new data into the database. You can use the insert() method of the query builder to build an INSERT query statement:

// 向 user 表中插入一条新数据
Db::name('user')->insert(['name' => 'Jack', 'age' => 20, 'role_id' => 1]);

In the insert() method, the parameter is the new data content to be inserted.

4. DELETE query statement

Use the DELETE query statement to delete data in the database. You can use the delete() method of the query builder to build a DELETE query statement:

// 删除 id 为 1 的数据
Db::name('user')->where('id', 1)->delete();

// 删除所有角色为 3 的用户数据
Db::name('user')->where('role_id', 3)->delete();

In the delete() method, the parameter is the condition of the record to be deleted.

2. Data Model

In addition to the query builder, ThinkPHP also provides a set of database operation methods based on the data model, which can map the data table into a class, and can Automatically handle CRUD of database records based on changes in class attributes.

1. Define the data model

You can use the following code to define a data model class:

namespace app\common\model;

use think\Model;

class User extends Model
{
    // 数据表名
    protected $table = 'user';

    // 自动写入时间戳
    protected $autoWriteTimestamp = true;

    // 模型关联：用户角色
    public function role()
    {
        return $this->belongsTo(Role::class, 'role_id');
    }
}

In the above example, we inherit think\Model class defines a user data model class. The $table attribute indicates the name of the data table to be mapped, and the $autoWriteTimestamp attribute indicates whether to automatically write timestamps.

2. Query data

We can use the find(), select(), where() and other methods of the data model to query the database:

// 查询 id 为 1 的用户数据
$user = User::find(1);

// 查询用户表中所有数据
$users = User::select();

// 查询年龄大于 18 岁的用户数据
$users = User::where('age', '>', 18)->select();

In the above example, we used the static methods of the data model class to perform database queries.

3. Update and insert data

We can use the save() method of the data model to update and insert data:

// 更新 id 为 1 的用户数据
$user = User::find(1);
$user->name = 'Tom';
$user->save();

// 向 user 表中插入一条新数据
$user = new User;
$user->name = 'Jack';
$user->age = 20;
$user->role_id = 1;
$user->save();

In the above example , we use the properties of the data model object to set the data to be updated or inserted, and then call the save() method to submit it to the database.

4. Delete data

We can use the delete() method of the data model to delete data:

// 删除 id 为 1 的用户数据
$user = User::find(1);
$user->delete();

// 删除所有角色为 3 的用户数据
User::where('role_id', 3)->delete();

In the above example, we used The delete() method of the data model object deletes the specified record. You can also use the where() method of the static method to select the record to be deleted, and then call the delete() method to delete it.

Summary

The above is how to use ThinkPHP to build query statements. The query builder provides a variety of methods to build SQL statements such as SELECT, UPDATE, INSERT and DELETE, and data models. It provides an object-oriented way to operate the database. Whether using the query builder or data model, we can quickly build complex SQL query statements to facilitate developers to perform database operations.

The above is the detailed content of Detailed explanation of query statements in thinkphp. For more information, please follow other related articles on the PHP Chinese website!