laravel 之 数据库

快速入门

简介

数据库读写分离

'mysql' => [
    'read' => [
        'host' => '192.168.1.1',
    ],
    'write' => [
        'host' => '196.168.1.2'
    ],
    'driver'    => 'mysql',
    'database'  => 'database',
    'username'  => 'root',
    'password'  => '',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
 ],   

使用多数据库连接

$users = DB::connection('foo')->select(...);

你也可以在连接的实例中使用 getPdo 方法访问原始的底层 PDO 实例：

$pdo = DB::connection()->getPdo();

运行原生 SQL 语句

select

$user = DB::select('select * from users where active = ?', [1]);
foreach ($users as $user) {
    echo $user->name;
}

$results = DB::select('select * from users where id = :id', ['id' => 1]);

insert

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);

update

$affected = DB::update('update users set votes = 100 where name = ?', ['John']);   // 返回此语句执行所影响的行数

delete

$deleted = DB::delete('delete from users');  // 返回此语句执行所删除的行数

运行一般声明

有些数据库没有返回值， 对于这种类型的操作，可以使用 DB facade 的 statement 方法。

DB::statement('drop table users');

监听查询事件

监控每一条 SQL 语句

# 添加 use Illuminate\Support\Facades\DB;
# App\Providers\AppServiceProvider 
# boot 方法下添加打印函数
if(1){
  DB::listen(function ($query) {
    echo "<pre>";
    var_dump($query->sql);
    var_dump($query->bindings);
    var_dump($query->time);
    echo "</pre>";
  });
}

数据库事务

普通事务

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);

    DB::table('posts')->delete();
});

处理死锁

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);

    DB::table('posts')->delete();
}, 5);  // 如果重试结束还没有成功执行，将会抛出一个异常：

手动操作事务

开始事务

DB::beginTransaction();

回滚事务：

DB::rollBack();

提交事务：

DB::commit();

使用 DB facade 的事务方法也适用于 查询语句构造器 和 Eloquent ORM 。

查询构造器

获取结果

get 所有值

$users = DB::table('users')->get();
foreach ($users as $user) {
    echo $user->name;
}

first 一行值

$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;

value 单个值

$email = DB::table('users')->where('name', 'John')->value('email');

pluck 一列值

$titles = DB::table('roles')->pluck('title');
foreach ($titles as $title) {
    echo $title;
}

chunk 分块

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        //  操作
        //  return false; 手动停止
    }
});

聚合

查询构造器也支持各种聚合方法，如 count、 max、 min、 avg 和 sum。

子句

$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();

原始sql（注意注入）

$users = DB::table('users')
                     ->select(DB::raw('count(*) as user_count, status'))    // 注意注入
                     ->where('status', '<>', 1)
                     ->groupBy('status')
                     ->get();

joins

# 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();

# cross join
$users = DB::table('sizes')
            ->crossJoin('colours')
            ->get();

高级join

DB::table('users')
    ->join('contacts', function ($join) {
                            $join->on('users.id', '=', 'contacts.user_id')->orOn('users.name', '=', 'contacts.user_name');
                       })

# select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id` or `users`.`name` = `contacts`.`user_name`)

union

$users = DB::table('users')
    ->whereNull('id')
    ->union(DB::table('users'))
    ->get();
# select * from `users` where `id` is null union (select * from `users`)

where 字句

where('votes', '=', 100)  # 等价于   where('votes', 100)
where('votes', '>=', 100)
where('votes', '<>', 100)
where('name', 'like', 'T%')
where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])
->where('votes', '>', 100) -> orWhere('name', 'John')
whereBetween('votes', [1, 100])
whereNotBetween('votes', [1, 100])
whereIn('id', [1, 2, 3])
whereNotIn('id', [1, 2, 3])
whereNull('updated_at')
whereNotNull('updated_at')
whereDate('created_at', '2016-12-31')
whereMonth('created_at', '12')
whereDay('created_at', '31')
whereYear('created_at', '2016')
whereColumn('first_name', 'last_name') # 检测两个列的数据是否一致
whereColumn('updated_at', '>', 'created_at')
whereColumn([
    ['first_name', '=', 'last_name'],
    ['updated_at', '>', 'created_at']
])

更高级的 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)

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
# )

json 查询语句

本特性仅支持 MySQL 5.7+ 和 Postgres数据库。可以使用 -> 运算符来查询 JSON 列数据：

$users = DB::table('users')
                ->where('options->language', 'en')
                ->get();

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

Ordering, Grouping, Limit 及 Offset

orderBy

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->get();

latest/oldest

$user = DB::table('users')
                ->latest()
                ->first();
// 默认查询结果将依据 created_at 列。

inRandomOrder

$randomUser = DB::table('users')
                ->inRandomOrder()
                ->first();

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();
# select * from `users` limit 5 offset 10
# 等同于
$users = DB::table('users')
                ->offset(10)
                ->limit(5)
                ->get();

条件语句

$role = $request->input('role');

$users = DB::table('users')
                ->when($role, function ($query) use ($role) {
                    return $query->where('role_id', $role);
                })
                ->get();
# 只有当 when 方法的第一个参数为 true 时，闭包里的 where 语句才会执行。如果第一个参数是 false，这个闭包将不会被执行。

$sortBy = null;

$users = DB::table('users')
                ->when($sortBy, function ($query) use ($sortBy) {
                    return $query->orderBy($sortBy);
                }, function ($query) {
                    return $query->orderBy('name');
                })
                ->get();
# # 只有当 when 方法的第一个参数为 true 时，闭包里的 where 语句才会执行。如果第一个参数是 false，第二个闭包被执行。

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]
]);

Updates

DB::table('users')
            ->where('id', 1)
            ->update(['votes' => 1]);

Updating JSON Columns

DB::table('users')
            ->where('id', 1)
            ->update(['options->enabled' => true]);

自增或自减

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']);

Deletes

# 慎用 DB::table('users')->delete();

DB::table('users')->where('votes', '>', 100)->delete();

# 慎用 DB::table('users')->truncate();

悲观锁

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
# 共享锁可防止选中的数据列被篡改，直到事务被提交为止

DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();
# 使用「更新」锁可避免行被其它共享锁修改或选取

分页

查询构造器分页

$users = DB::table('users')->paginate(15);
# 使用 groupBy 语句的分页操作无法由 Laravel 有效执行。如果你需要在一个分页结果集中使用 groupBy，建议你查询数据库并手动创建分页器。

$users = DB::table('users')->simplePaginate(15);
# 显示简单的「上一页」和「下一页」的链接,不需要显示页码链接，这对于大数据集非常有用。

Eloquent 模型分页

$users = App\User::paginate(15);

$users = User::where('votes', '>', 100)->paginate(15);

$users = User::where('votes', '>', 100)->simplePaginate(15);

手动创建分页

Illuminate\Pagination\Paginator 或 Illuminate\Pagination\LengthAwarePaginator 实例

Paginator 类不需要知道结果集中的数据项总数；然而，由于这个，该类没有用于检索最后一页索引的方法。

LengthAwarePaginator 接收的参数几乎和 Paginator一样；但是，它需要计算结果集中的数据项总数。

换一种说法，Paginator 对应于查询语句构造器和 Eloquent 的 simplePaginate 方法，而 LengthAwarePaginator 对应于 paginate 方法。

显示分页结果

<div class="container">
    @foreach ($users as $user)
        {{ $user->name }}
    @endforeach
</div>

{{ $users->links() }}
# links 方法生产的 HTML 兼容 Bootstrap CSS framework

自定义分页器的 URI

Route::get('users', function () {
    $users = App\User::paginate(15);
    $users->withPath('custom/url');
    //
});
# http://example.com/custom/url?page=N

附加参数到分页链接中

{{ $users->appends(['sort' => 'votes'])->links() }}

{{ $users->fragment('foo')->links() }}
# 附加「哈希片段」到分页器的链接中，你应该使用 fragment 方法。

Route::get('users', function () {
    return App\User::paginate()->toJson();
});

{
    "current_page": 1,
    "data": [
        {
            "id": 1,
            "name": "michael",
            "email": "754497892@qq.com",
            "created_at": "2017-07-28 03:04:07",
            "updated_at": "2017-07-28 03:04:07"
        },
        {
            "id": 3,
            "name": "aaa",
            "email": "aaa@qq.com",
            "created_at": "2017-08-02 02:04:54",
            "updated_at": "2017-08-02 02:04:54"
        }
    ],
    "from": 1,
    "last_page": 1,
    "next_page_url": null,
    "path": "http://192.168.1.63/home",
    "per_page": 15,
    "prev_page_url": null,
    "to": 2,
    "total": 2
}

自定义分页视图

如果你不使用 Bootstrap，你可以自定义你自己的视图去渲染这些链接。当在分页器实例中调用 links 方法，传递视图名称作为方法的第一参数：

{{ $paginator->links('view.name') }}

// 传递数据到视图中...
{{ $paginator->links('view.name', ['foo' => 'bar']) }}

然而，自定义分页视图最简单的方法是通过 vendor:publish 命令将它们导出到你的 resources/views/vendor 目录：

php artisan vendor:publish --tag=laravel-pagination

这个命令将视图放置在 resources/views/vendor/pagination 目录中。这个目录下的 default.blade.php 文件对应于默认分页视图。你可以简单地编辑这个文件以修改分页的 HTML 。

分页器示例方法

$results->count()
$results->currentPage()
$results->firstItem()
$results->hasMorePages()
$results->lastItem()
$results->lastPage() (当使用 simplePagination 时无效)
$results->nextPageUrl()
$results->perPage()
$results->previousPageUrl()
$results->total() (当使用 simplePagination 时无效)
$results->url($page)

数据库迁移

数据填充

Redis