laravel DB类，查询构造器

laravel分为三大数据库操作(DB facade[原始查找]，查询构造器[Query Builder]，Eloquent ORM)：

1，DB facade

use Illuminate\Support\Facades\DB;


DB::select('select * from users where id = :id', ['id' => 1]);
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
DB::update('update users set votes = 100 where name = ?', ['John']);
DB::delete('delete from vipinfo where vip_ID= ?',[5]); 
DB::statement('drop table users');

　　

2，查询构造器[Query Builder]

1. 查询所有
         $data = DB::table('user')->get();

 2. 查询所有，并指定字段 推荐使第一种方式
         $data = DB::table("user名")->select("name", "email")->get();
         $data = DB::table("user名") ->select(DB::raw('count(id) as count'), "email");
         $data = DB::table("user名")->selectRaw("count(id) as count, email")->get();
         $data = DB::table('user')->get(['name','age']);
         
 3. 查询单条数据
         $ret = DB::table('member')->where('id', 5)->first();
         
 4. 获取单个值
         $email = DB::table('users')->where('name', 'John')->value('email');

 5. 获取包含单列值的集合
         $titles = DB::table('roles')->pluck('title');
         // 会将 roles 表中的 name 字段当做键名，title 字段当做键值返回
         $roles = DB::table('roles')->pluck('title', 'name');

 6. 根据主键获取一条数据–find方法
         $user = DB::table('users')->find(1);
         $user = DB::table('users')->find([1, 2, 3]);
         
 7. 排序
         DB::table('users')->orderBy('name', 'desc') ->orderBy('rs.created_at', 'desc')->get();
         DB::table('orders')->orderByRaw('updated_at - created_at DESC')->get();
 
 9. 数据结果去重（distinct 方法会强制让查询返回的结果不重复）
         $users = DB::table('users')->distinct()->get();
 
 7. 查询前几条数据
         $data = DB::table('member')->where('active', 1)->take(10)->get();                
         $data = DB::table('member')->where('active', 1)->limit(10)->get();
         
 8. 分页查询
         $data = DB::table('member')->orderBy('id','desc')->paginate(10);
         $data = DB::table('member')->orderBy('id','desc')->offset(0)->limit(10)->get();
                 limit：表示限制输出的条数
                 offset：从什么地方开始,起始从0开始
         
 9. 条件查询
         DB::table("表名")->where([['a', '=', '1'],['b', '<>', '1']])->get();
         DB::table("表名")->where("name",">","10")->whereIn("titile",[1,2,3])->get();
         DB::table("表名")->where("name",">","10")->whereBetween("titile",[0,10])->get();
         DB::table("表名")->where("name",">","10")->orWhere("titile","=","0")->get();
         
 10. 分群（Group By）及 Having
         DB::table("users")->orderBy("name","desc")->groupBy("count")->having("count",">",100)->get()
         
 11. 模糊查询
          DB::table('users')->where('name', 'like', '%名称%')->get();
          
 12. 聚合查询 count, max, min, avg和 sum
          $users = DB::table('users')->count();
          $price = DB::table('orders')->max('price');

//count()统计记录条数
$nums=DB::table("vipinfo")->count();
echo $nums;
//max()某个字段的最大值,同理min是最小值
$max=DB::table("vipinfo")->max("vip_fenshu");
echo $max;
//avg()某个字段的平均值
$avg=DB::table("vipinfo")->avg("vip_fenshu");
echo $avg;
//sum()某个字段的和
$sum=DB::table("vipinfo")->sum("vip_fenshu")
 
 13. 强制让查询返回的结果不重复
         $users = DB::table('users')->distinct()->get();
 14. 关联查询
$result = DB::table('requirement_surveys as rs')
         ->select('rs.id', 'rs.process', 'r.requirement_id', 'r.title', 'u.name')
         ->leftJoin('requirements as r', 'rs.requirement_id', '=', 'r.requirement_id')
         ->leftJoin('users as u', 'u.id', '=', 'r.author_id')
         ->where('u.name', 'like', "%$name%")
         ->where('r.title', 'like', "%$title%")
         ->orderBy('rs.process', 'asc')
         ->orderBy('rs.created_at', 'desc')
         ->paginate($page_limit);
         
 $users = DB::table('users')
         ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
         ->get();
         
 $users = DB::table('users')
         ->join('contacts', 'users.id', '=', 'contacts.user_id')
         ->join('orders', 'users.id', '=', 'orders.user_id')
         ->select('users.*', 'contacts.phone', 'orders.price')
         ->get();
 15，复制查询
//查询出现次数最多的商品名
 $product_name = Db::table('guild_pay.order')
         ->where('status', '=', 1)
         ->select(DB::raw('count(*) as count, product_name'))
         ->orderBy('count', 'desc')
         ->groupBy('product_name')
         ->first();

 //两个数据库中有两个不相关的表，我需要将它们合并，并在order2中新增不存在的字段，并赋予默认值
 $product_name = '默认值'；
 $news = DB::table("guild_pay.order1")
         ->where('status', '=', 1)
         ->selectRaw('product_name','order_no','money','create_time');
 $res = Db::table('guild_pay.order2')
           ->where('status', '=', 1)
         ->union($news)
         ->selectRaw('IFNULL(null, ?) as product_name, order_no, money, create_time', [$product_name])
         ->orderBy('create_time', 'desc')
         ->paginate(10)->toArray();
 ps：记得两个表的key要一一对应，不可以一边多一边少，也不可以换位置
 

删除

$ret = DB::table('member')->where('id',2)->delete();
$num=DB::table("vipinfo")->truncate();//删除整表，不能恢复，谨慎使用
增加
1. 添加单条数据
         $ret = DB::table('member')->insertGetId($data);
         $ret = DB::table('member')->insert($data);
         insert() 可以同时添加一条或多条，返回值是布尔类型。
         insertGetId() 只能添加一条数据，返回得到插入时的ID值。
     
 2. 添加多条记录
         $ret = DB::table('member')->insert([
             ['name'=>'AAAA','age'=>20,'email'=>'111@111.com'],
             ['name'=>'BBBB','age'=>30,'email'=>'222@222.com'],
         ]);
 
修改
$ret = DB::table('member')->where('id', 2)->update([
     'name' => '修改一下',
     'age'  => 50
 ]);
$bool=DB::table("vipinfo")->where('vip_ID',6)->update(['vip_fenshu'=>500]);

echo $bool;

//自增

$bool=DB::table("vipinfo")->where('vip_ID',6)->increment("vip_fenshu");//自增1

$bool=DB::table("vipinfo")->where('vip_ID',6)->increment("vip_fenshu",3);//自增3

echo $bool;

//自减

$bool=DB::table("vipinfo")->where('vip_ID',6)->decrement("vip_fenshu");//自1

$bool=DB::table("vipinfo")->where('vip_ID',6)->decrement("vip_fenshu",3);//自增3

echo $bool;

//自增时再修改其他字段

$bool=DB::table("vipinfo")->where('vip_ID',6)->increment("vip_fenshu",3,['vip_name'=>'dbdibi']);//自增3