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" => "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程序设计有所帮助。