Go YII2 Common Database Operations

1. Object operation:

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 the 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 a single piece of data for the primary key 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 the SQL statement to query a data in the user table, User::find ()->andwhere ([' sex ' = ' male ', ' age ' = '] ')->count (' id ');    Statistics of the total number of bars that meet the conditions; 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 of 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); 10 data per fetch USer::find ()->each (10);  Fetch 10 data each time, iterate query second, query the method of the object//According to the primary key 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 '; Note that this is a condition for a query, and there is only one query condition. Multi-condition Addcondition $criteria->params=Array (":username=> ' admin '");  $criteria->order = "id DESC";  $criteria->limit = "3";       $post =post::model ()->find ($criteria);//$params IsNot needed//multi-conditional query $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 (+/-));   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 stripsData, if less than 0, is not processed $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 not found $exists =post::model ()->exists ($condition, $params);  $exists =post::model ()->exists ("Name=:name", Array (": name" = = $username));         Iv. 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 (Array (' username ' = ' 11111 ', ' password ' = ' 11111 '), ' password=:p ',  Array (':p-up ' and ' 1111a1 '); if ($count > 0) {echo "modified successfully";}     Else{echo "modification failed";} $rt = Postlist::model ()->updateall (Array (' 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";} The array (' status ' =>1) represents the admin table in the database according to the condition username= ' admin ', the query out all Results Status field is added 16, delete//deleteall Post::model ()  DeleteAll ($condition, $params); $count = Admin::model ()->deleteall (' Username=:name and password=:p ", Array (': Name ' = ' Admin ', ':p '  Admin ')); $count = Admin::model ()->deleteall (' ID in (") ');//delete the data ID for these if ($count >0) {echo" delete succeeded ";}     Else{echo "Delete failed";}  DELETEBYPK Post::model ()->deletebypk ($PK, $condition, $params);  $count = Admin::model ()->deletebypk (1);  $count =admin::model ()->deletebypk (array), ' Username=:name ', Array (': Name ' = ' Admin ')); if ($count >0) {echo "delete succeeded";}   Else{echo "Delete failed";}

2. Native SQL operation:

CreateCommand (executes 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" ;  $rows =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 single line:    $command = $connection->createcommand (' SELECT * from post WHERE id=1 ');  $post = $command->queryone ();  Query multi-line 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 at once  $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:

//basic structure of a transaction (use transaction processing for multiple table update insert operations)$dbTrans = Yii::app ()->db->BeginTransaction (); Try{$post=NewPost; $post-'title'='Hello dodobook!!!'; if(! $post->save ())ThrowNewexception ("Error processing Request",1); $dbTrans-commit (); //$this->_end (0, ' Add success!!! '); }Catch(exception$e) {$dbTrans-rollback (); //$this->_end ($e->getcode (), $e->getmessage ()); 

Ext.: http://blog.csdn.net/xundh/article/details/45955195

Go YII2 Common Database Operations