【Laravel笔记】Mysql 数据库操作详解

Laravel 提供了强大的数据库操作，本人使用最多的是Mysql，所以本文只针对此数据库学习记录，在我自己开发的CMS系统里，也封闭了数据库的基本操作，能满足我日常的需求，但和Laravel比起来简直太弱鸡了，本人不是太喜欢ORM方式操作数据库，更喜欢使用原生语句，这样更有助于性能的优化。

一、数据库配置

数据库的配置文件在 config/database.php 文件中。你可以在这个文件中配置Laravel支持的各种数据库连接，并指定默认的数据库连接。这里只针对Mysql说明，打开配置文件，找到下面项：

'default' => env('DB_CONNECTION', 'mysql'), //默认使用mysql数据库

'mysql' => [
    'driver' => 'mysql',//驱动类型
    'url' => env('DATABASE_URL'),//数据库连接串的方式支持：mysql://root:password@127.0.0.1/forge?charset=UTF-8 优先使用此项
    'host' => env('DB_HOST', '127.0.0.1'),//数据库服务器
    'port' => env('DB_PORT', '3306'),//连接端口
    'database' => env('DB_DATABASE', 'forge'),//数据库名
    'username' => env('DB_USERNAME', 'forge'),//用户名
    'password' => env('DB_PASSWORD', ''),//登陆密码
    'unix_socket' => env('DB_SOCKET', ''),//socket连接
    'charset' => 'utf8mb4',//默认字符
    'collation' => 'utf8mb4_unicode_ci',//字段使用的默认字符
    'prefix' => 'kwok_',//表前缀设置
    'prefix_indexes' => true,
    'strict' => true,//读写同步启用
    'engine' => null,//引擎
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

可以看到很多以env函数开始的配置，这说明 先从.env读取不成功的情况下，才使用后面的默认值，目前我还不知道官方使用.env的目的是否为了保护安全，但我使用的时候去掉env函数，直接将配置信息写到php文件里的。

 二、使用原生语句CRUD操作

我们通常在上一篇里讲到的控制器里引用数据库操作包（use IlluminateSupportFacadesDB;）对数据进行增、删、改、查操作，语句也非常简洁。

一旦配置好数据库连接，你就可以使用 DB Facade 来执行查询。它为每种类型的查询都提供了相应的方法：select、update、insert、delete 以及 statement。

原生语句没有查询构造器使用起来方便，此处做为扩展知识了解即可，重点请关注后面内容。

1、DB::select 查询操作

<?php

namespace App/Http/Controllers;
use App/Http/Controllers/Controller;
use Illuminate/Support/Facades/DB;//引入DB包
class UserController extends Controller
{
    public function index()
    {
        $users = DB::select('SELECT * FROM `users` WHERE `active` = ?', [1]);//查询ID=1的用户，预查询以防止sql注入
        return view('user.index', ['users' => $users]);
    }
}

select 方法将始终返回一个包含查询结果的数组。数组中的每个结果都对应一个数据库记录的 stdClass 对象：

如果要访问返回的对象，我们应该使用下面的方式，而不是数组：

$users = DB::select('select * from users');//stdClass 对象
foreach ($users as $user) {
    echo $user->name;//读取对象里的字段
}

预查询中除了可以使用“？”以外，还可以使用绑定参数的形势更明确的区别查询：

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

2、DB::insert 插入操作

跟 select 方法一样，该方法的第一个和第二个参数分别是原生 SQL 语句和绑定的数据：

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Kwok']);//插入ID=1，name=Kwok的用户

3、DB::update 更新操作

方法用于更新数据库中现有的记录。该方法将会返回受到本次操作影响的记录行数。

$affected = DB::update(
    'update users set votes = 100 where name = ?',
    ['Kwok']
);//$affected 应该返回1行，如果votes已是100，则返回0

4、DB::delete 删除操作

与 update 方法一样，将返回受到本次操作影响的记录行数：

$deleted = DB::delete('delete from users');//无限制的删除了所有用户

5、通用方法执行任何sql语句

虽然可以在此方法里运行增、删、改、查的操作，但使用这个语句的时候要注意安全哦~因为我们也可以删除整个表：

DB::statement('drop table users'); //干掉整个表~

6、不安全的非预处理查询：请永远不要让用户输入的值出现在未预处理的语句里。

DB::unprepared('update users set votes = 100 where name = "Kwok"');

此方法一般用于导入数据、第一次安装CMS时使用。尽量避免在线上业务使用，如果在事务中使用statement 和 unprepared 方法时，需要留意导致 隐匿提交 的sql语句，这些语句将会导致数据库引擎间接提交整个事务，从而让 Laravel 不知道数据库的事务级别。其中一个例子是创建数据表：

DB::unprepared('create table a (col varchar(1) null)');

三、查询构造器

查询构造器是一种仅次于ORM操作的方法，查询生成器提交了一些常用的且非常方便的数据操作封装，可以用于执行程序中大多数操作，基本可以替换上面的原生语句操作。

如果要看原生语句，请使用dd和dump方法：

DB::table('users')->where('votes', '>', 100)->dd();//显示调试信息，然后停止

DB::table('users')->where('votes', '>', 100)->dump();//显示调试信息，但允许请求继续执行

1、查询操作->get()

使用 DB facade 提供的 table 方法开始查询。table 方法为指定的表返回一个链式查询构造器实例，允许在查询上链接更多约束，最后使用 get 方法检索查询结果： 

<?php

namespace AppHttpControllers;
use AppHttpControllersController;
use IlluminateSupportFacadesDB;//引用数据库操作类

class UserController extends Controller
{
    public function index()
    {
        $users = DB::table('users')->get();//获取users表里所有的用户
        return view('user.index', ['users' => $users]);//返回给视图使用
    }
}

仔细看代码，和上面讲到的DB::select是不是一样的，只是封装后的使用方法更易阅读；

也是返回一个  stdClass 对象，然后遍历方式也同上面的代码，这里不重复说明。

技巧：Laravel 集合提供了各种及其强大的方法来映射和裁剪数据。有关 Laravel 集合的更多信息，请查看集合文档。

2、查询操作：条件限制 ->where()

$user = DB::table('users')->where('name', 'Kwok');//查询name=Kwok的用户
//select * from users where `name` = 'Kwok'
return $user->email;//返回用户的邮箱

3、查询操作：只返回首行数据 ->first()

$user = DB::table('users')->where('name', 'Kwok')->first();
//select * from users where `name` = 'Kwok' limit 1

4、查询操作：限制字段 ->value()

上面都是使用的select *查询，对于性能是有影响的，因为我们只需要用户的邮箱，可以使用 ->value方法指定查询的字段：

//限制单个字段
$email = DB::table('users')->where('name', 'Kwok')->value('email');
//select `email` from users where `name` = 'Kwok'

//通过 id 字段值获取单行数据，可以使用 find 方法
$user = DB::table('users')->find(3);
//select * from users where `id` = 3

//限制多个字段
$users = DB::table('users')
            ->select('name', 'email as user_email')
            ->get();

5、查询操作：某一列的值 ->pluck()

$titles = DB::table('users')->pluck('title');
//select `title` from users
foreach ($titles as $title) {
    echo $title;
}

//提供第二个参数来指定结果集中要作为键的列
$titles = DB::table('users')->pluck('title', 'name');
foreach ($titles as $name => $title) {
    echo $name.'->'.$title;//用户名 -> 标题
}

6、查询操作：大量数据分块->orderBy('字段')->pluck()

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        //每次查询 100条，查询整个表
        if($user->name = 'Kwok') return false;//找到用户kwok时，不再查询
    }
});

如果在对结果进行分块时更新数据库记录，那分块结果可能会以意想不到的方式更改。如果您打算在分块时更新检索到的记录，最好使用 chunkById 方法。此方法将根据记录的主键自动对结果进行分页:

DB::table('users')->where('active', false)//查询满足条件active=false的用户
    ->chunkById(100, function ($users) {//每次查询100条，并将结果交给闭包处理
        foreach ($users as $user) {//遍历结果
            DB::table('users')
                ->where('id', $user->id)//查询当前用户
                ->update(['active' => true]);//将当前用户的active= true
        }
});

注意：当在更新或删除块回调中的记录时，对主键或外键的任何更改都可能影响块查询。这可能会导致记录未包含在分块结果中。

7、查询操作：流式传输结果->orderBy('字段')->lazy()->each()

lazy 方法的工作方式类似于 chunk 方法，因为它以块的形式执行查询。但是，lazy() 方法不是将每个块传递给回调，而是返回一个 LazyCollection，它可以让您与结果进行交互单个流：

DB::table('users')->orderBy('id')->lazy()->each(function ($user) {
    //通过闭包处理当前数据
});

如果同上操作，更新用户时最好使用 lazyById 或 lazyByIdDesc 方法。 这些方法将根据记录的主键自动对结果进行分页：

DB::table('users')->where('active', false)
    ->lazyById()->each(function ($user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['active' => true]);
    });

注意：在迭代记录时更新或删除记录时，对主键或外键的任何更改都可能影响块查询。这可能会导致记录不包含在结果中。

7、查询操作：聚集

查询构建器还提供了多种检索聚合值的方法，例如 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');//平均价格

8、查询操作：判断记录是否存在

除了通过 count 方法可以确定查询条件的结果是否存在之外，还可以使用 exists 和 doesntExist 方法：

if (DB::table('orders')->where('finalized', 1)->exists()) {
    // 记录存在
}

if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
    // 记录不存在
}

9、查询操作：其它扩展查询

//distinct 方法会强制让查询返回的结果不重复：
$users = DB::table('users')->distinct()->get();

//如果你已经有了一个查询构造器实例，并且希望在现有的查询语句中加入一个字段，那么你可以使用 addSelect 方法
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

//原生表达式:有时可能需要在查询中插入任意字符串。要创建原始字符串表达式，可以使用 DB facade 提供的 raw 方法：
$users = DB::table('users')
             ->select(DB::raw('count(*) as user_count, status'))
             ->where('status', '<>', 1)
             ->groupBy('status')
             ->get();
//注意：原生表达式将会被当做字符串注入到查询中，因此你应该极度小心避免创建 SQL 注入的漏洞。


//代替 DB::raw，将原生表达式插入查询的各个部分。注意，Laravel 无法保证所有使用原生表达式的查询都受到防 SQL 注入漏洞保护。
$orders = DB::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();
//selectRaw 方法可以代替 addSelect(DB::raw(...))。该方法的第二个参数是可选项，值是一个绑定参数的数组：

//whereRaw 和 orWhereRaw 方法将原生的「where」注入到你的查询中。这两个方法的第二个参数是可选项，值是一个绑定参数的数组：
$orders = DB::table('orders')
                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                ->get();

//havingRaw 和 orHavingRaw 方法可以用于将原生字符串作为「having」语句的值。这两个方法的第二个参数是可选项，值是一个绑定参数的数组：
$orders = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > ?', [2500])
                ->get();

//orderByRaw 方法可用于将原生字符串设置为「order by」语句的值：
$orders = DB::table('orders')
                ->orderByRaw('updated_at - created_at DESC')
                ->get();

//groupByRaw 方法可以用于将原生字符串设置为 group by 语句的值：
$orders = DB::table('orders')
                ->select('city', 'state')
                ->groupByRaw('city, state')
                ->get();

10、查询操作：Joins

查询构造器也还可用于向查询中添加连接子句。若要执行基本的「内链接」，你可以对查询构造器实例使用 join 方法。传递给 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 或者 right join 代替 inner join ，可以使用 leftJoin 或者 rightJoin 方法。这两个方法与 join 方法用法相同：

//leftJoin 左连接
$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

//右连接
$users = DB::table('users')
            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

你可以使用 crossJoin 方法执行「交叉连接」。交叉连接在第一个表和被连接的表之间会生成笛卡尔积：

$sizes = DB::table('sizes')
            ->crossJoin('colors')
            ->get();

您还可以指定更高级的联接子句。首先，将闭包作为第二个参数传递给 join 方法。闭包将收到一个 illumbDatabaseQueryJoinClause 实例，该实例允许您指定对 join 子句的约束：

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

如果你想要在连接上使用「where」风格的语句，你可以在连接上使用 JoinClause 实例中的 where 和 orWhere 方法。这些方法会将列和值进行比较，而不是列和列进行比较：

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

你可以使用 joinSub，leftJoinSub 和 rightJoinSub 方法关联一个查询作为子查询。他们每一种方法都会接收三个参数：子查询，表别名和定义关联字段的闭包。

如下面这个例子，获取含有用户最近一次发布博客时的 created_at 时间戳的用户集合：

$latestPosts = DB::table('posts')
                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->where('is_published', true)
                   ->groupBy('user_id');

$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })->get();

查询构造器还提供了一种简洁的方式将两个或者多个查询「联合」在一起。例如，你可以先创建一个查询，然后使用 union 方法来连接更多的查询：

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

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

查询构造器不仅提供了 union 方法，还提供了一个 unionAll 方法。当查询结合 unionAll 方法使用时，将不会删除重复的结果。unionAll 方法的用法和 union 方法一样。

11、查询操作：Where语句高级应用

你可以在 where 语句中使用查询构造器的 where 方法。调用 where 方法需要三个基本参数。第一个参数是字段的名称。第二个参数是一个操作符，它可以是数据库中支持的任意操作符。第三个参数是与字段比较的值。

//在 users 表中查询 votes 字段等于 100 并且 age 字段大于 35 的数据：
$users = DB::table('users')
                ->where('votes', '=', 100)
                ->where('age', '>', 35)
                ->get();

//为了方便起见。如果你想要比较一个字段的值是否 等于 给定的值。你可以将这个给定的值作为第二个参数传递给 where 方法。那么，Laravel 会默认使用 = 操作符：
$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%')//以T开始的用户名
                ->get();

//您也可以将一个条件数组传递给 where 方法。通常传递给 where 方法的数组中的每一个元素都应该包含 3 个元素：
$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

注意：PDO 不支持绑定字段名。因此，你不应该允许让用户输入字段名进行查询引用，包括结果集「排序」语句。

当链式调用多个 where 方法的时候，这些「where」语句将会被看成是 and 关系。另外，您也可以在查询语句中使用 orWhere 方法来表示 or 关系。orWhere 方法接收的参数和 where 方法接收的参数一样：

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

//如果您需要在括号内对 or 条件进行分组，那么可以传递一个闭包作为 orWhere 方法的第一个参数：
$users = DB::table('users')
            ->where('votes', '>', 100)
            ->orWhere(function($query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();
//select * from users where votes > 100 or (name = 'Abigail' and votes > 50)

注意：为了避免应用全局作用出现意外，您应该用 orWhere 调用这个分组。

12、查询操作：JSON Where语句

如果你的数据库支持JSON类型( MySQL 5.7+),可以在where里使用 -> 操作符来查询 JSON 字段：

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

//您可以使用 whereJsonContains 方法来查询 JSON 数组。但是 SQLite 数据库不支持该功能：
$users = DB::table('users')
                ->whereJsonContains('options->languages', 'en')
                ->get();

//如果您的应用使用的是 MySQL 或者 PostgreSQL 数据库，那么您可以向 whereJsonContains 方法中传递一个数组类型的值：

$users = DB::table('users')
                ->whereJsonContains('options->languages', ['en', 'de'])
                ->get();

//你可以使用 whereJsonLength 方法来查询 JSON 数组的长度：
$users = DB::table('users')
                ->whereJsonLength('options->languages', 0)
                ->get();

$users = DB::table('users')
                ->whereJsonLength('options->languages', '>', 1)
                ->get();

关于查询 还有更多的使用方法，如：whereBetween / orWhereBetween(两个值之间)，whereNotBetween / orWhereNotBetween(不在两个值之间)，whereIn / whereNotIn / orWhereIn / orWhereNotIn(在给定的数组里)，whereNull / whereNotNull / orWhereNull / orWhereNotNull(给定的值是否为NULL)，whereDate / whereMonth / whereDay / whereYear / whereTime(比较字段的值与给定的日期值是否相等),whereColumn / orWhereColumn(给定的字段的值是否相等),想要了解这些知识可以查询官方文档。

13、查询操作：Limit 和 Offset

你可以使用 skip 和 take 方法去限制查询结果的返回数量或者在查询结果中跳过给定数量:

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

或者，你可以使用 limit 和 offset 方法。这些方法在功能上等同于 take 和 skip 方法，如下:

$users = DB::table('users')
                ->offset(10)
                ->limit(5)
                ->get();

当传入 HTTP 请求有一个给定的值的时候你才需要使用一个 where 语句。你可以使用 when 方法去实现:

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

$users = DB::table('users')
                ->when($role, function ($query, $role) {
                    return $query->where('role_id', $role);
                })
                ->get();

14、插入操作

查询构建器还提供了一个「插入」方法，可用于将记录插入到数据库表中。 insert 方法接受一个列名和值的数组：

DB::table('users')->insert([
    'email' => 'kayla@example.com',
    'votes' => 0
]);

你可以通过传递数组数组一次插入多条记录。每个数组代表一个应该插入到表中的记录：

DB::table('users')->insert([
    ['email' => 'picard@example.com', 'votes' => 0],
    ['email' => 'janeway@example.com', 'votes' => 0],
]);

insertOrIgnore 方法将在将记录插入数据库时忽略错误：

DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => 'sisko@example.com'],
    ['id' => 2, 'email' => 'archer@example.com'],
]);

如果数据表有自增 ID ，使用 insertGetId 方法来插入记录可以返回 ID 值：

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

15、更新操作

upsert 方法将插入不存在的记录，并使用您可以指定的新值更新已经存在的记录。该方法的第一个参数包含要插入或更新的值，而第二个参数列出了在关联表中唯一标识记录的列。 该方法的第三个也是最后一个参数是一个列数组，如果数据库中已经存在匹配的记录，则应该更新这些列：

DB::table('flights')->upsert([
    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
], ['departure', 'destination'], ['price']);

在上面的例子中，Laravel 会尝试插入两条记录。如果已经存在具有相同 departure 和 destination 列值的记录，Laravel 将更新该记录的 price 列。

除了将记录插入数据库之外，查询构建器还可以使用 update 方法更新现有记录。 update 方法与 insert 方法一样，接受一个列和值对数组，指示要更新的列。 update 方法返回受影响的行数。您可以使用 where 子句限制 update 查询：

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

有时您可能希望更新数据库中的现有记录，或者如果不存在匹配记录则创建它。在这种情况下，可以使用 updateOrInsert 方法。

DB::table('users')
    ->updateOrInsert(
        ['email' => 'john@example.com', 'name' => 'John'],
        ['votes' => '2']
    );

更新 JSON 字段时，你可以使用 -> 语法访问 JSON 对象中相应的值。

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

15、自增自减

查询构建器还提供了方便的方法来增加或减少给定列的值。这两种方法都至少接受一个参数：要修改的列。可以提供第二个参数来指定列应该增加或减少的数量：

DB::table('users')->increment('votes');//+1

DB::table('users')->increment('votes', 5);//+5

DB::table('users')->decrement('votes');//-1

DB::table('users')->decrement('votes', 5);//-5

您还可以在操作期间指定要更新的其他列：

DB::table('users')->increment('votes', 1, ['name' => 'John']);//votes+1后顺便把用户名改为John

16、删除操作

查询构建器的 delete 方法可用于从表中删除记录。 delete 方法返回受影响的行数。您可以通过在调用 delete 方法之前添加 “where” 子句来限制 delete 语句：

$deleted = DB::table('users')->delete();//删除表里所有数据，自增ID保留

$deleted = DB::table('users')->where('votes', '>', 100)->delete();//按条件操作删除

DB::table('users')->truncate();//清空表

查询构建器还包括一些函数，可帮助您在执行 select 语句时实现「悲观锁」。 要使用「共享锁」执行语句，您可以调用 sharedLock 方法。共享锁可防止选定的行被修改，直到您的事务被提交：

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

或者，您可以使用 lockForUpdate 方法。「update」锁可防止所选记录被修改或被另一个共享锁选中：

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

 

 

除非注明，网络人的文章均为原创，转载请以链接形式标明本文地址：https://www.55mx.com/post/206