Yii2数据库操作常用方法小结
本文实例讲述了Yii2数据库操作常用方法。分享给大家供大家参考，具体如下：
查询：
//findthecustomerswhoseprimarykeyvalueis10
$customers=Customer::findAll(10);
$customer=Customer::findOne(10);
//theabovecodeisequivalentto:
$customers=Customer::find()->where(['id'=>10])->all();
//findthecustomerswhoseprimarykeyvalueis10,11or12.
$customers=Customer::findAll([10,11,12]);
$customers=Customer::find()->where(['IN','id',[10,11,12]])->all();
//theabovecodeisequivalentto:
$customers=Customer::find()->where(['id'=>[10,11,12]])->all();
//findcustomerswhoseageis30andwhosestatusis1
$customers=Customer::findAll(['age'=>30,'status'=>1]);
//theabovecodeisequivalentto:
$customers=Customer::find()->where(['age'=>30,'status'=>1])->all();
//useparamsbinding
$customers=Customer::find()->where('age=:ageANDstatus=:status')->addParams([':age'=>30,':status'=>1])->all();
//useindex
$customers=Customer::find()->indexBy('id')->where(['age'=>30,'status'=>1])->all();
//getcustomerscount
$count=Customer::find()->where(['age'=>30,'status'=>1])->count();
//addadditioncondition
$customers=Customer::find()->where(['age'=>30,'status'=>1])->andWhere('score>100')->orderBy('idDESC')->offset(5)->limit(10)->all();
//findbysql
$customers=Customer::findBySql('SELECT*FROMcustomerWHEREage=30ANDstatus=1ANDscore>100ORDERBYidDESCLIMIT5,10')->all();
修改：
//updatestatusforcustomer-10 $customer=Customer::findOne(10); $customer->status=1; $customer->update(); //theabovecodeisequivalentto: Customer::updateAll(['status'=>1],'id=:id',[':id'=>10]);
删除：
//deletecustomer-10 Customer::findOne(10)->delete(); //theabovecodeisequivalentto: Customer::deleteAll(['status'=>1],'id=:id',[':id'=>10]);
----------------使用子查询----------------------
$subQuery=(newQuery())->select('COUNT(*)')->from('customer');
//SELECT`id`,(SELECTCOUNT(*)FROM`customer`)AS`count`FROM`customer`
$query=(newQuery())->select(['id','count'=>$subQuery])->from('customer');
----------------手写SQL-----------------------
//select
$customers=Yii::$app->db->createCommand('SELECT*FROMcustomer')->queryAll();
//update
Yii::$app->db->createCommand()->update('customer',['status'=>1],'id=10')->execute();
//delete
Yii::$app->db->createCommand()->delete('customer','id=10')->execute();
//transaction
//outer
$transaction1=$connection->beginTransaction();
try{
$connection->createCommand($sql1)->execute();
//internal
$transaction2=$connection->beginTransaction();
try{
$connection->createCommand($sql2)->execute();
$transaction2->commit();
}catch(Exception$e){
$transaction2->rollBack();
}
$transaction1->commit();
}catch(Exception$e){
$transaction1->rollBack();
}
---------------主从配置----------------------
[ 'class'=>'yii\db\Connection', //master 'dsn'=>'dsnformasterserver', 'username'=>'master', 'password'=>'', //slaves 'slaveConfig'=>[ 'username'=>'slave', 'password'=>'', 'attributes'=>[ //useasmallerconnectiontimeout PDO::ATTR_TIMEOUT=>10, ], ], 'slaves'=>[ ['dsn'=>'dsnforslaveserver1'], ['dsn'=>'dsnforslaveserver2'], ['dsn'=>'dsnforslaveserver3'], ['dsn'=>'dsnforslaveserver4'], ], ]
更多关于Yii相关内容感兴趣的读者可查看本站专题：《Yii框架入门及常用技巧总结》、《php优秀开发框架总结》、《smarty模板入门基础教程》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总》
希望本文所述对大家基于Yii框架的PHP程序设计有所帮助。