[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
- 虛擬主機