数据库操作增删改查Yii2为我们提供了非常丰富的查询详细的可以到官方文档处查看浏览

User::find()->all();	//此方法返回所有数据；

User::find()->one();	//此方法返回一条数据；

User::find()->count();		//此方法返回记录的数量；

User::find()->average();	//此方法返回指定列的平均值；

User::find()->min();		//此方法返回指定列的最小值 ；

User::find()->max();		//此方法返回指定列的最大值 ；

User::find()->scalar();		//此方法返回值的第一行第一列的查询结果；

User::find()->column();		//此方法返回查询结果中的第一列的值；

User::find()->exists();		//此方法返回一个值指示是否包含查询结果的数据行；

User::find()->batch(10);	//每次取 10 条数据 

User::find()->each(10);		//每次取 10 条数据， 迭代查询


User::findOne($id);		//此方法返回 主键 id=1  的一条数据(举个例子)； 

User::find()->where(['name' => 'dodobook'])->one();	//此方法返回 ['name' => 'dodobook'] 的一条数据；

User::find()->where(['name' => 'dodobook'])->all();	//此方法返回 ['name' => 'dodobook'] 的所有数据；

User::find()->select('id,name')->where("status=1")->all();	//此方法为Yii2 查询指定字段

User::find()->orderBy('id DESC')->all();	//此方法是排序查询；

User::findBySql('SELECT * FROM user')->all();	//此方法是用 sql  语句查询 user 表里面的所有数据；

User::findBySql('SELECT * FROM user')->one();	//此方法是用 sql  语句查询 user 表里面的一条数据；

User::find()->andWhere(['sex' => '男', 'age' => '24'])->count('id');	//统计符合条件的总条数；

//推荐使用asArray()这个函数，这样不仅会方便我们在Views中的输出，也在一定程度上节约了内存
$posts = Post::find()->asArray()->all();


//LIKE查询
MemberList::find()->andFilterWhere(['like','name','新']);

//orderby 查询
MemberList::find()->OrderBy(['date'=>SORT_DESC]);

//按主键id查询
MemberList::findOne($id);

// 多条件查询
MemberList::find()->where(['name'=>'小洋'])->andWhere(['age'=>24])->all();   //查询返回全部
// ->one()  查询1个结果    
// ->count()  获取数量

//多表联查
$model=new MemberList();
$model->fiind()->join('LEFT JOIN','student','student.cid=customer.id')
            ->where('student.id'=>\Yii::$app->user->id)
            ->andwhere('is_ok=1')
            ->one();



//修改
MemberList::updateAll(['age'=>24],['age'=>18]);   //将所有年龄为24的 修改为年龄 18


//删除
$model = GoodsList::findOne(23);
$model->delete();

MemberList::deleteAll('age > :age AND sex = :sex', [':age' => 20, ':sex' => '1']);

GoodsList::deleteAll(" status='2'");

一些常用的语句,请按照需要修改(SELECT\FROM\WHERE\ADD WHERE\FILTER WHERE\ORDER BY\GROUP BY\HAVING\LIMIT OR OFFSET\JOIN\UNION\QUERY METHODS\QUERY RESULTS\INDEXING...)

$rows = (new \yii\db\Query())
	->select(['dyn_id', 'dyn_name'])
	->from('zs_dynasty')
	->where(['between','dyn_id', 1,30])
	->limit(10)
	->all();



use yii\db\Query;
$query = (new Query())->from('user') ->orderBy('id');
	

$query->select('*')->
select('dyn_id as id, dynasty.dyn_name')->
$query->select(['dyn_id as id', "CONCAT(dyn_name,'a')"])->
$query->select('user_id')->distinct()->


$query->from('user');
$query->from(['public.user u', 'public.post p']);
$query->from('public.user u, public.post p');
$query->from(['u' => 'public.user', 'p' => 'public.post']);

$subQuery = (new Query())->select('id')->from('user')->where('status=1');
// SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u 
$query->from(['u' => $subQuery]);



where('status=1')->
where('status=:status', [':status' => $status])->
where(['status' => 10,'type' => null,'id' => [4, 8, 15],])->

$userQuery = (new Query())->select('id')->from('user');
// ...WHERE `id` IN (SELECT `id` FROM `user`)
$query->...->where(['id' => $userQuery])->...

['and', 'id=1', 'id=2']			//id=1 AND id=2
['and', 'type=1', ['or', 'id=1', 'id=2']]		//type=1 AND (id=1 OR id=2)
['between', 'id', 1, 10]		//id BETWEEN 1 AND 10
['not between', 'id', 1, 10]	//not id BETWEEN 1 AND 10
['in', 'id', [1, 2, 3]]			//id IN (1, 2, 3)
['not in', 'id', [1, 2, 3]]		//not id IN (1, 2, 3)
['like', 'name', 'tester']		//name LIKE '%tester%'
['like', 'name', ['test', 'sample']]		//name LIKE '%test%' AND name LIKE '%sample%'
['not like', 'name', ['or', 'test', 'sample']]		//not name LIKE '%test%' OR not name LIKE '%sample%'
['exists','id', $userQuery]		//EXISTS (sub-query) | not exists
['>', 'age', 10]				//age>10




$status = 10;
$search = 'yii';
$query->where(['status' => $status]);
if (!empty($search)) {
    $query->andWhere(['like', 'title', $search]);
}
//WHERE (`status` = 10) AND (`title` LIKE '%yii%')
//andWhere() or orWhere()



$query->filterWhere(['username' => $username,'email' => $email,]);
//如果email为空,则 WHERE username=:username



$query->orderBy([ 'id' => SORT_ASC,'name' => SORT_DESC,]);
//orderBy , addOrderBy



$query->groupBy('id, status');
$query->addGroupBy(['created_at', 'updated_at']);



$query->having(['status' => $status]);
//having,andHaving,orHaving



$query->limit(10);
$query->offset(10);



$query->select(['user.name AS author', 'post.title as title'])
    ->from('user')
    ->leftJoin('post', 'post.user_id = user.id');
$query->join('FULL OUTER JOIN', 'post', 'post.user_id = user.id');
$query->leftJoin(['u' => $subQuery], 'u.id=author_id');



$query = new Query();
$query->select("id, category_id as type, name")->from('post')->limit(10);
$anotherQuery = new Query();
$anotherQuery->select('id, type, name')->from('user')->limit(10);
$query->union($anotherQuery);



$count = (new \yii\db\Query())->from('user')->where(['last_name' => 'dodo'])->count();
//SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name



$command = (new \yii\db\Query())->select(['id', 'email'])->from('user')->where(['last_name' => 'dodo'])->limit(10)->createCommand();
// show the SQL statement
echo $command->sql;
// show the parameters to be bound
print_r($command->params);



// returns all rows of the query result
$rows = $command->queryAll();



use yii\db\Query;
$query = (new Query())->from('user')->indexBy('username');
foreach ($query->batch() as $users) {
    // $users is indexed by the "username" column
}
foreach ($query->each() as $username => $user) {
}



use yii\db\Query;
$query = (new Query())->from('user')->orderBy('id');
foreach ($query->batch() as $users) {
    // batch( $batchSize = 10, $db = null )
    // 一个批次取100行
}
foreach ($query->each() as $user) {
    // 一行一行取
}

心有猛虎,细嗅蔷薇~~