Laravel Framework Database curd operation and coherent operation summary

First, selects

Retrieving all rows in a table
The code is as follows:
$users = db::table (' users ')->get ();
foreach ($users as $user)
{
Var_dump ($user->name);
}

Retrieving a single row from a table
The code is as follows:
$user = db::table (' users ')->where (' name ', ' John ')->first ();
Var_dump ($user->name);

Retrieving rows for a single column
The code is as follows:
$name = db::table (' users ')->where (' name ', ' John ')->pluck (' name ');

Retrieving a list of column values
The code is as follows:
$roles = db::table (' roles ')->lists (' title ');

The method returns the function of an array header. You can also specify a custom key column to return an array of
The code is as follows:
$roles = db::table (' roles ')->lists (' title ', ' name ');

Specify a SELECT clause
The code is as follows:
$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 ');
The code is as follows:
$users = $query->addselect (' Age ')->get ();

where
The code is as follows:
$users = db::table (' users ')->where (' votes ', ' > ', +)->get ();

OR
The code is as follows:
$users = db::table (' users ')->where (' votes ', ' > ', ')->orwhere (' name ', ' John ')->get ();

The Where between code is as follows:
$users = db::table (' users ')->wherebetween (' votes ', array (1))->get ();

The Where not between code is as follows:
$users = db::table (' users ')->wherenotbetween (' votes ', array (1))->get ();

The Where in with an Array code is as follows:
$users = db::table (' users ')->wherein (' id ', array (1, 2, 3))->get ();
$users = db::table (' users ')->wherenotin (' id ', array (1, 2, 3))->get ();

Using Where Null to Find Records with Unset Values
The code is as follows:
$users = db::table (' users ')->wherenull (' Updated_at ')->get ();

Order by, Group by, and have
The code is as follows:
$users = db::table (' users ')->orderby (' name ', ' desc ')->groupby (' Count ')->having (' Count ', ' > ', Get ();

The Offset & Limit code is as follows:
$users = db::table (' users ')->skip (Ten)->take (5)->get ();

Second, the connection

Joins

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

The Basic Join Statement code is as follows:
Db::table (' users ')
->join (' Contacts ', ' users.id ', ' = ', ' contacts.user_id ')
->join (' orders ', ' users.id ', ' = ', ' orders.user_id ')
->select (' users.id ', ' contacts.phone ', ' Orders.price ')
->get ();

The left connection statement code is as follows:
Db::table (' users ')
->leftjoin (' posts ', ' users.id ', ' = ', ' posts.user_id ')
->get ();
Db::table (' users ')
->join (' Contacts ', function ($join)
{
$join->on (' users.id ', ' = ', ' contacts.user_id ')->oron (...);
})
->get ();
Db::table (' users ')
->join (' Contacts ', function ($join)
{
$join->on (' users.id ', ' = ', ' contacts.user_id ')
->where (' contacts.user_id ', ' > ', 5);
})
->get ();

Third, group

Sometimes, you may want to create more advanced where clauses, such as "presence" or nested parameter groupings. Laravel Query Builder can handle these: Copy code code as follows:
Db::table (' users ')
->where (' name ', ' = ', ' John ')
->orwhere (function ($query)
{
$query->where (' votes ', ' > ', 100)
->where (' title ', ' <> ', ' Admin ');
})
->get ();

The above query will produce the following SQL:
The code is as follows:
SELECT * from users where name = ' John ' or (Votes > and title
<> ' Admin ')
Exists statements
Db::table (' users ')
->whereexists (function ($query)
{
$query->select (Db::raw (1))
->from (' orders ')
->whereraw (' orders.user_id = Users.id ');
})
->get ();

The above query will produce the following SQL:
The code is as follows:
SELECT * FROM Userswhere exists (
Select 1 from orders where orders.user_id = Users.id
)

Iv. Aggregation

The Query Builder also provides a variety of aggregation methods, such as statistics, Max, Min,avg, and Sum.

Using Aggregate Methods
The code is as follows:
$users = db::table (' users ')->count ();
$price = db::table (' orders ')->max (' price ');
$price = db::table (' orders ')->min (' price ');
$price = db::table (' orders ')->avg (' price ');
$total = db::table (' users ')->sum (' votes ');

Raw Expressions

Sometimes you may want to use a query with a raw expression. These expressions will inject the query string, so be careful not to create any SQL injection points! To create a primitive expression, you can use Db:rawmethod:

The Using A Raw Expression code is as follows:
$users = db::table (' users ')
->select (Db::raw (' count (*) as User_count, status '))
->where (' status ', ' <> ', 1)
->groupby (' status ')
->get ();

Increment or decrement the value of a column
The code is as follows:
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:
The code is as follows:
Db::table (' users ')->increment (' votes ', 1, Array (' name ' = ' John ') ');

Inserts

insert the record into the table code as follows:
Db::table (' users ')->insert (
Array (' email ' = ' john@example.com ', ' votes ' = + 0)
);

automatically increment the ID of the record into the table

If the table has an auto-incrementing ID field, use Insertgetid to insert a record and retrieve the ID:
The code is as follows:
$id = db::table (' users ')->insertgetid (
Array (' email ' = ' john@example.com ', ' votes ' = + 0)
);

Note: When using the PostgreSQL Insertgetid method, it is expected that the self-increment column is named "id".

Inserting multiple records into a table
The code is as follows:
Db::table (' users ')->insert (Array (
Array (' email ' = ' taylor@example.com ', ' votes ' = + 0),
Array (' email ' = ' dayle@example.com ', ' votes ' = + 0),
));

Iv. Updates

To update a record in a table
The code is as follows:
Db::table (' users ')
->where (' id ', 1)
->update (Array (' votes ' = 1));

Wu, deletes

Delete a record in a table
The code is as follows:
Db::table (' users ')->where (' votes ', ' < ', +)->delete ();

Delete all records in a table
The code is as follows:
Db::table (' users ')->delete ();

Delete a table
The code is as follows:
Db::table (' users ')->truncate ();

Liu, unions

The Query Builder also provides a quick way to "federate" two of Queries: The code is as follows:
$first = db::table (' users ')->wherenull (' first_name ');
$users =
Db::table (' users ')->wherenull (' last_name ')->union ($first)->get ();

The UnionAll method can also have the same method signature.

Pessimistic Locking

The query Builder includes some "pessimistic locking" features to help you make your SELECT statement. Run the SELECT statement "Shared Lock", which you can query using the Sharedlock method:
The code is as follows:
Db::table (' users ')->where (' votes ', ' > ',
()->sharedlock ()->get ();

Update "Lock" in a SELECT statement, you can use the Lockforupdate method to query:
The code is as follows:
Db::table (' users ')->where (' votes ', ' > ', ')->lockforupdate ()->get ();

Seven, cache query

You can easily cache the results of a query using the mnemonic method:

The code is as follows:
$users = db::table (' users ')->remember (Ten)->get ();

In this case, the result of the query will be cached for 10 minutes.　　When you query the results cache, the database is not run, and the results are assigned to your application from the default cache load driver. If you are using a driver that supports caching, you can also add a tag to cache:
The code is as follows:
$users = db::table (' users ')->cachetags (Array (' People ', ' authors '))->remember ()->get ();