Laravel Database - fantasy0107/notes GitHub Wiki

Migrations

Laravel 5.5 migration

migration 檔案命名

  1. 建立表格用create - php artisan make:migration create_users_table --create=users
  2. 新增欄位用add - add_votes_to_users_table --table=users

建立migration檔案

php artisan make:migration create_users_table 

create table(已經幫你用好schema) 
php artisan make:migration create_users_table --create=users

change table(已經幫你用好schema)
php artisan make:migration add_votes_to_users_table --table=users

migration 結構

class CreateFlightsTable extends Migration
{
	public function up()
    {
		......
    }
    
    public function down()
    {
    	......
    }
}

up為migrate的動作
down為migrate:rollback的動作 -

建立column

$table->資料型態('欄位名稱');

$table->bigIncrements('id');

欄位資料型態

執行migrate

php artisan migrate
php artisan migrate:rollback // 回到上一步

Seeding

在資料庫中產生假資料

產生檔案

命名 : TableName + Table + Seeder

php artisan make:seeder UserTableSeeder

邏輯

產生一筆資料

<?php

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

class DatabaseSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        DB::table('users')->insert([
            'name' => str_random(10),
            'email' => str_random(10).'@gmail.com',
            'password' => bcrypt('secret'),
        ]);
    }
}

產生多筆資料

需要用到factory

Factory檔案命名

TableName + Factory

php artisna make:factory UserFactory

Factory 檔案邏輯

use Faker\Generator as Faker;

$factory->define(App\User::class, function (Faker $faker) {
    return [
        'name' => $faker->name,
        'email' => $faker->unique()->safeEmail,
        'password' => '123', // secret
        'remember_token' => str_random(10),
    ];
});

這樣每執行一次就會在DB中的User Table產生一筆資料

修改 factory 產生的 record 值

下面的方式就會修正 不然就是用預設的(寫在 factory 檔案裡面的)

factory(App\Models\User::class, 1)->create([
    'key1' => value1,
    'key2' => value2,
    'key3' => value13
]);

Seeder 檔案邏輯

public function run()
{
    factory(App\User::class, 50)->create()->each(function ($u) {
        $u->posts()->save(factory(App\Post::class)->make());
    });
}

執行

php artisan db:seed //全部

php artisan db:seed --class=UsersTableSeeder //特定seeder

Query Builder

1.提供方便的介面進行資料庫的Query 2.使用PDO所以可以避免sql injection

join

Inner Join

結合兩張表變成一張

eloquent

A::join("B", 'A.id', '=', 'B.itemid')
->select('A.*', 'B.time as C')

pluck

只取得某欄位的collection

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

first

只取一個

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

chunk

每次只取一小筆

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

get

取得collection

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

value

只取的某些欄位值

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

Select

擷取欄位

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

distinct

只回傳不同的

count/max/min/avg


//數量
$users = DB::table('users')->count();

//最大
$price = DB::table('orders')->max('price');

//平均
$price = DB::table('orders')->where('finalized', 1)->avg('price');

Where

條件判斷

相等

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

大於

$users = DB::table('users')
           ->where('votes', '>', 100)
           ->get();
條件判斷可以是
>, >= , <, <=, <>, like

多重where

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

orderBy

排序

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

skip/take

跳過幾個或者拿取幾個

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

inserts

DB::table('users')->insert(
    ['email' => '[email protected]', 'votes' => 0]
);

insertGetId

新增資料並且取的id

update

更新資料

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

// 根據欄位值更新

Model::where('id', $id)->update([ 'nums' => DB::raw('nums+1')]);

Delete


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

//有條件刪除
DB::table('users')->where('votes', '>', 100)->delete();
//刪除所有資料並且重新設定自動新增的id
DB::table('users')->truncate();

底端

⚠️ **GitHub.com Fallback** ⚠️