Yii2数据库操作常用方法小结

　　本文实例讲述了Yii2数据库操作常用方法。分享给大家供大家参考，具体如下：

　　查询：

　　 // find the customers whose primary key value is 10 $customers = Customer::findAll(10); $customer = Customer::findOne(10); // the above code is equivalent to: $customers = Customer::find()->where([id => 10])->all(); // find the customers whose primary key value is 10, 11 or 12. $customers = Customer::findAll([10, 11, 12]); $customers = Customer::find()->where([IN,id,[10,11,12]])->all(); // the above code is equivalent to: $customers = Customer::find()->where([id => [10, 11, 12]])->all(); // find customers whose age is 30 and whose status is 1 $customers = Customer::findAll([age => 30, status => 1]); // the above code is equivalent to: $customers = Customer::find()->where([age => 30, status => 1])->all(); // use params binding $customers = Customer::find()->where(age=:age AND status=:status)->addParams([:age=>30,:status=>1])->all(); // use index $customers = Customer::find()->indexBy(id)->where([age => 30, status => 1])->all(); // get customers count $count = Customer::find()->where([age => 30, status => 1])->count(); // add addition condition $customers = Customer::find()->where([age => 30, status => 1])->andWhere(score > 100)->orderBy(id DESC)->offset(5)->limit(10)->all(); // find by sql $customers = Customer::findBySql(SELECT * FROM customer WHERE age=30 AND status=1 AND score>100 ORDER BY id DESC LIMIT 5,10)->all();

　　修改：

　　 // update status for customer-10 $customer = Customer::findOne(10); $customer->status = 1; $customer->update(); // the above code is equivalent to: Customer::updateAll([status => 1], id = :id,[:id=>10]);

　　删除：

　　 // delete customer-10 Customer::findOne(10)->delete(); // the above code is equivalent to: Customer::deleteAll([status => 1], id = :id,[:id=>10]);

　　----------------使用子查询----------------------

　　 $subQuery = (new Query())->select(COUNT(*))->from(customer); // SELECT `id`, (SELECT COUNT(*) FROM `customer`) AS `count` FROM `customer` $query = (new Query())->select([id, count => $subQuery])->from(customer);

　　----------------手写SQL-----------------------

　　 // select $customers = Yii::$app->db->createCommand(SELECT * FROM customer)->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 => dsn for master server, username => master, password => , // slaves slaveConfig => [ username => slave, password => , attributes => [ // use a smaller connection timeout PDO::ATTR_TIMEOUT => 10, ], ], slaves => [ [dsn => dsn for slave server 1], [dsn => dsn for slave server 2], [dsn => dsn for slave server 3], [dsn => dsn for slave server 4], ], ]

　　更多关于Yii相关内容感兴趣的读者可查看本站专题：《Yii框架入门及常用技巧总结》、《php优秀开发框架总结》、《smarty模板入门基础教程》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总》

　　希望本文所述对大家基于Yii框架的PHP程序设计有所帮助。