Related operations on ThinkPHP5 database

ThinkPHP5-Database Operation
1. Database configuration
1. Add the following configuration parameters in database.php under the application directory or module directory:

return [    // 数据库类型
    'type'        => 'mysql',    // 数据库连接DSN配置
    'dsn'         => '',    // 服务器地址
    'hostname'    => '127.0.0.1',    // 数据库名
    'database'    => 'thinkphp',    // 数据库用户名
    'username'    => 'root',    // 数据库密码
    'password'    => '',    // 数据库连接端口
    'hostport'    => '',    // 数据库连接参数
    'params'      => [],    // 数据库编码默认采用utf8
    'charset'     => 'utf8',    // 数据库表前缀
    'prefix'      => 'think_',    // 数据库调试模式
    'debug'       => false,    // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
    'deploy'      => 0,    // 数据库读写是否分离 主从式有效
    'rw_separate' => false,    // 读写分离后 主服务器数量
    'master_num'  => 1,    // 指定从服务器序号
    'slave_no'    => '',    // 是否严格检查字段是否存在
    'fields_strict'  => true,    
];

2. String mode:

Db::connect('mysql://root:1234@127.0.0.1:3306/thinkphp#utf8');
数据库类型://用户名:密码@数据库地址:数据库端口/数据库名#字符集

2. query (query operation) execute (write operation) original SQL statement addition, deletion, modification and query

Db::execute("insert into t_test(username,password) values('qqq','qqq')");  
Db::execute("update t_test set username ='55' where id = '10'");
Db::query('select * from t_test where id = 5');
Db::execute('delete from t_test where id = 6');

Three parameter binding named placeholder binding

支持参数绑定：
Db::query('select * from think_user where id=?',[8]);
Db::execute('insert into think_user (id, name) values (?, ?)',[8,'thinkphp']);
支持占位符绑定：
Db::query('select * from think_user where id=:id',['id'=>8]);
Db::execute('insert into think_user (id, name) values (:id, :name)',['id'=>8,'name'=>'thinkphp']);

4. Query constructor
1. Query data:
(1) Query a data using:

// table方法必须指定完整的数据表名Db::table('think_user')->where('id',1)->find();//find 方法查询结果不存在，返回 null

(2) Query a data set

Db::table('think_user')->where('status',1)->select();
select 方法查询结果不存在，返回空数组

If a data table is set For prefix parameters, you can use

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

If your data table does not use the table prefix function, then the name and table methods have the same effect.
(3) Assistant function
The system provides a db assistant function for more convenient querying:

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

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

$data = ['foo' => 'bar', 'bar' => 'foo'];Db::table('think_user')->insert($data);

(2) Add multiple pieces of data

$data = [
    ['foo' => 'bar', 'bar' => 'foo'],
    ['foo' => 'bar1', 'bar' => 'foo1'],
    ['foo' => 'bar2', 'bar' => 'foo2']
];Db::name('user')->insertAll($data);
insertAll 方法添加数据成功返回添加成功的条数

(3) Assistant function

// 添加单条数据
db('user')->insert($data);// 添加多条数据
db('user')->insertAll($list);

3. Update data:
(1) Update data in the data table

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

(2) Assistant function

// 更新数据表中的数据
db('user')->where('id',1)->update(['name' => 'thinkphp']);// 更新某个字段的值
db('user')->where('id',1)->setField('name','thinkphp');// 自增 score 字段
db('user')->where('id', 1)->setInc('score');// 自减 score 字段
db('user')->where('id', 1)->setDec('score');

4. Delete data
(1) Delete data in the table // Delete according to the primary key
Db::table('think_user')->delete (1);
Db::table('think_user')->delete([1,2,3]);

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

// 根据主键删除
db('user')->delete(1);// 条件删除    
db('user')->where('id',1)->delete();

5. Chain operation
If we now want to query the first 10 records of a User table that satisfy the status of 1, and want to sort them according to the user's creation time, The code is as follows:

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

The where, order and limit methods here are called chain operation methods. Except that the select method must be placed at the last one (because the select method is not a chain operation method), the chain operation method There is no order in which the methods of formula operations are called.

This article explains the related operations of ThinkPHP5 database. For more related content, please pay attention to the php Chinese website.

Related recommendations:

About the database and model usage of ThinkPHP5

A case study on thinkphp5.0 database operation

List some similarities and differences between ThinkPHP5 and ThinkPHP3

The above is the detailed content of Related operations on ThinkPHP5 database. For more information, please follow other related articles on the PHP Chinese website!