作者:拾年之璐 公众号:知行研究院
首先说明一下,构造器查询的时候,末尾连缀上 ->toSql()
,获取的是当前执行的SQL语句。
初始数据:
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');
第一个执行结果:
5.2 构造器的分块与聚合
1、如果你一次性处理成千上万条记录,防止读取出错,可以使用chunk()
方法:
//切割分块执行,id 排序,默认增序;每次读取3 条
DB::table('users')->orderBy('id')->chunk(3, function ($users) {
foreach ($users as $user) {
echo $user->username.' ';
}
echo '<br>';
});
执行结果为:
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"')) = ?
这里,处理的数据是:
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();
以上。