Laravel Query Builder's example of checking database additions and deletions

To get the query Builder simple, or to rely on the db façade, we use the DB Façade table method and pass in the table name to get the table's Query Builder:

$users = db::table (' users ');

So we get to the query builder of the $users table, and in fact, the bottom return is an instance of Illuminate\database\query\builder, all of our actions on the Query Builder call the method on the corresponding class of the instance. Here's a list of some common ways to query builders, and we'll use the $users table created in the previous section as a demo.

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

4. Basic Inquiry

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

So we can eliminate the hassle of traversing an array of objects.

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

How do I use the Query Builder to implement advanced queries for a database? For example, join, union, add custom where condition, sort, group, page after pagination will be introduced.