hyperf 数据库模型-常用语句

查询

查询

use Hyperf\DbConnection\Db;

$users = Db::select('SELECT * FROM user;');
$users = Db::table('user')->get();
$users = Db::table('user')->select('name', 'age as user_age')->get();

获取首行记录

$row = Db::table('user')->first(); // sql 会自动加上 limit 1

获取单个字段

$id = Db::table('user')->where('id',1)->value('name');

获取字段一列

$names = Db::table('user')->pluck('name');

聚合查询

$count = Db::table('user')->count();
$max = Db::table('user')->max('age');
$min = Db::table('user')->min('age');
$avg = Db::table('user')->avg('age');
$sum = Db::table('user')->sum('age');

判断记录是否存在

$bool = Db::table('user')->where('id',3)->exists();

表连接

inner join

$users = Db::table('user')
    ->join('orders', 'user.id', '=', 'orders.user_id')
    ->select('user.*', 'orders.price')
    ->get();

left join

$users = Db::table('user')
    ->leftJoin('orders', 'user.id', '=', 'orders.user_id')
    ->select('user.*', 'orders.price')
    ->get();

right join

$users = Db::table('user')
    ->rightJoin('orders', 'user.id', '=', 'orders.user_id')
    ->select('user.*', 'orders.price')
    ->get();

where语句

$users = Db::table('user')->where('id','=','1')->get();
$users = Db::table('user')->where('id',2)->get();
$users = Db::table('user')->where('id','>=',2)->get();
$users = Db::table('user')->where('id','<>',2)->get();
$users = Db::table('users')->where('name', 'like', 'H%')->get();
$users = Db::table('user')->where([['id','=','1'],['name','like','H%']])->get();

orWhere

$users = Db::table('user')->where('id',1)->orWhere('id',3)->get();

whereBetween

$users = Db::table('user')->whereBetween('id',[1,3])->get();

whereIn

$users = Db::table('user')->whereBetween('id',[1,3])->get();

whereNotIn

$users = Db::table('user')->whereNotIn('id',[1,3])->get();

orderBy

$users = Db::table('user')->orderBy('id','asc')->get();
$users = Db::table('user')->orderBy('id','desc')->get();

inRandomOrder 随机获取一条记录

$user = Db::table('user')->inRandomOrder()->first();

groupBy

$user = Db::table('user')->select('age',Db::raw('COUNT(0) AS `count`'))->groupBy('age')->get();

插入

插入单条记录

$bool = Db::table('user')->insert(
    ['name' => '胡勇健', 'age' => 30]
);

插入多条记录

$bool = Db::table('user')->insert([
    ['name' => 'huyongjian1', 'age' => 30],
    ['name' => 'huyongjian2','age'=>31]
    ]
);

更新

Db::table('user')->where('id', 5)->update(['name' => 'xiaoming']);

更新或者新增

Db::table('user')->updateOrInsert(['id'=>4],['name' => 'xiaoming','age'=>28]);

删除和清空

删除

Db::table('user')->delete();
Db::table('user')->where('id',  1)->delete();

清空表

Db::table('user')->truncate();