数据库+模型 - Query查询 ¶

• 作者：KK

• 发表日期：2016.12.15

• 更新日期：2017.03.16

  更换了大部分知识点的代码示例形式和描述

---

介绍 ¶

Yii提供了一个 yii\db\Query 让我们方便地进行数据查询，这是一系列数据库查询功能的封装，也就是 DAO（Data Access Object - 数据访问对象）。

这不是模型，而是一个查询器，基本兼容各种主流关系型数据库。

示例：

$user = (new \yii\db\Query())->select(['id', 'name'])->from('user')->one();
print_r($user);

和db->createCommand('select id,name from user limit 1')的区别就是：createCommand 是直接写一个SQL语句来创建命令的，但 Query 是根据参数和数据库类型生成不同了最终 SQL 语句，所以用Query会提升项目的数据库可迁移性，而且代码看起来也比较好阅读.

下面是一些常用的查询方式：

all 查询指定表的所有记录 ¶

$query = new \yii\db\Query();
// SELECT * FROM user
$users = $query->from('user')->all();

print_r($users);

就是实例化一个\yii\db\Query对象来执行查询，from方法用于指定表。

---

where 指定条件 ¶

$query = new \yii\db\Query();
// SELECT * FROM user WHERE status = 1
$users = $query->from('user')->where(['status' => 1])->all();

print_r($users);

其中这个where方法的条件写法将在后面陆续扩展增强演示，各种条件都能表达，除非我写漏。

---

one 查询单条记录 ¶

根据ID查一条记录

$query = new \yii\db\Query();
// SELECT * FROM user WHERE id = 1
$data = $query->from('test')->where(['id' => 1])->one();

print_r($data);

注意one方法不会limit 1，而只是在编程语言级别获取第1条，所以比较适合条件本身就只有唯一的情况。

提示：后面我的演示代码都不再new了，直接以$query这个变量作为查询构造器开始演示，以上代码只是方便你直接复制调试。

---

判断记录是否存在 ¶

$query = new \yii\db\Query();

//SELECT EXISTS(SELECT * FROM user WHERE type = 2)
$exists = $query->from('user')->where(['default_patient_id' => 2])->exists();

if($exists == true){
	//...
}

---

select 指定查询字段 ¶

// SELECT id,name FROM user
$query->select(['id', 'name'])->from('user')->all();

---

定义字段别名 ¶

查找年龄为50的用户的id，名字和邮箱

// SELECT id,email AS mail FROM user
$query->select(['id', 'mail' => 'email'])->from('user')->all();

其中注意第2个字段'mail' => 'email'，意思是查询email字段，但返回值时以mail作为key。

---

orderBy排序，limit限制筛选记录数 ¶

查找最新注册的一个用户

//SELECT * FROM user ORDER BY add_time DESC limit 1
$query->from('user')->orderBy(['add_time' => SORT_DESC])->limit(1)->one();

orderBy设定排序字段，以数组作参数，key是要排序的字段，排序方式是PHP自带常量SORT_ASC或SORT_DESC。

limit用于限制输出几条记录。

需要增加更多排序字段则增加更多 KEY => VALUE 的数组单元，比如orderBy(['id' => SORT_ASC, 'age' => SORT_DESC])。

注意 SORT_ASC 和 SORT_DESC 是PHP自带的常量。

---

获取SQL语句 ¶

$query = new \yii\db\Query();
$query->from('user')->where(['id' => 999]); //注意不要加all或one

echo $query->createCommand()->sql; //获取 参数化的 SQL原型
echo $query->createCommand()->rawSql; //最终会生成的SQL语句

在还没有执行all或one之前不会进行查询操作，此时再通过createCommand方法创建一个yii\db\Command对象，再访问这个对象的rawSql就能得到SQL语句。

\yii\db\Query只是一个查询命令的构造器，底层最终其实还是由createCommand创建一个yii\db\Command来执行的）。

---

获取执行过的语句 ¶

在获取执行过的SQL方面，我并没有在Yii里看到简单快速的方式来获取，大家平时调试比较需要，所以我继承yii\db\Connection封装了一个方法来获取，参考代码。

使用方法是：下载这个类的代码放到你的项目中，按照你的需求修改命名空间，调用示例。

echo Yii::$app->db->getLastSqls();获取上次执行的一条SQL语句。

echo Yii::$app->db->getLastSqls(3);获取上次执行的三条SQL语句。

echo Yii::$app->db->getLastSqls(2, 'from user');获取上两条包含'from user'关键字的SQL语句。

---

多个and条件 ¶

查找年龄为60的女性用户

// SELECT * FROM user WHERE age = 50 AND sex = 2
$query->from('user')->where([
	'age' => 50,
	'sex' => 2,	//假设是女性的类型标识
])->all();

和大多数框架一样，where的参数是数组，每一对KEY => VALUE就是一个AND条件单元了。

好了后面我的代码不执行one或all了，为了让大家方便地直接复制代码看到生成的SQL我都用createCommand()->rawSql了。

---

in条件 ¶

查找 ID 为 1,2,3 并且年龄为 30 的用户

// SELECT * FROM user WHERE age = 30 AND id in(1, 2, 3)
echo $query->from('user')->where([
	'age' => 30,
	'id' => [1, 2, 3],
])->createCommand()->rawSql;

要实现in条件语句则为这个字段的值传入数组即可。

---

count 统计 ¶

统计（年龄为50）的（男性 和 人妖）的用户数量假设sex的1=男，2=女，3=人妖

// SELECT count(*) FROM user WHERE type = 1
$query->from('user')->where(['type' => 1])->count();

---

大于小于条件比较+offset分页 ¶

以10个用户显示一页的话，查找出第2页未成年用户

// SELECT * FROM user WHERE age < 18 OFFSET 10 LIMIT 10
echo $query->from('user')->where(['<', 'age', 18])->offset(10)->limit(10)->createCommand()->rawSql;

这时候你发现where的条件的每一个元素都没有key了，第一个参数表示比较逻辑符，第二个是比较字段，第三个是比较的值。

---

大于等于，小于等于条件比较 ¶

查询所有成年人

// SELECT * FROM user WHERE age >= 18
echo $query->from('user')->where(['>=', 'age', 18])->createCommand()->rawSql;

---

like查询 ¶

// SELECT * FROM user WHERE name LIKE '%abc%'
echo $query->from('user')->where(['like', 'name', 'abc'])->createCommand()->rawSql;

// SELECT * FROM user WHERE name LIKE '%abc%' AND name LIKE '%xyz%'
echo $query->from('user')->where(['like', 'name', ['abc', 'xyz']])->createCommand()->rawSql;

// SELECT * FROM user WHERE name LIKE '%abc'
echo $query->from('user')->where(['like', 'name', '%abc'])->createCommand()->rawSql;

// SELECT * FROM user WHERE name LIKE 'abc%'
echo $query->from('user')->where(['like', 'name', 'abc%'])->createCommand()->rawSql;

// SELECT * FROM user WHERE name NOE LIKE '%abc%'
echo $query->from('user')->where(['not like', 'name', 'abc'])->createCommand()->rawSql;

---

between筛选和group by分组结果 ¶

查询各性别的未成年用户

// SELECT * FROM user WHERE age BETWEEN 1 AND 18 GROUP BY sex
echo $query->from('user')->where(['between', 'age', 1, 18])->groupBy(['sex']) ->createCommand()->rawSql;

要groupBy多个字段的话就是['sex', 'country']这样，继续增加数组元素就好了。

---

having二级筛选 ¶

查询某分类的文章发表数量超过100的用户

// SELECT count(user_id) as arc_count FROM article WHERE category = 33 HAVING arc_count > 100
$query->select(['arc_count' => 'count(user_id)'])->from('article')->where(['category' => 33])->having('arc_count > 100')->createCommand()->rawSql;

---

or逻辑条件构造 ¶

查询名字为lily或者性别为男的用户

// SELECT * FROM user WHERE name = 'abc' OR sex = 1
$name = 'lily';
echo $query->from('user')->where([
	'or',
	['name' => 'abc'],
	['sex' => 1],
])->createCommand()->rawSql;

之前where方法传数组是以键值对来表达key1=val1 and key2 = val2 这样的，但你要表达复杂逻辑关系时，数组的第一个元素必须先声明你是什么逻辑关系，比如or，再以第二个元素表达or左边的条件是什么，然后第三个元素表达or右边的条件是什么。

这里要注意第二和第三个参数，第一个是字符串，表达了逻辑关系，第二和第三个为什么是数组？很简单的，因为逻辑关系的左边和右边可能会有很多条件，比如。

(a=1 and b=2) or （右边那一块...)

所以左边和右边都可能是多个条件，意味着如果左右边都有多条件的话就应该这样写了:

where([
	'or',
	[
		//or左边的条件块
		'name' => 'abc',
		['<', 'age', 18],	//当不是 字段 = 值 的条件而是大于小于之类的比较时，请注意这里要用数组了，前面介绍过大于小于，between那些都可以这样用
		'xxx' => 6,
	],

	[
		//or右边的条件块
		'sex' => 1,
		'email' => 'abc@dd.com',
	],
])

---

and、or条件嵌套 ¶

查询用户表 （年龄为$age）并且（性别为女 或者 2014年注册） 的用户

// SELECT * FROM user WHERE age = 33 AND (sex = 2 OR add_time > '2014-01-01 00:00:00')
echo $query->from('user')->where([
	'and',
	['age' => 33],
	[
		'or',
		['sex' => 2],
		['>', 'add_time', '2014-01-01 00:00:00'],
	],
])->createCommand()->rawSql;

回到数组的条件传达方式，之前说过，where条件的数组第一维度，第一元素是逻辑关键字，第二元素是逻辑的左边条件，第三元素是逻辑的右边条件。

而如果右边条件又是一个多条件表达式，那么则用数组表达，这个数组还是第一元素是逻辑关键字，第二元素是左边逻辑，第三元素是你懂的逻辑。

那么反过来，逻辑左边条件是复合型条件的话又怎么写第二元素呢？

---

追加and条件 ¶

$title = 'xx';
$query->from('user')->where(['status' => 2]);
if($title){
	//重点
	$query->andWhere(['like', 'title', $title]);
}

//SELECT * FROM user WHERE status AND title LIKE '%xx%'
echo $query->createCommand()->rawSql;

这样后面andWhere的会跟前面where的组合成and逻辑，如果要换or那就用orWhere方法也可以。

---

追加or条件 ¶

和andWhere道理是一样的

$title = 'xx';
$query->from('user')->where(['status' => 2]);
if($title){
	//重点
	$query->orWhere(['like', 'title', $title]);
}

//SELECT * FROM user WHERE status OR title LIKE '%xx%'
echo $query->createCommand()->rawSql;

---

自动过滤空值条件 ¶

你曾经可能经常写这样的代码:

if($location){
	$where['location'] = $location;
}

这样的判断逻辑在很多程序里都有，yii提供了这样的办法:

$category = 0;
$location = '';

// SELECT * FROM user WHERE (category = 0 AND size = 33) AND name LEKE '%xx%'
echo $query->from('user')->filterWhere([
	'category_id' => $category,
	'location' => $location,
	'size' => 33,
])->andWhere(['like', 'name', 'xx'])->createCommand()->rawSql;

由于location是空字符串所以该字段的条件不会生成，只会生成其它非空字符串的条件。

不过如果还是很喜欢自己写SQL的话那请一定要做好参数绑定工作防注入！（参数绑定方法可以自己抽时间另外学习yii官方的教程）。

---

表别名、左联接查询以及联接表别名的使用 ¶

查询所有文章的标题和发布人的名称

// SELECT * a.title as title, u.name as username FROM article as a LEFT JOIN user as u ON u.id = a.user_id
echo $query->select([
	'title' => 'a.title',
	'username' => 'u.name',
])->from(['a' => 'article'])->leftJoin(['u' => 'user'], 'u.id = a.user_id')->createCommand()->rawSql;

---

更新积分+1或-1什么的 ¶

这个要靠 yii\db\Expression 来实现

$expression = new \yii\db\Expression('score + 9999');
echo $query->createCommand()
	->update('user', ['score' => $expression])
	->rawSql; // UPDATE `user` SET `score` = score + 9999;

---

批量查询 ->大数据维护处理或统计 ¶

（哈哈，说得好高大上~~~反正这数据就是大，数量多！)

有没有过这样的经历，某类数据运营一年半年后产生了上千万条记录，且不这么说，就是上百万条吧，然后这个数据的A字段是一个复合数据，里面保存了一些具体的信息，比如里面包含了10个别的地方的ID集，再然后某天的产品需求造成程序要根据这些ID集的数量来进行查询，那总不能count这个字段啊！因为它对于MYSQL而言就是一个字符串而已，要查询就要where这个数据的数量是否大于指定值来查询了，而且还要搞个索引来优化查询速度，没办法，那就要在别的专用查询表或这个表上加一个xxx_count这样的字段，再将所有现存数据的A字段ID集个数统计一下存入到xxx_count字段以便查询。

这是一种需求，别的还有比如说简单地就是查出所有 XXX 字段大于多少的用户，坑爹了有时候就算你分表了，在前面的需求和这个需求的情况下你都能遇到一个问题:PHP内存不足，不能一下子读出所有的记录来处理。试过了吧？那你的办法可能就是做一些分页查询控制，比如第一次查先查1000条，处理完再查第2000条…做完一次再刷新，做完一次再刷新…..。或者还有别的流程，反正就是要你很麻烦地去搭建代码……

Yii 提供了底层的分批查询处理，不用你再写这些大数据转换的非核心逻辑代码。下面引用官方文档的代码足矣:

use yii\db\Query;

$query = (new Query())->from('user') ->orderBy('id');
foreach($query->batch() as $users) {
    //这样会先查出100条记录放到$users里，在第二次for循环的时候再查第二百条，第三次就查第三百条…但关于这个100条如何控制数量变成1000条等，暂时未在文档中找到控制参数，然而这个问题不大，毕竟最终会遍历整个表。除了数据维护，其实前端要为用户统计一些数据的时候，也避免了先查出所有记录一齐遍历统计的麻烦，统计一批数据就丢弃一批，再查下一批
}

foreach ($query->each() as $user) {
	//用each时每次for循环都会查询下一条出来
}