<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                ThinkChat2.0新版上線,更智能更精彩,支持會話、畫圖、視頻、閱讀、搜索等,送10W Token,即刻開啟你的AI之旅 廣告
                # 快速入門(五):查詢語言 本章帶你領略`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>; } }); ``` ```
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看