[Laravel] 5. Add, delete, change and check the constructor

Author: Shi Nianzhilu Public account: Zhixing Research Institute

First, the constructor queries with ->toSql() at the end of the concatenation to get the currently executed SQL statement.

Initial data:

5.1 Basic queries for the constructor

1. Table () method: introduce the corresponding table; The get() method queries all the data in the current table

// Get all results
$users = DB::table('users')->get();
Copy the code

To see the SQL statement, execute:

$users = DB::table('users')->toSql();
Copy the code

The SQL statement executed is

select * from `laravel_users`
Copy the code

2. First () method: the first data can be obtained

// Get the first data
$users = DB::table('users')->first();
Copy the code

3, value(field name) method: you can obtain the value of the specified field of the first data

// Get the email field value of the first data
$users = DB::table('users')->value('email');
Copy the code

4, find(id) method: can obtain a specified ID of the data

// Get a specified piece of data by id
$users = DB::table('users')->find(20);
Copy the code

5. Pluck (field name) method: Get a collection of single column values for all data

// Get a collection of single-column values
$users = DB::table('users')->pluck('username');
// The second argument is output as primary key
$users = DB::table('users')->pluck('username'.'id');
Copy the code

First execution result:

5.2 Partitioning and aggregation of constructors

1. If you process thousands of records at a time to prevent errors, use the chunk() method:

// Cut the block execution, id sort, default increment; Three entries are read each time
DB::table('users')->orderBy('id')->chunk(3.function ($users) {
    foreach ($users as $user) {
        echo $user->username.'　';
    }
    echo '<br>';
});
Copy the code

The execution result is as follows:

2. Constructor queries provide aggregate queries of count(), Max (), min(), AVg (), and sum()

// Query the total number of users tables
return DB::table('users')->count();
// Find the maximum value of user's price table
return DB::table('users')->max('price');
// Get the average price of the Users table
return DB::table('users')->avg('price');
Copy the code

The constructor looks for two ways to determine whether a record exists: the exists() and doesntExists() methods

// If there is a record in the Users table with id=18, return 1, otherwise no return. Put it in an array, return true if it exists, false if it does not.
return [DB::table('users')->where('id'.18)->exists()];
// The opposite of the previous one
return [DB::table('users')->where('id'.18)->doesntExist()];
Copy the code

Note: the first DB:: static, return the query object, then use ->where various query methods, these query methods return the query object, so you can continue the concatenation operation. Finally, stop the concatenation when you encounter methods such as get() that return results. Therefore, the return result must come last.

5.3 Constructor SELECT query

1, select() method: you can specify the columns you want, not all columns;

// Set the column to display. You can set the column alias
$users = DB::table('users')->select('username as name'.'email')->get();
// Get the SQL statement executed
$users = DB::table('users')->select('username as name'.'email')->toSql();
Copy the code

SQL is:

select `username` as `name`, `email` from `laravel_users`
Copy the code

2, addSelect() method: you can add the desired fields to your base query constructor;

Is to extend an existing constructor.

// Add more fields to the already built query
$base = DB::table('users')->select('username as name'.'email');
$users = $base->addSelect('gender')->get();
Copy the code

SQL is:

select `username` as `name`, `email`, `gender` from `laravel_users`
Copy the code

3, DB::raw() method: can implement native expression inside select(), otherwise parsing error;

// Implement complex queries with native SQL
$users = DB::table('users')->select(DB::raw('COUNT(*) AS count, gender'))
    ->groupBy('gender')
    ->get();
// ->toSql();
Copy the code

SQL is:

select COUNT(*) AS count, gender from `laravel_users` group by `gender`
Copy the code

Execution Result:

[{"count": 10."gender": "Male"
    },
    {
        "count": 2."gender": "Female"}]Copy the code

4. You can also use the selectRaw() method directly to implement internal primitives;

// Or implement native using the selectRaw() method directly
$users = DB::table('users')->selectRaw('COUNT(*) AS count, gender')
    ->groupBy('gender')
    ->get();
// ->toSql();
Copy the code

SQL is:

select COUNT(*) AS count, gender from `laravel_users` group by `gender`
Copy the code

The execution result is the same as the preceding.

5. HavingRaw () method can also be used to achieve more accurate group screening;

// Use the havingRaw method to implement group filtering
$users = DB::table('users')->selectRaw('COUNT(*) AS count, gender')
    ->groupBy('gender')
    ->havingRaw('count>5') // Display only groups whose count is greater than 5
// ->toSql();
    ->get();
Copy the code

SQL is:

select COUNT(*) AS count, gender from `laravel_users` group by `gender` having count>5
Copy the code

Execution Result:

[{"count": 10."gender": "Male"}]Copy the code

5.4 Constructor WHERE query

1, where() query: that is, conditional query, complete form requires field expression and value three;

//where query in full form
// Query a record whose ID is 19
$users = DB::table('users')->where('id'.'='.19)->get();
//$users = DB::table('users')->where('id', '=', 19)->toSql();
Copy the code

SQL is:

select * from `laravel_users` where `id` = ?
Copy the code

2. In most cases, the = parameter can be omitted if the = parameter is used more frequently.

//where query ellipsis form
// Query a record whose ID is 19
$users = DB::table('users')->where('id'.19)->get();
//$users = DB::table('users')->where('id', 19)->toSql();
Copy the code

The above SQL.

3, of course, there are >, <, >=, <=, <>, like operators:

// Query all records whose price is greater than or equal to 95
$users = DB::table('users')->where('price'.'> ='.95)->get();
// query all records with small characters in name
$users = DB::table('users')->where('username'.'like'.'small % %')->get();
// Query all records where status does not equal 2
$users = DB::table('users')->where('status'.'< >'.2)->get();
Copy the code

4. There are many conditions in the figure, so you can use arrays to add conditions as follows:

->toSql() ->get()
$users = DB::table('users')->where([
    'price'= >90.'gender'= >'male'
])->get();

// If the condition contains non-equal, then:
$users = DB::table('users')->where([
     ['price'.'> ='.90],
     ['gender'.'='.'male']
])->get();
Copy the code

SQL is:

select * from `laravel_users` where (`price` = ? and `gender` =?).select * from `laravel_users` where (`price` > = ? and `gender` =?).Copy the code

5.5 Constructor WHERE derived query

1. OrWhere () method: two or more or condition queries can be realized through concatenation;

Example 1:

//where() + orWhere (
$users = DB::table('users')
   ->where('price'.'>'.95)
   ->orWhere('gender'.'woman')
// ->toSql();
   ->get();
Copy the code

SQL is:

select * from `laravel_users` where `price` > ? or `gender` = ?
Copy the code

Example 2:

$users = DB::table('users')
    ->where([
         ['price'.'> ='.90],
         ['gender'.'='.'woman']
    ])
    ->orWhere([
         ['price'.'> ='.85],
         ['gender'.'='.'male']
     ])
     ->toSql();
// ->get();
Copy the code

SQL is:

select * from `laravel_users` where (`price` > = ? and `gender` =?).or (`price` > = ? and `gender` =?).Copy the code

Closures also allow us to build more complex orWhere queries.

//orWhere() combines closure queries
$users = DB::table('users')
    ->where('price'.'>'.'95')
    ->orWhere(function ($query) {
        $query->where('gender'.'woman')
            ->where('username'.'like'.'small % %');
    })->toSql();
Copy the code

SQL is:

select * from `laravel_users` where `price` > ? or (`gender` = ? and `username` like?).Copy the code

3. The whereBetween() method implements interval queries. Such as:

//whereBetween queries a range of prices between 60 and 90
$users = DB::table('users')->whereBetween('price'[60.90])->toSql();
/ / here also supports three kinds: whereNotBetween/orWhereBetween/orWhereNotBetween;
echo DB::table('users')->where('status'.1)->whereNotBetween('price'[60.90])->toSql();
echo DB::table('users')->where('status'.1)->orWhereBetween('price'[60.90])->toSql();
echo DB::table('users')->where('status'.1)->orWhereNotBetween('price'[60.90])->toSql();
Copy the code

SQL is as follows:

select * from `laravel_users` where `price` between ? and ?

select * from `laravel_users` where `status` = ? and `price` not between ? and ?
select * from `laravel_users` where `status` = ? or `price` between ? and ?
select * from `laravel_users` where `status` = ? or `price` not between ? and ?
Copy the code

Groupby () method: Can implement array matching query. For example, match the specified data in the array:

// groupby queries the matching value in the array
echo DB::table('users')->whereIn('id'[20.30.50])->toSql();
/ / here also supports three kinds: whereNotIn/orWhereIn/orWhereNotIn;
echo DB::table('users')->where('status'.1)->whereNotIn('id'[20.30.50])->toSql();
echo DB::table('users')->where('status'.1)->orWhereIn('id'[20.30.50])->toSql();
echo DB::table('users')->where('status'.1)->orWhereNotIn('id'[20.30.50])->toSql();
Copy the code

SQL is as follows:

select * from `laravel_users` where `id` in (?, ?, ?)

select * from `laravel_users` where `status` = ? and `id` not in (?, ?, ?)
select * from `laravel_users` where `status` = ? or `id` in (?, ?, ?)
select * from `laravel_users` where `status` = ? or `id` not in (?, ?, ?)
Copy the code

SQL > select * from whereNull();

//whereNull is used to query records
echo DB::table('users')->whereNull('uid')->toSql();
/ / here also supports three kinds: whereNotNull/orWhereNull/orWhereNotNull;
echo DB::table('users')->where('status'.1)->whereNotNull('uid')->toSql();
echo DB::table('users')->where('status'.1)->orWhereNull('uid')->toSql();
echo DB::table('users')->where('status'.1)->orWhereNotNull('uid')->toSql();
Copy the code

SQL is as follows:

select * from `laravel_users` where `uid` is null

select * from `laravel_users` where `status` = ? and `uid` is not null
select * from `laravel_users` where `status` = ? or `uid` is null
select * from `laravel_users` where `status` = ? or `uid` is not null
Copy the code

6, whereDate() series of methods: can query a specified date of the record;

//whereYear queries the record of a specified date. The default value is equal to
echo DB::table('users')->whereDate('created_at'.'2018-12-11')->toSql();
/ / here also supports the four: whereYear/whereMonth/whereDay/whereTime
echo DB::table('users')->whereYear('created_at'.'>'.'2018')->toSql();
// Support or prefix:
echo DB::table('users')->where('status'.1)->orwhereMonth('created_at'.'<'.'5')->toSql();
Copy the code

SQL is as follows:

select * from `laravel_users` where date(`created_at`) = ?
select * from `laravel_users` where year(`created_at`) > ?
select * from `laravel_users` where `status` = ? or month(`created_at`) < ?
Copy the code

5.6 Sorting grouping of constructors

1, whereColumn() method: to achieve two fields equal or meet other conditions of the query results.

OrWhereColumn ()
echo DB::table('users')
    ->whereColumn('creates_at'.'updated_at')
    ->toSql();

// Support symbols 'create_time','>', 'update_time'
echo DB::table('users')
    ->whereColumn('creates_at'.'>'.'updated_at')
    ->toSql();

/ / support symbols support multiple fields array format [' create_time ', '>', 'update_time']
echo DB::table('users')
    ->whereColumn([
        ['creates_at'.'>'.'updated_at'],
        ['id'.'< >'.'uid']
    ])
    ->toSql();
Copy the code

SQL is as follows:

select * from `laravel_users` where `created_at` = `updated_at`
select * from `laravel_users` where `created_at` > `updated_at`
select * from `laravel_users` where (`created_at` > `updated_at` and `id` <> `uid`)
Copy the code

OrderBy () : implements desc or ASC sorting.

echo DB::table('users')
    ->orderBy('id'.'desc')
    ->toSql();
Copy the code

SQL is:

select * from `laravel_users` order by `id` desc
Copy the code

Created_at = created_at = created_at = created_at = created_at

// In reverse order of creation time, created_at by default
echo DB::table('users')
    ->latest()
    ->toSql();
// You can customize the time field
echo DB::table('users')
    ->latest('create_time')
    ->toSql();
Copy the code

SQL is as follows:

select * from `laravel_users` order by `created_at` desc
select * from `laravel_users` order by `create_time` desc
Copy the code

4. Use inRandomOrder() to sort randomly and get a random list.

// sort randomly
echo DB::table('users')->inRandomOrder()->toSql();
Copy the code

SQL is:

select * from `laravel_users` order by RAND()
Copy the code

5. Use skip() and take() to limit the result set, or use offset() and limit();

// Start with item 3
echo DB::table('users')->skip(2)->take(3)->toSql();
echo DB::table('users')->offset(2)->limit(3)->toSql();
Copy the code

SQL is as follows:

select * from `laravel_users` limit 3 offset 2
select * from `laravel_users` limit 3 offset 2
Copy the code

6. Use when() to set the conditional selection and execute the corresponding SQL statement;

//when implements the condition select, true, execute the former
echo DB::table('users')->when(true.function ($query) {
    $query->where('id'.19);
}, function ($query) {
    $query->where('username'.'fai night');
})->toSql();

//when implements the condition selection, false, the latter
echo DB::table('users')->when(false.function ($query) {
    $query->where('id'.19);
}, function ($query) {
    $query->where('username'.'fai night');
})->toSql();
Copy the code

SQL is as follows:

select * from `laravel_users` where `id` = ?
select * from `laravel_users` where `username` = ?
Copy the code

7. If MySQL is in 5.7+, there are new features that support JSON data;

echo DB::table('users')->where('list->id'.19)->toSql();
///return [DB::table('users')->where('list->id', 19)->first()];
Copy the code

SQL to execute:

select * from `laravel_users` where json_unquote(json_extract(`list`, '$."id"')) = ?
Copy the code

Here, the data processed is:

5.7 the subquery

1. Implement a subquery result using the whereExists() method to return the corresponding main query;

Select * from books; select * from users
echo DB::table('users')->whereExists(function ($query) {
    $query->selectRaw(1)
        ->from('books')
        ->whereRaw('laravel_books.user_id = laravel_users.id');
})->toSql();

//whereRaw can also be used as: whereColumn
echo DB::table('users')->whereExists(function ($query) {
    $query->selectRaw(1)
        ->from('books')
        ->whereColumn('books.user_id'.'users.id')
        ->whereColumn('books.user_name'.'users.name');
})->toSql();
Copy the code

SQL is executed as follows:

select * from `laravel_users` where exists (
    select 1 from `laravel_books` 
    where laravel_books.user_id = laravel_users.id
)

select * from `laravel_users` where exists (
    select 1 from `laravel_books` 
    where `laravel_books`.`user_id` = `laravel_users`.`id` and `laravel_books`.`user_name` = `laravel_users`.`name`
)
Copy the code

PS: select 1 from, generally used as a means of sub-query, the purpose is to reduce costs, improve efficiency,

You can use the where(field,function()) closure to implement a subquery;

//id= user_id returned by subquery
echo DB::table('users')
    ->where('id'.function ($query) {
        $query->select('user_id')
            ->from('books')
            ->whereColumn('books.user_id'.'users.id');
    })
    ->toSql();
//id= user_id or name= user_name returned by the subquery
echo DB::table('users')
    ->where('id'.function ($query) {
        $query->select('user_id')
            ->from('books')
            ->whereColumn('books.user_id'.'users.id');
    })
    ->orWhere('name'.function ($query) {
        $query->select('user_name')
            ->from('books')
            ->whereColumn('books.user_name'.'users.name');
    })
    ->toSql();
Copy the code

SQL is executed as follows:

select * from `laravel_users` where `id` = (
    select `user_id` from `laravel_books` where `laravel_books`.`user_id` = `laravel_users`.`id`
)

select * from `laravel_users` 
where `id` = (
    select `user_id` from `laravel_books` where `laravel_books`.`user_id` = `laravel_users`.`id`
) 
or `name` = (
    select `user_name` from `laravel_books` where `laravel_books`.`user_name` = `laravel_users`.`name`
)
Copy the code

5.8 Constructor join query (multi-table query)

1. Use JOIN to realize multi-table query of inner join, such as inner join query of three tables;

echo DB::table('users')
    ->join('books'.'users.id'.'='.'books.user_id')
    ->join('profiles'.'users.id'.'='.'profiles.user_id')
    ->select('users.id'.'users.username'.'users.email'.'books.title'.'profiles.hobby')
    ->toSql();
Copy the code

SQL is:

select `laravel_users`.`id`, `laravel_users`.`username`, `laravel_users`.`email`, `laravel_books`.`title`, `laravel_profiles`.`hobby` 
from `laravel_users` 
inner join `laravel_books` on `laravel_users`.`id` = `laravel_books`.`user_id` 
inner join `laravel_profiles` on `laravel_users`.`id` = `laravel_profiles`.`user_id`
Copy the code

2. You can use leftjoin leftjoin or rightjoin rightjoin to implement multi-table queries

echo DB::table('users')
    ->leftJoin('books'.'users.id'.'='.'books.user_id')
    ->rightjoin('profiles'.'users.id'.'='.'profiles.user_id')
    ->toSql();
Copy the code

SQL is:

select * from `laravel_users` 
left join `laravel_books` on `laravel_users`.`id` = `laravel_books`.`user_id` 
right join `laravel_profiles` on `laravel_users`.`id` = `laravel_profiles`.`user_id`
Copy the code

Select distinct() from crossjoin to create a cartesian product;

echo DB::table('users')
    ->crossJoin('books')
    ->select('username'.'email')
    ->distinct()
    ->toSql();
Copy the code

SQL:

select distinct `username`, `email` from `laravel_users` cross join `laravel_books`
Copy the code

4, If you want to implement closure query, like where, but use on and orOn methods;

echo DB::table('users')
    ->join('books'.function ($join) {
        $join->on('users.id'.'='.'books.user_id');
    })->toSql();

echo DB::table('users')
    ->join('books'.function ($join) {
        // If you want to add more filters, you can append where(), etc
        $join->on('users.id'.'='.'books.user_id')
            ->orWhere('users.name'.'='.'Joe');
    })->toSql();

echo DB::table('users')
    ->join('books'.function ($join) {
        // Support orOn concatenation
        $join->on('users.id'.'='.'books.user_id')
            ->orOn('users.name'.'='.'books.user_name');
    })->toSql();

Copy the code

SQL is as follows:

select * from `laravel_users` inner join `laravel_books` on `laravel_users`.`id` = `laravel_books`.`user_id`

select * from `laravel_users` 
inner join `laravel_books` 
on `laravel_users`.`id` = `laravel_books`.`user_id` or `laravel_users`.`name` = ?

select * from `laravel_users` 
inner join `laravel_books` 
on `laravel_users`.`id` = `laravel_books`.`user_id` or `laravel_users`.`name` = `laravel_books`.`user_name`
Copy the code

5. Use joinSub to implement sub-connection query and combine the corresponding contents together to output;

// subjoin query
$query = DB::table('books')->selectRaw('user_id,title');
echo DB::table('users')->joinSub($query.'books'.function ($join) {
    $join->on('users.id'.'='.'books.user_id');
})->toSql();
Copy the code

SQL:

select * from `laravel_users`
inner join (
    select user_id,title from `laravel_books`
) as `laravel_books` 
on `laravel_users`.`id` = `laravel_books`.`user_id`
Copy the code

6. Use union() or unionAll() method to merge two queries;

//union cancels the duplication, unionAll does not cancel the duplication
$query = DB::table('users')->where('price'.'>'.'50');
echo DB::table('users')
    ->where('status'.'< >'.'0')
    ->union($query)
    ->toSql();
Copy the code

SQL :

(select * from `laravel_users` where `status` <>?).union
(select * from `laravel_users` where `price` >?).Copy the code

5.9 Constructor additions

1. Use the INSERT () method to add one or more records;

// Add a new record
DB::table('users')->insert([
    'username'= >'李白'.'password'= >'123456'.'email'= >'[email protected]'.'details'= >'123'
]);

// Add multiple records
DB::table('users')->insert([
    [
        'username'= >'李白'.'password'= >'123456'
    ],
    [
        'username'= >'black li'.'password'= >'654321']]);Copy the code

2. Use the insertOrIgnore() method to ignore the error of repeatedly inserting data;

// Failed to add data repeatedly
// There is already data 304 in the original data
DB::table('users')->insertOrIgnore([
    'id'= >304.'username'= >'李白'.'password'= >'123456'.'email'= >'[email protected]'.'details'= >'123'
]);
Copy the code

3. Use insertGetId() to obtain the newly added ID;

// Get the ID returned after the addition
$id = DB::table('users')->insertGetId([
    'username'= >'李白'.'password'= >'123456'.'email'= >'[email protected]'.'details'= >'123'
]);
return $id;
Copy the code

4. When adding JSON data, convert the array to JSON

DB::table('users')->insert([
    'username'= >'Li Bai Bai'.'password'= >'123456789'.'email'= >'[email protected]'.'details'= >'123'.// Convert to JSON data when added
    'list' => json_encode(['code'= >1900]]);Copy the code

5.10 Constructor update

The update() method allows you to update a data item with a condition.

// Update modifies a data item
DB::table('users')->where('id'.100)
    ->update([
        'username'= >'Li Hong Hei'.'email'= >'[email protected]'
    ]);
Copy the code

2, use updateOrInsert() method, you can first search for changes, if there is no, add new;

// Parameter 1: modified condition
// Parameter 2: modified content (new content)
DB::table('users')->updateOrInsert(
    ['id'= >307],
    ['username'= >'black li'.'password'= >'654321'.'details'= >'123']);Copy the code

3. For JSON data, the modification method is similar to normal data;

// To modify, use list->id
DB::table('users')->where('id'.101)
    ->update([
        'list->id'= >20.'price'= >Awesome!
    ]);
Copy the code

4. When updating data, you can use increment() and decrement() methods;

// The default value is 1, which can be set
DB::table('users')->where('id'.306)->increment('price');
// Increment by 2 each time
DB::table('users')->where('id'.306)->increment('price'.2);
Copy the code

5.11 Constructor deletion

Delete (); delete();

// Delete a piece of data
DB::table('users')->delete(307);
DB::table('users')->where('id'.307)->delete();
/ / to empty
DB::table('users')->delete();
DB::table('users')->truncate();
Copy the code

The above.