Yii 数据库操作

使用不同的数据库（需在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语句 ...
});

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