[TOC]
### select($columns)
~~~php
//不写select，默认是*
//SELECT * FROM `user`
$query->from('user');
//字符串形式
//SELECT `id`, `lying`.`sex`, count(id) AS `count`
$query->select('id, lying.sex, count(id) as count');
//如果需要对括号内的字段进行反引号处理
//SELECT `id`, `lying`.`sex`, count([[id]]) AS `count`
$query->select('id, lying.sex, count(`id`) as count');
//数组形式
//SELECT `id`, `lying`.`sex`, `username` AS `name`
$query->select(['id', 'lying.sex', 'name'=>'username']);
//使用到包含逗号的数据库表达式的时候，你必须使用数组的格式，以避免自动的错误的引号添加
//SELECT CONCAT(first_name, ' ', last_name) AS `full_name`, `email`
$query->select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']);
//或者
//SELECT CONCAT(first_name, ' ', last_name) AS `full_name`, `email`
$query->select(['full_name'=>"CONCAT(first_name, ' ', last_name)", 'email']);
//同理，如果需要对括号内的字段添加反引号，则
//SELECT CONCAT(`first_name`, ' ', `last_name`) AS `full_name`, `email`
$query->select(['full_name'=>"CONCAT([[first_name]], ' ', [[last_name]])", 'email']);
//使用子查询
//SELECT (SELECT count(`money`) from `pay` where` id`=1) AS `money`
$subquery = $db->query()->select('count([[money]])')->from('pay')->where(['id'=>1]);
$query->select('money'=>$subquery);
~~~
### distinct($distinct = true)
~~~php
//在查询字段的前面加上DISTINCT
//SELECT DISTINCT `id`, `username`
$query->select(['id', 'username'])->distinct();
//去掉DISTINCT
//SELECT `id`, `username`
$query->select(['id', 'username'])->distinct(false);
~~~
### from($tables)
~~~php
//字符串形式
//SELECT * FROM `user`, `lying`.`member` AS `member`
$query->from('user, lying.menber as member');
//使用表前缀
//SELECT * FROM `prefix_user`
$query->from('{{%user}}');
//使用数组，别名以及表前缀
//SELECT * FROM `prefix_table1`, `table2` AS `t2`
$query->from(['{{%table1}}', 't2'=>'table2']);
//使用子查询
//SELECT * FROM (SELECT `id`, `username` FROM `user` WHERE `id`=1) AS `t`
$subquery = $db->query()->select('id, username')->from('user')->where(['id'=>1]);
$query->from(['t'=>$subquery]);
~~~
### join($type, $table, $on = null, $params = [])
~~~php
//支持的join类型：'left join','right join','inner join'
//SELECT * FROM `user` LEFT JOIN `table` ON user.id=table.uid
$query->from('user')->join('left join', 'table', 'user.id=table.uid');
//关联的table请参考`from()`的用法，包括别名、子查询等
//SELECT * FROM `user` LEFT JOIN `table` AS `t` ON user.id=t.uid
$query->from('user')->join('left join', ['t'=>'table'], 'user.id=t.uid');
//子查询
//SELECT * FROM `user` LEFT JOIN (SELECT `uid`, `username` FROM `pay` WHERE `uid` = 1) AS `p` ON user.id=p.uid
$subquery = $db->query()->select('uid, username')->from('pay')->where(['uid'=>1]);
$query->from('user')->join('left join', ['p'=>$subquery ], 'user.id=p.uid');
//ON条件支持字符串形式和数组形式，如果要使用'字段1 = 字段2'的形式,请用字符串带入,用数组的话'字段2'将被解析为绑定参数
//字符串形式如果要给字段加上反引号，可以用[[字段]]形式
//字符串形式ON + 参数绑定（ON条件参见where()用法）
//SELECT * FROM `user` LEFT JOIN `table` AS `t` ON `user`.`id`=`t`.`uid` AND `t`.`id` < 100
$query->from('user')->join('left join', ['t'=>'table'], '[[user.id]]=[[t.uid]] and [[t.id]] < :tid', [':tid'=>100]);
//数组形式ON（ON条件参见where()用法）
//SELECT * FROM `user` LEFT JOIN `table` AS `t` ON `t`.`id` > 100
$query->from('user')->join('left join', ['t'=>'table'], ['>', 't.id', 100]);
//下面这种用法是错误的，因为`user.id`会被解析成字符串，而不是字段，除非你确定你要这么用
//SELECT * FROM `user` LEFT JOIN `table` AS `t` ON `t`.`id` = 'user.id'
$query->from('user')->join('left join', ['t'=>'table'], ['t.id'=>'user.id']);
//join支持多次调用哦
$query->from('user')->join('left join', ['t1'=>'table1'], '[[user.id]]=[[t1.uid]]');
->join('right join', ['t2'=>'table2'], '[[user.id]]=[[t2.uid]]');
~~~
### leftJoin($table, $on = null, $params = [])
~~~php
其实就是join的封装，type默认为left join
~~~
### rightJoin($table, $on = null, $params = [])
~~~php
其实就是join的封装，type默认为right join
~~~
### innerJoin($table, $on = null, $params = [])
~~~php
其实就是join的封装，type默认为inner join
~~~
### where($condition, $params = [])
> 字符串形式
~~~php
//WHERE id=1
$query->where('id=1 and sex > 0');
//WHERE `id`=1
$query->where('[[id]]=1'); //对字段名加上反引号
//WHERE `id`=1
$query->where('[[id]]=:id', [':id'=>1]); //参数绑定(推荐)
~~~
> 数组形式
数组形式的条件标准格式为：
~~~
[运算符, 操作1, 操作2, ...]
~~~
并且操作支持无限级嵌套：
~~~
[运算符, 操作1, [运算符1, 操作2, 操作3, ...], ...]
~~~
1. =
~~~php
//WHERE `id`=1
$where = ['=', 'id', 1];
$where = ['id'=>1]; //简写方式，等同于上面用法
$subquery = $db->query()->select(['MAX([[id]])'])->from(['user']); //SELECT MAX(`id`) FROM `user`
//WHERE `id` = (SELECT MAX(`id`) FROM `user`)
$where = ['=', 'id', $subquery]; //子查询
//WHERE `id` IN (SELECT MAX(`id`) FROM `user`)
$where = ['id'=>$subquery]; //子查询，简写方式会变成IN，这里要特别注意
~~~
2. AND
~~~php
//WHERE `id`=1 AND `sex`=2
$where = ['and', ['=', 'id', 1], ['=', 'sex', 2]];
$where = [['=', 'id', 1], ['=', 'sex', 2]]; //AND条件为默认值，可以省略，等同于上面用法
$where = ['id'=>1, 'sex'=>2]; //简写形式，等同于上面用法
~~~
3. OR
* 和`AND`用法一致，只不过`OR`运算符不能省略
4. IN
~~~php
//WHERE `id` IN (1, 2, 3)
$where = ['in', 'id', [1, 2, 3]];
$subquery = $db->query()->select(['id'])->from(['user']); //SELECT `id` FROM `user`
//WHERE `id` IN (SELECT `id` FROM `user`)
$where = ['in', 'id', $subquery];
$where = ['id'=>$subquery]; //简写方式，等同于上面用法
~~~
注意：错误用法 `$where = ['in', 'id', '(1, 2, 3)'];`
5. NOT IN
* 和`IN`用法一致，只不过不能使用简写方式
6. BETWEEN
~~~php
//WHERE `id` BETWEEN 1 AND 10
$where = ['between', 'id', [1, 10]];
~~~
7. NOT BETWEEN
* 和`BETWEEN`用法一致
8. LIKE
~~~php
//WHERE `name` LIKE '%lying%'
$where = ['like', 'name', '%lying%'];
~~~
9. NOT LIKE
* 和`LIKE`用法一致
10. IS NULL / IS NOT NULL
~~~php
//WHERE `sex` IS NULL
$where = ['null', 'sex', true];
$where = ['sex'=>null]; //简写方式，等同于上面用法
//WHERE `sex` IS NOT NULL
$where = ['null', 'sex', false];
~~~
11. EXISTS
~~~php
$subquery = $db->query()->select(['id'])->from(['user']);
//WHERE EXISTS (SELECT `id` FROM `user`)
$where = ['EXISTS', 'id', $subquery];
~~~
注意：错误用法 `$where = ['EXISTS', 'id', '(SELECT id FROM user)'];`
12. NOT EXISTS
* 和`EXISTS`用法一致
13. \>、<、>=、<= 等这边没有列出的标准DB操作符
~~~php
//WHERE id > 1
$where = ['>', 'id', 1];
//WHERE id <= 1
$where = ['<=', 'id', 1];
~~~
> 数组用法示例
~~~php
$subquery = $db->query()->select(['id'])->from(['user']);
//WHERE `sex` = 1 AND (`id` > 100 OR `time` IS NULL) AND `id` IN (SELECT `id` FROM `user`)
$where = ['sex'=>1, ['or', ['>', 'id', 100], 'time'=>null], 'id'=>$subquery];
~~~
### andWhere($condition, $params = [])
* 和`where()`用法一致，只不过是在`where()`的条件上再追加`AND`条件
~~~php
$query->where(['status' => 1]);
if (!empty($search)) {
$query->andWhere(['like', 'title', "%{$search}%"]);
}
~~~
### orWhere($condition, $params = [])
* 和`andWhere()`用法一致，只不过是在`where()`的条件上再追加`OR`条件
### groupBy($columns)
~~~php
//GROUP BY `id`, `sex`
$query->groupBy('id, sex');
$query->groupBy(['id', 'sex']); //等同于上面的用法
~~~
### having($condition, $params = [])
* 参见`where()`的用法
### andHaving($condition, $params = [])
* 参见`andWhere()`的用法
### orHaving($condition, $params = [])
* 参见`orWhere()`的用法
### orderBy($columns)
~~~php
//ORDER BY `id` ASC, `sex` DESC
$query->orderBy('id, sex desc');
$query->orderBy(['id', 'sex'=>SORT_DESC]); //等同于上面的用法
~~~
### limit($offset, $limit = null)
~~~php
//LIMIT 1
$query->limit(1);
//LIMIT 10, 20
$query->limit(10, 20);
~~~
### union(Query $query, $all = false)
~~~php
$query1 = $db->query()->from(['user1']);
$query2 = $db->query()->from(['user2']);
//SELECT * FROM `user1` UNION (SELECT * FROM `user2`)
$query1->union($query2);
//SELECT * FROM `user1` UNION ALL (SELECT * FROM `user2`)
$query1->union($query2, true);
~~~
- 序言
- 更新日志
- 安装
- 规范
- 常量
- 配置
- 自动加载
- MVC
- 模块
- 控制器
- 模型
- 视图
- php原生模板
- 模板引擎
- 变量输出
- 模板注释
- 模板继承
- 模板引用
- 流程控制
- 原样输出
- 服务组件
- Hook组件
- Request组件
- Router组件
- Cookie组件
- Encrypter组件
- Dispatch组件
- Response组件
- View组件
- Session组件
- Helper组件
- 数据分页
- 数据验证
- Logger组件
- Cache组件
- Redis组件
- Connection组件
- 执行sql语句
- 查询生成器
- 查询方法详解
- Schema
- Captcha组件
- CLI
- CLI工具
- 事件
- 类事件
- 实例事件
- 全局事件
- 助手函数
- 扩展
- 异常
- 部署
- Apache
- Nginx
- IIS
- 虚拟主机