laravel 資料庫操作

huaweichenai發表於2023-02-14
Laravel資料庫

一：資料庫配置

資料庫配置檔案放置在config/database.php 檔案中

1：基礎配置

'connections' => [
    'mysql' => [
        'driver' => 'mysql',   #資料庫型別
        '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' => '',#表字首
        'strict' => true,
        'engine' => null,
    ],
],

上述的host,port,database.....使用了env函式,他使用的是.env檔案的配置項,你也可以不用.env檔案配置項,直接填寫相關資訊,如果你使用了.env檔案配置項,你需要修改.env檔案內容

2：多表配置

'connections' => [
    'mysql' => [
        'driver' => 'mysql',   #資料庫型別
        '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' => '',#表字首
        'strict' => true,
        'engine' => null,
    ],
    'mysql001' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'port' => '3306',
        'database' => 'blog',
        'username' => 'root',
        'password' => 'root',
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => false,
        'engine' => null,
    ],
],

3：主從資料庫配置

'connections' => [
    'mysql' => [
        //讀庫地址
        'read' => [
            'host' => [
                '192.168.1.1',
                '196.168.1.2',
            ],
        ],
        //寫庫地址
        'write' => [
            'host' => [
                '196.168.1.3',
             ],
        ],
        'driver' => 'mysql',   #資料庫型別
        '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' => '',#表字首
        'strict' => true,
        'engine' => null,
    ],
],

二：資料庫基礎操作

1：資料查詢

$users = DB::select('select * from user'); #查詢預設mysql的user表所有資料
$users = DB::select('select * from user where id = ?',[1]); #查詢預設mysql的user表id等於1的資料
$users = DB::select('select * from user where id = ? and user_no = ?',[1,'001']);#查詢預設mysql的user表id等於1且user_no等於001的資料
$users = DB::select('select * from user where id = :id and user_no = :user_no',['id'=>1,'user_no'=>'001']);#查詢預設mysql的user表id等於1且user_no等於001的資料
$article = DB::connection('mysql001')->select('select * from article'); #查詢mysql001庫的article表資料
$article = DB::connection('mysql001')->select('select * from article where id = ?',[1]); #查詢mysql001庫的article表id等於1的資料
$article = DB::connection('mysql001')->select('select * from article where id = ? and column_no = ?',[1,'COL00005']);#查詢mysql001庫的article表id等於1且column_no等於COL00005的資料
$article = DB::connection('mysql001')->select('select * from article where id = :id and column_no = :column_no',['id'=>1,'column_no'=>'COL00005']);#查詢mysql001庫的article表id等於1且column_no等於COL00005的資料

2：資料插入

DB::insert('insert into user (user_no,user_name) values (?, ?)', ['002','test']);#向預設mysql的user表插入一條資料
DB::connection('mysql001')->insert('insert into article (title,column_no,content) values (?, ?, ?)',['test','COL00005','testcontent']);#向mysql001庫的article表插入一條資料

3：資料更新

DB::update('update user set user_name = ? where id = ?', ['test',1]); #更新預設mysql的user表id等於1的user_name欄位為test
DB::connection('mysql001')->update('update article set title = ? where id = ?', ['test',1]);#更新mysql001庫的article表的id等於1的title欄位為test

4：資料刪除

DB::delete('delete from user where id = ?',[1]);刪除預設mysql的user表id等於1的資料
DB::connection('mysql001')->delete('delete from article where id = ?',[1]);刪除mysql001庫的article表id等於1的資料

5：資料庫事務

DB::transaction(function () {
    //sql操作
});
DB::transaction(function () {
    //sql操作
}, 5);
傳遞第二個可選引數給 transaction 方法，該引數定義在發生死鎖時應該重新嘗試事務的次數。一旦嘗試次數都用盡了，就會丟擲一個異常

6：手動操作事務

如果你想要手動開始一個事務，並且能夠完全控制回滾和提交，那麼你可以使用beginTransaction 方法實現

DB::beginTransaction();

回滾事務

DB::rollBack();

提交事務

DB::commit();

三：查詢構造器

Laravel 的資料庫查詢構造器提供了一個方便的介面來建立及執行資料庫查詢語句。它能用來執行應用程式中的大部分資料庫操作，且能在所有被支援的資料庫系統中使用

1：資料查詢

(1)：簡單查詢

//獲取所有資料
$users = DB::table('user')->get();
//獲取mysql001庫的article表資料
$article = DB::connection('mysql001')->table('article')->get();
//獲取一行資料
$users = DB::table('user')->where('id', 1)->first();
//從記錄中取出單個值。該方法將直接返回欄位的值
$users = DB::table('user')->where('id', 1)->value('user_name');
//獲取一列的值
$users = DB::table('user')->pluck('user_name');
//獲取一列的值，並以指定欄位為鍵值
$users = DB::table('user')->pluck('user_name','user_no');

(2)：分塊查詢

如果你需要運算元千條資料庫記錄，可以考慮使用 chunk 方法。這個方法每次只取出一小塊結果傳遞給 閉包 處理

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

你可以從 閉包 中返回 false 來阻止進一步的分塊的處理

DB::table('user')->orderBy('id')->chunk(100, function ($users) {
    //
    return false;
});

如果要在分塊結果時更新資料庫記錄，則塊結果可能會和預計的返回結果不一致。 因此，在分塊更新記錄時，最好使用 chunkById 方法。 此方法將根據記錄的主鍵自動對結果進行分頁：

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

(3)：聚合方法

查詢構造器還提供了各種聚合方法，比如 count, max，min， avg，還有 sum。你可以在構造查詢後呼叫任何方法：

/查詢資料條數
$users = DB::table('user')->count();
//查詢資料中最大的price值
$users = DB::table('goods')->max('price');
//查詢資料中price值的平均值
$users = DB::table('goods')->avg('price');

(4)：判斷記錄是否存在

DB::table('orders')->where('finalized', 1)->exists();//判斷記錄是否存在
return DB::table('orders')->where('finalized', 1)->doesntExist();//判斷記錄是否不存在

(5)：selects

你並不會總是想從資料表中選出所有的欄位，這時可使用 select 方法自定義一個 select 語句來指定查詢的欄位：

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

distinct 方法允許你強制讓查詢返回不重複的結果：

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

如果你已有一個查詢構造器例項，並且希望在現有的 select 語句中加入一個欄位，則可以使用 addSelect 方法：

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

(6)：原生表示式

有時候你可能需要在查詢中使用原生表示式，使用 DB::raw 方法可以建立原生表示式

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

(7)：原生方法

可以使用以下的方法代替 DB::raw 將原生表示式插入查詢的各個部分

  1. selectRaw

selectRaw 方法可以用來代替 select(DB::raw(...))。這個方法的第二個引數接受一個可選的繫結引數的陣列：

$orders = DB::table('orders')
        ->selectRaw('price * ? as price_with_tax', [1.0825])
        ->get();
  1. whereRaw / orWhereRaw

可以使用 whereRaw 和 orWhereRaw 方法將原生的 where 語句注入到查詢中。這些方法接受一個可選的繫結陣列作為他們的第二個引數

$orders = DB::table('orders')
        ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
        ->get();
  1. havingRaw / orHavingRaw

havingRaw 和 orHavingRaw 方法可用於將原生字串設定為 having 語句的值

$orders = DB::table('orders')
        ->select('department', DB::raw('SUM(price) as total_sales'))
        ->groupBy('department')
        ->havingRaw('SUM(price) > 2500')
        ->get();
  1. orderByRaw

orderByRaw 方法可用於將原生字串設定為 order by 語句的值

$orders = DB::table('orders')
        ->orderByRaw('updated_at - created_at DESC')
        ->get();

(8)：連表查詢(join)

  1. Inner 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();
  1. Left Join / Right Join 語句(左連線/右連線)
$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();
$users = DB::table('users')
            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();
  1. Cross Join 語句(交叉連線)
$users = DB::table('sizes')
            ->crossJoin('colours')
            ->get();
  1. 高階 Join 語句

你可以指定更高階的 join 語句。比如傳遞一個 閉包 作為 join 方法的第二個引數。此 閉包 接收一個 JoinClause 物件，從而在其中指定 join 語句中指定約束

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

你可以使用 joinSub，leftJoinSub 和 rightJoinSub 方法關聯一個查詢作為子查詢。他們每一種方法都會接收三個引數：子查詢，表別名和定義關聯欄位的閉包

$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();
  1. Unions查詢

查詢構造器還提供了將兩個查詢「合併」起來的快捷方式。例如，你可以先建立一個初始查詢，並使用 union 方法將它與第二個查詢進行合併

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

(9)：where語句

//id欄位的值等於 100 的查詢
$users = DB::table('users')->where('id', '=', 100)->get(); 等同於 $users = DB::table('users')->where('id', 100)->get();

//id欄位的值大於等於 100 的查詢
$users = DB::table('users')
                ->where('id', '>=', 100)
                ->get();
                
//id欄位的值不等於 100 的查詢
$users = DB::table('users')
                ->where('id', '<>', 100)
                ->get();
//模糊查詢
$users = DB::table('users')
                ->where('name', 'like', 'T%')
                ->get();
                
$users = DB::table('users')
                ->where('name', 'like', '%T')
                ->get();
                
$users = DB::table('users')
                ->where('name', 'like', '%T%')
                ->get();
                
//status欄位等於1,subscribed欄位不等於1的查詢
$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

(10)：or查詢

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

(11)：其它 Where 語句

  1. whereBetween/whereNotBetween

whereBetween驗證欄位的值介於兩個值之間,whereNotBetween驗證欄位的值不在兩個值之間

$users = DB::table('users')
        ->whereBetween('votes', [1, 100])->get();
$users = DB::table('users')
        ->whereNotBetween('votes', [1, 100])
        ->get();
  1. whereIn / whereNotIn

whereIn 方法驗證欄位的值在指定的陣列內,whereNotIn 方法驗證欄位的值不在指定的陣列內

$users = DB::table('users')
                    ->whereIn('id', [1, 2, 3])
                    ->get();
$users = DB::table('users')
                    ->whereNotIn('id', [1, 2, 3])
                    ->get();
  1. whereNull / whereNotNull

whereNull 方法驗證欄位的值為 NULL，whereNotNull 方法驗證欄位的值不為 NULL

$users = DB::table('users')
                    ->whereNull('updated_at')
                    ->get();
$users = DB::table('users')
                    ->whereNotNull('updated_at')
                    ->get();
  1. whereDate / whereMonth / whereDay / whereYear / whereTime

whereDate 方法用於比較欄位的值和日期,whereMonth 方法用於比較欄位的值與一年的特定月份,whereDay 方法用於比較欄位的值與特定的一個月的某一天,whereYear 方法用於比較欄位的值與特定年份,whereTime 方法用於比較欄位的值與特定的時間

$users = DB::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();
$users = DB::table('users')
                ->whereMonth('created_at', '12')
                ->get();
$users = DB::table('users')
                ->whereDay('created_at', '31')
                ->get();
$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();
$users = DB::table('users')
                ->whereTime('created_at', '=', '11:20')
                ->get();
  1. whereColumn / orWhereColumn

whereColumn 方法用於驗證兩個欄位是否相等

$users = DB::table('users')
                ->whereColumn('first_name', 'last_name')
                ->get();

還可以將比較運算子傳遞給該方法

$users = DB::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

whereColumn 方法也可以傳遞一個包含多個條件的陣列。這些條件將使用 and 運算子進行連線

$users = DB::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at']
                ])->get();

(12)：引數分組

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

產生的SQL

select * from user where name = 'test' or (votes > 100 and title <> 'Admin')

查詢user表中name欄位為test或者(votes欄位大於100和title欄位不等於Admin)

(13)：Where Exists 語句

此方法接受一個 閉包 引數，此閉包要接收一個查詢構造器例項

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

產生的SQL

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

(14)：JSON where 語句

本特性僅支援 MySQL 5.7+ 和 Postgres、SQL Server 2016 以及 SQLite 3.9.0資料庫。可以使用 -> 運算子來查詢 JSON 列資料

經過我的測試,如果json的鍵值為陣列,查詢無效

例：在user表中id為1的test欄位值為{"a": "abc", "b": "def"}

$users = DB::table('user')
            ->where('test->a', 'abc')
            ->get();

(15)：排序,分組, 分頁

  1. 排序

• orderBy 排序

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->get();

• latest / oldest排序

按日期對查詢結果排序,預設情況下是對 created_at 欄位進行排序。或者，你可以傳遞你想要排序的欄位名稱,latest倒序 oldest正序

$user = DB::table('user')
                ->latest()
                ->first();
$user = DB::table('user')
                -oldest('create_time')
                ->first();

• inRandomOrder排序

將查詢結果隨機排序,例如，你可以使用這個方法獲取一個隨機使用者

$randomUser = DB::table('users')
                ->inRandomOrder()
                ->first();
  1. 分組

groupBy / having 對查詢結果進行分組，groupBy 分組，可以將單個引數或多個引數傳遞給 groupBy 方法，按一個欄位或多個欄位進行分組，having是一個過濾宣告，是在查詢返回結果集以後對查詢結果進行的過濾操作，having 方法的用法和 where 方法類似

//查詢user表id大於100的資料並按order_id進行分組
$users = DB::table('user')
                ->groupBy('order_id')
                ->having('id', '>', 100)
                ->get();
//查詢user表id大於100的資料並按order_id和status進行分組
$users = DB::table('user')
                ->groupBy('order_id', 'status')
                ->having('id', '>', 100)
                ->get();
  1. 分頁查詢

• skip / take分頁查詢

$page = 10;//每頁顯示條數
$num = 1;//第幾頁
$user = DB::table('user')
    ->skip(($num - 1)*$page)->take($page)
    ->get();

• limit / offset分頁查詢

$page = 10;//每頁顯示條數
$num = 1;//第幾頁
$user = DB::table('user')
            ->offset(($num - 1) * $page)
            ->limit($page)
            ->get();

(16)：條件語句

只有當 when 方法的第一個引數為 true 時，閉包裡的 where 語句才會執行。如果第一個引數是 false，這個閉包將不會被執行

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

我們還可以將另一個閉包當作第三個引數傳遞給 when 方法。如果第一個引數的值為 false 時，這個閉包將執行，這就相當於預設值

$sortBy = null;
$users = DB::table('users')
                ->when($sortBy, function ($query) use ($sortBy) {
                    return $query->orderBy($sortBy);
                }, function ($query) {
                    return $query->orderBy('name');
                })
                ->get();

2:資料插入

(1)：單條資料插入

DB::table('user')->insert(
    [
        'user_no' => '003',
        'user_name' => 'abc'
    ]
);

(2)：多條資料插入

DB::table('user')->insert(
    [
        [
            'user_no' => '003',
            'user_name' => 'abc'
        ],
        [
            'user_no' => '004',
            'user_name' => 'def'
        ]
    ]
);

(3)：插入返回資料ID

若資料表存在自增的 ID，則可以使用 insertGetId 方法來插入記錄然後獲取其 ID

注意：使用insertGetId,一次只能插入一條資料
$id = DB::table('user')->insertGetId(
    [
        'user_no' => '003',
        'user_name' => 'abc'
    ]
);

3：資料更新

(1)：基礎資料更新

DB::table('user')
    ->where('id', 1)
    ->update(['user_name' => 'test']);

(2)：JSON資料更新

更新 JSON 欄位時，應該使用 -> 語法來訪問 JSON 物件中的相應鍵。此操作只能在支援 JSON 欄位的資料庫上操作

例：user表中一個欄位test,在id為1的test欄位值為 {"0": "abc", "1": "def"}

DB::table('user')
    ->where('id', 1)
    ->update(['test->0' => '這是我修改的']);

(3)：自增自減更新

//自增1
DB::table('users')->increment('votes');
//自增5
DB::table('users')->increment('votes', 5);
//自減1
DB::table('users')->decrement('votes');
//自減5
DB::table('users')->decrement('votes', 5);
//將name欄位為john的votes欄位自增1
DB::table('users')->increment('votes', 1, ['name' => 'John']);

4：資料刪除

(1)：刪除資料

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

(2)：清空表

truncate 方法，將刪除所有行，並重置自動遞增 ID 為零

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

相關文章