2.关于laravel框架中的数据库基本操作

1. 数据库的配置 ：config/database.php

'mysql' => [
    'driver'    => 'mysql',
    'host'      => env('DB_HOST', 'localhost'),
    'database'  => env('DB_DATABASE', 'forge'),
    'username'  => env('DB_USERNAME', 'forge'),
    'password'  => env('DB_PASSWORD', ''),
    'charset'   => 'utf8',
    'collation' => 'utf8mb4_general_ci',
    'prefix'    => 'wx_',//表前缀
    'strict'    => false,
    'engine'    => null,
],

对应的.env

APP_ENV=local
APP_DEBUG=true
APP_KEY=FPg9LRkHTj5j14ig7ETzQhG29r5sEWNR

DB_HOST=localhost
DB_DATABASE=
DB_USERNAME=
DB_PASSWORD=

数据库三种操作方式：原始查找：DB facade

                                    查询构造器

                                    Eloquent ORM

DB facade：最原始的sql语句操作数据表

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;

class TestController extends Controller{

     public function testDB(){

     //查询

     DB::select('select * from tablename');

     //添加

     DB::insert('insert into tablename(name,age) values(?,?)',['name',18]);//用？绑定更安全

     //修改

     DB::update('update student set age = ? where name = ?',[20,'name']);//返回的值为修改的行数

     //删除

     DB::delete('delete from tablename where id>?',[1]);

     }

}

查询构造器

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;

class TestController extends Controller{

     //插入一条数据

     public function testinsert(){

           DB::table('tablename')->inster(

           ['name'=>'test','age'=>18]

           //插入多组数据时将数据写成数组的形式：[

                                                                              ['name'=>'name','age'=>12],

                                                                              ['name'=>'testname','age'=>13],

                                                                              ]

           );

     }

     //更新数据

     public function testupdate{

              //更新相应数据

              DB::table('tablename')->where('id',1)->update(['age'=>15]);//更新数据一定要带条件，where()

              //自增

              DB::table('tablename')->increment('age');//默认自增1

              DB::table('tablename')->increment('age',3);//自增3

              //自减

              DB::table('tablename')->decrement('age',3);//自减3

              //带上条件的情况

              DB::table('tablename')->where('id',3)->decrement('age',3);

              //同时修改其他字段

              DB::table('tablename')->decrement('age',3,['name'=>'test'])

     }

     //删除数据

     public function testdel(){

              DB::table('tablename')->delete();//整个表格删除

              DB::table('tablename')->where('id',15)->delete();//删除id=15  

              DB::table('tablename')->where('id','>=',15)->delete();//删除id>=15

              //总表删除，一般不用truncate();

              DB::table('tablename')->truncate();//不返回任何值。整个表的信息均被删除

     }

     //使用查询构造器查询数据！方法有：get(),fitst(),where(),pluck(),lists(),select(),chunk(),5.4后lists弃用不管了

    public function testsel(){

              //get()

              DB::table('tablename')->get();//获取表的所有数据

              //first()

              DB::table('tablename')->first();//获取结果中的第一条，可以加上排序条件  例：->where('id','>=',15);     ->orderBy('id','desc');//根据id倒叙排序，，asc则是顺序

             //where()

              DB::table('tablename')->where('id','>=',12)->get();

              //多个条件

              DB::table('tablename')->whereRaw('id >= ? and age >= ?',[12,15])->get();

              //pluck()->返回结果集中的某个字段

              DB::table('tablename')->pluck('name');

              //lists()->返回结果集中的某个字段，可以定义显示字段的下标

              DB::table('tablename')->lists('name','id');//输出的值为  id=>"name"

              //select()->指定查找固定的字段

               DB::table('tablename')->select('id','name','age')->get();//查找表中id，name，age字段的数据

              //chunk()->分段获取，5.4中，chunk必须使用orderBy()

              DB::table('tablename')->orderBy('id','desc')->chunk(1000,function($tables){

                       var_dump($tables);//每次查询出1000条数据，分段

                       if(条件){return false;}//跳出输出

              });

    }  

    //查询构造器中的聚合函数

    public function testju(){

        $count = DB::table('tablename')->count();//统计表的记录数

        $max   = DB::table('tablename')->max('age');//输出表中age最大值

        $min    = DB::table('tablename')->min('age');//最小值

        $avg    = DB::table('tablename')->avg('age');//平均值

        $sum   = DB::table('tablename')->sum('age');//求和

     

    }

}