thinkphp之数据库操作

是天呀

于 2023-09-25 21:51:24 发布

阅读量130

点赞数

分类专栏： thinphp fasetadmin php 文章标签：数据库 php

本文链接：https://blog.csdn.net/LTFLTFLTFLTFLTFL/article/details/133278845

版权

php 同时被 3 个专栏收录

13 篇文章 0 订阅

订阅专栏

thinphp

5 篇文章 0 订阅

订阅专栏

fasetadmin

5 篇文章 0 订阅

订阅专栏

thinkphp之数据库操作

连接数据库
基本使用
查询构造器

连接数据库

一、配置文件定义

常用的配置方式是在应用目录或者模块目录下面的 database.php 中

<?php

use think\Env;

return [
    // 数据库类型
    'type'            => Env::get('database.type', 'mysql'),
    // 服务器地址
    'hostname'        => Env::get('database.hostname', '127.0.0.1'),
    // 数据库名
    'database'        => Env::get('database.database', 'FastAdmin'),
    // 用户名
    'username'        => Env::get('database.username', 'root'),
    // 密码
    'password'        => Env::get('database.password', ''),
    // 端口
    'hostport'        => Env::get('database.hostport', ''),
    // 连接dsn
    'dsn'             => '',
    // 数据库连接参数
    'params'          => [],
    // 数据库编码默认采用 utf8mb4
    'charset'         => Env::get('database.charset', 'utf8mb4'),
    // 数据库表前缀
    'prefix'          => Env::get('database.prefix', 'fa_'),
    // 数据库调试模式
    'debug'           => Env::get('database.debug', false),
    // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
    'deploy'          => 0,
    // 数据库读写是否分离 主从式有效
    'rw_separate'     => false,
    // 读写分离后 主服务器数量
    'master_num'      => 1,
    // 指定从服务器序号
    'slave_no'        => '',
    // 是否严格检查字段是否存在
    'fields_strict'   => true,
    // 数据集返回类型
    'resultset_type'  => 'array',
    // 自动写入时间戳字段
    'auto_timestamp'  => false,
    // 时间字段取出后的默认时间格式,默认为Y-m-d H:i:s
    'datetime_format' => false,
    // 是否需要进行SQL性能分析
    'sql_explain'     => false,
];

二、方法配置

我们可以在调用Db类的时候动态定义连接信息

//直接定义一个Db类
Db::connect([
    // 数据库类型
    'type'        => 'mysql',
    // 数据库连接DSN配置
    'dsn'         => '',
    // 服务器地址
    'hostname'    => '127.0.0.1',
    // 数据库名
    'database'    => 'thinkphp',
    // 数据库用户名
    'username'    => 'root',
    // 数据库密码
    'password'    => '',
    // 数据库连接端口
    'hostport'    => '',
    // 数据库连接参数
    'params'      => [],
    // 数据库编码默认采用utf8
    'charset'     => 'utf8',
    // 数据库表前缀
    'prefix'      => 'think_',
]);


//或者使用字符串方式：
Db::connect('mysql://root:1234@127.0.0.1:3306/thinkphp#utf8');


//字符串连接的定义格式为：
//数据库类型:	 用户名:密码@数据库地址:数据库端口/数据库名#字符集

三、配置参数参考

参数名	描述	默认值
type	数据库类型	无
hostname	数据库地址	127.0.0.1
database	数据库名称	无
username	数据库用户名	无
password	数据库密码	无
hostport	数据库端口号	无
dsn	数据库连接dsn信息	无
params	数据库连接参数	空
charset	数据库编码	utf8
prefix	数据库的表前缀	无
debug	是否调试模式	false
deploy	数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)	0
rw_separate	数据库读写是否分离主从式有效	false
master_num	读写分离后主服务器数量	1
slave_no	指定从服务器序号	无
fields_strict	是否严格检查字段是否存在	true
resultset_type	数据集返回类型	array
auto_timestamp	自动写入时间戳字段	false
sql_explain	是否需要进行SQL性能分析开启调试有效	false
query	指定查询对象	think\db\Query
builder	指定数据库Builder对象	无

基本使用

配置了数据库连接信息后，我们就可以直接使用数据库运行原生SQL操作了，支持query（查询操作）和execute（写入操作）方法，并且支持参数绑定。

//直接使用query（查询操作）和execute（写入操作）方法
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']);
//可以使用多个数据库连接，使用

Db::connect($config)->query('select * from think_user where id=:id',['id'=>8]);
//$config是一个单独的数据库配置，支持数组和字符串，也可以是一个数据库连接的配置参数名。

查询构造器

基本查询

查询一个数据使用

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

查询数据集使用

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

不写数据表前缀的方法

//如果设置了数据表前缀参数的话，可以使用
Db::name('user')->where('id',1)->find();
Db::name('user')->where('status',1)->select();
//如果你的数据表没有使用表前缀功能，那么name和table方法的一样的效果。

在find和select方法之前可以使用所有的链式操作方法。
默认情况下，find和select方法返回的都是数组。

助手函数

//系统提供了一个db助手函数，可以更方便的查询：
db('user')->where('id',1)->find();
db('user')->where('status',1)->select();

值和列查询

查询某个字段的值可以用

// 返回某个字段的值
Db::table('think_user')->where('id',1)->value('name');
//value 方法查询结果不存在，返回 null

查询某一列的值可以用

// 返回数组
Db::table('think_user')->where('status',1)->column('name');
// 指定索引
Db::table('think_user')->where('status',1)->column('name','id');
Db::table('think_user')->where('status',1)->column('id,name'); // 同tp3的getField
//column 方法查询结果不存在，返回空数组

添加数据

添加一条数据

//使用 Db 类的 insert 方法向数据库提交数据

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

//如果你在database.php配置文件中配置了数据库前缀(prefix)，那么可以直接使用 Db 类的 name 方法提交数据

Db::name('user')->insert($data);
//insert 方法添加数据成功返回添加成功的条数，insert 正常情况返回 1

//添加数据后如果需要返回新增数据的自增主键，可以使用getLastInsID方法：

Db::name('user')->insert($data);
$userId = Db::name('user')->getLastInsID();
//或者直接使用insertGetId方法新增数据并返回主键值：

Db::name('user')->insertGetId($data);
//insertGetId 方法添加数据成功返回添加数据的自增主键

添加多条数据

//添加多条数据直接向 Db 类的 insertAll 方法传入需要添加的数据即可

$data = [
    ['foo' => 'bar', 'bar' => 'foo'],
    ['foo' => 'bar1', 'bar' => 'foo1'],
    ['foo' => 'bar2', 'bar' => 'foo2']
];
Db::name('user')->insertAll($data);

助手函数

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

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

更新数据

更新数据表中的数据

//更新数据表中的数据
Db::table('think_user')->where('id', 1)->update(['name' => 'thinkphp']);
//如果数据中包含主键，可以直接使用：

Db::table('think_user')->update(['name' => 'thinkphp','id'=>1]);
//update 方法返回影响数据的条数，没修改任何数据返回 0

//如果要更新的数据需要使用SQL函数或者其它字段，可以使用下面的方式：
Db::table('think_user')
    ->where('id', 1)
    ->update([
        'login_time'  => ['exp','now()'],
        'login_times' => ['exp','login_times+1'],
    ]);

更新某个字段的值

//更新某个字段的值：
Db::table('think_user')->where('id',1)->setField('name', 'thinkphp');
//setField 方法返回影响数据的条数，没修改任何数据字段返回 0

自增或自减一个字段的值

setInc/setDec 如不加第二个参数，默认值为1

// score 字段加 1
Db::table('think_user')->where('id', 1)->setInc('score');
// score 字段加 5
Db::table('think_user')->where('id', 1)->setInc('score', 5);
// score 字段减 1
Db::table('think_user')->where('id', 1)->setDec('score');
// score 字段减 5
Db::table('think_user')->where('id', 1)->setDec('score', 5);

延迟更新

setInc/setDec支持延时更新，如果需要延时更新则传入第三个参数

Db::table('think_user')->where('id', 1)->setInc('score', 1, 10);
//setInc/setDec 方法返回影响数据的条数

助手函数

// 更新数据表中的数据
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');

删除数据

删除数据表中的数据

delete 方法返回影响数据的条数，没有删除返回 0

// 根据主键删除
Db::table('think_user')->delete(1);
Db::table('think_user')->delete([1,2,3]);

// 条件删除    
Db::table('think_user')->where('id',1)->delete();
Db::table('think_user')->where('id','<',10)->delete();

助手函数

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

查询方法

where方法

//可以使用where方法进行AND条件查询：
Db::table('think_user')
    ->where('name','like','%thinkphp')
    ->where('status',1)
    ->find();
    
//多字段相同条件的AND查询可以简化为如下方式：
Db::table('think_user')
    ->where('name&title','like','%thinkphp')
    ->find();

whereOr方法

//使用whereOr方法进行OR查询：
Db::table('think_user')
    ->where('name','like','%thinkphp')
    ->whereOr('title','like','%thinkphp')
    ->find();

//多字段相同条件的OR查询可以简化为如下方式：
Db::table('think_user')
    ->where('name|title','like','%thinkphp')
    ->find();

混合查询(闭包函数)

where方法和whereOr方法在复杂的查询条件中经常需要配合一起混合使用

$result = Db::table('think_user')->where(function ($query) {
    $query->where('id', 1)->whereor('id', 2);
})->whereOr(function ($query) {
    $query->where('name', 'like', 'think')->whereOr('name', 'like', 'thinkphp');
})->select();

//生成的sql语句类似于下面：
SELECT * FROM `think_user` WHERE  (  `id` = 1 OR `id` = 2 ) OR (  `name` LIKE 'think' OR `name` LIKE 'thinkphp' )
//注意闭包查询里面的顺序，而且第一个查询方法用where或者whereOr是没有区别的。

查询表达式

查询表达式的使用格式：表达式不分大小写*

where('字段名','表达式','查询条件');
whereOr('字段名','表达式','查询条件');

表达式	含义
EQ、=	等于（=）
NEQ、<>	不等于（<>）
GT、>	大于（>）
EGT、>=	大于等于（>=）
LT、<	小于（<）
ELT、<=	小于等于（<=）
LIKE	模糊查询
[NOT] BETWEEN	（不在）区间查询
[NOT] IN	（不在）IN 查询
[NOT] NULL	查询字段是否（不）是NULL
[NOT] EXISTS	EXISTS查询
EXP	表达式查询，支持SQL语法
> time	时间比较
< time	时间比较
between time	时间比较
notbetween	time 时间比较

EXP：表达式

//支持更复杂的查询情况 例如：
where('id','in','1,3,8');


//也可以改成：
where('id','exp',' IN (1,3,8) ');
// exp 查询的条件不会被当成字符串，所以后面的查询条件可以使用任何SQL支持的语法，包括使用函数和字段名称。

链式操作

alias

alias用于设置当前数据表的别名，便于使用其他的连贯操作例如join方法等

Db::table('think_user')->alias('a')->join('__DEPT__ b ','b.user_id= a.id')->select();

//最终生成的SQL语句类似于：
SELECT * FROM think_user a INNER JOIN think_dept b ON b.user_id= a.id

field

field方法属于模型的连贯操作方法之一，主要目的是标识要返回或者操作的字段，可以用于查询和写入操作

用于查询

指定字段

//在查询操作中field方法是使用最频繁的。
Db::table('think_user')->field('id,title,content')->select();

//这里使用field方法指定了查询的结果集中包含id,title,content三个字段的值。执行的SQL相当于：

SELECT id,title,content FROM table
//可以给某个字段设置别名，例如：
Db::table('think_user')->field('id,nickname as name')->select();

//执行的SQL语句相当于：
SELECT id,nickname as name FROM table

使用SQL函数

//可以在field方法中直接使用函数，例如：
Db::table('think_user')->field('id,SUM(score)')->select();

//执行的SQL相当于：
SELECT id,SUM(score) FROM table
//除了select方法之外，所有的查询方法，包括find等都可以使用field方法。

使用数组参数

//field方法的参数可以支持数组，例如：
Db::table('think_user')->field(['id','title','content'])->select();
//最终执行的SQL和前面用字符串方式是等效的。

//数组方式的定义可以为某些字段定义别名，例如：
Db::table('think_user')->field(['id','nickname'=>'name'])->select();

//执行的SQL相当于：
SELECT id,nickname as name FROM table

//对于一些更复杂的字段要求，数组的优势则更加明显，例如：
Db::table('think_user')->field(['id','concat(name,"-",id)'=>'truename','LEFT(title,7)'=>'sub_title'])->select();

//执行的SQL相当于：
SELECT id,concat(name,'-',id) as truename,LEFT(title,7) as sub_title FROM table

获取所有字段

//如果有一个表有非常多的字段，需要获取所有的字段（这个也许很简单，因为不调用field方法或者直接使用空的field方法都能做到）：
Db::table('think_user')->select();
Db::table('think_user')->field('*')->select();

//上面的用法是等效的，都相当于执行SQL：
SELECT * FROM table

//但是这并不是我说的获取所有字段，而是显式的调用所有字段（对于对性能要求比较高的系统，这个要求并不过分，起码是一个比较好的习惯），下面的用法可以完成预期的作用：
Db::table('think_user')->field(true)->select();
//field(true)的用法会显式的获取数据表的所有字段列表，哪怕你的数据表有100个字段。

字段排除

注意的是字段排除功能不支持跨表和join操作。

//如果我希望获取排除数据表中的content字段（文本字段的值非常耗内存）之外的所有字段值，我们就可以使用field方法的排除功能，例如下面的方式就可以实现所说的功能：
Db::table('think_user')->field('content',true)->select();

//则表示获取除了content之外的所有字段，要排除更多的字段也可以：
Db::table('think_user')->field('user_id,content',true)->select();
//或者用
Db::table('think_user')->field(['user_id','content'],true)->select();

用于写入

除了查询操作之外，field方法还有一个非常重要的安全功能--字段合法性检测。

Db::table('think_user')->field('title,email,content')->insert($data);
//即表示表单中的合法字段只有title,email和content字段，无论用户通过什么手段更改或者添加了浏览器的提交字段，都会直接屏蔽。

order

order方法属于模型的连贯操作方法之一，用于对操作的结果排序。

Db::table('think_user')->where('status=1')->order('id desc')->limit(5)->select();
//注意：连贯操作方法没有顺序，可以在select方法调用之前随便改变调用顺序。

//支持对多个字段的排序，例如：
Db::table('think_user')->where('status=1')->order('id desc,status')->limit(5)->select();
//如果没有指定desc或者asc排序规则的话，默认为asc。

//如果你的字段和mysql关键字有冲突，那么建议采用数组方式调用，例如：
Db::table('think_user')->where('status=1')->order(['order','id'=>'desc'])->limit(5)->select();

limit

limit方法也是模型类的连贯操作方法之一，主要用于指定查询和操作的数量，特别在分页查询的时候使用较多

限制结果数量

//例如获取满足要求的10个用户，如下调用即可：
Db::table('think_user')->where('status=1')->field('id,name')->limit(10) ->select();
    
//limit方法也可以用于写操作，例如更新满足要求的3条数据：
Db::table('think_user')->where('score=100')->limit(3)->update(['level'=>'A']);

分页查询

用于文章分页查询是limit方法比较常用的场合，例如：

Db::table('think_article')->limit('10,25')->select();
//表示查询文章数据，从第10行开始的25条数据（可能还取决于where条件和order排序的影响 这个暂且不提）。

//你也可以这样使用，作用是一样的：
Db::table('think_article')->limit(10,25)->select();
//对于大数据表，尽量使用limit限制查询结果，否则会导致很大的内存开销和性能问题。

page

page方法也是模型的连贯操作方法之一，是完全为分页查询而诞生的一个人性化操作方法。

// 查询第一页数据
Db::table('think_article')->page('1,10')->select(); 
// 查询第二页数据
Db::table('think_article')->page('2,10')->select(); 
//显而易见的是，使用page方法你不需要计算每个分页数据的起始位置，page方法内部会自动计算。

//和limit方法一样，page方法也支持2个参数的写法，例如：
Db::table('think_article')->page(1,10)->select();
// 和下面的用法等效
Db::table('think_article')->page('1,10')->select();
//page方法还可以和limit方法配合使用，例如：

Db::table('think_article')->limit(25)->page(3)->select();
//当page方法只有一个值传入的时候，表示第几页，而limit方法则用于设置每页显示的数量，也就是说上面的写法等同于：

Db::table('think_article')->page('3,25')->select();

group

GROUP方法也是连贯操作方法之一，通常用于结合合计函数，根据一个或多个列对结果集进行分组。
group方法只有一个参数，并且只能使用字符串。

//例如，我们都查询结果按照用户id进行分组统计：
Db::table('think_user')
    ->field('user_id,username,max(score)')
    ->group('user_id')
    ->select();
    
//生成的SQL语句是：
SELECT user_id,username,max(score) FROM think_score GROUP BY user_id

//也支持对多个字段进行分组，例如：
Db::table('think_user')
    ->field('user_id,test_time,username,max(score)')
    ->group('user_id,test_time')
    ->select();

//生成的SQL语句是：
SELECT user_id,test_time,username,max(score) FROM think_score GROUP BY user_id,test_time

having

HAVING方法也是连贯操作之一，用于配合group方法完成从分组的结果中筛选（通常是聚合条件）数据。
having方法只有一个参数，并且只能使用字符串，

Db::table('think_user')
    ->field('username,max(score)')
    ->group('user_id')
    ->having('count(test_time)>3')
    ->select();
     
//生成的SQL语句是：
SELECT username,max(score) FROM think_score GROUP BY user_id HAVING count(test_time)>3

join

join通常有下面几种类型，不同类型的join操作会影响返回的数据结果。

INNER JOIN: 等同于 JOIN（默认的JOIN类型）,如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

JOIN方法也是连贯操作方法之一，用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

join 支持三种写法：

写法1：[ ‘完整表名或者子查询’=>‘别名’ ]
写法2：‘完整表名别名’
写法3：‘不带数据表前缀的表名’

语法

object join ( mixed join [, mixed $condition = null [, string $type = 'INNER']] )

//condition
//关联条件。可以为字符串或数组， 为数组时每一个元素都是一个关联条件。

//type
//关联类型。可以为:INNER、LEFT、RIGHT、FULL，不区分大小写，默认为INNER。

Db::table('think_artist')
->alias('a')
->join('think_work w','a.id = w.artist_id')
->join('think_card c','a.card_id = c.id')
->select();

表达式查询

Db::table('think_user')
    ->where('id','>',1)
    ->where('name','thinkphp')
    ->select();

fetchSql

fetchSql用于直接返回SQL而不是执行查询，适用于任何的CURD操作方法

$result = Db::table('think_user')->fetchSql(true)->find(1);
//输出result结果为： SELECT * FROM think_user where id = 1

子查询

1、使用select方法

//当select方法的参数为false的时候，表示不进行查询只是返回构建SQL，例如：
$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id','>',10)
    ->select(false); 

//生成的subQuery结果为：
SELECT `id`,`name` FROM `think_user` WHERE `id` > 10

2、使用fetchSql方法

//fetchSql方法表示不进行查询而只是返回构建的SQL语句，并且不仅仅支持select，而是支持所有的CURD查询。
$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id','>',10)
    ->fetchSql(true)
    ->select();

//生成的subQuery结果为：
SELECT `id`,`name` FROM `think_user` WHERE `id` > 10

3、使用buildSql构造子查询

//调用buildSql方法后不会进行实际的查询操作，而只是生成该次查询的SQL语句（为了避免混淆，会在SQL两边加上括号），然后我们直接在后续的查询中直接调用。
$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id','>',10)
    ->buildSql();

//生成的subQuery结果为：
( SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 )

需要注意的是，使用前两种方法需要自行添加‘括号’。

Db::table($subQuery.' a')
    ->where('a.name','like','thinkphp')
    ->order('id','desc')
    ->select();

//生成的SQL语句为：
SELECT * FROM ( SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 ) a WHERE a.name LIKE 'thinkphp' ORDER BY `id` desc

4、使用闭包构造子查询

IN/NOT IN和EXISTS/NOT EXISTS之类的查询可以直接使用闭包作为子查询

Db::table('think_user')
->where('id','IN',function($query){
    $query->table('think_profile')->where('status',1)->field('id');
})
->select();

//生成的SQL语句是
SELECT * FROM `think_user` WHERE `id` IN ( SELECT `id` FROM `think_profile` WHERE `status` = 1 )
Db::table('think_user')
->where(function($query){
    $query->table('think_profile')->where('status',1);
},'exists')
->find();

//生成的SQL语句为
SELECT * FROM `think_user` WHERE EXISTS ( SELECT * FROM `think_profile` WHERE `status` = 1 )

事务操作

使用事务处理的话，需要数据库引擎支持事务处理。比如 MySQL 的 MyISAM 不支持事务处理，需要使用 InnoDB 引擎。

事务处理分为两种自动和手动

自动控制事务处理

使用 transaction 方法操作数据库事务，当发生异常会自动回滚

Db::transaction(function(){
    Db::table('think_user')->find(1);
    Db::table('think_user')->delete(1);
});

手动控制事务

// 启动事务
Db::startTrans();
try{
    Db::table('think_user')->find(1);
    Db::table('think_user')->delete(1);
    // 提交事务
    Db::commit();    
} catch (\Exception $e) {
    // 回滚事务
    Db::rollback();
}