本文实例讲述了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" => "yiidbConnection", // 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程序设计有所帮助。