使用不同的數據庫（需在db.php中配置）

https://learnku.com/docs/yii-framework/2.0.x/active-record/12076#bdd6c2

Yii::$app->db

Yii::$app->dbOther

//數據庫連接實例化
$db = Yii::$app->db;

// 返回多行，每行都是列名和值的關聯數組
// 如果該查詢沒有結果則返回 空數組 []
$posts = $db->createCommand('SELECT * FROM post')->queryAll();

// 返回一行 (第一行)
// 如果該查詢沒有結果則返回 false
$post = $db->createCommand('SELECT * FROM post WHERE id=1')->queryOne();

// 返回一列 (第一列)
// 如果該查詢沒有結果則返回 空數組 []
$titles = $db->createCommand('SELECT title FROM post')->queryColumn();

// 返回一個標量值
// 如果該查詢沒有結果則返回 false
$count = $db->createCommand('SELECT COUNT(*) FROM post')->queryScalar();

綁定參數

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
->bindValue(':id', $_GET['id'])
->bindValue(':status', 1)
->queryOne();

————————————————
 

$params = [
':id' => $_GET['id'],
':status' => 1
];
$db = Yii::$app->db;
$post = $db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
->bindValues($params)
->queryOne();

$post = $db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status', $params)
->queryOne();

執行非查詢語句

//INSERT INTO user (name, age) VALUES ('Sam', 30);
$db = Yii::$app->db;
//原生方式
$db->createCommand("INSERT INTO user (name, age) VALUES ('Sam', 30)")->execute();
//推薦方式
$db->createCommand()->insert('user', [
'name' => 'Sam',
'age' => 30,
])->execute();

//UPDATE user SET status=1 WHERE id=1 AND age>30;
$db = Yii::$app->db;
//原生方式
$db->createCommand('UPDATE user SET status=1 WHERE id=1 AND age>30')->execute();
//推薦方式
$db->createCommand()->update('user', ['status' => 1], 'id=1 AND age>30')->execute();
//帶參數
$age = 30;
$db->createCommand()->update('user', ['status' => 1], 'id=1 AND age>:age', [':age' => $age])->execute();

// DELETE FROM user WHERE status = 0;
$db = Yii::$app->db;
//原生方式
$db->createCommand('DELETE FROM user WHERE status = 0')->execute();
//推薦方式
$db->createCommand()->delete('user', 'status = 0')->execute();

批量插入

————————————————

//INSERT INTO user (name, age) VALUES ('Tom', 30),('Jane', 20),('Linda', 25);
Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [
['Tom', 30],
['Jane', 20],
['Linda', 25],
])->execute();

update or  insert

————————————————

Yii::$app->db->createCommand()->upsert('pages', [
'name' => 'Front page',
'url' => 'http://example.com/', // url is unique
'visits' => 0,
], [
'visits' => new \yii\db\Expression('visits + 1'),
], $params)->execute();

數據庫事務

————————————————

Yii::$app->db->transaction(function($db) {
$db->createCommand($sql1)->execute();
$db->createCommand($sql2)->execute();
// ... 執行其他SQL語句 ...
});

————————————————