1.查询结果集
<?php
namespace App\Http\Controllers;
use DB;
use App\Http\Controllers\Controller;
class UserController extends Controller
{
/**
* Show a list of all of the application's users.
*
* @return Response
*/
public function index()
{
$users = DB::table('users')->get();
return view('user.index', ['users' => $users]);
}
}
只查询一个结果
$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;
如果不需要整行数据，而是其中的一个
$email = DB::table('users')->where('name', 'John')->value('email');
如果面对成千上万条数据，不可能一次读取，需要用到chunk函数
DB::table('users')->chunk(100, function($users) {
foreach ($users as $user) {
//
}
});
//返回false停止
DB::table('users')->chunk(100, function($users) {
// Process the records...
return false;
});
查询列的数据
$titles = DB::table('roles')->lists('title');
foreach ($titles as $title) {
echo $title;
}
//多列数据
$roles = DB::table('roles')->lists('title', 'name');
foreach ($roles as $name => $title) {
echo $title;
}
count, max,min, avg, and sum
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');
2.[Selects]
$users = DB::table('users')->select('name', 'email as user_email')->get();
//返回不同的值
$users = DB::table('users')->distinct()->get();
//如果想为一个已有的查询语句添加列
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
原生查询
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
3.Join
Inner Join
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
Left Join
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
高级 Join
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();
where 语句orWhere
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();
4.Union
UNION组合两个表时，将重复的记录删除；而使用UNION ALL组合两多个表时，不考虑结果集中是否存在重复记录，包括重复行
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();
5.where 语句
$users = DB::table('users')->where('votes', '=', 100)->get();
//===
$users = DB::table('users')->where('votes', 100)->get();
orWhere
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
****whereNotBetween****
$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();
whereIn / whereNotIn
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
whereNull / whereNotNull
$users = DB::table('users')
->whereNull('updated_at')
->get();
4.高级where的查询
DB::table('users')
->where('name', '=', 'John')
->orWhere(function ($query) {
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})
->get();
==>
select * from users where name = 'John' or (votes > 100 and title <> 'Admin')
Exists Statements
DB::table('users')
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();
==>
select * from users
where exists (
select 1 from orders where orders.user_id = users.id
)
6.[Ordering, Grouping, Limit, & Offset]
orderBy
$users = DB::table('users')
->orderBy('name', 'desc')
->get();
groupBy / having / havingRaw
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();
$users = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > 2500')
->get();
skip / take
可以用于分页
$users = DB::table('users')->skip(10)->take(5)->get();
7.[Inserts]
DB::table('users')->insert(
['email' => 'john@example.com', 'votes' => 0]
);
一次插入多个
DB::table('users')->insert([
['email' => 'taylor@example.com', 'votes' => 0],
['email' => 'dayle@example.com', 'votes' => 0]
]);
对于有自增id的表
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);
8.[Updates]
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
增加与减少
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);
可以指定其余列用于跟新
DB::table('users')->increment('votes', 1, ['name' => 'John']);
9.[Deletes]
DB::table('users')->where('votes', '<', 100)->delete();
如果想删除真个表内容，并且重置id
DB::table('users')->truncate();
10.锁
sharedLock
读锁，跟新的时候不能查询
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
lockForUpdate
写锁，阻止其它shared lock查询和跟新
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();
