YII2框架中操作数据库的方式实例分析

// YII2中通过createCommand来处理数据库
 
// 查询多条记录
// {{%user}} 表示如果设置了表前缀，YII会自动帮你替换
$data1 = YII::$app->db->createCommand('select * from {{%user}}')->queryAll();
 
 
// 查询一条记录
// createCommand的第二个参数可以进行参数绑定
$data2 = YII::$app->db->createCommand('select * from {{%user}} where id=:id', ['id' => 2])->queryOne();
 
 
// 返回一列(第一列)数据
$data3 = YII::$app->db->createCommand('select name from {{%user}}')->queryColumn();
 
 
// 返回一个标量值，常用于统计
$data4 = YII::$app->db->createCommand('select count(*) as cnt from {{%user}}')->queryScalar();
 
// 绑定参数，防止SQL注入问题
// bindValue绑定一个参数
$data5 = YII::$app->db->createCommand('select * from {{%user}} where id=:id')
  ->bindValue(':id', 3)
  ->queryOne();
 
// 绑定多个参数
$data6 = YII::$app->db->createCommand('select * from {{%user}} where id=:id and name=:name')
  ->bindValues([':id' => 5, ':name' => 'eee'])
  ->queryOne();
 
// 绑定参数引用
$id = 7;
$data7 = YII::$app->db->createCommand('select * from {{%user}} where id=:id')
  ->bindParam(':id', $id)
  ->queryOne();
 
// 执行非查询语句
$data8 = YII::$app->db->createCommand('update {{%user}} set name=:name where id=:id')
  ->bindValues([':name' => 'abcdef', ':id' => 8])
  ->execute();
 
// 当然，我们也可以用更加简便的方法
// insert()插入
$data9 = YII::$app->db->createCommand()->insert('{{%user}}', [
  'name' => 'test',
  'sex' => 1,
  'age' => 28,
])->execute();
 
// batchInsert()批量插入
$data10 = YII::$app->db->createCommand()->batchInsert('{{%user}}', ['name', 'sex', 'age'], [
  ['111', 1, 11],
  ['222', 1, 22],
])->execute();
 
// update()更新
$data11 = YII::$app->db->createCommand()->update('{{%user}}', [
  'name' => '1242143214'
], 'id=:id', ['id' => 10])->execute();
 
// delete()删除
$data12 = YII::$app->db->createCommand()->delete('{{%user}}', 'id=:id', ['id' => 11])->execute();
 
 
// 执行事务
$trans = YII::$app->db->beginTransaction();
 
try {
  YII::$app->db->createCommand()->update('{{%user}}', ['age' => 12], 'id=:id', ['id' => 13])->execute();
  YII::$app->db->createCommand()->update('{{%user}}', ['age' => 22], 'id=:id', ['id' => 14])->execute();
 
  $trans->commit();
} catch (\Exception $e) {
  //如果语句中有一个执行失败，那么就将回滚
  $trans->rollBack();
  throw $e;
}
 
// 获取表的定义信息
$info = YII::$app->db->getTableSchema('{{%user}}');