综合实战第四课：laravel基础3-PHP培训九期线上班

数据库查询

where

对象变数组

echo '<pre>';
        $res = DB::table('product')->where('id',2)->get()->toArray();
        foreach ($res as $item){
            $item = (array)$item;
            print_r($item);
        }

一维数组

 echo '<pre>';
        $res = DB::table('product')->where('id',2)->get()->toArray();
        foreach ($res as $key => $item){
            $res[$key] = (array)$item;
        }
        print_r($res);

二维数组

whereBetween

区间查询，id在4和6之间的，包括4和6

 echo '<pre>';
        $res = DB::table('product')->whereBetween('id',[4,6])->get()->toArray();
        foreach ($res as $key => $item){
            $res[$key] = (array)$item;
        }
        print_r($res);

whereIn

数据库查询尽量用in查询，杜绝用or，严重影响性能。in里面也不能太多

echo '<pre>';
        $res = DB::table('product')->whereIn('id', [4, 5, 6, 7])->get()->toArray();
        foreach ($res as $key => $item) {
            $res[$key] = (array)$item;
        }
        print_r($res);

orWhere

echo '<pre>';
        $res = DB::table('product')->orWhere('id','=',3)->orWhere('id','=',5)->get()->toArray();
        foreach ($res as $key => $item) {
            $res[$key] = (array)$item;
        }
        print_r($res);

指定字段select

$res = DB::table('product')->select('id','pname','price')->whereIn('id',[3,4,5,7,9])->get()->toArray();
        foreach ($res as $key => $item) {
            $res[$key] = (array)$item;
        }
        print_r($res);

聚合

count

$res = DB::table('product')->count();
        print_r($res);

max

 $res = DB::table('product')->max('price');
        print_r($res);

min

$res = DB::table('product')->min('price');
        print_r($res);

avg

求平均值需要扫描表，对性能造成很大的影响

$res = DB::table('product')->avg('price');
        print_r($res);

sum

leftJoin

对数据库性能影响很大，包括join，rightjoin，尽量优化掉

$res = DB::table('product')->leftJoin('category','product.category_id','=','category.cid')->select('product.id','product.pname','product.price','category.cname','product.category_id as productid','category.cid as cateid' )->get()->toArray();
        foreach ($res as $key => $item) {
            $res[$key] = (array)$item;
        }
       print_r($res);

化解leftJoin

方式一

有个foreach循环，采用插入数组新字段的方法化解，频繁查询数据库，并不完美

$res = DB::table('product')->get()->toArray();
//        拿出product的所有数据
        foreach ($res as $key => $val){
            $cate = DB::table('category')->where('cid',$val->category_id)->first();
//            循环category表中cid=product中category_id的数据，每次一条
            $res[$key]->cate_name = $cate->cname;
//            在product数据中强行插入一个key为cate_name的字段，值为上面的循环
        }
        foreach ($res as $key => $item) {
            $res[$key] = (array)$item;
        }
        return view('product.lists',['products'=>$res]);

<table>
    <thead>
    <tr>
        <th>cname</th>
        <th>pname</th>
        <th>price</th>
    </tr>
    </thead>
    <tbody>
    @foreach($products as $product)
    <tr>
        <td>{{$product['cname']}}</td>
        <td>{{$product['pname']}}</td>
        <td>{{$product['price']}}</td>
    </tr>
    @endforeach
    </tbody>
</table>

方式二

采用返回给view2个结果集，结果集互相调用

 $res = DB::table('product')->get()->toArray();
$cname = DB::table('category')->get()->toArray();
        $cates = [];
//        定义一个新的数组
        foreach ($cname as $val){
            $cates[$val->cid] = $val->cname;
//            构造一个一维维数组，下标就是cid，值是cname
//            $cates[$val->cid]['cname'] = $val->cname;
//            构造一个二维维数组
        }
        foreach ($res as $key => $item) {
            $res[$key] = (array)$item;
        }
        return view('product.lists',['products'=>$res],['cates'=>$cates]);

<table>
    <thead>
    <tr>
        <th>cname</th>
        <th>pname</th>
        <th>price</th>
    </tr>
    </thead>
    <tbody>
    @foreach($products as $product)
    <tr>
        <td>{{$cates[$product['category_id']]}}</td>
//      <td>{{$cates[$product['category_id']]['cname']}}</td>
        <td>{{$product['pname']}}</td>
        <td>{{$product['price']}}</td>
    </tr>
    @endforeach
    </tbody>
</table>

数据库插入

DB::table('category')->insert(['cid'=>4,'cname'=>'电脑']);

insertGetId

如果数据表有自增 ID ，使用 insertGetId 方法来插入记录并返回 ID 值

$res = DB::table('category')->insertGetId(['cid'=>5,'cname'=>'水果']);
        var_dump($res);

数据库更新

$res = DB::table('category')->where('cid',4)->update(['cname'=>'笔记本电脑']);
        var_dump($res);

数据库删除

$res = DB::table('category')->where('cid',4)->delete();
        var_dump($res);