ThinkPHP6 数据库链式操作

• 数据库提供的链式操作方法，可以有效的提高数据存取的代码清晰度和开发效率，并且支持所有的 CURD 操作
• 带 * 标识的表示支持多次调用

以下链式操作：自学

一、表达式查询

• 表达式是 SQL 语句的条件
• 表达式不分大小写
• 表达式写在 where 里

where 查询

• where 方法在链式操作方法里面是最常用的方法，可以完成包括普通查询、表达式查询、快捷查询、区间查询、组合查询在内的条件查询操作

# 等于（=） $select = Db::table('shop_goods')->where('id','=','1')->select(); print_r($select->toArray()); # 不等于（<>） $select = Db::table('shop_goods')->where('id','<>','2')->select(); print_r($select->toArray()); # 大于（>） $select = Db::table('shop_goods')->where('id','>','3')->select(); print_r($select->toArray()); # 大于等于（>=） $select = Db::table('shop_goods')->where('id','>=','4')->select(); print_r($select->toArray()); # 小于（<） $select = Db::table('shop_goods')->where('id','<','5')->select(); print_r($select->toArray()); # 小于等于（<=） $select = Db::table('shop_goods')->where('id','<=','6')->select(); print_r($select->toArray()); # 多 where $select = Db::table('shop_goods') ->where('id','>','3') ->where('id','<','8') ->select(); print_r($select->toArray()); # LIKE $select = Db::table('shop_goods')->where('title','like','% 连衣裙 %')->select(); print_r($select->toArray()); #  NOT LIKE $select = Db::table('shop_goods')->where('title','not like','% 连衣裙 %')->select(); print_r($select->toArray()); # BETWEEN $select = Db::table('shop_goods')->where('id','between','6,10')->select(); print_r($select->toArray()); #  NOT BETWEEN $select = Db::table('shop_goods')->where('id','not between',[6,10])->select(); print_r($select->toArray()); # IN $select = Db::table('shop_goods')->where('id','in','4,7,10')->select(); print_r($select->toArray()); #  NOT IN $select = Db::table('shop_goods')->where('id','not in',[4,7,10])->select(); print_r($select->toArray());

二、数据表

1、table  和  name

# 必须完整数据库名 $select = Db::table('shop_goods')->where('id','10')->select(); print_r($select->toArray()); # 数据库未设置前缀 $select = Db::name('shop_goods')->where('id','11')->select(); print_r($select->toArray()); # 数据库设置前缀，无前缀访问 $select = Db::name('list')->where('id','12')->select(); print_r($select->toArray());

2、数据库前缀

数据库配置  database.php

return [ 'connections' => [ 'mysql' => [ // 数据库表前缀 'prefix' => Env::get('database.prefix', 'shop_'), ] ] ];

三、返回值

• field  方法主要作用是标识要返回或者操作的字段，可以用于查询和写入操作
• 所有的查询方法都可以使用 field 方法

# 字符串 $select = Db::table('shop_goods') ->field('title,price,discount as d') ->where('status',1) ->select(); print_r($select->toArray()); # 数组 $select = Db::table('shop_goods') ->field([ 'title', 'price', 'discount'=>'d' ]) ->where('status',1) ->select(); print_r($select->toArray()); # 添加，只能添加这几个字段 # 多 field $data = [ 'title' => '新商品', 'price' => 50, 'discount' => 8, 'add_time' => 1576080000 ]; $insert = Db::table('shop_goods') ->field('title') ->field('price') ->field('discount') ->field('add_time') ->insert($data); print_r($insert); # 查询全部字段，速度较快 $select = Db::table('shop_goods') ->field(true) // ->field('*') ->where('status',1) ->select(); print_r($select->toArray());

四、排序

• order  方法用于对操作的结果排序或者优先级限制

$select = Db::table('shop_goods') ->field('title,price,id') ->where('status',1) ->order('price','DESC') ->order('id','DESC') ->select(); print_r($select->toArray());

五、分页

• limit  方法主要用于指定查询和操作的数量

$select = Db::table('shop_goods') ->field('title,price,id') ->where('status',1) ->order('price','DESC') ->limit(3) ->select(); print_r($select->toArray()); $select = Db::table('shop_goods') ->field('title,price,id') ->where('status',1) ->order('price','DESC') ->limit(0,5) ->select(); print_r($select->toArray());

• page  方法主要用于分页查询

$select = Db::table('shop_goods') ->field('title,price,id') ->where('status',1) ->order('price','DESC') ->page(1,5) ->select(); print_r($select->toArray());

六、聚合查询

• 聚合方法如果没有数据，默认都是 0，聚合查询都可以配合其它查询条件

// 统计数量，参数是要统计的字段名（可选） $select = Db::table('shop_goods')->count(); print_r($select); // 获取最大值，参数是要统计的字段名（必须） $select = Db::table('shop_goods')->max('id'); print_r($select); // 获取最小值，参数是要统计的字段名（必须） $select = Db::table('shop_goods')->min('id'); print_r($select); // 获取平均值，参数是要统计的字段名（必须） $select = Db::table('shop_goods')->avg('id'); print_r($select); // 获取总数，参数是要统计的字段名（必须） $select = Db::table('shop_goods')->sum('id'); print_r($select);

七、搜索、排序示例

controller 代码

public function index(){ $title = '商城'; $login = '欧阳克'; # 左侧菜单 $menu = Db::table('shop_menu')->where('fid',0)->select(); $left = $menu->toArray(); foreach($left as &$left_v){ $left_v['lists'] = Db::table('shop_menu')->where('fid',$left_v['id'])->select(); } # 右侧列表 $param = Request::param(); if(isset($param['status']) && $param['status'] == 1){ $where['status'] = 1; }else if(isset($param['status']) && $param['status'] == 2){ $where['status'] = 2; }else{ $where = true; } $list = Db::table('shop_goods') ->where($where) ->order('add_time DESC') ->order('id DESC') ->select(); $right = $list->toArray(); foreach($right as &$right_v){ $right_v['cat'] = Db::table('shop_cat')->where('id',$right_v['cat'])->value('name'); } View::assign([ 'title' => $title, 'login' => $login, 'left' => $left, 'right' => $right, 'status' => isset($param['status']) ? $param['status'] : null ]); return View::fetch(); }

view 代码

<form class="layui-form" method="post"> <div class="layui-form-item" style="margin-top:10px;"> <div class="layui-input-inline"> <select name="status"> <option value="0" {if $status==0}selected{/if}> 全部 </option> <option value="1" {if $status==1}selected{/if}> 开启</option> <option value="2" {if $status==2}selected{/if}> 关闭</option> </select> </div> <button class="layui-btn layui-btn-primary"><i class="layui-icon">&#xe615;</i> 搜索</button> </div> </form>

八、分页示例

controller 代码

public function index(){ $title = '商城'; $login = '欧阳克'; # 左侧菜单 $menu = Db::table('shop_menu')->where('fid',0)->select(); $left = $menu->toArray(); foreach($left as &$left_v){ $left_v['lists'] = Db::table('shop_menu')->where('fid',$left_v['id'])->select(); } # 右侧列表 $param = Request::param(); if(isset($param['status']) && $param['status'] == 1){ $where['status'] = 1; }else if(isset($param['status']) && $param['status'] == 2){ $where['status'] = 2; }else{ $where = true; } $p = isset($param['p']) ? $param['p'] : 1; // 统计总数 $count = Db::table('shop_goods')->where($where)->count(); $list = Db::table('shop_goods') ->where($where) ->order('add_time DESC') ->order('id DESC') ->page($p,10) ->select(); $right = $list->toArray(); foreach($right as &$right_v){ $right_v['cat'] = Db::table('shop_cat')->where('id',$right_v['cat'])->value('name'); } View::assign([ 'title' => $title, 'login' => $login, 'left' => $left, 'right' => $right, 'count' => ceil($count/10), 'p' => $p, 'status' => isset($param['status']) ? $param['status'] : 0 ]); return View::fetch(); }

view 代码

<div class="layui-box layui-laypage layui-laypage-default"> <a href="/index.php/Index/index?p={$p-1}&status={$status}" class="layui-laypage-prev {if $p<=1}layui-disabled{/if}">上一页</a> {for start="0" end="$count"}
        {if $p == $i+1} <span class="layui-laypage-curr"> <em class="layui-laypage-em"></em> <em>{$i+1}</em> </span> {else/} <a href="/index.php/Index/index?p={$i+1}&status={$status}">{$i+1}</a> {/if}
    {/for} <a href="/index.php/Index/index?p={$p+1}&status={$status}" class="layui-laypage-next {if $p>=$count}layui-disabled{/if}">下一页</a> </div>

九、模版分页

• paginate  内置了分页实现，要给数据添加分页输出功能变得非常简单
• render  获取翻页 html 代码
• total  获取总数量

controller 代码

$select = Db::table('shop_goods')->paginate(10); print_r($select);echo '<hr>'; foreach($select as $v){ print_r($v);echo '<hr>'; } print_r($select->render());echo '<hr>'; print_r('总数：'.$select->total());echo '<hr>'; View::assign([ 'select' => $select ]); return View::fetch();

view 代码

<div>{$select|raw}</div>

css 代码

.pagination { display: inline-block; padding-left: 0; margin: 20px 0; border-radius: 4px; } .pagination > li { display: inline; } .pagination > li > a, .pagination > li > span { position: relative; float: left; padding: 6px 12px; margin-left: -1px; line-height: 1.42857143; color: #337ab7; text-decoration: none; background-color: #fff; border: 1px solid #ddd; } .pagination > li:first-child > a, .pagination > li:first-child > span { margin-left: 0; border-top-left-radius: 4px; border-bottom-left-radius: 4px; } .pagination > li:last-child > a, .pagination > li:last-child > span { border-top-right-radius: 4px; border-bottom-right-radius: 4px; } .pagination > li > a:hover, .pagination > li > span:hover, .pagination > li > a:focus, .pagination > li > span:focus { z-index: 2; color: #23527c; background-color: #eee; border-color: #ddd; } .pagination > .active > a, .pagination > .active > span, .pagination > .active > a:hover, .pagination > .active > span:hover, .pagination > .active > a:focus, .pagination > .active > span:focus { z-index: 3; color: #fff; cursor: default; background-color: #337ab7; border-color: #337ab7; } .pagination > .disabled > span, .pagination > .disabled > span:hover, .pagination > .disabled > span:focus, .pagination > .disabled > a, .pagination > .disabled > a:hover, .pagination > .disabled > a:focus { color: #777; cursor: not-allowed; background-color: #fff; border-color: #ddd; }

十、模版分页示例

controller 代码

public function index(){ $title = '商城'; $login = '欧阳克'; # 左侧菜单 $menu = Db::table('shop_menu')->where('fid',0)->select(); $left = $menu->toArray(); foreach($left as &$left_v){ $left_v['lists'] = Db::table('shop_menu')->where('fid',$left_v['id'])->select(); } # 右侧列表 $param = Request::param(); if(isset($param['status']) && $param['status'] == 1){ $where['status'] = 1; }else if(isset($param['status']) && $param['status'] == 2){ $where['status'] = 2; }else{ $where = true; } $p = isset($param['p']) ? $param['p'] : 1; # thinkphp 自带分页 $list = Db::table('shop_goods') ->where($where) ->order('add_time DESC') ->order('id DESC') ->paginate([ 'list_rows'=> 10, 'query' => Request::param() ]); $right = $list->toArray(); foreach($right as &$right_v){ $right_v['cat'] = Db::table('shop_cat')->where('id',$right_v['cat'])->value('name'); } View::assign([ 'title' => $title, 'login' => $login, 'left' => $left, 'right' => $right, 'list' => $list, 'status' => isset($param['status']) ? $param['status'] : 0 ]); return View::fetch(); }

view 代码

<div>{$paginate|raw}</div>

十一、SQL  调试

• getLastSql  输出上次执行的 sql 语句
• getLastSql  方法只能获取最后执行的  SQL  记录

$select = Db::table('shop_goods')->select(); echo Db::getLastSql();

• fetchSql  方法直接返回当前的  SQL  而不执行

$select = Db::table('shop_goods')->fetchSql()->select(); echo $select;

十二、动态配置数据库

• config 目录 database.php 文件

return [ 'connections' => [ 'ouyangke' => [ // 数据库类型 'type' => Env::get('database.type', 'mysql'), // 服务器地址 'hostname' => Env::get('database.hostname', '127.0.0.1'), // 数据库名 'database' => 'ouyangke', // 用户名 'username' => Env::get('database.username', 'root'), // 密码 'password' => Env::get('database.password', 'root'), // 端口 'hostport' => Env::get('database.hostport', '3306'), // 数据库连接参数 'params' => [], // 数据库编码默认采用 utf8 'charset' => Env::get('database.charset', 'utf8'), // 数据库表前缀 'prefix' => Env::get('database.prefix', 'shop_'), // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器) 'deploy' => 0, // 数据库读写是否分离 主从式有效 'rw_separate' => false, // 读写分离后 主服务器数量 'master_num' => 1, // 指定从服务器序号 'slave_no' => '', // 是否严格检查字段是否存在 'fields_strict' => true, // 是否需要断线重连 'break_reconnect' => false, // 监听 SQL 'trigger_sql' => true, // 开启字段缓存 'fields_cache' => false, // 字段缓存路径 'schema_cache_path' => app()->getRuntimePath() . 'schema' . DIRECTORY_SEPARATOR, ] ] ];

• ouyangke 数据库中的 shop_user 表

CREATE TABLE `shop_user` ( `uid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户 ID', `account` varchar(50) NOT NULL COMMENT '账户', `password` char(32) NOT NULL COMMENT '密码', `name` varchar(50) NOT NULL COMMENT '姓名', `status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1 开启 2 关闭', `add_time` int(10) unsigned NOT NULL COMMENT '添加时间', PRIMARY KEY (`uid`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='后台管理员';

• connect  方法动态配置数据库连接信息

Db::connect('ouyangke')->table('shop_user')->select();

connect 方法必须在查询的最开始调用，而且必须紧跟着调用查询方法，否则可能会导致部分查询失效或者依然使用默认的数据库连接