laravel 数据库 1 -- 查询(上)
20 March 2017

配置

'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'    => '',
],

多个连接

$connection = 'connection_name';
// foo 是 connection 的名字, 在 config/database.php 配置文件中定义
$users = DB::connection('foo')->select(...); 

获得 pdo 实例

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

DB curd

原生 sql

$res = DB::select('select * from users where active = ?', [1]);

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

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

$affected = DB::update('update users set votes = 100 where name = ?', ['John']);
$affected = DB::delete('delete from users');

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

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

DB::statement('drop table users');                      // 不返回结果

DB get

$users = DB::table('users')->get();
$user  = DB::table('users')->where('name', 'John')->first();       // 只获取一行
$users = DB::table('users')->where('id', '>', 1)->take(2)->get();  // 取 2 个
$users = DB::table('users')->select(DB::raw('col1, col'))->get();  // 指定字段

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

$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();


DB::table('users')->chunk(100, function($users) {       // 每次获取 100
    foreach ($users as $user) {
        if (xxx) {
            return false;                               // 停止获取
        }
    }
});


// 获取 field
$title  = DB::table('roles')->value('title');           // 只获取一个
$titles = DB::table('roles')->lists('title');           // 获取所有的 title, key 是 0, 1, 2..
$titles = DB::table('roles')->lists('title', 'uid');    // 获取所有的 title, key 是 uid

$titles = DB::table('roles')->pluck('title');           // 获取所有的 title, key 是 0, 1, 2..
$titles = DB::table('roles')->pluck('title', 'uid');    // 获取所有的 title, key 是 uid

DB insert

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' => 'xxx', 'votes' => 0]
);


// 返回插入的主键 (主键名字不叫 id)
$id = DB::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0], 'other_id'
);

DB increment

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

DB max avg groupBy

$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')->where('finalized', 1)->avg('price');

DB::table('users')->groupBy('account_id')->having('account_id', '>', 100)->get();
DB::table('users')->groupBy('account_id')->havingRaw('account_id > 100')->get();

事务

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

DB::beginTransaction();
DB::rollBack();
DB::commit();