yii mysql数据库操作_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框架的PHP程序设计有所帮助。