lavarel7学习笔记：SQL数据库操作(CURD)+链式调用(查询构造器)

lavarel7操作数据库：

一、访问类操作数据库

引用DB类：use Illuminat\Support\Facades\DB;

配置路由：Route::get(‘/index’,’Mydb@index’);

1、查询:
$data = DB::select(‘select from articles where id=4’);
$data = DB::select(‘select from articles where id =:id’,[‘id’=>5]);
dd($data);

2、新增：
$sql = ‘insert into articles(title,contents) values (“测试新增”,”测试新增内容”);
$res = DB::insert($data);
dd($res);

3、修改
$sql = ‘update articles set title=”测试数据”,contents=”测试修改内容” where id=5’;
$res = DB::update($sql);
dd($res);

4、删除
$sql = ‘delete from articles where id=5’;
$res = DB::deletes($sql);
dd($res);

二、链式调用(查询构造器)

1、查询
$data = DB::table(‘articles’)->get();
$data = DB::table(‘articles’)->get()->toarry();
$data = DB::table(‘articles’)->get()->all();
$data = DB::table(‘articles’)->where(‘id’,2)->get()->all();
$data = DB::table(‘articles’)->where(‘id’,’>’,2)->get()->all();
$data = DB::table(‘articles’)->where(‘id’,’>’,2)->get([‘id’,’title’])->all();
dd($data);

2、新增一条
$data = array(‘title’=>’测试新增’,’contents’=>’测试新增内容’);
$res = DB::table(‘articles’)->insert($data );
dd(res);

新增多条
$data = [];
$data[] = array(‘title’=>’测试新增1’,’contents’=>’测试新增内容1’);
$data[] = array(‘title’=>’测试新增2’,’contents’=>’测试新增内容2’);
$data[] = array(‘title’=>’测试新增3’,’contents’=>’测试新增内容3’);
$res = DB::table(‘articles’)->insert($data );
echo ‘<pre>‘;
var_dump($res);

返回新增记录主键值
$data[] = array(‘title’=>’测试新增4’,’contents’=>’测试新增内容4’);
$res = DB::table(‘articles’)->insertGetId($data );
dd($res);

$data = [];
$data[] = array(‘title’=>’测试新增5’,’contents’=>’测试新增内容5’);
$data[] = array(‘title’=>’测试新增6’,’contents’=>’测试新增内容6’);
$res = DB::table(‘articles’)->insertGetId($data );
var_dump($res);
//结果报错

3、修改
$res = DB::table(‘articles’)->where(‘id’,12)->update(array(‘title’=>’测试修改’));
$res = DB::table(‘articles’)->where(‘id’,’>’,13)->update(array(‘title’=>’测试修改’));
var_dump($res);

4、删除
$res = DB::table(‘articles’)->where(‘id’=11)->delete();
$res = DB::table(‘articles’)->where(‘id’,’>’,12)->delete();
多条件
$res = DB::table(‘articles’)->where(‘id’,’>’,12)->(‘id’,’<’,22)->delete();
var_dump($res);

三、聚合查询

1、查询总数
//select count(*)from articles where id>5
//select count(id)from articles where id>5
$res =DB::table(‘articles’)->count();
$res =DB::table(‘articles’)->where(‘id’,’<’,5)->count();
var_dump($res);