SQL 基础查询
一旦有了连接实例就可以通过[[yii\db\Command]]执行 SQL 查询。
$connection = \Yii::$app->db;
SELECT 查询
查询返回多行:
$command = $connection->createCommand('SELECT * FROM post');
$posts = $command->queryAll();
返回单行:
$command = $connection->createCommand('SELECT * FROM post WHERE id=1');
$post = $command->queryOne();
查询多行单值:
$command = $connection->createCommand('SELECT title FROM post');
$titles = $command->queryColumn();
查询标量值/计算值:
$command = $connection->createCommand('SELECT COUNT(*) FROM post');
$postCount = $command->queryScalar();
UPDATE, INSERT, DELETE 更新、插入和删除操作
如果执行 SQL 不返回任何数据可使用命令中的 execute 方法:
$command = $connection->createCommand('UPDATE post SET status=1 WHERE id=1');
$command->execute();
可以使用insert
,update
,delete
方法,这些方法会根据参数生成合适的SQL并执行.
// 单条插入
$connection->createCommand()->insert('user', [
'name' => 'Sam',
'age' => 30,
])->execute();
// 批量插入数据 一次插入多行
$connection->createCommand()->batchInsert('users', ['name', 'phone'], [
['Sam', 13835353535],
['Tom', 13600000000],
['Davie', 13500000000],
])->execute();
// 更新操作
$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();
// 删除操作
$connection->createCommand()->delete('user', 'status = 0')->execute();