YII2 Database Operations Summary

1. Simple query $admin=admin::model ()->findall ($condition, $params); $admin =admin::model ()->findall ("Username=: Name ", Array (": name "= = $username)); $INFOARR = Newslist::model ()->findall ("status = ' 1 ' ORDER by ID DESC limit 10"); 2. FINDALLBYPK (the method is to query a collection based on the primary key, you can use multiple primary keys) $admin =admin::model ()->findallbypk ($postIDs, $condition, $params); $admin = Admin::model ()->findallbypk ($id, "name Like:name and Age=:age", Array (': Name ' = ' $name, ' age ' = $age)); $admin = Admin::model ()->findallbypk (array); 3.findAllByAttributes (the method is to query a set based on conditions, can be multiple conditions, put the conditions in the array) $admin =admin::model ()->findallbyattributes ($ Attributes, $condition, $params); $admin =admin::model ()->findallbyattributes (Array (' username ' = ' admin '); 4.FINDALLBYSQL (the method is to query an array based on an SQL statement) $admin =admin::model ()->findallbysql ($sql, $params); $admin =admin::model ()- >findallbysql ("select * from admin where username Like:name", Array (': Name ' = '%ad% ')); User::find ()->all ();     This method returns all data; User::findone ($id);   This method returns the primary key A piece of data id=1   (for example),  user::find ()->where ([' name ' = ' lad '])->one ();   This method returns a data of [' name ' = ' lad '], User::find ()->where ([' name ' = ' lad '])->all ();   This method returns all data for [' name ' = ' lad ']; User::find ()->orderby (' id DESC ')->all ();   This method is a sort query; User::findbysql (' SELECT * from User ')->all ();   This method uses SQL   statements to query all data in the user table; User::findbysql (' SELECT * from user ')->one ();   This method uses SQL   statements to query a data in the user table; User::find ()->andwhere ([' sex ' = ' male ', ' age ' = '] ')->count (' id ') ;   Statistics of the total number of eligible articles; User::find ()->one ();     This method returns a piece of data; User::find ()->all ();     This method returns all data; User::find ()->count ();     This method returns the number of records; User::find ()->average ();     This method returns the average of the specified column; User::find ()->min ();     This method returns the minimum value for the specified column; User::find ()->max ();     This method returns the maximum value of the specified column; User::find ()->scalar ();     This method returns the query result for the first column of the first row of the value; User::find ()->column ();     This method returns the value of the first column in the query result; User::find ()->exisTS ();     This method returns a value indicating whether the data row contains the query results; User::find ()->batch (10);   FETCH 10 data per  user::find ()->each (10);   FETCH 10 data each time, iterate query two, query the method of the object//According to the primary key to query an object, such as: FINDBYPK (1); $admin =admin::model ()->findbypk ($postID, $condition, $params); $admin =admin::model ()->findbypk (1); A set of data is queried based on one condition, possibly multiple, but he only returns the first row of data $row=admin::model ()->find ($condition, $params); $row =admin::model ()->find (' Username=:name ', Array (': Name ' = ' admin ')); The method is to query a set of data according to the condition, can be more than one condition, put the condition into the array, the query is also the first Data $admin=admin::model ()->findbyattributes ($attributes, $condition , $params); $admin =admin::model ()->findbyattributes (Array (' username ' = ' admin '); The method is to query a set of data according to the SQL statement, he also queried the first Data $admin=admin::model ()->findbysql ($sql, $params); $admin =admin::model () Findbysql ("select * from admin where username=:name", Array (': Name ' = ' admin ')); To make a method of getting SQL, to query an object based on find $criteria =newcdbcriteria;    $criteria->select= ' username ';//Only select the ' title ' column $criteria->condition= ' Username=:username '; Please note that this is aQuery criteria, and only one query condition. Multi-criteria with Addcondition$criteria->params=array (":username=> ' admin '"); $criteria->order = "id DESC "; $criteria->limit =" 3 "; $post =post::model ()->find ($criteria);//$params IsNot needed//multi-conditional query statement $criteria=     New Cdbcriteria; $criteria->addcondition ("id=1");//Query condition, that is, where id = 1 $criteria->addincondition (' id ', array (1,2,3,4,5));// Represents where ID in (1,2,3,4,5,); $criteria->addnotincondition (' id ', array (1,2,3,4,5));//with the above exact phase, is not in $criteria->addcondition (' id=1 ', ' OR ') ;//This is an OR condition, when multiple conditions, the condition is or instead of and $criteria->addsearchcondition (' name ', ' classification ');//The search condition, in fact, represents the.   Where name like '% category% ' $criteria->addbetweencondition (' id ', 1, 4);//between 1 and 4$criteria->compare (' id ', 1);   This method is special and will automatically be processed into addcondition or addincondition according to your parameters. $criteria->compare (' id ', array (all-in-one)); That is, if the second parameter is an array will call addincondition $criteria->select = ' id,parentid,name ';//represents the field to be queried, the default select= ' * '; $criteria->join = ' xxx '; Connection table $criteria->with = ' xxx '; Call Relations $criteria->limit = 10;  Take 1 data, if less than 0, then do not process $criteria->offset = 1; Two is combined, which means that the limit is offset 1, or the representative. Limit 1,10 $criteria->order = ' xxx desc,xxx ASC ';//Sort condition $criteria->group = ' group condition '; $criteria->having = ' having condition '; $criteria->distinct = false;//is the only query three, the number of queries, determine whether the query has a result//the method is to query a set according to a condition how many records, return an int type number $n=post::model () Count ($condition, $params); $n =post::model ()->count ("Username=:name", Array (": name" = = $username)); This method is based on the SQL statement query how many records of a collection, return an int type number $n=post::model ()->countbysql ($sql, $params); $n =post::model () Countbysql ("select * from admin where username=:name", Array (': Name ' = ' admin ')); The method is based on a conditional query query to get the array with no data, if there is data returned by a true, otherwise $exists=post::model ()->exists ($condition, $params) is not found; $exists =       Post::model ()->exists ("Name=:name", Array (": Name" and "= $username)"; new $admin= new admin; $admin->username = $username; $admin->password = $password; if ($admin->save () > 0) {echo "Add success";} Else{echo "Add failed";} V. Modification of Post::model ()->updateall ($attributes, $conditiOn, $params); $count =admin::model ()->updateall (' username ' = ' 11111 ', ' password ' = ' 11111 '), ' password =:p ', array (':p ' n ' and ' 1111a1 ')); if ($count > 0) {echo "modified successfully";} Else{echo "modification Failed";} $rt = Postlist::model ()->updateall (' status ' = ' 1 '), ' Staff_id=:staff and Host_id=:host ' , Array (': Staff ' = $staff _id, ': Host ' = $host _id)); $PK primary key, which can be either a collection, $attributes is a collection of fields to modify, $condition condition, $params the value passed in Post::model ()->UPDATEBYPK ($PK, $ Attributes, $condition, $params); $count =admin::model ()->updatebypk (1,array (' username ' = ' Admin ', ' password ' = > ' admin '); $count =admin::model ()->updatebypk (array), Array (' username ' + ' admin ', ' password ' = ') Admin '), ' Username=:name ', Array (': Name ' = ' admin ')), if ($count >0) {echo "modified successfully";} Else{echo "modification failed";} Post::model ()->updatecounters ($counters, $condition, $params), $count =admin::model ()->updatecounters (Array ( ' Status ' =>1, ' username=:name ', Array (': Name ' = ' admin ')), if ($count > 0) {echo "modified successfully";} Else{echo "modification failed";} Array(' status ' =>1) on behalf of the Admin table in the database according to the conditions username= ' admin ', the query out all the results of the Status field is added 16, delete//deleteallpost::model () DeleteAll ($condition, $params); $count = Admin::model ()->deleteall (' Username=:name and password=:p ' n ', Array (': Name ' = ' + ' admin ', ':p ' to ' the ' admin '); $count = Admin::model ()->deleteall (' ID in (") ');//delete the data ID for these if ($ count>0) {echo "delete succeeded";} Else{echo "Delete failed";}//deletebypkpost::model ()->deletebypk ($PK, $condition, $params); $count = Admin::model () DELETEBYPK (1); $count =admin::model ()->deletebypk (array), ' Username=:name ', Array (': Name ' = ' Admin ')); $count >0) {echo "delete succeeded";} Else{echo "Delete failed";}

Direct Database Operation Query

CreateCommand (executes the native SQL statement) $sql = "Select u.account,i.* from Sys_user as U left join User_info as I on u.id=i.user_id"; $row S=yii:: $app->db->createcommand ($sql)->query (), foreach ($rows as $k = = $v) {    echo$v[' add_time '];} The query returns multiple lines: $command = $connection->createcommand (' SELECT * from post '), $posts = $command->queryall (); return line: $command = $connection->createcommand (' SELECT * from post WHERE id=1 '); $post = $command->queryone (); query multiline single value: $command = $ Connection->createcommand (' SELECT title from Post '), $titles = $command->querycolumn (); Query scalar value/computed value: $command = $ Connection->createcommand (' SELECT COUNT (*) from post '); $postCount = $command->queryscalar ();

Update

$command = $connection->createcommand (' UPDATE post SET status=1 WHERE id=1 '); $command->execute ();

Insert Update Delete

Insert$connection->createcommand ()->insert (' User ', [    ' name ' = ' Sam ',    ' age ' = ', ']) Execute ();//Insert multiple lines $connection->createcommand ()->batchinsert (' User ', [' name ', ' age '], [    [' Tom ', 30] ,    [' Jane ', +],    [' Linda ', ' +],])->execute ();//Update$connection->createcommand ()->update (' user ' , [' Status ' = 1], ' Age > ')->execute ();//Delete$connection->createcommand ()->delete (' User ', ' status = 0 ')->execute ();

Transaction

The basic structure of the transaction (use transaction processing for multiple table update inserts) $dbTrans = Yii::app ()->db->begintransaction (), try{    $post = new post;    $post ' title ' = ' Hello dodobook!!! ';    if (! $post->save ()) Throw newexception ("Error processing Request", 1);    $dbTrans->commit ();//  $this->_end (0, ' Add success!!! ');} catch (exception$e) {    $dbTrans->rollback ();//  $this->_end ($e->getcode (), $e->getmessage ());

YII2 Database Operations Summary