Laravel Framework Database curd operation, coherent operation Summary _php example

Source: Internet
Author: User
Tags one table sql injection

First, selects

Retrieving all rows in a table

Copy 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 as follows:

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

retrieving rows for a single column
Copy Code code as follows:

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

retrieves a list of column values
Copy Code code as follows:

$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
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 Code code as follows:

$users = $query->addselect (' Age ')->get ();

where

Copy Code code as follows:

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

OR

Copy Code code as follows:

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

Where Between

Copy Code code as follows:

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

Where not Between

Copy Code code as follows:

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

Where in with the An Array

Copy 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 Code code as follows:

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

Order BY, Group by, and has

Copy Code code as follows:

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

Offset & Limit

Copy Code code as follows:

$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:

Basic Join Statement

Copy 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-Connect statement

Copy 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, 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:

Copy Code code as follows:

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

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

Using Aggregate Methods

Copy 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 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

Copy Code code as follows:

$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

Copy 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 Code code as follows:

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

Inserts

Insert a record into a table

Copy Code code as follows:

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:

Copy Code code as follows:

$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

Copy Code code 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

Copy Code code as follows:

Db::table (' users ')
->where (' id ', 1)
->update (Array (' votes ' => 1));

Five, deletes

Delete a record from a table

Copy Code code as follows:

Db::table (' users ')->where (' votes ', ' < ', m)->delete ();

Delete all records in a table

Copy Code code as follows:

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

Delete a table
Copy Code code as follows:

Db::table (' users ')->truncate ();

VI. Unions

The Query Builder also provides a quick way to "federate" two queries:

Copy 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 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:

Copy Code code as follows:

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

Update "Lock" in a SELECT statement, you can query using the Lockforupdate method:
Copy Code code 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:

Copy Code code 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:
Copy Code code as follows:

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.