Laravel framework database CURD operation coherent operation summary

1. Selects

Retrieve all rows in a table

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

foreach ($users as $user)

{

var_dump($user->name);

}

Retrieve a single row from a table

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

var_dump($user->name);

Retrieve rows for a single column

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

Retrieve a list of 1 column values

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

This method will return the action of 1 array title. You can also specify an array returned by a custom key column

$roles = DB::table('roles')->lists('title', 'name');

Specify 1 Select clause

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

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

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

The Select clause is added to an existing query $query = DB:: table ('users')- > select('name');

$users = $query->addSelect('age')->get();

where

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

OR

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

Where Between

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

Where Not Between

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

var_dump($user->name);

0

Where In With An Array

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

var_dump($user->name);

1

Using Where Null To Find Records With Unset Values

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

Order By, Group By, And Having

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

Offset & Limit

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

var_dump($user->name);

4

Step 2 Connect

Joins

The query builder can also be used to write join statements. Take a look at the following example:

Basic Join Statement

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

var_dump($user->name);

5

Left join statement

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

var_dump($user->name);

6

3. Grouping

Sometimes, you may need to create more advanced where clauses, such as "exist" or nested parameter groups. Laravel query builder can handle these:

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

var_dump($user->name);

7
The above query will produce the following SQL:

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

var_dump($user->name);

8

The above query will produce the following SQL:

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

var_dump($user->name);

9

Step 4 Aggregate

The query builder also provides various aggregation methods, such as statistics, Max, min, avg, and summation.

Using Aggregate Methods

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

0

Raw Expressions

Sometimes you may need to use a query with an original expression. These expressions will inject the query string, so be careful not to create any SQL injection points! Create a primitive expression using DB: rawmethod:

Using A Raw Expression

$users = DB::table('users')

->select(DB::raw('count(*) as user_count, status'))

->where('status', '<>', 1)

->groupBy('status')

->get();

Increments or decrements the value of 1 column

DB::table('users')->increment('votes');

DB::table('users')->increment('votes', 5);

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes', 5);

You can also specify additional column updates:

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

Inserts

Insert a record into a table

DB::table('users')->insert(

array('email' => 'john@example.com', 'votes' => 0)

);

Insert records into ID automatically added to the table

If the table has 1 automatically incremented id field, insert 1 record using insertGetId and retrieve id:

$id = DB::table('users')->insertGetId(

array('email' => 'john@example.com', 'votes' => 0)

);

Note: When using the PostgreSQL insertGetId method, the self-added column is named "id".

Multiple records are inserted into a table

DB::table('users')->insert(array(

array('email' => 'taylor@example.com', 'votes' => 0),

array('email' => 'dayle@example.com', 'votes' => 0),

));

4. Updates

Update records in 1 table

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

7

5. Deletes

Delete a record in a table

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

8

Delete all records in the table

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

Delete 1 table

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

0

6. Unions

The query builder also provides a quick way to "federate" two queries:

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

1

The unionAll method can also have the same method signature.

Pessimistic Locking

The query builder includes a few "pessimistic locking" features to help you make your SELECT statements. Running the SELECT statement "Shared Lock", you can use the sharedLock method to query:

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

2
Update "lock" in 1

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

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

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

statement, you can use lockForUpdate method query:

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

3

7. Cache queries

You can easily cache the results of queries using mnemonics:

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

4
In this example, the results of the query will be cached for 10 minutes. When querying the results cache, it does not run against the database, and the results will load your application from the default cache to the driver specified. If you are using a driver who supports caching, you can also add tags to cache:

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

5