Laravel Learning Notes (v)---Operational database-Query Builder (queries Builder)

Queries Builder (query Builder)
1. New data

You can insert one/more data by using the Insert method of the Query Builder:

Db::table (' users ')->insert ([
[' ID ' =>1, ' name ' => ' laravel ', ' email ' => ' laravel@test.com ', ' password ' => ' 123 '],
[' ID ' =>2, ' name ' => ' Academy ', ' email ' => ' academy@test.com ', ' password ' => ' 123 '],
[' ID ' =>3, ' name ' => ' laravelacademy ', ' email ' => ' laravel-academy@test.com ', ' password ' => ' 123 ']
]);

After successful execution, you can insert three records in the datasheet $users. Sometimes we need to insert a record and get our own ID, and we can use the Insertgetid method:

$insertId = db::table (' users ')->insertgetid (
[' Name ' => ' laravel-academy ', ' email ' => ' laravelacademy@test.com ', ' Password ' => ' 456 ']
);

2, update the data

Updating a table record is simple, using the Query Builder's Update method:

$affected = db::table (' users ')->where (' name ', ' Laravel-academy ')->update ([' Password ' => ' 123]);

The method returns the affected function.
3. Delete data

Use the Delete method to delete a table record, similar to the deletion method and update method, to return the number of rows that were deleted:

$deleted = db::table (' users ')->where (' id ', ' > ', 3)->delete ();

If we want to delete the entire datasheet data, omit the where condition, and if you want to empty the datasheet and set the self ID to 0, you can use the Truncate method:

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

Basic Query
4.1 Get all table records

Use the Get method to obtain all the records for a table:

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

If you are getting the data for the specified column, you need to add a select condition:

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

4.2 Get a single record

Getting a single record requires adding the primary method to the query:

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

4.3 block blocks to get data

If the database contains more than one piece of data, a one-time fetch can greatly affect performance, and correspondingly, we can call the chunk method block to get the data:

Db::table (' users ')->chunk (2,function ($users) {
foreach ($users as $user) {
if ($user->name== ' Laravelacademy ')
return false;
echo $user->name. ' <br> ';
}
});

Here we specify to take two records at a time. The comment section means that we can set the query exit condition, and when that condition is reached, the query exits and no longer executes.
4.4 Getting a Single-column value

The query result obtained by the above method is an object instance/object instance array, sometimes we just want to get a single column of values, traversing the array to get the value of the specified column too troublesome, you can use the lists method to get a column value array:

$users = db::table (' users ')->lists (' name ');
DD ($users);

4.5 Native expressions

In addition, the Query Builder also supports native expressions, and we need to call the db façade's raw method to implement:

$users = db::table (' users ')->select (Db::raw (' Name,email '))->where (' id ', ' < ', 3)->get ();
DD ($users);

1. Connection query (join)

A connection query is a query that associates two or more tables together to get data that matches the rows of one table with the rows of another. Common connection queries include inner joins (equivalent connections), left (outside) connections, right (outside) connections, and cross joins (full connections).

1.1 Internal connections

The inner join is used to get the intersection of two table result sets, and we can use the Query Builder join method for the INNER join query:

$users = db::table (' users ')->join (' posts ', ' users.id ', ' = ', ' posts.user_id ')->get ();
DD ($users);

1.2 Left Connection

The left-connected result set specifies all rows of the left table, and if a row in the left table does not have a matching row in the right table, all picklist columns in the right table in the associated result set row are null (NULL). We use the Query Builder's Leftjoin method for the left-join query:

$users = db::table (' users ')->leftjoin (' posts ', ' users.id ', ' = ', ' posts.user_id ')->get ();
DD ($users);

1.3 More complex connection queries

Many times, the query conditions of the connection query are often more complex, not a simple Join/leftjoin method can be done, then how do we add more complex query conditions. Use JoinClause instead of condition parameters:

$users = db::table (' users ')->join (' posts ', function ($join) {
$join->on (' users.id ', ' = ', ' posts.user_id ')
->where (' posts.id ', ' > ', 1);
})->get ();
DD ($users);

2. Union Query (Union)

A union query is used to combine the result sets of two or more queries into a single result set that contains all the rows of all queries in a federated query. The result set column name of the union is the same as the column name of the result set of the first SELECT statement in the Union operator, and the result set column names of the other SELECT statement are ignored, and the number of other query fields must be the same as the first. In the Laravel Query Builder we use the Union method for federated queries:

$users = db::table (' users ')->where (' id ', ' < ', 3);
$users = db::table (' users ')->where (' id ', ' > ', 2)->union ($users)->get ();
DD ($users);

3, WHERE clause

You can add a custom query condition by using the Where method on the query builder, which requires passing in three parameters: the first column name, the second is the operator, and the third is the comparison value:

$user = db::table (' users ')->where (' name ', ' = ', ' Laravel ')->get ();
DD ($user);

Note that the Query Builder supports the method chain, which means that if there are multiple query conditions and the multiple conditions are connected, you can use more than one where method before get. If multiple conditions are connected using or, you can use the Orwhere method:

$user = db::table (' users ')->where (' name ', ' Laravel ')->orwhere (' name ', ' Academy ')->get ();

4, sorting

The Query Builder sorts query results by using the order by method:

$users = db::table (' users ')->orderby (' id ', ' desc ')->get ();
DD ($users);

5. Group
Groups are generally used to aggregate queries, and then we use the GroupBy method to group query results, such as we can count several articles under each category:

$posts = db::table (' posts ')->select (' cat_id ', Db::raw (' COUNT (ID) as num ')->groupby (' cat_id ')->get ();
DD ($posts);

We can also use the having method to add conditions to the grouping, for example, we can count the categories with the total number of views greater than 500:

$posts = db::table (' posts ')->select (' cat_id ', Db::raw (' SUM (views) as views ')->groupby (' cat_id ')->having (' Views ', ' > ',->get ();
DD ($posts);
Note: The conditional field in the having must appear in the Select query field or it will be an error.

6, pagination

The Query Builder uses skip and take to page through the query results, which is equivalent to the limit statement in the SQL statement:

$posts = db::table (' posts ')->skip (0)->take (2)->get ();
DD ($posts);