thinkphp的数据库操作(上)
生活随笔
收集整理的這篇文章主要介紹了
thinkphp的数据库操作(上)
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
如果想了解更多相關知識，可以前往我的個人博客看看：eyes++的個人空間
一：連接數據庫與模型初探
ThinkPHP 采用內置抽象層將不同的數據庫操作進行封裝處理，數據抽象層基于 PDO 模式，無須針對不同的數據庫編寫相應的代碼。使用數據庫的第一步，就是連接數據庫，在根目錄的 config 下的 database.php 可以設置數據庫連接信息，大部分系統已經給了默認值，你只需要修改和填寫需要的值即可。
其中default配置用于設置默認使用的數據庫連接配置。connections配置具體的數據庫連接信息，default配置參數定義的連接配置必須要存在。
| mysql | MySQL |
| sqlite | SqLite |
| pgsql | PostgreSQL |
| sqlsrv | SqlServer |
| mongo | MongoDb |
| oracle | Oracle |
下面是默認支持的數據庫連接信息：
| type | 數據庫類型 | 無 |
| hostname | 數據庫地址 | 127.0.0.1 |
| database | 數據庫名稱 | 無 |
| username | 數據庫用戶名 | 無 |
| password | 數據庫密碼 | 無 |
| hostport | 數據庫端口號 | 無 |
| dsn | 數據庫連接dsn信息 | 無 |
| params | 數據庫連接參數 | 空 |
| charset | 數據庫編碼 | utf8 |
| prefix | 數據庫的表前綴 | 無 |
| deploy | 數據庫部署方式:0 集中式(單一服務器),1 分布式(主從服務器) | 0 |
| rw_separate | 數據庫讀寫是否分離 主從式有效 | false |
| master_num | 讀寫分離后 主服務器數量 | 1 |
| slave_no | 指定從服務器序號 | 無 |
| fields_strict | 是否嚴格檢查字段是否存在 | true |
| fields_cache | 是否開啟字段緩存 | false |
| schema_cache_path | 字段緩存目錄 | 無 |
| trigger_sql | 是否開啟SQL監聽 | true |
| auto_timestamp | 自動寫入時間戳字段 | false |
| query | 指定查詢對象 | think\db\Query |
connect方法必須在查詢的最開始調用，而且必須緊跟著調用查詢方法，否則可能會導致部分查詢失效或者依然使用默認的數據庫連接。且動態連接數據庫的connect方法僅對當次查詢有效。這種方式的動態連接和切換數據庫比較方便，經常用于多數據庫連接的應用需求。
對于本地測試，會優先采用.env 的配置信息，可以通過刪除改變.env 的配置，或刪除.env 來驗證 database 的執行優先級，操作中我們和 database 配置對應上即可：
現在來測試有沒有成功連接：
二：查詢構造器(基礎)
在學習查詢之前可以先了解一下一個新方法：Db::getLastSql()，該方法可以返回最近一條SQL查詢的原生語句。我在這里的演示是使用了我的myemployees庫內容如下：
1) 查詢數據
查詢單個數據
查詢單個數據一般使用find方法。如果沒有任何的查詢條件，并且也沒有調用order方法的話 ，find查詢不會返回任何結果。
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){$result = json(Db::table('employees')->where('employee_id', 100)->find());return Db::getLastSql() . '<br>' . $result;} }find方法查詢結果不存在，返回 null，否則返回結果數組，如果希望查詢數據不存在的時候返回空數組，可以如下修改：
$result = Db::table('employees')->where('employee_id', 99)->findOrEmpty();如果希望在沒有找到數據后拋出異常可以如下使用，如果沒有查找到數據，則會拋出一個think\db\exception\DataNotFoundException異常。
$result = Db::table('employees')->where('employee_id', 99)->findOrFail();查詢數據集
查詢多個數據（數據集）使用select方法：
select 方法查詢結果是一個數據集對象，如果需要轉換為數組可以如下使用，但一般DB操作返回是默認是數組，這種情況下調用toArray()會報錯，別問我是怎么知道的。。。。
如果希望在沒有查找到數據后拋出異常可以如下使用，如果沒有查找到數據，同樣也會拋出一個think\db\exception\DataNotFoundException異常
$result = Db::table('employees')->where('employee_id', 102)->select()->selectOrFail();如果設置了數據表前綴參數的話，可以使用name替代table，如果表名為"tp_user"，然后在數據庫設置那里添加了表前綴"tp_"，那么查詢時可以用name('user')替代table('tp_user')。如果你的數據表沒有設置表前綴的話，那么name和table方法效果一致。
值和列查詢
查詢某個字段的值可以用value()，如果不存在則返回null，如果有多個結果則只返回第一個。
查詢某一列的值可以用column()，第一個參數是返回的值，第二個參數的值作為索引。
數據分批處理
如果處理的數據量巨大，成百上千那種，一次性讀取有可能會導致內存開銷過大，為了避免內存處理太多數據出錯，可以使用 chunk()方法分批處理數據，該方法一次獲取結果集的一小塊，然后填充每一小塊數據到要處理的閉包，該方法在編寫處理大量數據庫記錄的時候非常有用。比如，我們可以全部員工表數據進行分批處理，每次處理 3 個員工記錄，先展示chunk分批次處理的特性。
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){// function傳入的$data代表所有數據$result = Db::table('employees')->chunk(3, function ($data){foreach ($data as $data) {dump($data);}echo 1;});} }如果要給員工計算年薪，那就是 月薪*12*(1+獎金率)，那樣就可以通過如下方法計算了：
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){// function傳入的$data代表所有數據$result = Db::table('employees')->chunk(3, function ($data){foreach ($data as $data) {if (isset($data['commission_pct'])) {echo $data['employee_id'] . ' ' . (12*$data['salary']*(1+$data['commission_pct'])) . "<br>";} else {echo $data['employee_id'] . ' ' . (12*$data['salary']) . "<br>";}}echo 1 . "<br>";});} }chunk方法的處理默認是根據主鍵查詢，支持指定字段，并且支持指定處理數據的順序。
Db::table('think_user')->chunk(100, function($users) {// 處理結果集...return false; },'create_time', 'desc');游標查詢
可以利用游標查詢功能，可以大幅度減少海量數據的內存開銷，它利用了 PHP 生 成器特性。每次查詢只讀一行，然后再讀取時，自動定位到下一行繼續讀取，cursor方法返回的是一個生成器對象。
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){$result = Db::table('employees')->cursor();foreach ($result as $e){echo $e['last_name'] . "<br>";};} }2) 添加數據
單數據新增
單數據新增一般使用insert()方法插入，如果新增成功會返回一個1，沒有指定的列的值默認為null。
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){$data = ['employee_id' => '99','first_name' => 'eyes','last_name' => '++'];return Db::table('employees')->insert($data);} }如果你添加一個不存在的字段數據，會拋出一個異常 Exception，如果想強行新增拋棄不存在的字段數據，則使用 strick(false)方法，忽略異常：
return Db::table('employees')->strict(false)->insert($data);我采用的數據庫是 mysql，可以支持 replace 寫入，insert 和 replace 寫入的區別，前者遇到表中存在主鍵相同則報錯，后者則修改。另外，使用 insertGetId()方法，可以在新增成功后返回當前數據 ID
批量數據新增
使用 insertAll()方法，可以添加二維數組批量新增數據，但要保持數組結構一致，成功則返回增加的行數
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){$data =[['employee_id' => '98','first_name' => '虛哥','last_name' => 'xx'],['employee_id' => '97','first_name' => '雞哥','last_name' => 'jj']];return Db::table('employees')->insertAll($data);} }
如果是mysql數據庫則可以使用replay()批量添加或修改：
如果批量插入的數據比較多，可以指定分批插入，使用limit方法指定每次插入的數量限制:
//分批次寫入，每次最多100條數據 Db::table('employees')->replay()->limit(100)->insertAll($data);save()新增
save()方法是一個通用方法，可以自行判斷是新增還是修改(更新)數據，判斷的依據是是否存在主鍵，不存在即新增。用法如insert，此處略。
3) 更新數據
更新數據可以使用save()方法或者update()方法，如果修改數據包括了主鍵信息，則可以省略where條件
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){$data =['first_name' => '東哥','last_name' => 'hh'];return Db::table('employees')->where('employee_id', 97)->save($data); // 支持使用data方法傳入要更新的數據，如果update方法和data方法同時傳入更新數據，則以update方法為準。 // return Db::table('employees') // ->where('employee_id', 97) // ->data($data) // ->save();} }如果要更新的數據需要使用SQL函數或者其它字段，可以使用下面的方式:
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){return Db::table('employees')->where('employee_id', 99)->exp('first_name', 'UPPER(first_name)')->update();} }
可以使用inc/dec方法自增或自減一個字段的值（ 如不加第二個參數，默認步長為1）。
4) 刪除數據
- 極簡刪除可以根據主鍵直接刪除，刪除成功返回影響行數，否則 0；
Db::table('employees')->delete(100); - 根據主鍵，還可以刪除多條記錄；
Db::table('employees')->delete([97,98,99]); - 正常情況下，通過 where()方法來刪除；
Db::table('employees')->where('id', 100)->delete(); - 通過 true 參數刪除數據表所有數據
Db::table('employees')->delete(true);
一般情況下，業務數據不建議真實刪除數據，系統提供了軟刪除機制（模型中使用軟刪除更為方便），useSoftDelete方法表示使用軟刪除，并且指定軟刪除字段為delete_time，寫入數據為當前的時間戳。。
// 軟刪除數據 使用delete_time字段標記刪除 Db::table('employees')->where('id', 100)->useSoftDelete('delete_time',time())->delete();5) 查詢表達式
查詢表達式支持大部分的SQL查詢語法，也是ThinkPHP查詢語言的精髓，查詢表達式的使用格式：where('字段名','查詢表達式','查詢條件')。除了where方法外，還可以支持whereOr，用法是一樣的。為了更加方便查詢，大多數的查詢表達式都提供了快捷查詢方法。
| = | 等于 | |
| <> | 不等于 | |
| > | 大于 | |
| >= | 大于等于 | |
| < | 小于 | |
| <= | 小于等于 | |
| [NOT] LIKE | 模糊查詢 | whereLike/whereNotLike |
| [NOT] BETWEEN | （不在）區間查詢 | whereBetween/whereNotBetween |
| [NOT] IN | （不在）IN 查詢 | whereIn/whereNotIn |
| [NOT] NULL | 查詢字段是否（不）是NULL | whereNull/whereNotNull |
| [NOT] EXISTS | EXISTS查詢 | whereExists/whereNotExists |
| [NOT] REGEXP | 正則（不）匹配查詢（僅支持Mysql） | |
| [NOT] BETWEEN TIME | 時間區間比較 | whereBetweenTime |
| > TIME | 大于某個時間 | whereTime |
| < TIME | 小于某個時間 | whereTime |
| >= TIME | 大于等于某個時間 | whereTime |
| <= TIME | 小于等于某個時間 | whereTime |
| EXP | 表達式查詢，支持SQL語法 | whereExp |
| find in set | FIND_IN_SET查詢 | whereFindInSet |
比較查詢
- 查詢表達式支持大部分常用的 SQL 語句，語法格式如下：
where('字段名','查詢表達式','查詢條件'); - 在查詢數據進行篩選時，我們采用 where()方法，比如 id=80；
Db::name('user')->where('id', 80)->find();
Db::name('user')->where('id','=',80)->find(); - 使用<>、>、<、>=、<=可以篩選出各種符合比較值的數據列表； Db::name('user')->where('id','>',80)->select();
區間查詢
- 使用 like 表達式進行模糊查詢；
Db::name('user')->where('email','like','xiao%')->select(); - like 表達式還可以支持數組傳遞進行模糊查詢；
Db::name('user')->where('email','like',['xiao%','wu%'], 'or')->select();
SELECT * FROM tp_user WHERE (email LIKE xiao% OR email LIKE 'wu%') - like 表達式具有兩個快捷方式 whereLike()和 whereNoLike()； Db::name('user')->whereLike('email','xiao%')->select();
Db::name('user')->whereNotLike('email','xiao%')->select(); - between 表達式具有兩個快捷方式 whereBetween()和 whereNotBetween()； Db::name('user')->where('id','between','19,25')->select();
Db::name('user')->where('id','between',[19, 25])->select();
Db::name('user')->whereBetween('id','19,25')->select(); Db::name('user')->whereNotBetween('id','19,25')->select(); - in 表達式具有兩個快捷方式 whereIn()和 whereNotIn()；
Db::name('user')->where('id','in', '19,21,29')->select();
Db::name('user')->where('id','in', [19, 21, 29])->select();
Db::name('user')->whereIn('id','19,21,29')->select();
Db::name('user')->whereNotIn('id','19,21,29')->select(); - null 表達式具有兩個快捷方式 whereNull()和 whereNotNull()； Db::name('user')->where('uid','null')->select();
Db::name('user')->where('uid','not null')->select();
Db::name('user')->whereNull('uid')->select();
Db::name('user')->whereNotNull('uid')->select();
EXP查詢
EXP表達式支持更復雜的查詢：
Db::name('user')->where('id','in','1,3,8')->select();可以改成：
Db::name('user')->where('id','exp',' IN (1,3,8) ')->select();exp查詢的條件不會被當成字符串，所以后面的查詢條件可以使用任何SQL支持的語法，包括使用函數和字段名稱。因此推薦使用whereExp方法查詢。
Db::name('user')->whereExp('id', 'IN (1,3,8) ')->select();三：查詢構造器(鏈式)
1)．查詢規則
Db::name('user')->where('id', 27)->order('id', 'desc')->find()
2)．更多查詢
$userQuery = Db::name('user');
$dataFind = $userQuery->where('id', 27)->find();
$dataSelect = $userQuery->select();
$data1 = $userQuery->order('id', 'desc')->select();
$data2 = $userQuery->select();
return Db::getLastSql();
SELECT * FROM tp_user ORDER BY id DESC
3). 鏈式查詢方法
where
- 表達式查詢，即where()方法的基礎查詢方式
- 關聯數組查詢，通過鍵值對來數組鍵值對匹配的查詢方式
- 索引數組查詢，通過數組里的數組拼裝方式來查詢
- 將復雜的數組組裝后，通過變量傳遞，將增加可讀性
- 字符串形式傳遞，簡單粗暴的查詢方式，whereRaw()支持復雜字符串格式，也支持SQL預處理模式
field
- 使用 field()方法，可以指定要查詢的字段
- 使用 field()方法，給指定的字段設置別名
- 在 fieldRaw()方法里，可以直接給字段設置 MySQL 函數
- 使用 field(true)的布爾參數，可以顯式的查詢獲取所有字段，而不是*
- 使用 withoutField()方法中字段排除，可以屏蔽掉想要不顯示的字段
- 使用 field()方法在新增時，驗證字段的合法性
alias
使用 alias()方法，給數據庫起一個別名:
limit
- 使用 limit()方法，限制獲取輸出數據的個數
- 分頁模式，即傳遞兩個參數，比如從第 3 條開始顯示 5 條 limit(2,5)
page
page()分頁方法，優化了 limit()方法，無須計算分頁條數
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){$result=Db::table('employees')->field('employee_id, last_name')->page(2, 5) // 每頁顯示五條，第二頁->select();return json($result);} }order
- 使用 order()方法，可以指定排序方式，沒有指定第二參數，默認 asc
- 支持數組的方式，對多個字段進行排序
- 使用 orderRaw()方法，支持排序的時候指定 MySQL 函數
group
- 使用 group()方法，給性別不同的人進行 price 字段的總和統計
- 也可以進行多字段分組統計
having
使用 group()分組之后，再使用 having()進行篩選
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){$result=Db::table('employees')->field('job_id, manager_id, SUM(salary)')->group('job_id, manager_id')->having('SUM(salary)>10000')->select();return json($result);} }四：查詢構造器(進階)
1) 聚合查詢
在應用中我們經常會用到一些統計數據，例如當前所有（或者滿足某些條件）的用戶數、所有用戶的最大積分、用戶的平均成績等等，ThinkPHP為這些統計操作提供了一系列的內置方法，包括以下部分：
| count | 統計數量，參數是要統計的字段名（可選） |
| max | 獲取最大值，參數是要統計的字段名（必須） |
| min | 獲取最小值，參數是要統計的字段名（必須） |
| avg | 獲取平均值，參數是要統計的字段名（必須） |
| sum | 獲取總分，參數是要統計的字段名（必須） |
Db::name('user')->count();
Db::name('user')->count('uid');
Db::name('user')->max('price');
Db::name('user')->max('price', false);
Db::name('user')->min('price');
Db::name('user')->avg('price');
Db::name('user')->sum('price');
2) 分頁查詢
3) 時間查詢
傳統方式
- 可以使用>、<、>=、<=來篩選匹配時間的數據；
Db::name('user')->where('create_time', '>', '2018-1-1')->select(); - 可以使用 between 關鍵字來設置時間的區間
Db::name('user')->where('create_time', 'between', ['2018-1-1', '2019-12-31'])->select();
Db::name('user')->where('create_time', 'not between', ['2018-1-1', '2019-12-31'])->select();
快捷方式
- 時間查詢的快捷方法為 whereTime()，直接使用>、<、>=、<=； Db::name('user')->whereTime('create_time', '>', '2018-1-1')->select();
- 快捷方式也可以使用 between 和 not between；
Db::name('user')->whereBetween('create_time', ['2018-1-1', '2019-12-31'])->select(); - 還有一種快捷方式為：whereBetweenTime()和 whereNotBetweenTime()； Db::name('user')->whereBetweenTime('create_time', '2018-1-1', '2019-12-31')->select();
- 默認的大于>，可以省略；
Db::name('user')->whereTime('create_time', '2018-1-1')->select();
固定查詢
- 使用 whereYear 查詢今年的數據、去年的數據和某一年的數據
Db::name('user')->whereYear('create_time')->select();
Db::name('user')->whereYear('create_time', 'last year')->select();
Db::name('user')->whereYear('create_time', '2016')->select(); - 使用 whereMonth 查詢當月的數據、上月的數據和某一個月的數據
Db::name('user')->whereMonth('create_time')->select();
Db::name('user')->whereMonth('create_time', 'last month')->select();
Db::name('user')->whereMonth('create_time', '2016-6')->select(); - 使用 whereDay 查詢今天的數據、昨天的數據和某一個天的數據
Db::name('user')->whereDay('create_time')->select();
Db::name('user')->whereDay('create_time', 'last day')->select();
Db::name('user')->whereDay('create_time', '2016-6-27')->select();
其它查詢
- 查詢指定時間的數據，比如兩小時內的
Db::name('user')->whereTime('create_time', '-2 hours')->select(); - 查詢兩個時間字段時間有效期的數據，比如會員開始到結束的期間
Db::name('user')->whereBetweenTimeField('start_time', 'end_time')->select();
4) 高級查詢
快捷查詢
快捷查詢方式是一種多字段相同查詢條件的簡化寫法，可以進一步簡化查詢條件的寫法，在多個字段之間用|分割表示OR查詢，用&分割表示AND查詢，可以實現下面的查詢，例如：
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){Db::table('employees')->where('first_name|last_name', 'like', '%a%')->where('employee_id&manager_id', '>', 103)->select();return Db::getLastSql();} }閉包查詢
<?php namespace app\controller; use think\facade\Db;class DbTest {public function index(){$jobId='IT_PROG';$salary=11000;$res=Db::table('employees')->where(function ($query) use($jobId, $salary) {$query->where('job_id', $jobId)->whereOr('salary', '>', $salary);})->select();return json($res);} }字符串條件查詢
對于一些實在復雜的查詢，也可以直接使用原生SQL語句進行查詢，例如:
Db::table('think_user')->whereRaw('id > 0 AND name LIKE "thinkphp%"')->select();為了安全起見，我們可以對字符串查詢條件使用參數綁定，例如：
Db::table('think_user')->whereRaw('id > :id AND name LIKE :name ', ['id' => 0, 'name' => 'thinkphp%'])->select();快捷方法
系統封裝了一系列快捷方法，用于簡化查詢
| whereOr | 字段OR查詢 |
| whereXor | 字段XOR查詢 |
| whereNull | 查詢字段是否為Null |
| whereNotNull | 查詢字段是否不為Null |
| whereIn | 字段IN查詢 |
| whereNotIn | 字段NOT IN查詢 |
| whereBetween | 字段BETWEEN查詢 |
| whereNotBetween | 字段NOT BETWEEN查詢 |
| whereLike | 字段LIKE查詢 |
| whereNotLike | 字段NOT LIKE查詢 |
| whereExists | EXISTS條件查詢 |
| whereNotExists | NOT EXISTS條件查詢 |
| whereExp | 表達式查詢 |
| whereColumn | 比較兩個字段 |
動態查詢
查詢構造器還提供了動態查詢機制，用于簡化查詢條件
| whereFieldName | 查詢某個字段的值 |
| whereOrFieldName | 查詢某個字段的值 |
| getByFieldName | 根據某個字段查詢 |
| getFieldByFieldName | 根據某個字段獲取某個值 |
5) 子查詢
使用 fetchSql()方法，可以設置不執行 SQL，而返回 SQL 語句，默認 true；
Db::name('user')->fetchSql(true)->select();
使用 buildSql()方法，也是返回 SQL 語句，不需要再執行 select()，且有括號
Db::name('user')->buildSql(true);
結合以上方法，我們實現一個子查詢：
子查詢還有閉包模式，IN/NOT IN和EXISTS/NOT EXISTS之類的查詢可以直接使用閉包作為子查詢，例如：
Db::table('think_user')->where('id', 'IN', function ($query) {$query->table('think_profile')->where('status', 1)->field('id');})->select();6) 原生查詢
注意：V6.0.3+版本開始，原生查詢僅支持Db類操作，不支持在模型中調用原生查詢方法（包括query和execute方法）。
Db類支持原生SQL查詢操作，主要包括query方法和execute方法
query方法用于執行SQL查詢操作，返回查詢結果數據集（數組）。
Db::query("select * from think_user where status=:id", ['id' => 1]);execute用于更新和寫入數據的sql操作，如果數據非法或者查詢錯誤則返回false，否則返回影響的記錄數。
Db::execute("update think_user set name='thinkphp' where status=1");支持在原生查詢的時候使用參數綁定，包括問號占位符或者命名占位符，例如：
Db::query("select * from think_user where id=? AND status=?", [8, 1]); // 命名綁定 Db::execute("update think_user set name=:name where status=:status", ['name' => 'thinkphp', 'status' => 1]);總結
以上是生活随笔為你收集整理的thinkphp的数据库操作(上)的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： English语法_介词搭配
- 下一篇： 对LRU算法的理解和应用场景