使用数据库操作汇总（增删查改、事务）(2)

//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());