Yii2——使用数据库操作汇总（增删查改、事务）

直接数据库操作

查询

//createCommand(执行原生的SQL语句) 
$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']; 
} 
 
查询返回多行： 
 
$command = $connection->createCommand('SELECT * FROM post'); 
$posts = $command->queryAll(); 
返回单行： 
 
$command = $connection->createCommand('SELECT * FROM post WHERE id=1'); 
$post = $command->queryOne(); 
查询多行单值： 
 
$command = $connection->createCommand('SELECT title FROM post'); 
$titles = $command->queryColumn(); 
查询标量值/计算值： 
 
$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 一次插入多行 
$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(); 

事务

//事务的基本结构(多表更新插入操作请使用事务处理) 
$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,'添加成功!!!'); 
}catch(Exception$e){ 
  $dbTrans->rollback(); 
// $this->_end($e->getCode(),$e->getMessage()); 

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持易采站长站。