Laravel Framework Database curd operation, coherent operation Summary, Laravelcurd
First, selects
Retrieving all rows in a table
Copy the Code code as follows:
$users = db::table (' users ')->get ();
foreach ($users as $user)
{
Var_dump ($user->name);
}
retrieving a single row from a table
Copy Code code is as follows:
$user = db::table (' users ')->where (' name ', ' John ')->first ();
Var_dump ($user->name);
Retrieve rows for a single column
Copy code code as follows:
$name = db::table (' users ')->where (' name ') , ' John ')->pluck (' name ');
Retrieve a list of column values
Copy code code 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
Copy code code as follows:
$roles = db::table (' roles ')->lists (' title ', ' Name ');
Specify a SELECT clause
Copy code code 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 ');
Copy the Code code as follows:
$users = $query->addselect (' Age ')->get ();
where
Copy the Code code as follows:
$users = db::table (' users ')->where (' votes ', ' > ', +)->get ();
OR
Copy the Code code as follows:
$users = db::table (' users ')->where (' votes ', ' > ', ')->orwhere (' name ', ' John ')->get ();
Where between
Copy the Code code as follows:
$users = db::table (' users ')->wherebetween (' votes ', array (1))->get ();
Where not between
Copy the Code code as follows:
$users = db::table (' users ')->wherenotbetween (' votes ', array (1))->get ();
Where in with an Array
Copy the Code code 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
Copy the Code code as follows:
$users = db::table (' users ')->wherenull (' Updated_at ')->get ();
Order by, Group by, and have
Copy the Code code as follows:
$users = db::table (' users ')->orderby (' name ', ' desc ')->groupby (' Count ')->having (' Count ', ' > ', Get ();
Offset & Limit
Copy the Code code 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:
Basic Join Statement
Copy the Code code 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 ();
Left JOIN-up statements
Copy the Code code 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 the 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:
Copy the Code code 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:
Copy the Code code 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
Copy the Code code 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:
Using A Raw Expression
Copy the Code code 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
Copy the Code code 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:
Copy the Code code as follows:
Db::table (' users ')->increment (' votes ', 1, Array (' name ' = ' John ') ');
Inserts
Inserting records into a table
Copy the Code 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:
Copy the Code code 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
Copy the Code code 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
Copy the Code code as follows:
Db::table (' users ')
->where (' id ', 1)
->update (Array (' votes ' = 1));
Wu, deletes
Delete a record in a table
Copy the Code code as follows:
Db::table (' users ')->where (' votes ', ' < ', +)->delete ();
Delete all records in a table
Copy the Code code as follows:
Db::table (' users ')->delete ();
Delete a table
Copy the Code code as follows:
Db::table (' users ')->truncate ();
Liu, unions
The Query Builder also provides a quick way to "federate" two of Queries:
Copy the Code code 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:
Copy the Code code as follows:
Db::table (' users ')->where (' votes ', ' > ',
()->sharedlock ()->get ();
Update "Lock" in a SELECT statement, you can use the Lockforupdate method to query:
Copy the Code code 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:
Copy the Code code 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:
Copy the Code code as follows:
$users = db::table (' users ')->cachetags (Array (' People ', ' authors '))->remember ()->get ();
Php_laravel Frame
280907494 Development Group, many of the group engaged in this.
PHP Laravel Framework, the first access to the portal normal, and then refresh the error
Open the Debug and see the detailed error.
http://www.bkjia.com/PHPjc/874112.html www.bkjia.com true http://www.bkjia.com/PHPjc/874112.html techarticle laravel Framework Database curd operation, coherent operation Summary, Laravelcurd One, selects all lines in the Retrieval table copy code code as follows: $users = db::table (' users ')-get (); foreach ($u ...