Yii::$app->db
->createCommand()
->batchInsert($table, $columns, $rows)
->execute();
app()->db->createCommand()->update(Activity::tableName(),$this->params_data,'id='.$activity_id)->execute();
2、原生SQL操作：
//createCommand(执行原生的SQL语句)
$sql="SELECT u.account,i.* FROM sys_user as u left join user_info as i on u.id=i.user_id";
$rows=Yii::$app->db->createCommand($sql)->query();
foreach($rowsas$k=>$v){
echo$v['add_time'];
}
查询返回多行：
$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();
更新：
$command = $connection->createCommand('UPDATE post SET status=1 WHERE id=1');
$command->execute();
插入更新删除：
// INSERT
$connection->createCommand()->insert('user', [
'name'=>'Sam',
'age'=> 30,
])->execute();
// INSERT 一次插入多行
$connection->createCommand()->batchInsert('user', ['name','age'], [
['Tom', 30],
['Jane', 20],
['Linda', 25],
])->execute();
// UPDATE
$connection->createCommand()->update('user', ['status'=> 1], 'age > 30')->execute();
// DELETE
$connection->createCommand()->delete('user','status = 0')->execute();
事务：
//事务的基本结构(多表更新插入操作请使用事务处理)
$dbTrans= Yii::app()->db->beginTransaction();
try{
$post=newPost;
$post->'title'='Hello dodobook!!!';
if(!$post->save())thrownewException("Error Processing Request",1);
$dbTrans->commit();
//$this->_end(0,'添加成功!!!');
}catch(Exception$e){
$dbTrans->rollback();
//$this->_end($e->getCode(),$e->getMessage());
//事务eg
findOne()和findAll()传入一个参数时默认为主键，否则须以键值对表示
// 查询pk值为10的客户
$customer = Customer::findOne(10);
$customer = Customer::find()->where(['id' => 10])->one();
// 查询age为30，status为1的客户
$customer = Customer::findOne(['age' => 30, 'status' => 1]);
$customer = Customer::find()->where(['age' => 30, 'status' => 1])->one();
// 查询pk为10的所有客户
$customers = Customer::findAll(10);
$customers = Customer::find()->where(['id' => 10])->all();
// 查询pk值为10，11,12的客户
$customers = Customer::findAll([10, 11, 12]);
$customers = Customer::find()->where(['id' => [10, 11, 12]])->all();
// 查询年龄为30，状态值为1的所有客户
$customers = Customer::findAll(['age' => 30, 'status' => 1]);
$customers = Customer::find()->where(['age' => 30, 'status' => 1])->all();
$orders= Orders::find()->where([
'AND',
['pay_status'=>1],
['ship_status'=>0]
])->orWhere([
'AND',
['pay_status'=>0],
['payment'=>1],
['ship_status'=>0]
])->all();
多个条件使用
$customers = Customer::find()->where(['and',['id','in',$array],['id','between',1,10],['!=','id',666],['age' => 30, 'status' => 1]])->all();
$sql1 = 'insert into business_ip (gid, name, area, belongName, belongArea, destIPv4, created, updated) values ';
$sql2 = 'on duplicate key update name = values(name), area = values(area), belongName = values(belongName), belongArea = values(belongArea), destIPv4 = values(destIPv4), created = values(created), updated = values(updated)';
$values = '';
foreach ($data as $v) {
$values .= '('.$v['gid'].', "'.$v['name'].'", "'.$v['area'].'", "'.$v['belongName'].'", "'.$v['belongArea'].'", "'
.$v['ip'].'", "'.$v['created'].'", "'.$v['updated'].'"),';
}
$query = $sql1.substr($values,0,-1).$sql2.';';
Yii::$app->db->createCommand($query)->execute();
主要就是运用mysql的 on dumpicate key update
字段中需有unique索引的字段.
结合yii2 $db->batch()方法可大量处理数据,记录一下.
