Laravel Framework Database Curd operation, consistent operation use method

Laravel is a concise, elegant PHP Web development Framework (PHP Web framework). It frees you from the same messy code as noodles, which can help you build a perfect web app, and each line of code can be concise and expressive.

First, selects

Retrieving all rows in a table

$users = db::table (' users ')->get ();
foreach ($users as $user)
{
Var_dump ($user->name);
}

Retrieving a single row from a table

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

Var_dump ($user->name);

Retrieving rows for a single column

$name = db::table (' users ')->where (' name ', ' John ')->pluck (' name ');
Retrieves a list of column values

$roles = db::table (' roles ')->lists (' title ');
This method returns the role of an array caption. You can also specify a custom key column to return an array of

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

Specify a 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 ', ' > ', M)->get ();

OR

$users = db::table (' users ')->where (' votes ', ' > ')->orwhere (' name ', ' John ')->get ();

Where Between

$users = db::table (' users ')->wherebetween (' votes ', array (1))->get ();

Where not Between

$users = db::table (' users ')->wherenotbetween (' votes ', array (1))->get ();

Where in with the An Array

$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

$users = db::table (' users ')->wherenull (' Updated_at ')->get ();

Order BY, Group by, and has

$users = db::table (' users ')->orderby (' name ', ' desc ')->groupby (' Count ')->having (' Count ', ' > ')-> Get ();
Offset & Limit

$users = db::table (' users ')->skip->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:

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 ();

Left JOIN-Connect statement

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, grouped

Sometimes, you might want to create a more advanced where clause, such as "exist" or a nested parameter grouping. Laravel Query Builder can handle these:

Db::table (' users ')
->where (' name ', ' = ', ' John ')
->orwhere (function ($query)
{
$query->where (' votes ', ' > ', 100)
->where (' title ', ' <> ', ' Admin ');
})
->get ();

The query above will produce the following SQL:

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 query above will produce the following SQL:

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 totals.

$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 might want to use a query of the original expression. These expressions will inject the query string, so be careful not to create any SQL injection points! Create an original expression, you can use 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 a 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)
);

Automatically increase the ID of the record inserted into the table

If the table has an automatically incremented ID field, use Insertgetid to insert a record and retrieve the ID:

1
2
3
$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 inserted 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),
));

Four, Updates

Update records in one table

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

Five, deletes

Delete a record from a table

The code is as follows:
Db::table (' users ')->where (' votes ', ' < ', m)->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 ();

VI. Unions

The Query Builder also provides a quick way to "federate" two 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 be signed with the same method.

Pessimistic locking

The query Builder includes some "pessimistic locking" features to help you make your SELECT statement. To run the SELECT statement shared lock, you can use the Sharedlock method to query:

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

The code is as follows:

Db::table (' users ')->where (' votes ', ' > ')->lockforupdate ()->get ();

VII. Caching queries

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

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

The code is as follows:

$users = db::table (' users ')->cachetags (Array (' People ', ' authors ')->remember ()->get ();