课程内容

介绍

Laravel 能使用原生 SQL、流畅的查询构造器 和 Eloquent ORM 在各种数据库后台与数据库进行非常简单的交互。当前 Laravel 支持四种数据库:

  • MySQL
  • PostgreSQL
  • SQLite
  • SQL Server

配置

config/database.php
但一般都写在.env下配置

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=demolv
DB_USERNAME=root3
DB_PASSWORD=1234564

读写分离

'mysql' => [
    'read' => [
        'host' => [
            '192.168.1.1',
            '196.168.1.2',
        ],
    ],
    'write' => [
        'host' => [
            '196.168.1.3',
         ],
    ],
    'sticky'    => true,
    'driver'    => 'mysql',
    'database'  => 'database',
    'username'  => 'root',
    'password'  => '',
    'charset'   => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix'    => '',
],

多个数据库连接

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

原生运行语句

原生写法

DB::select('select * from users where active = ?', [1]);
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
DB::update('update users set votes = 100 where name = ?', ['John']);
DB::delete('delete from users');
//没有返回
DB::statement('drop table users');

除了使用 ? 表示参数绑定外,你也可以使用命名绑定来执行一个查询:

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

查询构造器

//插入数据
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]
]);

//忽略重复插入记录到数据库

DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => 'taylor@example.com'],
    ['id' => 2, 'email' => 'dayle@example.com']
]);
//返回插入的ID
$id = DB::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
);

//更新或者新增,第一个数组表示查询的数据条件,第二个数组表示更新或插入的数据
DB::table('users')
    ->updateOrInsert(
        ['email' => 'john@example.com', 'name' => 'John'],
        ['votes' => '2']
    );

//更新
$affected = 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')->where('votes', '>', 100)->delete();
//清空数据,重设表ID
DB::table('users')->truncate();

$users = DB::table('users')->get();
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');
//数据是否存在                                
DB::table('orders')->where('finalized', 1)->exists();
//
DB::table('orders')->where('finalized', 1)->doesntExist();
//查询返回的结果不重复
$users = DB::table('users')->distinct()->get();
//指定一个 Select 语句
$users = DB::table('users')->select('name', 'email as user_email')->get();

//加入一个字段
$query = DB::table('users')->select('name');

$users = $query->addSelect('age')->get();
单行
$user = DB::table('users')->where('name', 'John')->first();
//单个数据
$email = DB::table('users')->where('name', 'John')->value('email');
//获取一列
$titles = DB::table('roles')->pluck('title');

锁数据列 sharedLock()

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

锁行数据 lockForUpdate()

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

Eloquent Orm

忽略异常获取单个数据

App\Flight::findOrFail(1);
 App\Flight::where('legs', '>', 100)->firstOrFail();

插入、更新

$flight = new Flight;
$flight->name = $request->name;
$flight->save();

$flight = App\Flight::find(1);
$flight->name = 'New Flight Name';
$flight->save();

批量更新, 更新的模型不会触发 saved 和 updated 事件

App\Flight::where('active', 1)
          ->where('destination', 'San Diego')
          ->update(['delayed' => 1]);

删除

$flight = App\Flight::find(1);

$flight->delete();
App\Flight::destroy(1);

App\Flight::destroy(1, 2, 3);

App\Flight::destroy([1, 2, 3]);

App\Flight::destroy(collect([1, 2, 3]));

数据库事务

匿名函数

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

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

手动开启

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

评论区 (0)

没有记录
支持 markdown,图片截图粘贴拖拽都可以自动上传。