14.）PHPWeb开发框架~Laravel操作数据库DB类CRUD增删改查的使用

按照MVC 的架构，对数据的操作应该放在 Model 中完成，但如果不使用Model，我们也可以用 laravel框架提供的 DB 类操作数据库。而且，对于某些极其复杂的sql，用Model 已经很难完成，需要开发者自己手写sql语句，使用用 DB 类去执行原生sql。 laravel 中 DB 类的基本用法DB::table(‘tableName’)获取操作tableName表的实例。

Laravel 的数据库查询构造器为创建和运行数据库查询提供了一个方便的接口。它可用于执行应用程序中大部分数据库操作，且可在所有支持的数据库系统上运行。

Laravel 的查询构造器使用 PDO 参数绑定来保护您的应用程序免受 SQL 注入攻击。因此没有必要清理作为绑定传递的字符串。

注意：PDO 不支持绑定列名。因此，不能让用户通过输入来指定查询语句所引用的列名，包括 order by 字段等等。如果必须要允许用户通过选择某些列来进行查询，请始终根据允许列的白名单来校验列名。

1、数据表的创建与配置

建立数据库：

建数据库的方式：

①sql语句

②图形界面

（1）创建数据库和测试表

create table member(
  id int primary key auto_increment,
  name varchar(32) not null,
  age tinyint unsigned not null,
emailvarchar(32) not null
)engine myisam charset utf8;

（2）数据库在laravel框架中的配置

在.env文件里面，

也可以在config目录下面的database.php文件里面配置，使用env函数，表示先从env文件里面获取，如果获取成功则使用，如果获取失败，则使用env函数的第二个参数。

注意：如果是php artisan serve方式启动的，修改了配置文件，则需要重新启动，才能读取修改后的配置文件；如果是wamp/lamp等环境则不需要重启。

（3）在Test控制器中引入DB门面；

DB门面在app.php中已经定义别名DB，可以直接use，不需要写太长空间

（4）定义增删改查需要的路由；

增加：/home/test/add
删除：/home/test/del
修改：/home/test/update
查询：/home/test/select

数据库：查询构造器：https://learnku.com/docs/laravel/7.x/queries/7494

2、增加信息（insert）

对数据库中的某个表增加数据主要有两个函数可以实现，分别是insert()和insertGetId()

insert(数组)可以同时添加一条或多条，返回值是布尔类型。
insertGetId(一维数组)，只能添加一条数据，返回自增的id。

注意：DB::table(‘前缀的表名’) -> insert(); 连贯操作/链式操作

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]
]);
// insertOrIgnore 方法用于忽略重复插入记录到数据库的错误：
DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => 'taylor@example.com'],
    ['id' => 2, 'email' => 'dayle@example.com']
]);

如果数据表有自增 ID , 使用 insertGetId 方法来插入记录并返回 ID 值

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

注意：当使用 PostgreSQL 时，insertGetId 方法将默认把 id 作为自动递增字段的名称。如果你要从其他「序列」来获取 ID ，则可以将字段名称作为第二个参数传递给 insertGetId 方法。

向member表同时添加多条数据，

数据表的数据插入结果：

3、修改数据( update)

当然，除了插入记录到数据库中，查询构造器也可以通过 update 方法更新已有的记录。 update 方法和 insert 方法一样，接受包含要更新的字段及值的数组。你可以通过 where 子句对 update 查询进行约束：

数据修改可以使用update()、increment()和decrement()函数来实现。

Update表示可以修改整个记录中的全部字段；
Increment和decrement表示修改数字字段的数值（递增或者递减）

典型的应用：记录登录次数、积分的增加；

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

案例：把id=1的名称，改名为‘张三丰’

->where()->update([ ])

Where语法：

->where(字段,运算符,值) 如果运算符为“=”，则第二个参数可以不写

其返回的结果表示受到影响的行数。

数据表的结果：

案例：把id=1的用户年龄字段值加10；【了解】

查询构造器还为给定字段的递增或递减提供了方便的方法。此方法提供了一个比手动编写 update 语句更具表达力且更精练的接口。

这两种方法都至少接收一个参数：需要修改的列。第二个参数是可选的，用于控制列递增或递减的量：

DB::table(‘ member’)->increment(‘votes’); 每次+1

DB::table(‘ member’)->increment(‘votes’,5); 每次+5

DB::table(‘ member’)->decrement(‘votes’); 每次-1

DB::table(‘ member’)->decrement(‘votes’,5); 每次-5

你也可以在操作过程中指定要更新的字段：

DB::table('users')->increment('votes', 1, ['name' => 'John']);

有时您可能希望更新数据库中的现有记录，或者如果不存在匹配记录则创建它。在这种情况下，可以使用 updateOrInsert 方法。 updateOrInsert 方法接受两个参数：一个用于查找记录的条件数组，以及一个包含要更该记录的键值对数组。

updateOrInsert 方法将首先尝试使用第一个参数的键和值对来查找匹配的数据库记录。如果记录存在，则使用第二个参数中的值去更新记录。如果找不到记录，将插入一个新记录，更新的数据是两个数组的集合：

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

4、查询数据(get)

（1）获取所有行

你可以使用 DB facade 里的 table 方法来开始查询。 table 方法为给定的表返回一个查询构造器实例，允许你在查询上链式调用更多的约束，最后使用 get 方法获取结果：

可以在app.php中配置DB的引入别名

'aliases' => [
        'DB' => Illuminate\Support\Facades\DB::class,
        'View' => Illuminate\Support\Facades\View::class,
    ],

<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
// use Illuminate\Support\Facades\DB;
use DB;
class UserController extends Controller
{
    /**
     * 显示所有应用程序的用户列表。
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::table('users')->get();
        // 和tp一样返回的是集合对象，我们可以调用toArray转换为数组
        $users = DB::table('users')->get()->toArray();
        return view('user/index', ['users' => $users]);
    }
}

该 get 方法返回一个包含 Illuminate\Support\Collection 的结果，其中每个结果都是 PHP StdClass 对象的一个实例。你可以访问字段作为对象的属性来访问每列的值：

案例1：获取member表中所有的数据

DB::table(‘member’)->get(); //相当于select * from member;

返回值是一个集合对象，

返回值：

完成遍历取出的数据：

Get查询的结果每一行的记录是对象的形式，不是数组。

注意：where方法之后继续调用where方法。

-> where() -> where() -> where().. 这个语法是并且（and）关系语法。

-> where() -> orWhere() -> orWhere()… 这个语法是或者（or）关系语法。

Orwhere方法的参数与where一致。

案例3：查询id>2且年龄<21

（2）获取单行数据

如果你只需要从数据表中获取一行数据，你可以使用 first 方法。该方法返回一个 StdClass 对象

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

如果你甚至不需要整行数据，则可以使用 value 方法从记录中获取单个值。该方法将直接返回该字段的值：

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

如果是通过 id 字段值获取一行数据，可以使用 find 方法：

$user = DB::table('users')->find(3);

DB::table(‘member’)->where(‘id’,’1’)->first();//返回值是一个对象

等价于limit 1

返回结果是对象：

DB::table(‘member’)->where(‘id’,’1’)->value(‘name’); // 返回单个字段的值

返回结果：

（3）获取一列的值

如果你想获取包含单列值的集合，则可以使用 pluck 方法。在下面的例子中，我们将获取角色表中标题的集合：

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

你还可以在返回的集合中指定字段的自定义键名：(会将 roles 表中的 name 字段当做键名，title 字段当做键值返回)

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

（4）获取某些字段数据

当然你可能并不总是希望从数据库表中获取所有列。使用 select 方法，你可以自定义一个 select 查询语句来查询指定的字段：

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

distinct 方法会强制让查询返回的结果不重复：

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

如果你已经有了一个查询构造器实例，并且希望在现有的查询语句中加入一个字段，那么你可以使用 addSelect 方法：

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

$users = DB::table(‘member’)->select(‘name’,’email’)->get();

$users = DB::table(‘member’)->select(‘name asuser_name’)->get();

（5）排序操作

DB::table(‘member’)->orderBy(‘age’,’desc’)->get();

（6）分页操作

DB::table(‘member’)->limit(3)->offset(2)->get();

Limit：表示限制输出的条数

Offset：从什么地方开始

组合起来等价于limit 5,5

5、删除数据(delete)【了解】

在删除中，有两种方式：物理删除（本质就是删除）、逻辑删除（本质是修改）

数据删除可以通过delete函数和truncate函数实现，

Delete表示删除记录；
Truncate表示清空整个数据表；

DB::table('table_name')->where('id','1')->delete();

查询构造器也可以使用 delete 方法从表中删除记录。在使用 delete 前，可以添加 where 子句来约束 delete 语法：

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

如果你需要清空表，你可以使用 truncate 方法，它将删除所有行，并重置自增 ID 为零：

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

例如：删除id小于3的记录

返回值表示受到影响的行数

6、执行原生SQL表达式（了解）

（1）执行任意的insert update delete语句【影响记录的语句使用statement语法】

DB::statement(“insert into member values(null,’’)”);

（2）执行任意的select语句【不影响记录的语句使用select语法】

$res = DB::select("select * frommember");

7、聚合方法

查询构造器还提供了各种聚合方法，比如 count, max，min， avg，还有 sum。你可以在构造查询后调用任何方法：

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

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

return DB::table('orders')->where('finalized', 1)->exists();
return DB::table('orders')->where('finalized', 1)->doesntExist();

数据库：查询构造器

Laravel 的数据库查询构造器为创建和运行数据库查询提供了一个方便的接口。它可以用于支持大部分数据库操作，并且在所有支持的数据库系统上统一运行。

Laravel 的查询构造器使用 PDO 参数绑定的形式，保护您的应用程序免受 SQL 注入攻击。因此不必清理因参数绑定而传入的字符串。