准备工作：见php语法篇末尾环境部署
一、【创建一个表+插入数据】：手动创建或者执行sql语句
DROP TABLE IF EXISTS `tb_business`; CREATE TABLE IF NOT EXISTS `tb_business` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `category_id` int(10) UNSIGNED DEFAULT NULL COMMENT '品类id(tb_category)', `ename` varchar(64) NOT NULL COMMENT '英文名', `cname` varchar(64) NOT NULL COMMENT '中文名', PRIMARY KEY (`id`), UNIQUE KEY `business_cname_unique` (`cname`), UNIQUE KEY `business_ename_unique` (`ename`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = '业务数据';
// 注意：// 如果主键设置了自增 末尾不必再设置AUTO_INCREMENT 或者 AUTO_INCREMENT = 0
INSERT INTO `tb_business` (`category_id`, `ename`, `cname`) VALUES (1, 'lol', '英雄联盟'), (7, 'sugar', '糖罐');
二、【新建路由、获取请求参数、中间件处理逻辑、sql表db数据操作等文件】：
Route::post('activityType/get', 'ProjectControllerActivityTypeActivityType@getActivityType');//活动列表
<?php namespace AppHttpControllersProjectControllerBusinessManage;//命名空间 use AppHttpControllersProjectModelBusinessManageBusinessList;//间接操作 use AppHttpControllersProjectModelJsonRequest; //获取请求参数 class BusinessInfo { //构建实例 private $json_request; private $businessInfo; public function __construct() { // $this->json_request = new JsonRequest();//废弃 $this->json_request = resolve(JsonRequest::class); $this->businessInfo = new BusinessList();//统一请求，定义在Database外部 } //获取 public function getBusinessInfo() { $res = $this->businessInfo->getTbBusinessInfo(); return response()->success($res); } //查询某个业务 public function searchBusinessInfo() { $id = $this->json_request->get('id'); $res = $this->businessInfo->searchTbBusinessInfo($id); return response()->success($res); } //新建 public function addBusinessInfo() { $category_id = $this->json_request->get('category_id'); $ename = $this->json_request->get('ename'); $cname = $this->json_request->get('cname'); $res = $this->businessInfo->addTbBusinessInfo($category_id,$ename,$cname); return response()->success($res); } //更新 public function updateBusinessInfo() { $id = $this->json_request->get('id'); $category_id = $this->json_request->get('category_id'); $ename = $this->json_request->get('ename'); $cname = $this->json_request->get('cname'); $res = $this->businessInfo->updateTbBusinessInfo($id,$category_id,$ename,$cname); return response()->success($res); } //删除 public function delBusinessInfo() { $id = $this->json_request->get('id'); $res = $this->businessInfo-> delTbBusinessInfo($id); return response()->success($res); } }
<?php namespace AppHttpControllersProjectModelBusinessManage; use AppHttpControllersProjectModelBusinessManageDatabaseTbBusinessInfo; use AppHttpControllersProjectModelActivityManageLogicActivityList; use AppHttpControllersProjectModelCommonProtocolErrorsLogicError; class BusinessList { private $tbBusinessInfo; public function __construct() { $this->tbBusinessInfo = new TbBusinessInfo(); } /** * 获取 * @param string * @return array **/ public function getTbBusinessInfo() { $info = $this->tbBusinessInfo->getBusinessInfo(); return $info; } /** * 查询 * @param string * @return array **/ public function searchTbBusinessInfo($business_id) { $info = $this->tbBusinessInfo->searchBusinessInfo($business_id); return $info; } /** * 新增 * @param string * @return array **/ public function addTbBusinessInfo($category_id,$business_ename,$business_cname) { $info = $this->tbBusinessInfo->addBusinessInfo($category_id,$business_ename,$business_cname); return $info; } /** * 更新 * @param string * @return array **/ public function updateTbBusinessInfo($business_id,$category_id,$business_ename,$business_cname) { $info = $this->tbBusinessInfo->updateBusinessInfo($business_id,$category_id,$business_ename,$business_cname); return $info; } /** * 删除 * @param string * @return array **/ public function delTbBusinessInfo($business_id) { $activityClass = resolve_class(ActivityList::class); $activityList = $activityClass::search(["activity.business.id"=>$business_id]); if ( !$activityList->isEmpty() ) { throw new Error("有活动引用，需要先删除活动"); }else{ return $this->tbBusinessInfo->delBusinessInfo($business_id); } } } ?>
放在Database文件夹的文件才是操作表数据的直接操作：写法就是laravel中的Eloquent的类封装
Eloquent: 关联
https://learnku.com/docs/laravel/5.7/eloquent-relationships/2295
<?php namespace AppHttpControllersProjectModelBusinessManageDatabase; use AppHttpControllersProjectModelCommonDatabaseEloBusinessBaseDatabase; //父类 class TbBusinessInfo extends EloBusinessBaseDatabase { //表名 protected $table = "tb_business"; //主键名 protected $primaryKey = "id"; //主键是自增id public $incrementing = true; //所有字段可以批量修改 protected $guarded = []; /** * 获取业务信息 * @param int * @return array **/ public function getBusinessInfo() { return static::all(); } /** * 查询 * @param int * @return array **/ public function searchBusinessInfo($business_id) { if(!$business_id){ return "参数不可为空"; } else { return static::where("id", $business_id)->first(); } } /** * 添加业务信息 * @param int * @return array **/ public function addBusinessInfo($category_id,$business_ename,$business_cname) { $info = []; $info["category_id"] = $category_id; $info["ename"] = $business_ename; $info["cname"] = $business_cname; if(!$category_id || !$business_ename || !$business_cname){ return "请求参数不全"; } else { return static::create($info) } } /** * 更新 * @param int id * @return object **/ public function updateBusinessInfo($business_id, $category_id,$business_ename,$business_cname) { $info = []; $info["id"] = $business_id; $info["category_id"] = $category_id; $info["ename"] = $business_ename; $info["cname"] = $business_cname; if(!$business_id || !$category_id || !$business_ename || !$business_cname){ return "请求参数不全"; } else { return static::where( "id", $business_id)->update($info); } } /** * 删除业务信息 * @param int * @return array **/ public function delBusinessInfo($business_id) { if(!$business_id){ return "参数不可为空"; } else { return static::where( "id", $business_id)->delete(); } } } ?>
以下是常用表操作方法总结：
<?php namespace AppHttpControllersProjectModelBusinessInfoDatabase; use AppHttpControllersProjectModelCommonDatabaseEloBusinessBaseDatabase; //父类 class TbBusinessInfo extends EloBusinessBaseDatabase { //表名 protected $table = "tb_business"; //主键名 protected $primaryKey = "id"; //主键是自增id public $incrementing = true; //所有字段可以批量修改 protected $guarded = []; /** * 获取业务信息 * @param int * @return array **/ public function getBusinessInfo() { // return static::all();// 不能与where()共存 return static::all()->groupBy('category_id');// 分组返回 // return static::select('cname')->get();// 只返回某些字段 // return static::distinct()->get();//去重返回 // return static::count();// 返回总条数，max('xxx')某参数最大，min最小， avg平均，还有 sum求和 // return static::orderBy('cname')->get();// 排序返回 // return static::pluck('cname');// 返回指定内容index:value;或者（value,key）如：lol:英雄联盟 // return static::get();// 可以与where()共存 // return static::first();// 获取第一条 } /** * 查询业务信息 * @param int * @return array **/ public function searchBusinessInfo($id) { if ($id){ return static::where("category_id", $id)->get();//->first();返回指定范围where() // return static::where([ //多个条件 // ['status', '=', '1'], // ['subscribed', '<>', '1'], // ])->get(); // return static::select(static::raw('count(*) as user_count, status')); //原生表达式:DB::raw // return static::->selectRaw('price * ? as price_with_tax', [1.0825]); //selectRaw()方法: // return static::->whereRaw('price > IF(state = "TX", ?, 100)', [200]); //绑定参数的数组 // return static::->havingRaw('SUM(price) > ?', [2500]); //是否 // return static::->orderByRaw('updated_at - created_at DESC'); // return static::where("id", $id)->value('cname');//直接返回某个字符串 // return static::select("category_id", "cname")->where("category_id", $id)->orderBy('cname')->take(10)->get(); // return static::select("id", "ename", "cname")->where("id", $id)->get(); // return static::where("category_id", $id)->get()->map(function ($item){ // $item->data = '王者荣耀666'; // return $item; // }); // return static::findOrFail($id); }else{ return null; } } /** * 添加业务信息 * @param int * @return array **/ public function addBusinessInfo($category_id,$business_ename,$business_cname) { $info = []; $info["category_id"] = $category_id; $info["ename"] = $business_ename; $info["cname"] = $business_cname; return static::create( $info);// 返回数组 // return static::insert( //插入内容，返回true //// ['category_id' => $category_id, 'ename' => $business_ename, 'cname' => $business_cname], // ['category_id' => $category_id, 'ename' => $business_ename, 'cname' => $business_cname] // ); } /** * 添加业务信息 * @param int * @return array **/ public function updateBusinessInfo($id,$business_cname) { // $info = array( // "cname" => $business_cname // ); // $info -> cname = $business_cname;//错误：对象或函数 $info = []; $info["cname"] = $business_cname; if(!$id || !$business_cname){ return '参数不全'; }else{ static::where( "id", $id) // ->update(['votes' => 1]) // 修改某个字符串或数字 // ->update(['options->enabled' => true]) //修改json某个字段 ->update($info); // 修改当前行所有参数 return $info; } } /** * 删除业务信息 * @param int * @return array **/ public function delBusinessInfo($business_id) { return static::where( "id", $business_id) // ->truncate() //清空表 // ->where('votes', '>', 100) //限制一个范围 ->delete();// 直接删除 } } ?>
调试问题：连接到本地库，执行PHPstorm的测试文档
<?php use TestsTestCase; use AppHttpControllersProjectModelBusinessInfoDatabaseTbBusinessInfo; class TestDemo extends TestCase { public function testBasicTest() { $res = new TbBusinessInfo(); $info['error_code'] = '0'; $info['data'] = $res->getBusinessInfo()->toArray();// 使用时不需要转换成简单的数组格式->toArray() // $info['data'] = $res->searchBusinessInfo('1')->toArray(); // $info['data'] = $res->addBusinessInfo('2','test03','测试03')->toArray(); // $info['data'] = $ai->updateBusinessInfo('1','英雄联盟'); // dd(redirect('tb_business'));// 整个表数据 dd($info);// 打印测试数据 } }