Database operation of Laravel framework

1. Basic operation with DB façade once you've set up your database connection, you can use DB facade to find it. DB Facade provides a lookup method for each type: Select, UPDATE, INSERT, delete, statement. 1.1 Add

Db::insert (' INSERT into users (ID, name) VALUES (?,?) ', [1, ' Dayle ']);

1.2 by deleting

$deleted = DB::d elete (' Delete from users ');

Return value: The number of deleted rows will be returned 1.3

$affected = db::update (' update users set votes = + WHERE name =? ', [' John ']);

Return value: The method returns the number of rows affected by this declaration 1.4

$users = Db::select (' select * from users where active =? ', [1]);

Return value: The method always returns the array data of the result. Each result in the array is a PHP StdClass object, which allows you to access the value of the result: Access method: foreach ($users as $user) {echo $user->name;} 1.5 transactions

// automatically　　Db::transaction (function  () {DB:: Table (' users ')->update ([' votes ' = 1]); DB:: Table (' posts '),Delete ();}); // Manual (can be used with try catch) DB::begintransaction (); if　　($someContion) {DB::rollback ();  returnfalse;} DB:: Commit ();

1.6 Sometimes some database operations should not return any parameters. For this type of operation, you can use the statement method in DB facade:

Db::statement (' drop table users ');

2. The query constructor gets the query builder is very simple, or to rely on the db façade, we use the DB Façade table method, passing in the form name, you can get the table Query Builder: 2.1 Get results <1> get all data columns from the data table

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

Return value: The method returns an array of results, each of which is an instance of the PHP StdClass object

Access method: foreach ($users as $user) {echo $user->name;} <2> get a single column or row from a datasheet 1> you can use the first method if you only need to remove a single row of data from the datasheet.

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

Return value: This method returns a single StdClass object: How to use: Echo $user->name; 2> If you do not want to take out the full line, you can use the value method to remove a single value from the individual record.

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

Return value: This method returns the value of the field directly: <3> dice the results from the data table if you need to manipulate thousands of database records, you might consider using the chunk method. This method takes only a small "block" result at a time and passes each chunk to a closure for processing. For example, let's dice the entire User data table and process 100 records at a time:

function　　($users) {  foreach ($usersas$user) {  //  }});

You can return false from the closure to stop the processing of subsequent chunks:

function ($users) {  //  process record ... return false ;});　　

<4> get a list of field values if you want to get an array that contains a single field value, you can use the Lists method

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

Return value: In this example, we will take out the array of the Role data table title field: using: foreach ($titles as $title) {echo $title;} You can also specify a custom key-value field in the returned array:

$roles = db::table (' roles ')->lists (' title ', ' name '); foreach ($rolesas$name$title) {  echo$title;}

Usage of 2.2select 1> specify a Select clause

//Specify Field $users = db::table (' users ')->select (' name ', ' email as User_email '),get (); // the distinct method allows you to force a lookup to return a result that is not duplicated: $users = db::table (' users ')->distinct (),get (); // There is already an instance that you want to add a field to in the SELECT clause, you can use the Addselect method: $query = db::table (' users ')->select (' name '); $users $query->addselect (' Age ')->get ();

2> Original expression

// The Db::raw method can prevent injection $users = db::table (' users ')->select (Db::raw (' count (*) as User_count, Status '))->where (' status ', ' < > ', 1)->groupby (' status ')->get ();

Usage of 2.3join 1> basic usage

$users
Join
Join

->get ();

If you want to connect using left JOIN or right, you can 2> advanced join syntax by replacing the join with Leftjoin,rightjoin. You may also specify a more advanced join clause. Let's start by passing in a closure as the second parameter to the join method. This closure receives the JoinClause object, allowing you to specify the constraint on the join clause:

Db::table (' users ') ,joinfunction ($join) {  $join->on ( ' Users.id ', ' = ', ' contacts.user_id ') ->oron (...  ->get ();

If you want to use "where"-style clauses in a connection, you can use the where and Orwhere methods in the connection. These methods compare fields and a value to replace the comparison of two fields:

Joinfunction ($join) {  $join
->where (' contacts.user_id ', ' > ', 5->get ();

Here to mention if the closure function Functiton () {} 5 is a variable, we need to pass it so that it should be written

function ($joinuse ($num) {  $join
$num )};

If there is no use ($num) the $num outside cannot be passed to the WHERE clause

Usage of 2.4where 1> basic usage

$users= Db::table (' users ')->where (' votes ', ' = ', +)get ();$users= Db::table (' users ')->where (' name ', ' like ', ' t% ')get ();$users= Db::table (' users ')->where (' votes '),->get ();//omit the equals sign$users= Db::table (' users ')->where (' votes ', ' > ', ')->orwhere (' name ', ' John ')get ();$users= Db::table (' users ')->wherebetween (' votes ', [1])->get ();//the value of a field is between two values$users= Db::table (' users ')->wherenotbetween (' votes ', [1])->get ();//the value of a field falls outside of two values$users= Db::table (' users ')->wherein (' id ', [1, 2, 3])->get ();//The value of the field is contained within the specified array$users= Db::table (' users ')->wherenotin (' id ', [1, 2, 3])->get ();//The value of the field is not contained within the specified array$users= Db::table (' users ')->wherenull (' Updated_at ')->get ();//the value of the specified column is NULL$users= Db::table (' users ')->wherenotnull (' Updated_at ')->get ();//the value of a column is not NULL

2> Advanced Usage Grouping constraints

->orwhere (function ($query) {  $query
->where (' title ', ' <> ', ' Admin '->get ();

2.5orderby,groupby,having

$users = db::table (' users ')->orderby (' name ', ' desc '),get (); $users = db::table (' users ')->groupby (' account_id ')->having (' account_id ', ' > ', '),get () ; // The Havingraw method can be used to set the original string to the value of the HAVING clause $users = db::table (' orders ')->select (' Department ', Db::raw (' SUM (price) as Total_sales '))->groupby (' Department ')->havingraw (' SUM (price) > 2500 ')->get ();

Usage of 2.6insert

Db::table (' users ')->insert ([' Email ' = ' [email protected] ', ' votes ' + 0]); // multiple sets of data can be inserted at one time  Db::table (' users ')->insert ([' Email ' + ' [email protected] ', ' votes ' + 0], [' email ' = ' [email protected] ', ' votes ' = 0]]); // If the data table has an auto-incrementing ID, you can use the Insertgetid method to insert the record and get its ID: $id = db::table (' users ')->insertgetid ([' Email ' = ' [email protected] ', ' votes ' + 0]);

Usage of 2.7update

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

2.8delete

Db::table (' users '),delete ();D b:: Table (' users ')->where (' votes ', ' < ', +),Delete (); // if you want to truncate all data columns of the entire data table and reset the auto-increment ID to zero, you can use the Truncate method: Db::table (' users ')->truncate ();

Database operation of Laravel framework