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

# Yii2 # 数据库 # Yii操作数据库的3种方法 # 解析yii数据库的增删查改 # PHP的Yii框架中使用数据库的配置和SQL操作实例教程 # Yii2框架数据库简单的增删改查语法小结 # Yii2.0高级框架数据库增删改查的一些操作 # Yii2——使用数据库操作汇总（增删查改、事务） # YII2数据库查询实践 # Yii实现多数据库主从读写分离的方法 # yii数据库的查询方法 # Yii操作数据库实现动态获取表名的方法 # Yii 连接、修改 MySQL 数据库及phpunit 测试连接 # Yii 框架使用数据库(databases)的方法示例 # 程序设计 # 相关内容 # 感兴趣 # 给大家 # 更多关于 # 所述 # 面向对象 # 操作技巧 # 讲述了 # count # indexBy # add # condition # addition # index # status # params # addParams # binding # andWhere

返回目录在线咨询

上一篇：C++的UB(未定义行为)如何避免_C++编程中常见的未定义行为陷阱与检测方法
下一篇：虚拟主机建站完成后，如何备份和恢复网站数据？

您的项目需求

*请认真填写需求信息，我们会在24小时内与您取得联系。

全网整合营销服务商

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

您的项目需求