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}}');

<?phpnamespace app\models;use yii\db\ActiveRecord;class MyUser extends ActiveRecord
{
public static function tableName()
{
return '{{%user}}';
}
}

// 查询一条记录
// asArray的作用是以数组形式返回结果
// find()会返回一个ActiveQuery对象，ActiveQuery对象继承自yii\db\Query，所以可以使用Query中的所有查询方法
$data1 = MyUser::find()->where(['id' => 1])->asArray()->one();// 查询多条记录
$data2 = MyUser::find()->where([])->orderBy('id desc')->asArray()->all();// 根据主键获取一条记录
$data3 = MyUser::findOne(3);// 根据主键获取多条记录
$data4 = MyUser::findAll([4, 5, 6, 7]);// 带条件查询多条记录
$data5 = MyUser::findAll(['sex' => 1]);// 运行原生SQL
$data6 = MyUser::findBySql('select * from {{%user}} where id=:id', [':id' => 8])->asArray()->one();// 添加数据
// 准备一个新的实例，然后设置对应字段属性，保存
$user = new MyUser();
$user->name = 'test';
$user->sex = 1;
$user->age = 22;
$user->save();// 修改数据
// 先获取一个对象，然后设置对应字段属性，保存
$upd = MyUser::findOne(10);
$upd->name = 'abcdefg';
$upd->save();// 修改多条数据
$data7 = MyUser::updateAll(['name' => 'update'], ['sex' => 1]);// 删除一条数据
$del = MyUser::findOne(15);
$del->delete();// 删除多条数据
$data8 = MyUser::deleteAll(['sex' => 0]);// 事务操作
$user2 = MyUser::findOne(22);
$user3 = MyUser::findOne(33);
$trans = MyUser::getDb()->beginTransaction();
try {
$user2->name = '2222';
$user3->name = '3333';
//修改数据
$user2->save();
$user3->save(); $trans->commit();
} catch (\Exception $e) {
$trans->rollBack();
throw $e;
}