1. 查询构造器(query builder)
2. 新建记录
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
class StudentController extends Controller
{
public function query1(){
// 新增记录
$bool = DB::table('student')->insert([
'name' => 'Lisa',
'age' => 18
]);
var_dump($bool);
// 新增记录获取自动增加的id
$id = DB::table('student')->insertGetId([
'name' => 'Lucy',
'age' => 22
]);
var_dump($id);
// 一次插入多条数据
$bool = DB::table('student')->insert([
['name' => 'Tommy', 'age' => 23],
['name' => 'Peggy', 'age' => 21]
]);
var_dump($bool);
}
}
3. 更新数据
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
class StudentController extends Controller
{
public function query2(){
// 基本更新
$num = DB::table('student')
->where('id', 1006)
->update(['age' => 28]);
var_dump($num);
// 更新自增1
$num = DB::table('student')
->increment('age');
// 更新自减1
$num = DB::table('student')
->decrement('age');
// 更新自增10
$num = DB::table('student')
->increment('age', 10);
// 更新自减10
$num = DB::table('student')
->decrement('age', 10);
// 指定id
$num = DB::table('student')
->where('id', 1006)
->decrement('age', 5, ['name' => 'Jane']);
}
}
4. 删除数据
两种方式:
- delete
- truncate
truncate
操作相当危险,会清空所有的数据!!!
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
class StudentController extends Controller
{
// 删除
public function query3()
{
// 简单删除
$num = DB::table('student')
->where('id', 1006)
->delete();
var_dump($num);
// 简单删除带 >, <或>=, <=
$num = DB::table('student')
->where('id', '>=', 1005)
->delete();
var_dump($num);
// truncate table,不返回任何值
// DB::table('student')->truncate();
}
}
5. 查询数据
- get():获取全部
- first():获取第一个
- where():条件,如果是用原生的,用whereRaw()
- pluck(): 获取指定字段
- lists():获取指定字段,并返回数组,且可以设置数组下标
- select():选择要返回的字段
- chunk():不全部返回,每次返回指定数量的记录
调试利器:Laravel的dd()函数
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
class StudentController extends Controller
{
// 查询
public function query4()
{
// get()获取所有
$rs = DB::table('student')->get();
dd($rs);
// first()获取第一条
$rs = DB::table('student')
->first();
dd($rs);
// 排序
$rs = DB::table('student')
->orderBy('id', 'DESC')
->first();
dd($rs);
// where()
$rs = DB::table('student')
->where('id', '>=', 1002)
->get();
dd($rs);
// where()多个条件
$rs = DB::table('student')
->whereRaw('id >= ? AND age > ?', [1001, 18])
->get();
dd($rs);
// pluck() 返回结果集中指定的字段
$names = DB::table('student')
->pluck('name');
dd($names);
// lists() 返回结果集中指定的字段到数组,可以指定下标
$names = DB::table('student')
->lists('name', 'id');
dd($names);
// select() 返回指定字段
$names = DB::table('student')
->select('id', 'name')
->get();
dd($names);
// chunk()一次只查部分数据
DB::table('student')->chunk(2, function($students){
var_dump($students);
return false;
});
}
}
6. 聚合函数
- count
- max
- min
- avg
- sum
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
class StudentController extends Controller
{
// 使用聚合函数
public function query5()
{
// count
$qty = DB::table('student')->count();
dd($qty);
// max
$qty = DB::table('student')->max('age');
dd($qty);
// min
$qty = DB::table('student')->min('age');
dd($qty);
// avg
$qty = DB::table('student')->avg('age');
dd($qty);
// sum
$qty = DB::table('student')->sum('age');
dd($qty);
}
}