Yii2——Use database operations summary (addition, deletion, search, and transactions)

//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 (This method is to query a collection based on the primary key, and multiple primary keys can be used)$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(1,2)); 
  
// (This method is to query a collection based on conditions, which can be multiple conditions and put the conditions into the array)$admin=Admin::model()->findAllByAttributes($attributes,$condition,$params); 
$admin=Admin::model()->findAllByAttributes(array('username'=>'admin')); 
  
// (This method is to query an array according to 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 Primary key id=1 A data of(Give an example)；  
User::find()->where(['name' => 'Young man'])->one();  This method returns ['name' => 'Young man'] A data of； 
User::find()->where(['name' => 'Young man'])->all();  This method returns ['name' => 'Young man'] All data of； 
User::find()->orderBy('id DESC')->all();  This method is sorting query； 
User::findBySql('SELECT * FROM user')->all(); This method is used sql Statement query user 表里面All data of； 
User::findBySql('SELECT * FROM user')->one(); This method is used sql Statement query user 表里面A data of； 
User::find()->andWhere(['sex' => 'male', 'age' => '24'])->count('id');  Statistics the total number of qualifying items； 
User::find()->one();  This method returns一Data； 
User::find()->all();  This method returns all data； 
User::find()->count();  This method returns记录的数量； 
User::find()->average();  This method returns指定列的平均值； 
User::find()->min();  This method returns指定列的最小值 ； 
User::find()->max();  This method returns指定列的最大值 ； 
User::find()->scalar();  This method returns值的第一行第一列的查询结果； 
User::find()->column();  This method returns查询结果中的第一列的值； 
User::find()->exists();  This method returns一个值指示是否包含查询结果的数据行； 
User::find()->batch(10); Take each time 10 Data  
User::find()->each(10); Take each time 10 Data， Iterative query 
two、Methods to query objects 
//Query an object based on the primary key, such as: findByPk(1);$admin=Admin::model()->findByPk($postID,$condition,$params); 
$admin=Admin::model()->findByPk(1); 
  
//Query a set of data based on one condition, which may be multiple, but it only returns the first row of data$row=Admin::model()->find($condition,$params); 
$row=Admin::model()->find('username=:name',array(':name'=>'admin')); 
  
//This method is to query a set of data based on conditions, which can be multiple conditions, put the conditions into the array, and the query is also the first piece of data.$admin=Admin::model()->findByAttributes($attributes,$condition,$params); 
$admin=Admin::model()->findByAttributes(array('username'=>'admin')); 
  
//This method is to query a set of data based on SQL statements, and the first piece of data it queryes is also the first piece of data$admin=Admin::model()->findBySql($sql,$params); 
$admin=Admin::model()->findBySql("select * from admin where username=:name",array(':name'=>'admin')); 
  
//Semble a method to obtain SQL, and 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 a query condition and there is only one query condition. Use addCondition for multiple conditions$criteria->params=array(":username=>'admin'"); 
$criteria->order ="id DESC"; 
$criteria->limit ="3"; 
$post=Post::model()->find($criteria);// $params isnot needed  
  
// Multi-condition query statement$criteria= new CDbCriteria;    
$criteria->addCondition("id=1");//Query conditions, i.e. 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));//The method is exactly the same as above, it is NOT IN$criteria->addCondition('id=1','OR');//This is an OR condition. When there are multiple conditions, the condition is OR rather than AND$criteria->addSearchCondition('name','Classification');//Search conditions actually represent it.  .  where name like '%Category%'$criteria->addBetweenCondition('id', 1, 4);//between 1 and 4 
$criteria->compare('id', 1);  //This method is quite special. It will automatically process it into addCondition or addInCondition according to your parameters.$criteria->compare('id',array(1,2,3));  //That is, if the second parameter is an array, addInCondition will be called  
  
$criteria->select ='id,parentid,name';//Represents the field to be query, default select='*';$criteria->join ='xxx'; //Connection table$criteria->with ='xxx'; //Call relationships$criteria->limit = 10;  //Take 1 piece of data, if it is less than 0, no processing will be done$criteria->offset = 1; //The two items merge to represent limit 10 offset 1, or represent it.  limit 1,10$criteria->order ='xxx DESC,XXX ASC' ;//Sorting conditions$criteria->group ='group conditions';  
$criteria->having ='having conditions';  
$criteria->distinct = FALSE;//Is it unique to querythree、Number of queries,Determine whether the query has results 
//This method is to query how many records a collection has based on a condition and return an int-type number$n=Post::model()->count($condition,$params); 
$n=Post::model()->count("username=:name",array(":name"=>$username)); 
  
//This method is to query how many records a collection has based on SQL statements and return an int-type number$n=Post::model()->countBySql($sql,$params); 
$n=Post::model()->countBySql("select * from admin where username=:name",array(':name'=>'admin')); 
  
//This method is to query the array obtained based on a condition query whether there is data. If there is data, return a true, otherwise it is not found$exists=Post::model()->exists($condition,$params); 
$exists=Post::model()->exists("name=:name",array(":name"=>$username)); 
Four、New 
$admin= new Admin;     
$admin->username =$username; 
$admin->password =$password; 
if($admin->save() > 0){echo "Added successfully"; }else{echo "Add failed"; } 
five、Revise 
Post::model()->updateAll($attributes,$condition,$params); 
$count=Admin::model()->updateAll(array('username'=>'11111','password'=>'11111'),'password=:pass',array(':pass'=>'1111a1')); 
if($count> 0){ echo "Modification was successful"; }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 can be either a set or a set. $attributes is the collection of fields to be modified, $condition conditions, and the value passed in $paramsPost::model()->updateByPk($pk,$attributes,$condition,$params); 
$count=Admin::model()->updateByPk(1,array('username'=>'admin','password'=>'admin')); 
$count=Admin::model()->updateByPk(array(1,2),array('username'=>'admin','password'=>'admin'),'username=:name',array(':name'=>'admin')); 
if($count>0){echo "Modification was successful"; }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 "Modification was successful"; }else{echo "Modification failed"; } 
//array('status'=>1) represents the admin table in the database. According to the condition username='admin', all the query status fields are added to 1 by themselves.six、delete 
//deleteAll 
Post::model()->deleteAll($condition,$params); 
$count= Admin::model()->deleteAll('username=:name and password=:pass',array(':name'=>'admin',':pass'=>'admin')); 
$count= Admin::model()->deleteAll('id in("1,2,3")');//Delete data with ids as theseif($count>0){echo"Delete successfully"; }else{echo "Delete failed"; } 
  
//deleteByPk 
Post::model()->deleteByPk($pk,$condition,$params); 
$count= Admin::model()->deleteByPk(1); 
$count=Admin::model()->deleteByPk(array(1,2),'username=:name',array(':name'=>'admin')); 
if($count>0){echo "Delete successfully"; }else{echo "Delete failed"; } 

//createCommand (execute native SQL statement)$sql= "SELECT ,i.* FROM sys_user as u left join user_info as i on =i.user_id"; 
$rows=Yii::$app->db->createCommand($sql)->query(); 
foreach($rows as $k => $v){ 
  echo$v['add_time']; 
} 
 
Query returns multiple rows： 
 
$command = $connection->createCommand('SELECT * FROM post'); 
$posts = $command->queryAll(); 
Return to a single line： 
 
$command = $connection->createCommand('SELECT * FROM post WHERE id=1'); 
$post = $command->queryOne(); 
Query multiple rows of single values： 
 
$command = $connection->createCommand('SELECT title FROM post'); 
$titles = $command->queryColumn(); 
Query scalar values/Calculate the value： 
 
$command = $connection->createCommand('SELECT COUNT(*) FROM post'); 
$postCount = $command->queryScalar(); 

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

// INSERT 
$connection->createCommand()->insert('user', [ 
  'name' => 'Sam', 
  'age' => 30, 
])->execute(); 
 
// INSERT insert multiple rows at once$connection->createCommand()->batchInsert('user', ['name', 'age'], [ 
  ['Tom', 30], 
  ['Jane', 20], 
  ['Linda', 25], 
])->execute(); 
 
// UPDATE 
$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute(); 
 
// DELETE 
$connection->createCommand()->delete('user', 'status = 0')->execute(); 

//The basic structure of the transaction (please use transaction processing for multi-table update insertion operation)$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,'Added successfully!!!');}catch(Exception$e){ 
  $dbTrans->rollback(); 
// $this->_end($e->getCode(),$e->getMessage());