yii2.0 curd操作数据写法
一、执行原生sql查询,创建yii\db\Command insert(),update(),delete()直接构建,相应的sql语句
查:
1.查询一条 \Yii::$app->db->createCommand('SELECT * FROM test')->queryOne(); 2.查询多条 \Yii::$app->db->createCommand('SELECT * FROM test')->queryAll(); 3.绑定单个防SQL注入参数 \Yii::$app->db->createCommand('SELECT * FROM test WHERE id=:id')->bindValue(':id',5)->queryOne(); 4.绑定多个防SQL注入参数 \Yii::$app->db->createCommand('SELECT * FROM test WHERE id=:id AND name=:name')->bindValues([':id'=>3,':name'=>'Tom'])->queryOne(); 5.统计查询 \Yii::$app->db->createCommand('SELECT COUNT("id") FROM test')->queryScalar();
增:
1.插入数据 \Yii::$app->db->createCommand()->insert('test',['name'=>'James','age'=>'22'])->execute(); 2.一次插入多行 \Yii::$app->db->createCommand()->batchInsert('test',['name','age'],[ ['Hong','33'], ['Wei','44'] ])->execute();
改:
修改数据 \Yii::$app->db->createCommand()->update('test',['name'=>'Li'],'id=:id and class=:class',[':id'=>1,':class'=>'E'])->execute();
删:
删除数据 \Yii::$app->db->createCommand()->delete('test','id>:id',[':id'=>6])->execute();
二、查询构建器,创建yii\db\Query
查:
1.查询一条 $row = (new \yii\db\Query()) ->select('*') ->from('test') ->where('id=:id', [':id'=>6]) ->one(); 2.查询多条 $rows = (new \yii\db\Query()) ->select('id,name') ->from('test') ->where(['>','id', 2]) ->andWhere('class=:class', [':class'=>'A']) ->all(); 3.统计查询 $count = (new \yii\db\Query()) ->select('count(id)') ->from('test') ->where('class=:class', [':class'=>'B']) ->count(); 4.关联查询 $join = (new \yii\db\Query()) ->from('student s') ->select('s.*,t.id t_id,t.name t_name') ->leftJoin('teacher t','s.class = t.class') ->where('t.id=:t_id', [':t_id' => '2']) ->offset(3) ->limit(2) ->all();
三、建模ORM
增:
插入数据 $add = new Test(); $add->name = 'Xing'; $add->class = 'B'; $add->save();
查:
1.查询一条 $row = Test::find()->where('id=:id',[':id'=>5])->asArray()->one(); 2.查询多条 $rows = Test::find()->where('id>:id and class=:class',[':id'=>12,':class'=>'C'])->asArray()->all(); 3.统计查询 $count = Test::find()->count();
改:
1.save()进行修改 $update = Test::find()->where('id=:id',[':id'=>6])->one(); $update->score = 100;//修改属性值 $update->save(); 2.方法updateAll() $updateAll = Test::updateAll(['name'=>'Ping'],'id=:id',[':id'=>10]);
删:
1.先查后删 $del = Test::find()->where('id=:id',[':id'=>6])->one(); $del->delete(); 2.直接删除 Test::deleteAll('id>:id', [':id' => 29]);