Database of Laravel

I. Basic 1. configuration file: Config/database.php2. Run Native SQL query $users = db::select (' select * from users where active =? ', [1]); $results = DB:: Select (' SELECT * from users WHERE id =: id ', [' id ' = + 1]); 3.insertdb::insert (' INSERT into users (ID, name) VALUES (?,?  ) ', [1, ' Dayle ']; 4.update$affected = db::update (' update users set votes = + WHERE name =? ', [' John ']); 5.delete$deleted = DB::d elete (' Delete from users '), 6. General queries, some queries that do not return results, such as deleting table db::statement (' drop table users '); Listening for Query event class Appserviceprovider extends serviceprovider{/*** start all app Services * * @return void*/public function boot () {Db::listen (function ($ SQL, $bindings, $time) {//});} ....} Three. Transaction 1. Automatic db::transaction (function () {db::table (' users ')->update ([' votes ' + 1]);D b::table (' posts ') Delete ();}); If an error occurs in the closure function, the transaction is rolled back 2. Manual db::begintransaction () db::rollback ();D b::commit (); Iv. connection name for multiple database connections to the connection method corresponding to the configuration file config/ The corresponding connection in database.php $users = db::connection (' foo ')->select (...); $pdo = Db::connection ()->getpdo (); Gets the underlying native PDO instance five. Query 1. Query result set (if a table prefix is specified in the configuration file, TaThe table name in the Ble method can be used without a table prefix. A. Get all records, Table,get$users = db::table (' users ')->get (); b. Take out a row, first$users = db::table (' users ')- >first (); c. Get partial results, chunkdb::table (' users ')->chunk (function ($users) {foreach ($users as $user) {//}}); False to abort the operation of the Block Db::table (' users ')->chunk (function ($users) {//processing result set ... return false;}); D. Get a list of column values, Lists$titles = db::table (' roles ')->lists (' title '), foreach ($titles as $title) {echo $title;} Specifies the key//returns an array, the Name field as the Key,title field as Value$roles = db::table (' roles ')->lists (' title ', ' name '); foreach ($roles as $ Name = $title) {echo $title;} 2. aggregate function $users = db::table (' users ')->count (), $price = db::table (' orders ')->max (' price '); $price = Db::table (' Orders ')->where (' finalized ', 1)->avg (' Price '); 3. Specify the field $users = db::table (' users ')->select (' name ', ' email as User_email ')->get (); 4. Do not repeat results $users = db::table (' users ')->distinct ()->get (); 5. If you already have a query builder instance and want to add a query column to the existing SELECT clause, you can use the Addselect method $query = db::table (' users ')->select (' name '); $users = $qUery->addselect (' Age ')->get (); 6. Native Statement $users = db::table (' users ')->select (Db::raw (' count (*) as User_count, Status ')->where (' status ', ' <> ', 1)->groupby (' status ')->get (); six. Connection 1. Internal connection $users = db::table (' users ')- >join (' Contacts ', ' users.id ', ' = ', ' contacts.user_id ')->join (' orders ', ' users.id ', ' = ', ' orders.user_id '), Select (' users.* ', ' contacts.phone ', ' Orders.price ')->get (); 2. Left Connection $users = db::table (' users ')->leftjoin (' posts ', ' users.id ', ' = ', ' posts.user_id ')->get (); 3. Advanced Connection Statement db::table (' users ')->join (' Contacts ', function ($join) {$ Join->on (' users.id ', ' = ', ' contacts.user_id ')->oron (...);}) ->get (); You can use the WHERE style clause db::table (' users ')->join (' Contacts ', function ($join) {$join->on (' users.id ', ' = ', ' contacts.user_id ')->where (' contacts.user_id ', ' > ', 5);}) ->get (); vi, union $first = db::table (' users ')->wherenull (' first_name '); $users = db::table (' users ')->wherenull (' Last_Name ')->union ($first)->get () UnionAll method is also valid and has the same use as the UnionMethod. Seven. where statement 1. $users = db::table (' users ')->where (' votes ', ' = ', ')->get (); if it is =, it can be shortened to $users = db::table (' users ')- >where (' votes ', +)->get (); You can use other operators to write WHERE clauses $users = db::table (' users ')->where (' votes ', ' >= ', 100)- >get (); $users = db::table (' users ')->where (' votes ', ' <> ', ')->get (); $users = db::table (' users ') Where (' name ', ' like ', ' t% ')->get (); 2.or$users = db::table (' users ')->where (' votes ', ' > ', ')->orwhere (' Name ', ' John ')->get (); 3.and$users = db::table (' users ')->where (' votes ', ' > ', ')->where (' name ', ' John ')- >get (); 4.wherebetween/wherenotbetween$users = db::table (' users ')->wherebetween (' votes ', [1, +])->get (); $users = db::table (' users ')->wherenotbetween (' votes ', [1])->get (); 5.wherein/wherenotin$users = Db::table ( ' Users ')->wherein (' id ', [1, 2, 3])->get (); $users = db::table (' users ')->wherenotin (' id ', [1, 2, 3])->get (); 6.wherenull/wherenotnull$users = db::table (' users ')->wherenull (' UpdatEd_at ')->get (); $users = db::table (' users ')->wherenotnull (' Updated_at ')->get (); 6. Advanced Wherea. Parameter Grouping db::table ( ' Users ')->where (' name ', ' = ', ' John ')->orwhere (function ($query) {$query->where (' votes ', ' > ', Where (' title ', ' <> ', ' Admin ');}) ->get (); equivalent to: SELECT * from users where name = ' John ' or (votes > + title <> ' Admin ') b.exists statement db::table ( ' Users ')->whereexists (function ($query) {$query->select (Db::raw (1))->from (' orders ')->whereraw (' orders.user_id = Users.id ');})  ->get (); equivalent to: SELECT * from Userswhere exists (select 1 from orders where orders.user_id = Users.id) eight. Sort 1.orderby$users = Db::table (' users ')->orderby (' name ', ' desc ')->get (); 2.groupby/having/havingraw$users = db::table (' users ')- >groupby (' account_id ')->having (' account_id ', ' > ', ')->get (); The Havingraw method can be used to set the native string as the value of the HAVING clause, for example, We're going to find all the parts that sell more than $2500: $users = db::table (' orders ')->select (' Department ', Db::raw (' SUM (price) as Total_sales ')- >groupby (' DEPArtment ')->havingraw (' SUM (price) > 2500 ')->get (); Nine, limit the result set (MySQL limit) skip/take$users = db::table (' users ') ->skip (->take) (5)->get (); x, Insert1. Single record db::table (' users ')->insert ([' Email ' = ' [email  ') Protected] ', ' votes ' + 0]); 2. Multiple Records db::table (' users ')->insert ([' Email ' = ' [email protected] ', ' votes ' = 0],[' email ' + ' [email protected] ', ' votes ' + 0]]; 3. Gets the self-increment ID based on the previously inserted column value to get $id = db::table (' users ')- >insertgetid ([' Email ' = ' [email protected] ', ' votes ' + 0]); 11. Update 1. General db::table (' users ')->where (' Id ', 1)->update ([' votes ' + 1]) 2. Increase/decrease, default step is 1db::table (' users ')->increment (' votes ');D b::table (' users ')- >increment (' votes ', 5);D b::table (' users ')->decrement (' votes ');D b::table (' users ')->decrement (' votes ', 5) ; Specify update additional columns db::table (' users ')->increment (' votes ', 1, [' name ' = ' John ']); 12. Delete 1. General db::table (' users ')->delete () 2.wheredb::table (' users ')->where (' votes ', ' < ', +)->delete (); 3. Clear Table db::table (' UsERs ')->truncate () 13. Pessimistic lock/Optimistic lock (with transaction) db::table (' users ')->where (' votes ', ' > ', ')->sharedlock ()->get ();D b::table (' users ')->where (' votes ', ' > ', ')->lockforupdate ()->get ();

　　

Database of Laravel