【Laravel】5. 构造器的增删改查

710 阅读6分钟

作者:拾年之璐   公众号:知行研究院

首先说明一下,构造器查询的时候,末尾连缀上 ->toSql() ,获取的是当前执行的SQL语句。

初始数据:

image-20210106173155310

5.1 构造器的基本查询

1、 table() 方法:引入相应的表;get() 方法:可以查询当前表的所有数据

//获取全部结果
$users = DB::table('users')->get();

如果想看SQL语句,执行:

$users = DB::table('users')->toSql();

执行的SQL语句为

select * from `laravel_users`

2、 first() 方法:可以获取到第一条数据

//获取第一条数据
$users = DB::table('users')->first();

3、value(字段名) 方法:可以获取到第一条数据的指定字段的值

//获取第一条数据的email 字段值
$users = DB::table('users')->value('email');

4、find(id) 方法:可以获取指定id 的一条数据

//通过id获取指定一条数据
$users = DB::table('users')->find(20);

5、pluck(字段名) 方法:可以获取所有数据单列值的集合

//获取单列值的集合
$users = DB::table('users')->pluck('username');
//第二个参数是作为主键输出
$users = DB::table('users')->pluck('username', 'id');

第一个执行结果:

image-20210106170846261

5.2 构造器的分块与聚合

1、如果你一次性处理成千上万条记录,防止读取出错,可以使用chunk() 方法:

//切割分块执行,id 排序,默认增序;每次读取3 条
DB::table('users')->orderBy('id')->chunk(3, function ($users) {
    foreach ($users as $user) {
        echo $user->username.' ';
    }
    echo '<br>';
});

执行结果为:

image-20210106171152593

2、构造器查询提供了:count()max()min()avg()sum()聚合查询

//查询users表的总数
return DB::table('users')->count();
//查找user表的price表的最大值
return DB::table('users')->max('price');
//获取users表的price的平均值
return DB::table('users')->avg('price');

3、构造器查询两个判断记录是否存在的方法:exists()doesntExists() 方法

//如果users表中存在id=18的记录,则返回1,否则没有返回。放在数组里,存在返回true,不存在返回false。
return [DB::table('users')->where('id', 18)->exists()];
//与上一个相反
return [DB::table('users')->where('id', 18)->doesntExist()];

注:这里DB::第一个使用静态,返回查询对象,然后使用->where 等各种查询方法,这些查询方法返回的还是查询对象,所以可以继续连缀操作。最后当遇到比如get()返回结果等方法时,停止连缀。所以,返回结果必须放在最后。

5.3 构造器的SELECT查询

1、select() 方法:可以制定你想要的列,而不是所有列;

//设置显示的列,可以设置列别名
$users = DB::table('users')->select('username as name', 'email')->get();
//获取执行的SQL语句
$users = DB::table('users')->select('username as name', 'email')->toSql();

SQL为:

select `username` as `name`, `email` from `laravel_users`

2、addSelect() 方法:可以在你基础的查询构造器上再增加想要显示的字段;

就是扩展已存在的构造器。

//给已经构建好的查询添加更多字段
$base = DB::table('users')->select('username as name', 'email');
$users = $base->addSelect('gender')->get();

SQL为:

select `username` as `name`, `email`, `gender` from `laravel_users`

3、DB::raw() 方法:可以在select()内部实现原生表达式,否则解析错误;

//结合原生SQL 实现复杂查询
$users = DB::table('users')->select(DB::raw('COUNT(*) AS count, gender'))
    ->groupBy('gender')
    ->get();
//  ->toSql();

SQL 为:

select COUNT(*) AS count, gender from `laravel_users` group by `gender`

执行结果:

[
    {
        "count": 10,
        "gender": "男"
    },
    {
        "count": 2,
        "gender": "女"
    }
]

4、也可以直接使用 selectRaw() 方法实现内部原生;

//或者直接使用selectRaw()方法实现原生
$users = DB::table('users')->selectRaw('COUNT(*) AS count, gender')
    ->groupBy('gender')
    ->get();
//  ->toSql();

SQL为:

select COUNT(*) AS count, gender from `laravel_users` group by `gender`

执行结果同上。

5、还可以通过 havingRaw() 方法实现更精准的分组筛选;

//使用havingRaw 方法实现分组筛选
$users = DB::table('users')->selectRaw('COUNT(*) AS count, gender')
    ->groupBy('gender')
    ->havingRaw('count>5') //只显示分组的count大于5的分组
//  ->toSql();
    ->get();

SQL为:

select COUNT(*) AS count, gender from `laravel_users` group by `gender` having count>5

执行结果:

[
    {
        "count": 10,
        "gender": "男"
    }
]

5.4 构造器的WHERE查询

1、where() 查询:即条件查询,完整形式需要字段表达式和值三个;

//where 查询完整形式
//查询id=19的一条记录
$users = DB::table('users')->where('id', '=', 19)->get();
//$users = DB::table('users')->where('id', '=', 19)->toSql();

SQL为:

select * from `laravel_users` where `id` = ?

2、大部分情况下,等于用的比较多,就可以省略掉=号参数

//where 查询省略形式
//查询id=19的一条记录
$users = DB::table('users')->where('id', 19)->get();
//$users = DB::table('users')->where('id', 19)->toSql();

SQL同上。

3、当然,还有>、<、>=、<=、<>、like 等操作符:

//查询price大于等于95的所有记录
$users = DB::table('users')->where('price', '>=', 95)->get();
//查询name里含有小字的所有记录
$users = DB::table('users')->where('username', 'like', '%小%')->get();
//查询status不等于2的所有记录
$users = DB::table('users')->where('status', '<>', 2)->get();

4、如图条件较多,可以用数组来分别添加条件,具体如下:

//如果条件都是等于,查看SQL 语句用->toSql()替换->get()
$users = DB::table('users')->where([
    'price' => 90,
    'gender' => '男'
])->get();

//如果条件包含非等于,则:
$users = DB::table('users')->where([
     ['price', '>=', 90],
     ['gender', '=', '男']
])->get();

SQL分别为:

select * from `laravel_users` where (`price` = ? and `gender` = ?)
select * from `laravel_users` where (`price` >= ? and `gender` = ?)

5.5 构造器的WHERE派生查询

1、 orWhere() 方法:可以通过连缀实现两个或以上的or 条件查询;

示例1:

//where() + orWhere 实现or 条件查询
$users = DB::table('users')
   ->where('price', '>', 95)
   ->orWhere('gender', '女')
// ->toSql();
   ->get();

SQL为:

select * from `laravel_users` where `price` > ? or `gender` = ?

示例2:

$users = DB::table('users')
    ->where([
         ['price', '>=', 90],
         ['gender','=', '女']
    ])
    ->orWhere([
         ['price', '>=', 85],
         ['gender','=', '男']
     ])
     ->toSql();
//   ->get();

SQL为:

select * from `laravel_users` where (`price` >= ? and `gender` = ?) or (`price` >= ? and `gender` = ?)

2、通过包,我们还可以构建更加复杂的 orWhere 查询;

//orWhere()结合闭包查询
$users = DB::table('users')
    ->where('price', '>', '95')
    ->orWhere(function ($query) {
        $query->where('gender', '女')
            ->where('username', 'like', '%小%');
    })->toSql();

SQL为:

select * from `laravel_users` where `price` > ? or (`gender` = ? and `username` like ?)

3、whereBetween() 方法:可以实现区间查询。比如:

//whereBetween 查询区间价格60~90 之间
$users = DB::table('users')->whereBetween('price', [60, 90])->toSql();
//这里还支持相关三种:whereNotBetween/orWhereBetween/orWhereNotBetween;
echo DB::table('users')->where('status',1)->whereNotBetween('price', [60, 90])->toSql();
echo DB::table('users')->where('status',1)->orWhereBetween('price', [60, 90])->toSql();
echo DB::table('users')->where('status',1)->orWhereNotBetween('price', [60, 90])->toSql();

SQL依次为:

select * from `laravel_users` where `price` between ? and ?

select * from `laravel_users` where `status` = ? and `price` not between ? and ?
select * from `laravel_users` where `status` = ? or `price` between ? and ?
select * from `laravel_users` where `status` = ? or `price` not between ? and ?

4、whereIn() 方法:可以实现数组匹配查询。比如匹配出数组里指定的数据:

//whereIn 查询数组里匹配的数值
echo DB::table('users')->whereIn('id', [20,30,50])->toSql();
//这里还支持相关三种:whereNotIn/orWhereIn/orWhereNotIn;
echo DB::table('users')->where('status',1)->whereNotIn('id', [20,30,50])->toSql();
echo DB::table('users')->where('status',1)->orWhereIn('id', [20,30,50])->toSql();
echo DB::table('users')->where('status',1)->orWhereNotIn('id', [20,30,50])->toSql();

SQL依次为:

select * from `laravel_users` where `id` in (?, ?, ?)

select * from `laravel_users` where `status` = ? and `id` not in (?, ?, ?)
select * from `laravel_users` where `status` = ? or `id` in (?, ?, ?)
select * from `laravel_users` where `status` = ? or `id` not in (?, ?, ?)

5、whereNull() 方法:可以查询字段为Null 的记录;

//whereNull 查询字段值为Null 的记录
echo DB::table('users')->whereNull('uid')->toSql();
//这里还支持相关三种:whereNotNull/orWhereNull/orWhereNotNull;
echo DB::table('users')->where('status',1)->whereNotNull('uid')->toSql();
echo DB::table('users')->where('status',1)->orWhereNull('uid')->toSql();
echo DB::table('users')->where('status',1)->orWhereNotNull('uid')->toSql();

SQL依次为:

select * from `laravel_users` where `uid` is null

select * from `laravel_users` where `status` = ? and `uid` is not null
select * from `laravel_users` where `status` = ? or `uid` is null
select * from `laravel_users` where `status` = ? or `uid` is not null

6、whereDate() 系列方法:可以查询指定日期的记录;

//whereYear 查询指定日期的记录,缺省为等于
echo DB::table('users')->whereDate('created_at', '2018-12-11')->toSql();
//这里还支持相关四种:whereYear/whereMonth/whereDay/whereTime
echo DB::table('users')->whereYear('created_at', '>', '2018')->toSql();
//以上5中方法,支持or 前缀:
echo DB::table('users')->where('status',1)->orwhereMonth('created_at', '<', '5')->toSql();

SQL依次为:

select * from `laravel_users` where date(`created_at`) = ?
select * from `laravel_users` where year(`created_at`) > ?
select * from `laravel_users` where `status` = ? or month(`created_at`) < ?

5.6 构造器的排序分组

1、whereColumn() 方法:实现两个字段相等或者符合其他条件的查询结果。

//判断两个相等的字段,同样支持orWhereColumn()
echo DB::table('users')
    ->whereColumn('creates_at', 'updated_at')
    ->toSql();

//支持符号'create_time','>', 'update_time'
echo DB::table('users')
    ->whereColumn('creates_at', '>', 'updated_at')
    ->toSql();

//支持符号支持数组多个字段格式['create_time','>', 'update_time']
echo DB::table('users')
    ->whereColumn([
        ['creates_at', '>', 'updated_at'],
        ['id', '<>', 'uid']
    ])
    ->toSql();

SQL依次为:

select * from `laravel_users` where `created_at` = `updated_at`
select * from `laravel_users` where `created_at` > `updated_at`
select * from `laravel_users` where (`created_at` > `updated_at` and `id` <> `uid`)

2、orderBy() :方法实现desc 或asc 排序功能。

echo DB::table('users')
    ->orderBy('id', 'desc')
    ->toSql();

SQL为:

select * from `laravel_users` order by `id` desc

3、latest() 方法:设置时间倒序来排,默认时间字段是created_at:

//按照创建时间倒序排,默认字段created_at
echo DB::table('users')
    ->latest()
    ->toSql();
//可以自定义时间的字段
echo DB::table('users')
    ->latest('create_time')
    ->toSql();

SQL依次为:

select * from `laravel_users` order by `created_at` desc
select * from `laravel_users` order by `create_time` desc

4、使用 inRandomOrder() 方法来随机排序,得到一个随机列表;

//随机排序
echo DB::table('users')->inRandomOrder()->toSql();

SQL为:

select * from `laravel_users` order by RAND()

5、使用 skip()take() 限制结果集,或使用 offset()limit()

//从第3 条开始,显示3 条
echo DB::table('users')->skip(2)->take(3)->toSql();
echo DB::table('users')->offset(2)->limit(3)->toSql();

SQL依次为:

select * from `laravel_users` limit 3 offset 2
select * from `laravel_users` limit 3 offset 2

6、使用 when() 方法可以设置条件选择,执行相应的SQL 语句;

//when 实现条件选择,true,执行前者
echo DB::table('users')->when(true, function ($query) {
    $query->where('id', 19);
}, function ($query) {
    $query->where('username', '辉夜');
})->toSql();

//when 实现条件选择,false,执行后者
echo DB::table('users')->when(false, function ($query) {
    $query->where('id', 19);
}, function ($query) {
    $query->where('username', '辉夜');
})->toSql();

SQL依次为:

select * from `laravel_users` where `id` = ?
select * from `laravel_users` where `username` = ?

7、如果MySQL 在5.7+,有支持JSON 数据的新特性;

echo DB::table('users')->where('list->id', 19)->toSql();
///return [DB::table('users')->where('list->id', 19)->first()];

执行的SQL为:

select * from `laravel_users` where json_unquote(json_extract(`list`, '$."id"')) = ?

这里,处理的数据是:

image-20210123215529719

5.7 子查询

1、使用 whereExists() 方法实现一个 子查询 结果,返回相应的 主查询

//通过books 表数据,查询到users 表关联的所有用户
echo DB::table('users')->whereExists(function ($query) {
    $query->selectRaw(1)
        ->from('books')
        ->whereRaw('laravel_books.user_id = laravel_users.id');
})->toSql();

//whereRaw 也可以替代为:whereColumn
echo DB::table('users')->whereExists(function ($query) {
    $query->selectRaw(1)
        ->from('books')
        ->whereColumn('books.user_id', 'users.id')
        ->whereColumn('books.user_name', 'users.name');
})->toSql();

执行的SQL依次为:

select * from `laravel_users` where exists (
    select 1 from `laravel_books` 
    where laravel_books.user_id = laravel_users.id
)

select * from `laravel_users` where exists (
    select 1 from `laravel_books` 
    where `laravel_books`.`user_id` = `laravel_users`.`id` and `laravel_books`.`user_name` = `laravel_users`.`name`
)

PS:select 1 from,一般用于子查询的手段,目的是减少开销,提升效率,

2、可以使用 where(字段,function()) 闭包,来实现一个子查询;

//id=子查询返回的user_id
echo DB::table('users')
    ->where('id', function ($query) {
        $query->select('user_id')
            ->from('books')
            ->whereColumn('books.user_id', 'users.id');
    })
    ->toSql();
//id=子查询返回的user_id或name=子查询返回的user_name
echo DB::table('users')
    ->where('id', function ($query) {
        $query->select('user_id')
            ->from('books')
            ->whereColumn('books.user_id', 'users.id');
    })
    ->orWhere('name', function ($query) {
        $query->select('user_name')
            ->from('books')
            ->whereColumn('books.user_name', 'users.name');
    })
    ->toSql();

执行的SQL依次为:

select * from `laravel_users` where `id` = (
    select `user_id` from `laravel_books` where `laravel_books`.`user_id` = `laravel_users`.`id`
)

select * from `laravel_users` 
where `id` = (
    select `user_id` from `laravel_books` where `laravel_books`.`user_id` = `laravel_users`.`id`
) 
or `name` = (
    select `user_name` from `laravel_books` where `laravel_books`.`user_name` = `laravel_users`.`name`
)

5.8 构造器的join 查询(多表查询)

1、使用 join 实现内联接的多表查询,比如三张表进行 inner join 查询;

echo DB::table('users')
    ->join('books', 'users.id', '=', 'books.user_id')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->select('users.id', 'users.username', 'users.email', 'books.title', 'profiles.hobby')
    ->toSql();

SQL 为:

select `laravel_users`.`id`, `laravel_users`.`username`, `laravel_users`.`email`, `laravel_books`.`title`, `laravel_profiles`.`hobby` 
from `laravel_users` 
inner join `laravel_books` on `laravel_users`.`id` = `laravel_books`.`user_id` 
inner join `laravel_profiles` on `laravel_users`.`id` = `laravel_profiles`.`user_id`

2、可以使用 leftjoin 左连接或 rightjoin 右连接实现多表查询

echo DB::table('users')
    ->leftJoin('books', 'users.id', '=', 'books.user_id')
    ->rightjoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->toSql();

SQL 为:

select * from `laravel_users` 
left join `laravel_books` on `laravel_users`.`id` = `laravel_books`.`user_id` 
right join `laravel_profiles` on `laravel_users`.`id` = `laravel_profiles`.`user_id`

3、使用 crossjoin 交叉连接查询,会生成笛卡尔积,再用 distinct() 取消重复;

echo DB::table('users')
    ->crossJoin('books')
    ->select('username', 'email')
    ->distinct()
    ->toSql();

SQL:

select distinct `username`, `email` from `laravel_users` cross join `laravel_books`

4、如果想要实现闭包查询,和where 类似,只不过要用on 和orOn 方法;

echo DB::table('users')
    ->join('books', function ($join) {
        $join->on('users.id', '=', 'books.user_id');
    })->toSql();

echo DB::table('users')
    ->join('books', function ($join) {
        //如果想要再增加筛选条件,可以追加where()等
        $join->on('users.id', '=', 'books.user_id')
            ->orWhere('users.name', '=', '张三');
    })->toSql();

echo DB::table('users')
    ->join('books', function ($join) {
        //支持orOn 连缀
        $join->on('users.id', '=', 'books.user_id')
            ->orOn('users.name', '=', 'books.user_name');
    })->toSql();

SQL依次为:

select * from `laravel_users` inner join `laravel_books` on `laravel_users`.`id` = `laravel_books`.`user_id`

select * from `laravel_users` 
inner join `laravel_books` 
on `laravel_users`.`id` = `laravel_books`.`user_id` or `laravel_users`.`name` = ?

select * from `laravel_users` 
inner join `laravel_books` 
on `laravel_users`.`id` = `laravel_books`.`user_id` or `laravel_users`.`name` = `laravel_books`.`user_name`

5、使用 joinSub 实现 子连接查询,将对应的内容合并在一起输出;

//子连接查询
$query = DB::table('books')->selectRaw('user_id,title');
echo DB::table('users')->joinSub($query, 'books', function ($join) {
    $join->on('users.id', '=', 'books.user_id');
})->toSql();

SQL:

select * from `laravel_users`
inner join (
    select user_id,title from `laravel_books`
) as `laravel_books` 
on `laravel_users`.`id` = `laravel_books`.`user_id`

6、使用 union()unionAll() 方法实现两个查询的合并操作;

//union 取消重复,unionAll 不取消重复
$query = DB::table('users')->where('price', '>', '50');
echo DB::table('users')
    ->where('status', '<>', '0')
    ->union($query)
    ->toSql();

SQL :

(select * from `laravel_users` where `status` <> ?)
union
(select * from `laravel_users` where `price` > ?)

5.9 构造器的增加

1、使用 insert() 方法可以新增一条或多条记录;

//新增一条记录
DB::table('users')->insert([
    'username' => '李白',
    'password' => '123456',
    'email' => 'libai@163.com',
    'details' => '123'
]);

//新增多条记录
DB::table('users')->insert([
    [
        'username' => '李白',
        'password' => '123456'
    ],
    [
        'username' => '李黑',
        'password' => '654321'
    ]
]);

2、使用 insertOrIgnore() 方法,可以忽略重复插入数据的错误;

//忽略重复新增数据的错误
//这里原数据中已经有id为304的数据
DB::table('users')->insertOrIgnore([
    'id' => 304,
    'username' => '李白',
    'password' => '123456',
    'email' => 'libai@163.com',
    'details' => '123'
]);

3、使用 insertGetId() 方法,获取新增后的自增ID;

//获取新增后返回的ID
$id = DB::table('users')->insertGetId([
    'username' => '李白',
    'password' => '123456',
    'email' => 'libai@163.com',
    'details' => '123'
]);
return $id;

4、对于 JSON数据 新增时,要将数组转换成JSON

DB::table('users')->insert([
    'username' => '李白白',
    'password' => '123456789',
    'email' => 'libai@163.com',
    'details' => '123',
    //新增时,转换为json 数据
    'list' => json_encode(['code' => 1900])
]);

5.10 构造器的更新

1、使用 update() 方法,可以通过条件更新一条数据内容;

//更新修改一条数据
DB::table('users')->where('id', 100)
    ->update([
        'username' => '李红黑',
        'email' => 'lihong@163.com'
    ]);

2、使用 updateOrInsert() 方法,可以先进行查找修改,如不存在,则新增;

// 参数1:修改的条件
// 参数2:修改的内容(新增的内容)
DB::table('users')->updateOrInsert(
    ['id' => 307],
    ['username' => '李黑', 'password' => '654321', 'details' => '123']
);

3、对于 json 数据,修改的方法和正常数据类似;

//修改时,使用list->id 指定
DB::table('users')->where('id', 101)
    ->update([
        'list->id' => 20,
        'price' => 666
    ]);

4、更新数据时,可以使用自增 increment() 和自减 decrement() 方法;

//默认自增/自减为1,可设置
DB::table('users')->where('id', 306)->increment('price');
// 每次自增2
DB::table('users')->where('id', 306)->increment('price', 2);

5.11 构造器的删除

使用 delete() 删除数据,一般来说要加上 where 条件,否则全表清空;

//删除一条数据
DB::table('users')->delete(307);
DB::table('users')->where('id', 307)->delete();
//清空
DB::table('users')->delete();
DB::table('users')->truncate();

以上。