# 快速入門(五):查詢語言
本章帶你領略`ThinkPHP5.0`的查詢語法,以及如何使用查詢構建器進行查詢操作,主要包括:
- - [查詢表達式](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#u67E5u8BE2u8868u8FBEu5F0F)
- [批量查詢](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#u6279u91CFu67E5u8BE2)
- [快捷查詢](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#u5FEBu6377u67E5u8BE2)
- [視圖查詢](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#u89C6u56FEu67E5u8BE2)
- [閉包查詢](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#u95EDu5305u67E5u8BE2)
- [使用Query對象](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#-query-)
- [獲取數值](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#u83B7u53D6u6570u503C)
- [獲取列數據](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#u83B7u53D6u5217u6570u636E)
- [聚合查詢](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#u805Au5408u67E5u8BE2)
- [字符串查詢](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#u5B57u7B26u4E32u67E5u8BE2)
- [時間(日期)查詢](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#--3)
- [分塊查詢](http://www.hmoore.net/thinkphp/thinkphp5_quickstart/147280#u5206u5757u67E5u8BE2)
> 本章查詢內容均配置了數據表前綴`think_`,因此統一使用`Db`類的`name`方法代替`table`方法進行舉例說明。
## 查詢表達式
最普通的查詢就是判斷某個字段是否等于某個值,例如,我們查詢`think_data`數據表中`id`等于1的數據,用法如下:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'id'</span>, <span class="hljs-number">1</span>)
->find();
dump(<span class="hljs-regexp">$result</span>);
```
```
新手注意,如果沒有使用`use`引入`Db`類的話 需要使用:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = \think\Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'id'</span>, <span class="hljs-number">1</span>)
->find();
dump(<span class="hljs-regexp">$result</span>);
```
```
> #### 提示:
>
> - - - - - -
>
> `find`方法用于查找滿足條件第一個記錄(即使你的查詢條件有多個符合的數據),如果查詢成功,返回的是一個一維數組,沒有滿足條件的話則默認返回`null`(也支持設置是否拋出異常)。
生成的SQL語句是:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-string">`id`</span> = <span class="hljs-number">1</span></span>
```
```
上述的查詢其實等同于:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'id'</span>, <span class="hljs-string">'='</span>, <span class="hljs-number">1</span>)
->find();
dump(<span class="hljs-regexp">$result</span>);
```
```
使用表達式查詢的時候,where方法的參數依次為:
> #### where( 字段名,條件表達式,查詢值 )
可以支持的查詢表達式包括如下:
表達式 含義 EQ、= 等于(=) NEQ、<> 不等于(<>) GT、> 大于(>) EGT、>= 大于等于(>=) LT、< 小于(<) ELT、<= 小于等于(<=) LIKE 模糊查詢 \[NOT\] BETWEEN (不在)區間查詢 \[NOT\] IN (不在)IN 查詢 \[NOT\] NULL 查詢字段是否(不)是NULL \[NOT\] EXISTS EXISTS查詢 EXP 表達式查詢,支持SQL語法> 其中條件表達式不區分大小寫
下面就來查詢`id`大于等于1的數據,使用如下代碼:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'id'</span>, <span class="hljs-string">'>='</span>, <span class="hljs-number">1</span>)
->limit(<span class="hljs-number">10</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
因為這里需要返回多條記錄,因此這里我們使用了`select`方法,并且使用`limit`方法限制了返回的最多記錄數。
> #### 提示:
>
> - - - - - -
>
> `select`方法用于查詢數據集,如果查詢成功,返回的是一個二維數組,如果沒有滿足條件的話則返回空數組(也支持設置是否需要拋出異常)。
生成的SQL語句是:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-string">`id`</span> >= <span class="hljs-number">1</span> <span class="hljs-keyword">LIMIT</span> <span class="hljs-number">10</span></span>
```
```
如果使用EXP條件表達式的話,表示后面是原生的SQL語句表達式,例如上面的查詢可以改成:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'id'</span>, <span class="hljs-string">'exp'</span>, <span class="hljs-string">'>= 1'</span>)
->limit(<span class="hljs-number">10</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
生成的SQL語句和前面是一樣的。
如果要查詢id的范圍,可以使用
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
<span class="hljs-comment">// id 是 1、2、3 其中的數字</span>
->where(<span class="hljs-string">'id'</span>, <span class="hljs-string">'in'</span>, [<span class="hljs-number">1</span>, <span class="hljs-number">2</span>, <span class="hljs-number">3</span>])
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
生成的SQL語句是:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-string">`id`</span> <span class="hljs-keyword">IN</span> (<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>) </span>
```
```
或者
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
<span class="hljs-comment">// id 在 5到8之間的</span>
->where(<span class="hljs-string">'id'</span>, <span class="hljs-string">'between'</span>, [<span class="hljs-number">5</span>, <span class="hljs-number">8</span>])
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
生成的SQL語句是:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-string">`id`</span> <span class="hljs-keyword">BETWEEN</span> <span class="hljs-number">5</span> <span class="hljs-keyword">AND</span> <span class="hljs-number">8</span> </span>
```
```
接下來,使用多個字段查詢:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
<span class="hljs-comment">// id 在 1到3之間的</span>
->where(<span class="hljs-string">'id'</span>, <span class="hljs-string">'between'</span>, [<span class="hljs-number">1</span>, <span class="hljs-number">3</span>])
<span class="hljs-comment">// name 中包含think</span>
->where(<span class="hljs-string">'name'</span>, <span class="hljs-string">'like'</span>, <span class="hljs-string">'%think%'</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
這樣生成的查詢語句為:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-string">`id`</span> <span class="hljs-keyword">BETWEEN</span> <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-number">3</span> <span class="hljs-keyword">AND</span> <span class="hljs-string">`name`</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'%think%'</span></span>
```
```
如果要查詢某個字段是否為`NULL`,可以使用:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'name'</span>, <span class="hljs-string">'null'</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
這樣生成的查詢語句為:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-string">`name`</span> <span class="hljs-keyword">IS</span> <span class="hljs-number">NULL</span></span>
```
```
## 批量查詢
我們可以使用一個方法完成多個查詢條件,例如上面的查詢可以改成:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->where([
<span class="hljs-string">'id'</span> => [<span class="hljs-string">'between'</span>, <span class="hljs-string">'1,3'</span>],
<span class="hljs-string">'name'</span> => [<span class="hljs-string">'like'</span>, <span class="hljs-string">'%think%'</span>],
])->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
這樣生成的查詢語句還是和之前一樣:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-string">`id`</span> <span class="hljs-keyword">BETWEEN</span> <span class="hljs-string">'1'</span> <span class="hljs-keyword">AND</span> <span class="hljs-string">'3'</span> <span class="hljs-keyword">AND</span> <span class="hljs-string">`name`</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'%think%'</span></span>
```
```
我們再來看一些復雜的用法,使用`OR`和`AND`混合條件查詢,例如:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
<span class="hljs-comment">// name 中包含think</span>
->where(<span class="hljs-string">'name'</span>, <span class="hljs-string">'like'</span>, <span class="hljs-string">'%think%'</span>)
->where(<span class="hljs-string">'id'</span>, [<span class="hljs-string">'in'</span>, [<span class="hljs-number">1</span>, <span class="hljs-number">2</span>, <span class="hljs-number">3</span>]], [<span class="hljs-string">'between'</span>, <span class="hljs-string">'5,8'</span>], <span class="hljs-string">'or'</span>)
->limit(<span class="hljs-number">10</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
或者使用批量方式:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->where([
<span class="hljs-string">'id'</span> => [[<span class="hljs-string">'in'</span>, [<span class="hljs-number">1</span>, <span class="hljs-number">2</span>, <span class="hljs-number">3</span>]], [<span class="hljs-string">'between'</span>, <span class="hljs-string">'5,8'</span>], <span class="hljs-string">'or'</span>],
<span class="hljs-string">'name'</span> => [<span class="hljs-string">'like'</span>, <span class="hljs-string">'%think%'</span>],
])->limit(<span class="hljs-number">10</span>)->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
生成的SQL語句為:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> ( <span class="hljs-string">`id`</span> <span class="hljs-keyword">IN</span> (<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>) <span class="hljs-keyword">or</span> <span class="hljs-string">`id`</span> <span class="hljs-keyword">BETWEEN</span> <span class="hljs-string">'5'</span> <span class="hljs-keyword">AND</span> <span class="hljs-string">'8'</span> ) <span class="hljs-keyword">AND</span> <span class="hljs-string">`name`</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'%think%'</span> <span class="hljs-keyword">LIMIT</span> <span class="hljs-number">10</span></span>
```
```
## 快捷查詢
如果你有多個字段需要使用相同的查詢條件,可以使用快捷查詢。例如,我們要查詢id和status都大于0的數據,可以使用:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'id&status'</span>, <span class="hljs-string">'>'</span>, <span class="hljs-number">0</span>)
->limit(<span class="hljs-number">10</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
生成的SQL語句為:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> ( <span class="hljs-string">`id`</span> > <span class="hljs-number">0</span> <span class="hljs-keyword">AND</span> <span class="hljs-string">`status`</span> > <span class="hljs-number">0</span> ) <span class="hljs-keyword">LIMIT</span> <span class="hljs-number">10</span></span>
```
```
也可以使用or方式查詢,例如:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'id|status'</span>, <span class="hljs-string">'>'</span>, <span class="hljs-number">0</span>)
->limit(<span class="hljs-number">10</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
生成的SQL語句為:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> ( <span class="hljs-string">`id`</span> > <span class="hljs-number">0</span> <span class="hljs-keyword">OR</span> <span class="hljs-string">`status`</span> > <span class="hljs-number">0</span> ) <span class="hljs-keyword">LIMIT</span> <span class="hljs-number">10</span></span>
```
```
## 視圖查詢
如果需要快捷查詢多個表的數據,可以使用視圖查詢,相當于在數據庫創建了一個視圖,但僅僅支持查詢操作,例如:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::view(<span class="hljs-string">'user'</span>,<span class="hljs-string">'id,name,status'</span>)
->view(<span class="hljs-string">'profile'</span>,[<span class="hljs-string">'name'</span>=><span class="hljs-string">'truename'</span>,<span class="hljs-string">'phone'</span>,<span class="hljs-string">'email'</span>],<span class="hljs-string">'profile.user_id=user.id'</span>)
->where(<span class="hljs-string">'status'</span>,<span class="hljs-number">1</span>)
->order(<span class="hljs-string">'id desc'</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
生成的SQL語句為:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT <span class="hljs-keyword">user</span>.<span class="hljs-keyword">id</span>,<span class="hljs-keyword">user</span>.<span class="hljs-keyword">name</span>,<span class="hljs-keyword">user</span>.<span class="hljs-keyword">status</span>,profile.<span class="hljs-keyword">name</span> <span class="hljs-keyword">AS</span> truename,profile.phone,profile.email <span class="hljs-keyword">FROM</span> think_user <span class="hljs-keyword">user</span> <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> think_profile profile <span class="hljs-keyword">ON</span> profile.user_id=<span class="hljs-keyword">user</span>.<span class="hljs-keyword">id</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">user</span>.<span class="hljs-keyword">status</span> = <span class="hljs-number">1</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">user</span>.<span class="hljs-keyword">id</span> <span class="hljs-keyword">desc</span></span>
```
```
## 閉包查詢
`find`和`select`方法可以直接使用閉包查詢:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)->select(<span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-number">(<span class="hljs-regexp">$query</span>)</span> </span>{
<span class="hljs-regexp">$query</span>->where(<span class="hljs-string">'name'</span>, <span class="hljs-string">'like'</span>, <span class="hljs-string">'%think%'</span>)
->where(<span class="hljs-string">'id'</span>, <span class="hljs-string">'in'</span>, <span class="hljs-string">'1,2,3'</span>)
->limit(<span class="hljs-number">10</span>);
});
dump(<span class="hljs-regexp">$result</span>);
```
```
生成的SQL語句是:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-string">`name`</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'%think%'</span> <span class="hljs-keyword">AND</span> <span class="hljs-string">`id`</span> <span class="hljs-keyword">IN</span> (<span class="hljs-string">'1'</span>,<span class="hljs-string">'2'</span>,<span class="hljs-string">'3'</span>) <span class="hljs-keyword">LIMIT</span> <span class="hljs-number">10</span></span>
```
```
## 使用Query對象
也可以事先封裝Query對象,并傳入select方法,例如:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$query</span> = <span class="hljs-keyword">new</span> \think\db\Query;
<span class="hljs-regexp">$query</span>->name(<span class="hljs-string">'city'</span>)->where(<span class="hljs-string">'name'</span>, <span class="hljs-string">'like'</span>, <span class="hljs-string">'%think%'</span>)
->where(<span class="hljs-string">'id'</span>, <span class="hljs-string">'in'</span>, <span class="hljs-string">'1,2,3'</span>)
->limit(<span class="hljs-number">10</span>);
<span class="hljs-regexp">$result</span> = Db::select(<span class="hljs-regexp">$query</span>);
dump(<span class="hljs-regexp">$result</span>);
```
```
使用`Query`對象的話,`select`方法之前調用的任何的鏈式操作都是無效。
## 獲取數值
如果僅僅是需要獲取某行表的某個值,可以使用`value`方法:
```
<pre class="calibre18">
```
<span class="hljs-comment">// 獲取id為8的data數據的name字段值</span><span class="hljs-regexp">$name</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'id'</span>, <span class="hljs-number">8</span>)
->value(<span class="hljs-string">'name'</span>);
dump(<span class="hljs-regexp">$name</span>);
```
```
name的結果為:`thinkphp`
## 獲取列數據
也支持獲取某個列的數據,使用`column`方法,例如:
```
<pre class="calibre18">
```
<span class="hljs-comment">// 獲取data表的name列</span><span class="hljs-regexp">$list</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'status'</span>, <span class="hljs-number">1</span>)
->column(<span class="hljs-string">'name'</span>);
dump(<span class="hljs-regexp">$list</span>);
```
```
返回的結果類似下面:
```
<pre class="calibre18">
```
<span class="hljs-keyword">array</span> (size=<span class="hljs-number">5</span>)
<span class="hljs-number">0</span> => string <span class="hljs-string">'thinkphp'</span><span class="hljs-number">1</span> => string <span class="hljs-string">'onethink'</span><span class="hljs-number">2</span> => string <span class="hljs-string">'topthink'</span><span class="hljs-number">3</span> => string <span class="hljs-string">'kancloud'</span>
```
```
如果希望返回以id為索引的name列數據,可以改成:
```
<pre class="calibre18">
```
<span class="hljs-comment">// 獲取data表的name列 并且以id為索引</span><span class="hljs-regexp">$list</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'status'</span>, <span class="hljs-number">1</span>)
->column(<span class="hljs-string">'name'</span>, <span class="hljs-string">'id'</span>);
dump(<span class="hljs-regexp">$list</span>);
```
```
返回的結果類似下面:
```
<pre class="calibre18">
```
<span class="hljs-keyword">array</span> (size=<span class="hljs-number">5</span>)
<span class="hljs-number">1</span> => string <span class="hljs-string">'thinkphp'</span><span class="hljs-number">2</span> => string <span class="hljs-string">'onethink'</span><span class="hljs-number">3</span> => string <span class="hljs-string">'topthink'</span><span class="hljs-number">4</span> => string <span class="hljs-string">'kancloud'</span>
```
```
如果需要返回以主鍵為索引的數據集,可以使用:
```
<pre class="calibre18">
```
<span class="hljs-comment">// 獲取data表的name列 并且以id為索引</span><span class="hljs-regexp">$list</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'status'</span>, <span class="hljs-number">1</span>)
->column(<span class="hljs-string">'*'</span>, <span class="hljs-string">'id'</span>);
dump(<span class="hljs-regexp">$list</span>);
```
```
返回的結果類似下面:
```
<pre class="calibre18">
```
<span class="hljs-keyword">array</span> (size=<span class="hljs-number">5</span>)
<span class="hljs-number">1</span> => <span class="hljs-keyword">array</span> (size=<span class="hljs-number">3</span>)
<span class="hljs-string">'id'</span> => int <span class="hljs-number">1</span><span class="hljs-string">'name'</span> => string <span class="hljs-string">'thinkphp'</span><span class="hljs-string">'status'</span>=> int <span class="hljs-number">1</span><span class="hljs-number">2</span> => <span class="hljs-keyword">array</span> (size=<span class="hljs-number">3</span>)
<span class="hljs-string">'id'</span> => int <span class="hljs-number">1</span><span class="hljs-string">'name'</span> => string <span class="hljs-string">'onethink'</span><span class="hljs-string">'status'</span>=> int <span class="hljs-number">1</span><span class="hljs-number">3</span> => <span class="hljs-keyword">array</span> (size=<span class="hljs-number">3</span>)
<span class="hljs-string">'id'</span> => int <span class="hljs-number">1</span><span class="hljs-string">'name'</span> => string <span class="hljs-string">'topthink'</span><span class="hljs-string">'status'</span>=> int <span class="hljs-number">1</span><span class="hljs-number">4</span> => <span class="hljs-keyword">array</span> (size=<span class="hljs-number">3</span>)
<span class="hljs-string">'id'</span> => int <span class="hljs-number">1</span><span class="hljs-string">'name'</span> => string <span class="hljs-string">'kancloud'</span><span class="hljs-string">'status'</span>=> int <span class="hljs-number">1</span>
```
```
## 聚合查詢
thinkphp為聚合查詢提供了更便捷的方法,如下:
```
<pre class="calibre18">
```
<span class="hljs-comment">// 統計data表的數據</span><span class="hljs-regexp">$count</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'status'</span>, <span class="hljs-number">1</span>)
->count();
dump(<span class="hljs-regexp">$count</span>);
<span class="hljs-comment">// 統計user表的最高分</span><span class="hljs-regexp">$max</span> = Db::name(<span class="hljs-string">'user'</span>)
->where(<span class="hljs-string">'status'</span>, <span class="hljs-number">1</span>)
->max(<span class="hljs-string">'score'</span>);
dump(<span class="hljs-regexp">$max</span>);
```
```
支持的聚合查詢方法包括:
方法 說明 參數 count 統計數量 統計的字段名(可選) max 獲取最大值 統計的字段名(必須) min 獲取最小值 統計的字段名(必須) avg 獲取平均值 統計的字段名(必須) sum 獲取總分 統計的字段名(必須)## 字符串查詢
在必要的時候,仍然可以使用原生的字符串查詢,但建議是**配合參數綁定**一起使用,可以避免注入問題,例如:
```
<pre class="calibre18">
```
<span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'id > :id AND name IS NOT NULL'</span>, [<span class="hljs-string">'id'</span> => <span class="hljs-number">10</span>])
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
可以直接在`where`方法中使用字符串查詢條件,并支持第二個參數傳入參數綁定,上面這個查詢生成的SQL語句是:
```
<pre class="calibre18">
```
<span class="hljs-operator">SELECT * <span class="hljs-keyword">FROM</span> <span class="hljs-string">`think_data`</span> <span class="hljs-keyword">WHERE</span> ( <span class="hljs-keyword">id</span> > <span class="hljs-string">'10'</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">IS</span> <span class="hljs-keyword">NOT</span> <span class="hljs-number">NULL</span> )</span>
```
```
## 時間(日期)查詢
> 首先需要在`think_data`數據表新增`create_time`字段,用于日期查詢的字段類型推薦使用`datetime`類型。
`ThinkPHP5.0`的查詢語言強化了對時間日期查詢的支持,例如:
```
<pre class="calibre18">
```
<span class="hljs-comment">// 查詢創建時間大于2016-1-1的數據</span><span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->whereTime(<span class="hljs-string">'create_time'</span>, <span class="hljs-string">'>'</span>, <span class="hljs-string">'2016-1-1'</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
<span class="hljs-comment">// 查詢本周添加的數據</span><span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->whereTime(<span class="hljs-string">'create_time'</span>, <span class="hljs-string">'>'</span>, <span class="hljs-string">'this week'</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
<span class="hljs-comment">// 查詢最近兩天添加的數據</span><span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->whereTime(<span class="hljs-string">'create_time'</span>, <span class="hljs-string">'>'</span>, <span class="hljs-string">'-2 days'</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
<span class="hljs-comment">// 查詢創建時間在2016-1-1~2016-7-1的數據</span><span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->whereTime(<span class="hljs-string">'create_time'</span>, <span class="hljs-string">'between'</span>, [<span class="hljs-string">'2016-1-1'</span>, <span class="hljs-string">'2016-7-1'</span>])
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
日期查詢對`create_time`字段類型沒有要求,可以是`int/string/timestamp/datetime/date`中的任何一種,系統會自動識別進行處理。
還可以使用下面的人性化日期查詢方式,例如:
```
<pre class="calibre18">
```
<span class="hljs-comment">// 獲取今天的數據</span><span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->whereTime(<span class="hljs-string">'create_time'</span>, <span class="hljs-string">'today'</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
<span class="hljs-comment">// 獲取昨天的數據</span><span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->whereTime(<span class="hljs-string">'create_time'</span>, <span class="hljs-string">'yesterday'</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
<span class="hljs-comment">// 獲取本周的數據</span><span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->whereTime(<span class="hljs-string">'create_time'</span>, <span class="hljs-string">'week'</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
<span class="hljs-comment">// 獲取上周的數據</span><span class="hljs-regexp">$result</span> = Db::name(<span class="hljs-string">'data'</span>)
->whereTime(<span class="hljs-string">'create_time'</span>, <span class="hljs-string">'last week'</span>)
->select();
dump(<span class="hljs-regexp">$result</span>);
```
```
## 分塊查詢
分塊查詢是為查詢大量數據的需要而設計的,假如`think_data`表已經有超過1萬條記錄,但是一次性取那么大的數據會導致內存開銷非常之大,但確實又有這個需要(例如查詢所有的數據并導出到`excel`),采用分塊查詢可以緩解這個問題。
使用分塊查詢,可以把1萬條記錄分成`100`次處理,每次處理`100`條記錄,代碼示例如下:
```
<pre class="calibre18">
```
Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'status'</span>, <span class="hljs-string">'>'</span>, <span class="hljs-number">0</span>)
->chunk(<span class="hljs-number">100</span>, <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-number">(<span class="hljs-regexp">$list</span>)</span> </span>{
<span class="hljs-comment">// 處理100條記錄</span><span class="hljs-keyword">foreach</span>(<span class="hljs-regexp">$list</span> <span class="hljs-keyword">as</span> <span class="hljs-regexp">$data</span>){
}
});
```
```
第二個參數可以是有效的`callback`類型,包括使用閉包函數。
系統會按照主鍵順序查詢,每次查詢`100`條,如果你不希望使用主鍵進行查詢,或者沒有主鍵的話,則需要指定查詢的排序字段(但必須是唯一的),例如:
```
<pre class="calibre18">
```
Db::name(<span class="hljs-string">'user'</span>)
->where(<span class="hljs-string">'status'</span>, <span class="hljs-string">'>'</span>, <span class="hljs-number">0</span>)
->chunk(<span class="hljs-number">100</span>, <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-number">(<span class="hljs-regexp">$list</span>)</span> </span>{
<span class="hljs-comment">// 處理100條記錄</span><span class="hljs-keyword">foreach</span>(<span class="hljs-regexp">$list</span> <span class="hljs-keyword">as</span> <span class="hljs-regexp">$data</span>){
}
}, <span class="hljs-string">'uid'</span>);
```
```
然后交給`callback`進行數據處理,處理完畢后繼續查詢下一個`100`條記錄,如果你需要在中途中斷后續的查詢,只需要在`callback`方法調用中返回`false`即可,例如:
```
<pre class="calibre42">
```
Db::name(<span class="hljs-string">'data'</span>)
->where(<span class="hljs-string">'status'</span>,<span class="hljs-string">'>'</span>,<span class="hljs-number">0</span>)
->chunk(<span class="hljs-number">100</span>,<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-number">(<span class="hljs-regexp">$list</span>)</span></span>{
<span class="hljs-keyword">foreach</span>(<span class="hljs-regexp">$list</span> <span class="hljs-keyword">as</span> <span class="hljs-regexp">$data</span>){
<span class="hljs-comment">// 返回false則中斷后續查詢</span><span class="hljs-keyword">return</span> <span class="hljs-keyword">false</span>;
}
});
```
```
- 脕茫隆壟脨貌脩脭
- 脕茫隆壟脨貌脩脭
- 脪祿隆壟祿霉麓隆
- 脪祿隆壟祿霉麓隆
- 露鎂隆壟URL潞脥脗路脫脡
- 露鎂隆壟URL潞脥脗路脫脡
- 脠媒隆壟脟毛脟貿潞脥脧矛脫婁
- 脠媒隆壟脟毛脟貿潞脥脧矛脫婁
- 脣脛隆壟脢媒戮脻驢芒
- 脣脛隆壟脢媒戮脻驢芒
- 脦氓隆壟虜茅脩爐脫茂脩脭
- 脦氓隆壟虜茅脩爐脫茂脩脭
- 脕霉隆壟脛攏脨脥潞脥鹿脴脕陋
- 攏簍1攏漏脛攏脨脥露簍脪氓
- 攏簍2攏漏祿霉麓隆虜脵脳梅
- 攏簍3攏漏露脕脠隆脝梅潞脥脨脼賂脛脝梅
- 攏簍4攏漏脌脿脨脥脳陋祿祿潞脥脳脭露爐脥錨魯脡
- 攏簍5攏漏虜茅脩爐路露脦摟
- 攏簍6攏漏脢盲脠毛潞脥脩茅脰隴
- 攏簍7攏漏鹿脴脕陋
- 攏簍8攏漏脛攏脨脥脢盲魯枚
- 脝脽隆壟脢脫脥錄潞脥脛攏擄氓
- 脝脽隆壟脢脫脥錄潞脥脛攏擄氓
- 擄脣隆壟碌梅脢脭潞脥脠脮脰戮
- 擄脣隆壟碌梅脢脭潞脥脠脮脰戮
- 戮脜隆壟API驢陋路壟
- 戮脜隆壟API驢陋路壟
- 脢廬隆壟脙眉脕卯脨脨鹿隴戮脽
- 脢廬隆壟脙眉脕卯脨脨鹿隴戮脽
- 脢廬脪祿隆壟脌漏脮鹿
- 脢廬脪祿隆壟脌漏脮鹿
- 脢廬露鎂隆壟脭脫脧卯
- Cookie
- Session
- 碌樓脭陋虜芒脢脭
- 脥錄脧帽麓婁脌鉚
- 脦脛錄鎂脡脧麓蘆
- 脩茅脰隴脗毛
- 賂陸脗錄
- A隆壟魯攏錄沒脦脢脤芒錄爐
- B隆壟3.2潞脥5.0脟酶鹵冒
- C隆壟脰煤脢脰潞爐脢媒
- 路盧脥芒脝陋攏潞脩摟脧擄ThinkPHP5碌脛脮媒脠路脳脣脢脝
- 路盧脥芒脝陋攏潞脩摟脧擄ThinkPHP5碌脛脮媒脠路脳脣脢脝