Laravel & Lumen 数据库操作速查

摘要：在中执行数据库操作有两种方式，一种是使用外观对象的静态方法直接执行查询，另外一种是使用类的静态方法实际上也是的实现，使用静态访问方式访问的方法，内部采用了魔术方法代理了对成员方法的访问。在闭包函数中，如果返回，则会停止后续的处理。

在Laravel中执行数据库操作有两种方式，一种是使用DB外观对象的静态方法直接执行sql查询，另外一种是使用Model类的静态方法（实际上也是Facade的实现，使用静态访问方式访问Model的方法，内部采用了__callStatic魔术方法代理了对成员方法的访问。

$results = DB::select("select * from users where id = ?", [1]);
foreach ($results as $res) {
    echo $res->name;
}

返回结果为数组，数组中每一个值为一个StdClass对象。

也可以使用命名绑定，推荐使用这种方式，更加清晰一些

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

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

foreach ($users as $user)
{
    var_dump($user->name);
}

使用first方法返回单行数据，该方法返回的是一个stdClass对象

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

如果只需要一列的值，则可以使用value方法直接获取单列的值

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

该方法用于数据表中有大量的数据的操作，每次从结果集中取出一部分，使用闭包函数进行处理，然后再处理下一部分，该命令一般用于Artisan命令行程序中处理大量数据。

DB::table("users")->chunk(100, function($users)
{
    foreach ($users as $user)
    {
        //
    }
});

在闭包函数中，如果返回false，则会停止后续的处理。

比如我们希望查询出角色表中所有的title字段值

$titles = DB::table("roles")->pluck("title");

foreach ($titles as $title) {
    echo $title;
}

这里的pluck函数有两个参数

Collection pluck( string $column, string|null $key = null)

第一个参数为要查询的列，第二个参数是每一列的key

$roles = DB::table("roles")->pluck("title", "name");

foreach ($roles as $name => $title) {
    echo $title;
}

查询构造器也提供了一些聚集函数如count，max，min，avg，sum等

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

查询指定的列

$users = DB::table("users")->select("name", "email as user_email")->get();

如果已经指定了select，但是又希望再次添加一些字段，使用它addSelect方法

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

查询不同的结果distinct

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

使用DB::raw方法可以向查询中注入需要的sql片段，但是非常不推荐使用该方法，用不好会 产生sql注入

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

使用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();

使用leftJoin方法执行左连接操作，参数和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();

如果join约束中要使用列值与指定数组比较，则可以使用where和OrWhere方法

DB::table("users")
   ->join("contacts", function ($join) {
       $join->on("users.id", "=", "contacts.user_id")
            ->where("contacts.user_id", ">", 5);
   })
   ->get();

要使用union操作，可以先创建一个query，然后再使用union方法去绑定第二个query

$first = DB::table("users")
            ->whereNull("first_name");

$users = DB::table("users")
            ->whereNull("last_name")
            ->union($first)
            ->get();

同样，unionAll方法也是可以使用的，参数与union相同。

使用where方法为查询增加where条件，该函数一般需要三个参数：列名，操作符（任何数据库支持的操作符都可以），列值。

$users = DB::table("users")->where("votes", "=", 100)->get();
$users = DB::table("users")->where("votes", 100)->get();

为了方便起见，如果只提供两个参数，则默认第二个参数为=，执行相等匹配。

$users = DB::table("users")
      ->where("votes", ">=", 100)
      ->get();

$users = DB::table("users")
      ->where("votes", "<>", 100)
      ->get();

$users = DB::table("users")
      ->where("name", "like", "T%")
      ->get();

where条件也可以使用数组提供：

$users = DB::table("users")->where([
    ["status","1"],
    ["subscribed","<>","1"],
])->get();

如果where条件要使用or操作，则使用orWhere方法

$users = DB::table("users")
     ->where("votes", ">", 100)
     ->orWhere("name", "John")
     ->get();

whereBetween / whereNotBetween

$users = DB::table("users")
    ->whereBetween("votes", [1, 100])->get();
$users = DB::table("users")
    ->whereNotBetween("votes", [1, 100])->get();

whereIn / whereNotIn

$users = DB::table("users")
     ->whereIn("id", [1, 2, 3])
     ->get();
$users = DB::table("users")
     ->whereNotIn("id", [1, 2, 3])
     ->get();

whereNull / whereNotNull

$users = DB::table("users")
     ->whereNull("updated_at")
     ->get();
$users = DB::table("users")
     ->whereNotNull("updated_at")
     ->get();

参数组（嵌套条件）

DB::table("users")
  ->where("name", "=", "John")
  ->orWhere(function ($query) {
      $query->where("votes", ">", 100)
            ->where("title", "<>", "Admin");
  })
  ->get();

上述代码等价于下列sql

select * from users where name = "John" or (votes > 100 and title <> "Admin")

whereExists (where exist)

DB::table("users")
  ->whereExists(function ($query) {
      $query->select(DB::raw(1))
            ->from("orders")
            ->whereRaw("orders.user_id = users.id");
  })
  ->get();

上述代码与下列sql等价

select * from users
where exists (
    select 1 from orders where orders.user_id = users.id
)

MySQL 5.7和Postgres数据库中提供了新的数据类型json，对json提供了原生的支持，使用->可以对json列进行查询。

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

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

$users = DB::table("users")
      ->orderBy("name", "desc")
      ->get();
      
$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();

要限制查询返回的结果行数，或者是跳过指定行数的结果（OFFSET），可以使用skip和take方法

$users = DB::table("users")->skip(10)->take(5)->get();

插入操作与select操作类似,使用insert函数

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

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，则可以使用insertGetId方法

$id = DB::table("users")->insertGetId(
    ["email" => "john@example.com", "votes" => 0]
);

执行DB中的update后，会返回 操作影响的数据行数

DB::update("update users set votes = 100 where name = ?", ["John"]);

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

执行DB中的delete后，会返回 操作影响的数据行数

DB::delete("delete from users");

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

如果希望truncate整个表，则使用truncate方法

DB::table("users")->truncate();

使用sharedLock方法可以避免选定的行在事务提交之前被修改

DB::table("users")->where("votes", ">", 100)->sharedLock()->get();

另外lockForUpdate方法可以避免其它的共享锁修改或者是选定

DB::table("users")->where("votes", ">", 100)->lockForUpdate()->get();

使用transaction方法的callback函数执行事务处理

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

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

在回调函数中，抛出任何异常都会导致事务回滚

如果需要手动管理事务，则使用如下函数

DB::beginTransaction();
DB::rollback();
DB::commit();

使用DB类的静态方法启用的事务不仅对普通sql查询有效，对Eloquent ORM同样有效，因为它内部也是调用了DB类的数据库连接。

查看请求执行的sql日志记录，需要先执行enableQueryLog开启，然后执行getQueryLog获取

DB::connection()->enableQueryLog();
$queries = DB::getQueryLog();

执行一般的sql语法

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

监听查找事件，可以用来对执行的sql进行记录

DB::listen(function($sql, $bindings, $time)
{
    // $query->sql
    // $query->bindings
    // $query->time

});

获取某个数据库连接

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

如果还不能满足需求，可以获取PDO对象

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

这样不管什么操作都可以做了吧

另外含有两个方法，用于重新连接到指定数据库和断开连接

DB::reconnect("foo");
DB::disconnect("foo")d;

参考： Laravel 5.2 官方文档